CODEWITHSUNDEEP
oracle-databaseplsqlddl
Parvej Islam
Parvej Islam

Reputation: 55

Oracle Object Creation and FlashBack

I need to create a table from another table along with it's indexes and constraints in another schema in Oracle. I know about CTAS syntax but it doesn't take the indexes and constraints with it. Is there any way to do it?

Also is there any way to flashback procedure, triggers or package after dropping?

Upvotes: 1

Views: 184

Answers (2)

APC
APC

Reputation: 146309

The simplest approach is to treat DDL statements like any other piece of application code, and keep them as scripts in a source control repository.

However, it's easy to be wise after the event. If you're working in an environment where the schema is a bit of a free fire zone there are various options.

The best thing is to use DBMS_METADATA to re-create the DDL statements. These can be saved as scripts, run in other schemas and - crucially - stored somewhere which gets backed-up, ideally source control.

To generate all the DDL for a table and its dependent objects is reasonably straightforward. The DBMS_METADATA functions return clobs, which is not ideal but simple enough to spool them out in SQL*Plus:

SQL> set long 10000 
SQL> set heading off
SQL> spool create_tab_t23.sql
SQL> select dbms_metadata.get_ddl('TABLE', 'T23') from dual;
SQL> select dbms_metadata.get_dependent_ddl('INDEX', 'T23') from dual;
SQL> select dbms_metadata.get_dependent_ddl('TRIGGER', 'T23') from dual; 
SQL> spool off

Having to specify the individual object types is a bit of a nausea. Fortunately most IDEs (Oracle SQL Developer, PLSQL Developer, TOAD, etc) provide handy right-click menu options to handle all this for us.

Upvotes: 1

MileP
MileP

Reputation: 101

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.

Upvotes: 0

Related Questions