Oracle 11g – Decreasing Big Table Size

oracle-11goracle-11g-r2shrinksize;table

I want to reduce the size of a table.

I thought if I could do this with export / import, but when I check it
with the ESTIMATE_ONLY option, it doesn't seem to work.

I have no chance of using partitioning.

If I do it with CTAS (create table as select), the archive production
will peak and the disaster database will be affected.

How can I reduce the size of this table? If I do shrink, will UNDO be
enough and how much space will be enough to add UNDO tablespace to
overcome this problem? Do you have an estimate of how long it will
take if I shrink?

I have to be very careful as it will be done directly in the
production database.

Version: Oracle 11g Release 2 – Standard Edition

ASM option: yes

Cluster: yes

Partitioning option: no

Compress data pump option: no

Table size information:

SQL> select segment_name,sum(bytes)/1024/1024/1024 GB from dba_segments where owner='OWNER_NAME' and segment_type='TABLE' and segment_name=upper('TABLE_NAME') group by segment_name;

SEGMENT_NAME                                                                              GB
--------------------------------------------------------------------------------- ----------
TABLE_NAME                                                                        392.493164

Export information (It does not work in reducing the table size.
):

nohup expdp "'/ as sysdba'" directory=DP_DIR tables=OWNER_NAME.TABLE_NAME ESTIMATE_ONLY=Y &

Output:

Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=DP_DIR tables=OWNER_NAME.TABLE_NAME ESTIMATE_ONLY=Y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "OWNER_NAME"."TABLE_NAME"            392.4 GB
Total estimation using BLOCKS method: 392.4 GB

Best regards,

Best Answer

The most direct method shrinking a table is:

ALTER TABLE [table name] ENABLE ROW MOVEMENT;
ALTER TABLE [table name] SHRINK SPACE CASCADE;

More details here: https://oracle-base.com/articles/misc/alter-table-shrink-space-online

There are some limitations to this command:

  • Moving rows can cause problem with rowid based triggers.
  • Rowid materialized views must be rebuilt after a shrink operation.
  • The shrinking process is only available for objects in tablespaces with automatic segment-space management enabled.
  • You can't combine the SHRINK SPACE clause with any other ALTER TABLE clauses.
  • You can't shrink a cluster or a clustered table.
  • You can't shrink any object with a LONG column.
  • You can't shrink tables with dependent function-based indexes, domain indexes, or bitmap join indexes.
  • You can't shrink tables that are the master table of an ON COMMIT materialized view Mapping tables of index-organized tables are not affected by a shrink.
  • Shrinks can't be used for compressed tables, except those using Advanced Row Compression (ROW STORE COMPRESS ADVANCED).
  • The shrink operation against a table doesn't cascade to the LOB segments. They need to handled separately.
  • You can't shrink securefile LOB segments.
  • Changing the arrangement of rows in a table can have a negative impact on performance in some circumstances. Test thoroughly before making any decisions.
  • After any structural change, like a move, remember to check for unusuable indexes. If you have any, rebuild them.
Related Question