79800

Dropping a Table

For thc purpose of lcarning about Flashback Drop, you will crcatc a ncw table named reg_histand then drop it. The database places the table in the recycle bin so that it can be retricvcd with thc Flashback Drop featurc.

To create and then drop a table:

1.    Connect SQL*Plus to the hrschema.

Notę: For a table to be recovcrablc using Flashback Drop, it must reside in a locally managed tablespace. Also, you cannot recover tables in the SYSTEM tablespaces with Flashback Drop, regardless of thc tablespace type.

Performing User-Directed Recovery Performing Backup and Recovery 9-37

2.    Create table reg_histbased on the existing REGIONS table in the hrschema by using the fol łowi ng command:

CREATE TABLE reg_hist as SELECT * FROM REGIONS;

3.    Drop the reg_histtable using the following command:

DROP TABLE REG_HIST;

Because Flashback is enabled for the database, the dropped table is stored in the recycle bin.

4.    Display the tables in the hrschema.

SELECT * FROM TAB;

TNAME TABTYPE CLUSTERID

BIN$ANbliLHaSiu02xI+zbvDvQ==$0 TABLE

COUNTRIES TABLE

DEPARTMENTS TABLE

EMPLOYEES TABLE

EMP_DETAILS_VIEW VIEW

JOBS TABLE

JOB_HISTORY TABLE

LOCATIONS TABLE

REGIONS TABLE

9 rows selected.

The first namc displayed in the command output. beginning with ’BIN\ is thc table that you just dropped. Because Flashback Database is enabled, the deleted table is still in the recycle bin and is therefore displayed in the command output.