steps to export- import table structure

Hello,

 

after each insert of structure and substance, i run an export of the table structure and substance. (SYSADMIN the owner of the cartridge)

 

Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

Export done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user SYSADMIN 

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user SYSADMIN 

About to export SYSADMIN's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export SYSADMIN's tables via Conventional Path ...

. . exporting table                JCHEMPROPERTIES         22 rows exported

. . exporting table             JCHEMPROPERTIES_CR          1 rows exported

. . exporting table                JC_IDX_PROPERTY          6 rows exported

. . exporting table                    JC_IDX_UDOP          0 rows exported

. . exporting table                        SUB_LOT        313 rows exported

. . exporting table                  SUB_STRUCTURE      50961 rows exported

. . exporting table               SUB_STRUCTURE_UL       5105 rows exported

. . exporting table                  SUB_SUBSTANCE      90072 rows exported

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting operators

. exporting referential integrity constraints

. exporting triggers

. exporting indextypes

. exporting bitmap, functional and extensible indexes

. exporting posttables actions

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting statistics

Export terminated successfully without warnings.

 

 

Do you have a procedure to import data of structure tables in a new structure table (with same column)?

For table substance, i use import command and i recreate and reinitialize the sequence.

 

Thanks

Hi,

 

We don't have such option. However, there is a workaround:

1. create an empty structure table (and index) with the name you want (<new_table>)

2. use the import tool of Oracle to import the tables to a different (temporary) schema (<imported_table>)

3. copy the data from the imported table to the empty structure table like this:

INSERT INTO <new_table> SELECT * FROM <imported_table>

With these you'll have all the exported data in the new table.

We are a bit surprised that all the data is exported regularly after every insert. It is very dangerous to export in a multi-user environment (e.g. sequences and tables can be inconsistent if a new column is added to the table during the export.

Regards,

Tamas