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>------------------------------------------------------

Script:列出没有主键或唯一索引的表

以下脚本可以用于列出数据库中没有主键的表,已排除了系统schema:

REM List tables with no primary key
SELECT owner, table_name
FROM dba_tables
WHERE 1 = 1
AND owner NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'FLOWS_030000',
'FLOWS_FILES')
MINUS
SELECT owner, table_name
FROM dba_constraints
WHERE constraint_type = 'P'
AND owner NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'FLOWS_030000',
'FLOWS_FILES')
/

以下脚本可以用于列出数据库中没有唯一约束或索引的表,已排除了系统schema:

REM List tables with no unique key or index 
SELECT owner, table_name
FROM dba_all_tables
WHERE 1 = 1
AND owner NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'FLOWS_030000',
'FLOWS_FILES')
MINUS
SELECT owner, table_name
FROM dba_constraints
WHERE constraint_type = 'U'
AND owner NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'FLOWS_030000',
'FLOWS_FILES')
MINUS
SELECT owner, table_name
FROM dba_indexes
WHERE uniqueness = 'UNIQUE'
AND owner NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'FLOWS_030000',
'FLOWS_FILES')
/

Oracle SQL Developer 的一个Bug

Oracle SQL Developer是Oracle近几年来推出的一款免费的图形界面SQL PL/SQL开发工具,在11g中它甚至被集成到了Oracle server软件中;如果你平常有用这款IDE的话,大概会注意到它提供了十分实用的Database copy,diff,export功能。

当在Oracle SQL developer中使用sys用户登录数据库,并尝试导出某些schema的ddl定义时,可能会丢失一些constraint约束的ddl信息,如以下例子:

我们尝试导出scott用户的表,索引以及约束的相关ddl,当使用sys用户登录数据库时,Oracle SQL developer给出的SQL:

--------------------------------------------------------
--  File created - Monday-August-30-2010
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table BONUS
--------------------------------------------------------
CREATE TABLE "BONUS"
(	"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"SAL" NUMBER,
"COMM" NUMBER
) ;
--------------------------------------------------------
--  DDL for Table DEPT
--------------------------------------------------------
CREATE TABLE "DEPT"
(	"DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13)
) ;
--------------------------------------------------------
--  DDL for Table EMP
--------------------------------------------------------
CREATE TABLE "EMP"
(	"EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) ;
--------------------------------------------------------
--  DDL for Table SALGRADE
--------------------------------------------------------
CREATE TABLE "SALGRADE"
(	"GRADE" NUMBER,
"LOSAL" NUMBER,
"HISAL" NUMBER
) ;
--------------------------------------------------------
--  DDL for Index PK_DEPT
--------------------------------------------------------
CREATE UNIQUE INDEX "PK_DEPT" ON "DEPT" ("DEPTNO")   ;
--------------------------------------------------------
--  DDL for Index PK_EMP
--------------------------------------------------------
CREATE UNIQUE INDEX "PK_EMP" ON "EMP" ("EMPNO")   ;

以上SQL中未给出PK和FK约束的ddl信息。

如果使用scott用户登录数据库,导出自身schema的table,index,constraint定义信息时:

--------------------------------------------------------
--  File created - Monday-August-30-2010
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table BONUS
--------------------------------------------------------
CREATE TABLE "BONUS"
(	"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"SAL" NUMBER,
"COMM" NUMBER
) ;
--------------------------------------------------------
--  DDL for Table DEPT
--------------------------------------------------------
CREATE TABLE "DEPT"
(	"DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13)
) ;
--------------------------------------------------------
--  DDL for Table EMP
--------------------------------------------------------
CREATE TABLE "EMP"
(	"EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) ;
--------------------------------------------------------
--  DDL for Table SALGRADE
--------------------------------------------------------
CREATE TABLE "SALGRADE"
(	"GRADE" NUMBER,
"LOSAL" NUMBER,
"HISAL" NUMBER
) ;
--------------------------------------------------------
--  Constraints for Table EMP
--------------------------------------------------------
ALTER TABLE "EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") ENABLE;
--------------------------------------------------------
--  Constraints for Table DEPT
--------------------------------------------------------
ALTER TABLE "DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO") ENABLE;
--------------------------------------------------------
--  DDL for Index PK_EMP
--------------------------------------------------------
CREATE UNIQUE INDEX "PK_EMP" ON "EMP" ("EMPNO")   ;
--------------------------------------------------------
--  DDL for Index PK_DEPT
--------------------------------------------------------
CREATE UNIQUE INDEX "PK_DEPT" ON "DEPT" ("DEPTNO")  ;
--------------------------------------------------------
--  Ref Constraints for Table EMP
--------------------------------------------------------
ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "DEPT" ("DEPTNO") ENABLE;

Oracle SQL developer给出了正确的SQL,包含FK和PK约束。
这个Bug在8月(2010 Aug)被确认将影响此前所有版本的Oracle SQL developer。

沪ICP备14014813号

沪公网安备 31010802001379号