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