View the Exhibit and examine the structure of the EMP table belonging to the user SCOTT. The EMP table contains the details of all the current employees in your organization. EMPNO is the PRIMARY KEY. User SCOTT has created an ENAME_IDX index on the ENAME column and an EMP_VW view that displays the ENAME and SALARY columns. The recyclebin is enabled in the database. SCOTT executes the following command:
SQL> DROP TABLE emp;
Which details would be stored in the recycle bin? (Choose all that apply)
A. EMP_VW
B. ENAME_IDX
C. The PRIMARY KEY constraint
D. Only the structure of the EMP table
E. Structure and data of the EMP table
Exhibit:
Theory:
Since the question is about FLASHBACK (DROP TABLE and RECYCLE BIN), the required information needed to answer this question can be found as follows: FLASHBACK TECHNOLOGY and FLASHBACK TABLE. So when a user drops a table, Oracle database does not immediately remove the space associated with the table. Instead, the table is renamed and, along with any associated objects, it is placed in the Recycle Bin of the database, and the Flashback Drop operation can recover the table from the recycle bin. The recycle bin is a logical container for all dropped tables and their dependent objects. When a table is dropped, the database will store the table (structure and data), along with its dependent objects in the recycle bin so that they can be recovered later. Dependent objects which are stored in the recycle bin include indexes, constraints, triggers, nested tables, LOB segments and LOB index segments. The table and its dependent objects will remain in the recycle bin until they are purged from the recycle bin. Dropped objects still appear in the views USER_TABLES, ALL_TABLES, DBA_TABLES, USER_INDEX, ALL_INDEX and DBA_INDEX. A new column, DROPPED, is set to YES for these objects. Any stored procedures that referenced the original object, though, are invalidated.
Answer:
A - Wrong - dropping table does not drop view
B - Correct - index gets stored
C - Correct - primary key constraint gets stored
D - Wrong - not only the structure of the table but also the data
E - Correct - the structure and the data of the table gets stored
1 comments:
Thanks so much for this. You could add that the Oracle recycle bin does not preserve foreign keys - another way of testing this knowledge is a "what would happen if" question.
http://docs.oracle.com/cd/B14117_01/server.101/b10734/rcmflash.htm#1016941
The recycle bin does not preserve referential constraints on a table (though other constraints will be preserved if possible). If a table had referential constraints before it was dropped (that is, placed in the recycle bin), then you may re-create any referential constraints after you perform Flashback Drop to retrieve the table from the recycle bin.