Home
All Oracle Error Codes
Oracle DBA Forum

Frequent Oracle Errors

TNS:could not resolve the connect identifier specified
Backtrace message unwound by exceptions
invalid identifier
PL/SQL compilation error
internal error
missing expression
table or view does not exist
end-of-file on communication channel
TNS:listener unknown in connect descriptor
insufficient privileges
PL/SQL: numeric or value error string
TNS:protocol adapter error
ORACLE not available
target host or object does not exist
invalid number
unable to allocate string bytes of shared memory
resource busy and acquire with NOWAIT specified
error occurred at recursive SQL level string
ORACLE initialization or shutdown in progress
archiver error. Connect internal only, until freed
snapshot too old
unable to extend temp segment by string in tablespace
Credential retrieval failed
missing or invalid option
invalid username/password; logon denied
unable to create INITIAL extent for segment
out of process memory when trying to allocate string bytes
shared memory realm does not exist
cannot insert NULL
TNS:unable to connect to destination
remote database not found ora-02019
exception encountered: core dump
inconsistent datatypes
no data found
TNS:operation timed out
PL/SQL: could not find program
existing state of packages has been discarded
maximum number of processes exceeded
error signaled in parallel query server
ORACLE instance terminated. Disconnection forced
TNS:packet writer failure
see ORA-12699
missing right parenthesis
name is already used by an existing object
cannot identify/lock data file
invalid file operation
quoted string not properly terminated

Re: Debug DDL trigger

rjamya

2005-08-24

Replies:
Not true, see below for an example on 9204

SQL> get t1.sql
  1  conn t1/t1
  2  create table t1.some_tbl tablespace cons
  3  as select * from dba_objects where rownum < 100
  4  /
  5  drop trigger raj_test_trig
  6  /
  7  alter session set events '10046 trace name context forever, level 4'
  8  /
  9  CREATE OR REPLACE TRIGGER raj_test_trig
 10     BEFORE DDL ON SCHEMA
 11  DECLARE
 12     PRAGMA AUTONOMOUS_TRANSACTION;
 13     v_datestamp          DATE := sysdate;
 14     v_ddl_stmt           VARCHAR2(4000);
 15     v_ora_error          NUMBER;
 16     v_table_count        PLS_INTEGER := 1;
 17  BEGIN
 18     dbms_output.enable(1000000);
 19     dbms_output.put_line('Trigger: checking for table');
 20     dbms_output.put_line('Trigger: sysevent is ' || ora_sysevent);
 21     if ora_sysevent = 'DROP' then
 22     SELECT COUNT(*) INTO v_table_count
 23       FROM user_tables
 24      WHERE table_name = ora_dict_obj_name;
 25     dbms_output.put_line('Trigger: table count is ' || v_table_count);
 26     IF v_table_count > 0 THEN
 27        v_ddl_stmt := 'CREATE TABLE t1.'||ora_dict_obj_name||'_bkp AS SELECT * FROM t1.'||ora_dict_obj_name;
 28        dbms_output.put_line('Trigger cmd: ' || v_ddl_stmt);
 29        EXECUTE IMMEDIATE v_ddl_stmt;
 30     END IF;
 31     END IF;
 32  END;
 33  /
 34  set serveroutput on
 35  drop table some_tbl;
 36* select table_name from user_tables;

SQL> @t1
Connected.
create table t1.some_tbl tablespace cons
                *
ERROR at line 1:
ORA-00955: name is already used by an existing object



Trigger dropped.


Session altered.


Trigger created.

drop table some_tbl
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at line 19

TABLE_NAME
------------------------------
SOME_TBL

SQL> connect / as sysdba
Connected.
SQL> drop table t1.some_tbl;

Table dropped.


Raj

On 8/24/05, Jared Still <jkstill@gmail.com> wrote:
I believe the triggers that Raj was thinking of were logon triggers.

Those do not fire for accounts with the DBA privilege.

Jared

On 8/23/05, Wolfgang Breitling < breitliw@centrexcc.com> wrote:
At 08:08 AM 8/23/2005, rjamya wrote:
>psst: for all this to succeed, you should be a non-dba user ...
>these triggers don't fire for people with DBA privs.

Not quite correct. They don't fire for sys, but they DO fire for
ordinary users, even those with the DBA role.


Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l



--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist




--
------------------------------
select standard_disclaimer from company_requirements where category = 'MANDATORY';