AWSのRDS利用している場合、OSにログインできないため、テーブルデータのexp,impのディレクトリを変更できない。
今回はRDSのOracle環境でテーブルデータのエクスポート、インポートおよびdmpファイルの削除の方法を話します。
事前に環境確認
テスト用テーブルのデータを確認する
SELECT COUNT(*) FROM EMP;
SELECT * FROM EMP;
SELECT * FROM EMP;
DATA_PUMP_DIRのディレクトリパスの確認
SELECT directory_name, directory_path FROM dba_directories
WHERE directory_name=’DATA_PUMP_DIR’;
WHERE directory_name=’DATA_PUMP_DIR’;
DATA_PUMP_DIR内のファイル一覧取得
SELECT * FROM TABLE(RDSADMIN.RDS_FILE_UTIL.LISTDIR(‘DATA_PUMP_DIR’)) ORDER BY MTIME;
スキーマ「DBMYST」の全テーブルのエクスポート
DECLARE
hndl NUMBER;
BEGIN
hndl := DBMS_DATAPUMP.open( operation => ‘EXPORT’, job_mode => ‘SCHEMA’,
job_name=>null);
DBMS_DATAPUMP.ADD_FILE( handle => hndl, filename => ‘tab1.dmp’,
directory => ‘DATA_PUMP_DIR’, filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.add_file( handle => hndl, filename => ‘exp.log’,
directory => ‘DATA_PUMP_DIR’, filetype => dbms_datapump.ku$_file_type_log_file);
DBMS_DATAPUMP.METADATA_FILTER(hndl,’SCHEMA_EXPR’,’IN (”DBMYST”)’);
DBMS_DATAPUMP.start_job(hndl);
END;
/
hndl NUMBER;
BEGIN
hndl := DBMS_DATAPUMP.open( operation => ‘EXPORT’, job_mode => ‘SCHEMA’,
job_name=>null);
DBMS_DATAPUMP.ADD_FILE( handle => hndl, filename => ‘tab1.dmp’,
directory => ‘DATA_PUMP_DIR’, filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.add_file( handle => hndl, filename => ‘exp.log’,
directory => ‘DATA_PUMP_DIR’, filetype => dbms_datapump.ku$_file_type_log_file);
DBMS_DATAPUMP.METADATA_FILTER(hndl,’SCHEMA_EXPR’,’IN (”DBMYST”)’);
DBMS_DATAPUMP.start_job(hndl);
END;
/
スキーマ全体ではなく、特定のテーブルのみを対象として、エクスポートをする場合
下記のようなEMPを対象とする場合、以下のような設定をします。
また、”NOT IN”を利用することで特定のテーブルを除外することもできるようです。
DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.open( operation => ‘EXPORT’, job_mode => ‘SCHEMA’, job_name=>null);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => ‘expdb_tables.dmp’,
directory => ‘DATA_PUMP_DIR’, filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.add_file( handle => hdnl, filename => ‘expdb_tables.log’,
directory => ‘DATA_PUMP_DIR’, filetype => dbms_datapump.ku$_file_type_log_file);
DBMS_DATAPUMP.METADATA_FILTER( handle => hdnl, name => ‘SCHEMA_EXPR’, value => ‘IN (DBMYST)’);
DBMS_DATAPUMP.METADATA_FILTER( handle => hdnl, name => ‘NAME_EXPR’, value => ‘IN (EMP)’, object_path=>’TABLE’);
DBMS_DATAPUMP.start_job(hdnl);
END;
/
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.open( operation => ‘EXPORT’, job_mode => ‘SCHEMA’, job_name=>null);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => ‘expdb_tables.dmp’,
directory => ‘DATA_PUMP_DIR’, filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.add_file( handle => hdnl, filename => ‘expdb_tables.log’,
directory => ‘DATA_PUMP_DIR’, filetype => dbms_datapump.ku$_file_type_log_file);
DBMS_DATAPUMP.METADATA_FILTER( handle => hdnl, name => ‘SCHEMA_EXPR’, value => ‘IN (DBMYST)’);
DBMS_DATAPUMP.METADATA_FILTER( handle => hdnl, name => ‘NAME_EXPR’, value => ‘IN (EMP)’, object_path=>’TABLE’);
DBMS_DATAPUMP.start_job(hdnl);
END;
/
DATA_PUMP_DIR内のファイル一覧取得
SELECT * FROM TABLE(RDSADMIN.RDS_FILE_UTIL.LISTDIR(‘DATA_PUMP_DIR’)) ORDER BY MTIME;
テーブルのデータを削除する
truncate table emp;
DBMS_DATAPUMPでエクスポートしたデータのインポート
DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.open( operation => ‘IMPORT’, job_mode => ‘SCHEMA’, job_name=>null);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => ‘tab1_copied.dmp’,
directory => ‘DATA_PUMP_DIR’, filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.add_file( handle => hdnl, filename => ‘imp.log’,
directory => ‘DATA_PUMP_DIR’, filetype => dbms_datapump.ku$_file_type_log_file);
DBMS_DATAPUMP.METADATA_FILTER(hdnl,’SCHEMA_EXPR’,’IN (”DBMYST”)’);
DBMS_DATAPUMP.start_job(hdnl);
END;
/
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.open( operation => ‘IMPORT’, job_mode => ‘SCHEMA’, job_name=>null);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => ‘tab1_copied.dmp’,
directory => ‘DATA_PUMP_DIR’, filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.add_file( handle => hdnl, filename => ‘imp.log’,
directory => ‘DATA_PUMP_DIR’, filetype => dbms_datapump.ku$_file_type_log_file);
DBMS_DATAPUMP.METADATA_FILTER(hdnl,’SCHEMA_EXPR’,’IN (”DBMYST”)’);
DBMS_DATAPUMP.start_job(hdnl);
END;
/
テーブルのデータの確認
select * from emp;
ログファイルの確認方法
SELECT TEXT FROM TABLE(RDSADMIN.RDS_FILE_UTIL.READ_TEXT_FILE(‘DATA_PUMP_DIR’,’exp.log’));
dmp , logファイルの削除
一つファイルの削除
EXEC UTL_FILE.FREMOVE(‘DATA_PUMP_DIR’,’exp.log’);
ファイル一括削除
DEClARE
directory_object VARCHAR2(100);
BEGIN
directory_object:=’DATA_PUMP_DIR’;
FOR vRec IN (
SELECT FILENAME
FROM table(RDSADMIN.RDS_FILE_UTIL.LISTDIR(directory_object))
WHERE TYPE=’file’
ORDER BY mtime
) LOOP
utl_file.fremove(directory_object,vRec.FILENAME);
END LOOP;
END;
directory_object VARCHAR2(100);
BEGIN
directory_object:=’DATA_PUMP_DIR’;
FOR vRec IN (
SELECT FILENAME
FROM table(RDSADMIN.RDS_FILE_UTIL.LISTDIR(directory_object))
WHERE TYPE=’file’
ORDER BY mtime
) LOOP
utl_file.fremove(directory_object,vRec.FILENAME);
END LOOP;
END;