MySQL drop table and purge

First, DROP table

Execute DROP TABLE XX statement

The drop table is placed in the Recycle Bin (user_recyclebin) instead of being deleted directly. In this way, the table information in the Recycle Bin can be restored or completely erased.

Retrieve the deleted table information by querying the Recycle Bin User_recyclebin, 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, and the data in the table is not lost.

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

Clearing the information in the Recycle Bin

Clear the specified table: Purge table <table_name>;

Clear the current user's Recycle Bin: Purge RecycleBin;

Clear the Recycle Bin for all users: Purge Dba_recyclebin;

Do not put in the Recycle Bin, the direct deletion is: drop table xx purge;

Examples are as follows:

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

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

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

Bin$vqwemdg4r9mk9fyjndyzvg==$0 TEST2 DROP TABLE

Sql> Flashback table Test2 to before drop rename to test3;--' to test3 ' renaming tables

Done

Sql> select * from Test3;

A B C

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

11 5

11 10

2 rows selected

Sql> SELECT * from Test2

ORA-00942: Table or view does not exist

--Delete the table completely

sql> drop table Test3 purge;

Table dropped

Second, clear the data in the table

The truncate operation is very similar to the delete operation without a where condition, except that all the information in the table is deleted, but the tables still exist.

Example: Truncate TABLE XX

Truncate does not support rollback and cannot truncate a table with a foreign key, if you want to remove the foreign key first, and then delete it.

After TRUNCATE TABLE, it is possible that the tablespace is still not released and can use the following statement:

ALTER TABLE name deallocate UNUSED KEEP 0;

Note that if you do not add keep 0, the tablespace will not be freed.

For example:

ALTER TABLE F_MINUTE_TD_NET_FHO_B7 deallocate UNUSED KEEP 0;

Or:

TRUNCATE Table (Schema) table_name DROP (reuse) storage to release the tablespace.

For example: Truncate TABLE test1 DROP STORAGE;

Iii. What partitions are queried for partition tables:

Query the partition table, you can query in User_tab_partitions. For example:

Select ' ALTER TABLE ' | | T.table_name | | ' truncate PARTITION ' | | T.partition_name from User_tab_partitions t where t.table_name like ' f_% '

Clears the partition data for the specified partition table:

ALTER TABLE name truncate partition partition name;

Four, clear the partition table occupies the space:

ALTER TABLE name DROP partition partition name;

For example:

ALTER TABLE F_hour_td_net_mpvoice DROP partition p_09121913;

V. Querying table space Information

You can use the following statements to query the usage of each table in storage space:

SELECT Tablespace_name,to_char (SUM (BYTES)/(1024*1024), ' 999g999d999 ') cnt_mb from dba_extents WHERE owner= ' & OWNER ' and segment_name= ' &table_name ' and segment_type like ' table% ' GROUP by Tablespace_name;

You can query the storage space using the following statement:

Select tablespace_name, Sum (bytes)/1024/1024 from Dba_segments GROUP by Tablespace_name

Vi. querying the table under the user

If your user rights are not DBA:

Then you use

SELECT * from User_tables;

You can query the tables that are owned by the current user.

If you are a DBA user:

SELECT * from Dba_tables;

MySQL drop table and purge