OraFAQ Forum: Linux » How to get the oracle table structure?

Home » Infrastructure » Linux » How to get the oracle table structure?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to get the oracle table structure? [message #205558]

Sun, 26 November 2006 01:28

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

Report message to a moderator

Re: How to get the oracle table structure? [message #205577 is a reply to message #205558]

Sun, 26 November 2006 06:37

Mahesh Rajendran
Messages: 10708
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.

Report message to a moderator

Re: How to get the oracle table structure? [message #205579 is a reply to message #205577]

Sun, 26 November 2006 07:12

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

Mahesh Rajendran
Messages: 10708
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.

Report message to a moderator

Re: How to get the oracle table structure? [message #217042 is a reply to message #205581]

Wed, 31 January 2007 08:12

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.

Report message to a moderator

Re: How to get the oracle table structure? [message #217059 is a reply to message #217042]

Wed, 31 January 2007 09:12

Mahesh Rajendran
Messages: 10708
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.

Report message to a moderator

Re: How to get the oracle table structure? [message #217072 is a reply to message #217059]

Wed, 31 January 2007 10:14

agostino_neto
Messages: 180
Registered: July 2005

Senior Member

I will have a file (programmatically) with a list of ddl for different schemas.

Report message to a moderator

Re: How to get the oracle table structure? [message #217080 is a reply to message #217072]

Wed, 31 January 2007 10:46

Mahesh Rajendran
Messages: 10708
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"

Report message to a moderator

Previous Topic:	installing oracle 10g r2 on redhat4 64bits
Next Topic:	GRUB loading, please wait... Error 22

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jan 22 03:37:25 CST 2025