Good morning,
While reading other posts, I found this interesting statement:
SELECT DBMS_METADATA.GET_DDL('TABLE','TABLE_NAME') FROM DUAL;
I figured that would be useful. When I tried it as user SCOTT, the results I obtained were "incomplete" as shown below:
SQL> select dbms_metadata.get_ddl('TABLE', 'EMP') from dual;
DBMS_METADATA.GET_DDL('TABLE','EMP')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
SQL> describe emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> select dbms_metadata.get_ddl('TABLE', 'DEPT') from dual;
DBMS_METADATA.GET_DDL('TABLE','DEPT')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14
SQL>
looks like the function choked halfway thru.
The question: Any idea why GET_DDL did not return the entire DDL for the tables above ?
I thought that maybe it was a matter of user privileges, so I tried from sys as sysdba, then it tried to find EMP in SYS (which is obviously not there). How would the DBA go about getting the DDL for a table without having to log in as the user that owns the schema ?
Thank you for your help,
John.