0

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,

0

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.
10
  • Hi sir, thank you for response. UNDO tablespace is missing as space for shrink operation. It is very difficult to clearly predict how much free space is required for UNDO tablespace during the shrink process, there may be too much datafile loss for ASM. Does it work to use only the lob_column shrink option for the table?
    – jrdba123
    Mar 22 at 9:53
  • "shrink table" isn't a transaction in the sense that it doesn't use UNDO and doesn't require a commit. You can only shrink the LOB space (it must be handled separately from the rest of the table), assuming it meets the criteria for LOBs (i.e. they're not SecureFile LOBs). The link I provided has details and examples. Did you read the article?
    – pmdba
    Mar 22 at 10:06
  • Yes I have read. Actually, I mean, does shrinking lob_segment column alone save space well? Additionally, my question about UNDO tablespace was for this; Getting ORA-30036 error during shrink.
    – jrdba123
    Mar 22 at 10:19
  • LOB columns have their own separately defined storage; shrinking a table will not shrink the LOB columns; they must be done separately. Ergo, shrinking a LOB column by itself is the only way to shrink a LOB column and this will also save space. As for your error, you should have lead with that error code. Is this a very active table? Are there ongoing transactions during the shrink?
    – pmdba
    Mar 22 at 10:35
  • Shrink operation continues for 1.5 days. In this table, there is a total of 10 years of data and it is used only for select during the day, and at night the jobs do insert and update. This is one of the main tables in the database and I can say that it takes heavy processing. You are saying that the error received for UNDO tablespace is not related to shrink, but we were not getting this error before and it happened during shrink. Are you sure that it is completely independent of the shrink process?
    – jrdba123
    Mar 22 at 10:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.