經常碰到這樣的問題，有許可權使用expdp匯出檔案（owner為oracle，other無讀許可權），但沒許可權讀取匯出檔案。

使用DBMS_FILE_TRANSFER包可以繞過該限制，測試如下：

1，匯出檔案無許可權讀取

[bnet@SHPAM013:/bnet/exp_data]$ uname -a
AIX SHPAM013 3 5 00F710654C00

[bnet@SHPAM013:/bnet/exp_data]$ ls -lrt *.dmp
-rw-r----- 1 oracle dba 21667840 Dec 31 10:19 tmp_accounts_ef0109.dmp

2，使用COPY_FILE複製檔案，許可權不變，不能讀取：
BEGIN
DBMS_FILE_TRANSFER.COPY_FILE('DATA_PLATFORM_OVERSEA','tmp_accounts_ef0109.dmp', 'DATA_PLATFORM_OVERSEA',
'a.dmp');
END;
/

PL/SQL procedure successfully completed.

SQL> host ls -lrt *.dmp
-rw-r----- 1 oracle dba 21667840 Dec 31 10:19 tmp_accounts_ef0109.dmp
-rw-r----- 1 oracle dba 21667840 Jan 20 15:11 a.dmp

3，使用GET_FILE在本機複製檔案，許可權不變，不能讀取：
create database link aaa
connect to bnet303 IDENTIFIED BY bnet303
using '(DESCRIPTION=
(LOAD_BALANCE=ON)
(ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))
    )
    (CONNECT_DATA=
      (SERVICE_NAME=orau10g)
    )
)';

BEGIN
DBMS_FILE_TRANSFER.GET_FILE('DATA_PLATFORM_OVERSEA','a.dmp', 'AAA','DATA_PLATFORM_OVERSEA',
'b.dmp');
END;
/

SQL> host ls -lrt *.dmp
-rw-r-----    1 oracle   dba        21667840 Dec 31 10:19 tmp_accounts_ef0109.dmp
-rw-r-----    1 oracle   dba        21667840 Jan 20 15:11 a.dmp
-rw-r-----    1 oracle   dba        21667840 Jan 20 16:24 b.dmp

4，使用GET_FILE把檔案複製到有Oracle許可權的資料庫伺服器上：

[oracle@bnet95 ~]$ uname -a
Linux bnet95 2.6.18-164.el5xen #1 SMP Thu Sep 3 04:47:32 EDT 2009 i686 i686 i386 GNU/Linux

4.1 複製
create database link aaa
connect to bnet303 IDENTIFIED BY bnet303
using '(DESCRIPTION=
(LOAD_BALANCE=ON)
(ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=TCP)(HOST=22.188.20.138)(PORT=1521))
    )
    (CONNECT_DATA=
      (SERVICE_NAME=orau10g)
    )
)';

BEGIN
DBMS_FILE_TRANSFER.GET_FILE('DATA_PLATFORM_OVERSEA','a.dmp', 'AAA','MYDIR',
'b.dmp');
END;
/

[oracle@bnet95 ~]$ ls -lrt b.dmp
-rw-r----- 1 oracle oinstall 21667840 Oct 9 16:33 b.dmp

4.2 匯入
[oracle@bnet95 ~]$ impdp mh/mh directory=mydir dumpfile=b.dmp sqlfile=b.sql full=y

Import: Release 10.2.0.1.0 - Production on Monday, 09 October, 2028 16:35:50

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "MH"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "MH"."SYS_SQL_FILE_FULL_01": mh/******** directory=mydir dumpfile=b.dmp sqlfile=b.sql full=y
Processing object type TABLE_EXPORT/TABLE/TABLE
Job "MH"."SYS_SQL_FILE_FULL_01" successfully completed at 16:36:01

[oracle@bnet95 ~]$ more b.sql
-- CONNECT MH
-- new object type path is: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "bnet303"."TMP_ACCOUNTS_EF0109"
( "ACTNUM" VARCHAR2(24)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "bnetOVS_TBS" ;

[oracle@bnet95 ~]$ impdp mh/mh full=y directory=mydir dumpfile=b.dmp remap_schema=bnet303:MH remap_tablespace=bnetOVS_TBS:USERS

Import: Release 10.2.0.1.0 - Production on Monday, 09 October, 2028 16:39:59

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "MH"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "MH"."SYS_IMPORT_FULL_01": mh/******** full=y directory=mydir dumpfile=b.dmp remap_schema=bnet303:MH remap_tablespace=bnetOVS_TBS:USERS
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "MH"."TMP_ACCOUNTS_EF0109" 20.60 MB 1269546 rows
Job "MH"."SYS_IMPORT_FULL_01" successfully completed at 16:40:08

4.3 匯入資料驗證

SQL> select count(*) from TMP_ACCOUNTS_EF0109;

COUNT(*)
----------
1269546

使用DBMS_FILE_TRANSFER解決無許可權讀取dump檔案問題

相關文章