Home » Infrastructure » Linux » How to get the oracle table structure?
How to get the oracle table structure? [message #205558] Sun, 26 November 2006 01:28 Go to next message
sathyguy
Messages: 31
Registered: January 2006
Member
Friends,

I have to take the table structure of a user.
i tried like this....

select dbms_metadata.get_ddl('TABLE',table_name,'SCOTT')
from user_tables;

but...the output is not so good. Is there any other way to get a table structure apart from sql tool like sql navigator etc...

thanks

Re: How to get the oracle table structure? [message #205577 is a reply to message #205558] Sun, 26 November 2006 06:37 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> but...the output is not so good.
What do you mean by that?
Formatting? I had no problems in formatting.
You can prettify the output to some extent.
use
exec DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.session_transform, 'PRETTY', true);


http://www.orafaq.com/forum/m/135427/42800/?srch=SESSION_TRANSFORM#msg_135427

To answer question, yes.
You can export the schema with rows=n and import with show=y and logfile=somelogfile.
This somelogfile will have all the ddl you want.
But, it is not "pretty".
Else
You need to write some sql statement joining several v$views to get DDL
and re-invent the wheel by duplicating what dbms_metadata already does it for you.
Re: How to get the oracle table structure? [message #205579 is a reply to message #205577] Sun, 26 November 2006 07:12 Go to previous messageGo to next message
sathyguy
Messages: 31
Registered: January 2006
Member
Thanks for your reply....but....


DBMS_METADATA.GET_DDL('TABLE',TABLE_NAME,'SCOTT')
--------------------------------------------------------------------------------

CREATE TABLE "SCOTT"."BONUS"
( "ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"SAL" NUMBER,
"COMM" NUMBER
) 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 "SYSTEM"

====================

see the extra entries....PCTFREE, STORAGE ETC....
if i need only the create table structure and its related constraints what should i do?

also, i am having one more doubt.
if i just copy the above statement will it work when i try to create a table......

[Updated on: Sun, 26 November 2006 07:12]

Report message to a moderator

Re: How to get the oracle table structure? [message #205581 is a reply to message #205579] Sun, 26 November 2006 07:32 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
As said in the URL
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);

will not display the storage/tablespace information.
I am not aware of any method that supresses pctfree etc. It is actually derived from tablespace, unless explicitly set in table.

>>if i just copy the above statement will it work when i try to create a table......
Try it.
Re: How to get the oracle table structure? [message #217042 is a reply to message #205581] Wed, 31 January 2007 08:12 Go to previous messageGo to next message
agostino_neto
Messages: 180
Registered: July 2005
Senior Member
Hi,
Does somebody know how to extract table structure without the name of the owner ?
I would like to have CREATE TABLE "BONUS" and not CREATE TABLE "SCOTT"."BONUS".

Does somebody know how to avoid having description splitting into many lines
For example, instead of having "NOT NULL ENABLE", I am having
line1: NOT NULL EN
line2: ABLE

Thanks.
Re: How to get the oracle table structure? [message #217059 is a reply to message #217042] Wed, 31 January 2007 09:12 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Why not continue in other thread?
>>I would like to have CREATE TABLE "BONUS" and not CREATE TABLE "SCOTT"."BONUS".
Use a replace on SCHEMANAME.
>>Does somebody know how to avoid having description splitting into many lines
Answered previously. Format your column accordingly.
Re: How to get the oracle table structure? [message #217072 is a reply to message #217059] Wed, 31 January 2007 10:14 Go to previous messageGo to next message
agostino_neto
Messages: 180
Registered: July 2005
Senior Member
I will have a file (programmatically) with a list of ddl for different schemas.
Re: How to get the oracle table structure? [message #217080 is a reply to message #217072] Wed, 31 January 2007 10:46 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Still, you need to feed the value of SCHEMA_OWNER programatically. Right?
Replace there~!. I believe, there is no other option in 9i (not sure about 10g).
scott@9i > var sname varchar2(30);
scott@9i > exec :sname :='SCOTT';

PL/SQL procedure successfully completed.

scott@9i > SELECT replace(DBMS_METADATA.GET_DDL('INDEX',D.INDEX_NAME)||';','"'||:sname||'".') FROM DBA_INDEXES d
  2  where owner=:sname;

REPLACE(DBMS_METADATA.GET_DDL('INDEX',D.INDEX_NAME)||';','"'||:SNAME||'".')
--------------------------------------------------------------------------------

  CREATE UNIQUE INDEX "SYS_C001612" ON "DEPT" ("DEPTNO")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  TABLESPACE "USERS"
Previous Topic: installing oracle 10g r2 on redhat4 64bits
Next Topic: GRUB loading, please wait... Error 22
Goto Forum:
  


Current Time: Fri Mar 29 00:51:38 CDT 2024