Managing Tables   «Prev  Next»

Lesson 8Deleting a table
ObjectiveProcess involved with deleting a table.

Deleting or Dropping Oracle table

When you want to get rid of a table, you can drop it from the database. You can delete a table from an Oracle database by using the SQL syntax found in the MouseOver below:

SQL Drop Table 
DROP TABLE tablename
  1. The required DROP TABLE keywords begin the DDL statement which deletes a table and all associated database objects.
  2. The tablename must identify an existing table.

Dropping Table
Oracle Database 12c

Characters of DROP TABLE Command

The DROP TABLE command has several different effects. It can:
  1. Delete all of the rows in the table
  2. Delete the table structure from the database
  3. Delete any indexes associated with the table from the database
  4. Remove all entries for the table and its components from the data dictionary table

The DROP TABLE command will not affect any other database objects that use the table.
For instance,
  1. views that are built on the table, or
  2. foreign key relationships that use the table, or
  3. stored procedure code that references the table.
If you attempt to access other database objects after you drop a table they depend on, you will get an error message from Oracle.
You can search the appropriate tables in the data dictionary to find out which of these conditions may occur, or use a CASE[1] tool like Oracle Designer to help you understand the impact of dropping a table.

If you only want to delete the data from a table, you can use the syntax of:
DELETE * FROM tablename

wheretablename = the unique name of the table.
As with any SQL statement, the effect of a DROP TABLE command is not made a permanent part of the database until you add a COMMIT command. You can use the ROLLBACK statement to negate the effects of a SQL statement before you issue a COMMIT command. The next lesson is the module wrap-up. You will briefly review the topics covered in this module. You can take a quiz to see what you learned and discover topics that you need to review in a more detailed manner.

[1]CASE: Computer Aided Software Design, a process where the implementation details of application systems are partially determined through computerized automation.