ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 表與表空間建置規則

表與表空間建置規則

原创 Linux操作系统 作者:golden_zhou 时间:2011-03-10 10:04:04 0 删除 编辑

大型資料庫
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';

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25198367/viewspace-688894/,如需转载,请注明出处,否则将追究法律责任。

上一篇: awr script
下一篇: dbms_stats_all
请登录后发表评论 登录
全部评论

注册时间:2011-03-09

  • 博文量
    238
  • 访问量
    301791