DBA Hub

📋Steps in this guide1/5

ALTER TABLE ... SHRINK SPACE Command : Online Segment Shrink for Tables, LOBs and IOTs

The ALTER TABLE ... SHRINK SPACE command is used to perform online segment shrinks for tables, LOBs and IOT overflow segments.

oracle miscconfigurationintermediate
by OracleDba
14 views
1

Shrink Space Examples

Here are some simple examples of the command. The option allows the shrink operation to be broken into two stages. First the rows are moved using the option but the high water mark (HWM) is not adjusted so no parsed SQL statements are invalidated. The HWM can be adjusted at a later date by reissuing the statement without the option. At this point any dependent SQL statements will need to be re-parsed. Other shrink commands of interest are displayed below. There is more detail about this functionality below.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Enable row movement.
ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

-- Recover space and amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE;

-- Recover space, but don't amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE COMPACT;

-- Recover space for the object and all dependant objects.
ALTER TABLE scott.emp SHRINK SPACE CASCADE;

-- Shrink a LOB segment (basicfile only until 21c).
ALTER TABLE table_name MODIFY LOB(lob_column) (SHRINK SPACE);
ALTER TABLE table_name MODIFY LOB(lob_column) (SHRINK SPACE CASCADE);

-- Shrink an IOT overflow segment.
ALTER TABLE iot_name OVERFLOW SHRINK SPACE;
2

Identify Large Segments

The views can be used to identify large segments. The following example uses a top-n query to display the 20 largest segments. You may see many of the larger segments being LOB segments. You can get more information about LOB segments specifically using the following top-n query. The following scripts are examples of these types of queries. - large_segments.sql - large_lob_segments.sql

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
SET LINESIZE 200
COLUMN owner FORMAT A30
COLUMN segment_name FORMAT A30
COLUMN tablespace_name FORMAT A30
COLUMN size_mb FORMAT 99999999.00

SELECT *
FROM   (SELECT owner,
               segment_name,
               segment_type,
               tablespace_name,
               ROUND(bytes/1024/1024,2) size_mb
        FROM   dba_segments
        ORDER BY 5 DESC)
WHERE  ROWNUM <= 20;

SET LINESIZE 200
COLUMN owner FORMAT A30
COLUMN table_name FORMAT A30
COLUMN column_name FORMAT A30
COLUMN segment_name FORMAT A30
COLUMN tablespace_name FORMAT A30
COLUMN size_mb FORMAT 99999999.00

SELECT *
FROM   (SELECT l.owner,
               l.table_name,
               l.column_name,
               l.segment_name,
               l.tablespace_name,
               ROUND(s.bytes/1024/1024,2) size_mb
        FROM   dba_lobs l
               JOIN dba_segments s ON s.owner = l.owner AND s.segment_name = l.segment_name
        ORDER BY 6 DESC)
WHERE  ROWNUM <= 20;
3

Row Movement

The command moves rows between existing blocks to compact the data, so before you attempt to shrink a table segment you need to enable row movement. You can check if row movement is already enabled by querying the column of the views. Row movement is enabled with the following command. Repeating the previous query shows row movement is now enabled.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT row_movement
FROM   user_tables
WHERE  table_name = 'EMP';

ROW_MOVE
--------
DISABLED

SQL>

ALTER TABLE emp ENABLE ROW MOVEMENT;

SELECT row_movement
FROM   user_tables
WHERE  table_name = 'EMP';

ROW_MOVE
--------
ENABLED

SQL>
4

SecureFile LOBs

When using basicfile LOBs the shrink commands work as expected. To demonstrate this we need to create the following table containing a basicfile LOB column. We can see both shrink commands complete without errors. Now recreate the table using a securefile LOB column. Now the first command fails, but adding the option appears to make it work. Unfortunately, the second command doesn't work and the securefile LOB segment is not shrunk. Instead, to shrink a securefile LOB segment you need to move it. In the following example the move is to the same tablespace.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
DROP TABLE lob_tab PURGE;

CREATE TABLE lob_tab (
  id NUMBER,
  data CLOB
)
LOB(data) STORE AS
BASICFILE
(DISABLE STORAGE IN ROW);

INSERT INTO lob_tab VALUES (1, 'ONE');
COMMIT;

SQL> ALTER TABLE lob_tab MODIFY LOB(data) (SHRINK SPACE);

Table altered.

SQL> ALTER TABLE lob_tab MODIFY LOB(data) (SHRINK SPACE CASCADE);

Table altered.

SQL>

DROP TABLE lob_tab PURGE;

CREATE TABLE lob_tab (
  id NUMBER,
  data CLOB
)
LOB(data) STORE AS
SECUREFILE
(DISABLE STORAGE IN ROW);

INSERT INTO lob_tab VALUES (1, 'ONE');
COMMIT;

SQL> ALTER TABLE lob_tab MODIFY LOB(data) (SHRINK SPACE);
ALTER TABLE lob_tab MODIFY LOB(data) (SHRINK SPACE)
*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type


SQL> ALTER TABLE lob_tab MODIFY LOB(data) (SHRINK SPACE CASCADE);

Table altered.

SQL>

ALTER TABLE lob_tab MOVE LOB(data) STORE AS (TABLESPACE users);
5

Comments and Restrictions

Here are some things to consider before performing shrink operations. - 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 clause with any other clauses. - You can't shrink a cluster or a clustered table. - You can't shrink any object with a 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 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. You can use the unusuable_indexes.sql script to find them. If you have any, rebuild them. For more information see: - ALTER TABLE - Online Segment Shrink for Tables : Free Unused Space - Reclaiming Unused Space in Datafiles Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!