Oracle SQL for clearing data and releasing tablespaces

A few days ago, when the database space was full, it had increased to a maximum of 32 GB for a single storage file. However, a lot of methods are used to release the tablespace. The main reason is that the system uses a large number of partition tables. To clear data from a partition table, the tablespace is not released. You must drop the partition to release the space. Record the statements learned and used during the current operation:

1. drop table

Run the drop table xx statement.

The drop table is placed in the recycle bin (user_recyclebin) instead of deleted directly. In this way, the table information in the recycle bin can be restored or completely cleared.

Query the user_recyclebin of the recycle bin to obtain the information of the table to be deleted, and then use the statement

Flashback table <user_recyclebin.object_name or user_recyclebin.original_name> to before drop [rename to <new_table_name>];

Restore the table in the recycle bin to the original name or specify a new name. data in the table is not lost.

To completely delete a table, use the following statement: drop table <table_name> purge;

Clear information in the recycle bin

Clear the specified table: purge table <table_name>;

Clear the recycle bin of the current user: purge recyclebin;

Clear the recycle bin of all users: purge dba_recyclebin;

Drop table xx purge;

Example:

========================================================== ========================================================

SQL> select * from test1;

A B C

--------------

11 5

11 10

2 rows selected

SQL> create table test2 as select * from test1;

Table created

SQL> select * from test2;

A B C

--------------

11 5

11 10

2 rows selected

SQL> drop table test2;

Table dropped

SQL> select object_name, original_name, operation, type from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME OPERATION TYPE

1 2 Next Page