[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 13192
  • Last Modified:

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> SET HEADING OFF
SQL> SET PAGESIZE 100
SQL> SET LONG 10000
 
SQL> spool c:\ddl.sql
 
SQL> select dbms_metadata.get_ddl(object_type,object_name)
     from user_objects;
SQL> spool off
 
SQL>
OR select DBMS_METADATA.GET_DDL('INDEX','<INDEX NAME>') from DUAL;

I tried
CREATE TABLE LETTERS_BAK
 AS SELECT * FROM LETTERS;

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

Thanks
0
Dovberman
Asked:
Dovberman
3 Solutions
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
1st:
select dbms_metadata.get_ddl('TABLE', 'LETTERS') from dual;

Open in new window


2nd:
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.
0
 
sventhanCommented:
You could also use oracle EXPORT tool (datapump) to get the results.
0
 
DovbermanAuthor Commented:
I don'tget it.

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

Please clarify.

Thanks,
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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.
0
 
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...
0
 
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.
0
 
DovbermanAuthor Commented:
Thank you.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now