Copy database tables

This page describes how to copy tables from dev to test to production using external tables

On all three databases - dev, test and production - there is a package called MIGRATETABLES which is designed to migrate the following tables:

• GENE_SYMBOLS
• EXPRESSION_QTLS
• PROBESETS
• EXON_IDENTIFIERS
• HERITABILITY
• AFFY_EXON_PROBES
• AFFY_EXON_PROBESET
• SNPS
• LOCATION_SPECIFIC_EQTL

Check to be sure the structure of these tables has not changed. Otherwise, the package will not work.

Basic Idea

To copy table X from dev to test, for example:

1. Create an external table external_X on dev with the data from X.
2. Copy the dmp file from the EXTERNAL_TABLE_DIR location for dev to the EXTERNAL_TABLE_DIR location for test.
3. Create an external table external_X on test with the dmp file
4. Backup up X on test
5. Copy the data from external_X to X on test

Detailed Steps

1. Look at the procedure MIGRATETABlES.runAllMigrations
2. On dev, run datapumpWrite
3. Copy the dmp files from /data/userFiles/OracleExternalFiles/Development to /data/userFiles/OracleExternalFiles/Test on phenogen
4. On test, run datapumpRead
5. On test, run createTableForMigration
6. On test, run copyTableOnDestination

These steps need more work ... Next release finalize the packages.

Go back to Database Update Procedures