Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Question about SELECT DBMS_METADATA.GET_DDL

783956Aug 10 2010 — edited Aug 11 2010
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.
This post has been answered by Satish Kandi on Aug 10 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 8 2010
Added on Aug 10 2010
7 comments
1,700 views