《干货分享》分区表改造（脚本模板生成），值得收藏起来实战再用

select t.owner,t.segment_name,t.tablespace_name,sum(bytes/1024/1024/1024) gb
from dba_segments t 
where t.segment_name in (select t2.OBJECT_NAME
                          from dba_objects t2
                         where t2.OBJECT_TYPE = 'TABLE'
                         AND t2.owner=upper('tabel_owner')
                         )
group by t.owner,t.segment_name,t.tablespace_name
order by 4 desc；
复制代码

select round(sum(bytes) / 1024 / 1024 / 1024, 4) IDX_GB  --表上索引对象占用空间
          from dba_segments
         where owner || segment_name in(select owner || index_name
                  from dba_indexes
                 where table_owner = upper('table_name')
                   and table_name = upper('table_owner'))；
复制代码

-----------1.新建分区临时表
  v_sql_temp1   := 'CREATE TABLE ' || v_owner || '.' || v_table_name ||
                   '_P( ';
 select COLUMN_NAME ||'   '|| decode( DATA_TYPE,'DATE',DATA_TYPE, DATA_TYPE||'('|| DATA_LENGTH || ')')
 || decode(NULLABLE,'N','  not null','') || ',' bulk collect
  into v_output_list
  from   dba_tab_columns t 
  where  table_name =v_table_name
  order by t.COLUMN_ID ;  
  dbms_output.put_line(v_sql_temp1);
  for i in 1 ..v_output_list.count  loop
  --去掉数组中的最后一个字段字符中的逗号","
    if i = v_output_list.count then
     select  REPLACE(v_output_list(i),',',' ') into v_sql_temp2 from dual;
     dbms_output.put_line(v_sql_temp2);
   else
     dbms_output.put_line(v_output_list(i));
   end if;
  end loop;
  --输出分区信息
  v_sql_temp3 := ')PARTITION BY RANGE (ARCHIVE_DATE) INTERVAL (NUMTOYMINTERVAL(1, ''YEAR'')) (PARTITION ' || v_table_name || '_2019' ||
                   ' VALUES LESS THAN (TO_DATE(''2020-01-01'', ''YYYY-MM-DD'')))ENABLE ROW MOVEMENT MONITORING INITRANS 6;';
  dbms_output.put_line(v_sql_temp3);
复制代码

CREATE TABLE PASDATA.CLM_PERSON_HOSPITAL_P( 
  CREATED_BY   VARCHAR2(100)  not null,
  CREATED_DATE   DATE  not null,
  UPDATED_BY   VARCHAR2(100)  not null,
  UPDATED_DATE   DATE  not null,
  ID_CLM_PERSON_HOSPITAL   VARCHAR2(32)  not null,
  REPORT_NO   VARCHAR2(30)  not null,
  ID_CLM_CHANNEL_PROCESS   VARCHAR2(32)  not null,
  CASE_TIMES   NUMBER(22)  not null,
  HOSPITAL_CODE   VARCHAR2(22),
  HOSPITAL_NAME   VARCHAR2(100),
  SUBJECT_CODE   VARCHAR2(20),
  BED_CODE   VARCHAR2(100),
  START_DATE   DATE,
  END_DATE   DATE,
  MIGRATE_FROM   VARCHAR2(1),
  AFFIRM_SIGN   VARCHAR2(1)  not null,
  DOCUMENT_GROUP_ID   VARCHAR2(30),
  HOSPITALIZATION_NUMBER   VARCHAR2(30),
  HOSPITALIZE_DAYS   NUMBER(22),
  ARCHIVE_DATE   DATE 
)PARTITION BY RANGE (ARCHIVE_DATE) INTERVAL (NUMTOYMINTERVAL(1, 'YEAR')) (
 PARTITION CLM_PERSON_HOSPITAL_2018 VALUES LESS THAN (TO_DATE('2019-01-01', 'YYYY-MM-DD'))
)ENABLE ROW MOVEMENT MONITORING INITRANS 6;
复制代码

declare
  v_table_name varchar2(100) := upper('表名');
  -------建表变量
  v_sql_temp1   varchar2(1000);
  v_sql_temp2   varchar2(1000);
  v_sql_partion varchar2(1000);
  ----属主变量
  v_owner varchar2(100) := upper('表属主');
  -----输出变量 如果字段数量超过60个，修改数组大小即可
  type remark_list is varray(60) of varchar2(3000);
  v_remark_list remark_list;
begin
  ----在begin后面加上DBMS_OUTPUT.ENABLE(buffer_size => null) ，表示输出buffer不受限制。
  DBMS_OUTPUT.ENABLE(buffer_size => null);
  ----------------------------------------1.新建分区临时表----------------------------------
  v_sql_temp1   := 'CREATE TABLE ' || v_owner || '.' || v_table_name ||
                   '_P PARTITION BY RANGE (ARCHIVE_DATE) INTERVAL (NUMTOYMINTERVAL(1, ''YEAR'')) (';
  v_sql_temp2 := 'PARTITION ' || v_table_name || '_2019' ||
                   ' VALUES LESS THAN (TO_DATE(''2020-01-01'', ''YYYY-MM-DD''))';
  v_sql_temp3   := ')ENABLE ROW MOVEMENT MONITORING INITRANS 6 AS SELECT * FROM  ' ||
                   v_owner || '.' || v_table_name || '  WHERE 1=0;';
  dbms_output.put_line(v_sql_temp1 || v_sql_temp2 || v_sql_temp3);
复制代码

CREATE TABLE PASDATA.EDR_APPLY_PLAN_INFO_P 
PARTITION BY RANGE (ARCHIVE_DATE) INTERVAL (NUMTOYMINTERVAL(1, 'YEAR')) (
PARTITION PART_BEFORE_2018 VALUES LESS THAN (TO_DATE('2018-01-01', 'YYYY-MM-DD'))
)ENABLE ROW MOVEMENT MONITORING INITRANS 6 
AS 
SELECT * FROM  PASDATA.EDR_APPLY_PLAN_INFO  WHERE 1=0;
复制代码

------- 2.对分区新表字段增加默认值
  select 'alter table ' || t.owner || '.' || t.table_name || '_NEW modify ' ||
         t.column_name || ' default  记得填默认值;' bulk collect
    into v_output_list
    from DBA_TAB_COLS t
   where t.TABLE_NAME = v_table_name
     and t.owner = v_owner
     and t.data_default is not null;
  for i in 1 .. v_output_list.count loop
    dbms_output.put_line(v_output_list(i));
  end loop;
复制代码

------------- 3.对分区新表的表名，字段增加注释
  select 'comment on table ' || a.owner || '.' || a.table_name || '_P is ''' ||
         a.comments || ''';' bulk collect
    into v_output_list
    from dba_tab_comments a
   where a.table_name = upper(v_table_name)
     and a.owner = upper(v_owner);
  for i in 1 .. v_output_list.count loop
    dbms_output.put_line(v_output_list(i));
  end loop;
  select 'comment on column ' || owner || '.' || table_name || '_P.' ||
         column_name || ' is ' || '''' || comments || ''';' bulk collect
    into v_output_list
    from dba_col_comments
   where table_name = v_table_name;
  for i in 1 .. v_output_list.count loop
    dbms_output.put_line(v_output_list(i));
  end loop;
复制代码

dbms_output.put_line('insert /*+ append parallel(A, 4) */ into ' ||
                       v_owner || '.' || v_table_name ||
                       '_P A select /*+ parallel(T, 4) */  * from ' ||
                       v_owner || '.' || v_table_name || ' T;');
  dbms_output.put_line('commit;');
复制代码

select 'create ' || decode(a.uniqueness, 'UNIQUE', 'UNIQUE', '') ||
         ' index ' || a.owner || '.' || a.index_name || '_N on ' ||
         a.table_owner || '.' || a.table_name || '_P (' ||
         (select wm_concat(b.column_name)
            from dba_ind_columns b
           where b.index_name = a.index_name
             and b.table_owner = v_owner) || ') initrans 16 PARALLEL 8 online;' bulk collect
    into v_output_list
    from dba_indexes a
   where a.table_name = v_table_name
     and a.index_type != 'LOB';
  for i in 1 .. v_output_list.count loop
   dbms_output.put_line(v_output_list(i));
  end loop;
复制代码

----创建主键约束
  select 'ALTER TABLE ' || a.owner || '.' || a.table_name ||
         '_P ADD CONSTRAINT ' || a.constraint_name || '_N PRIMARY KEY (' ||
         a.column_name || ');' bulk collect
    into v_output_list
    from dba_cons_columns a
   where a.constraint_name =
         (select constraint_name
            from dba_constraints b
           where b.table_name = v_table_name
           and b.owner = a.owner
            and constraint_type = 'P')
            and a.owner = v_owner;
  for i in 1 .. v_output_list.count loop
    dbms_output.put_line(v_output_list(i));
  end loop;
复制代码

-----（如果有外键的话，创建外键约束）
  select 'alter table ' || a.owner || '.' || a.table_name ||
         '_P add constraint ' || a.constraint_name || '_N foreign key(' ||
         b.column_name || ') references ' ||
         (select c.owner || '.' || c.table_name || '(' || c.column_name || ')'
            from dba_cons_columns c, dba_constraints d
           where c.constraint_name = d.constraint_name
             and d.constraint_type = 'P'
             and c.constraint_name = a.r_constraint_name
             and c.owner = v_owner
             and d.owner = v_owner) || ';' bulk collect
    into v_output_list
    from dba_constraints a, dba_cons_columns b
   where a.constraint_name = b.constraint_name
     and a.table_name = v_table_name
     and a.constraint_type = 'R';
 for i in 1 .. v_output_list.count loop
    dbms_output.put_line(v_output_list(i));
  end loop;
复制代码

select 'alter index ' || a.owner || '.' || a.index_name || ' rename to ' ||
         a.index_name || '_T;' bulk collect
    into v_output_list
    from dba_indexes a
   where a.table_name = v_table_name
     and a.index_type != 'LOB';
  for i in 1 .. v_output_list.count loop
    dbms_output.put_line(v_output_list(i));
  end loop;
select 'alter table ' || a.owner || '.' || a.table_name ||
         ' rename constraint ' || a.constraint_name || ' to ' ||
         a.constraint_name || '_T;' bulk collect
   into v_output_list
   from dba_constraints a
  where a.table_name = v_table_name
    and a.constraint_type in ('P', 'R');
 for i in 1 .. v_output_list.count loop
    dbms_output.put_line(v_output_list(i));
 end loop;
复制代码

select 'alter index ' || a.owner || '.' || a.index_name ||
         '_N rename to ' || a.index_name || ';' bulk collect
    into v_output_list
    from dba_indexes a
   where a.table_name = v_table_name
     and a.index_type != 'LOB';
  for i in 1 .. v_output_list.count loop
    dbms_output.put_line(v_output_list(i));
  end loop;
  select 'alter table ' || a.owner || '.' || a.table_name ||
         '_P rename constraint ' || a.constraint_name || '_N to ' ||
         a.constraint_name || ';' bulk collect
    into v_output_list
    from dba_constraints a
   where a.table_name = v_table_name
     and a.constraint_type in ('P', 'R');
  for i in 1 .. v_output_list.count loop
    dbms_output.put_line(v_output_list(i));
  end loop;
复制代码

select distinct (t.grantee) bulk collect
    into grantee_list
    from dba_tab_privs t
   where (t.owner = upper(v_owner))
     and t.table_name = v_table_name;
  for i in 1 .. grantee_list.count loop
    v_grantee := grantee_list(i);
```
select 'grant ' || (select wm_concat(t.privilege)
                      from dba_tab_privs t
                     where t.table_name = v_table_name
                       and t.grantee = v_grantee) || ' on ' || t.owner || '.' ||
       t.table_name || ' to ' || t.grantee || ';'
  into v_grant_sql
  from dba_tab_privs t
 where t.table_name = v_table_name
   and t.grantee = v_grantee
   and rownum = 1;
dbms_output.put_line(v_grant_sql);
end loop;
复制代码

select * from  dba_indexes a where a.table_name = 'CLM_HIS_RECIPE_DETAIL_NEW'
     and a.index_type != 'LOB';
复制代码

select *
  from dba_tab_privs t
 where t.table_name in
       ('CLM_PERSON_HOSPITAL_NEW')
   and t.owner = 'CLAIMDATA';
复制代码