Skip to Main Content
  • Questions
  • DBMS_METADATA.GET_DDL - Output is getting Truncated

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: May 04, 2017 - 1:09 pm UTC

Last updated: April 11, 2022 - 5:13 am UTC

Version: 11.2

Viewed 10K+ times! This question is

You Asked

Hi Team,

I am trying to get table & its dependent ddl, but it its not giving completed output -

SQL> set linesize 1000
SQL> set pages 1000
SQL> set pages 1000
SQL> set lines 1000
SQL> select DBMS_METADATA.GET_DDL('TABLE','TB_TEST') from dual;

DBMS_METADATA.GET_DDL('TABLE','TB_TEST')
--------------------------------------------------------------------------------

  CREATE TABLE "FOO"."TB_TEST"
   (    "CARD_NO" VARCH


SQL> 



I tried spooling the output but same truncated output I am getting in log file as well.
Can you please help.

and Chris said...

You need to increase the value of the long system variable. This controls the maximum width displayed for CLOBs, BLOBs and other "large" data types

SQL> create table t ( x int, y date, z varchar2(100) );

Table created.

SQL> sho long
long 80
SQL> select DBMS_METADATA.GET_DDL('TABLE','T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

  CREATE TABLE "CHRIS"."T"
   (    "X" NUMBER(*,0),
        "Y" DATE,
        "Z" VARCHAR2(10


SQL> set long 100000
SQL> select DBMS_METADATA.GET_DDL('TABLE','T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

  CREATE TABLE "CHRIS"."T"
   (    "X" NUMBER(*,0),
        "Y" DATE,
        "Z" VARCHAR2(100)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505
  PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"


http://docs.oracle.com/database/122/SQPUG/SET-system-variable-summary.htm#SQPUG089

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Parameter of GET_DDL

A reader, September 11, 2020 - 10:34 am UTC

How can i see value of "object type" parameter of "GET_DDL" ?
for example object type may be table for table , type for type but what for the database link?

I getting ora-31600 error.
Connor McDonald
September 12, 2020 - 3:56 am UTC

Have you checked the docs ?

Hint.... there's a table listing all the valid object types in the docs for DBMS_METADATA

A reader, September 12, 2020 - 8:09 am UTC

I check the document following link.
https://docs.oracle.com/database/121/ARPLS/d_metada.htm#ARPLS026

but not find any more parameter accepted by get_ddl argument
Connor McDonald
September 14, 2020 - 1:58 am UTC

Its right there in the 19c docs

DBMS_METADATA_OBJTYPE

and I checked - same in 18c docs, same in 12.2 docs, same in 11.2 docs.

I mean...you gotta meet us half way here.

List object_types

David, September 12, 2020 - 10:32 am UTC

Hello,

This is an OLD list from oracle 9.2 but, I think, still valid.
https://docs.oracle.com/cd/B10501_01/appdev.920/a96612/d_metad2.htm#1031458


Thanks

A reader, September 14, 2020 - 11:36 am UTC

thank you finally got answers of my question.
before post question, i find "object type" of dbms_metadata.get_ddl
instead of "dbms_metadata"

It does not work!

A reader, April 09, 2022 - 1:18 am UTC


Connor McDonald
April 11, 2022 - 5:13 am UTC

What exactly do you mean by "it" ?

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here