2群基础群 适合刚入门的同学，会共享最佳入门实践和资料 QQ群 # QQ群号:171092051 # 已经升级到 500人的超级群，空位多多，无需面试
Script:List Schema/Table Constraints
以下脚本可以用于列出相关模式或表上的Constraints约束:
---<tfsscons.sql begin>------------------------------------------------------
SET ECHO off
REM NAME: tfsscons.sql
REM USAGE:"@path/tfsscons"
REM --------------------------------------------------------------------------
REM REQUIREMENTS:
REM SELECT permissions on dba_constraints, dba_cons_columns and
REM dba_ind_columns.
REM --------------------------------------------------------------------------
SET ARRAYSIZE 1
SET FEEDBACK off
SET SERVEROUT on
SET PAGESIZE 66
SET NEWPAGE 6
SET LINESIZE 75
SET PAUSE off
SET VERIFY off
ACCEPT puser PROMPT 'Enter the schema name: '
ACCEPT pexcp PROMPT 'Enter the EXCEPTIONS table name for schema &puser: '
PROMPT 'NOTE: This will take some time, please wait...'
SPOOL schema_cons_&puser
DECLARE
CURSOR cons_cur (v_userid VARCHAR2) IS
SELECT * FROM dba_constraints
WHERE owner = v_userid
AND constraint_type in ('P','U','C','R')
ORDER BY constraint_type;
CURSOR col_cur (con_name VARCHAR2, con_owner VARCHAR2) IS
SELECT * FROM dba_cons_columns
WHERE owner = con_owner
AND constraint_name = con_name
ORDER BY position;
CURSOR indx_cur (con_name VARCHAR2, ind_own VARCHAR2) IS
SELECT a.*
FROM dba_indexes a, dba_ind_columns b, dba_cons_columns c
WHERE c.constraint_name = con_name
AND a.owner = ind_own
AND b.index_owner = ind_own
AND c.owner = b.index_owner
AND c.position = 1
AND c.table_name = b.table_name
AND c.column_name = b.column_name
AND b.index_name = a.index_name;
col_str VARCHAR2(200);
v_user VARCHAR2(30) := UPPER('&puser');
v_output VARCHAR2(480); -- max of 16 cols at 30 chars each
v_excp NUMBER(1) := 0;
v_excptab VARCHAR2(60) := NULL;
v_delrule VARCHAR2(4);
v_status VARCHAR2(4);
srch_cond VARCHAR2(1000);
v_errcode NUMBER := 0;
v_errmsg varchar2(50) := ' ';
BEGIN
DBMS_OUTPUT.ENABLE(1000000); -- Prevents buffer exceeded error
BEGIN
v_excptab := UPPER('&pexcp');
IF v_excptab IS NOT NULL THEN
SELECT 1
INTO v_excp
FROM dba_objects
WHERE owner = UPPER('&puser')
AND object_name = UPPER('&pexcp');
v_excptab := 'EXCEPTIONS INTO '||LOWER('&pexcp');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Exceptions table does not exist in the schema: ');
RAISE NO_DATA_FOUND;
GOTO err;
END;
FOR c1 IN cons_cur(v_user) LOOP
begin
srch_cond := substr(c1.search_condition,1,length(c1.search_condition));
-- Dont remove table constraint NOT NULL
IF (instr(srch_cond,'NOT NULL') < 1) or
(instr(srch_cond,'NOT NULL') IS NULL) THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('ALTER TABLE '||C1.OWNER||'.'||C1.TABLE_NAME);
DBMS_OUTPUT.PUT_LINE(' ADD (CONSTRAINT '||C1.CONSTRAINT_NAME);
IF c1.constraint_type = 'P' THEN v_output := ' PRIMARY KEY (';
ELSIF c1.constraint_type = 'R' THEN v_output := ' FOREIGN KEY (';
ELSIF c1.constraint_type = 'U' THEN v_output := ' UNIQUE (';
ELSIF c1.constraint_type = 'C' THEN
v_output := ' CHECK ('||c1.search_condition||') '||v_excptab;
END IF;
FOR c2 IN col_cur(c1.constraint_name, c1.owner) LOOP
IF c2.position = 1 THEN
v_output := v_output||c2.column_name;
ELSIF c2.position > 1 THEN
v_output := v_output||', '||c2.column_name;
END IF;
END LOOP;
v_output := v_output||')';
DBMS_OUTPUT.PUT_LINE(v_output);
IF c1.constraint_type = 'R' THEN
v_output := NULL;
FOR c3 IN col_cur(c1.r_constraint_name, c1.r_owner) LOOP
IF c3.position = 1 THEN
v_output := ' REFERENCES '||c3.owner||'.'||c3.table_name||'(';
v_output := v_output||c3.column_name;
ELSIF c3.position > 1 THEN
v_output := v_output||', '||c3.column_name;
END IF;
END LOOP;
v_output := v_output||')';
DBMS_OUTPUT.PUT_LINE(v_output);
v_delrule := substr(c1.delete_rule,1,2);
IF v_delrule IS NULL THEN v_output := v_excptab || ' )';
ELSIF v_delrule = 'NO' THEN v_output := v_excptab || ' )';
ELSIF v_delrule = 'CA' THEN v_output := ' ON DELETE CASCADE '||v_excptab || ')';
END IF;
DBMS_OUTPUT.PUT_LINE(v_output);
END IF;
FOR c4 IN indx_cur(c1.constraint_name, c1.owner) LOOP
IF c1.constraint_type in ('P','U') THEN
DBMS_OUTPUT.PUT_LINE(' USING INDEX ');
DBMS_OUTPUT.PUT_LINE(' pctfree '||c4.pct_free);
DBMS_OUTPUT.PUT_LINE(' initrans '||c4.ini_trans);
DBMS_OUTPUT.PUT_LINE(' maxtrans '||c4.max_trans);
DBMS_OUTPUT.PUT_LINE(' tablespace '||c4.tablespace_name);
DBMS_OUTPUT.PUT_LINE(' Storage (');
DBMS_OUTPUT.PUT_LINE(' initial '||c4.initial_extent);
DBMS_OUTPUT.PUT_LINE(' next '||c4.next_extent);
DBMS_OUTPUT.PUT_LINE(' minextents'||c4.min_extents);
DBMS_OUTPUT.PUT_LINE(' maxextents '||c4.max_extents);
DBMS_OUTPUT.PUT_LINE(' pctincrease '||c4.pct_increase||') '|| v_excptab ||')');
END IF;
END LOOP;
v_output := NULL;
v_status := substr(c1.status,1,1);
IF v_status = 'E' THEN
v_output := ' REM This constraint '||c1.constraint_name||' was ENABLED';
ELSIF v_status = 'D' THEN
v_output :=' REM This constraint '||c1.constraint_name ||' was DISABLED';
END IF;
DBMS_OUTPUT.PUT_LINE('/ ');
DBMS_OUTPUT.PUT_LINE(v_output);
DBMS_OUTPUT.PUT_LINE('-------------------------------------------- ');
DBMS_OUTPUT.PUT_LINE(' ');
END;
END IF;
EXCEPTION
WHEN no_data_found THEN
DBMS_OUTPUT.PUT_LINE('No Data Found');
WHEN others THEN
v_errcode := sqlcode;
v_errmsg := SUBSTR(sqlerrm, 1, 50);
DBMS_OUTPUT.PUT_LINE('ERROR: '||v_errcode||': ' || v_errmsg);
DBMS_OUTPUT.PUT_LINE(c1.constraint_name||' '||c1.constraint_type);
DBMS_OUTPUT.PUT_LINE(c1.search_condition);
END;
END LOOP;
<<err>>
NULL;
END;
/
SPOOL off
SET PAGESIZE 14
SET FEEDBACK on
SET NEWPAGE 0
SET ARRAYSIZE 20
SET SERVEROUT off
SET LINESIZE 79
SET VERIFY on
---<tfsscons.sql end>------------------------------------------------------
---<tfstcons.sql begin>------------------------------------------------------
SET ECHO off
REM NAME: tfstcons.sql
REM USAGE:"@path/tfstcons"
REM --------------------------------------------------------------------------
REM REQUIREMENTS:
REM SELECT permissions on dba_constraints, dba_cons_columns and
REM dba_ind_columns.
REM --------------------------------------------------------------------------
SET ARRAYSIZE 1
SET SERVEROUT on
SET PAGESIZE 66
SET NEWPAGE 6
SET LINESIZE 75
SET PAUSE off
SET VERIFY off
SET FEEDBACK off
ACCEPT puser PROMPT 'Enter the schema name: '
ACCEPT ptab PROMPT 'Enter the table name: '
ACCEPT pexcp PROMPT 'Enter the EXCEPTIONS table name for schema &puser: '
PROMPT 'NOTE: This will take some time, please be patient...'
SPOOL tab_cons_&ptab
DECLARE
CURSOR cons_cur (v_userid VARCHAR2, v_tabname VARCHAR2) IS
SELECT * FROM sys.dba_constraints
WHERE owner = v_userid
AND constraint_type in ('P','U','C','R')
AND table_name = v_tabname
ORDER BY constraint_type;
CURSOR col_cur (con_name VARCHAR2, con_owner VARCHAR2) IS
SELECT * FROM sys.dba_cons_columns
WHERE owner = con_owner
AND constraint_name = con_name
ORDER BY position;
CURSOR indx_cur (con_name VARCHAR2, ind_own VARCHAR2) IS
SELECT a.*
FROM sys.dba_indexes a, sys.dba_ind_columns b, sys.dba_cons_columns c
WHERE c.constraint_name = con_name
AND a.owner = ind_own
AND b.index_owner = ind_own
AND c.owner = b.index_owner
AND c.position = 1
AND c.table_name = b.table_name
AND c.column_name = b.column_name
AND b.index_name = a.index_name;
col_str VARCHAR2(200);
v_user VARCHAR2(30) := UPPER('&puser');
v_tabname VARCHAR2(30) := UPPER('&ptab');
v_output VARCHAR2(480); -- max of 16 cols at 30 chars each
v_dummy NUMBER := 0;
v_delrule VARCHAR2(4);
v_status VARCHAR2(4);
v_excp NUMBER(1) := 0;
v_excptab VARCHAR2(60) := NULL;
srch_cond VARCHAR2(1000);
BEGIN
DBMS_OUTPUT.ENABLE(1000000); -- Prevents buffer exceeded error
SELECT 1 -- Check to see if the table exists
INTO v_dummy
FROM dba_tables
WHERE table_name = v_tabname
AND owner = v_user;
BEGIN
v_excptab := UPPER('&pexcp');
IF v_excptab IS NOT NULL THEN
SELECT 1
INTO v_excp
FROM sys.dba_objects
WHERE owner = UPPER('&puser')
AND object_name = UPPER('&pexcp');
v_excptab := 'EXCEPTIONS INTO '||LOWER('&pexcp');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Exceptions table does not exist in your schema: ');
RAISE NO_DATA_FOUND;
GOTO err;
END;
FOR c1 IN cons_cur(v_user, v_tabname) LOOP
begin
srch_cond := substr(c1.search_condition,1,length(c1.search_condition));
-- Dont remove table constraint NOT NULL
IF (instr(srch_cond,'NOT NULL') < 1) or
(instr(srch_cond,'NOT NULL') IS NULL) THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('ALTER TABLE '||C1.OWNER||'.'||C1.TABLE_NAME);
DBMS_OUTPUT.PUT_LINE(' ADD (CONSTRAINT '||C1.CONSTRAINT_NAME);
IF c1.constraint_type = 'P' THEN v_output := ' PRIMARY KEY (';
ELSIF c1.constraint_type = 'R' THEN v_output := ' FOREIGN KEY (';
ELSIF c1.constraint_type = 'U' THEN v_output := ' UNIQUE (';
ELSIF c1.constraint_type = 'C' THEN
v_output := ' CHECK ('||c1.search_condition||') '||v_excptab;
END IF;
FOR c2 IN col_cur(c1.constraint_name, c1.owner) LOOP
IF c2.position = 1 THEN
v_output := v_output||c2.column_name;
ELSIF c2.position > 1 THEN
v_output := v_output||', '||c2.column_name;
END IF;
END LOOP;
v_output := v_output ||')';
DBMS_OUTPUT.PUT_LINE(v_output);
IF c1.constraint_type = 'R' THEN
v_output := NULL;
FOR c3 IN col_cur(c1.r_constraint_name, c1.r_owner) LOOP
IF c3.position = 1 THEN
v_output := ' REFERENCES '||c3.owner||'.'||c3.table_name||'(';
v_output := v_output||c3.column_name;
ELSIF c3.position > 1 THEN
v_output := v_output||', '||c3.column_name;
END IF;
END LOOP;
v_output := v_output||') ';
DBMS_OUTPUT.PUT_LINE(v_output);
v_delrule := substr(c1.delete_rule,1,2);
IF v_delrule IS NULL THEN v_output := v_excptab ||' )';
ELSIF v_delrule = 'NO' THEN v_output := v_excptab || ' )';
ELSIF v_delrule = 'CA' THEN v_output := ' ON DELETE CASCADE '||v_excptab || ')';
END IF;
DBMS_OUTPUT.PUT_LINE(v_output);
END IF;
FOR c4 IN indx_cur(c1.constraint_name, c1.owner) LOOP
IF c1.constraint_type in ('P','U') THEN
DBMS_OUTPUT.PUT_LINE(' USING INDEX ');
DBMS_OUTPUT.PUT_LINE(' pctfree '||c4.pct_free);
DBMS_OUTPUT.PUT_LINE(' initrans '||c4.ini_trans);
DBMS_OUTPUT.PUT_LINE(' maxtrans '||c4.max_trans);
DBMS_OUTPUT.PUT_LINE(' tablespace '||c4.tablespace_name);
DBMS_OUTPUT.PUT_LINE(' Storage (');
DBMS_OUTPUT.PUT_LINE(' initial '||c4.initial_extent);
DBMS_OUTPUT.PUT_LINE(' next '||c4.next_extent);
DBMS_OUTPUT.PUT_LINE(' minextents '||c4.min_extents);
DBMS_OUTPUT.PUT_LINE(' maxextents '||c4.max_extents);
DBMS_OUTPUT.PUT_LINE(' pctincrease '||c4.pct_increase||') '|| v_excptab ||')');
END IF;
END LOOP;
v_output := NULL;
v_status := substr(c1.status,1,1);
IF v_status = 'E' THEN
v_output := ' REM This constraint '||c1.constraint_name||' was ENABLED';
ELSIF v_status = 'D' THEN
v_output :=' REM This constraint '||c1.constraint_name ||' was DISABLED';
END IF;
DBMS_OUTPUT.PUT_LINE('/ ');
DBMS_OUTPUT.PUT_LINE(v_output);
DBMS_OUTPUT.PUT_LINE('-------------------------------------------- ');
DBMS_OUTPUT.PUT_LINE(' ');
END;
END IF;
EXCEPTION
WHEN no_data_found THEN
DBMS_OUTPUT.PUT_LINE('No Data Found');
WHEN others THEN
DBMS_OUTPUT.PUT_LINE('Other: '||substr(sqlerrm,1,60));
DBMS_OUTPUT.PUT_LINE(c1.constraint_name||' '||c1.constraint_type);
DBMS_OUTPUT.PUT_LINE(c1.search_condition);
END;
END LOOP;
<<err>>
NULL;
EXCEPTION
WHEN no_data_found THEN
DBMS_OUTPUT.PUT_LINE('This table: '||v_tabname||', Does not exist or has no constraints!');
END;
/
SPOOL off
SET PAGESIZE 14
SET FEEDBACK on
SET NEWPAGE 0
SET ARRAYSIZE 20
SET SERVEROUT off
SET LINESIZE 79
SET VERIFY on
---<tfstcons.sql end>------------------------------------------------------
posted on 2013-03-19 00:47 Oracle和MySQL 阅读(239) 评论(0) 编辑 收藏 举报
