How to copy an oaracle table including data, indexes, and constraints

I am new to Oracle. I know how to do this using MS SQL Server.

How is this done in Oracle?

SQL> spool c:\ddl.sql
SQL> select dbms_metadata.get_ddl(object_type,object_name)
     from user_objects;
SQL> spool off

I tried

This gave me the table including data, but not the indexes and constraints.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alexander Eßer [Alex140181]Software DeveloperCommented:
select dbms_metadata.get_ddl('TABLE', 'LETTERS') from dual;

Open in new window

select dbms_metadata.get_dependent_ddl('TABLE', 'LETTERS') from dual;

Open in new window

These 2 statements should provide the DDLs you'll need to get a full clone/copy of your table LETTERS.
You could also use oracle EXPORT tool (datapump) to get the results.
DovbermanAuthor Commented:
I don'tget it.

I need to create a table named Letters_bak from the existing table named Letters.

Please clarify.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mark GeerlingsDatabase AdministratorCommented:
The easiest way to copy an entire Oracle table (structure, contents, indexes, constraintes, triggers, etc.) is to use Oracle's export and import utiilities (expdp and impdp).  These are command-line utilities that you run on the database server using parameters that you provide.  Or, you can use OEM (Oracle Enterprise Manager) to run these for you.  Note that they they depend on having at least one "logical directory" defined where the "dump" file can get written to by export and read from by import.

This method work well when you want to copy a table from one schema to another, or from one database to another, and keep the same table name.  If however your goal is to create a copy table in the same schema, but with a different name, then the process gets more complex.  You can still use export, but then with import instead of doing the actual import directly, you have import create a text file for you that contains all of the SQL commands it finds in the export file.  Then you edit that text file to change the index, constraint and trigger names that need to be changed plus change the table name in those commands to the new table_name (but do not change the table name in the "create table..." command). Then rename the existing table to something else and run just the "create table ..." command (with the original table_name) from the script file.  Next, run import to get just the data.  Then rename the new table to the name you want it to have and rename the original table to its original name.  After that, you manually run the other SQL scripts from the script file.  You don't want those triggers, constraints and indexes in place when you do the actual data import.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alexander Eßer [Alex140181]Software DeveloperCommented:
Note that they they depend on having at least one "logical directory" defined where the "dump" file can get written to by export and read from by import.
Just as an add-on: This is true if you use (the "new"/actual) Export/Import via DataPump. If you're using the "old fashioned" exp/imp, then you actually don't need such Oracle directories to work with...
DovbermanAuthor Commented:
This is more complex than I had anticipated.  I am running Oracle 11g R2. I used SQL to simply copy a legacy production table as a backup.  I can work with the backup and leave the production table untouched.  Someone has written a 3 page long sql statement to support an ad hoc report  where the end user selects from as many as 17 criteria.  The database cols have plenty of nulls.  nvls are used to create constant values.  The person who wrote the sql statement no longer works for the company.

The database lacks normalization.

Developer and end user documentation does not exist.
DovbermanAuthor Commented:
Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

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.