How to delete tables in Oracle to release table space

In Oracle database, deleting a table is a very common operation, but deleting a table does not necessarily release the corresponding table space immediately, which will lead to a waste of database space. Therefore, when deleting a table, freeing the table space is also a very important task. This article will introduce readers to how to delete tables and release the corresponding table space in Oracle database.

1. Delete table

In Oracle database, the operation of deleting a table can be achieved through SQL statements. The specific syntax is as follows:

DROP TABLE 表名;

Among them, table The name is the name of the table that needs to be deleted.

2. Check the usage of the table space

Before deleting the table, we need to check the usage of the table space to ensure that the table space can be released in time after deletion.

We can use the following statement to view the usage of table space:

SELECT TABLESPACE_NAME, FILE_NAME, ROUND(SUM(BYTES)/(1024*1024),2) AS "SIZE (MB)", AUTOEXTENSIBLE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME, FILE_NAME, AUTOEXTENSIBLE;

Among them, DBA_DATA_FILES is the Oracle system table, which contains all data file information. This query statement can query the data file name corresponding to each table space, the occupied size and whether automatic expansion is allowed. If the data file corresponding to a certain table space allows automatic expansion, it may be reoccupied after the table is deleted.

3. Clear the table

Before performing the table deletion operation, we can choose to clear the data in the table, which can effectively reduce the use of table space.

We can use the following statement to clear the table data:

TRUNCATE TABLE 表名;

Among them, the table name is the name of the table that needs to clear the data.

It should be noted that the TRUNCATE operation is a DDL statement that will clear the data of the entire table, not rows one by one. This means that the operation is very fast because it does not log the deletion of each row. However, we need to be careful with this operation because data that has been wiped cannot be recovered.

4. Delete the table and release the table space

After clearing the table data, we can use the following statement to delete the table and release the corresponding table space:

DROP TABLE 表名 PURGE;

Among them, the PURGE keyword indicates immediate release of the table space.

It should be noted that after executing a DELETE or TRUNCATE operation, the table space may not be released immediately even if the PURGE keyword is executed. This is because in Oracle, the usage of table space is maintained by the SMON (System Monitor) process. The SMON process regularly scans the database, collects all unused space, and turns these spaces into "available space". Therefore, if you need to release the table space immediately, you can use the ALTER TABLESPACE statement to force and compress the table space.

5. View the released table space

We can use the following statement to query the released table space:

SELECT TABLESPACE_NAME, FILE_NAME, ROUND(SUM(BYTES)/(1024*1024),2) AS "SIZE (MB)", AUTOEXTENSIBLE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME, FILE_NAME, AUTOEXTENSIBLE;

Among them, DBA_FREE_SPACE is the Oracle system table, which contains all free space information.

6. Summary

In Oracle database, deleting a table and releasing the corresponding table space is a very important task. Before deleting a table, we need to check the table space usage and clear the table data to reduce space usage. When deleting a table, you can use the DROP TABLE statement and add the PURGE keyword to immediately release the table space. If you need to further release the table space, you can use the ALTER TABLESPACE statement to force and compress the table space.

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