大型資料庫
BASE_DATA ( < 5     萬  或 < 500   筆/月 或 < 5000   筆/年) 
LOG_DATA  ( < 500   萬  或 < 2000  筆/天 或 < 50000  筆/月) 
WIP_DATA  ( < 5000  萬  或 < 20000 筆/天 或 < 500000 筆/月)
SN_DATA   ( > 5000  萬  )                                 

小中型資料庫
BASE_DATA ( < 1     萬  或 < 100   筆/月 或 < 1000   筆/年)  
LOG_DATA  ( < 100   萬  或 < 500   筆/天 或 < 10000  筆/月) 
WIP_DATA  ( < 1000  萬  或 < 5000  筆/天 或 < 100000 筆/月) 
SN_DATA   ( > 1000  萬  )  

select owner,table_name,tablespace_name,'->BASE_DATA',num_rows from dba_tables where wner='SFISM4' and num_rows<1000

select owner,table_name,tablespace_name,'->LOG_DATA',num_rows from dba_tables where wner='SFISM4' and num_rows >= 1000 and num_rows < 100000

select owner,table_name,tablespace_name,'->WIP_DATA',num_rows from dba_tables where wner='SFISM4' and num_rows >= 100000 and num_rows < 10000000

select owner,table_name,tablespace_name,'->SN_DATA',num_rows from dba_tables where wner='SFISM4' and num_rows >= 10000000

BASE_DATA UNIFORM=2M
ROWS<1000

LOG_DATA  UNIFORM=20M
1000=

WIP_DATA  UNIFORM=50M
100000=

SN_DATA   UNIFORM=100M
ROWS>=10,000,000

exec dbms_stats.unlock_table_stats(ownname => 'SFISM4',tabname => 'R_CARTON_LIST_T');
select 'exec dbms_stats.unlock_table_stats(ownname =>'''||owner||''',tabname =>'''||table_name||''''||') ;' from dba_tables where wner='SFISM4'

SELECT      ' alter table  SFISM4.'
         || object_name
         || '  move tablespace BASE_DATA ; '
  FROM   dba_objects
 WHERE   object_type = 'TABLE' AND wner = 'SFISM4'
         AND object_name IN
                  (SELECT   table_name
                     FROM   dba_tables
                    WHERE   wner = 'SFISM4'
                            AND num_rows <1000
                    union
                    SELECT   table_name
                     FROM   dba_tables
                    WHERE   wner = 'SFISM4'
                            AND num_rows is null)

union

/* Formatted on 2011/2/18 下午 04:54:27 (QP5 v5.115.810.9015) */
SELECT      ' alter index  SFISM4.'
         || index_name
         || '  rebuild  tablespace BASE_IDX ;'
  FROM   dba_indexes
 WHERE   table_owner = 'SFISM4' AND wner = 'SFISM4'
         AND table_name IN
                  (SELECT   table_name
                     FROM   dba_tables
                    WHERE   wner = 'SFISM4'
                            AND num_rows <1000
                    union
                    SELECT   table_name
                     FROM   dba_tables
                    WHERE   wner = 'SFISM4'
                            AND num_rows is null)

union

/* Formatted on 2011/2/18 下午 04:54:19 (QP5 v5.115.810.9015) */
SELECT      ' alter table  SFISM4.'
         || object_name
         || '  move tablespace LOG_DATA ; '
  FROM   dba_objects
 WHERE   object_type = 'TABLE' AND wner = 'SFISM4'
         AND object_name IN
                  (SELECT   table_name
                     FROM   dba_tables
                    WHERE   wner = 'SFISM4'
                            AND num_rows >= 1000 and num_rows < 100000)
union

/* Formatted on 2011/2/18 下午 04:54:27 (QP5 v5.115.810.9015) */
SELECT      ' alter index  SFISM4.'
         || index_name
         || '  rebuild  tablespace LOG_IDX ;'
  FROM   dba_indexes
 WHERE   table_owner = 'SFISM4' AND wner = 'SFISM4'
         AND table_name IN
                  (SELECT   table_name
                     FROM   dba_tables
                    WHERE   wner = 'SFISM4'
                            AND num_rows >= 1000 and num_rows < 100000)

union

/* Formatted on 2011/2/18 下午 04:54:19 (QP5 v5.115.810.9015) */
SELECT      ' alter table  SFISM4.'
         || object_name
         || '  move tablespace WIP_DATA ; '
  FROM   dba_objects
 WHERE   object_type = 'TABLE' AND wner = 'SFISM4'
         AND object_name IN
                  (SELECT   table_name
                     FROM   dba_tables
                    WHERE   wner = 'SFISM4'
                            AND num_rows >= 100000 and num_rows < 10000000)
union

/* Formatted on 2011/2/18 下午 04:54:27 (QP5 v5.115.810.9015) */
SELECT      ' alter index  SFISM4.'
         || index_name
         || '  rebuild  tablespace WIP_IDX ;'
  FROM   dba_indexes
 WHERE   table_owner = 'SFISM4' AND wner = 'SFISM4'
         AND table_name IN
                  (SELECT   table_name
                     FROM   dba_tables
                    WHERE   wner = 'SFISM4'
                            AND num_rows >= 100000 and num_rows < 10000000)

union

/* Formatted on 2011/2/18 下午 04:54:19 (QP5 v5.115.810.9015) */
SELECT      ' alter table  SFISM4.'
         || object_name
         || '  move tablespace SN_DATA ; '
  FROM   dba_objects
 WHERE   object_type = 'TABLE' AND wner = 'SFISM4'
         AND object_name IN
                  (SELECT   table_name
                     FROM   dba_tables
                    WHERE   wner = 'SFISM4'
                            AND num_rows >= 10000000)
union

/* Formatted on 2011/2/18 下午 04:54:27 (QP5 v5.115.810.9015) */
SELECT      ' alter index  SFISM4.'
         || index_name
         || '  rebuild  tablespace SN_IDX ;'
  FROM   dba_indexes
 WHERE   table_owner = 'SFISM4' AND wner = 'SFISM4'
         AND table_name IN
                  (SELECT   table_name
                     FROM   dba_tables
                    WHERE   wner = 'SFISM4'
                            AND num_rows >= 10000000);

SELECT 'ALTER TABLE SFISM4.'||TABLE_NAME||' STORAGE ( NEXT 20M ) ;' from dba_tables where tablespace_name='LOG_DATA' and wner='SFISM4'
UNION
SELECT 'ALTER INDEX SFISM4.'||INDEX_NAME||' STORAGE ( NEXT 20M ) ;' from dba_indexes where tablespace_name='LOG_IDX' and wner='SFISM4'
UNION
SELECT 'ALTER TABLE SFISM4.'||TABLE_NAME||' STORAGE ( NEXT 50M ) ;' from dba_tables where tablespace_name='WIP_DATA' and wner='SFISM4'
UNION
SELECT 'ALTER INDEX SFISM4.'||INDEX_NAME||' STORAGE ( NEXT 50M ) ;' from dba_indexes where tablespace_name='WIP_IDX' and wner='SFISM4'
UNION
SELECT 'ALTER TABLE SFISM4.'||TABLE_NAME||' STORAGE ( NEXT 100M ) ;' from dba_tables where tablespace_name='SN_DATA' and wner='SFISM4'
UNION
SELECT 'ALTER INDEX SFISM4.'||INDEX_NAME||' STORAGE ( NEXT 100M ) ;' from dba_indexes where tablespace_name='SN_IDX' and wner='SFISM4';