SQL & PL/SQL :: Unable To Drop A Trigger?
Jun 14, 2011
I have a trigger called DDL_TRIG which is used to audit all DDL level operation on the Database. The trigger script is as below:-
CREATE OR REPLACE TRIGGER ddl_trig
AFTER DDL
ON DATABASE
BEGIN
INSERT INTO ddl_log
(user_name, ddl_date, ddl_type,
[code].....
The Table DDL_LOG exists.Now i want to disable or drop this trigger and it wont allow us. While doing on one schema it fails and with below error:
when logged in schema1 and give drop or disable command it fails
ORA:04098:trigger schema2.ddl_trig is invalid and failed re-validation.
when logged in schema2 and give drop or disable command it fails
ORA:04098:trigger schema1.ddl_trig is invalid and failed re-validation.
how can we drop this trigger in both the schema's. This trigger is not allowing us to do any activity on the Database and causing lot of problem.
View 7 Replies
ADVERTISEMENT
Nov 29, 2012
I tried to Drop a procedure we have created, but an error is raised while droping it.
ora-04043 object does not exist
View 28 Replies
View Related
Jan 3, 2013
i want to drop a broken job but it is taking long time why?
i am using below syntax:
begin
dbms_job.broken(job=> 345,FALSE);
commit;
end;
/
View 5 Replies
View Related
Oct 29, 2013
i'm issuing drop procedure generatePasswordForEportalUser
i have an
error ORA-00600: [kqlobjlod-no-result-from-proc$], [1403], [659858], [], [], [], [], [], [], [], [], []. i found on metalink then Doc ID 1267554.1
is about this error i'm using oracle 11.2.0.3 in this document there is an info that 11.2.0.2 solves the issue Interesting thing is that select * from user_procedures where upper(object_name) like upper ('generatePasswordForEportalUser');
generatePasswordForEportalUser6598581PROCEDURENONONONONODEFINERobject name is in lovercase it was created by create procedure generatePasswordForEportalUser asbeginnull;end ;without " name " why it is in lowercase in data dictionary
View 1 Replies
View Related
May 13, 2011
I am trying to Truncate the table but it doesnt get truncated. When I issue the command it even doesnt throw the error. I also tried to drop the table but cant even able to drop the table. I thought table might be locked. But it allows me delete a row from the table.
I am using Oracle 11g Release 11.1.0.7.0
View 17 Replies
View Related
Nov 14, 2013
SQL> SELECT * FROM DBA_UNUSED_COL_TABS; OWNER TABLE_NAME COUNT ------------------------------ ------------------------------ ---------- CRIS_WAREHOUSE_USER F_TRADING_COLLATERAL_SR 1 SQL> SELECT * FROM DBA_UNUSED_COL_TABS; OWNER TABLE_NAME COUNT ------------------------------ ------------------------------ ---------- CRIS_WAREHOUSE_USER F_TRADING_COLLATERAL_SR 1 SQL> alter table F_TRADING_COLLATERAL_SR drop unused columns; Table altered. SQL> SELECT * FROM DBA_UNUSED_COL_TABS; OWNER TABLE_NAME COUNT ------------------------------ ------------------------------ ---------- CRIS_WAREHOUSE_USER F_TRADING_COLLATERAL_SR 1 SQL>
View 6 Replies
View Related
Sep 29, 2011
writing a trigger to drop partitions with zero rows which are older than 6months and drop the local indexes and rebuild the global indexes for any schema in a databaase ?
I have tried the below code :
declare
v_statement varchar2(600);
v_rows number;
begin
for x in (select *
from dba_tab_partitions
[code]........
I want to avoid using row number and also want to dynamically select a schema when executing the script.
View 39 Replies
View Related
Jan 10, 2012
When I try to drop the roles in dbartisan tool, I get an error saying "Drop DDL for **_***********_ROLE has not been generated because it is a system object", but if i go to sql prompt and say "drop role rolename", it is working. How do I remove the role in dbartisan itself?
View 9 Replies
View Related
Oct 10, 2013
,I am trying to set up Streams on a 11.2.0.3 on a Windows 2008R2 server. Due to an error in running propagation, i a, trying to delete both Capture and Apply queues. I have deleted the Apply queues but unable to drop the Catpure,
SQL>EXEC DBMS_APPLY_ADM.STOP_APPLY(apply_name =>'LAO_NLPG73_BLPU_APPLY'); SQL> select * from dba_apply; LAO_NLPG73_BLPU_APPLY NLPG73_BLPU_APPLY_Q STRMADMIN YES RULESET$_732 STRMADMIN STRMADMIN 301355 ABORTED 09/10/2013 17:34:21 1013 ORA-01013: user requested cancel of current operation CAPTURED STREAMS APPLY SQL> select * from dba_queues; STRMADMIN NLPG73_BLPU_APPLY_Q
[code]...
View 5 Replies
View Related
May 11, 2012
I performed the Table Redefinition process on one of my transaction tables. I wanted to redefine the existing table to a new table with partitions. Everything worked fine until i reached the last step where we had to drop the interim table. Now in between the redefinition , we created the interim table with partition along with the constraints and indexes and then atlast ran the "dbms_redefinition.finish_redef_table" proc to finish the redefinition.
And after that when I tried to drop the interim table , I am getting the below error message
"ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys"
So this means all the constraints from my actual table were swapped with the interim table and hence I am getting this error.So is this a normal case or I have missed something while performing the redefinition? If thats normal then should i Just Drop table tablename CASCADE CONSTRAINTS?
View 10 Replies
View Related
Apr 19, 2013
We are unable to drop user due to below error, how to drop the below user without shutdown the database.
SQL> drop user mvm_2010 cascade;
drop user mvm_2010 cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-14452: attempt to create, alter or drop an index on temporary table already in use
No session was available for particular user.
View 19 Replies
View Related
Jun 6, 2011
Im facing the problem whenever I try to drop a user. Following thing that I m trying..
system@vahan> drop user knp cascade;
Error at line 1:
ORA-00604: error occured at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-06512: at line 7
View 4 Replies
View Related
Dec 3, 2010
I have created a procedure to build trigger dynamically using Dynamic SQL. Here procedure created successfully, when we execute the procedure to build trigger getting the following error.
Note: We are able to create a Trigger, but it is INVALID. if we get the code of a trigger and execute, trigger created a successfully with Valid Status.
ERROR:
ORA-24344: success with compilation error
ORA-06512: at "APPS.CREATE_TRIGGER", line 28
ORA-06512: at line 2
SQL*Plus internal error state 2087, context 47:0:0
Unsafe to proceed
View 2 Replies
View Related
Apr 19, 2012
I have schema a and b in the same database with synonym table a (schema a) to schema b. When I try to disable trigger from table a in schema b; I got this message:
ORA-00942: table or view does not exist
I have granted all permissions to schema b and I am able to SELECT FROM table a in schema b
View 11 Replies
View Related
Aug 17, 2012
One of my Customer had followed the document applied pertaining to mitigate against vulnerability CVE-2012-3132.
New Document Mitigation steps for CVE-2012-3132 [ID 1482694.1]
This was applied to a database and it stopped the 'password' command from succeeding in SQLPLUS. Once the trigger was disabled, it worked fine.
ORA-00604: error occurred at recursive SQL level 1
ORA-06531: Reference to uninitialized collection
ORA-06512: at "SYS.NAME_SECURITY", line 165
ORA-06512: at line 2
Why is this behavior seen? By the way, he was able to change the password using TOAD. Only sqlplus is the issue.
View 2 Replies
View Related
Oct 22, 2013
I am using the following code in Java Script function
function notNull(object){ if(object.value=="") alert('This field must contain a value.'); }
I am then calling the function from one of the page item under HTML Form Element Attributes by using the following
onblur="notNull(this);"
But it doesn't seem to work. I am using APEX 4.2.3
View 3 Replies
View Related
Jul 1, 2011
How can "call one trigger of item in trigger of form"
View 5 Replies
View Related
Nov 28, 2012
I have a dropdown in my jsp with all users from database. When I select a user I'm able to get the details of the corresponding result, but when I click on all it should give the total individually. Below is the code that I use to get individually.
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@include file="DBCon.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<meta charset="utf-8" />
[code]...
View 1 Replies
View Related
Aug 25, 2010
i am tring to drop column but it is taking much time . And there is not locking session also. The table size is 500GB. We have any way to drop to column in fast?
View 2 Replies
View Related
Apr 6, 2010
When drop a table what happen to view,sequence,synonyms,index,constraint for the table
View 9 Replies
View Related
Aug 29, 2012
I have a schema sys_eg .I dont have any DBA privileges .How can i delete all the procedure with one sql or pl/sql.
ORACLE 11G
I am using sql developer 3.2 version tool
View 6 Replies
View Related
Apr 19, 2012
we have our production database running on 11.2.0.2. I have a user table which has partitions. i would like to drop the partition and seeing weird issue:
The command to drop the partition i have is:
alter table SA.accounts drop partition dec_20111203 update global indexes;
Say after 10 minutes, the session is terminated and we are seeing the following: ORA-00028. We don't have a process that kills the session so we are trying to find the root cause why the session is being killed. what might be going on? also as an alternative - i was looking to do the following and want to know - if this works or not:
ALTER TABLE sa.accounts TRUNCATE PARTITION dec_20111203;
Then try to do the drop partition again.
View 7 Replies
View Related
Apr 3, 2012
How to find out who has dropped and when was the index created in database?
View 39 Replies
View Related
Dec 17, 2012
i want drop all tables in oracle8
View 2 Replies
View Related
Sep 26, 2011
Query to drop partitions on a table who have no.of rows as zero.
select 'ALTER' || '' || 'TABLE' || TABLE_NAME || 'DROP' || 'PARTITION' || PARTITION_NAME from dba_tab_partitions where TABLE_OWNER='xyz' ;
select count(*) from table_name partition (partition_name);
View 14 Replies
View Related
Oct 16, 2013
i want to create a dropdown field it will display list of currencies for selection derived from
sy_curr_code (columns currency_code and des1)
in the s_sales_center(table )
-If INF_SRC_DB = AUS, will default to USD, otherwise default to BPS.
i have created this so far
vSQl := 'select toValueText(a.code1, a.descr) from (select currency_code code1, DES1 descr1 '||
'from sy_curr_code where active = ''Y'' '||
'and subcat1 = ''Y'' '||
') a ';
how to add the other logic into my code
View 37 Replies
View Related
Nov 3, 2012
in one short i want to drop more than one user?
View 6 Replies
View Related
Nov 9, 2012
i tried creating an context index. But it is aborted in the middle. do to reuse the spce i want to drop the index. But it shows an error that " cannot drop index marked as process" how to delete the index.
View 1 Replies
View Related
Aug 31, 2012
I want to free up some space , as i have created some testing users and schema.
View 3 Replies
View Related
Jan 31, 2013
How to monitor the DROP USER command?
OS: AIX, Oracle: 11.2.0.2
View 8 Replies
View Related
