Database Tutorial

Course Tutorial Site

Site Admin

C – DROP Statement

without comments

You can use the DROP statement to remove the definition of objects, tables, views, sequences, types, functions, procedures, and sequences. These examples demonstrate how to drop tables, sequences, and views.

DROP TABLE Statement

The following demonstrates removing the table structure and naturally any data from an Oracle database. It only works when there aren’t any FOREIGN KEY dependents in the database.

DROP TABLE military_user;

When you want to drop a table notwithstanding dependent tables, you append the CASCADE CONSTRAINTS clause. This is shown in the following example:

DROP TABLE military_user CASCADE CONSTRAINTS;

You can also drop a series of tables with a single PL/SQL anonymous block program unit. Inside the program unit, you call the DROP statement as many times as necessary. This is typically done in a loop. This is also how you drop tables contingent on whether or not they exist. The following shows you how to call the DROP TABLE statement inside a loop structure.

1
2
3
4
5
6
7
8
9
10
11
12
13

BEGIN
  FOR i IN (SELECT TABLE_NAME
            FROM   user_tables
            WHERE  TABLE_NAME IN ('MILITARY_USER','MILITARY_LOOKUP')) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT sequence_name
            FROM   user_sequences
            WHERE  sequence_name IN ('MILITARY_USER_S1','MILITARY_LOOKUP_S1')) LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/

Oracle does not support rollback activity when you drop a table. All DDL commands, like the DROP statement are considered final. They don’t log data being removed like the DELETE command. When you drop a table, the data is lost unless it is also stored on a backup. Oracle does supports Flashback technology, which changes the DROP model because can enable recovery of the structure and data.

Written by michaelmclaughlin

January 17th, 2018 at 1:45 am

Posted in