You issue the following command to drop the PRODUCTS table:
SQL>DROP TABLE products;
What is the implication of this command? (Choose all that apply.)
A.
All data in the table are deleted but the table structure will remain
B.
All data along with the table structure is deleted
C.
All views and synonyms will remain but they are invalidated
D.
The pending transaction in the session is committed
E.
All indexes on the table will remain but they are invalidated
From official website:
table
Specify the name of the table to be dropped. OracleDatabase automatically performs
the following operations:
■ All rows from the table are dropped.
■ All table indexes and domain indexesare dropped, as well as any triggers defined
on the table, regardless of who created them or whoseschema contains them. If
table is partitioned, then any corresponding local indexpartitions are also
dropped.
If the table is a base table for a view, a container or master table of a materialized
view, or if it is referenced in a stored procedure,function, or package, then the
database invalidates these dependent objects but does not drop them. You cannot
use these objects unless you re-create the table or dropand re-create the objects so
that they no longer depend on the table.
drop table products; — without purge clause
— move table (table structure & its data) to the recycle bin.
— Constraints and indexes are dropped too.
— Dependent objects like view, sequence … becomes invalid.
— Auto commit cause it is a DDL statement.
answer is ABD
BCD are correct. Drop and truncate are different effect.
Difference between DROP and TRUNCATE Statement:
If a table is dropped, all the relationships with other tables will no longer be valid, the integrity constraints will be dropped, grant or access privileges on the table will also be dropped, if want use the table again it has to be recreated with the integrity constraints, access privileges and the relationships with other tables should be established again. But, if a table is truncated, the table structure remains the same, therefore any of the above problems will not exist.
AC are correct.
B) invalid. drop does not remove structure
D) drop is not ddl cmd so not committed. and can be rolled back
E) indexes r dropped
DROP is definitely a DDL command.
Drop removes the structure alongside all the data in the other side truncate remove the data and finally drop have a implicit commit
Drop table is a DDL Statement => have an implicit commit statement inside.
When you see a DDL or DCL statement, it’s equivalent to DDL; commit; or DCL; commit;
Correct Answer:
B. All data along with the table structure is deleted
C. All views and synonyms will remain but they are invalidated
D. The pending transaction in the session is committed
yes BCD are correct
I’ve read the oracle doc and the said that: drop table (without purge) just move table to the recyble bin. I’m wondering table in this case just contain table structure with out it’s data or both.
At first, I just choose C and D.