OraFAQ Forum: Client Tools » How to create a table in another schema as in existing schema

Home » Other » Client Tools » How to create a table in another schema as in existing schema (Oracle 11.1.0.6.0 Vista)

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

How to create a table in another schema as in existing schema [message #452883]

Mon, 26 April 2010 01:24

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

Report message to a moderator

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

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

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

Report message to a moderator

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

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

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

Report message to a moderator

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

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

Littlefoot
Messages: 21823
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?

Report message to a moderator

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

ramoradba
Messages: 2457
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

Report message to a moderator

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

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