00.Anyone Asked
USER_DEPENDENCIES IS NOT GIVING THE INFORMATION FOR TABLES.
ALTER TABLE EMP ADD
(CONSTRAINT PK_EMP PRIMARY KEY (EMPNO))
/
ALTER TABLE EMP ADD
(CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO)
REFERENCES DEPT (DEPTNO))
/
01.Tom Said
user_constraints would be the place I would look for constraints. user_dependencies -- well, that I would use to see who relies on who.
itpux@orcl(PRIMARY)> begin
dbms_metadata.set_transform_param
( DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', true );
dbms_metadata.set_transform_param
( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false );
dbms_metadata.set_transform_param
( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.24
02.Getting Foreign and Primary Key
itpux@orcl(PRIMARY)> select dbms_metadata.get_ddl( 'TABLE', 'EMP' ) from dual;
DBMS_METADATA.GET_DDL('TABLE','EMP')
--------------------------------------------------------------------------------
CREATE TABLE "ITPUX"."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)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "ITPUX" ;
ALTER TABLE "ITPUX"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "ITPUX" ENABLE;
ALTER TABLE "ITPUX"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "ITPUX"."DEPT" ("DEPTNO") ENABLE;
Elapsed: 00:00:03.39
参考文档：
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7490088329317
