Where Are My Views?

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.)