Oracle's steps and methods to shrink tablespace

In Oracle databases, shrinking tablespaces is a common maintenance operation that can recycle unused space, reduce tablespace fragmentation, and improve performance. Here are some steps and methods:

1. Identify unused spaces

First, it is necessary to identify unused spaces in the tablespace. You can get relevant information by querying the DBA_SEGMENTS and DBA_FREE_SPACE views.

-- Query all segments in the tablespace
SELECT segment_type, segment_name, bytes / 1024 / 1024 AS mb
FROM dba_segments
WHERE tablespace_name = 'YOUR_TABLESPACE_NAME';

-- Query free space in tablespace
SELECT tablespace_name, sum(bytes) / 1024 / 1024 AS free_mb
FROM dba_free_space
WHERE tablespace_name = 'YOUR_TABLESPACE_NAME'
GROUP BY tablespace_name;

2. Shrink tables and indexes

Shrinking the table and indexing are important steps in reclaiming space. It can be done by:

2.1 Mobile table

useALTER TABLE ... MOVEThe command moves the table to a new location, which will recycle unused space in the table.

ALTER TABLE schema_name.table_name MOVE;

2.2 Rebuild the index

After moving the table, the index on the table needs to be reconstructed to ensure that the index is also in the best state.

ALTER INDEX schema_name.index_name REBUILD;

2.3 Rebuild all indexes

If there are multiple indexes on the table, you can rebuild all indexes at once using the following script:

BEGIN
    FOR idx IN (SELECT index_name FROM user_indexes WHERE table_name = 'TABLE_NAME') LOOP
        EXECUTE IMMEDIATE 'ALTER INDEX ' || idx.index_name || ' REBUILD';
    END LOOP;
END;
/

3. Shrink the table space

After shrinking the table and index, you can useALTER TABLESPACE ... SHRINK SPACECommand to shrink the tablespace.

3.1 Shrink the table space

ALTER TABLESPACE your_tablespace_name SHRINK SPACE;

3.2 Shrink the table space and compact it

If you want to be compact while shrinking the tablespace, you can use the following command:

ALTER TABLESPACE your_tablespace_name SHRINK SPACE COMPACT;

4. Resize the data file

After shrinking the tablespace, you may need to resize the data file. You can reduce the size of the data file by:

ALTER DATABASE DATAFILE '/path/to/' RESIZE 100M;

5. Use the DBMS_SPACE package

Oracle provides aDBMS_SPACEPackages can be used to analyze and manage tablespace usage in more detail.

-- Get the space usage of the table
DECLARE
    used_bytes NUMBER;
    alloc_bytes NUMBER;
BEGIN
    DBMS_SPACE.OBJECT_SPACE_USAGE(
        segment_owner => 'SCHEMA_NAME',
        segment_name => 'TABLE_NAME',
        segment_type => 'TABLE',
        used_bytes => used_bytes,
        alloc_bytes => alloc_bytes
    );
    DBMS_OUTPUT.PUT_LINE('Used Bytes: ' || used_bytes);
    DBMS_OUTPUT.PUT_LINE('Allocated Bytes: ' || alloc_bytes);
END;
/

6. Regular maintenance

Regular table space maintenance can prevent fragmentation from accumulating. Here are some regular maintenance tasks:

• Collect statistics regularly：

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME');

• Regularly rebuild indexes：

BEGIN
    FOR idx IN (SELECT index_name FROM user_indexes WHERE table_name = 'TABLE_NAME') LOOP
        EXECUTE IMMEDIATE 'ALTER INDEX ' || idx.index_name || ' REBUILD';
    END LOOP;
END;
/

• Regular movement table：

ALTER TABLE schema_name.table_name MOVE;

7. Monitoring and Diagnosis

Use Oracle-provided tools and views to monitor and diagnose performance issues in tablespaces:

• AWR Report：

@?/rdbms/admin/

• SQL Trace and TKPROF：

ALTER SESSION SET SQL_TRACE = TRUE;
-- implementSQL
ALTER SESSION SET SQL_TRACE = FALSE;
-- usetkprofanalyzetracedocument
tkprof trace_file.trc output_file.txt

Summarize

Through the above steps, the table space in the Oracle database can be effectively shrinked.

This is the article about the steps and methods of Oracle shrinking tablespace. For more information about Oracle shrinking tablespace, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!