使用oracle sqldeveloper工具实现客户端导入expdp的dmp数据

尝试使用普通用户u1登录主机后做导入操作，不可行：There was 1 failed login attempt since the last successful login.[u1@oem ~]$ impdp-bash: impdp: command not found[u1@oem ~]$ /u01/app/oracle/product/19.3.0/db_1/bin/impdp jyc/

好记忆不如烂笔头abc

5867人浏览 · 2021-06-09 15:28:19

好记忆不如烂笔头abc · 2021-06-09 15:28:19 发布

实现目标:

避免给予厂商oracle用户权限，但又保证厂商可以自行dmp导入。

方案：（经验证可行）

1.建个普通用户，建个dmp目录，赋予777权限。允许应用厂商上传dmp文件到此目录（每个厂商对应一个用户一个目录也行，但数量太多会比较麻烦）
2.应用厂商(指定相应pdb库的dba权限用户)只能通过客户端工具oracle sqldeveloper工具做dmp导入操作

工具下载地址：

Oracle SQL Developer Downloads

测试过程：

创建普通用户u1及对应目录/test，将dmp文件放入此目录，给予777读取权限。

[root@oem ~]# mkdir -p /test
[root@oem ~]# useradd u1
[root@oem ~]# passwd u1
Changing password for user u1.
New password:
BAD PASSWORD: The password is shorter than 7 characters
Retype new password:
passwd: all authentication tokens updated successfully.
[root@oem ~]#

[root@oem ~]# chown -R u1:u1 /test
[root@oem ~]# ll /
total 16
lrwxrwxrwx. 1 root root 7 Oct 6 2020 bin -> usr/bin
drwxr-xr-x 2 u1 u1 6 Jun 9 14:38 test
[root@oem ~]# chmod -R 777 /test
[root@oem ~]# ll /test
total 0
[root@oem ~]# ll /
total 16
lrwxrwxrwx. 1 root root 7 Oct 6 2020 bin -> usr/bin
drwxrwxrwx 2 u1 u1 6 Jun 9 14:38 test

SQL> create directory dmp as '/test';

Directory created.

SQL> grant read,write on directory dmp to public;

Grant succeeded.

[oracle@oem ~]$ expdp jyc/jyc@jyc schemas=jyc dumpfile=jyc.dmp logfile=jyc-e.log directory=dmp

Export: Release 19.0.0.0.0 - Production on Wed Jun 9 14:44:14 2021
Version 19.3.0.0.0

UDE-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "JYC"."SYS_EXPORT_SCHEMA_01": jyc/********@jyc schemas=jyc dumpfile=jyc.dmp logfile=jyc-e.log directory=dmp
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "JYC"."FUND_DAILY" 6.565 MB 93021 rows
. . exported "JYC"."FUND_DAILY_INCOME" 3.272 MB 46359 rows
. . exported "JYC"."T1" 5.531 KB 6 rows
. . exported "JYC"."T2" 5.531 KB 6 rows
. . exported "JYC"."T" 5.492 KB 1 rows
. . exported "JYC"."1" 0 KB 0 rows
. . exported "JYC"."1111111111111111119" 0 KB 0 rows
. . exported "JYC"."111111111111111119" 0 KB 0 rows
. . exported "JYC"."11111111111111119" 0 KB 0 rows
. . exported "JYC"."111111111111119" 0 KB 0 rows
. . exported "JYC"."11111111111119" 0 KB 0 rows
. . exported "JYC"."1111111111119" 0 KB 0 rows
. . exported "JYC"."111111111119" 0 KB 0 rows
. . exported "JYC"."1111111119" 0 KB 0 rows
. . exported "JYC"."111111119" 0 KB 0 rows
. . exported "JYC"."11111119" 0 KB 0 rows
. . exported "JYC"."1111119" 0 KB 0 rows
. . exported "JYC"."111119" 0 KB 0 rows
. . exported "JYC"."11119" 0 KB 0 rows
. . exported "JYC"."1119" 0 KB 0 rows
. . exported "JYC"."119" 0 KB 0 rows
. . exported "JYC"."19" 0 KB 0 rows
. . exported "JYC"."2" 0 KB 0 rows
. . exported "JYC"."3" 0 KB 0 rows
. . exported "JYC"."4" 0 KB 0 rows
. . exported "JYC"."7" 0 KB 0 rows
. . exported "JYC"."8" 0 KB 0 rows
. . exported "JYC"."9" 0 KB 0 rows
. . exported "JYC"."B" 0 KB 0 rows
. . exported "JYC"."J" 0 KB 0 rows
. . exported "JYC"."TEST" 0 KB 0 rows
. . exported "JYC"."a1" 0 KB 0 rows
. . exported "JYC"."a12" 0 KB 0 rows
. . exported "JYC"."a122" 0 KB 0 rows
. . exported "JYC"."a1222" 0 KB 0 rows
. . exported "JYC"."a12223" 0 KB 0 rows
. . exported "JYC"."a122233" 0 KB 0 rows
. . exported "JYC"."a1222333" 0 KB 0 rows
. . exported "JYC"."a12223334" 0 KB 0 rows
. . exported "JYC"."a122233344" 0 KB 0 rows
. . exported "JYC"."a1222333444" 0 KB 0 rows
. . exported "JYC"."a12223334441" 0 KB 0 rows
. . exported "JYC"."a1222333444132" 0 KB 0 rows
. . exported "JYC"."a12223334441323" 0 KB 0 rows
Master table "JYC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for JYC.SYS_EXPORT_SCHEMA_01 is:
/test/jyc.dmp
Job "JYC"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Jun 9 14:44:39 2021 elapsed 0 00:00:24

[oracle@oem ~]$
[oracle@oem ~]$ ll /test
total 10808
-rw-r----- 1 oracle oinstall 11059200 Jun 9 14:44 jyc.dmp
-rw-r--r-- 1 oracle oinstall 5024 Jun 9 14:44 jyc-e.log
[oracle@oem ~]$ exit
logout
[root@oem ~]# chmod -R 777 /test
[root@oem ~]# ll /test
total 10808
-rwxrwxrwx 1 oracle oinstall 11059200 Jun 9 14:44 jyc.dmp
-rwxrwxrwx 1 oracle oinstall 5024 Jun 9 14:44 jyc-e.log

[oracle@oem ~]$ which impdp
/u01/app/oracle/product/19.3.0/db_1/bin/impdp
[oracle@oem ~]$ ll /u01/app/oracle/product/19.3.0/db_1/bin/impdp
-rwxr-x--x 1 oracle oinstall 232752 Oct 22 2020 /u01/app/oracle/product/19.3.0/db_1/bin/impdp

登录u1

/u01/app/oracle/product/19.3.0/db_1/bin/impdp jyc/jyc@jyc schemas=jyc dumpfile=jyc.dmp logfile=jyc-i.log directory=dmp

尝试使用普通用户u1登录主机后做导入操作，还是可行：

There was 1 failed login attempt since the last successful login.
[u1@oem ~]$ impdp
-bash: impdp: command not found
[u1@oem ~]$ /u01/app/oracle/product/19.3.0/db_1/bin/impdp jyc/jyc@jyc schemas=jyc dumpfile=jyc.dmp logfile=jyc-i.log directory=dmp
UDI-00013: Message 13 not found; No message file for product=RDBMS, facility=UDI
UDI-00019: You may need to set ORACLE_HOME to your Oracle software directory

[u1@oem ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.3.0/db_1
[u1@oem ~]$ /u01/app/oracle/product/19.3.0/db_1/bin/impdp jyc/jyc@jyc schemas=jyc dumpfile=jyc.dmp logfile=jyc-i.log directory=dmp

Import: Release 19.0.0.0.0 - Production on Wed Jun 9 14:48:25 2021
Version 19.3.0.0.0

UDI-12154: operation generated ORACLE error 12154
ORA-12154: TNS:could not resolve the connect identifier specified

[u1@oem ~]$ ll /u01/app/oracle/product/19.3.0/db_1/bin/lsnrctl
-rwxr-x--x 1 oracle oinstall 178824 Oct 22 2020 /u01/app/oracle/product/19.3.0/db_1/bin/lsnrctl
[u1@oem ~]$ id
uid=1001(u1) gid=54331(u1) groups=54331(u1)
[u1@oem ~]$ export ORACLE_PDB_SID=jyc
[u1@oem ~]$ /u01/app/oracle/product/19.3.0/db_1/bin/impdp jyc/jyc schemas=jyc dumpfile=jyc.dmp logfile=jyc-i.log directory=dmp

Import: Release 19.0.0.0.0 - Production on Wed Jun 9 14:51:17 2021
Version 19.3.0.0.0

UDI-12162: operation generated ORACLE error 12162
ORA-12162: TNS:net service name is incorrectly specified

[u1@oem ~]$ ll /u01/app/oracle/product/19.3.0/db_1/bin/lsnrctl status
ls: cannot access status: No such file or directory
-rwxr-x--x 1 oracle oinstall 178824 Oct 22 2020 /u01/app/oracle/product/19.3.0/db_1/bin/lsnrctl
[u1@oem ~]$ echo $ORACLE_PDB_SID
jyc
[u1@oem ~]$ export TWO_TASK=jyc
[u1@oem ~]$ /u01/app/oracle/product/19.3.0/db_1/bin/impdp jyc/jyc schemas=jyc dumpfile=jyc.dmp logfile=jyc-i.log directory=dmp

Import: Release 19.0.0.0.0 - Production on Wed Jun 9 14:52:18 2021
Version 19.3.0.0.0

UDI-12154: operation generated ORACLE error 12154
ORA-12154: TNS:could not resolve the connect identifier specified

-------------------------------------------------

[u1@oem ~]$ id
uid=1001(u1) gid=54331(u1) groups=54331(u1)
[u1@oem ~]$ ll /test
total 10808
-rwxrwxrwx 1 oracle oinstall 11059200 Jun 9 2021 jyc.dmp
-rwxrwxrwx 1 oracle oinstall 5024 Jun 9 2021 jyc-e.log
[u1@oem ~]$ /u01/app/oracle/product/19.3.0/db_1/bin/impdp jyc/jyc@192.168.52.129:1521/jyc schemas=jyc dumpfile=jyc.dmp logfile=jyc-i.log directory=dmp
UDI-00013: Message 13 not found; No message file for product=RDBMS, facility=UDI
UDI-00019: You may need to set ORACLE_HOME to your Oracle software directory

[u1@oem ~]$ source /home/oracle/.bash_profile
-bash: /home/oracle/.bash_profile: Permission denied
[u1@oem ~]$ cat /home/oracle/.bash_profile
cat: /home/oracle/.bash_profile: Permission denied

[u1@oem ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.3.0/db_1
[u1@oem ~]$ /u01/app/oracle/product/19.3.0/db_1/bin/impdp jyc/jyc@192.168.52.129:1521/jyc schemas=jyc dumpfile=jyc.dmp logfile=jyc-i.log directory=dmp

Import: Release 19.0.0.0.0 - Production on Thu Apr 20 14:24:11 2023
Version 19.3.0.0.0

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "JYC"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "JYC"."SYS_IMPORT_SCHEMA_01": jyc/********@192.168.52.129:1521/jyc schemas=jyc dumpfile=jyc.dmp logfile=jyc-i.log directory=dmp
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"JYC" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39151: Table "JYC"."1" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."11111119" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."FUND_DAILY" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."111111111111111119" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."11119" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."J" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."a1222333" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."1111119" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."T1" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."8" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."a12223" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."111111119" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."a1" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."1119" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."a1222" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."a12" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."STUDENTS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."111111111119" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."a1222333444132" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."1111111119" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."111119" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."11111111111111119" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."a122233344" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."9" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."TEST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."a12223334" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."a122233" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."111111111111119" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."T" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."3" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."19" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."1111111111111111119" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."a12223334441323" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."1111111111119" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."B" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."a12223334441" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."FUND_DAILY_INCOME" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."119" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."a122" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."7" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."2" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."a1222333444" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."T2" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."11111111111119" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."IMP_SD_246-15_01_30" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."4" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "JYC"."IMP_SD_252-15_09_33" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "JYC"."SYS_IMPORT_SCHEMA_01" completed with 48 error(s) at Thu Apr 20 14:24:21 2023 elapsed 0 00:00:08

[u1@oem ~]$ tnsping jyc
-bash: tnsping: command not found
[u1@oem ~]$

如果要现在ssh登录主机，则参考centos7.x添加sftp用户允许上传下载但不允许ssh登录_只允许sftp上传_好记忆不如烂笔头abc的博客-CSDN博客

改用oracle sqldeveloper工具进行导入：当前使用版本20.2

调用的代码如下：

set scan off
set serveroutput on
set escape off
whenever sqlerror exit 
DECLARE
    s varchar2(1000); 
    h1 number;
    errorvarchar varchar2(100):= 'ERROR';
    tryGetStatus number := 0;
begin
    h1 := dbms_datapump.open (operation => 'IMPORT', job_mode => 'FULL', job_name => 'IMP_SD_246-15_01_30', version => 'COMPATIBLE'); 
    tryGetStatus := 1;
    dbms_datapump.set_parallel(handle => h1, degree => 1); 
    dbms_datapump.add_file(handle => h1, filename => 'IMPORT-'||to_char(sysdate,'hh24_mi_ss')||'.LOG', directory => 'DATA_PUMP_DIR', filetype=>DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE); 
    dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 1); 
    dbms_datapump.add_file(handle => h1, filename => 'jyc.dmp', directory => 'DMP', filetype => 1); 
    dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1); 
    dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC'); 
    dbms_datapump.set_parameter(handle => h1, name => 'REUSE_DATAFILES', value => 0); 
    dbms_datapump.set_parameter(handle => h1, name => 'TABLE_EXISTS_ACTION', value => 'REPLACE'); 
    dbms_datapump.set_parameter(handle => h1, name => 'SKIP_UNUSABLE_INDEXES', value => 0);
    dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0); 
    dbms_datapump.detach(handle => h1); 
    errorvarchar := 'NO_ERROR'; 
EXCEPTION
    WHEN OTHERS THEN
    BEGIN 
        IF ((errorvarchar = 'ERROR')AND(tryGetStatus=1)) THEN 
            DBMS_DATAPUMP.DETACH(h1);
        END IF;
    EXCEPTION 
    WHEN OTHERS THEN 
        NULL;
    END;
    RAISE;
END;
/