Rebuild/Move tables and Rebuild indexes and Shrink tables

 1) Rebuild tables with no long columns and LOB columns (below example for

sequential move)

SQL> alter table <owner>.<table_name> move tablespace <tablespace_name>;

SQL> alter table &owner.&table_name move tablespace &tablespace_name;

2) If time constraint and having multiple cpu's then use below

SQL> alter table <owner>.<table_name> move tablespace <tablespace name>

parallel < no_of_cpu>;

SQL> alter table &owner.&table_name move tablespace &tablespace_name parallel &no_of_cpu;

3) If we are using parallel, then the above statement permanent

changes to the table  for parallelism, so better to reset after

the table rebuild is done

SQL> alter table table_name parallel 1;

SQL> alter table &table_name parallel &no_of_cpu;

4) We need to rebuild  all the table indexes after move them to new tablespaces

SQL>

select 'alter index '|| owner || '.' || index_name || ' rebuild ;'

from dba_indexes

where table_owner = '&owner'

and table_name = '&table_name'

and partitioned = 'NO'

and index_type != 'LOB'

order by owner, index_name;

5) check index status

SQL> select index_name, status from dba_indexes where table_name='&table' ;

SQL> ALTER TABLE pune.city ENABLE ROW MOVEMENT;

SQL> ALTER TABLE pune.city SHRINK SPACE CASCADE;

SQL> alter tablespace users coalesce;

-- Use Segment Advisor

-- Analyze table

SQL> ANALYZE TABLE pune.city COMPUTE STATISTICS;

SQL> ALTER TABLE pune.city COMPRESS FOR ALL OPERATIONS;

HOW TO CHECK IF INDEX NEEDS TO BE REBUILD

1.  SQL>  analyze index &index_name validate structure;

1.1 SQL> select name,height,lf_rows,lf_blks,del_lf_rows from index_stats;

NAME                HEIGHT    LF_ROWS    LF_BLKS    DEL_LF_ROWS

ASIA_INDEX                1                    3                    1                            0

Hint:- if  HEIGHT > 4 -----> REBUILD THE INDEX

           DEL_LF_ROWS < 20%

2. SQL>  analyze index &index_name monitoring usage;   ----> query v$object_usage;

3.  SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS('SYS', 'ASIA_INDEX');

To shrink tables in oracle database:

--Enable Row Movement

SQL> alter table employees enable row movement;

--Shrink the table  and compact data and release space back to tablespace

SQL> alter table employees shrink space;

--Rebuild indexes

SQL> alter index emp_idx rebuild;

--Disable row movement

SQL> alter table employees disable row movement;