Shrink Space on tables

Shrink space on table can be divided into 2 steps:

1) make table storage more compact, this step doesn’t set any locks on table and its depending objects

2) reset HWM of the table to minimum possible position. It makes a lock on table for a very short period.

Prerequsite:

1. need to enable “row movement” on the table, which means changes of rowid of the data. NOTE, if any application relies on rowid this make cause application conflicts.

Verify if any application uses ROWID of the table,

e.g. check all PL/SQL packages for usage of rowid

select distinct owner || '.' || name from dba_source where upper(text) like '%ROWID%' and owner in ('APPUSER')
intersect
select distinct owner || '.' || name from dba_source where upper(text) like '%&TABLE_NAME%' and owner in ('APPUSER');

If no application is using rowid of the table, we can shrink space online, otherwise it’s necessary to reserve a time window where no application is running against the table. If, however, the application STOREs rowid in some tables, we CANNOT do ‘shrink space compact’ !!!

Example of calling shrink space on table

-- before calling shrink space

@shrink_space_candidate_check_usage.sql
Enter value for owner: CENPROD
Enter value for name: TST_FLODWH_VEN
Enter value for type: TABLE
FS1 Blocks = 1 and Bytes = 8192
FS2 Blocks = 0 and Bytes = 0
FS3 Blocks = 2 and Bytes = 16384
FS4 Blocks = 920 and Bytes = 7536640
Full Blocks = 295078 and Bytes = 2417278976
Unformatted Blocks = 26392 and Bytes =216203264
SQL> select bytes from dba_segments where segment_name = 'TST_FLODWH_VEN';

-- call shrink space compact

SQL> set timing on
SQL> alter table CENPROD.TST_FLODWH_VEN shrink space compact;

Table altered.

Elapsed: 00:03:35.98

SQL> SQL> @shrink_space_candidate_check_usage.sql
Enter value for owner: CENPROD
Enter value for name: TST_FLODWH_VEN
Enter value for type: TABLE
FS1 Blocks = 1 and Bytes = 8192
FS2 Blocks = 0 and Bytes = 0
FS3 Blocks = 0 and Bytes = 0
FS4 Blocks = 6353 and Bytes = 52043776
Full Blocks = 295079 and Bytes = 2417287168
Unformatted Blocks = 20960 and Bytes =171704320

-- call shrink space, it only takes a few second
SQL> set timing on
SQL> alter table CENPROD.TST_FLODWH_VEN shrink space;

Table altered.

Elapsed: 00:00:01.16

SQL> @shrink_space_candidate_check_usage.sql
Enter value for owner: CENPROD
Enter value for name: TST_FLODWH_VEN
Enter value for type: TABLE
FS1 Blocks = 1 and Bytes = 8192
FS2 Blocks = 0 and Bytes = 0
FS3 Blocks = 0 and Bytes = 0
FS4 Blocks = 0 and Bytes = 0
Full Blocks = 295079 and Bytes = 2417287168
Unformatted Blocks = 0 and Bytes =0
SQL>  select (2647654400-bytes)/1024/1024 SAVED_MB from dba_segments where segment_name = 'TST_FLODWH_VEN';

SAVED_MB
----------
213.5625

Shrink space on index

Script to check current space usage of a table or an index: shrink_space_candidate_check_usage.sql

cat shrink_space_candidate_check_usage.sql

set serverout on verify off echo off
declare
l_fs1_bytes number;
l_fs2_bytes number;
l_fs3_bytes number;
l_fs4_bytes number;
l_fs1_blocks number;
l_fs2_blocks number;
l_fs3_blocks number;
l_fs4_blocks number;
l_full_bytes number;
l_full_blocks number;
l_unformatted_bytes number;
l_unformatted_blocks number;
begin
dbms_space.space_usage('&owner',
'&name',
'&type',
fs1_bytes=> l_fs1_bytes,
fs1_blocks         => l_fs1_blocks,
fs2_bytes          => l_fs2_bytes,
fs2_blocks         => l_fs2_blocks,
fs3_bytes          => l_fs3_bytes,
fs3_blocks         => l_fs3_blocks,
fs4_bytes          => l_fs4_bytes,
fs4_blocks         => l_fs4_blocks,
full_bytes         => l_full_bytes,
full_blocks        => l_full_blocks,
unformatted_blocks => l_unformatted_blocks,
unformatted_bytes  => l_unformatted_bytes   );
dbms_output.put_line(' FS1 Blocks = '||l_fs1_blocks||' and Bytes = '||l_fs1_bytes);
dbms_output.put_line(' FS2 Blocks = '||l_fs2_blocks||' and Bytes = '||l_fs2_bytes);
dbms_output.put_line(' FS3 Blocks = '||l_fs3_blocks||' and Bytes = '||l_fs3_bytes);
dbms_output.put_line(' FS4 Blocks = '||l_fs4_blocks||' and Bytes = '||l_fs4_bytes);
dbms_output.put_line(' Full Blocks = '||l_full_blocks||' and Bytes = '||l_full_bytes);
dbms_output.put_line(' Unformatted Blocks = '||l_unformatted_blocks||' and Bytes ='||l_unformatted_bytes);
end;
/
quit;
EOF

Before calling ‘SHRINK SPACE’, check current spage usage.

SQL> @shrink_space_candidate_check_usage.sql

Enter value for owner: SP84P
Enter value for name: DBXTESPTP1
Enter value for type: INDEX

FS1 Blocks = 0 and Bytes = 0
FS2 Blocks = 218 and Bytes = 1785856
FS3 Blocks = 0 and Bytes = 0
FS4 Blocks = 0 and Bytes = 0
Full Blocks = 445624 and Bytes = 3650551808
Unformatted Blocks = 1360 and Bytes =11141120

As show above it has 1785856 bytes in low usage blocks (FS2 refers to 25-50% occupation) and 11141120 bytes which has never been used. Now call ‘shrink space compact’ on index.

SQL> alter index SP84P.DBXTESPTP1 shrink space compact;
Index altered.

the effect:

SQL> @shrink_space_candidate_check_usage.sql
 Enter value for owner: SP84P
 Enter value for name: DBXTESPTP1
 Enter value for type: INDEX
 FS1 Blocks = 0 and Bytes = 0
 FS2 Blocks = 3 and Bytes = 24576
 FS3 Blocks = 0 and Bytes = 0
 FS4 Blocks = 110310 and Bytes = 903659520
 Full Blocks = 335529 and Bytes = 2748653568
 Unformatted Blocks = 1360 and Bytes =11141120

PL/SQL procedure successfully completed.

As we can see, SHRINK SPACE COMPACT move blocks from FS2 to FS4. Now call ‘shrink space’ on index.

SQL> alter index SP84P.DBXTESPTP1 shrink space;
Index altered.

the effect:

SQL> @shrink_space_candidate_check_usage.sql
Enter value for owner: SP84P
Enter value for name: DBXTESPTP1
Enter value for type: INDEX
FS1 Blocks = 0 and Bytes = 0
FS2 Blocks = 0 and Bytes = 0
FS3 Blocks = 0 and Bytes = 0
FS4 Blocks = 0 and Bytes = 0
Full Blocks = 335517 and Bytes = 2748555264
Unformatted Blocks = 0 and Bytes =0

PL/SQL procedure successfully completed.

As shown above, the unformatted blocks are move off from HWM and total allocated space is shrinked from 3.6GB to 2.7GB. We saved 900MB to tablespace.

shrink space tablespace

COLUMN SHRINK_DATAFILES FORMAT A75 WORD_WRAPPED
COLUMN VALUE NEW_VAL BLKSIZE
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_block_size';

SELECT 'ALTER DATABASE DATAFILE ''' || FILE_NAME || ''' RESIZE ' || CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) ||
'M;' SHRINK_DATAFILES FROM DBA_DATA_FILES DBADF,
(SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS
WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+) AND CEIL(BLOCKS*&&BLKSIZE/1024/1024)- CEIL((NVL(HWM,1)* &&BLKSIZE)/1024/1024 ) > 0;

SHRINK_DATAFILES
-------------------------------------------------------------------------
ALTER DATABASE DATAFILE '/db/oradata/GOLDTEST/centestdata01.dbf'
RESIZE 9139M;

ALTER DATABASE DATAFILE '/db/oradata/GOLDTEST/centestidx01.dbf'
RESIZE 14871M;

.....

Increase the suggested value to avoid potential errors

ALTER DATABASE DATAFILE '/db/oradata/GOLDTEST/centestdata01.dbf' resize 10000M;

Database altered.