257 lines
11 KiB
SQL
257 lines
11 KiB
SQL
drop table TEST_TABLE purge;
|
|
|
|
create table TEST_TABLE (col1 date);
|
|
grant all on TEST_TABLE TO PUBLIC;
|
|
|
|
insert into TEST_TABLE values (sysdate);
|
|
BEGIN
|
|
for i in 1 .. 10
|
|
LOOP
|
|
insert into TEST_TABLE select * from TEST_TABLE;
|
|
END LOOP;
|
|
commit;
|
|
END;
|
|
/
|
|
|
|
|
|
SELECT count(1) FROM TEST_TABLE;
|
|
select user from dual;
|
|
|
|
|
|
set serveroutput on
|
|
BEGIN
|
|
dba_own.datapump.export_import(operation => 'EXPORT'
|
|
, job_mode => 'TABLE'
|
|
, directory => 'DATA_DUMP_DIR2'
|
|
, dumpfile => 'vg_test_table.%U.dmp'
|
|
, logfile => 'vg_test_table.exp.log'
|
|
, parallelism => 8
|
|
, schema_name => 'DBAVGUPTA'
|
|
, table_name => 'TEST_TABLE'
|
|
, filesize => '10G'
|
|
, reusefile => 1
|
|
, compression => 'NONE'
|
|
, include_metadata => 0
|
|
, estimate_method => 'BLOCKS'
|
|
);
|
|
END;
|
|
/
|
|
|
|
TRUNCATE TABLE TEST_TABLE;
|
|
|
|
|
|
BEGIN
|
|
dba_own.datapump.export_import(operation => 'IMPORT'
|
|
, job_mode => 'TABLE'
|
|
, directory => 'DATA_DUMP_DIR2'
|
|
, dumpfile => 'vg_test_table.%U.dmp'
|
|
, logfile => 'vg_test_table.imp.log'
|
|
, parallelism => 8
|
|
, schema_name => 'DBAVGUPTA'
|
|
, table_name => 'TEST_TABLE'
|
|
, table_exists_action => 'TRUNCATE'
|
|
, skip_unusable_indexes => 1
|
|
);
|
|
END;
|
|
/
|
|
|
|
|
|
SELECT count(1) FROM TEST_TABLE;
|
|
|
|
|
|
|
|
|
|
|
|
##################################
|
|
# Datapump testing
|
|
##################################
|
|
drop table DBAVGUPTA.TEST_TABLE purge;
|
|
create table DBAVGUPTA.TEST_TABLE (col1 date);
|
|
insert into DBAVGUPTA.TEST_TABLE values (sysdate);
|
|
insert into DBAVGUPTA.TEST_TABLE values (sysdate);
|
|
insert into DBAVGUPTA.TEST_TABLE values (sysdate);
|
|
insert into DBAVGUPTA.TEST_TABLE values (sysdate);
|
|
commit;
|
|
|
|
set serveroutput on
|
|
BEGIN
|
|
dba_own.datapump.export_import(operation => 'EXPORT' /* Valid values EXPORT, IMPORT */
|
|
, job_mode => 'TABLE'
|
|
, directory => 'DATA_DUMP_DIR2'
|
|
, dumpfile => 'vg_test_table.%U.dmp'
|
|
, logfile => 'vg_test_table.exp.log'
|
|
, parallelism => 8
|
|
, schema_name => 'DBAVGUPTA'
|
|
, table_name => 'TEST_TABLE'
|
|
, filesize => '10G'
|
|
, reusefile => 1
|
|
, compression => 'NONE'
|
|
, include_metadata => 1
|
|
);
|
|
END;
|
|
/
|
|
|
|
set serveroutput on
|
|
--drop table DBAVGUPTA.TEST_TABLE_1 purge;
|
|
BEGIN
|
|
dba_own.datapump.export_import(operation => 'IMPORT'
|
|
, job_mode => 'TABLE'
|
|
, directory => 'DATA_DUMP_DIR2'
|
|
, dumpfile => 'vg_test_table.%U.dmp'
|
|
, logfile => 'vg_test_table.imp.log'
|
|
, parallelism => 8
|
|
, schema_name => 'DBAVGUPTA'
|
|
, remap_schema => 'DBAVGUPTA'
|
|
, table_name => 'TEST_TABLE'
|
|
, remap_table => 'TEST_TABLE_1'
|
|
, table_exists_action => 'REPLACE'
|
|
);
|
|
END;
|
|
/
|
|
|
|
select count(1) from DBAVGUPTA.TEST_TABLE;
|
|
select count(1) from DBAVGUPTA.TEST_TABLE_1;
|
|
select count(1) from VGUPTA.TEST_TABLE_1;
|
|
|
|
|
|
set lines 1000
|
|
set serveroutput on
|
|
BEGIN
|
|
dba_own.datapump.export_import(operation => 'EXPORT'
|
|
, job_mode => 'TABLE'
|
|
, directory => 'DATA_DUMP_DIR2'
|
|
, dumpfile => 'intex_summary_full_vg.%U.dmp'
|
|
, logfile => 'intex_summary_full_vg.exp.log'
|
|
, parallelism => 12
|
|
, schema_name => 'INTEX_OWN'
|
|
, table_name => 'INTEX_SUMMARY_FULL'
|
|
, filesize => '10G'
|
|
, reusefile => 1
|
|
, compression => 'NONE'
|
|
, include_metadata => 1
|
|
, service_name => 'dv03pimi_adv_con'
|
|
);
|
|
END;
|
|
/
|
|
|
|
set lines 1000
|
|
set serveroutput on
|
|
BEGIN
|
|
dba_own.datapump.export_import(operation => 'IMPORT'
|
|
, job_mode => 'TABLE'
|
|
, directory => 'DATA_DUMP_DIR2'
|
|
, dumpfile => 'intex_summary_full.%U.dmp'
|
|
, logfile => 'intex_summary_full_vg.imp2.log'
|
|
, parallelism => 12
|
|
, schema_name => 'INTEX_OWN'
|
|
, table_name => 'INTEX_SUMMARY_FULL'
|
|
, remap_table => 'INTEX_SUMMARY_FULL_VG'
|
|
, table_exists_action => 'TRUNCATE'
|
|
, service_name => 'dv03pimi_adv_con'
|
|
);
|
|
END;
|
|
/
|
|
|
|
|
|
impdp dbavgupta tables=INTEX_OWN.INTEX_SUMMARY_FULL \
|
|
table_exists_action=TRUNCATE \
|
|
remap_table=INTEX_SUMMARY_FULL:INTEX_SUMMARY_FULL_VG \
|
|
parallel=12 \
|
|
directory=data_dump_dir2 \
|
|
dumpfile=intex_summary_full.%U.dmp \
|
|
logfile=intex_summary_full_vg.imp2.log
|
|
|
|
|
|
|
|
set lines 1000
|
|
set serveroutput on
|
|
BEGIN
|
|
dba_own.datapump.export_import(operation => 'EXPORT'
|
|
, job_mode => 'TABLE'
|
|
, directory => 'DATA_DUMP_DIR2'
|
|
, dumpfile => 'DBAVGUPTA.intex_summary_full.%U.dmp'
|
|
, logfile => 'DBAVGUPTA.intex_summary_full.exp.log'
|
|
, parallelism => 12
|
|
, schema_name => 'DBAVGUPTA'
|
|
, table_name => 'INTEX_SUMMARY_FULL'
|
|
, filesize => '10G'
|
|
, reusefile => 1
|
|
, compression => 'NONE'
|
|
, include_metadata => 1
|
|
, service_name => 'dv03pimi_adv_con'
|
|
);
|
|
END;
|
|
/
|
|
|
|
set lines 1000
|
|
set serveroutput on
|
|
BEGIN
|
|
dba_own.datapump.export_import(operation => 'IMPORT'
|
|
, job_mode => 'TABLE'
|
|
, directory => 'DATA_DUMP_DIR2'
|
|
, dumpfile => 'DBAVGUPTA.intex_summary_full.%U.dmp'
|
|
, logfile => 'DBAVGUPTA.intex_summary_full_truncate.imp.log'
|
|
, parallelism => 12
|
|
, schema_name => 'DBAVGUPTA'
|
|
, table_name => 'INTEX_SUMMARY_FULL'
|
|
, table_exists_action => 'TRUNCATE'
|
|
, service_name => 'dv03pimi_adv_con'
|
|
);
|
|
END;
|
|
/
|
|
|
|
|
|
set lines 1000
|
|
set serveroutput on
|
|
BEGIN
|
|
dba_own.datapump.export_import(operation => 'IMPORT'
|
|
, job_mode => 'TABLE'
|
|
, directory => 'DATA_DUMP_DIR2'
|
|
, dumpfile => 'DBAVGUPTA.intex_summary_full.%U.dmp'
|
|
, logfile => 'DBAVGUPTA.intex_summary_full_append.imp.log'
|
|
, parallelism => 12
|
|
, schema_name => 'DBAVGUPTA'
|
|
, table_name => 'INTEX_SUMMARY_FULL'
|
|
, table_exists_action => 'APPEND'
|
|
, service_name => 'dv03pimi_adv_con'
|
|
);
|
|
END;
|
|
/
|
|
|
|
set lines 1000
|
|
set serveroutput on
|
|
BEGIN
|
|
dba_own.datapump.export_import(operation => 'IMPORT'
|
|
, job_mode => 'TABLE'
|
|
, directory => 'DATA_DUMP_DIR2'
|
|
, dumpfile => 'DBAVGUPTA.intex_summary_full.%U.dmp'
|
|
, logfile => 'DBAVGUPTA.intex_summary_full_vg2_remap_replace.imp.log'
|
|
, parallelism => 12
|
|
, schema_name => 'DBAVGUPTA'
|
|
, table_name => 'INTEX_SUMMARY_FULL_VG'
|
|
, remap_table => 'INTEX_SUMMARY_FULL_VG2'
|
|
, table_exists_action => 'REPLACE'
|
|
, service_name => 'dv03pimi_adv_con'
|
|
);
|
|
END;
|
|
/
|
|
|
|
|
|
set lines 1000
|
|
set serveroutput on
|
|
BEGIN
|
|
dba_own.datapump.export_import(operation => 'IMPORT'
|
|
, job_mode => 'TABLE'
|
|
, directory => 'DATA_DUMP_DIR2'
|
|
, dumpfile => 'DBAVGUPTA.intex_summary_full.%U.dmp'
|
|
, logfile => 'DBAVGUPTA.intex_summary_full_vg2_remap_truncate.imp.log'
|
|
, parallelism => 12
|
|
, schema_name => 'DBAVGUPTA'
|
|
, table_name => 'INTEX_SUMMARY_FULL_VG'
|
|
, remap_table => 'INTEX_SUMMARY_FULL_VG2'
|
|
, table_exists_action => 'TRUNCATE'
|
|
, service_name => 'dv03pimi_adv_con'
|
|
);
|
|
END;
|
|
/
|