适用范围
适用于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 2026Version 19.27.0.0.0Copyright (c) 1982, 2024, Oracle. All rights reserved. Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.27.0.0.0Hello------------------------------------------------Welcome! you are connected to CDB19C databaseCONNAME--------------------------------------------------------------------------------CDB$ROOTSYS@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 NOSYS@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 NOSYS@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 2026Version 19.27.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production10-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=all10-JUN-26 15:13:43.158: Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA10-JUN-26 15:13:44.467: Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA10-JUN-26 15:13:45.791: Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA10-JUN-26 15:13:47.312: Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA10-JUN-26 15:13:47.692: Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS10-JUN-26 15:13:47.918: Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS10-JUN-26 15:13:48.043: Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS10-JUN-26 15:13:48.199: Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS10-JUN-26 15:13:54.145: Processing object type DATABASE_EXPORT/STATISTICS/MARKER10-JUN-26 15:13:54.468: Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER10-JUN-26 15:13:54.501: Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER10-JUN-26 15:13:54.648: Processing object type DATABASE_EXPORT/TABLESPACE10-JUN-26 15:13:54.783: Processing object type DATABASE_EXPORT/PROFILE10-JUN-26 15:13:54.877: Processing object type DATABASE_EXPORT/SCHEMA/USER10-JUN-26 15:13:55.029: Processing object type DATABASE_EXPORT/ROLE10-JUN-26 15:13:55.091: Processing object type DATABASE_EXPORT/RADM_FPTM10-JUN-26 15:13:55.480: Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT10-JUN-26 15:13:55.560: Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT10-JUN-26 15:13:55.636: Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT10-JUN-26 15:13:55.708: Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE10-JUN-26 15:13:55.779: Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT10-JUN-26 15:13:55.904: Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA10-JUN-26 15:13:55.967: Processing object type DATABASE_EXPORT/RESOURCE_COST10-JUN-26 15:13:56.163: Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK10-JUN-26 15:13:56.261: Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE10-JUN-26 15:13:58.100: Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY10-JUN-26 15:13:59.362: Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT10-JUN-26 15:14:01.131: Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM10-JUN-26 15:14:01.375: Processing object type DATABASE_EXPORT/SCHEMA/TYPE/INC_TYPE10-JUN-26 15:14:02.298: Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC10-JUN-26 15:14:02.984: Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT10-JUN-26 15:14:03.984: Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM10-JUN-26 15:14:04.388: Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJExport> statusJob: 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,096Worker 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 a15select 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 2026Version 19.27.0.0.0Copyright (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 79ORA-06512: at "SYS.KUPV$FT", line 421ORA-31638: cannot attach to job SYS_EXPORT_FULL_01 for user SYSORA-06512: at "SYS.DBMS_SYS_ERROR", line 95ORA-06512: at "SYS.KUPV$FT", line 414ORA-31632: master table "SYS.SYS_EXPORT_FULL_01" not found, invalid, or inaccessible ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95ORA-06512: at "SYS.KUPV$FT", line 406ORA-00942: table or view does not exist ORA-06512: at "SYS.KUPV$FT_INT", line 2847ORA-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 2026Version 19.27.0.0.0Copyright (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,152Worker 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: EXECUTINGExport>Export> kill_jobAre 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 a15select 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_012026-06-10T16:22:09.701715+08:00HRPDB(3):DM00 started with pid=69, OS id=7217, job DUMPER.SYS_EXPORT_FULL_012026-06-10T16:22:14.281822+08:00HRPDB(3): HRPDB(3):DW00 started with pid=71, OS id=7223, wid=1, job DUMPER.SYS_EXPORT_FULL_012026-06-10T16:23:46.750429+08:00HRPDB(3):DM00 stopped with pid=69, OS id=7217, job DUMPER.SYS_EXPORT_FULL_01 ...
数据库日志中也可也看到DM00进程stopped。
【小结】
用户权限分离是好多客户生产环境中的常见需求和审计重点内容，用户分离后我们要非常清楚每个用户在权限内能够执行的操作，确保生产环境安全稳定。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28373936/viewspace-3174662/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- Oracle数据库性能诊断与优化工具全景指南（11g、12c、19c与26ai）
- Oracle 19c 本地索引分区变为 UNUSABLE 后的空间占用验证
- Oracle中建立到CDB和PDB的连接
- Oracle 19c CDB中如何创建PDB
- 如何获取Data Pump dump file文件
- 19c CDB架构下使用具有SYSBACKUP权限用户执行DataPump-expdp报ORA-01045和ORA-39406
- IMPDP时有2张表报ORA-31693 ORA-38500
- Oracle中建立到CDB和PDB的连接
- 19c 多租户架构下数据库备份
- Oracle 19c 因环境变量导致通过opatchauto执行失败
