Question 15

Explanation

Correct Statements:

All indexes and constraints defined on the table being dropped are also dropped.

• When a table is dropped, all dependent objects like indexes, constraints, triggers, and privileges are also dropped automatically.

The EMPLOYEES table may be moved to the recycle bin.

• If the recycle bin is enabled (which it is by default in most Oracle setups), then the table is not immediately removed but renamed and stored in the recycle bin. You can recover it using FLASHBACK TABLE.

If there is an uncommitted transaction in the session, it is committed.

• In Oracle, a DROP TABLE is a DDL statement, and all DDLs perform an implicit commit before and after the operation. Any uncommitted transactions are automatically committed.

Incorrect Statements:

Sequences used in the EMPLOYEES table become invalid.

• Sequences are independent objects and are not tied to the table. Dropping a table does not affect the sequences used by it.

The space used by the EMPLOYEES table is always reclaimed immediately.

• If the table goes to the recycle bin, the space is not immediately reclaimed. It is only reclaimed when the table is purged from the recycle bin.

The EMPLOYEES table can be recovered using the ROLLBACK command.

• DDL operations cannot be rolled back.

• ROLLBACK only works for DML (like INSERT, UPDATE, DELETE) — not for DROP.

Use the DROP TABLE statement to move a table or object table to the recycle bin or to remove the table and all its data from the database entirely.

Unless you specify the PURGE clause, the DROP TABLE statement does not result in space being released back to the tablespace for use by other objects, and the space continues to count toward the user's space quota.

For an external table, this statement removes only the table metadata in the database. It has no affect on the actual data, which resides outside of the database.

When you drop a table that is part of a cluster, the table is moved to the recycle bin. However, if you subsequently drop the cluster, the table is purged from the recycle bin and can no longer be recovered with a FLASHBACK TABLE operation.

Dropping a table invalidates dependent objects and removes object privileges on the table. All indexes and constraints defined on the table being dropped are also dropped.

If you want to re-create the table, then you must regrant object privileges on the table, re-create the indexes, integrity constraints, and triggers for the table, and respecify its storage parameters. Truncating has none of these effects. Therefore, removing rows with the TRUNCATE statement can be more efficient than dropping and re-creating a table.

Statements That Cause an Implicit Commit

The statements listed in this section (and any synonyms for them) implicitly end any transaction active in the current session, as if you had done a COMMIT before executing the statement. If there is an uncommitted transaction in the session, it is committed upon execution of DROP TABLE.

These statements also cause an implicit commit after executing. The intent is to handle each such statement in its own special transaction because it cannot be rolled back anyway.

Data definition language (DDL) statements that define or modify database objects.

ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME, ALTER EVENT, ALTER PROCEDURE, ALTER SERVER, ALTER TABLE, ALTER VIEW, CREATE DATABASE, CREATE EVENT, CREATE INDEX, CREATE PROCEDURE, CREATE SERVER, CREATE TABLE, CREATE TRIGGER, CREATE VIEW, DROP DATABASE, DROP EVENT, DROP INDEX, DROP PROCEDURE, DROP SERVER, DROP TABLE, DROP TRIGGER, DROP VIEW, RENAME TABLE, TRUNCATE TABLE.

ALTER FUNCTION, CREATE FUNCTION and DROP FUNCTION also cause an implicit commit when used with stored functions, but not with user-defined functions.