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,
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 ?
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?
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
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
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
January 27, 2004 - 1:16 pm UTC
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 ?
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
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
May 19, 2006 - 9:16 am UTC
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!
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
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
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
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.
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
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"