Shulou(Shulou.com)06/01 Report--

1. Create a test table

Create table cux.cux_test_data as

Select * from GL_JE_HEADERS

-2. View the number of data items

Select count (1) from cux.cux_test_data

-- 3. View block size

SELECT segment_name

, extents

, blocks

, initial_extent

FROM dba_segments

WHERE segment_name = 'CUX_TEST_DATA'

-- 4. Delete the table in truncate mode

Truncate table cux.cux_test_data

-- View block siz

SELECT segment_name

, extents

, blocks

, initial_extent

FROM dba_segments

WHERE segment_name = 'CUX_TEST_DATA'

This shows that truncate clears the size of the data blocks occupied by the table

-- 5. Delete the table in delete mode

First, reinsert the test table and find out that the block size is as follows:

Perform the delete operation:

Delete from cux.cux_test_data

The block size of the query table again is as follows:

It can be seen that the occupied data blocks have not changed.

-- after delete, insert 1 million pieces of data into the test table, and query the block size again as follows:

It is found that the table space distribution is the same as before deletion, that is, when the delete-related data is inserted, if the data is smaller than that before deletion, the tablespace distribution will not change; when more data is inserted than before deletion, the block size will be increased.

6. Use SHRINK and MOVE to shrink the tablespace

ALTER TABLE cux.cux_test_data MOVE;-lowers the water mark and frees up table space

Note: the alter table move command frees up space and locks the table during this operation

It will also cause the index to fail. If the table has an index, an index reconstruction operation is required. The re-index script is as follows:

ALTER INDEX XXX REBUILD ONLINE;-re-indexing

Alter table cux.cux_test_data enable row movement;-enable row migration

Alter table cux.cux_test_data shrink;-- lowering water mark

After shrinking in the above two steps, the tablespace has been released

Conclusion:

1. TRUNCATE deleting a table will directly free the tablespace.

2. DELETE deleting a table will not free up the tablespace. If you want to free it, you can use SHRINK and MOVE to shrink it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.