Home » SQL & PL/SQL » SQL & PL/SQL » Mirror Table
Mirror Table [message #194284] Thu, 21 September 2006 07:07 Go to next message
alibawa
Messages: 11
Registered: May 2006
Junior Member
Dear All,

I want to create a mirror table by mirror i mean

Lets say i have a table abc

i want to create a table xyz which has exactly the same attributes at abc

Thanks in advance
Re: Mirror Table [message #194286 is a reply to message #194284] Thu, 21 September 2006 07:09 Go to previous messageGo to next message
sandeepk7
Messages: 137
Registered: September 2006
Senior Member

CREATE TABLE xyz AS SELECT * FROM abc WHERE 1=2


** 1=2 if you don't want to copy data of abc to xyz.


Sandy
Re: Mirror Table [message #194288 is a reply to message #194286] Thu, 21 September 2006 07:28 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
This will not create triggers, indexes etc.

If You need those, use EXP with rows=N and then IMP.
An alternative would be to extract the DML from the database. Here the options depend on Your version of the database.

Br
Kim
Re: Mirror Table [message #194294 is a reply to message #194286] Thu, 21 September 2006 07:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This won't get you the indexes, triggers, or any other constraints except Not Null ones.

What you can do is:
select dbms_metadata.get_ddl('TABLE','<table name>') from dual;

select dbms_metadata.get_dependent_ddl('INDEX','<table_name>') from dual;

select dbms_metadata.get_dependent_ddl('TRIGGER','<table_name>') from dual;

This will get you the SQL to create copies of the tables. Once you've created those, you can do a simple
INSERT INTO table_copy SELECT * FROM table_original;

to transfer the data.

Or, as a totally diffferent approach, you could do an EXP of the table (using the TABLES parameter in the export), and then use IMP to import this into a different schema.

[edit: Drat, @Kimant beat me to it]

[Updated on: Thu, 21 September 2006 07:37]

Report message to a moderator

Re: Mirror Table [message #194969 is a reply to message #194294] Tue, 26 September 2006 06:02 Go to previous messageGo to next message
be2sp1
Messages: 52
Registered: September 2005
Location: India
Member
JRowbottom,

I tried firing the sql statements (DBMS_METADAT package) mentioned be you, but it doesn't retreive anything even when the table is existing in the Database and also indexes are present.

Thanks
Re: Mirror Table [message #194975 is a reply to message #194969] Tue, 26 September 2006 06:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Does it actually return a blank row, or does it give an error message.

Are you logged on as the schema owner, or if not, are you specifying the Schema in the call to DBMS_METADATA?
Re: Mirror Table [message #195891 is a reply to message #194975] Tue, 03 October 2006 01:02 Go to previous message
be2sp1
Messages: 52
Registered: September 2005
Location: India
Member
Thanks its working.....my mistake.
Previous Topic: USING INDEX TABLESPACE
Next Topic: sorting the month
Goto Forum:
  


Current Time: Fri Dec 02 14:34:29 CST 2016

Total time taken to generate the page: 0.21605 seconds