OraFAQ Forum: SQL & PL/SQL » copy table structure

Home » SQL & PL/SQL » SQL & PL/SQL » copy table structure

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

copy table structure [message #661350]

Wed, 15 March 2017 11:46

asliyanage
Messages: 60
Registered: January 2017

Member

I need to create a copy of table in same database. what is the best way to do this without creating manually table ?
copy means same columns with same data types.

Report message to a moderator

Re: copy table structure [message #661351 is a reply to message #661350]

Wed, 15 March 2017 11:48

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

Senior Member
Account Moderator

- CREATE TABLE ... AS SELECT
- expdp/impdp
- dbms_metadata.get_ddl

Report message to a moderator

Re: copy table structure [message #661353 is a reply to message #661351]

Wed, 15 March 2017 11:59

asliyanage
Messages: 60
Registered: January 2017

Member

i don't need to copy data, i just need the structure

Report message to a moderator

Re: copy table structure [message #661354 is a reply to message #661353]

Wed, 15 March 2017 12:04

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

asliyanage wrote on Wed, 15 March 2017 09:59

i don't need to copy data, i just need the structure

Nobody here prevents you from doing as you desire.
Please proceed without delay.

Report message to a moderator

Re: copy table structure [message #661355 is a reply to message #661353]

Wed, 15 March 2017 12:25

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

Senior Member
Account Moderator

asliyanage wrote on Wed, 15 March 2017 17:59

i don't need to copy data, i just need the structure

Yes, do it in one the ways I said, there may be others.

Report message to a moderator

Re: copy table structure [message #661356 is a reply to message #661355]

Wed, 15 March 2017 16:04

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

For CTAS you just add WHERE 1=2

Report message to a moderator

Re: copy table structure [message #661357 is a reply to message #661356]

Wed, 15 March 2017 16:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

cookiemonster wrote on Wed, 15 March 2017 14:04

For CTAS you just add WHERE 1=2

or
WHERE 0 = 1

Report message to a moderator

Re: copy table structure [message #661358 is a reply to message #661357]

Wed, 15 March 2017 21:24

asliyanage
Messages: 60
Registered: January 2017

Member

thanks all

Report message to a moderator

Re: copy table structure [message #661363 is a reply to message #661358]

Thu, 16 March 2017 01:32

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

Senior Member
Account Moderator

So what is the conclusion?
Which way did you choose?
Why?

Report message to a moderator

Re: copy table structure [message #661396 is a reply to message #661363]

Thu, 16 March 2017 11:55

asliyanage
Messages: 60
Registered: January 2017

Member

I used below

create table employees_copy as select * from employees where 1=0;

this will not copy any rows to new table

Report message to a moderator

Re: copy table structure [message #661398 is a reply to message #661396]

Thu, 16 March 2017 12:01

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

Senior Member
Account Moderator

Yes but you have neither the constraints nor the indexes on the table.

Report message to a moderator

Re: copy table structure [message #661412 is a reply to message #661398]

Thu, 16 March 2017 19:20

asliyanage
Messages: 60
Registered: January 2017

Member

you mean this will not copy constraints and indexes, then what is the solution?

Report message to a moderator

Re: copy table structure [message #661413 is a reply to message #661412]

Thu, 16 March 2017 19:23

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

- expdp/impdp
- dbms_metadata.get_ddl

Report message to a moderator

Re: copy table structure [message #661420 is a reply to message #661412]

Fri, 17 March 2017 02:29

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

Senior Member
Account Moderator

asliyanage wrote on Fri, 17 March 2017 01:20

you mean this will not copy constraints and indexes, then what is the solution?

To read more than the first line of our posts.

Report message to a moderator

Re: copy table structure [message #661441 is a reply to message #661420]

Fri, 17 March 2017 12:16

asliyanage
Messages: 60
Registered: January 2017

Member

ok michel. tnx

Report message to a moderator

Re: copy table structure [message #661445 is a reply to message #661441]

Fri, 17 March 2017 13:26

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

Senior Member
Account Moderator

Did you succeed to do it with the other methods?

Report message to a moderator

Re: copy table structure [message #661519 is a reply to message #661445]

Wed, 22 March 2017 08:27

asliyanage
Messages: 60
Registered: January 2017

Member

other methods mean what methods? i tried only this method

Report message to a moderator

Re: copy table structure [message #661523 is a reply to message #661519]

Wed, 22 March 2017 09:08

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

The first reply in this thread, from Michel, lists three methods. The two you skipped were repeated further down.
Pay attention to what others write.

Report message to a moderator

Previous Topic:	Conditional Join Logic
Next Topic:	Orcale: Missing Group By expression working

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 12:19:25 CDT 2024