Managing Database Resources - OCP: Oracle Database 12c Administrator Certified Professional - page 488

SQL> ALTER TABLE HR.EMPLOYEES SHRINK SPACE;
Table altered.
There are two optional clauses with the SHRINK SPACE command: COMPACT and CASCADE .
The COMPACT clause defragments and compacts but does not reset the high-water mark or
return the free space. Execute the SHRINK SPACE command without the COMPACT clause at a
later time to complete the task.
The CASCADE clause performs the Shrink Space operation on all dependent objects, as
reported by the DBMS_SPACE . OBJECT_DEPENDENT_SEGMENTS procedure.
Partitions in a partitioned table are automatically shrunk with the SHRINK
SPACE command, so you don't need to specify the CASCADE clause.
Here's an example of a small sample table called HR.EMPLOYEES_HIST , built as a copy of
the Oracle-provided HR.EMPLOYEE table. We've inserted rows until we've allocated 40 blocks,
then deleted about 70 percent of the rows. The shrink operation should reduce the number
of blocks to 16. We'll perform the two-step COMPACT process and CASCADE so that you can see
how they work:
SQL> SELECT COUNT(1) from hr.employees_hist;
COUNT(1)
----------
2943
SQL> SELECT SEGMENT_NAME, BLOCKS FROM DBA_SEGMENTS
WHERE OWNER = 'HR' and SEGMENT_NAME LIKE 'EMPL%';
SEGMENT_NAME BLOCKS
------------------------- ----------
EMPLOYEES 8
EMPLOYEES_HIST 40
EMPLOYEES_HIST_IX 16
SQL> ALTER TABLE HR.EMPLOYEES_HIST SHRINK SPACE COMPACT;
Table altered.
SQL> SELECT SEGMENT_NAME, BLOCKS FROM DBA_SEGMENTS
WHERE OWNER = 'HR' and SEGMENT_NAME LIKE 'EMPL%';
SEGMENT_NAME BLOCKS
------------------------- ----------
EMPLOYEES 8
EMPLOYEES_HIST 40