OCP Oracle 10g Admin II - Ch 6: Recovering from User Errors

Chapter 6: Recovering from User Errors

Review Questions

1. Which underlying database technology is used by Flashback Drop, Flashback Table, and Flashback Versions Query to recover data?
A. Redo logs
B. Rollback segments
C. Undo data
D. Archive logs
---
Ans: C.
All the Flashback Technologies, with the exception of the Flashback Database,
utilize the undo data to recover objects.

2. Which of the following statements is true regarding the Recycle Bin? (Choose all that apply.)
A. The Recycle Bin is a physical storage area of flashback dropped objects.
B. The Recycle Bin is a logical container area of flashback dropped objects.
C. The objects in the Recycle Bin are stored in the UNDO tablespace.
D. The objects in the Recycle Bin are stored in the tablespace they were created in.
---
Ans: B, D.
The Recycle Bin is a logical container of flashback dropped objects. The objects in the Recycle Bin are stored in the tablespace they were created in.

3. What actions does the Flashback Drop process perform?
A. Back up table only
B. Back up table and indexes only
C. Back up table and referential constraints only
D. Back up table and dependent objects
---
Ans: D.
The Flashback Drop process is responsible for backing up tables and their dependent objects.

4. Which activity can occur with the Recycle Bin?
A. All indexed-organized tables are protected by the Recycle Bin.
B. System-and dictionary-managed tablespaces are stored in the Recycle Bin.
C. Dependent objects of stored tables, including referential constraints, are stored in the Recycle Bin.
D. Data Manipulation Language (DML) and Data Definition Language (DDL) can be run against objects in the Recycle Bin.
E. None of the above.
---
Ans: E.
Only non-partitioned index-organized tables are protected by the Recycle Bin.
Non-system and locally managed tablespaces are stored in the Recycle Bin.
Referential constraints are not stored in the Recycle Bin. DML or DDL cannot be
performed against objects in the Recycle Bin.

5. One method of dropping objects and bypassing the Recycle Bin is to perform which command?
A. DROP USER user CASCADE
B. DROP TABLE
C. DROP TABLE INCLUDING CONTENTS
D. DROP USER user
--
Ans: A.
The command DROP USER user CASCADE drops the user and the database objects
without recording a backup in the Recycle Bin. Objects in the Recycle Bin owned by
the user will be removed from the Recycle Bin.

6. Which command is responsible for removing the objects in multiple users from the Recycle Bin?
A. PURGE RECYCLEBIN
B. PURGE TABLESPACE user
C. PURGE DBA_RECYCLEBIN
D. PURGE TABLES user
---
Ans: C.
The PURGE DBA_RECYCLEBIN command purges the complete Recycle Bin for all users.

7. What is the naming convention of a Recycle Bin object?
A. BIN$globalUID$version
B. BIN$global$UIDversion
C. BIN$globalUIDversion
D. BINglobalUIDversion
---
Ans: A.
The naming convention of objects in the Recycle Bin consists of a globalUID and
version number assigned by the database with BIN$ prefixed, and a $ between the
globalUID and the version number. The formatting looks like
BIN$globalUID$version.

8. What two methods can be used to view the Recycle Bin?
A. Run the SHOW RECYCLEBIN command.
B. Query the view DBA_RECYCLEBIN.
C. Query the view V$RECYCLEBIN.
D. Query the view RECYCLEBIN.
----
Ans: A, D.
The SHOW RECYCLEBIN command and querying the RECYCLEBIN view are two methods of viewing the contents of the Recycle Bin.

Note: the answer should be A, B. RECYCLENIN is a public synonym for USER_RECYTCLEBIN

select owner, synonym_name, table_owner, table_name from dba_synonyms where synonym_name='RECYCLEBIN'

OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
--------------- -------------------- -------------------- --------------------
PUBLIC RECYCLEBIN SYS USER_RECYCLEBIN


9. What best describes the space pressure in the Recycle Bin?
A. No free extents in the Recycle Bin, and objects being removed from the Recycle Bin to free up extents for non-Recycle Bin objects.
B. No free extents in a tablespace, and objects being removed from the Recycle Bin to free up extents for non-Recycle Bin objects.
C. No free extents in a tablespace, and objects being removed from the Recycle Bin to free up extents in a tablespace for non-Recycle Bin objects on a first in, first out (FIFO) basis.
D. No free extents in the Recycle Bin tablespace and objects being removed from Recycle Bin tablespace to free up extents for non- Recycle Bin objects on a first in, first out (FIFO) basis.
---
Ans: C.
The Recycle Bin is not stored in a Recycle Bin tablespace. It is a logical container pointing to objects in the tablespace where the objects were originally created. Objects are removed from the Recycle Bin when there is a need for available extents in a non-system tablespace. The objects are removed on a first in, first out (FIFO) basis.


10. If a tablespace is configured for AUTO EXTEND, what will occur when there are no free extents and there are objects in the AUTO EXTEND tablespace?
A. The tablespace will autoextend to make more free space and Oracle
will not remove Recycle Bin objects associated with the tablespace.
B. The tablespace will not autoextend, and objects in the Recycle Bin will
be removed to make space in the tablespace.
C. The tablespace will autoextend to make more free space and Oracle
will remove Recycle Bin objects associated with the tablespace.
D. The tablespace will not autoextend, and objects in the Recycle Bin will
be compressed to make space in the tablespace.
---
Ans: B.
A tablespace with AUTO EXTEND enabled will not autoextend to make space for the Recycle Bin objects. The objects will be removed from the Recycle Bin in the standard first in, first out (FIFO) basis.

11. Which of the following statements best describes Flashback Versions Query?
A. Flashback Versions Query is a query to perform diagnostics on version
changes in row data on rows that existed between the times the query
was executed to a determined point-in-time in the past.
B. Flashback Versions Query is a method of querying all version changes
on rows that existed between the times the query was executed to a
determined point-in-time in the past.
C. Flashback Versions Query is a query to perform diagnostics on table
changes in row data on rows that existed between the times the query
was executed to a determined point-in-time in the past.
D. Flashback Versions Query is a method of querying all version changes
on rows that existed between the times the query was executed to a
determined point-in-time in the future.
---
Ans: B.
Flashback Versions Query is a method of querying all version changes on rows. The rows must exist from the time the query was executed to a point-in-time in the past. The query will not display committed data after the query has been executed.

12. What view can be used to query diagnostic information about transactional changes in the database?
A. FLASHBACK_TRANSACTION_QUERY
B. DBA_TRANSACTION_QUERY
C. V$TRANSACTION_QUERY
D. V$FLASHBACK_TRANSACTION_QUERY
----
Ans: A.
The view FLASHBACK_TRANSACTION_QUERY is used as a diagnostic tool to identify version information about transactional changes to the database. This view can be used to view the DML statements that were executed against a row and in a specific table.

13. What are the methods of performing a Flashback Versions Query? (Choose all that apply.)
A. Flashback Versions Query can be performed by minimum and maximum SCN value.
B. Flashback Versions Query can be performed by minimum and maximum sequence number.
C. Flashback Versions Query can be performed by starting and ending timestamp.
D. Flashback Versions Query can be performed by minimum and maximum undo value.
----
Ans: A, C.
The Flashback Versions Query can be performed by either MINVALUE and MAXVALUE SCN or starting and ending TO_TIMESTAMP value.

14. Which of the following statements is true regarding the VERSIONS BETWEEN clause?
A. The VERSIONS BETWEEN clause only supports SCN.
B. The VERSIONS BETWEEN clause only supports log sequences.
C. The VERSIONS BETWEEN clause cannot produce versions past modifications to the table structure.
D. The VERSIONS BETWEEN clause can produce versions past Data Definition Language (DDL) changes to an object.
---
Ans: C.
The VERSIONS BETWEEN clause of the Flashback Versions Query cannot query past table modifications or DDL changes to a table.

15. Which pseudocolumn do you use to identify a unique row in a Flashback Versions Query?
A. VERSIONS_XID
B. BVERSIONS_OPERATION
C. VERSIONS_ENDTIME
D. VERSION_STARTTIME
----
Ans: A.
VERSIONS_XID uniquely identifies a row in the Flashback Versions Query. The other pseudocolumns can narrow down the selection criteria and may identify multiple rows in a timeframe or SCN range.

16. Which of the following statements are true regarding the VERSIONS BETWEEN clause? (Choose all that apply.)
A. The VERSIONS BETWEEN clause may be used in DML statements.
B. The VERSIONS BETWEEN clause may be used in DDL statements.
C. The VERSIONS BETWEEN clause may be used to query past DDL changes to tables.
D. The VERSIONS BETWEEN clause may not be used to query past DML statements to tables.
----
Ans: A, B.
The VERSIONS BETWEEN clause can be used in DML and DDL statements to identify data.


17. Which of the following statements is true regarding implementing a Flashback Table recovery?
A. SCN is never needed to perform a Flashback Table recovery.
B. ROW MOVEMENT must be enabled to perform a Flashback Table recovery.
C. Only one table may be recovered to perform a Flashback Table recovery.
D. Flashback Table recovery does not use undo data to perform a Flashback Table recovery.
---
Ans: B.
The command ALTER TABLE table_name ENABLE ROW MOVEMENT must be enabled to allow Flashback Table recovery.

18. What happens to enabled triggers on a table after a FLASHBACK TABLE command is performed? (Choose all that apply.)
A. The triggers are disabled by default.
B. The triggers are enabled by default.
C. Disabled triggers remain disabled with the default FLASHBACK TABLE command.
D. All triggers are enabled if the ENABLE TRIGGER clause is used.
---
Ans: A, C.
The default action for the FLASHBACK TABLE command is that the triggers will be disabled. If you disable the triggers on a table, the triggers will stay disabled after the FLASHBACK TABLE command as well. Also, if you use the ENABLE TRIGGER clause, all enabled triggers on the table prior to the Flashback Table operation will be enabled and all disabled triggers will be disabled.

19. What method can you use to identify a DML operation and the SQL statement
that has been performed against a row in a specific table for a schema owner?
(Choose all that apply.)
A. Query DBA_TRANSACTION_QUERY for TABLE_NAME, OPERATION, and
UNDO_SQL. Limit rows by START_SCN and TABLE_OWNER.
B. Query FLASHBACK_TRANSACTION_QUERY for TABLE_NAME, OPERATION,
and UNDO_SQL. Limit rows by START_SCN and TABLE_OWNER.
C. Query FLASHBACK_TRANSACTION_QUERY for TABLE_NAME, OPERATION,
and UNDO_SQL. Limit rows by START_TIMESTAMP and TABLE_OWNER.
D. Query DBA_TRANSACTION_QUERY for TABLE_NAME, OPERATION, and
UNDO_SQL. Limit rows by START_SCN and TABLE_OWNER.
----
Ans: B, C.
The proper method is to query the FLASHBACK_TRANSACTION_QUERY view. The TABLE_ NAME, OPERATION, and UNDO_SQL columns should be queried to display the information about the transactional information. The transactional information from FLASHBACK_TRANSACTION_ QUERY should then be narrowed down by START_TIMESTAMP or START_SCN. Then the information can be further narrowed down by TABLE_OWNER. Alternatively, the XID, if known, can be queried to identify the exact transaction, but that was not an option in this example.

20. How can you protect the amount of time you can query information from the Flashback Transaction Query?
A. Add UNDO GUARANTEE to the UNDO tablespace.
B. Add RETENTION GUARANTEE to the UNDO tablespace.
C. Add RETENTION GUARANTEE to the Recycle Bin logical storage container.
D. Add UNDO GUARANTEE to the Recycle Bin logical storage container.
---
Ans: B.
Adding RETENTION GUARANTEE to the UNDO tablespace or during database creation to the UNDO tablespace will protect unexpired undo data in the UNDO tablespace. This is where the Flashback Transaction Query retrieves information about transaction changes to rows in the database.