Home » SQL & PL/SQL » Client Tools » How to create a table in another schema as in existing schema (Oracle 11.1.0.6.0 Vista)
How to create a table in another schema as in existing schema [message #452883] Mon, 26 April 2010 01:24 Go to next message
nehaverma
Messages: 80
Registered: January 2010
Location: JAIPUR
Member
Hello

I would like to create a table in another schema(CBF) as already exist in my schema(TLC) without data but related indexes,synonyms and grants should be include.
How could I do this without using export import.
I am using TOAD 9.0.1


//Neha
Re: How to create a table in another schema as in existing schema [message #452887 is a reply to message #452883] Mon, 26 April 2010 01:53 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
DBMS_METADATA.get_ddl

or,

Or, In toad you can extract the script content this way:

1. Login to database in toad > Go to schema browser > Select the schema you want to copy the table structure, index etc from
> Click on the table > Go to script on the Right pane> Copy the content

2. Execute the script content in other schema


Thanks
Ved

[Updated on: Mon, 26 April 2010 02:09]

Report message to a moderator

Re: How to create a table in another schema as in existing schema [message #452888 is a reply to message #452887] Mon, 26 April 2010 02:11 Go to previous messageGo to next message
nehaverma
Messages: 80
Registered: January 2010
Location: JAIPUR
Member
Hi

Please explain how to use this when I will create table in other schema.
I just run this command:

select dbms_metadata.get_ddl ('TABLE', 'SMFT_SO_HEADERS', 'TLC') from dual

but what happen I didnt get this.

//Neha
Re: How to create a table in another schema as in existing schema [message #452889 is a reply to message #452888] Mon, 26 April 2010 02:18 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Example:

SET long 9999999
SELECT DBMS_METADATA.get_ddl ('INDEX', index_name)
  FROM user_indexes
 WHERE table_name = 'EMPLOYEES';


Also please go through the documentation here

But, I think second alternative would be easiest way to get all the related ddl as you are working on Toad

Thanks
Ved

[Updated on: Mon, 26 April 2010 02:37]

Report message to a moderator

Re: How to create a table in another schema as in existing schema [message #452894 is a reply to message #452889] Mon, 26 April 2010 02:39 Go to previous messageGo to next message
nehaverma
Messages: 80
Registered: January 2010
Location: JAIPUR
Member
Hi sorry

But this table is having so many columns,indexes,grants,synonyms,triggers and constraints.
So I need to create a table in other schema having all these objects except their data.
Kindly help me out.

And I am not able to use import exp utility.

//Neha
Re: How to create a table in another schema as in existing schema [message #452897 is a reply to message #452894] Mon, 26 April 2010 02:45 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

Or, In toad you can extract the script content this way:

1. Login to database in toad > Go to schema browser > Select the schema you want to copy the table structure, index etc from
> Click on the table > Go to SCRIPT tab on the Right pane > Copy the content

2. Execute the script content in other schema


Thanks
Ved

[Updated on: Mon, 26 April 2010 02:49]

Report message to a moderator

Re: How to create a table in another schema as in existing schema [message #452898 is a reply to message #452894] Mon, 26 April 2010 02:47 Go to previous messageGo to next message
Littlefoot
Messages: 21493
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It's already been answered: if you use TOAD, then use it properly! In Schema Browser, right click table name and select "Create Script" from the menu. Why do you insist on making things complicated when it isn't necessary?
Re: How to create a table in another schema as in existing schema [message #452900 is a reply to message #452894] Mon, 26 April 2010 02:52 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
http://www.nyoug.org/Presentations/2006/September_NYC_Metro_Meeting/200609Baumgartel_Introduction%20to%20DBMS_METADATA.pdf

sriram Smile
Re: How to create a table in another schema as in existing schema [message #452902 is a reply to message #452894] Mon, 26 April 2010 02:56 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

second alternative would be easiest way to get all the related ddl as you are working on Toad


Quote:


1. a) Login to database in toad
b) Go to schema browser
c) Select the schema you want to copy the table structure, index etc from
d) Click on the table
e) Go to SCRIPT tab on the Right pane copy the content or select "Create Script" from the menu

2. Execute the script content in other schema you want to create

After following the above steps are you having any problems?


Thanks
Ved

[Updated on: Mon, 26 April 2010 02:57]

Report message to a moderator

Re: How to create a table in another schema as in existing schema [message #452912 is a reply to message #452902] Mon, 26 April 2010 04:09 Go to previous messageGo to next message
nehaverma
Messages: 80
Registered: January 2010
Location: JAIPUR
Member
HI

THANKS TO ALL.
I JUST RUN ALL THE SCRIPTS RELATED WITH THAT AND I GOT MY SOLUTION.

BEST REGARDS//
NEHA VERMA
Re: How to create a table in another schema as in existing schema [message #452913 is a reply to message #452912] Mon, 26 April 2010 04:13 Go to previous message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Next time please don`t post in upper case
Sriram Smile

[Updated on: Mon, 26 April 2010 04:14]

Report message to a moderator

Previous Topic: Need assistance with passing variable and displaying Select data in TOAD
Next Topic: oracle toad 8.6 install in window 7
Goto Forum:
  


Current Time: Mon Aug 19 18:08:38 CDT 2019