Home » SQL & PL/SQL » SQL & PL/SQL » How to create a duplicate table in oracle without using select,create,insert etc. commands
How to create a duplicate table in oracle without using select,create,insert etc. commands [message #255506] Wed, 01 August 2007 01:42 Go to next message
shweta.bansal
Messages: 4
Registered: August 2007
Junior Member
Hi all,
I want to create a table abc similar to already exixting table xyz ,but without using select ,insert or create commands.my table consists appx 20 lakhs records and hence select query would take a lot of time.
Please suggest if there exists any alternative.


Thanks,
Shweta...

[mod-edit] removed those famous irrelevant words that newbies always seem to use.

[Updated on: Wed, 01 August 2007 07:59] by Moderator

Report message to a moderator

Re: How to create a duplicate table in oracle without using select,create,insert etc. commands [message #255511 is a reply to message #255506] Wed, 01 August 2007 01:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I want to create a table [...] without using [...] create commands

I want a house but without anyone building it.
Am I the only one to think this is quite silly?

Regards
Michel
Re: How to create a duplicate table in oracle without using select,create,insert etc. commands [message #255515 is a reply to message #255511] Wed, 01 August 2007 01:54 Go to previous messageGo to next message
shweta.bansal
Messages: 4
Registered: August 2007
Junior Member
hey..
its not silly..
there must be some way which should allow me to create a duplicate table with 20 lakhs records in 10-15 seconds..select will take a lot of time,hence i want to avoid it.
just wanted to know if there is any other way to do it.
Re: How to create a duplicate table in oracle without using select,create,insert etc. commands [message #255519 is a reply to message #255515] Wed, 01 August 2007 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"create table as select" is the fastest way.

You want to create without create?
You want to select without select?

Regards
Michel
Re: How to create a duplicate table in oracle without using select,create,insert etc. commands [message #255521 is a reply to message #255515] Wed, 01 August 2007 02:07 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
shweta.bansal wrote on Wed, 01 August 2007 08:54
hey..
its not silly..
Read your question again: I want to create without create...I understand the response. Why do people insist on using Oracle's functionality without using the keywords that were invented for that functionality? Select without select...order without order by...create without create... . I really don't understand it.

shweta.bansal wrote on Wed, 01 August 2007 08:54
there must be some way which should allow me to create a duplicate table with 20 lakhs records in 10-15 seconds..
I don't have the slightest clue what lakhs are but you are not only limited to Oracle's performance but also machine resources, I/O takes time. Lahks of time perhaps? Who knows...

I'd use EXP/IMP...or CTAS (create table as select)

shweta.bansal wrote on Wed, 01 August 2007 08:54
select will take a lot of time,hence i want to avoid it.
A table creation shouldn't be a process you do over and over again. No one benefits from a 'dynamic data model'.
shweta.bansal wrote on Wed, 01 August 2007 08:54
just wanted to know if there is any other way to do it.


- EXP/IMP
- CTAS => 'create' keyword is necessary, so it doesn't meet your requirements.
- SQL*Plus COPY command (?)

Re: How to create a duplicate table in oracle without using select,create,insert etc. commands [message #255533 is a reply to message #255506] Wed, 01 August 2007 02:24 Go to previous messageGo to next message
Raaghav
Messages: 5
Registered: August 2007
Location: INDIA
Junior Member

Hi shweta,

I don't think there is no other way to create another table without using create and select with a false conditon.

Regards,
Raaghav.
Re: How to create a duplicate table in oracle without using select,create,insert etc. commands [message #255691 is a reply to message #255506] Wed, 01 August 2007 08:59 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
If it is a time issue then simply do the following


create table new_table as 
select * from old_table
where 1=2;


This will create an exact copy of the original table (without constraints or indexes) without trying to copy over any rows from the first table.

[Updated on: Wed, 01 August 2007 09:00]

Report message to a moderator

Re: How to create a duplicate table in oracle without using select,create,insert etc. commands [message #256031 is a reply to message #255506] Thu, 02 August 2007 16:22 Go to previous messageGo to next message
charansundaram
Messages: 8
Registered: August 2007
Location: INDIA
Junior Member
hi,

Try this one

create table duplicate_X as select * from X
Re: How to create a duplicate table in oracle without using select,create,insert etc. commands [message #256033 is a reply to message #256031] Thu, 02 August 2007 16:24 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
charansundaram wrote on Thu, 02 August 2007 15:22
hi,

Try this one

create table duplicate_X as select * from X


Did you read the question?
Re: How to create a duplicate table in oracle without using select,create,insert etc. commands [message #256398 is a reply to message #255506] Sat, 04 August 2007 04:54 Go to previous messageGo to next message
shahidmughal
Messages: 91
Registered: January 2006
Location: Faisalabad Pakistan
Member

SALAAM

YOU CAN DO THIS BY USING oracle enterprise manager consol

login to Oem then Click on schema node then under this node find the specified user then click tables node .

you will find the tables of whole schema
select the desired table Right click on the table and click on the Option (Create Like...)

then specify the new table's name and click create.

this is how you can create table using any sort of query in SQL.

Regards

Muhammad Shahid Mughal
I.T Manager
Fivestar International
Faisalabad Pakistan
Re: How to create a duplicate table in oracle without using select,create,insert etc. commands [message #256410 is a reply to message #256398] Sat, 04 August 2007 07:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
this is how you can create table using any sort of query in SQL.

This is a corrupt argument as the tool generates and executes the SQL for you.
In this case I have another solution: put the create statement in a script and then call the script, you then don't use SQL as you just call a script.

Regards
Michel
Re: How to create a duplicate table in oracle without using select,create,insert etc. commands [message #256531 is a reply to message #256410] Sun, 05 August 2007 09:54 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Nice Reply Smile
Previous Topic: ORA-20000, ORA-00933, why no NO DATA FOUND (merged)
Next Topic: ora-997 error
Goto Forum:
  


Current Time: Fri Dec 02 20:59:25 CST 2016

Total time taken to generate the page: 0.16509 seconds