March 5, 2010
A year ago I received an email that was sent to an ERP user’s mailing list that stated that statically defined views were disappearing from one of the email author’s databases. He stated that this problem had been an issue since Oracle 9i (probably 9.2.0.x) and continues to be a problem with Oracle 10g (likely 10.2.0.3 or 10.2.0.4). The views were simply disappearing. Was it caused by a bug in Oracle? Was it caused by a bug or a naming conflict in the ERP system? Was it caused by a renegade developer? Was it a permission problem? Was it a dropped synonym? Or was someone routinely breaking it at night, found that the actual table was too big to fit in the get-away car, and managed to escape with only a picture of the table?
Since we do not know what is happening to the views, maybe the best course of action is to set a trap. First, we will create a logging table to capture details about any objects that are dropped:
CREATE TABLE OBJECT_DROP_LOG ( DICT_OBJ_TYPE VARCHAR2(40), DICT_OBJ_OWNER VARCHAR2(40), DICT_OBJ_NAME VARCHAR2(40), DROP_USERNAME VARCHAR2(30) DEFAULT USER, DROP_DATE DATE DEFAULT SYSDATE); GRANT INSERT ON OBJECT_DROP_LOG TO PUBLIC; GRANT ALL ON OBJECT_DROP_LOG TO SYS;
As rows are inserted into the table, the DROP_USERNAME column will be automatically populated with the username of the person performing the object drop, and the DROP_DATE column will be automatically populated with the date and time of the drop. Now we need a trigger to capture the other three columns in the table, the trigger will be created by the SYS user:
CREATE OR REPLACE TRIGGER LOG_DROP AFTER DROP ON DATABASE BEGIN IF ORA_SYSEVENT='DROP' THEN INSERT INTO TESTUSER.OBJECT_DROP_LOG( DICT_OBJ_TYPE, DICT_OBJ_OWNER, DICT_OBJ_NAME) VALUES( ORA_DICT_OBJ_TYPE, ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME); END IF; END; /
Now let’s log back in as our test user and create a couple of objects that will be dropped later:
CREATE TABLE T10 AS SELECT ROWNUM C1 FROM DUAL CONNECT BY LEVEL<=10; Table created. CREATE UNIQUE INDEX IND_T10 ON T10(C1); Index created. CREATE VIEW MY_STUFF AS SELECT * FROM T10; View created.
Now let’s suppose that the event happens, something does this:
DROP VIEW MY_STUFF; View dropped. DROP TABLE T10; Table dropped.
Now that the damage is done, will the logging table contain 0, 1, 2, or 3 entries? Let’s log in as SYS and see what was captured:
COLUMN DICT_OBJ_TYPE FORMAT A13 COLUMN DICT_OBJ_OWNER FORMAT A14 COLUMN DICT_OBJ_NAME FORMAT A13 COLUMN DROP_USERNAME FORMAT A13 COLUMN DROP_DATE FORMAT A19 SELECT DICT_OBJ_TYPE, DICT_OBJ_OWNER, DICT_OBJ_NAME, DROP_USERNAME, TO_CHAR(DROP_DATE,'MM/DD/YYYY HH24:MI:SS') DROP_DATE FROM OBJECT_DROP_LOG ORDER BY DROP_DATE; DICT_OBJ_TYPE DICT_OBJ_OWNER DICT_OBJ_NAME DROP_USERNAME DROP_DATE ------------- -------------- ------------- ------------- ------------------- VIEW TESTUSER MY_STUFF TESTUSER 03/04/2010 23:17:33 TABLE TESTUSER T10 TESTUSER 03/04/2010 23:18:08
We see the drop of the view and the drop of the table, does the index still exist?
SELECT COUNT(*) FROM DBA_OBJECTS WHERE OBJECT_NAME='IND_T10'; COUNT(*) ---------- 0
Looks like the table’s index is gone without a trace. Let’s recover the table:
FLASHBACK TABLE TESTUSER.T10 TO BEFORE DROP; Flashback complete. SELECT COUNT(*) FROM DBA_OBJECTS WHERE OBJECT_NAME='T10'; COUNT(*) ---------- 1 SELECT COUNT(*) FROM DBA_OBJECTS WHERE OBJECT_NAME='IND_T10'; COUNT(*) ---------- 0
The table is back, but where is my index?
SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='T10' AND OWNER='TESTUSER'; INDEX_NAME ------------------------------ BIN$9mtb8W99SfiPr1EXVFGkgw==$0
That is a rather ugly name for an index – it was automatically changed when the associated table was dropped. Connecting again as the TESTUSER:
ALTER INDEX "BIN$9mtb8W99SfiPr1EXVFGkgw==$0" RENAME TO IND_T10; Index altered. SELECT COUNT(*) FROM USER_OBJECTS WHERE OBJECT_NAME='IND_T10'; COUNT(*) ---------- 1
The table and index are now back, so:
SELECT COUNT(*) FROM USER_OBJECTS WHERE OBJECT_NAME='MY_STUFF'; COUNT(*) ---------- 0
Where is my view? If I had executed the following, I could easily recreate the view:
SET PAGESIZE 0 SET LONG 90000 SPOOL MYSTUFF.SQL SELECT DBMS_METADATA.GET_DDL('VIEW','MY_STUFF','TESTUSER') FROM DUAL; SPOOL OFF
(Caution: If you choose to implement this trigger, test it very carefully before pushing it into a production environment. You could of course extend this example to capture much more information.)