How to delete table and release table space in oracle

In the Oracle database, when we delete a table, the table space associated with it will not be automatically released. This will cause a large amount of junk data to appear in the database, thus wasting storage space. Therefore, after deleting the table, we need to manually release the occupied table space. Next, this article will introduce how to delete tables and release the corresponding table space in the Oracle database.

1. Two ways to delete a table

In Oracle database, we can use two ways to delete a table:

1. DROP TABLE statement

Use the DROP TABLE statement to completely delete a table. The syntax is as follows:

DROP TABLE table_name;

Among them, table_name is the name of the table that needs to be deleted. It should be noted that when deleting a table, you need to have ownership of the table or have DROP ANY TABLE permission.

2. TRUNCATE TABLE statement

The TRUNCATE TABLE statement can quickly delete all data in a table, but does not delete the table structure. Its syntax is as follows:

TRUNCATE TABLE table_name;

It should be noted that the TRUNCATE TABLE statement can only delete the data of the entire table, but not part of the data in the table. Compared with the DROP TABLE statement, using the TRUNCATE TABLE statement to delete data is faster and safer.

2. Delete the table and release the table space

No matter you use the DROP TABLE statement or the TRUNCATE TABLE statement, the corresponding table space will not be automatically released. Therefore, we need to manually release the table space occupied after deleting the table. The specific method is as follows:

1. Check the table space used by the table

Before deleting the table, we need to check the table space used by the target table. We can query the storage information of the table through the following statement:

SELECT table_name, tablespace_name, bytes FROM user_segments WHERE segment_type = 'TABLE' AND table_name = 'TABLE_NAME';

Among them, table_name is the name of the table to be queried. The query results will display the name and size of the table space occupied by the table.

2. Delete table

Before deleting the table, we need to back up the data for future recovery. When deleting a table, we can use the DROP TABLE statement or the TRUNCATE TABLE statement. See above for specific methods.

3. Release table space

After deleting the table, we need to manually release the table space related to the table. We can release the table space through the following statement:

ALTER TABLESPACE tablespace_name COALESCE;

where tablespace_name is the name of the table space that needs to be released. The COALESCE keyword is used to reorganize unused table space fragments into larger contiguous space, thereby freeing up the table space.

It should be noted that if the table space is shared by multiple tables, releasing the table space may cause the performance of other tables to decrease. Therefore, before releasing the table space, it is best to back up the table space for future use. recover.

3. Summary

In Oracle database, deleting tables and releasing table space is one of the important operations to maintain database performance and storage space. This article introduces how to use the DROP TABLE statement or TRUNCATE TABLE statement to delete a table, and demonstrates how to manually release the corresponding table space through examples. When performing these operations, caution is required to avoid unnecessary damage to the database.

The above is the detailed content of How to delete table and release table space in oracle. For more information, please follow other related articles on the PHP Chinese website!