Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, khalid.

Asked: September 15, 2002 - 12:27 pm UTC

Last updated: October 10, 2006 - 8:08 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom
I have a table how can i obtain the difinition of that table "create statment that create it"

Thanks

and Tom said...

exp userid=u/p tables=that_table rows=n
imp userid=u/p full=y show=y


it'll be on your screen.


in 9i, it'll be

scott@ORA9I.WORLD> select dbms_metadata.get_ddl( 'TABLE', 'EMP' ) from dual
2 /

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

CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "EMP_PK" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCRE
ASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ENABLE,

CONSTRAINT "EMP_FK_DEPT" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE NOVALIDATE,
CONSTRAINT "EMP_FK_EMP" FOREIGN KEY ("MGR")
REFERENCES "SCOTT"."EMP" ("EMPNO") ENABLE NOVALIDATE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCRE
ASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"



Rating

  (19 ratings)

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

Comments

dbms_metadata

Andrew, September 16, 2002 - 12:55 pm UTC

Now there's a long overdue package!

A reader, September 27, 2002 - 11:07 am UTC

Tom,

check this

SQL> drop table BIG_TABLE;

Table dropped.

SQL> create table BIG_TABLE compress as select * from all_objects;                --------

Table created.

--Extract defination by DBMS_METADATA

SQL> select replace(dbms_metadata.GET_DDL('TABLE','BIG_TABLE'),'"',' ') xx from dual;

XX
--------------------------------------------------------------------------------

  CREATE TABLE  UPATEGV . BIG_TABLE
   (     OWNER  VARCHAR2(30) NOT NULL ENABLE,
         OBJECT_NAME  VARCHAR2(30) NOT NULL ENABLE,
         SUBOBJECT_NAME  VARCHAR2(30),
         OBJECT_ID  NUMBER NOT NULL ENABLE,
         DATA_OBJECT_ID  NUMBER,
         OBJECT_TYPE  VARCHAR2(18),
         CREATED  DATE NOT NULL ENABLE,
         LAST_DDL_TIME  DATE NOT NULL ENABLE,
         TIMESTAMP  VARCHAR2(19),
         STATUS  VARCHAR2(7),
         TEMPORARY  VARCHAR2(1),
         GENERATED  VARCHAR2(1),
         SECONDARY  VARCHAR2(1)
   ) PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS LOGGING
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE  USERS

--No COMPRESS word in Definition


Any comments.

Thanks, 

Tom Kyte
September 27, 2002 - 1:36 pm UTC

really:

) PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS LOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE USERS



Compressed table limitations?

Christo Kutrovsky, May 02, 2003 - 11:50 pm UTC

Tom,

SQL> create table bsd (a number) compress;

Table created

SQL> alter table bsd add (b number);

alter table bsd add (b number)

ORA-22856: cannot add columns to object tables


I did not found such a limitation of compressed tables in the documentation. Is this a BUG ? (Oracle 9.2.0.0 on linux)

Same thing if the table is partitioned:
SQL> create table bsd2 (a number)
  2  partition by range (a)
  3  (partition a values less than (10), partition b values less than (maxvalue) compress)
  4  ;

Table created

SQL> alter table bsd2 add b number;

alter table bsd2 add b number

ORA-22856: cannot add columns to object tables

HOWEVER!!!

if you add the compression AFTER creation it's fine:
SQL> create table bsd2 (a number)
  2  partition by range (a)
  3  (partition a values less than (10), partition b values less than (maxvalue) )
  4  ;

Table created

SQL> alter table bsd2 modify partition b compress;

Table altered

SQL> alter table bsd2 add b number;

Table altered

Sounds to me like a BUG ?

 

Tom Kyte
May 03, 2003 - 11:50 am UTC

there is in progress work to remove this restriction

Just out of curiousity...

Christo Kutrovsky, May 04, 2003 - 11:05 pm UTC

Why is this limitation not present when it's only on a single partition?

Is this going to be treated as a bug or as extention?

Tom Kyte
May 05, 2003 - 7:06 am UTC

if you feel it is a bug, please contact support and file one. Not sure why it works, it probably "shouldn't"

ORA-31603 - Error

Maniappan, January 22, 2004 - 11:13 am UTC

Hi Tom,

We have a partitioned table in schema 'AP'. When we give the query to find the DDL Script by using dbms_metadata.get_ddl
its giving the above mentioned error.

But while querying through the DBA_Objects the same table under the same schema (which was given in the param for get_ddl function) is very much there, with the object type 'TABLE'.

select dbms_metadata.get_ddl('TABLE','AP_LIABILITY_BALANCE','AP')
from dual;
ORA-31603: object "AP_LIABILITY_BALANCE" of type TABLE not found in schema "AP" ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105 ORA-06512: at "SYS.DBMS_METADATA", line 628 ORA-06512: at "SYS.DBMS_METADATA", line 1221 ORA-06512: at line 1

select * from dba_objects where object_name = 'AP_LIABILITY_BALANCE'
AND OBJECT_TYPE='TABLE';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- ------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- --------- ------------------- ------- - - -
AP
AP_LIABILITY_BALANCE
4387765 TABLE
27-OCT-02 11-JUL-03 2003-01-17:21:11:43 VALID N N N

Whatz wrong with my approach?

Thanks in advance

Mani


Tom Kyte
January 22, 2004 - 7:03 pm UTC

DBA_OBJECTS lets you see "everything", even things you cannot query.

can you query this
do you have access directly, not via a role?

Reply

Maniappan, January 27, 2004 - 6:23 am UTC

Tom,

Yeah I can query the table. The access is not given directly, but via the role only.

Is this the cause?. If yes, any other alternate methods available for us to get the definition?

Thanks
Mani

Tom Kyte
January 27, 2004 - 8:55 am UTC

to narrow down your issue -- grant on the table directly and see if that corrects the issue. If so, then we know it is:

</code> http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>

but it is not clear -- do you have the ability to query this table (not the view dba_objects -- do you have access to the actual table itself)

LMT and storage clause

Arun Gupta, January 27, 2004 - 12:35 pm UTC

Tom,
Since we use LMTs for all our tables, I do not want the storage clause to appear in output of dbms_metadata.get_ddl.
Is there a way to suppress this?
Thanks


8.1.7 specific.

A reader, December 21, 2004 - 4:16 pm UTC

Hi tom, I saw your reply how to get the
ddl for 8.1.7. but I am not in situation to do exp/imp.
is there a way to get the ddl of the table(specifically partitioned) by simple Sql*plus way ? for exising partitioned table ?



Tom Kyte
December 21, 2004 - 7:12 pm UTC

but you should be able to use exp/imp to get your ddl? it is the way in 8i


also, why isn't in your source code control system?

How abt views and SYNONYM

romit, May 17, 2006 - 11:31 am UTC

and do we have anything for views and SYNONYM on the similar lines ( as do we have any dbms_<something>.<something> for them.)

thanks


Tom Kyte
May 17, 2006 - 11:48 pm UTC

don't understand what you are asking

defination of view / synonym

romit, May 18, 2006 - 7:21 am UTC

I have a view or synonym, how can i obtain the difinition of that view/synonym "create statment
that create it"

thanks

Views and Synonyms for Romit

Greg, May 18, 2006 - 9:03 am UTC

Romit, 
did you check the documentation on dbms_metadata?
http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm#sthref4255

Views and synonym ddl retrieval is just the same as Tom showed in the initital response. 
e.g.

SQL>  create synonym test_dual for dual;

Synonym created.

SQL> select dbms_metadata.get_ddl('SYNONYM','TEST_DUAL') from dual;

DBMS_METADATA.GET_DDL('SYNONYM','TEST_DUAL')
--------------------------------------------------------------------------------

  CREATE SYNONYM "GWHITE"."TEST_DUAL" FOR "GWHITE"."DUAL"


Unless I misunderstood the problem, that's it. 

ORA-22856: cannot add columns to object tables

A reader, August 16, 2006 - 10:46 am UTC

Hi Tom,

Some one had mentioned in this thread that while adding a column to a compressed / partitioned table one gets ORA-22856. My version is 9.2.0.7 on Solaris, and I got this error.

If I do the following, will the existing data get uncompressed (there by increase in size)?


alter table test nocompress;
ALTER TABLE test
ADD (BOOK_TYPE VARCHAR2(1));
alter table TEST compress;


Thanks!

Tom Kyte
August 16, 2006 - 11:21 am UTC

still won't work in 9i.

It is the existence of compressed data, not the attribute of "compress"

ops$tkyte%ORA9IR2> create table t compress
  2  as
  3  select * from all_objects;
 
Table created.
 
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> alter table t add x number;
alter table t add x number
                  *
ERROR at line 1:
ORA-22856: cannot add columns to object tables
 
 
ops$tkyte%ORA9IR2> alter table t nocompress;
 
Table altered.
 
ops$tkyte%ORA9IR2> alter table t add x number;
alter table t add x number
                  *
ERROR at line 1:
ORA-22856: cannot add columns to object tables
 
<b>see, did not work, but if we rewrite the table:</b>
 
ops$tkyte%ORA9IR2> alter table t move;
 
Table altered.
 
ops$tkyte%ORA9IR2> alter table t add x number;
 
Table altered.
 
<b>it does, and we can reset compress:</b>

ops$tkyte%ORA9IR2> alter table t compress;
 
Table altered.

<b>but that just sets an attribute, we need to reorg:</b>
 
ops$tkyte%ORA9IR2> alter table t move;
 
Table altered.

<b>so, perhaps what you would want to do instead would be:</b>

 
ops$tkyte%ORA9IR2> create table new_t
  2  compress
  3  as
  4  select t.*, cast( '' as varchar2(1) ) y
  5    from t;
 
Table created.
 
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> drop table t;
 
Table dropped.
 
ops$tkyte%ORA9IR2> alter table new_t rename to t;
 
Table altered.
 
ops$tkyte%ORA9IR2> desc t
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 OWNER                                    NOT NULL VARCHAR2(30)
 OBJECT_NAME                              NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME                                    VARCHAR2(30)
 OBJECT_ID                                NOT NULL NUMBER
 DATA_OBJECT_ID                                    NUMBER
 OBJECT_TYPE                                       VARCHAR2(18)
 CREATED                                  NOT NULL DATE
 LAST_DDL_TIME                            NOT NULL DATE
 TIMESTAMP                                         VARCHAR2(19)
 STATUS                                            VARCHAR2(7)
 TEMPORARY                                         VARCHAR2(1)
 GENERATED                                         VARCHAR2(1)
 SECONDARY                                         VARCHAR2(1)
 X                                                 NUMBER
 Y                                                 VARCHAR2(1)
 
<b>use a create table as select</b>
 

ORA-22856: cannot add columns to object tables

A reader, August 16, 2006 - 11:13 am UTC

Sorry Tom, I should have tried out the steps before asking you. Even with table defined as nocompress, I get ORA-22856 when adding a column. In an another thread you have mentioned that this issue is resolved in 10G. Would you have any suggestions for 9i?

Thanks

Tom Kyte
August 16, 2006 - 11:30 am UTC

see above

ORA-22856: cannot add columns to object tables

a Reader, August 16, 2006 - 4:00 pm UTC

Thank you, Tom.
Your suggestion is very good in the sense that I only have to come up with the space equal to the existing space occupied by the table I am working on.

I did this test just for my understanding. 

-- create a compressed partitioned table:
create table t1 (object_id number,
                 owner varchar2(10))
compress
partition by range(object_id)
subpartition by list (owner)
(
partition a values less than (10000000)
(subpartition b values ('TEST')
)
)
SQL> /

Table created.

SQL> alter table t1 nocompress;

Table altered.

-- as expected: 

SQL> alter table t1 add (p varchar2(1));
alter table t1 add (p varchar2(1))
                    *
ERROR at line 1:
ORA-22856: cannot add columns to object tables

SQL> alter table t1 modify partition a nocompress
SQL> /

Table altered.

-- as expected

SQL> alter table t1 add (p varchar2(1));
alter table t1 add (p varchar2(1))
                    *
ERROR at line 1:
ORA-22856: cannot add columns to object tables

-- Here I moved the subpartition, so the table is basically rid of any compression.

SQL> alter table t1 move subpartition b;

Table altered.

-- Then why do I still have the problem? 

SQL> alter table t1 add (p varchar2(1));
alter table t1 add (p varchar2(1))
                    *
ERROR at line 1:
ORA-22856: cannot add columns to object tables

 

Tom Kyte
August 16, 2006 - 5:29 pm UTC

you have to move all of them I would presume - to get all of the blocks rewritten.



ORA-22856: cannot add columns to object tables

A reader, August 16, 2006 - 10:32 pm UTC

There is one partition and one susubpartition. already moved the susubpartition.
When I tried to move the partition, I get this:

SQL> alter table t1 move partition a;
alter table t1 move partition a
                              *
ERROR at line 1:
ORA-14257: cannot move partition other than a Range or Hash partition

Thanks 

Tom Kyte
August 17, 2006 - 8:50 am UTC

looks like it does not work on the individual partitions, sorry about that.

Why is my output not displayed in full?

Umesh Kasturi, August 17, 2006 - 12:54 am UTC

SQL> select dbms_metadata.get_ddl('TABLE','T') from dual;

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

  CREATE TABLE "TEST_USAUTH"."T"
   (    "USERNAME" VARCHAR2(30) NOT NULL ENABLE,


SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------
 USERNAME                                  NOT NULL VARCHAR2(30)
 USER_ID                                   NOT NULL NUMBER
 CREATED                                   NOT NULL DATE

SQL> sho lines
linesize 80
SQL> sho lines 220
linesize 80
SP2-0158: unknown SHOW option "220"
SQL> set lines 220
SQL> select dbms_metadata.get_ddl('TABLE','T') from dual;

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

  CREATE TABLE "TEST_USAUTH"."T"
   (    "USERNAME" VARCHAR2(30) NOT NULL ENABLE,


SQL> col a format a220
SQL> select dbms_metadata.get_ddl('TABLE','T') a from dual;

A
-------------------------------------------------------------------------------
-----------------------------------------------------------------

  CREATE TABLE "TEST_USAUTH"."T"
   (    "USERNAME" VARCHAR2(30) NOT NULL ENABLE,


SQL> set lines 80
SQL> 

To Umesh Kasturi

Michel Cadot, August 17, 2006 - 1:04 am UTC

Change "long" parameter:

SQL> select dbms_metadata.get_ddl('TABLE','T') from dual;
DBMS_METADATA.GET_DDL('TABLE','T')
-------------------------------------------------------------------------------

  CREATE TABLE "MICHEL"."T"
   (    "NAME" VARCHAR2(6),
        "CATG" VARCHAR2(5),
        "

1 row selected.

SQL> set long 1000
SQL> select dbms_metadata.get_ddl('TABLE','T') from dual;
DBMS_METADATA.GET_DDL('TABLE','T')
-------------------------------------------------------------------------------

  CREATE TABLE "MICHEL"."T"
   (    "NAME" VARCHAR2(6),
        "CATG" VARCHAR2(5),
        "COL1" NUMBER(*,0),
        "COL2" NUMBER(*,0),
        "COL3" NUMBER(*,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TS_D01"


1 row selected.

Michel 

ORA-22856

A reader, August 20, 2006 - 3:00 pm UTC

Tom,

I am not able to reproduce your test case (from August 16th 2006):

SQL> create table t1 compress as select * from new_t3;

Table created.

SQL> alter table t1 nocompress;

Table altered.

SQL> alter table t1 add (a number);
alter table t1 add (a number)
                    *
ERROR at line 1:
ORA-22856: cannot add columns to object tables


SQL> alter table t1 move;

Table altered.

SQL> alter table t1 add (a number);
alter table t1 add (a number)
                    *
ERROR at line 1:
ORA-22856: cannot add columns to object tables


SQL> select * from v$version;

BANNER
---------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
PL/SQL Release 9.2.0.7.0 - Production
CORE    9.2.0.7.0       Production
TNS for Solaris: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production

SQL>

I use LMT, segment space management auto (I doubt if these matter).

Thanks for your help. 

Tom Kyte
August 27, 2006 - 8:53 am UTC

interesting, I had 9206 when I tried that - indeed, in 9207 it did not work. I'm not overly concerned however since the create table as select is the only "reasonable" approach (to avoid uncompressing and then recompressing the entire table)

extra info in DDL

Eric Peterson, October 10, 2006 - 12:14 pm UTC

Using DBMS_METADATA.GET_DDL I notice that some columns have "ENABLE" and some don't.

CREATE TABLE sch.aa_rp
(
SUB_KEY NUMBER(28,0) NOT NULL ENABLE,
CANCELLATION_DATE DATE,
SUB_SITE_CODE VARCHAR2(10 BYTE),
OLD_PLAN_KEY NUMBER(15,0) NOT NULL ENABLE,
OLD_PLAN_CODE VARCHAR2(10 BYTE) NOT NULL ENABLE,
...


Could you help explain why and what that might be? I'm on Oracle 10g (10.2.0.2.0). I've heard of declaring a column DISABLED, but am unsure why in the DDL extract for fields that have data some would be identified as enabled and some with no identification.

Thanks


Tom Kyte
October 10, 2006 - 8:08 pm UTC

the enable is for the constraint, has nothing to do with the "column" (except for the fact these are column constraints)

there is no such thing as a "disabled" column - not sure where you heard it, but it isn't "so"

More to Explore

DBMS_METADATA

More on PL/SQL routine DBMS_METADATA here