You executed a DROP USER CASCADE on an Oracle 11g release 1 database and immediately
realized that you forgot to copy the OCA.EXAM_RESULTS table to the OCP schema.
The RECYCLE_BIN enabled before the DROP USER was executed and the OCP user has been
granted the FLASHBACK ANY TABLE system privilege.
What is the quickest way to recover the contents of the OCA.EXAM_RESULTS table to the OCP
schema?
A.
Execute FLASHBACK TABLE OCA.EXAM_RESULTS TO BEFORE DROP RENAME TO
OCP.EXAM_RESULTS; connected as SYSTEM.
B.
Recover the table using traditional Tablespace Point In Time Recovery.
C.
Recover the table using Automated Tablespace Point In Time Recovery.
D.
Recovery the table sing Database Point In Time Recovery.
E.
Execute FLASHBACK TABLE OCA.EXAM_RESULTS TO BEFORE DROP RENAME TO
EXAM_RESULTS; connected as the OCP user.
Explanation:
* To flash back a table to an earlier SCN or timestamp, you must have either the FLASHBACK
object privilege on the table or the FLASHBACK ANY TABLE system privilege.
* From question: the OCP user has been granted the FLASHBACK ANY TABLE system privilege.
* Syntax
flashback_table::=
A, and E are WRONG. It hink it’s B
7.4.8.6 Dropping a Tablespace, Cluster, User or Type and the Recycle Bin
When a tablespace is dropped including its contents, the objects in the tablespace are dropped immediately, and not placed in the recycle bin. Any objects in the recycle bin from the dropped tablespace are purged from the recycle bin.
If all objects from a tablespace have been placed in the recycle bin, then dropping the tablespace causes the objects to be purged, even if you do not use the INCLUDING CONTENTS clause with DROP TABLESPACE.
When a user is dropped, any objects belonging to the user that are not in the recycle bin are dropped immediately, not placed in the recycle bin. Any objects in the recycle bin that belonged to the user are purged from the recycle bin.
When you drop a cluster, all tables in the cluster are purged. When you drop a user-defined data type, all objects directly or indirectly dependent upon that type are purged.
Sorry I think it’s C, not B, but I’m not sure 100%
E results in error – ORA-01435: user does not exist
Scope of tspitr does not include dropped users.
Answer would be D.
sorry whay not A???
A and E leads to ORA-01435: user does not exist
B and C impliet usint TABLESPACE point in time recovery or TSPITR cannot recover dropped tablespaces.
The only answer is D which works any time.
This is a 11g question (Oracle 11g release1). AE ware wrong in 11g and 12c.
In 12c there is a new feature: recover tables and table partition without affecting the running database/instance.
RECOVER TABLE HR.PDB_EMP OF PLUGGABLE DATABASE HR_PDB
UNTIL TIME ‘SYSDATE-4’
AUXILIARY DESTINATION ‘/tmp/backups’
REMAP TABLE ‘HR’.’PDB_EMP’:’EMP_RECVR’;
RECOVER TABLE SCOTT.EMP, SCOTT.DEPT
UNTIL TIME ‘SYSDATE-1’
AUXILIARY DESTINATION ‘/tmp/oracle/recover’
DATAPUMP DESTINATION ‘/tmp/recover/dumpfiles’
DUMP FILE ’emp_dept_exp_dump.dat’
NOTABLEIMPORT;
C
Recovery Manager (RMAN) automatic tablespace point-in-time recovery (commonly abbreviated TSPITR) enables you to quickly recover one or more tablespaces in an Oracle database to an earlier time, without affecting the state of the rest of the tablespaces and other objects in the database.
12c table recover tech are implemented by tspitr in fact.
E is the Correct Answer.
RECYCLE bin and FLASHBACK are features available in 10g and 11g
https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables011.htm#ADMIN11681
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9012.htm
Sorry – A, E does not work , As USER IS DROPPED. We cannot use FLASHBACK COMMAND
So Best answer is C
C