Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
SQL Statements:
SAVEPOINT to UPDATE, 8 of 9
Use the TRUNCATE
statement to remove all rows from a table or cluster and reset the STORAGE
parameters to the values when the table or cluster was created.
Removing rows with the TRUNCATE
statement can be more efficient than dropping and re-creating a table. Dropping and re-creating a table invalidates the table's dependent objects, requires you to regrant object privileges on the table, and requires you to re-create the table's indexes, integrity constraints, and triggers and respecify its storage parameters. Truncating has none of these effects.
To truncate a table or cluster, the table or cluster must be in your schema or you must have DROP
ANY
TABLE
system privilege.
truncate::=
truncate
Specify the schema and name of the table to be truncated. This table cannot be part of a cluster. If you omit schema, Oracle assumes the table is in your own cluster.
NEXT
storage parameter of table
to be the size of the last extent deleted from the segment in the process of truncation.
UNUSABLE
indicators for the following indexes on table: range and hash partitions of local indexes and subpartitions of local indexes.
UNUSABLE
all nonpartitioned indexes and all partitions of global partitioned indexes on the table.
Restrictions:
IN_PROGRESS
.
The MATERIALIZED
VIEW
LOG
clause lets you specify whether a materialized view log defined on the table is to be preserved or purged when the table is truncated. This clause permits materialized view master tables to be reorganized through export/import without affecting the ability of primary-key materialized views defined on the master to be fast refreshed. To support continued fast refresh of primary-key materialized views, the materialized view log must record primary-key information.
Specify PRESERVE
if any materialized view log should be preserved when the master table is truncated. This is the default.
Specify PURGE
if any materialized view log should be purged when the master table is truncated.
See Also:
Oracle9i Replication for more information about materialized view logs and the |
Specify the schema and name of the cluster to be truncated. You can truncate only an indexed cluster, not a hash cluster. If you omit schema, Oracle assumes the cluster is in your own schema.
When you truncate a cluster, Oracle also automatically deletes all data in the indexes of the cluster tables.
The STORAGE
clauses let you determine what happens to the space freed by the truncated rows. The DROP
STORAGE
clause and REUSE
STORAGE
clause also apply to the space freed by the data deleted from associated indexes.
Specify DROP
STORAGE
to deallocate all space from the deleted rows from the table or cluster except the space allocated by the MINEXTENTS
parameter of the table or cluster. This space can subsequently be used by other objects in the tablespace. This is the default.
Specify REUSE
STORAGE
to retain the space from the deleted rows allocated to the table or cluster. Storage values are not reset to the values when the table or cluster was created. This space can subsequently be used only by new data in the table or cluster resulting from insert or update operations.
The following statement removes all rows from the employees
table and returns the freed space to the tablespace containing employees
:
TRUNCATE TABLE employees;
The above statement also removes all data from all indexes on employees
and returns the freed space to the tablespaces containing them.
The following statement removes all rows from all tables in the personnel
cluster, but leaves the freed space allocated to the tables:
TRUNCATE CLUSTER personnel REUSE STORAGE
The above statement also removes all data from all indexes on the tables in the personnel
cluster.
The following statements are examples of truncate statements that preserve materialized view logs:
TRUNCATE TABLE sales PRESERVE MATERIALIZED VIEW LOG; TRUNCATE TABLE orders;
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|