Oracle的特权管理用户终止其他用户发起的数据泵作业

适用范围
适用于Oracle 11g及以上版本
方案概述
在生产运维过程中，按照用户管理要求，进行了权限分离，执行数据泵的expdp/impdp使用dumper发起数据泵作业，需要终止数据泵作业时使用数据库管理用户来执行终止操作。
实施步骤
1、创建执行数据泵expdp/impdp作业的用户并授权

登录cdb然后切换到hrpdb创建用户
[oracle@19cdb01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 10 18:05:32 2026
Version 19.27.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0


Hello
------------------------------------------------
Welcome! you are connected to CDB19C database


CONNAME
--------------------------------------------------------------------------------
CDB$ROOT

SYS@cdb19c(CDB$ROOT)> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 HRPDB                          READ WRITE NO
         6 RCATPDB                        READ WRITE NO
SYS@cdb19c(CDB$ROOT)> alter session set container=hrpdb;

Session altered.

SYS@cdb19c(CDB$ROOT)> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 HRPDB                          READ WRITE NO
SYS@cdb19c(CDB$ROOT)>

SYS@cdb19c(CDB$ROOT)> create user dumper identified by password;

User created.

SYS@cdb19c(CDB$ROOT)> grant DATAPUMP_EXP_FULL_DATABASE,DATAPUMP_IMP_FULL_DATABASE to dumper;

Grant succeeded.

SYS@cdb19c(CDB$ROOT)> alter user dumper quota unlimited on users;

User altered.

SYS@cdb19c(CDB$ROOT)>

创建执行数据泵expdp/impdp作业的dumper用户并授权。
2、使用dumper用户执行expdp数据泵作业

[oracle@19cdb01 ~]$ expdp dumper/password@hrpdb directory=IMP_DIR dumpfile=full_cdb19c.dumpfilemp full=y logfile=expfull_cdb19c_20260610.log logtime=all

Export: Release 19.0.0.0.0 - Production on Wed Jun 10 15:13:29 2026
Version 19.27.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
10-JUN-26 15:13:36.639: Starting "DUMPER"."SYS_EXPORT_FULL_01":  dumper/password@hrpdb directory=IMP_DIR dumpfile=full_cdb19c.dumpfilemp full=y logfile=expfull_cdb19c_20260610.log logtime=all
10-JUN-26 15:13:43.158: Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
10-JUN-26 15:13:44.467: Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
10-JUN-26 15:13:45.791: Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
10-JUN-26 15:13:47.312: Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
10-JUN-26 15:13:47.692: Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
10-JUN-26 15:13:47.918: Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
10-JUN-26 15:13:48.043: Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
10-JUN-26 15:13:48.199: Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
10-JUN-26 15:13:54.145: Processing object type DATABASE_EXPORT/STATISTICS/MARKER
10-JUN-26 15:13:54.468: Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
10-JUN-26 15:13:54.501: Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
10-JUN-26 15:13:54.648: Processing object type DATABASE_EXPORT/TABLESPACE
10-JUN-26 15:13:54.783: Processing object type DATABASE_EXPORT/PROFILE
10-JUN-26 15:13:54.877: Processing object type DATABASE_EXPORT/SCHEMA/USER
10-JUN-26 15:13:55.029: Processing object type DATABASE_EXPORT/ROLE
10-JUN-26 15:13:55.091: Processing object type DATABASE_EXPORT/RADM_FPTM
10-JUN-26 15:13:55.480: Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
10-JUN-26 15:13:55.560: Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
10-JUN-26 15:13:55.636: Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
10-JUN-26 15:13:55.708: Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
10-JUN-26 15:13:55.779: Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
10-JUN-26 15:13:55.904: Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
10-JUN-26 15:13:55.967: Processing object type DATABASE_EXPORT/RESOURCE_COST
10-JUN-26 15:13:56.163: Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
10-JUN-26 15:13:56.261: Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
10-JUN-26 15:13:58.100: Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
10-JUN-26 15:13:59.362: Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
10-JUN-26 15:14:01.131: Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
10-JUN-26 15:14:01.375: Processing object type DATABASE_EXPORT/SCHEMA/TYPE/INC_TYPE
10-JUN-26 15:14:02.298: Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
10-JUN-26 15:14:02.984: Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
10-JUN-26 15:14:03.984: Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
10-JUN-26 15:14:04.388: Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Export> status

Job: SYS_EXPORT_FULL_01
  Operation: DUMPER
  Mode: FULL
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Job heartbeat: 1
  Dump File: /home/oracle/full_cdb19c.dump
    bytes written: 4,096

Worker 1 Status:
  Instance ID: 1
  Instance name: cdb19c
  Host name: 19cdb01
  Object start time: Wednesday, 10 June, 2026 16:22:20
  Object status at: Wednesday, 10 June, 2026 16:22:21
  Process Name: DW00
  State: EXECUTING

3、检查数据泵作业

在hrpdb中执行
col OWNER_NAME for a20
col job_name for a20
col OPERATION for a15
col state for a15
select OWNER_NAME,JOB_NAME,OPERATION,state,CON_ID from cdb_datapump_jobs;
OWNER_NAME           JOB_NAME             OPERATION       STATE               CON_ID
-------------------- -------------------- --------------- ------------
DUMPER       SYS_EXPORT_FULL_01   EXPORT   EXECUTING              3

expdp作业名称为SYS_EXPORT_FULL_01，作业正在执行中，con_id是3，即hrdpb。
4、检查数据泵后台进程

[oracle@19cdb01 ~]$  ps -ef | grep -v grep | grep ora_dm
oracle   12414     1 45 17:47 ?        00:00:03 ora_dm00_cdb19c
[oracle@19cdb01 ~]

5、使用数据库管理用户sys来终止数据泵作业

[oracle@19cdb01 ~]$ expdp '"sys/password@hrpdb as sysdba"' attach=SYS_EXPORT_FULL_01

Export: Release 19.0.0.0.0 - Production on Wed Jun 10 15:38:20 2026
Version 19.27.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.KUPV$FT", line 421
ORA-31638: cannot attach to job SYS_EXPORT_FULL_01 for user SYS
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 414
ORA-31632: master table "SYS.SYS_EXPORT_FULL_01" not found, invalid, or inaccessible
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 406
ORA-00942: table or view does not exist
ORA-06512: at "SYS.KUPV$FT_INT", line 2847
ORA-06512: at "SYS.KUPV$FT", line 210

使用sys用户attach方式进行expdp命令行交互模式，ORA-31632 master table “SYS.SYS_EXPORT_FULL_01” not found，sys用户下没有找到SYS_EXPORT_FULL_01状态表。

[oracle@19cdb01 ~]$  expdp '"sys/password@hrpdb as sysdba"' attach=dumper.SYS_EXPORT_FULL_01

Export: Release 19.0.0.0.0 - Production on Wed Jun 10 15:40:47 2026
Version 19.27.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Job: SYS_EXPORT_FULL_01
  Owner: DUMPER
  Operation: DUMPER
  Creator Privs: TRUE
  GUID: 53E25B880FB21261E065DDCE3A0EF6FC
  Start Time: Wednesday, 10 June, 2026 15:40:29
  Mode: FULL
  Instance: cdb19c
  Max Parallelism: 1
  Timezone: +00:00
  Timezone version: 32
  Endianness: LITTLE
  NLS character set: AL32UTF8
  NLS NCHAR character set: AL16UTF16
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        dumper/password@hrpdb directory=IMP_DIR dumpfile=full_cdb19c.dump full=y logfile=expfull_cdb19c_20260610.log logtime=all
     LOGTIME               ALL
     TRACE                 0
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Job heartbeat: 2
  Dump File: /home/oracle/full_cdb19c.dump
    bytes written: 49,152

Worker 1 Status:
  Instance ID: 1
  Instance name: cdb19c
  Host name: 19cdb01
  Object start time: Wednesday, 10 June, 2026 15:40:45
  Object status at: Wednesday, 10 June, 2026 15:40:45
  Process Name: DW00
  State: EXECUTING

Export>
Export> kill_job
Are you sure you wish to stop this job ([yes]/no): yes

attach=dumper.SYS_EXPORT_FULL_01进入到了expdp命令行交互模式，可以使用sys用户通过kill_job终止expdp作业。
6、检查expdp作业状态

在hrpdb中执行
col OWNER_NAME for a20
col job_name for a20
col OPERATION for a15
col state for a15
select OWNER_NAME,JOB_NAME,OPERATION,state,CON_ID from cdb_datapump_jobs;
OWNER_NAME           JOB_NAME             OPERATION       STATE               CON_ID
-------------------- -------------------- --------------- ------------
DUMPER          SYS_EXPORT_FULL_01   EXPORT    STOPPING              3

此时SYS_EXPORT_FULL_01 作业时STOPPING。
如果状态表长时间没有自动清理，可以使用以下命令

SQL>DROP TABLE dumper.SYS_EXPORT_FULL_01;

–数据库日志

...
2026-06-10T16:21:59.026649+08:00
HRPDB(3):DM00 stopped with pid=61, OS id=7187, job DUMPER.SYS_EXPORT_FULL_01
2026-06-10T16:22:09.701715+08:00
HRPDB(3):DM00 started with pid=69, OS id=7217, job DUMPER.SYS_EXPORT_FULL_01
2026-06-10T16:22:14.281822+08:00
HRPDB(3):
HRPDB(3):DW00 started with pid=71, OS id=7223, wid=1, job DUMPER.SYS_EXPORT_FULL_01
2026-06-10T16:23:46.750429+08:00
HRPDB(3):DM00 stopped with pid=69, OS id=7217, job DUMPER.SYS_EXPORT_FULL_01
...

数据库日志中也可也看到DM00进程stopped。

【小结】
用户权限分离是好多客户生产环境中的常见需求和审计重点内容，用户分离后我们要非常清楚每个用户在权限内能够执行的操作，确保生产环境安全稳定。