This content has been marked as final. Show 2 replies
I have tested on my DEVELOPMENT DB Release 126.96.36.199.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');
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
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
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.