Examine the command and its output:
SQL> DROP TABLE EMPLOYEE;
SQL> SELECT object_name AS recycle_name, original_name, type FROM recyclebin;
RECYCLE_NAMEORIGINAL_NAMETYPE
————————————————————————————————————–
binsgk31sj/3akk5hg3j21kl5j3d==$0EMPLOYEE TABLE
You then successfully execute the command:
SQL> FLASHBACK TABLE “BINSgk31sj/3akk5hg3j21kl5j3d==$0” TO BEFORE DROP;
Which two statements are true?
A.
It flashes back the employee table and all the constraints associated with the table.
B.
It automatically flashes back all the indexes on the employes table.
C.
It automatically flashes back any triggers defined on the table.
D.
It flashes back only the structure of the table and not the data.
E.
It flashes back the data from the recycle bin and the existing data in the original table is
permanently lost.
A, B and C are correct answers.
D & E are wrong as data is also restored with flashback.
A and C
Before drop not back ALL indexes.
http://www.toadworld.com/platforms/oracle/w/wiki/599.flashback-drop
AC
I think by querying recyclebin, they’re demonstrating that the indexes are gone.
https://docs.oracle.com/database/121/BRADV/rcmflash.htm#BRADV89748
A is not correct : the recycle bin does not preserve referential constraints on a table
B and D are wrong :
When you drop a table, the table and all of its dependent objects go into the recycle bin together. Likewise, when you perform Flashback Drop, the objects are generally all retrieved together. When you restore a table from the recycle bin, dependent objects such as indexes do not get their original names back; they retain their system-generated recycle bin names. Oracle Database retrieves all indexes defined on the table EXCEPT for bitmap join indexes, and all triggers and constraints defined on the table EXCEPT for referential integrity constraints that reference other tables.
C and E are true :
Flashback Table restores tables while automatically maintaining associated attributes such as current indexes, triggers, and constraints, and not requiring you to find and restore application-specific properties.
This is from the link you sent. This confirms A is correct, why would you not choose for A it does not say anything about original names so this has nothing to do with the answer.
Hello all,
I agree with Steve. ” I think by querying recyclebin, they’re demonstrating that the indexes are gone.”
A&C are correct ( although we don’t get back all constraints)
The below is what I think:
D, E & B: incorrect.
——————-
(D) is incorrect because data is also flash back.
(E) The sentence uses TO BEFORE DROP. In this option:”the existing data in the original table” . There is no original table(only the table we want to recuperate). If during flashing back, a new table with same name is created in the same schema, then we will need to rename the table from the recycle bin to a new table_name.
(B) This option does not include the employee table.
——————-
Any other suggestion?
Cheers,
MLC
It should have one option. A
A table and all of its dependent objects (indexes, LOB segments, nested tables, triggers, constraints and so on) go into the recycle bin together, when you drop the table. Likewise, when you perform Flashback Drop, the objects are generally all retrieved together.
A, C are correct. Trigger automatically flashbacked.
http://docs.oracle.com/cd/B19306_01/backup.102/b14192/flashptr004.htm#i1020594
Not B. can’t flashback bitmap index.
Oracle Database retrieves all indexes defined on the table retrieved from the recycle bin except for bitmap join indexes. (Bitmap join indexes are not put in the recycle bin during a DROP TABLE operation, so cannot be retrieved.)
AC or BC
AC if ‘all the constraints’ does not include referential constraints. see last sentence
as you can see there this table does not have an index
not E because this is nonsense as there is no ‘original table’ any more.
not D because it flashes back structure and data
If the retrieved table had referential constraints before it was placed in the recycle bin, then re-create them.
Notes on Flashing Back Dropped Tables The following notes apply to flashing back dropped tables:
Oracle Database retrieves all indexes defined on the table retrieved from the recycle bin except for bitmap join indexes. (Bitmap join indexes are not put in the recycle bin during a DROP TABLE operation, so cannot be retrieved.)
The database also retrieves all triggers and constraints defined on the table except for referential integrity constraints that reference other tables.
The retrieved indexes, triggers, and constraints have recycle bin names. Therefore it is advisable to query the USER_RECYCLEBIN view before issuing a FLASHBACK TABLE … TO BEFORE DROP statement so that you can rename the retrieved triggers and constraints to more usable names.
When you drop a table, all materialized view logs defined on the table are also dropped but are not placed in the recycle bin. Therefore, the materialized view logs cannot be flashed back along with the table.
When you drop a table, any indexes on the table are dropped and put into the recycle bin along with the table. If subsequent space pressures arise, then the database reclaims space from the recycle bin by first purging indexes. In this case, when you flash back the table, you may not get back all of the indexes that were defined on the table.
You cannot flash back a table if it has been purged, either by a user or by Oracle Database as a result of some space reclamation operation.
A and B are not true always. there are some exceptions.
very difficult….
I think A is wrong because not ALL Contraints can be flashed back
B is wrong also because bitmap Indexex cannot flashed back, so C must be correct
but what is the second correct answer ?????
When you drop a table, the table and all of its dependent objects go into the recycle bin together. Likewise, when you perform Flashback Drop, the objects are generally all retrieved together. When you restore a table from the recycle bin, dependent objects such as indexes do not get their original names back; they retain their system-generated recycle bin names. Oracle Database retrieves all indexes defined on the table except for bitmap join indexes, and all triggers and constraints defined on the table except for referential integrity constraints that reference other tables.
I prefer A & C
http://docs.oracle.com/cd/B19306_01/backup.102/b14192/flashptr004.htm
7.4.10 Limitations and Restrictions on Flashback Drop
“A table and all of its dependent objects (indexes, LOB segments, nested tables, triggers, constraints and so on) go into the recycle bin together, when you drop the table. Likewise, when you perform Flashback Drop, the objects are generally all retrieved together.
It is possible, however, that some dependent objects such as indexes may have been reclaimed due to space pressure. In such cases, the reclaimed dependent objects are not retrieved from the recycle bin.”
or:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables011.htm#i1011362
“Restoring Dependent Objects
When you restore a table from the recycle bin, dependent objects such as indexes do not get their original names back; they retain their system-generated recycle bin names. You must manually rename dependent objects if you want to restore their original names. If you plan to manually restore original names for dependent objects, ensure that you make note of each dependent object’s system-generated recycle bin name before you restore the table.”
B C – https://docs.oracle.com/cd/B19306_01/backup.102/b14192/flashptr004.htm
– A table and all of its dependent objects (indexes, LOB segments, nested tables, triggers, constraints and so on) go into the recycle bin together,
when you drop the table. Likewise, when you perform Flashback Drop, the objects are generally all retrieved together.
– The recycle bin does not preserve referential constraints on a table
– the most recently dropped table with that original name is retrieved from the recycle bin, with its original name
I feel this is an badly formed question.
except C all the answers are debatable, I pray I do not get such mal-formed questions in my exam.
Correct A,C
B not correct because we cant flashback bitmap index
Question itself not properly formed.
But we are bound to answer it as approximately as we can.
So i concluded as below
A (Incorrect): Since CASCADE CONSTRAINTS not explicitly mentioned, it is assumed there are no constraints. Had there been any, it would have error out while dropping table.
B (Correct): It will flash back all related indexes (except bitmap join indexes) along with table, but names of flashed back index will be system generated.
C (Correct): It will flash back all triggers associated with the table.
D (Incorrect): It flashes back structure as well as data. Table as logical entity always remains at it’s original place, except change in pointer locating it as dropped. So when it is flashed back only pointer is changed along with re structuring with associated objects.
E (Incorrect): It checks for existence of table with same name before flashing it back. If it finds one, it will error out.
“A (Incorrect): Since CASCADE CONSTRAINTS not explicitly mentioned, it is assumed there are no constraints. Had there been any, it would have error out while dropping table.”
This a very good thouth!!!
But because of this, A should be corrent!!!
For B: there is no way to know if the table has any Bitmap jon idexes!!!
CASCADE CONSTRAINTS
Specify CASCADE CONSTRAINTS to drop all referential integrity constraints that refer to primary and unique keys in the dropped table. If you omit this clause, and such referential integrity constraints exist, then the database returns an error and does not drop the table.
CASCADE CONTRAINTS is used to drop REFERENCIAL CONSTRAINTS
The table could have other constaint types like not null etc.
Howdy I am so grateful I found your blog, I really found you by mistake, while I was looking on Bing for something else, Regardless I am here now and would just like to say many thanks for a tremendous post and a all round exciting blog (I also love the theme/design), I don’t have time to read it all at the minute but I have book-marked it and also added in your RSS feeds, so when I have time I will be back to read much more, Please do keep up the awesome job.|
Hi There,
A table and all of its dependent objects (indexes, LOB segments, nested tables, triggers, constraints and so on) go into the recycle bin together, when you drop the table. Likewise, when you perform Flashback Drop, the objects are generally all retrieved together.
Oracle Database retrieves all indexes defined on the table retrieved from the recycle bin except for bitmap join indexes. (Bitmap join indexes are not put in the recycle bin during a DROP TABLE operation, so cannot be retrieved.)
The database also retrieves all triggers and constraints defined on the table except for referential integrity constraints that reference other tables.
Conclusion :
DROP TABLE EMPLOYEE; statement would have been failed with an error if we have any referntial constraints defined.
So the table doesn’t have any referential constraints.
Option A is correct and as per the oracle docs option C also correct as It automatically flashes back any triggers defined on the table .
We don’t know if we have any Bimap Join indexes created on the table EMPLOYEE so we can’t conclude the Option B as correct .
In my openion the answer is A , C .
A is wrong
If the retrieved table had referential constraints before it was placed in the recycle bin, then re-create them.
This step must be performed manually because the recycle bin does not preserve referential constraints on a table.
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admqs/performing-backup-and-recovery.html#GUID-5CC5F46F-2D15-4333-BC1A-2626046EA488
AC