DBMS_DATAPUMPにて、「ORA-39001 引数値が無効です」が発生

Question

お世話になります。

スキーマ内の全テーブルをバックアップする処理を作成しているのですが、
題意の通り、ORA-39001 引数値が無効です のエラーを解決することができません。

①どこでエラーが発生しているのか。
②エラー解決のために、どの部分を変えればよいのか。

上記2点について、解答をよろしくお願いします。

なお、バッチからSQLファイルを呼び出して実行しております。
以下、ソース(SQLファイル)です。

CREATE OR REPLACE DIRECTORY BK_DIR AS '&1';

    DECLARE
        HANDLE NUMBER;
        RET_STR VARCHAR2(20);
        INFO CHAR;
        T_NAME VARCHAR2(100);

    BEGIN
        FOR INFO IN (select TABLE_NAME from USER_TABLES order by 1 desc) LOOP
        DBMS_OUTPUT.PUT_LINE('開始');

            HANDLE:=DBMS_DATAPUMP.OPEN(
                                       'EXPORT'
                                      ,'TABLE'
                                      );
            DBMS_DATAPUMP.ADD_FILE(
                                   HANDLE
                                  ,INFO.TABLE_NAME||'.dmp'
                                  ,'BK_DIR'
                                  ,NULL
                                  ,DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE
                                  ,1);
                                  
            T_NAME := ''''||INFO.TABLE_NAME||'''';
            DBMS_DATAPUMP.METADATA_FILTER(
                                          HANDLE
                                         ,'NAME_EXPR'
                                         ,'IN('||T_NAME||')'
                                         );

            DBMS_DATAPUMP.START_JOB(
                                    HANDLE
                                   ,0
                                   );
            
            DBMS_DATAPUMP.WAIT_FOR_JOB(
                                       HANDLE
                                      ,RET_STR
                                      );            
        END LOOP;
    /
 END;

DROP DIRECTORY BK_DIR;
EXIT;

Answer 1

スクリプトが正常に動作するために修正が必要な点がいくつかあります。

(1)DBMS_DATAPUMP.ADD_FILE内
誤) ,'||INFO.TABLE_NAME||'.dmp'
正) ,INFO.TABLE_NAME||'.dmp'

(2)DBMS_DATAPUMP.ADD_FILEの後
誤) TABLE_NAME := ''''||INFO.TABLE_NAME||'''';
正) T_NAME := ''''||INFO.TABLE_NAME||'''';

(3)DBMS_DATAPUMP.METADATA_FILTER内
誤) ,'IN('||TABLE_NAME||')'
正) ,'IN('||T_NAME||')'

(4)END LOOP;の後
誤)
正) END;

本題ですが、

ORA-39001 引数値が無効です

おそらく次の修正で改善されます。
1行目
誤) CREATE OR REPLACE DIRECTORY BACKUP_DIR AS '&1';
正) CREATE OR REPLACE DIRECTORY BK_DIR AS '&1';

Comments

[nabe3]

エラーが出た時の追跡等のためにダンプファイルに合わせて
ログファイルも出力しておいた方が良いかもしれません。

DBMS_DATAPUMP.ADD_FILEの後に次を追加

DBMS_DATAPUMP.ADD_FILE(
HANDLE
,INFO.TABLE_NAME||'.log'
,'BK_DIR'
,NULL
,DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
);

[yamaha]

回答ありがとうございます。
投稿したソースの内容ですが、多々間違いがあり申し訳ありません。
ただ、ソースをそのままコピーしたわけではなく、ところどころ名前を変えて修正しているうちに、他の同名を使うべき部分の整合性が取れておりませんでした。
実際のソースですと、整合性は取れております。
投稿内容については整合性が取れますように修正いたしますが、エラーが発生している部分は別の部分になるかと思われます。
お時間がありましたら、再度回答をよろしくお願いいたします。

[nabe3]

このスクリプトでは開始前にディレクトリオブジェクトを作成しています。
DBMS_DATAPUMPを実行するユーザーがそのディレクトリオブジェクト(BK_DIR)に、
WRITE権限が無いのが原因ではないでしょうか？
--権限付与のスクリプト
GRANT WRITE ON DIRECTORY BK_DIR TO username;

もしくはBK_DIRのディレクトリが存在しない場合にも、
ORA-39001 引数値が無効です が発生します。
'&1'で指定されるディレクトリはエクスプローラ等からアクセスできるでしょうか？

[guestusr]

即興で作成したIDであるため、ログイン情報を忘れてしまいました。新しいユーザーでコメントさせていただきます。(yamaha本人です。)

<WRITE権限について>
SELECT * FROM DBA_TAB_PRIVS WHERE TABLE_NAME = 'BK_DIR'
で調べましたところ、PRIVILEGE列は「EXECUTE,READ,WRITE」で、
GRANTABLE列はすべて’YES’となっていました。
GRANTEE列はusernameのみです。

<ディレクトリの存在有無>
エクスプローラからアクセスできることを確認しております。

権限についての知識がかなり乏しいため、検索した結果がいまいち読み取れません。
WRITE権限は付与されているように思うのですが、何か情報が不十分でしたら、
追記いたしますので、よろしくお願いします。

[nabe3]

> <ディレクトリの存在有無>
> エクスプローラからアクセスできることを確認しております。
ディレクトリのアクセス確認はDBサーバーから実施していますか？
クライアントから実施していませんか？
DBサーバーがディレクトリオブジェクトで指定されたパスに読み書きをするので、
DBサーバーで読み書きできないといけません。

[guestusr]

回答ありがとうございます。
原因が特定できました。nabe3がおっしゃるとおり、DBサーバーからの読み書きが
できないため、出ていたエラーでした。一時的にローカルに環境を作成していたため、
この事象がおきていたのだと思います。

大変お世話になりました。

[nabe3]

無事解決できたようでよかったです。

Answer 2

処理ステップを格納する変数を定義し、各プロシージャを呼び出す前に
その変数に一意の値を設定しておき、EXCEPTIONでその変数を出力するように
すればどこでエラーが発生しているのかが明確になります。

ただ、私の環境（Oracle Linux 5、Oracle Database 11.2.0.2）で試して
みてもエラーにはなりません。

以下は実験の結果

systemユーザで実施
SQL> grant create any directory to scott;
SQL> grant drop any directory to scott;

事前にoracleユーザで実施
$ mkdir /home/oracle/bk_dir

問題のSQLを test.sql として作成
ただし、最後のほうの
END LOOP;
/
END;
は
END LOOP;
END;
/
に修正

$ sqlplus scott/tiger
SQL> @test
1に値を入力してください: /home/oracle/bk_dir
旧 1: CREATE OR REPLACE DIRECTORY BK_DIR AS '&1'
新 1: CREATE OR REPLACE DIRECTORY BK_DIR AS '/home/oracle/bk_dir'

ディレクトリが作成されました。

PL/SQLプロシージャが正常に完了しました。

ディレクトリが削除されました。

※1に値を入力してください: に対して、存在しないディレクトリを
指定するとエラー（ORA-39001）になります。

Comments

[guestusr]

回答ありがとうございます。
ユーザー情報をわすれてしまったため、新ユーザーでコメントさせていただきます。
(yamaha本人です。)

ログの出力について、解答の通りに書き換え調べたところ、ADD_FILEの処理で
エラーが発生しているようです。

デイレクトリの存在確認は、テーブルに存在することと、アクセスが可能であることでよろしいでしょうか？

nabe3へのコメントの通り、確認しています。
何か情報が不足していましたら、追記いたします。よろしく願いします。

[tsutomuyamanaka]

ディレクトリの存在確認は、私の実験の結果においてでは、/home/oracle/bk_dir
が存在していて、oracleユーザで読み込み／書き込みができるようになっているか
どうかを確認して下さい。

Linux環境ならば、以下のように確認できます。

$ ls -l /home/oracle | grep bk_dir
drwxr-xr-x 2 oracle oinstall 4096 2月 21 22:25 bk_dir

あと、スクリプトを実行するユーザ（私の実験の結果においてでは scott）
にcreate any directoryとdrop any directoryシステム権限が付与されて
いるかどうかも確認して下さい。
スクリプトを実行するユーザでSQL*Plusで接続後以下のSQLで確認できます。

$ sqlplus scott/tiger
SQL> select privilege from user_sys_privs;

[tsutomuyamanaka]

以下をためしてもらえませんか。

$ sqlplus scott/tiger
※SQL*Plusにログインするユーザはscottでなくてもかまいません。

SQL> CREATE OR REPLACE DIRECTORY BK_DIR AS '/home/oracle/bk_dir';
※エラーが発生しないか
（そちらはWindows環境のようなので、/home/oracle/bk_dirの部分は
　適当に書き換えて下さい。）

SQL> SELECT GRANTEE,PRIVILEGE FROM USER_TAB_PRIVS WHERE TABLE_NAME='BK_DIR';
※以下のような結果になるか
GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
SCOTT EXECUTE
SCOTT READ
SCOTT WRITE

SET SERVEROUTPUT ON
DECLARE
HANDLE NUMBER;
STEP VARCHAR2(100);
BEGIN
STEP := 'OPEN';
HANDLE:=DBMS_DATAPUMP.OPEN('EXPORT','TABLE');
STEP := 'ADD_FILE';
DBMS_DATAPUMP.ADD_FILE(HANDLE,'DEPT.dmp','BK_DIR',NULL,DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE,1);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(STEP);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
※上記スクリプトの実行でエラーにならないか

SQL> DROP DIRECTORY BK_DIR;

Answer 3

例外処理でSQLCODEとSQLERRMを使用してエラーメッセージを拾ってみてはいかがでしょうか？

DECLARE
    vMsg    VARCHAR2(2048);

BEGIN

EXCEPTION
    WHEN OTHERS THEN
        vMsg := SQLERRM(SQLCODE);
        DBMS_OUTPUT.PUT_LINE(vMsg);

END
/

こんな感じで。

Comments

[yamaha]

回答ありがとうございます。
例外処理を実装し、ログを確認しましたが、同じく、
「ORA-39001 引数値が無効です」
のエラーを確認しました。