MarkView Database - Importing Table Data

Question / Problem:

It might occasionally be useful to import some table data into an internal schema.  In particular, we sometimes need to import mverp_vendor, mvap_po_lim_loc_mv, or mvap_receipts_mv while troubleshooting issues in the KTM project.

Answer / Solution: 

The easiest/fastest/only reasonable way to get a large amount of table data into an internal schema, should you need to do so, is to have the customer use DataPump (expdp) to export it and then to use DataPump (impdp) to import it.

To export (in case the customer needs to know how to do it:)
For materialized views, the command should look like this.  Note that the escape character (ie. \) is required in internal tests even though it doesn't appear in Oracle's documentation
$ expdp system/welcome01 schemas=markview include=materialized_view:\"=\'MVAP_PO_LIM_LOC_MV\'\" directory=load_dir dumpfile=lim_loc_mv.dmp logfile=limloc_export.log

For tables, it should look like this
$ expdp system/welcome01 schemas=markview include=table:\"=\'MVAP_PO_LIM_LOC_MV\'\" directory=load_dir dumpfile=lim_loc_mv3.dmp logfile=limloc_export3.log

Once the customer has provided a dmp file:

1. Drop the dmp file into a directory on the vm 

2. Connect to sqlplus as SYSDBA while logged into vm as appmgr01

$ sqlplus / as SYSDBA

3. Create a directory object in Oracle, or choose one that already exists.  DATA_PUMP_DIR likely exists, but if not:
SQL> create or replace directory load_dir as '/appl01/home/appmgr01/LoadFiles';

4. Grant directory access to MARKVIEW, if MARKVIEW doesn’t already have it
SQL> grant read,write on directory load_dir to markview;

5. Backup and truncate the table
SQL> create table mvap_po_lim_loc_mv_bk as select * from mvap_po_lim_loc_mv;
SQL> truncate table mvap_po_lim_loc_mv;

6. Run a command similar to this in order to do the import.  “APPEND” is required to add the rows to an existing table.  If the table does not already exist, the “Backup and truncate” step is skipped and the table_exists_action argument can be omitted
$ impdp markview/markview tables=MVAP_PO_LIM_LOC_MV directory=LOAD_DIR dumpfile=markview_mvap_po_lim_loc_mv.dmp logfile= limloc_import.log table_exists_action=APPEND

Note: If you are importing a materialized view you still have to get the underlying table exported and that is what you import.  don't bother getting a dmp of the mat view

Applies to: