CREATE TABLE newTable as (SELECT * FROM oldTable WHERE 1=2)

I have number of tables and I want to make same tables again with different names without keeping the data, ie,  copy their structures only. I am using "CREATE TABLE newTable as (SELECT * FROM oldTable WHERE 1=2)". Everyting is fine but the newTable created keeps at least the NOT NULL constraints while throwing out all other type of constraints. My questions are :

Q1. How to modify the above statement so that even NOT NULL constraints are thrown out.

Q2. Just opposite of what I asked above. How to modify the above statement so that ALL (yes all) constraints are intact in the newTable.

Who is Participating?
VidyadharsConnect With a Mentor Commented:
you can use "copy from .." command. Syntax is:

Copy from username1/password1@connectstring1 to username2/password2@connectstring2 create newtable using-
select * from oldtable where 1=2;

you can user the below procedure to create a table without any contsraint:
create or replace procedure CreateAsSelect (tab_name in varchar2)

     string     varchar2(1000) := '';
     new_table     varchar2(50);
     col_name     varchar2(100) := '22';
     cursor c1 is select column_name || ' ' || data_type || '(' || data_length || ')'
     from user_tab_columns
     where table_name = tab_name order by column_id;

     new_table := tab_name || '_TMP';
     string := 'create table ' || new_table || '(';

     open c1;
     while col_name is not null LOOP
          fetch c1 into col_name;
          exit when c1%NOTFOUND;
          string := string || col_name || ',';
     end LOOP;
     close c1;
     string := substr(string,1,length(string)-1);
     string := string || ');';

end CreateAsSelect;
In addition of the above you can use the "ACCEPT" with variable if you do not know the table values initially.  

Good luck.
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

brown_ism  --  you should have posted as a comment --  not as a PROPOSED ANSWER
your answers doesn't really provide more than what DBAORA has already suggested.
read the link:
javaq092999Author Commented:
See dbalaski comments...
You could also do something like this in a higher-level GUI tool.  For example, in TOAD (free demo at ), you go to Database / Schema Browser, then select the table you wish to copy, and click on the Create Script icon.  It will bring up a series of options to allow you to include/not include Indexes, Constraints, etc., then paste that script to the clipboard or to a file.  Just change the table name in that script and run it to create the copy table.
ADMINISTRATION WILL BE CONTACTING YOU SHORTLY.  Moderators Computer101 or Netminder will return to finalize these if still open in seven days.  Please post closing recommendations before that time.

Question(s) below appears to have been abandoned. Your options are:
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you. You must tell the participants why you wish to do this, and allow for Expert response.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question. Again, please comment to advise the other participants why you wish to do this.

For special handling needs, please post a zero point question in the link below and include the question QID/link(s) that it regards.
Please click the Help Desk link on the left for Member Guidelines, Member Agreement and the Question/Answer process.

Please click you Member Profile to view your question history and keep them all current with updates as the collaboration effort continues, to track all your open and locked questions at this site.  If you are an EE Pro user, use the Power Search option to find them.  Anytime you have questions which are LOCKED with a Proposed Answer but does not serve your needs, please reject it and add comments as to why.  In addition, when you do grade the question, if the grade is less than an A, please add a comment as to why.  This helps all involved, as well as future persons who may access this item in the future to seek help.

To view your open questions, please click the following link(s) and keep them all current with updates.

To view your locked questions, please click the following link(s) and evaluate the proposed answer.

------------>  EXPERTS:  Please leave any comments regarding your closing recommendations if this item remains inactive another seven (7) days.  Also, if you are interested in the cleanup effort, please click this link

Moderators will finalize this question if still open in 7 days, by either moving this to the PAQ (Previously Asked Questions) at zero points, deleting it or awarding expert(s) when recommendations are made, or an independent determination can be made.  Expert input is always appreciated to determine the fair outcome.
Thank you everyone.
Moderator @ Experts Exchange

P.S.  For any year 2000 questions, special attention is needed to ensure the first correct response is awarded, since they are not in the comment date order, but rather in Member ID order.
javaq092999Author Commented:
Thank you for returning and finalizing this question.

Please take an extra moment when you grade questions less than an "A" to comment as to why.  This helps not only the experts who have helped you, but also others in the future who may access this question to get help.  If you've found alternative solutions to achieve your goal and add it here, it further adds value to this item which has not moved to our PAQ (Previously Asked Question database).  Anytime that you need our help, for example, if the grade was chosen in error, let us know by posting a zero point question in the Community Support with the URL to the question involved, we're happy to help.  The points to you are the same, regardless of grade assigned, but the expert points which flow are directly impacted by grades assigned.  There is more about this in the HELP DESK link regarding Questions and Answers.

Moondancer - EE Moderator
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.