When You a Drop a Table in Sql and Run It Again

Drib TABLE

Purpose

Use the Drib Table statement to motion a table or object tabular array to the recycle bin or to remove the table and all its data from the database entirely.

Caution:

Unless you lot specify the PURGE clause, the DROP Tabular array statement does not upshot in space existence released back to the tablespace for use by other objects, and the space continues to count toward the user's space quota.

For an external table, this statement removes only the table metadata in the database. It has no touch on on the actual data, which resides exterior of the database.

When you drop a table that is part of a cluster, the table is moved to the recycle bin. However, if you subsequently drop the cluster, the tabular array is purged from the recycle bin and tin can no longer be recovered with a FLASHBACK Table operation.

Dropping a tabular array invalidates dependent objects and removes object privileges on the tabular array. If you want to copy the table, then y'all must regrant object privileges on the table, re-create the indexes, integrity constraints, and triggers for the table, and respecify its storage parameters. Truncating has none of these effects. Therefore, removing rows with the TRUNCATE statement tin can be more than efficient than dropping and re-creating a table.

Prerequisites

The table must exist in your own schema or you must have the DROP Whatsoever Table organization privilege.

You tin can perform DDL operations (such equally ALTER TABLE, DROP Table, CREATE Alphabetize) on a temporary tabular array simply when no session is bound to information technology. A session becomes bound to a temporary table past performing an INSERT operation on the table. A session becomes unbound to the temporary table by issuing a TRUNCATE statement or at session termination, or, for a transaction-specific temporary tabular array, by issuing a COMMIT or ROLLBACK statement.

Syntax

drop_table::=

Description of the illustration drop_table.gif

Semantics

schema

Specify the schema containing the tabular array. If you lot omit schema , then Oracle Database assumes the table is in your ain schema.

table

Specify the name of the tabular array to be dropped. Oracle Database automatically performs the following operations:

• All rows from the tabular array are dropped.

• All table indexes and domain indexes are dropped, as well as any triggers defined on the table, regardless of who created them or whose schema contains them. If table is partitioned, and so any corresponding local index partitions are besides dropped.

• All the storage tables of nested tables and LOBs of table are dropped.

• When you lot driblet a range-, hash-, or list-partitioned tabular array, so the database drops all the tabular array partitions. If you lot drop a blended-partitioned tabular array, and then all the partitions and subpartitions are also dropped.

• When you drop a partitioned table with the PURGE keyword, the statement executes as a series of subtransactions, each of which drops a subset of partitions or subpartitions and their metadata. This segmentation of the drop operation into subtransactions optimizes the processing of internal arrangement resource consumption (for example, the library cache), particularly for the dropping of very large partitioned tables. As before long as the start subtransaction commits, the table is marked UNUSABLE. If any of the subtransactions fails, the but operation immune on the table is another DROP TABLE ... PURGE statement. Such a statement volition resume work from where the previous DROP TABLE statement failed, assuming that you have corrected whatsoever errors that the previous operation encountered.

  You can list the tables marked UNUSABLE by such a drop operation by querying the condition cavalcade of the *_TABLES, *_PART_TABLES, *_ALL_TABLES, or *_OBJECT_TABLES data dictionary views, every bit appropriate.

• For an index-organized table, any mapping tables divers on the index-organized table are dropped.

• For a domain alphabetize, the appropriate drop routines are invoked. Please refer to Oracle Data Cartridge Developer's Guide for more data on these routines.

• If any statistic types are associated with the tabular array, and so the database disassociates the statistics types with the Force clause and removes any user-divers statistics nerveless with the statistics type.

• If the table is not part of a cluster, and so the database returns all data blocks allocated to the table and its indexes to the tablespaces containing the tabular array and its indexes.

  To drib a cluster and all its the tables, utilize the DROP CLUSTER statement with the INCLUDING TABLES clause to avoid dropping each table individually. Run into Drop CLUSTER.

• If the table is a base table for a view, a container or primary tabular array of a materialized view, or if it is referenced in a stored procedure, function, or bundle, and so the database invalidates these dependent objects but does not driblet them. You cannot use these objects unless you lot re-create the table or driblet and re-create the objects so that they no longer depend on the table.

  If y'all cull to re-create the table, then it must incorporate all the columns selected past the subqueries originally used to define the materialized views and all the columns referenced in the stored procedures, functions, or packages. Any users previously granted object privileges on the views, stored procedures, functions, or packages demand not be regranted these privileges.

  If the table is a principal table for a materialized view, then the materialized view tin can still be queried, but information technology cannot be refreshed unless the table is re-created and so that it contains all the columns selected past the defining query of the materialized view.

  If the table has a materialized view log, then the database drops this log and whatever other directly-path INSERT refresh information associated with the table.

Restriction on Dropping TablesY'all cannot directly drib the storage tabular array of a nested table. Instead, you must drib the nested tabular array column using the Modify TABLE ... Drop COLUMN clause.

CASCADE CONSTRAINTS

Specify Pour CONSTRAINTS to drop all referential integrity constraints that refer to principal and unique keys in the dropped tabular array. If you lot omit this clause, and such referential integrity constraints exist, then the database returns an mistake and does non driblet the table.

PURGE

Specify PURGE if you want to drop the tabular array and release the infinite associated with it in a unmarried step. If you specify PURGE, so the database does not place the tabular array and its dependent objects into the recycle bin.

Caution:

You lot cannot scroll dorsum a DROP TABLE statement with the PURGE clause, nor can you recover the table if you have dropped information technology with the PURGE clause.

Using this clause is equivalent to first dropping the table and then purging it from the recycle bin. This clause lets you lot save one pace in the process. It also provides enhanced security if y'all want to forbid sensitive material from appearing in the recycle bin.

Example

Dropping a Table: CaseThe following statement drops the oe.list_customers table created in "List Partition Example".

Drop TABLE list_customers PURGE;        

schmidtyouttleste.blogspot.com

Source: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9003.htm

Share this post