Insight Technology, Inc

--Move command--

SQL> alter table emp move;

ORA-01652: unable to extend temp segment by 8 in tablespace AUTOSEG_TST

Space is insufficient in AUTOSEG_TST tablespace.

SQL> select f.tablespace_name,d.file_name,f.bytes/1024 KBytes,
     f.blocks,d.autoextensible
     from dba_free_space f,dba_data_files d,dba_tables t
     where f.tablespace_name = d.tablespace_name
     and   f.tablespace_name = t.tablespace_name
     and   t.owner='SCOTT' and t.table_name='EMP';

TABLESPACE_NAME FILE_NAME                                 KBYTES  BLOCKS AUTOEXT
--------------- ---------------------------------------- ------- ------- -------
AUTOSEG_TST     /export/home/ora10g/oradata/AUTOSEG1.DBF      64       8 NO

--Shrink command--

Shrink command can be issued to an object that is stored in a tablespace
with insufficient space.

SQL> alter table emp shrink space;

Table altered.

--Move command--

When the table is indexed, it is necessary to rebuild the index.

SQL> alter table dept move;

Table altered.

SQL> select owner,index_name,status from dba_indexes where table_owner='SCOTT'
and table_name='DEPT';

OWNER INDEX_NAME STATUS
----- ---------- --------
SCOTT PK_DEPT    UNUSABLE <-(Index is unusable)

SQL> select /*+ index(dept pk_dept) */ * from dept  where rownum=1;

ORA-01502: index 'SCOTT.PK_DEPT' or partition of such index is in unusable state

After move command is issued, index is in unusable state, which means that
index scan is not available.
In order to make the index usable again, it is necessary to rebuild the index.

SQL> alter index pk_dept rebuild;

Index altered.

SQL> select /*+ index(dept pk_dept) */ * from dept where rownum=1;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

--Shrink command--

It is not necessary to rebuild the index.

SQL> alter table dept shrink space;

Table altered.

SQL> select /*+ index(dept pk_dept) */ * from dept where rownum=1;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SQL> select owner,index_name,status from dba_indexes where table_owner='SCOTT'
and table_name='DEPT';

OWNER INDEX_NAME STATUS
----- ---------- --------
SCOTT PK_DEPT    VALID   <-(Index is usable)

--Shrink command (without cascade option)--

--dept table (before shrink command is issued)
SQL> select owner,segment_name,bytes,blocks,extents from dba_segments
where segment_name = 'PK_DEPT';

OWNER SEGMENT_NAME      BYTES     BLOCKS    EXTENTS
----- ------------ ---------- ---------- ----------
SCOTT PK_DEPT        18874368       2304         33

SQL> alter table dept shrink space;

Table altered.

--dept table (after shrink command is issued)
SQL> select owner,segment_name,bytes,blocks,extents from dba_segments
where segment_name = 'PK_DEPT';

OWNER SEGMENT_NAME      BYTES     BLOCKS    EXTENTS
----- ------------ ---------- ---------- ----------
SCOTT PK_DEPT        18874368       2304         33

pk_dept index is not shrunk

--Shrink command (with cascade option)--

--dept table (before shrink command is issued)
SQL> select owner,segment_name,bytes,blocks,extents from dba_segments
where segment_name = 'PK_DEPT';

OWNER SEGMENT_NAME      BYTES     BLOCKS    EXTENTS
----- ------------ ---------- ---------- ----------
SCOTT PK_DEPT        18874368       2304         33

SQL> alter table dept shrink space cascade;

Table altered.

--dept table (after shrink command is issued)
SQL> select owner,segment_name,bytes,blocks,extents from dba_segments
where segment_name = 'PK_DEPT';

OWNER SEGMENT_NAME      BYTES     BLOCKS    EXTENTS
----- ------------ ---------- ---------- ----------
SCOTT PK_DEPT           65536          8          1