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::=
E. is incorrect because FLASHBACK..BEFORE DROP uses the Recycle Bin which gets purged for a user when the user is dropped.
the question states “The RECYCLE_BIN enabled” not purge recycle_bin
however also When you drop a user, Oracle Database also purges all of that user’s schema objects from the recycle bin.
E is correct test it! if drop table purge statement is not used then drop table goes into recycle bin if it is enabled
Thnx but there wasn’t drop table before drop user cascade.
Only drop user cascade…
Answer is C. When you drop a user, Oracle Database also purges all of that user’s schema objects from the recycle bin.
Shawn is correct, but answer is B: “Oracle 11g release 1” “Automated Tablespace Point In Time Recovery.” >= 12.1 🙂
Hi,
Since this comment made me check it out and lose some time in the process I will save future users from this by correcting you: Auto TSPITR is also availabale to 11g. I think you confused it with Automatic Table PITR of 12c.
Regards.
May be am I wrong but, even if Automated TABLE PITR (TABLE not TABLESPACE) is new with Oracle 12.1, automatic TABLESPACE PITR (TABLESPACE not TABLE) already exists in 10g…
http://docs.oracle.com/cd/B19306_01/backup.102/b14191/rcmtspit.htm#BRADV009
C is correct.
When you drop a user cascade option, it not fill the recycle bin, and if the user have objects on recycle bin those are purged too.
When you drop a table, the table goes to recycle bien, but is differente when drop user cascade was issued.
B is an option, but the question says “quickly”.
Then C is correct. Please refer to:
http://docs.oracle.com/cd/B10501_01/server.920/a96566/rcmtspit.htm
The question assumes that table still exists in recycle bin (not considering that table also purged when dropped user objects in recycle bin also gone when cascade option was used). The examiner may treat correct answer as E based on that assumption although C may be the best answer.
Hey Rafiq what is this interpretation ??! The examiner cannot go against Oracle behavior !! Oracle product works like this : if you use CASCADE when dropping a schema, then the recycle bin of the user is dropped as well.
You are not in the examiner’s head. And if Oracle would consider that the table still exists into the recycle bin, the question wouldn’t use the CASCADE clause here.
If the CASCADE claused has been explicitely used in this question, so the RECYCLE BIN does not exist anymore and the single solution to recover data from the table is to use a TABLESPACE PITR in 11g or a TABLE PITR in 12c, that’s all…
E
I do not think E makes sense, the user OCP has been dropped, so i think it is not possible to execute flashback as the ocp user?
user ocA is dropeed not ocP
C – OK
RMAN TSPITR is most useful for the following situations:
You want to recover a table after it has been dropped with the PURGE option.
When you drop a user, Oracle Database also purges all of that user’s schema objects from the recycle bin.
http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmtspit.htm#BRADV89796
“E” is not correct in Oracle 11.1: “When you drop a user, Oracle Database also purges all of that user’s schema objects from the recycle bin”
http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9008.htm#SQLRF01811
definitely C
C
C
c
Since the statement “DROP USER OCA CASCADE;” drops the user and its objects including the recycle bin, A, E are ruled out. I have tested this. Once the user is dropped with cascade, the sys cannot flashback the oca.table, nor the system user. You cannot login as OCA as there is no user called OCA. It had been dropped. The user OCA is dead. Hence A and E are ruled out.
Choice D can be ruled out as you don’t need to recover a database to recover a dropped table.
Now the Choice is between B and C.
In 12c, RMAN has a nifty new feature called table recovery, which lets you recover a table or a table partition to a past time using backups, thus providing a significant alternative to the traditional TSPITR techniques.
Normally, using a flashback feature (such as flashback table before drop) is a smart thing to do, when you need to recover a table. However, you can’t flashback table data when you purge a table’s data because the flashback drop feature won’t work after all the data is permanently purged from the table. Similarly, you can’t employ the flashback table feature following a DDL command (such as truncate table, drop user, drop table, etc), or when you need to go back beyond the available undo because this feature depends on the availability of undo data.
TSPITR is not very handy when you need to recover a small set of tables to a past point in time, because the method requires that you move all the contents of a tablespace to a past point in time. The ability to recover a table is useful, when you need to recover accidentally dropped or logically corrupted tables. This feature to recover a table easily is available in 12c. (Ref: 12c New Features, Sam Alapati)
The question pertains to oracle 11g and not 12c. Therefore we can eliminate choice C. Hence the answer is B.
rsv1999’s answer makes more sense, it says “11g” is used
Sorry Automated Tablespace PITR is available even in 10g, so I’ll stay with C
c.
the user oca is dropped;
SQL> flashback table oca.exam_results to before drop rename to exam_results;
flashback table oca.exam_results to before drop rename to exam_results
*
ERROR at line 1:
ORA-01435: user does not exist
C makes sense if the tablespace contains only objects from the dropped user. Tricky question
C is the correct response.
11g CAN perform automated TSPITR: https://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmtspit.htm#i1035361
B is feasible too, but C is faster, as the question requests.
Correct Answer is B.
There are no logs generated in case of drop user cascade.
only one option, tablespace point in time recovery to get the table.
It says “You executed a DROP USER CASCADE on an Oracle 11g ,”
but You did NOT executed DROP USER OCA CASCADE on an Oracle 11g , so OCA user and OCA.EXAM_RESULTS table had never been dropped.
Its C
Hi, i have passed my Oracle 1Z0-060 exam last week, score of my 1Z0-060 exam: Section 1 = 90%; Section 2 = 92% (the passing score: Section 1 = 64%; Section 2 = 65%, both sections must be passed to pass 1Z0-060 exam). All 85 questions (Section 1 = 51q; Section 2 = 34q) were multiple choice questions, most of them were easy to answer, but few questions on Core Administration and Performance Management were a little difficult.
And, I learned valid 1Z0-060 dumps here: https://drive.google.com/open?id=0B-ob6L_QjGLpNjQta2dQSmFJLWs (recommend to get FULL VERSION 1Z0-060 VCE dumps and PDF dumps for learning).
Good Luck!