Shulou(Shulou.com)06/01 Report--

Real question 1. How to stop the expdp process completely?

Answer: when using the expdp command, many colleagues accidentally press the CTRL+C key combination, and then enter the exit command (or network outage and other anomalies), resulting in the non-existence of the expdp process, but the session of the Oracle database still exists, so the dmp file has been growing. The treatment in this case is as follows:

1. Check whether the expdp process is still there.

Ps-ef | grep expdp

If it exists, you can use the "kill-9 process" command to kill the expdp process.

2. Check whether the session still exists, and if so, kill the relevant session (note: first use the command "ALTER SYSTEM KILL SESSION SID,SERIAL# IMMEDIATE;" to kill the session at the database level, and then use kill-9 to kill the process at the OS level). If you do not have the permission to kill the session, you can DROP the related table. The table name can be queried using the following SQL:

SELECT * FROM DBA_DATAPUMP_SESSIONS

SELECT * FROM DBA_DATAPUMP_JOBS

For example:

SYS@orclasm > SELECT * FROM DBA_DATAPUMP_SESSIONS

OWNER_NAME JOB_NAME INST_ID SADDR SESSION_TYPE

--

LHR SYS_EXPORT_SCHEMA_04 1 00000000A8B71D98 MASTER

LHR SYS_EXPORT_SCHEMA_04 1 00000000AB98AFC8 WORKER

SYS@orclasm > DROP TABLE LHR.SYS_EXPORT_SCHEMA_04 PURGE

Table dropped.

SYS@orclasm > SELECT * FROM DBA_DATAPUMP_SESSIONS

No rows selected

SYS@orclasm > SELECT * FROM DBA_DATAPUMP_JOBS

No rows selected

Using the same method, the tables queried from the view DBA_DATAPUMP_JOBS are also deleted until there are no records for both views.

3. Delete the exported dmp file. If it is not deleted, an error that already exists in the dmp file will be reported when the expdp command is reexecuted.

Here, the author gives a commonly used SQL statement to query the details of the session of expdp, as shown below:

-- how to stop the expdp process completely?

SET LINE 9999

COL OWNER_NAME FOR A10

COL JOB_NAME FOR A25

COL OPERATION FOR A10

COL JOB_MODE FOR A10

COL STATE FOR A15

COL OSUSER FOR A10

COL "DEGREE | ATTACHED | DATAPUMP" FOR A25

COL SESSION_INFO FOR A20

SELECT DS.INST_ID

DJ.OWNER_NAME

DJ.JOB_NAME

TRIM (DJ.OPERATION) OPERATION

TRIM (DJ.JOB_MODE) JOB_MODE

DJ.STATE

DJ.DEGREE | |','| | DJ.ATTACHED_SESSIONS | |','| | DJ.DATAPUMP_SESSIONS "DEGREE | ATTACHED | DATAPUMP"

DS.SESSION_TYPE

S.OSUSER

(SELECT S.SID | |','| | S.SERIAL# | |','| | P.SPID

FROM GV$PROCESS P

WHERE S.PADDR = P.ADDR

AND S.INST_ID = P.INST_ID) SESSION_INFO

FROM DBA_DATAPUMP_JOBS DJ-GV$DATAPUMP_JOB

FULL OUTER JOIN DBA_DATAPUMP_SESSIONS DS-GV$DATAPUMP_SESSION

ON (DJ.JOB_NAME = DS.JOB_NAME AND DJ.OWNER_NAME = DS.OWNER_NAME)

LEFT OUTER JOIN GV$SESSION S

ON (S.SADDR = DS.SADDR AND DS.INST_ID = S.INST_ID)

ORDER BY DJ.OWNER_NAME, DJ.JOB_NAME

Select * from GV$DATAPUMP_SESSION

Select * from GV$datapump_jobs

Select * From dba_datapump_jobs

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.