OraFAQ Forum: SQL & PL/SQL » how to get script of table in sql plus

Home » SQL & PL/SQL » SQL & PL/SQL » how to get script of table in sql plus (oracle 8i)

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

how to get script of table in sql plus [message #617538]

Tue, 01 July 2014 07:37

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

How to get create table script of any table in sql plus . I am using Oracle 8i .

I found this over internet but it is not working:-

set heading off;
set echo off;
Set pages 999;
set long 90000;
 
spool ddl_list.sql
 
select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;
 
select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;
 
spool off;

Thanks

Report message to a moderator

Re: how to get script of table in sql plus [message #617539 is a reply to message #617538]

Tue, 01 July 2014 07:39

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

export + import with SHOW=Y parameter

SY.

Report message to a moderator

Re: how to get script of table in sql plus [message #617540 is a reply to message #617538]

Tue, 01 July 2014 07:44

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Or, as a good exercise to improve your knowledge of Oracle catalog and SQL, do it yourself querying DBA_TABLES, DBA_TAB_COLUMNS, DBA_SEGMENTS and, if you want constraints and indexes, DBA_CONSTRAINTS and DBA_INDEXES (this is what DBMS_METADATA does).

Report message to a moderator

Re: how to get script of table in sql plus [message #617543 is a reply to message #617540]

Tue, 01 July 2014 07:48

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I found this over internet but it is not working:-
you should only read thing published last Century to be compatible with 8i.

In which Oracle version was DBMS_METADATA first released.

Report message to a moderator

Re: how to get script of table in sql plus [message #617546 is a reply to message #617538]

Tue, 01 July 2014 07:56

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

"it's not working" contains no useful or actionable information.

What DOES it produce, even if not what you think it should?

Report message to a moderator

Re: how to get script of table in sql plus [message #617548 is a reply to message #617546]

Tue, 01 July 2014 08:03

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

OP is on 8i, so "not working" is quite obvious.

SY.

Report message to a moderator

Re: how to get script of table in sql plus [message #617561 is a reply to message #617548]

Tue, 01 July 2014 09:54

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Solomon Yakobson wrote on Tue, 01 July 2014 08:03

OP is on 8i, so "not working" is quite obvious.

SY.

8i. Ah, I missed that detail.

I wonder if the rest of his technology stack is as old . . . Or is this yet another case of an organization upgrading everything except the rdbms.

Report message to a moderator

Re: how to get script of table in sql plus [message #617666 is a reply to message #617561]

Wed, 02 July 2014 10:37

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

what can i do if our client is working on oracle 8i . Shocked

[Updated on: Wed, 02 July 2014 10:38]

Report message to a moderator

Re: how to get script of table in sql plus [message #617668 is a reply to message #617666]

Wed, 02 July 2014 10:41

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

jgjeetu wrote on Wed, 02 July 2014 08:37

what can i do if our client is working on oracle 8i .

asked & previously answered
Michel Cadot wrote on Tue, 01 July 2014 05:44

Or, as a good exercise to improve your knowledge of Oracle catalog and SQL, do it yourself querying DBA_TABLES, DBA_TAB_COLUMNS, DBA_SEGMENTS and, if you want constraints and indexes, DBA_CONSTRAINTS and DBA_INDEXES (this is what DBMS_METADATA does).

Report message to a moderator

Re: how to get script of table in sql plus [message #617675 is a reply to message #617666]

Wed, 02 July 2014 11:58

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

jgjeetu wrote on Wed, 02 July 2014 10:37

what can i do if our client is working on oracle 8i .

the technical solution is as Michael said .. start getting familiar with the relevant data dictionary views.

But apart from that, a bit of fatherly career advice ... if this client is a major part of your current resume, find another job ASAP. You are doing your career no favors being tied to antiquated software, and the longer it goes on the less employable you will be elsewhere.

Report message to a moderator

Re: how to get script of table in sql plus [message #617987 is a reply to message #617675]

Sun, 06 July 2014 07:08

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

bro i am working for govt. organizations , n you don't know how miser govt. organizations are here in India they do not want to spend a single penny bt want everything and meanwhile i am working on one another project i.e for one Telecom Company and i am using 11g for that. but the problem is project based on 11g is quite easier , bt project based on oracle 8i is quite tough. sorry i cant tell the name of organizations. anyways thanks for everyone's suggestion Smile

Report message to a moderator

Re: how to get script of table in sql plus [message #617989 is a reply to message #617987]

Sun, 06 July 2014 07:45

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Gee, government organization that allows to be hacked...

Did you succeed to do what you asked?

Report message to a moderator

Re: how to get script of table in sql plus [message #618201 is a reply to message #617989]

Wed, 09 July 2014 06:11

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

yeah @michel , the problem is solved now, following command is working on oracle 10g :-

set heading off;
set echo off;
Set pages 999;
set long 90000;
 
spool ddl_list.sql
 
select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;
 
select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;
 
spool off;

and meanwhile i am working on your provided topics too. Smile

Report message to a moderator

Re: how to get script of table in sql plus [message #618202 is a reply to message #618201]

Wed, 09 July 2014 06:16

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

jgjeetu wrote on Wed, 09 July 2014 16:41

yeah @michel , the problem is solved now, following command is working on oracle 10g

But throughout the discussion you kept saying,

jgjeetu wrote on Tue, 01 July 2014 18:07

How to get create table script of any table in sql plus . I am using Oracle 8i

jgjeetu wrote on Sun, 06 July 2014 17:38

but the problem is project based on 11g is quite easier , bt project based on oracle 8i is quite tough.

Report message to a moderator

Re: how to get script of table in sql plus [message #618207 is a reply to message #618202]

Wed, 09 July 2014 06:55

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

@Lalit I am using 8i and 11g inside office , n i have oracle 10g at home.
Thanks

Report message to a moderator

Re: how to get script of table in sql plus [message #618213 is a reply to message #618207]

Wed, 09 July 2014 07:49

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Yes, but I believe that the point that Lalit was making was that you implied that the problem for which you were trying to use the dbms_metdata package resided on a database (8i) that does not support that package. Therefore, even if you can get it working at home, you won't be able to get it to work in your office (if you are truly on v8i)

Report message to a moderator

Re: how to get script of table in sql plus [message #618372 is a reply to message #618213]

Fri, 11 July 2014 06:44

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

yeah right @pabolee , that's why i will bring script codes in soft copies with me , when i will visit the client , so that time oracle 8i version will not be problem , n i get enough time from client to solve the problems so no worries , yeah but for knowledge purpose or in case of urgent requirement i will not be able to get script code using oracle 8i and the biggest problem is they don't allow me install Toad. , I have read few topics that @michel and others provided n they were very useful, i can use them to find constraints, column name, size, reference tables etc etc, i can use them together to generate script of any table but that will take a lot of time so till now i have no idea how to get script code using 8i. Isn't there any short way ? thanks

Report message to a moderator

Re: how to get script of table in sql plus [message #618376 is a reply to message #618372]

Fri, 11 July 2014 07:00

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

jgjeetu wrote on Fri, 11 July 2014 17:14

and the biggest problem is they don't allow me install Toad.

That shouldn't be the biggest problem.

Report message to a moderator

Re: how to get script of table in sql plus [message #618381 is a reply to message #618376]

Fri, 11 July 2014 07:21

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

ok thats not a problem , but whats the solution ,tell me that only. Razz

Report message to a moderator

Re: how to get script of table in sql plus [message #618390 is a reply to message #618381]

Fri, 11 July 2014 07:49

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

jgjeetu wrote on Fri, 11 July 2014 17:51

ok thats not a problem , but whats the solution ,tell me that only.

PEBKAC!

Michel already suggested to you,

Michel Cadot wrote on Tue, 01 July 2014 18:14

Or, as a good exercise to improve your knowledge of Oracle catalog and SQL, do it yourself querying DBA_TABLES, DBA_TAB_COLUMNS, DBA_SEGMENTS and, if you want constraints and indexes, DBA_CONSTRAINTS and DBA_INDEXES (this is what DBMS_METADATA does).

Report message to a moderator

Re: how to get script of table in sql plus [message #618413 is a reply to message #618372]

Fri, 11 July 2014 10:04

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Isn't there any short way ?

Yes, of course there is one but as we are bastard we don't post it. Razz

Report message to a moderator

Re: how to get script of table in sql plus [message #618564 is a reply to message #618390]

Sun, 13 July 2014 10:35

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

& i said earlier

Quote:

I have read few topics that @michel and others provided n they were very useful, i can use them to find constraints, column name, size, reference tables etc etc, i can use them together to generate script of any table but that will take a lot of time so till now i have no idea how to get script code using 8i. Isn't there any short way ?

Report message to a moderator

Re: how to get script of table in sql plus [message #618565 is a reply to message #618413]

Sun, 13 July 2014 10:36

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

no , i am serious , you guys are senior than me that's why i asked . Laughing

Report message to a moderator

Re: how to get script of table in sql plus [message #618567 is a reply to message #618565]

Sun, 13 July 2014 11:13

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

I already gave you the answer. Let Oracle do the job for you. Use export + import show=y.

SY.

Report message to a moderator

Re: how to get script of table in sql plus [message #618570 is a reply to message #618565]

Sun, 13 July 2014 12:58

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

jgjeetu wrote on Sun, 13 July 2014 21:06

no , i am serious , you guys are senior than me that's why i asked .

In this entire thread you kept demanding for a solution for 8i DB. Then you said the 11g solution is OK for you. But now you still want to know the solution. Michel, Solomon gave you the solutions for respective requirements. Now what else do you expect?

Moreover, you never have had come back with a feedback to us. Your questions have always been like "So I did this I did that and got an error, now what to do". Please spend some time to follow posring guidelines and forum erhics.

Report message to a moderator

Re: how to get script of table in sql plus [message #618572 is a reply to message #618570]

Sun, 13 July 2014 13:13

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

I don't care about why you said this, on what basis?

jgjeetu wrote on Sun, 06 July 2014 17:38

bro i am working for govt. organizations , n you don't know how miser govt. organizations are here in India they do not want to spend a single penny

How many IT working models do you even know? How many clients have you ever had worked with to post such a comment including a country name? If you don't know something then honestly say you don't know, your honesty would never be questioned. Refrain youself about speaking of clients and countries when you are not in a position to say so.

Report message to a moderator

Re: how to get script of table in sql plus [message #618574 is a reply to message #618572]

Sun, 13 July 2014 13:38

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

The pot calling the kettle black, eh?

Report message to a moderator

Re: how to get script of table in sql plus [message #618576 is a reply to message #618574]

Sun, 13 July 2014 14:08

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Littlefoot wrote on Mon, 14 July 2014 00:08

The pot calling the kettle black, eh?

Perhaps you are right LF, and there is no denying the fact that I was not aware of such technical thing in the link you mentioned.

Admitting about not knowing something is good, but speaking ill about something without or with half knowledge is too dangerous. I wouldn't have replied unless I was confident about what I am speaking. Just because OP has an issue with something doesn't give him the right to generalize it, that the same issue exists ubiquitously. Well, we do have our own world where we work, learn but we share our knowledge globally. How does anybody's issue would ever relate to a client/organization without any proof? And to be specific, OP mentioned a country's name and that the clients(that too govt. Org.) are miser? Where did that come from, eh?

Report message to a moderator

Previous Topic:	how to display any particular column first using select * query.
Next Topic:	Result as per Double Outer Join ?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 11:46:58 CDT 2024