The basic syntax for the DROP TABLE statement is:
DROP TABLE table_name;
For example: DROP TABLE XX_supplier;
This would drop table called XX_supplier.
Sometimes objects are associated with a table that exists in a database along with the table. These objects may include indexes, constraints, and triggers. If the table is dropped, Oracle automatically drops any index, trigger, or constraint associated with the table as well. Here are two other factors to be aware of with respect to dropping tables:
- You cannot roll back a drop table command.
- To drop a table, the table must be part of your own schema, or you must have the drop any table privilege granted to you.
Let’s move on to discuss how you can remove all data from a table quickly using a special option available in Oracle. In this situation, the DBA or developer may use the truncate table statement. This statement is a part of the data definition language (DDL) of Oracle, much like the create table statement and completely unlike the delete statement. Truncating a table removes all row data from a table quickly, while leaving the definition of the table intact, including the definition of constraints and any associated database objects such as indexes, constraints, and triggers on the table. The truncate statement is a high-speed data-deletion statement that bypasses the transaction controls available in Oracle for recoverability in data changes. Truncating a table is almost always faster than executing the delete statement without a where clause, but once this operation has been completed, the data cannot be recovered unless you have a backed-up copy of the data. Here’s an example:
SQL> truncate table tester;