Which two statements are true regarding the DELETE and TRUNCATE commands? (Choose two.)
A.
DELETE can be used to remove only rows from only one table at a time.
B.
DELETE can be used to remove only rows from multiple tables at a time.
C.
DELETE can be used only on a table that is a parent of a referential integrity constraint.
D.
DELETE can be used to remove data from specific columns as well as complete rows.
E.
DELETE and TRUNCATE can be used on a table that is a parent of a referential integrity constraint having
ON DELETE rule.
Explanation:
Transactions, consisting of INSERT, UPDATE, and DELETE (or even MERGE) commands can be made
permanent (with a COMMIT) or reversed (with a ROLLBACK). A TRUNCATE command, like any other DDL
command, is immediately permanent: it can never be reversed.
The Transaction Control Statements
A transaction begins implicitly with the first DML statement. There is no command to explicitly start a
transaction. The transaction continues through all subsequent DML statements issued by the session. These
statements can be against any number of tables: a transaction is not restricted to one table. It terminates
(barring any of the events listed in the previous section) when the session issues a COMMIT or ROLLBACK
command. The SAVEPOINT command can be used to set markers that will stage the action of a ROLLBACK,
but the same transaction remains in progress irrespective of the use of SAVEPOINT
Explicit Transaction Control Statements
You can control the logic of transactions by using the COMMIT, SAVEPOINT, and ROLLBACK statements.
Note: You cannot COMMIT to a SAVEPOINT. SAVEPOINT is not ANSI-standard SQL.
A. True
B. False
C. False
D. False
E. False- truncate doesn’t work on on delete
TRUNCATE
• TRUNCATE is a DDL command
• TRUNCATE is executed using a table lock and whole table is locked for remove all records.
• We cannot use Where clause with TRUNCATE.
• TRUNCATE removes all rows from a table.
• Minimal logging in transaction log, so it is performance wise faster.
• TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.
• Identify column is reset to its seed value if table contains any identity column.
• To use Truncate on a table you need at least ALTER permission on the table.
• Truncate uses the less transaction space than Delete statement.
• Truncate cannot be used with indexed views.
DELETE
• DELETE is a DML command.
• DELETE is executed using a row lock, each row in the table is locked for deletion.
• We can use where clause with DELETE to filter & delete specific records.
• The DELETE command is used to remove rows from a table based on WHERE condition.
• It maintain the log, so it slower than TRUNCATE.
• The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.
• Identity of column keep DELETE retain the identity.
• To use Delete you need DELETE permission on the table.
• Delete uses the more transaction space than Truncate statement.
• Delete can be used with indexed views.
DROP
• The DROP command removes a table from the database.
• All the tables’ rows, indexes and privileges will also be removed.
• No DML triggers will be fired.
• The operation cannot be rolled back.
• DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command.
• DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.
Question: I read that in 12c there is a “cascade” clause on the truncate table syntax. Can you show an example of truncate table xxx cascade?
Answer: As you might expect from the usage of the cascade clause in other Oracle syntax, the Oracle truncate table xxx cascade will delete remove the rows of the target table, plus all rows from “child” tables, as defined by foreign key constraints.
Remember, unlike a delete DML, the truncate table command only lowers the table high water mark and does not physically visit the rows in the the tables.
Note: in order to use the truncate table xxx cascade syntax, you must have the foreign key defined with the “on delete cascade” clause. If you do not have this defined, Oracle will throw a ORA-14705 error message.
ORA-14705: unique or primary keys referenced by enabled foreign keys in table xxx
So, E is True.
Answer:A,E