Table Reorganization

By moving the table to another tablespace temporarily, the DBA is also able to reorganize the tablespace.

Take the sizes of tables , associate indexes and take the invalid object information , Check for table,index status

Example:

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES/1024/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN (<TABLENAMES>);

SELECT OWNER, INDEX_NAME, INDEX_TYPE, TABLE_NAME, STATUS FROM DBA_INDEXES WHERE TABLE_NAME IN (<TABLENAMES>);

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES/1024/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN (<INDEXNAMES>);

SELECT LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME IN (<>);

SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS=’INVALID';

EXEC DBMS_STATS.CREATE_STAT_TABLE(OWNNAME =>’SCOTT’,STATTAB =>’MY_STATS_TABLE’, TBLSPACE =>’USER’);

(b) Export the stats to the above created table

EXEC

DBMS_STATS.EXPORT_TABLE_STATS(‘<OWNERNAME>’,'<TABLENAME>’,NULL,'<STATS TABLENAME>’,NULL,TRUE);

Example:

EXEC DBMS_STATS.EXPORT_TABLE_STATS(‘SCOTT’,’EMP’,NULL,’MY_STATS_TABLE’,NULL,TRUE);