脚本

[sql] view plaincopy

1. -- 检测删除导致高水位线没有降低，造成大量逻辑读  
2. EXEC DBMS_STATS.gather_table_stats(ownname => 'CODERJIANG', tabname => 'MY_TEST', estimate_percent => 10, method_opt => 'FOR ALL INDEXED COLUMNS', cascade => TRUE);  
3. SELECT T.NUM_ROWS, T.BLOCKS, T.TABLE_NAME FROM USER_TAB_STATISTICS T WHERE T.TABLE_NAME = 'MY_TEST';  

[sql] view plaincopy

1. -- 表空间使用情况  
2. SELECT A.TABLESPACE_NAME "表空间名",  
3.        A.TOTAL_SPACE AS "总空间(G)",  
4.        NVL(B.FREE_SPACE, 0) "剩余空间(G)",  
5.        A.TOTAL_SPACE - NVL(B.FREE_SPACE, 0) " 使用空间(G) ",  
6.        CASE  
7.          WHEN A.TOTAL_SPACE = 0 THEN  
8.           0  
9.          ELSE  
10.           TRUNC(NVL(B.FREE_SPACE, 0) / A.TOTAL_SPACE * 100, 2)  
11.        END " 剩余百分比% "  
12.   FROM (SELECT TABLESPACE_NAME,  
13.                TRUNC(SUM(BYTES) / 1024 / 1024 / 1024, 2) TOTAL_SPACE  
14.           FROM DBA_DATA_FILES  
15.          GROUP BY TABLESPACE_NAME) A,  
16.        (SELECT TABLESPACE_NAME,  
17.                TRUNC(SUM(BYTES / 1024 / 1024 / 1024), 2) FREE_SPACE  
18.           FROM DBA_FREE_SPACE  
19.          GROUP BY TABLESPACE_NAME) B  
20.  WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+);  

[sql] view plaincopy

1. -- 临时表空间  
2. SELECT * FROM DBA_TEMP_FILES;  

[sql] view plaincopy

1. -- 表空间扩展的次数  
2. SELECT COUNT(*) FROM USER_EXTENTS WHERE SEGMENT_NAME = 'MY_TEST';  

[sql] view plaincopy

1. -- 用户持有对象大小  
2. SELECT T.SEGMENT_NAME "对象名称", T.SEGMENT_TYPE AS "类型", SUM(T.BYTES) / 1024 / 1024 AS "大小(M)", COUNT(*) AS "对象个数"  
3.   FROM USER_SEGMENTS T  
4.  GROUP BY T.SEGMENT_NAME, T.SEGMENT_TYPE  
5.  ORDER BY T.SEGMENT_NAME, SUM(T.BYTES) / 1024 / 1024 DESC;  

[sql] view plaincopy

1. -- 普通表转分区表  
2. create or replace package pkg_deal_part_tab  
3. Authid Current_User  
4. as  
5.   
6.  ----------------------------------------------------------------------------------------------  
7. /*  
8.  功能： 实现普通表到分区表自动改造工作  
9.  版本号：V2.2  
10.  <1>. 代码履历   
11.   create by liangjb at 2010-09-16  
12.   added by liangjb  at 2010-9-20  增加外键约束功能  
13.   added by liangjb  at 2010-9-20  增加自动建索引功能(当前只是处理普通索引，含唯一和非唯一索引，但是不含函数索引）  
14.   added by liangjb  at 2010-9-20  能通过p_struct_only参数来决定新建的表是否只是结构，还是需要把数据CREATE过来  
15.   added by liangjb  at 2010-9-21  索引指定索引表空间  
16.   added by liangjb  at 2010-9-28  将唯一索引和普通索引两个小过程合并为一个  
17.   added by liangjb  at 2010-10-06 原先的外键依赖有BUG，未考虑联合外键和联合组键相关的情况，修正p_deal_constraint代码  
18.   added by liangjb  at 2010-10-08 将普通索引转化为LOCAL索引，UNIQUE索引仍然为全局索引  
19.   added by liangjb  at 2010-10-28 程序增加P_CREATE_LOG自动判断PART_TAB_LOG表是否存在，如果不存在，就自动建立，相应的p_insert_log也要由静态SQL改为动态SQL  
20.   added by liangjb  at 2010-10-28 所有的v_sql都替换为各个程序特定的V_SQL，比如v_sql_p_main  
21.   added by liangjb  at 2010-10-29 对RENAME的备份表已经存在的情况做判断（p_rename_001)  
22.   added by liangjb  at 2010-10-30 将p_pk,pk_constraint,p_index这三个过程的删除备份表的对象做dbms_output 输出，以方便跟踪,定位可能出现的问题  
23.   added by liangjb  at 2010-10-31 考虑到河南无端出现的主键建失败异常的情况，将建索引的顺序调整到建主键的前面，这样即便将来主键再失败，数量比较多的索引先建立好，手工修复也更快一些  
24.   added by liangjb  at 2010-11-2  由于本脚本主要是针对最常用的时间分区的脚本，所以增加了限制，p_main主程序带入的参数p_part_colum分区列必须是DATA类型的字段  
25.   added by liangjb  at 2010-11-2  如果手工执行脚本不自动执行，将手工执行的方法打印出来  
26.   added by liangjb  at 2010-11-2  将输入到part_tab_log的表的手工执行的脚本拿出来执行的时候CTAS部分会有"",是因为 p_ctas_002部分的v_sql_p_ctas脚本有换行，修正v_sql_p_ctas部分代码，将换行去掉就正常了。  
27.   added by liangjb  at 2010-11-2  新增加p_do模块，对主程序p_main 进行封装，目的是在调用p_main之前先判断输入表名，数据表空间和索引表空间等是否存在，避免直接调用p_main的时候在001-010的某个环节出错，又不能在一事务同时成功同时失败带来的麻烦。为了避免误操作，包头的p_main注释掉了  
28.   added by liangjb  at 2010-11-2  考虑到有的表名比较长，所以将类似T2_20101102的表改为T2_1102的方式  
29.   added by liangjb  at 2010-11-2  将步骤001-010这10个步骤中无需执行的也打印出来，比如原表没有索引，就提示备份出的原表无索引  
30.   added by liangjb  at 2010-11-24  很奇怪p_main模块中的SELECT COUNT(*) INTO v_cnt_tab_log FROM USER_TABLES WHERE TABLE_NAME='PART_TAB_LOG';怎么消失了，现在补充进去。  
31.  --------------------------------------------------------------------------------------------  
32.  <2>. 参数含义：  
33.     p_tab in varchar2,                 -----------------需要进行分区的普通表的表名  
34.     p_struct_only                      -----------------新表是否是只建表结构不导入数据，0为只建结构，非0如1等值，为导数据  
35.     p_deal_flag in number default 0,   -----------------0为不执行，只将脚本记录进part_tab_log的sql_text字段中，1为不仅记录，而且执行！  
36.     p_parallel in number default 4,    -----------------分区操作中的并行度，默认为4  
37.     p_part_colum in varchar2,          -----------------需要分区的列，本程序主要针对最常见的时间范围类型分区，按月份分区  
38.     p_part_nums in number default 24,  -----------------默认情况下为最近一个月到后续24个月，可以自行选择  
39.     p_tablespace IN VARCHAR2           -----------------分区表的数据表空间  
40.     p_idx_tablespace IN VARCHAR2       -----------------分区的索引表空间  
41.  -----------------------------------------------------------------------------------------------------------------------------  
42.  <3>.注意点：  
43.      1.被改造的表必须要有时间字段，我们使用时间字段来进行分区  
44.      2. PART_TAB_LOG是用来记录操作日志的（该表由程序自动生成，无需手动建立)  
45.      3.在不执行脚本，只获取脚本时，脚本可以用如下方式获取，并手工执行  
46.      select sql_text||';' from part_tab_log t where tab_name='ts_real_datatrance' order by exec_order1,exec_order2    
47.      4. 从理论上来说，p_main调用的001-010这10个模块的程序一起成功，一起失败，在一个事务里，才是合理的！  
48.         但是由于都是DDL语句，所以无法实现，这是一个遗憾。  
49.  --------------------------------------------------------------------------------------------------------------------------------  
50.  <4>. 试验检验脚本  
51.   <4 1=""> 构造  
52.   ---下面通过我构造的试验，可以详细的让大家试验，了解。我的这个T2表，涵盖了上面的所有属性甚至特殊的属性，比如组合主键，组合外键，组合索引等  
53.     drop table t1 CASCADE CONSTRAINTS;  
54.     drop table t2 CASCADE CONSTRAINTS;  
55.     DROP TABLE T3 CASCADE CONSTRAINTS;  
56.     drop table t2_&mmdd;  
57.     CREATE TABLE T1 (ID1 NUMBER,ID2 NUMBER, PRIMARY KEY (ID1,ID2));  
58.     CREATE TABLE T3 (ID1 NUMBER,  PRIMARY KEY (ID1));  
59.     CREATE TABLE T2 (T2ID   NUMBER DEFAULT 8,  
60.                      T1ID1  NUMBER NOT NULL,  
61.                      T1ID2  NUMBER,   
62.                      deal_date date,  
63.                      primary key (T2ID,T1ID1),  
64.                      FOREIGN KEY (T1ID1,T1ID2) REFERENCES T1(ID1,ID2),  
65.                      FOREIGN KEY (T2ID) REFERENCES T3(ID1));  
66.     alter table T2 add constraint CKC_T2 check (T1ID1>=0);  
67.     comment on column T2.T2ID is '标识';  
68.     comment on table T2 is '分区测试表';  
69.     create index idx_t1id2 on t2(t1id1);  
70.     create unique index idx_t2id2 on t2(t1id2);  
71.     --以下构造数据  
72.     insert into t1 select rownum,rownum+1 from dual connect by level<=200;  
73.     insert into t3 select rownum from dual connect by level<=200;  
74.     insert into t2 select rownum,rownum+1,rownum+2,sysdate from dual connect by level<=100;  
75.     commit;  
76.    <4 2=""> 测试（如果将p_deal_flag=0则是不执行，只记录日志，我们选择手动从日志中取脚本执行，1是既记录日志，又执行)  
77.     set serveroutput on size 1000000  
78.     exec pkg_deal_part_tab.p_do(p_tab => 'T2', p_deal_flag => 1, p_parallel => 4, p_part_colum => 'deal_date',p_part_nums=> 24,p_struct_only =>1 ,p_tab_tablespace => '&表空间名',p_idx_tablespace => '&索引空间名');           
79. -------------------------------------------------------------------------------------------    
80. */  
81.   
82.    procedure p_rename_001         (p_tab in varchar2 );     
83.    procedure p_ctas_002           (p_tab in varchar2,  
84.                                    p_struct_only  in number,  
85.                                    p_deal_flag in number,  
86.                                    p_part_colum in varchar2,  
87.                                    p_parallel in number default 4,  
88.                                    p_tablespace IN VARCHAR2);                                    
89.    procedure p_split_part_003     (p_tab in varchar2,  
90.                                    p_deal_flag in number,  
91.                                    p_part_nums in number default 24,  
92.                                    p_tab_tablespace IN VARCHAR2);                                     
93.    procedure p_tab_comments_004   (p_tab in varchar2,p_deal_flag in number);   
94.    procedure p_col_comments_005   (p_tab in varchar2,p_deal_flag in number);   
95.    procedure p_defau_and_null_006 (p_tab in varchar2,p_deal_flag in number);  
96.    procedure p_check_007          (p_tab in varchar2,p_deal_flag in number );  
97.    procedure p_index_008          (p_tab in varchar2,p_deal_flag in number,p_idx_tablespace IN VARCHAR2);  
98.    procedure p_pk_009             (p_tab in varchar2,p_deal_flag in number,p_idx_tablespace IN VARCHAR2);  
99.    procedure p_constraint_010     (p_tab in varchar2,p_deal_flag in number);  
100.    /*procedure p_main               (p_tab in varchar2,  
101.                                    p_deal_flag      in number default 0,  
102.                                    p_parallel       in number default 4,  
103.                                    p_part_colum     in varchar2,  
104.                                    p_part_nums      in number default 24,  
105.                                    p_struct_only    in number default 0,  
106.                                    p_tab_tablespace IN VARCHAR2,  
107.                                    p_idx_tablespace IN VARCHAR2);*/  
108.    procedure p_do                 (p_tab in varchar2,                  
109.                                    p_deal_flag in number default 0,      
110.                                    p_parallel in number default 4,       
111.                                    p_part_colum in varchar2,             
112.                                    p_part_nums in number default 24,     
113.                                    p_struct_only in number default 0,   
114.                                    p_tab_tablespace IN VARCHAR2,         
115.                                    p_idx_tablespace IN VARCHAR2);  
116.   
117. end pkg_deal_part_tab;  
118. /  
119. create or replace package body pkg_deal_part_tab  
120. as  
121. --YYYYMMDD      VARCHAR2(50) :=TO_CHAR(TRUNC(SYSDATE),'yyyymmdd'); --全局生效，很多过程需要引用  
122. YYYYMMDD        VARCHAR2(50) :=TO_CHAR(TRUNC(SYSDATE),'mmdd'); --考虑到有的表名比较长，修改为mmdd  
123.   
124.   procedure p_create_log_tab   
125.   as  
126.   /*  
127.    功能：创建分区日志记录表，以方便记录操作的步骤到part_tab_log表中  
128.   */  
129.   v_sql_p_create_log_tab         varchar2(4000);  
130.   begin  
131.    v_sql_p_create_log_tab:=  
132.     '  create table PART_TAB_LOG  
133.      (  
134.       TAB_NAME      VARCHAR2(200),  
135.       DEAL_MODEL    VARCHAR2(200),  
136.       SQL_TEXT      VARCHAR2(4000),  
137.       DEAL_TIME     DATE,  
138.       remark        VARCHAR2(4000),  
139.       exec_order1   number,  
140.       exec_order2   number  
141.      )';  
142.     execute immediate v_sql_p_create_log_tab;  
143.     dbms_output.put_line('程序已经自动完成了PART_TAB_LOG表的建立');  
144.   end p_create_log_tab;  
145.     
146.   
147.   procedure p_insert_log(p_tab in varchar2,v_deal_model in varchar2,v_sql_text in varchar2,v_remark in varchar2 default null,v_exec_order1 number default 0,v_exec_order2 number default 0)  
148.   as  
149.   /*  
150.    功能：记录日志，将各步骤中拼的SQL保存起来  
151.    难点：因为part_tab_log是程序自己建的，所以需要改造为如下动态SQL，构造中要考虑单引号的替换  
152.   */  
153.   v_sql_p_insert_log         varchar2(4000);  
154.   v_sql_text1                varchar2(4000);  
155.   begin  
156.      --insert into PART_TAB_LOG (TAB_NAME ,deal_model,SQL_TEXT,DEAL_TIME,REMARK,exec_order1,exec_order2)  values (p_tab,v_deal_model,v_SQL_text,SYSDATE,v_remark,v_exec_order1,v_exec_order2);  
157.    v_sql_text1 := REPLACE(v_sql_text,'''',''''''); ---先将字符串中的单引号用双引号代替  
158.    v_sql_p_insert_log:= 'insert into PART_TAB_LOG (TAB_NAME ,deal_model,SQL_TEXT,DEAL_TIME,REMARK,exec_order1,exec_order2)  values ('||''''||p_tab||''''||','||''''||v_deal_model||''''||','||''''||v_SQL_text1||''''||','||''''||SYSDATE||''''||','||''''||v_remark||''''||','||v_exec_order1||','||v_exec_order2||')';  
159.      
160.    --DBMS_OUTPUT.PUT_LINE( v_sql_p_insert_log);--调试使用  
161.   /*  
162.    仅仅调试使用，有的时候由于ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line导致DBMS_OUTPUT不可用，所以第一建立一个仅有一个VARCHAR2(4000)字段的表，将字符串插入其中来调试  
163.    INSERT INTO TEST VALUES (v_sql_p_insert_log);  
164.    COMMIT;  
165.    */  
166.    execute immediate v_sql_p_insert_log;  
167.     
168.    commit;  
169.   end p_insert_log;  
170.   
171.   
172.   
173.   
174.   procedure p_if_judge(v_sql in varchar2,p_deal_flag in number )  
175.   as  
176.   /*  
177.    功能：在获取到V_SQL的动态SQL后，是否EXECUTE IMMEDIATE执行前做一个判断，  
178.          根据p_deal_flag的入参而定，0为不执行，非0为执行  
179.   */  
180.   begin  
181.    if p_deal_flag=0 then  
182.     null;  
183.    else  
184.       execute immediate(v_sql);  
185.    end if;  
186.   end p_if_judge;  
187.   
188.   
189.   
190.   
191.   
192.   procedure p_rename_001 (p_tab in varchar2)  
193.   as  
194.   /*  
195.    功能：将原表重命名为_yyyymmdd格式的表名  
196.    完善点： 要考虑RENMAE的目标表已存在的情况，先做判断  
197.   */  
198.   V_CNT_RE_TAB  NUMBER(9) :=0;  
199.   v_sql_p_rename         varchar2(4000);  
200.   begin  
201.   SELECT  COUNT(*)   INTO V_CNT_RE_TAB FROM user_objects where object_name=UPPER(P_TAB||'_'||YYYYMMDD);  
202.     if V_CNT_RE_TAB=0 then  
203.            v_sql_p_rename:= 'rename '||P_TAB ||' to '||P_TAB||'_'||YYYYMMDD;  
204.        --   DBMS_OUTPUT.PUT_LINE(v_sql_p_rename);--调试使用  
205.            p_insert_log(p_tab,'P_RENAME',v_sql_p_rename,'完成原表的重命名，改为_YYYYMMDD形式',1);  
206.            execute immediate(v_sql_p_rename); --这里无需做判断，rename动作真实完成！如果后续只是为生成脚本而不是真实执行分区操作，最后再把这个表RENAME回去！  
207.     ELSE    
208.            RAISE_APPLICATION_ERROR(-20066,'备份表'||P_TAB||'_'||YYYYMMDD||'已存在,请先删除或重命名该备份表后再继续执行！');  
209.          --  DBMS_OUTPUT.PUT_LINE('备份表'||P_TAB||'_'||YYYYMMDD||'已存在');  
210.     end if;  
211.     DBMS_OUTPUT.PUT_LINE('操作步骤1(备份原表)-------将'||p_tab ||' 表RENMAE成 '||p_tab||'_'||YYYYMMDD||'，并删除其约束索引等');  
212.   end p_rename_001;  
213.   
214.   
215.   
216.   
217.   
218.   procedure p_ctas_002 (p_tab in varchar2,  
219.                         p_struct_only  in number,  
220.                         p_deal_flag in number,  
221.                         p_part_colum in varchar2,  
222.                         p_parallel in number default 4,  
223.                         p_tablespace IN VARCHAR2)  
224.   as  
225.    /*  
226.    功能：用CREATE TABLE AS SELECT 的方式从RENAME的_yyyymmdd表中新建出一个只有MAXVALUE的初步分区表  
227.    完善点：要考虑并行，nologging 的提速方式，也要考虑最终将NOLOGGING和PARALLEL恢复成正常状态  
228.   */  
229.   v_sql_p_ctas         varchar2(4000);  
230.   begin  
231.        v_sql_p_ctas:='create table '||p_tab   
232.        ||' partition by range ( '||p_part_colum||' ) ('  
233.        || ' partition P_MAX  values less than (maxvalue))'||  
234.        ' nologging parallel 4  tablespace '||p_tablespace||  
235.        ' as select /*+parallel(t,'||p_parallel||')*/ *'||  
236.        ' from '|| P_TAB||'_'||YYYYMMDD ;  
237.       if p_struct_only=0 then  
238.         v_sql_p_ctas:=v_sql_p_ctas ||' where 1=2';  
239.       else  
240.         v_sql_p_ctas:=v_sql_p_ctas ||' where 1=1';  
241.         end if;  
242.        --DBMS_OUTPUT.PUT_LINE(v_sql_p_ctas);--调试使用  
243.        p_insert_log(p_tab,'p_ctas',v_sql_p_ctas,'完成CTAS建初步分区表',2,1);  
244.        p_if_judge(v_sql_p_ctas,p_deal_flag);  
245.   
246.        v_sql_p_ctas:='alter table '|| p_tab ||' logging';  
247.        p_insert_log(p_tab,'p_ctas',v_sql_p_ctas,'将新分区表修改回LOGGING属性',2,2);  
248.        p_if_judge(v_sql_p_ctas,p_deal_flag);  
249.   
250.        v_sql_p_ctas:='alter table '|| p_tab || ' noparallel';  
251.        p_insert_log(p_tab,'p_ctas',v_sql_p_ctas,'将新分区表修改回NOPARALLEL属性',2,3);  
252.        p_if_judge(v_sql_p_ctas,p_deal_flag);  
253.     DBMS_OUTPUT.PUT_LINE('操作步骤2(建分区表)-------通过CTAS的方式从 '||p_tab||'_'||YYYYMMDD|| ' 中新建'||p_tab ||'表，完成初步分区改造工作');  
254.    end p_ctas_002;  
255.   
256.   
257.   
258.   
259.   
260.   procedure p_split_part_003 (p_tab in varchar2,  
261.                               p_deal_flag in number,  
262.                               p_part_nums in number default 24,  
263.                               p_tab_tablespace IN VARCHAR2)  
264.   as  
265.   /*  
266.    功能：将CREATE TABLE AS SELECT 的方式新建出一个只有MAXVALUE的初步分区表进行SPLIT，  
267.    按月份进行切分，默认p_part_nums产生24个分区，构造2年的分区表，第一个分区为当前月的  
268.    上一个月  
269.   */  
270.   v_first_day   date;  
271.   v_next_day    date;  
272.   v_prev_day    date;  
273.   v_sql_p_split_part         varchar2(4000);  
274.   begin  
275.    select to_date(to_char(sysdate, 'yyyymm') || '01', 'yyyymmdd')  
276.           into v_first_day  
277.           from dual;  
278.     for i in 1 .. p_part_nums loop  
279.        select add_months(v_first_day, i) into v_next_day from dual;  
280.        select add_months(v_next_day, -1) into v_prev_day from dual;  
281.        v_sql_p_split_part := 'alter table '||p_tab||' split partition p_MAX at ' ||  
282.                '(to_date(''' || to_char(v_next_day, 'yyyymmdd') ||  
283.                ''',''yyyymmdd''))' || 'into (partition PART_' ||  
284.                to_char(v_prev_day, 'yyyymm') || ' tablespace '|| p_tab_tablespace||' ,partition p_MAX)';  
285.       -- DBMS_OUTPUT.PUT_LINE(v_sql_p_split_part);--调试使用  
286.        p_insert_log(p_tab,'p_split_part',v_sql_p_split_part,'分区表完成分区SPLIT工作',3,i);  
287.        p_if_judge(v_sql_p_split_part,p_deal_flag);  
288.     end loop;  
289.   DBMS_OUTPUT.PUT_LINE('操作步骤3(分区操作)-------对新建的'||p_tab ||'分区表完成分区SPLIT工作');  
290.   end p_split_part_003;  
291.   
292.   
293.   
294.   
295.   
296.   procedure p_tab_comments_004  (p_tab in varchar2,p_deal_flag in number)  
297.   as  
298.   /*  
299.    功能：从_YYYYMMDD备份表中得到表和字段的注释，为新分区表的表名和字段增加注释  
300.   */  
301.   v_sql_p_tab_comments         varchar2(4000);  
302.   v_cnt number;  
303.   begin  
304.    select count(*) into v_cnt from user_tab_comments where table_name=UPPER(P_TAB)||'_'||YYYYMMDD AND COMMENTS IS NOT NULL;  
305.    if v_cnt>0 then   
306.     for i in (select * from user_tab_comments where table_name=UPPER(P_TAB)||'_'||YYYYMMDD AND COMMENTS IS NOT NULL) loop  
307.       v_sql_p_tab_comments:='comment on table '||p_tab||' is '|| ''''||i.COMMENTS||'''';  
308.      -- DBMS_OUTPUT.PUT_LINE(v_sql_p_deal_tab_comments);--调试使用  
309.       p_insert_log(p_tab,'p_deal_comments',v_sql_p_tab_comments,'将新分区表的表的注释加上',4,1);  
310.       p_if_judge(v_sql_p_tab_comments,p_deal_flag);  
311.     end loop;  
312.      DBMS_OUTPUT.PUT_LINE('操作步骤4(表的注释)-------对'||p_tab ||'表增加表名的注释内容');  
313.     ELSE   
314.      DBMS_OUTPUT.PUT_LINE('操作步骤4(表的注释)-------'||UPPER(P_TAB)||'_'||YYYYMMDD ||'并没有表注释!');  
315.     END IF;  
316.   end p_tab_comments_004;  
317.   
318.   
319.   procedure p_col_comments_005  (p_tab in varchar2,p_deal_flag in number)  
320.   as  
321.   /*  
322.    功能：从_YYYYMMDD备份表中得到表和字段的注释，为新分区表的表名和字段增加注释  
323.   */  
324.   v_sql_p_col_comments         varchar2(4000);  
325.   v_cnt number;  
326.   begin  
327.     select count(*) into v_cnt from user_col_comments where table_name=UPPER(P_TAB)||'_'||YYYYMMDD AND COMMENTS IS NOT NULL;  
328.     if v_cnt>0 then   
329.     for i in (select * from user_col_comments where table_name=UPPER(P_TAB)||'_'||YYYYMMDD AND COMMENTS IS NOT NULL) loop  
330.       v_sql_p_col_comments:='comment on column '||p_tab||'.'||i.COLUMN_NAME||' is '|| ''''||i.COMMENTS||'''';  
331.       p_insert_log(p_tab,'p_deal_col_comments',v_sql_p_col_comments,'将新分区表的列的注释加上',5,1);  
332.       p_if_judge(v_sql_p_col_comments,p_deal_flag);  
333.     end loop;  
334.      DBMS_OUTPUT.PUT_LINE('操作步骤5(列的注释)-------对'||p_tab ||'表增加列名及字段的注释内容');  
335.     else   
336.      DBMS_OUTPUT.PUT_LINE('操作步骤5(列的注释)-------'||UPPER(P_TAB)||'_'||YYYYMMDD ||'并没有列注释!');  
337.     end if;  
338.   end p_col_comments_005;  
339.   
340.   
341.   
342.   procedure p_defau_and_null_006 (p_tab in varchar2,p_deal_flag in number)  
343.   as  
344.   /*  
345.    功能：从_YYYYMMDD备份表中得到原表的DEFAULT值，为新分区表的表名和字段增加DEFAULT值  
346.   */  
347.   v_sql_defau_and_null        varchar2(4000);  
348.   v_cnt  number;  
349.   begin  
350.   select count(*) into v_cnt  from user_tab_columns where table_name=UPPER(P_TAB)||'_'||YYYYMMDD and data_default is not null;  
351.     if v_cnt>0 then   
352.     for i in (select * from user_tab_columns where table_name=UPPER(P_TAB)||'_'||YYYYMMDD and data_default is not null) loop  
353.      v_sql_defau_and_null:='alter table '||p_tab||' modify '||i.COLUMN_NAME ||' default ' ||i.data_default;  
354.      p_insert_log(p_tab,'p_deal_default',v_sql_defau_and_null,'将新分区表的默认值加上',6);  
355.      p_if_judge(v_sql_defau_and_null,p_deal_flag);  
356.     end loop;  
357.      DBMS_OUTPUT.PUT_LINE('操作步骤6(空和默认)-------对'||p_tab ||'表完成默认DEFAULT值的增加');  
358.     else  
359.      DBMS_OUTPUT.PUT_LINE('操作步骤6(空和默认)-------'||UPPER(P_TAB)||'_'||YYYYMMDD ||'并没有DEFAULT或NULL值!');  
360.     end if;  
361.       
362.   end p_defau_and_null_006;  
363.   
364.   
365.   
366.   
367.   procedure p_check_007 (p_tab in varchar2,p_deal_flag in number)  
368.   as  
369.   /*  
370.    功能：从_YYYYMMDD备份表中得到原表的CHECK值，为新分区表增加CHECK值  
371.    另注：  
372.     user_constraints已经包行了非空的判断，可以略去如下类似的从user_tab_columns获取非空判断的代码编写来判断是否  
373.     for i in (select * from user_tab_columns where table_name=UPPER(P_TAB)||'_'||YYYYMMDD and nullable='N') loop  
374.      v_sql:='alter table '||p_tab||' modify '||i.COLUMN_NAME ||' not null';  
375.   */  
376.    v_sql_p_check         varchar2(4000);  
377.    v_cnt number;  
378.   begin  
379.   select count(*) into v_cnt from user_constraints where table_name=UPPER(P_TAB)||'_'||YYYYMMDD and constraint_type='C';  
380.    if v_cnt>0 then   
381.    for i in (select * from user_constraints where table_name=UPPER(P_TAB)||'_'||YYYYMMDD and constraint_type='C') loop  
382.       v_sql_p_check :='alter table '||P_TAB||'_'||YYYYMMDD ||' drop constraint ' || I.CONSTRAINT_NAME;  
383.       p_insert_log(p_tab,'p_deal_check',v_sql_p_check ,'将备份出来的原表的CHECK删除',7,1);  
384.       p_if_judge(v_sql_p_check ,p_deal_flag);  
385.       v_sql_p_check :='alter table '||p_tab||' ADD CONSTRAINT '||I.CONSTRAINT_NAME||' CHECK ('||I.SEARCH_CONDITION ||')' ;  
386.       p_insert_log(p_tab,'p_deal_check',v_sql_p_check ,'将新分区表的CHECK加上',7,2);  
387.       p_if_judge(v_sql_p_check ,p_deal_flag);  
388.     end loop;  
389.      DBMS_OUTPUT.PUT_LINE('操作步骤7(check约束)-------对'||p_tab ||'完成CHECK的约束');  
390.     else   
391.      DBMS_OUTPUT.PUT_LINE('操作步骤7(check约束)-----'||UPPER(P_TAB)||'_'||YYYYMMDD ||'并没有CHECK!');  
392.     end if;  
393.       
394.   end p_check_007;  
395.   
396.    procedure p_index_008 (p_tab in varchar2,p_deal_flag in number,p_idx_tablespace IN VARCHAR2)  
397.   as  
398.   /*  
399.    功能：从_YYYYMMDD备份表中得到原表的索引信息，为新分区表增加普通索引（唯一和非唯一索引，函数索引暂不考虑），并删除旧表索引  
400.    难点：需要考虑联合索引的情况  
401.   */  
402.    v_sql_p_normal_idx         varchar2(4000);  
403.    v_cnt number;  
404.      
405.   begin  
406.      SELECT count(*) into v_cnt  
407.       from user_indexes  
408.       where table_name = UPPER(P_TAB)||'_'||YYYYMMDD   
409.       and index_type='NORMAL' AND INDEX_NAME NOT IN (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS);  
410.   if v_cnt>0 then   
411.     for i in  
412.     (  
413.       WITH T AS   
414.       (  
415.       select C.*,I.UNIQUENESS  
416.       from user_ind_columns C  
417.       ,(SELECT DISTINCT index_name,UNIQUENESS  
418.       from user_indexes  
419.       where table_name = UPPER(P_TAB)||'_'||YYYYMMDD   
420.       and index_type='NORMAL'   
421.       AND INDEX_NAME NOT IN  
422.       (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS)  
423.       ) i  
424.       where c.index_name = i.index_name  
425.       )  
426.       SELECT INDEX_NAME,TABLE_NAME,UNIQUENESS, MAX(substr(sys_connect_by_path(COLUMN_NAME, ','), 2)) str ---考虑组合索引的情况  
427.       FROM (SELECT column_name,INDEX_NAME,TABLE_NAME, row_number() over(PARTITION BY INDEX_NAME,TABLE_NAME ORDER BY COLUMN_NAME) rn  
428.       ,UNIQUENESS  
429.       FROM T) t  
430.       START WITH rn = 1  
431.       CONNECT BY rn = PRIOR rn + 1  
432.       AND INDEX_NAME = PRIOR INDEX_NAME  
433.       GROUP BY INDEX_NAME,T.TABLE_NAME,UNIQUENESS  
434. ) loop  
435.       v_sql_p_normal_idx:= 'drop index '||i.index_name;  
436.       p_insert_log(p_tab,'p_deal_normal_idx',v_sql_p_normal_idx,'删除原表索引',8,1);  
437.       p_if_judge(v_sql_p_normal_idx,p_deal_flag);  
438.       DBMS_OUTPUT.PUT_LINE('操作步骤8(处理索引)-------将'||i.table_name ||'的'||i.str||'列的索引'||i.index_name||'删除完毕');  
439.        if i.uniqueness='UNIQUE' then  
440.          v_sql_p_normal_idx:='CREATE UNIQUE INDEX ' || i.INDEX_NAME || ' ON '|| p_tab||'('||i.STR||')'||' tablespace '||p_idx_tablespace ;  
441.         elsif i.uniqueness='NONUNIQUE' then  
442.          v_sql_p_normal_idx:='CREATE  INDEX ' || i.INDEX_NAME || ' ON '|| p_tab ||'('||i.STR||')'||' LOCAL tablespace '||p_idx_tablespace ;  
443.         end if;  
444.       p_insert_log(p_tab,'p_deal_normal_idx',v_sql_p_normal_idx,'将新分区表的索引加上',8,2);  
445.       p_if_judge(v_sql_p_normal_idx,p_deal_flag);  
446.       DBMS_OUTPUT.PUT_LINE('操作步骤8(处理索引)-------对'||p_tab ||'新分区表'||i.STR||'列增加索引'||i.index_name);  
447.     end loop;  
448.    else   
449.      DBMS_OUTPUT.PUT_LINE('操作步骤8(处理索引)-------'||UPPER(P_TAB)||'_'||YYYYMMDD ||'并没有索引(索引模块并不含主键判断)!');  
450.    end if;  
451.     
452.   end p_index_008;  
453.     
454.   procedure p_pk_009 (p_tab in varchar2,p_deal_flag in number,p_idx_tablespace IN VARCHAR2)  
455.   as  
456.   /*  
457.    功能：从_YYYYMMDD备份表中得到原表的主键信息，为新分区表增加主键值，并删除旧表主键  
458.    难点：需要考虑联合主键的情况  
459.   */  
460.   v_sql_p_pk         varchar2(4000);  
461.   v_cnt              number;  
462.     
463.   begin  
464.   SELECT count(*) into v_cnt  
465.           from USER_IND_COLUMNS  
466.           where index_name in (select index_name  
467.                                 from sys.user_constraints t  
468.                                WHERE TABLE_NAME =UPPER(P_TAB)||'_'||YYYYMMDD  
469.                                  and constraint_type = 'P');  
470.   if v_cnt>0 then   
471.     for i in  
472.     (WITH T AS  
473.      (SELECT INDEX_NAME,TABLE_NAME,COLUMN_NAME  
474.           from USER_IND_COLUMNS  
475.           where index_name in (select index_name  
476.                                 from sys.user_constraints t  
477.                                WHERE TABLE_NAME =UPPER(P_TAB)||'_'||YYYYMMDD  
478.                                  and constraint_type = 'P')  
479.       )  
480.      SELECT INDEX_NAME,TABLE_NAME, MAX(substr(sys_connect_by_path(COLUMN_NAME, ','), 2)) str  
481.     FROM (SELECT  column_name,INDEX_NAME,TABLE_NAME, row_number() over(PARTITION BY INDEX_NAME,TABLE_NAME ORDER BY COLUMN_NAME) rn  
482.           FROM T) t  
483.     START WITH rn = 1  
484.     CONNECT BY rn = PRIOR rn + 1  
485.     AND INDEX_NAME = PRIOR INDEX_NAME  
486.     GROUP BY INDEX_NAME,T.TABLE_NAME  
487. ) loop  
488.       v_sql_p_pk:= 'alter table '||i.table_name||' drop constraint '||i.index_name|| ' cascade';  
489.       p_insert_log(p_tab,'p_deal_pk',v_sql_p_pk,'将备份出来的原表的主键删除',9,1);  
490.       p_if_judge(v_sql_p_pk,p_deal_flag);  
491.       DBMS_OUTPUT.PUT_LINE('操作步骤9(处理主键)-------将备份出来的原表'||i.table_name||'的'||i.str||'列的主键'||i.index_name||'删除完毕！'); ---放在FOR循环中效率没问题，因为主键只有一个，只会循环一次  
492.       v_sql_p_pk:='ALTER TABLE '||p_tab||' ADD CONSTRAINT '||I.INDEX_NAME||' PRIMARY KEY ('||I.STR||')' ||' using index tablespace '||p_idx_tablespace ;  
493.       p_insert_log(p_tab,'p_deal_pk',v_sql_p_pk,'将新分区表的主键加上',9,2);  
494.       p_if_judge(v_sql_p_pk,p_deal_flag);  
495.       DBMS_OUTPUT.PUT_LINE('操作步骤9(处理主键)-------对'||p_tab ||'表的'||i.str||'列增加主键'||i.index_name); ---放在FOR循环中效率，因为主键只有一个，只会循环一次  
496.     end loop;  
497.    else   
498.       DBMS_OUTPUT.PUT_LINE('操作步骤9(处理主键)-------'||UPPER(P_TAB)||'_'||YYYYMMDD ||'并没有主键!');  
499.    end if;  
500.     
501.   end p_pk_009;  
502.   
503.   
504.   
505.   procedure p_constraint_010 (p_tab in varchar2,p_deal_flag in number)  
506.   as  
507.   /*  
508.    功能：从_YYYYMMDD备份表中得到原表的约束，为新分区表增加约束值，并删除旧表约束  
509.    难点：需要考虑联合外键REFERENCE 联合主键的情况  
510.   */  
511.   v_sql_p_constraint         varchar2(4000);  
512.   v_cnt  number;  
513.   begin  
514.   SELECT count(*) into v_cnt  FROM user_constraints where table_name=UPPER(P_TAB)||'_'||YYYYMMDD AND CONSTRAINT_TYPE='R';  
515.   if v_cnt>0 then  
516.     for i in  
517.     (with t1 as (  
518.       SELECT  /*+no_merge */   
519.              POSITION  
520.             ,t.owner,t.constraint_name as constraint_name1,t.table_name as table_name1 ,t.column_name as column_name1  FROM user_cons_columns t where constraint_name in  
521.       (  
522.       SELECT CONSTRAINT_NAME FROM user_constraints where table_name=UPPER(P_TAB)||'_'||YYYYMMDD AND CONSTRAINT_TYPE='R'  
523.       )  
524.       ),  
525.     t2 as (  
526.       SELECT  /*+no_merge */   
527.              t.POSITION  
528.             ,c.constraint_name constraint_name1  
529.             ,t.constraint_name as constraint_name2,t.table_name as table_name2 ,t.column_name as column_name2  
530.             ,MAX(t.POSITION) OVER (PARTITION BY c.constraint_name) MAX_POSITION   
531.         FROM user_cons_columns t   
532.             ,user_constraints c  
533.         WHERE c.table_name = UPPER(P_TAB)||'_'||YYYYMMDD  
534.               AND t.constraint_name = c.r_constraint_name  
535.               AND c.constraint_type='R'  
536.       ),  
537.     t3 AS (  
538.       SELECT t1.*   
539.             ,t2.constraint_name2  
540.             ,t2.table_name2   
541.             ,t2.column_name2  
542.             ,t2.max_position   
543.       FROM t1,t2   
544.       WHERE t1.constraint_name1 = t2.constraint_name1 AND t1.position=t2.position)  
545.     select t3.*,SUBSTR(SYS_CONNECT_BY_PATH(column_name1,','),2) as FK,SUBSTR(SYS_CONNECT_BY_PATH(column_name2,','),2) AS PK from t3   
546.      WHERE POSITION=MAX_POSITION  
547.      START WITH position=1  
548.      CONNECT BY constraint_name1 = PRIOR constraint_name1  
549.                AND position = PRIOR position+1) loop  
550.     v_sql_p_constraint:= 'alter table '||p_tab||'_'||YYYYMMDD  ||' drop constraint '||i.constraint_name1;  
551.     p_insert_log(p_tab,'p_deal_constraint',v_sql_p_constraint,'删除原表FK外键' ,10,1);  
552.     p_if_judge(v_sql_p_constraint,p_deal_flag);  
553.     DBMS_OUTPUT.PUT_LINE('操作步骤10(处理外键)------将备份出来的'||i.table_name1||'表的'||i.column_name1||'列的外键'||i.constraint_name1||'删除完毕!');  
554.     v_sql_p_constraint:= 'alter table ' || p_tab ||' add constraint '||i.constraint_name1 || ' foreign key ( '  
555.     ||i.fk||') references '||i.table_name2|| ' ('||i.pk||' )';  
556.     p_insert_log(p_tab,'p_deal_constraint',v_sql_p_constraint,'将新分区表的外键加上',10,2);  
557.     p_if_judge(v_sql_p_constraint,p_deal_flag);  
558.     DBMS_OUTPUT.PUT_LINE('操作步骤10(处理外键)------对'||p_tab ||'表的'||i.column_name1||'列增加外键'||i.constraint_name1);  
559.     end loop;  
560.    else   
561.     DBMS_OUTPUT.PUT_LINE('操作步骤10(处理外键)------'||UPPER(P_TAB)||'_'||YYYYMMDD ||'并没有外键!');  
562.    end if;  
563.       
564.   end p_constraint_010;  
565.   
566.    
567.   procedure p_main (p_tab in varchar2,                 --需要进行分区改造的表名  
568.                     p_deal_flag in number default 0,   --0为不执行，只将脚本记录进part_tab_log的sql_text字段中，1为不仅记录，而且执行！  
569.                     p_parallel in number default 4,    --并行度设置  
570.                     p_part_colum in varchar2,         --需要分区的列（时间范围分区）  
571.                     p_part_nums in number default 24,  --需要分区的分区数  
572.                     p_struct_only in number default 0, --新表是否是只建表结构不导入数据，0为只建结构，非0如1等值，为导数据  
573.                     p_tab_tablespace IN VARCHAR2,      --分区的数据表空间  
574.                     p_idx_tablespace IN VARCHAR2)      --分区的索引表空间  
575.   as  
576.   /*  
577.    功能：主程序，调用前面的各个子程序  
578.    完善点：1.既要考虑只建新表结构的情况，也要考虑建新分区表同时将数据导入的情况；  
579.            2.既要考虑只记录日志将来手工执行的情况，也要考虑直接执行的情况  
580.            3.分区字段必须是时间字段，因为本程序主要是针对时间来做范围分区的。  
581.   */  
582.   v_sql_p_main               varchar2(4000);  
583.   v_data_type                varchar2(40);  
584.   v_cnt_tab_log              varchar2(40);  
585.   begin  
586.    
587.       select DATA_TYPE into v_data_type from user_tab_columns where table_name=upper(p_tab) and column_name=upper(p_part_colum);--用来后续判断分区列是否是时间字段（注意大写）  
588.       SELECT COUNT(*) INTO v_cnt_tab_log FROM USER_TABLES WHERE TABLE_NAME='PART_TAB_LOG';  
589.        IF v_data_type ='DATE' THEN ---开始对输入分区列是否是时间类型进行判断  
590.         if v_cnt_tab_log=0 then   
591.         p_create_log_tab;---日志表不存在则创建日志表  
592.         end if;  
593.         v_sql_p_main :='delete from  part_tab_log where tab_name='||''''||p_tab||'''';  
594.         --DBMS_OUTPUT.PUT_LINE(v_sql);--调试使用  
595.         execute immediate v_sql_p_main ;  
596.         commit;  
597.         p_rename_001         (p_tab); ---将原表先RENAME为YYYYMMDD的后缀名的表  
598.         p_ctas_002           (p_tab, p_struct_only ,p_deal_flag, p_part_colum,p_parallel ,p_tab_tablespace ); ---CTAS建立除部分分区的分区表  
599.         p_split_part_003     (p_tab,p_deal_flag,p_part_nums ,p_tab_tablespace); ---将分区表分区完整  
600.         p_tab_comments_004   (p_tab,p_deal_flag);  --增加分区表的表注释  
601.         p_col_comments_005   (p_tab,p_deal_flag);  --增加分区表的列注释  
602.         p_defau_and_null_006 (p_tab,p_deal_flag);  --增加分区表的默认值  
603.         p_check_007          (p_tab,p_deal_flag);  --增加分区表的CHECK  
604.         p_index_008          (p_tab,p_deal_flag,p_idx_tablespace); --增加分区表的索引  
605.         p_pk_009             (p_tab,p_deal_flag,p_idx_tablespace);  --增加分区表的主键  
606.         p_constraint_010     (p_tab,p_deal_flag );---增加外键  
607.         if p_deal_flag=0 then  
608.         DBMS_OUTPUT.PUT_LINE('请特别留意!!!，以上只是对'||p_tab ||'进行生成脚本动作，未真正执行分区改造等执行动作，表也未真正执行RENAME动作，具体脚本可从part_tab_log中获取从而手工执行,具体如下：');  
609.         dbms_output.put_Line('select sql_text'||'||'||''';'''||' from part_tab_log t where tab_name='||''''||p_tab||''''||' order by exec_order1,exec_order2;');    
610.         DBMS_OUTPUT.PUT_LINE('如果需要真正完成执行动作，请将pkg_deal_part_tab.p_main的 p_deal_flag参数值设置为非0值，如1');  
611.         execute immediate 'rename '||P_TAB||'_'||YYYYMMDD ||' to '||P_TAB; --即便只获取脚本而无需执行，前面RENAME的动作也必须真实完成，因为很多数据字典信息要取自_YYYYMM表的信息，因此前面必须执行，只好在这里最后再RENAME替换回去  
612.         ELSE  
613.         DBMS_OUTPUT.PUT_LINE('操作步骤结束----对'||p_tab ||'已经真正完成了操作分区改造等执行步骤，脚本可参看part_tab_log表');         
614.         end IF;  
615.        ELSE   
616.         RAISE_APPLICATION_ERROR(-20066,'分区字段'||p_part_colum||'必须是时间字段');  
617.     END IF;  
618.    end p_main;  
619.   
620.    
621.   
622.     
623.   procedure p_do   (p_tab            in varchar2,                 --需要进行分区改造的表名  
624.                     p_deal_flag      in number   default 0,       --0为不执行，只将脚本记录进part_tab_log的sql_text字段中，1为不仅记录，而且执行！  
625.                     p_parallel       in number   default 4,       --并行度设置  
626.                     p_part_colum     in varchar2,                 --需要分区的列（时间范围分区）  
627.                     p_part_nums      in number   default 24,      --需要分区的分区数  
628.                     p_struct_only    in number   default 0,       --新表是否是只建表结构不导入数据，0为只建结构，非0如1等值，为导数据  
629.                     p_tab_tablespace IN VARCHAR2,                 --分区的数据表空间  
630.                     p_idx_tablespace IN VARCHAR2)                 --分区的索引表空间  
631.   as  
632.   /*  
633.    功能：将主程序p_main做一层封装，将输入表名，输入数据表空间和输入索引表空间这三个参数是否  
634.    正确做了判断。由于p_main里调用的001-010这10个模块是DDL语句，不能在一个事务中，所以尽量考虑  
635.    逻辑在前面判断正确后再执行这个P_MAIN  
636.   */  
637.   
638.   v_cnt_p_tab                number;  
639.   v_cnt_tab_tablespace       varchar2(40);  
640.   v_cnt_idx_tablespace       varchar2(40);  
641.   begin  
642.    select count(*) into v_cnt_p_tab from user_tables where table_name=upper(p_tab);  
643.   select count(*) into v_cnt_tab_tablespace from user_tablespaces where tablespace_name=upper(p_tab_tablespace);  
644.   select count(*) into v_cnt_idx_tablespace from user_tablespaces where tablespace_name=upper(p_idx_tablespace);  
645.     
646.   if v_cnt_p_tab>0  then   
647.     if  v_cnt_tab_tablespace>0 then   
648.        if v_cnt_idx_tablespace>0 then  
649.          p_main(p_tab,p_deal_flag, p_parallel,p_part_colum,p_part_nums, p_struct_only,p_tab_tablespace,p_idx_tablespace);    
650.        else  
651.          RAISE_APPLICATION_ERROR(-20067,'输入的索引表空间'||p_idx_tablespace||'不存在，请仔细检查输入的索引表空间名');  
652.        end if;   
653.     else  
654.        RAISE_APPLICATION_ERROR(-20068,'输入的数据表空间'||p_tab_tablespace||'不存在，请仔细检查输入的数据表空间名');  
655.     end if;  
656.   else   
657.      RAISE_APPLICATION_ERROR(-20069,'输入参数的表名'||p_tab||'不存在，请仔细检查表名');  
658.   end if;  
659. end p_do;  
660.   
661.   
662.   
663. end pkg_deal_part_tab;  
664. /  

-- 增加分区ALTER TABLE RANGE_PART_TAB ADD PARTITION P_MAX VALUES LESS THAN (MAXVALUE);

-- 重建索引ALTER INDEX indexname REBUILD ONLINE;

--将外键未建索引的情况列出 select table_name,       constraint_name,       cname1 || nvl2(cname2, ',' || cname2, null) ||       nvl2(cname3, ',' || cname3, null) ||       nvl2(cname4, ',' || cname4, null) ||       nvl2(cname5, ',' || cname5, null) ||       nvl2(cname6, ',' || cname6, null) ||       nvl2(cname7, ',' || cname7, null) ||       nvl2(cname8, ',' || cname8, null) columns  from (select b.table_name,               b.constraint_name,               max(decode(position, 1, column_name, null)) cname1,               max(decode(position, 2, column_name, null)) cname2,               max(decode(position, 3, column_name, null)) cname3,               max(decode(position, 4, column_name, null)) cname4,               max(decode(position, 5, column_name, null)) cname5,               max(decode(position, 6, column_name, null)) cname6,               max(decode(position, 7, column_name, null)) cname7,               max(decode(position, 8, column_name, null)) cname8,               count(*) col_cnt          from (select substr(table_name, 1, 30) table_name,                       substr(constraint_name, 1, 30) constraint_name,                       substr(column_name, 1, 30) column_name,                       position                  from user_cons_columns) a,               user_constraints b         where a.constraint_name = b.constraint_name           and b.constraint_type = 'R'         group by b.table_name, b.constraint_name) cons where col_cnt > ALL (select count(*)          from user_ind_columns i         where i.table_name = cons.table_name           and i.column_name in (cname1, cname2, cname3, cname4, cname5,                cname6, cname7, cname8)           and i.column_position <= cons.col_cnt         group by i.index_name);

--当前用户下,哪些表的索引个数字超过5个的 select table_name, count(*) cnt  from user_indexes group by table_namehaving count(*) >= 5order by cnt desc ;

--针对普通表（大于2GB的表未建任何索引）select segment_name, bytes/1024/1024/1024 "GB", blocks, tablespace_name  from user_segments where segment_type = 'TABLE'   and segment_name not in (select table_name from user_indexes)   and bytes / 1024 / 1024 / 1024 >= 2 order by GB desc;      --针对分区表（大于2GB的分区表未建任何索引）--无论是建了局部索引还是全局索引，在user_indexes都可以查到，只是status不一样。select segment_name, sum(bytes)/1024/1024/1024 "GB", sum(blocks)  from user_segments  where segment_type = 'TABLE PARTITION'   and segment_name not in (select table_name from user_indexes)   group by segment_name   having sum(bytes)/1024/1024/1024>=2 order by GB desc;   --注：无论是建了局部索引还是全局索引，在user_indexes都可以查到，只是status不一样。 

--当前用户下，哪些索引的聚合因子特别大。select a.table_name,       a.index_name,       a.blevel,       a.leaf_blocks,       b.num_rows,       b.blocks,       a.clustering_factor,       trunc(a.clustering_factor / b.num_rows,2) cluster_rate  from user_indexes a, user_tables b where a.table_name = b.table_name     and a.clustering_factor is not null     and a.clustering_factor / b.num_rows>0.9 order by cluster_rate desc  ;

--失效-普通索引 select t.index_name,       t.table_name,       blevel,       t.num_rows,       t.leaf_blocks,       t.distinct_keys  from user_indexes twhere status = 'UNUSABLE' ;--失效-分区索引select t1.blevel,       t1.leaf_blocks,       t1.INDEX_NAME,       t2.table_name,       t1.PARTITION_NAME,       t1.STATUS  from user_ind_partitions t1, user_indexes t2where t1.index_name = t2.index_name   and t1.STATUS = 'UNUSABLE';

--当前用户下，哪些表的组合索引与单列索引存在交叉的情况。select table_name, trunc(count(distinct(column_name)) / count(*),2) cross_idx_rate  from user_ind_columns group by table_namehaving count(distinct(column_name)) / count(*) < 1order by cross_idx_rate desc;

--当前用户下，哪些索引的高度比较高，大于5层（LEVEL=4)select table_name,       index_name,       blevel,       leaf_blocks,       num_rows,       last_analyzed,       degree,       status  from user_indexes  where  blevel>=4;

--当前用户下，哪些索引被设置了并行。select table_name,       index_name,       blevel,       leaf_blocks,       num_rows,       last_analyzed,       degree,       status  from user_indexes  where  degree>1;

--当前用户下，哪些外键的约束失效了。SELECT TABLE_NAME,       CONSTRAINT_NAME,       STATUS,       CONSTRAINT_TYPE,       R_CONSTRAINT_NAME  FROM USER_CONSTRAINTS WHERE STATUS='DISABLED';

-- 杀掉一个进程alter system kill session 'sid,serial#' ;

-- 不该建位图索引的列select t1.index_name,       t1.table_name,       t2.column_name,       t2.column_position,       t1.distinct_keys,       t1.num_rows,       t1.distinct_keys / t1.num_rows  from user_indexes t1, user_ind_columns t2 where t1.index_name = t2.INDEX_NAME   and t1.index_type = 'BITMAP'   and t1.distinct_keys / t1.num_rows >= 0.1;

</pre><pre code_snippet_id="452267" snippet_file_name="blog_20140817_15_6731286" name="code" class="sql">--当前系统有哪些函数索引， 建在哪个表的哪个列上，状态如何。select t1.table_name,       t1.index_name,       t2.COLUMN_NAME,       t2.COLUMN_POSITION,       t1.status,       t1.funcidx_status  from user_indexes t1, user_ind_columns t2 where t1.index_name = t2.INDEX_NAME   and t1.index_type = 'FUNCTION-BASED NORMAL';

--当前系统有哪些全文索引， 建在哪个表的哪个列上，状态如何。select t1.table_name,       t1.index_name,       t1.parameters,       t2.column_name,       t2.column_position,       t1.status,       t1.domidx_status  from user_indexes t1, user_ind_columns t2 where t1.index_name = t2.INDEX_NAME   and t1.index_type = 'DOMAIN';

--当前系统有哪些位图索引， 建在哪个表的哪个列上，状态如何。select t1.table_name,       t1.index_name,       t2.COLUMN_NAME,       t2.COLUMN_POSITION,       t1.status  from user_indexes t1, user_ind_columns t2 where t1.index_name = t2.INDEX_NAME   and t1.index_type = 'BITMAP';

--当前系统有哪些反向键索引， 建在哪个表的哪个列上，状态如何。select t1.table_name,       t1.index_name,       t2.COLUMN_NAME,       t2.COLUMN_POSITION,       t1.status  from user_indexes t1, user_ind_columns t2 where t1.index_name = t2.INDEX_NAME   and t1.index_type = 'NORMAL/REV';

-- 哪些SQL存在列运算 注意，这只是一个参考SELECT *  FROM V$SQL T WHERE T.PARSING_SCHEMA_NAME = 'CODERJIANG'   AND REGEXP_LIKE(SQL_TEXT, '(AND|OR|WHERE)+\s+[\w\d]*[+-/\*()]', 'i') ORDER BY T.LAST_ACTIVE_TIME DESC