按用户批量重建f复合分区索引： 按用户将此用户下面复合分区索引索引全部重建，此过程建议在SYS用户下面执行：

CREATE OR REPLACE PROCEDURE BATCH_REBUILD_SUB_INDEX(USER_NAME IN VARCHAR2, DEGREE IN NUMBER) IS 

S_SQL2 VARCHAR2(500); 

ACCOUNT3 NUMBER := 0; 

BEGIN 

execute immediate 'alter session enable parallel dml';

-- 重建复合分区索引

FOR LINE3 IN (SELECT S.INDEX_OWNER, S.INDEX_NAME, S.SUBPARTITION_NAME

FROM ALL_IND_SUBPARTITIONS S

WHERE S.INDEX_OWNER = UPPER(USER_NAME)) LOOP

S_SQL2 := 'alter index ' || LINE3.INDEX_OWNER || '.' || LINE3.INDEX_NAME || 

' rebuild subpartition '|| LINE3.SUBPARTITION_NAME ||' online parallel '|| DEGREE ||' NOLOGGING';

ACCOUNT3 := ACCOUNT3 + 1;

EXECUTE IMMEDIATE S_SQL2;

END LOOP;

-- 判断是否存在复合分区索引

IF ( ACCOUNT3 = 0 ) THEN

DBMS_OUTPUT.PUT_LINE('Not Found Subpartition_Index');

ELSE

DBMS_OUTPUT.PUT_LINE('REBUILD Subpartition Index Nums: ' || ACCOUNT3); 

END IF;

-- 捕获异常

EXCEPTION 

WHEN OTHERS THEN 

DBMS_OUTPUT.PUT_LINE(SQLERRM); 

END BATCH_REBUILD_SUB_INDEX;

过程在SYS用户下面创建完成后，用下面的代码调整创建好的存储过程：http://www.cndba.cn/Marvinn/article/2732

set serveroutput on
begin 

-- Call the procedure 

batch_rebuild_sub_index(user_name => 'marvin',degree=> 2); --输入用户名以及并行度 

end;

按用户批量重建分区索引： 按用户将此用户下面分区索引索引全部重建，此过程建议在SYS用户下面执行：http://www.cndba.cn/Marvinn/article/2732

CREATE OR REPLACE PROCEDURE BATCH_REBUILD_PART_INDEX(USER_NAME IN VARCHAR2, DEGREE IN NUMBER) IS 

S_SQL1 VARCHAR2(500); 

ACCOUNT2 NUMBER := 0; 

BEGIN 

execute immediate 'alter session enable parallel dml';

-- 重建分区索引

FOR LINE2 IN (SELECT P.INDEX_OWNER, P.INDEX_NAME, P.PARTITION_NAME

FROM ALL_IND_PARTITIONS P

WHERE P.INDEX_OWNER = UPPER(USER_NAME)) LOOP

S_SQL1 := 'alter index ' || LINE2.INDEX_OWNER || '.' || LINE2.INDEX_NAME || 

' rebuild partition '|| LINE2.PARTITION_NAME ||' online parallel '|| DEGREE ||' NOLOGGING';

ACCOUNT2 := ACCOUNT2 + 1;

EXECUTE IMMEDIATE S_SQL1;

END LOOP;

-- 判断是否存在分区索引

IF ( ACCOUNT2 = 0 ) THEN

    DBMS_OUTPUT.PUT_LINE('Not Found Partiton_Index');

ELSE

    DBMS_OUTPUT.PUT_LINE('REBUILD Partiton Index Nums: ' || ACCOUNT2); 

END IF;

-- 捕获异常

EXCEPTION 

WHEN OTHERS THEN 

DBMS_OUTPUT.PUT_LINE(SQLERRM); 

END BATCH_REBUILD_PART_INDEX;

过程在SYS用户下面创建完成后，用下面的代码调整创建好的存储过程：

set serveroutput on
begin 

-- Call the procedure 

batch_rebuild_part_index(user_name => 'marvin',degree=> 2); --输入用户名以及并行度 

end;

按用户批量重建索引：
按用户将此用户下面非临时表上面的索引全部重建，此过程建议在SYS用户下面执行：

CREATE OR REPLACE PROCEDURE BATCH_REBUILD_INDEX(USER_NAME IN VARCHAR2, DEGREE IN NUMBER) IS 

S_SQL VARCHAR2(500); 

ACCOUNT NUMBER := 0; 

BEGIN 

execute immediate 'alter session enable parallel dml';

FOR LINE2 IN (SELECT T.OWNER, T.INDEX_NAME 

FROM ALL_INDEXES T 

WHERE T.OWNER = UPPER(USER_NAME) 

AND T.TABLE_TYPE = 'TABLE' 

AND T.TEMPORARY = 'N' 

AND T.INDEX_TYPE = 'NORMAL') LOOP 

S_SQL := 'alter index ' || LINE2.OWNER || '.' || LINE2.INDEX_NAME || 
' rebuild '||' parallel '|| DEGREE ||' NOLOGGING'; 

ACCOUNT := ACCOUNT + 1; 

EXECUTE IMMEDIATE S_SQL; 

END LOOP; 

DBMS_OUTPUT.PUT_LINE(ACCOUNT); 

EXCEPTION 

WHEN OTHERS THEN 

DBMS_OUTPUT.PUT_LINE(SQLERRM); 

END BATCH_REBUILD_INDEX;

过程在SYS用户下面创建完成后，用下面的代码调整创建好的存储过程：http://www.cndba.cn/Marvinn/article/2732

set serveroutput on
begin 

-- Call the procedure 

batch_rebuild_index(user_name => 'marvin',degree=> 2); --输入用户名以及并行度 

end;

按用户批量激活约束： 按用户将此用户下面约束条件全部激活，此过程建议在SYS用户下面执行：

CREATE OR REPLACE PROCEDURE BATCH_ENABLE__CONSRAINTS(USER_NAME IN VARCHAR2) IS 

S_SQL1 VARCHAR2(500);

S_SQL2 VARCHAR2(500); 

ACCOUNT NUMBER := 0;

V_OWNER VARCHAR2(30);

V_TABNAME VARCHAR2(50);

V_CONSTRAINT_NAME VARCHAR2(100);

TYPE CUR_DCON IS REF CURSOR;

DCON_CUR CUR_DCON;

BEGIN 

execute immediate 'alter session enable parallel dml';

S_SQL1 := 'SELECT  T.OWNER,

    T.TABLE_NAME,

    S.CONSTRAINT_NAME

FROM  ALL_TABLES T, ALL_CONSTRAINTS S 

WHERE T.OWNER=' ||''''||UPPER(USER_NAME)||''''||' AND T.OWNER=S.OWNER 

AND T.TABLE_NAME=S.TABLE_NAME AND S.STATUS <> '||''''||'ENABLED'||'''';

OPEN DCON_CUR FOR S_SQL1;

LOOP

    FETCH DCON_CUR INTO V_OWNER,V_TABNAME,V_CONSTRAINT_NAME;
    EXIT WHEN  DCON_CUR%NOTFOUND;
IF ((DCON_CUR%NOTFOUND) = TRUE) THEN
    DBMS_OUTPUT.PUT_LINE('Owner Table Constraints  NOT FOUND');
ELSE
    S_SQL2 := 'ALTER TABLE ' ||V_OWNER ||'.'|| V_TABNAME || ' ENABLE CONSTRAINT '|| V_CONSTRAINT_NAME; 
    ACCOUNT := ACCOUNT + 1;
    EXECUTE IMMEDIATE S_SQL2;
END IF;
END LOOP;

DBMS_OUTPUT.PUT_LINE('Owner Table Constraints  Enable Nums: ' || ACCOUNT); 

-- 捕获异常

EXCEPTION 

WHEN OTHERS THEN 

DBMS_OUTPUT.PUT_LINE(SQLERRM); 

END  BATCH_ENABLE__CONSRAINTS;

过程在SYS用户下面创建完成后，用下面的代码调整创建好的存储过程：

set serveroutput on
begin 

-- Call the procedure 

BATCH_ENABLE__CONSRAINTS(user_name => 'marvin'); --输入用户名

end;

按用户批量禁止约束： 按用户将此用户下面约束条件全部禁止，此过程建议在SYS用户下面执行：

CREATE OR REPLACE PROCEDURE BATCH_DISABLE__CONSRAINTS(USER_NAME IN VARCHAR2) IS 

S_SQL1 VARCHAR2(500);

S_SQL2 VARCHAR2(500); 

ACCOUNT NUMBER := 0;

V_OWNER VARCHAR2(30);

V_TABNAME VARCHAR2(50);

V_CONSTRAINT_NAME VARCHAR2(100);

TYPE CUR_DCON IS REF CURSOR;

DCON_CUR CUR_DCON;

BEGIN 

execute immediate 'alter session enable parallel dml';

S_SQL1 := 'SELECT  T.OWNER,

    T.TABLE_NAME,

    S.CONSTRAINT_NAME

FROM  ALL_TABLES T, ALL_CONSTRAINTS S 

WHERE T.OWNER=' ||''''||UPPER(USER_NAME)||''''||' AND T.OWNER=S.OWNER 

AND T.TABLE_NAME=S.TABLE_NAME AND S.STATUS <> '||''''||'DISABLED'||'''';

OPEN DCON_CUR FOR S_SQL1;

LOOP

    FETCH DCON_CUR INTO V_OWNER,V_TABNAME,V_CONSTRAINT_NAME;
    EXIT WHEN  DCON_CUR%NOTFOUND;
IF ((DCON_CUR%NOTFOUND) = TRUE) THEN
    DBMS_OUTPUT.PUT_LINE('Owner Table Constraints  NOT FOUND');
ELSE
    S_SQL2 := 'ALTER TABLE ' ||V_OWNER ||'.'|| V_TABNAME || ' DISABLE CONSTRAINT '|| V_CONSTRAINT_NAME; 
    ACCOUNT := ACCOUNT + 1;
    EXECUTE IMMEDIATE S_SQL2;
END IF;
END LOOP;

DBMS_OUTPUT.PUT_LINE('Owner Table Constraints  Disable Nums: ' || ACCOUNT); 

-- 捕获异常

EXCEPTION 

WHEN OTHERS THEN 

DBMS_OUTPUT.PUT_LINE(SQLERRM); 

END  BATCH_DISABLE__CONSRAINTS;

过程在SYS用户下面创建完成后，用下面的代码调整创建好的存储过程：

set serveroutput on
begin 

-- Call the procedure 

BATCH_DISABLE__CONSRAINTS(user_name => 'marvin'); --输入用户名

end;

版权声明：本文为博主原创文章，未经博主允许不得转载。