2 Replies Latest reply: Oct 11, 2012 4:44 AM by NM RSS

    Table is getting Dropped automatically but not recorded in dba_audit_trail

    NM
      Hi,

      I have strange problem where the table is getting dropped automatically but not recorded in dba_audit_trail even after enabling audit on the table.

      sys@SURV1> select * from v$version;

      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
      PL/SQL Release 11.2.0.2.0 - Production
      CORE 11.2.0.2.0 Production
      TNS for Solaris: Version 11.2.0.2.0 - Production
      NLSRTL Version 11.2.0.2.0 - Production


      This is the Activity user does

      MASTER_STATS_BACKUP is table which contains the stats and stored in tools schema

      execute dbms_stats.gather_schema_stats(user, cascade => TRUE,options=> 'GATHER');
      EXEC DBMS_STATS.create_stat_table(user,'MASTER_STATS_BACKUP');
      EXEC DBMS_STATS.export_schema_stats(user,'MASTER_STATS_BACKUP');

      Now the user does the below activities but before that table is getting dropped.

      create table MASTER_STATS as select * from tools.MASTER_STATS_BACKUP;
      update MASTER_STATS set c5=upper('${LOGNAME}_trd');
      commit;
      exec DBMS_STATS.delete_schema_stats(user);
      EXEC DBMS_STATS.import_schema_stats(user,'MASTER_STATS');

      Regards
      NM
        • 1. Re: Table is getting Dropped automatically but not recorded in dba_audit_trail
          SanjayPatel
          I have tested on my DEVELOPMENT DB Release 11.2.0.1.0. Table MASTER_STATS_BACKUP is not getting dropped after performing following 3 steps.

          execute dbms_stats.gather_schema_stats(user, cascade => TRUE,options=> 'GATHER');
          EXEC DBMS_STATS.create_stat_table(user,'MASTER_STATS_BACKUP');
          EXEC DBMS_STATS.export_schema_stats(user,'MASTER_STATS_BACKUP');

          As far as AUDITING is concerned ORACLE does not LOG entry, if OWNER, DROP the table.

          However we can write down one system trigger as follows, which will help is in identifying the reason for table drop.

          CREATE TRIGGER DBMIS.BEF_DROP_STATS_TABLE BEFORE DROP ON DATABASE
          begin
          if ora_sysevent = 'DROP' and
          ora_dict_obj_owner = 'TOOLS' and
          ora_dict_obj_type = 'TABLE' and
          ora_dict_obj_name = ('MASTER_STATS_BACKUP') then

          /* Capture here the information like Login USER (ora_login_user), Terminal (userenv('terminal')) etc and Log into some TABLE
          or raise here error so that we can come to know when the TABLE is getting dropped
          */
          null;
          end if;
          end;
          • 2. Re: Table is getting Dropped automatically but not recorded in dba_audit_trail
            NM
            Hi,

            I found the problem why this problem was happening.

            Table Master_stats_backup was exported from user ABC his default tablespace ABC so Master_stats_backup was in ABC tablespace.Now Master_stats_backup was imported using system user command to Tools schema but the object was created in ABC rather then in his own tablespace.Daily we have job which drop and recreates the schema for Regression test purpose where the ABC will be dropped and recreated so during this period the Master_stats_backup table was dropped automatically.

            Regards
            NM