ANBOB™

statement suspended, wait error to be cleared(一)

Resumable space allocation, introduced in Oracle 9i, is for all tablespaces at the session level. Database operations are suspended when an out-of-space condition is encountered. These suspended operations automatically resume when the error condition disappears. In Oracle Database 10g, this can be enabled at the instance level. Besides this improvement, automatic alert notification is sent when an operation is suspended.

[oracle@dev-db ~]$ impdp system/oracle schemas=icme remap_schema=icme:icme6 directory=DATAPUMP dumpfile=icme_sch.dump remap_tablespace=user:icmetbs parallel=7

……
. . imported “ICME6″.”ICME_NOPROJECT_SCORE_OPLOG” 513.9 MB 6206122 rows
. . imported “ICME6″.”TMP_DATA” 0 KB 0 rows
. . imported “ICME6″.”TMP_XMSB_TJ_G” 0 KB 0 rows
. . imported “ICME6″.”ICME_STUDENT_RANK_TRAN_LOG” 619.1 MB 9324227 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

IN HANG.

SQL> select username,event from v$session where username=’ICME6′;
USERNAME EVENT
—————————— ————————————————————
ICME6 PX Deq: Execution Msg
ICME6 statement suspended, wait error to be cleared
ICME6 PX Deq: Execution Msg
ICME6 PX Deq: Execution Msg
ICME6 PX Deq: Execution Msg
ICME6 PX Deq: Execution Msg
ICME6 PX Deq: Execution Msg
ICME6 PX Deq: Execution Msg
ICME6 PX Deq: Execution Msg
ICME6 PX Deq: Execute Reply
ICME6 PX Deq: Execution Msg

[oracle@dev-db bdump]$ vi alert_devdb.log

Thread 1 advanced to log sequence 60395
Current log# 7 seq# 60395 mem# 0: /oradata/devdb/redo07.log
Sat Jan 5 10:22:26 2013
statement in resumable session ‘SYSTEM.SYS_IMPORT_SCHEMA_06.1’ was suspended due to
ORA-01652: unable to extend temp segment by 128 in tablespace ICME

There is a view that contains information on sessions that are and were suspended, the below query shows the STATUS is SUSPENDED and time when it occurred .

sql>select user_id, instance_id, status, name, error_msg, suspend_time, resume_time from dba_resumable;

USER_ID INSTANCE_ID STATUS NAME ERROR_MSG SUSPEND_TIME RESUME_TIME
———- ———– ——— —————————— —————————— ——————– ——————–
5 1 NORMAL SYSTEM.SYS_IMPORT_SCHEMA_06.6
5 1 NORMAL SYSTEM.SYS_IMPORT_SCHEMA_06.3
5 1 NORMAL SYSTEM.SYS_IMPORT_SCHEMA_06
5 1 NORMAL SYSTEM.SYS_IMPORT_SCHEMA_06.4
5 1 NORMAL SYSTEM.SYS_IMPORT_SCHEMA_06.5
5 1 NORMAL SYSTEM.SYS_IMPORT_SCHEMA_06.7
5 1 NORMAL SYSTEM.SYS_IMPORT_SCHEMA_06.1
5 1 NORMAL SYSTEM.SYS_IMPORT_SCHEMA_06.2

If the tablepsace is not fixed within the timeout, the session will error out and the status is TIMEOUT for the session in DBA_RESUMABLE.

If the tablepsace is fixed within the timeout, the session will continue to run the status is NORMAL for the session in DBA_RESUMABLE with resume_time set to indicate when the session start running again

to solve:
SQL> alter tablespace icme add datafile ‘/backup/oradata/icme02.dbf’ size 1g autoextend on;

SQL> select username,event from v$session where username=’ICME6′;

USERNAME EVENT
—————————— —————————————————————-
ICME6 PX Deq: Execution Msg
ICME6 PX Deq: Execution Msg
ICME6 PX Deq: Execution Msg
ICME6 Data file init write
ICME6 PX Deq: Execution Msg
ICME6 PX Deq: Execution Msg
ICME6 PX Deq: Execution Msg
ICME6 PX Deq: Execution Msg
ICME6 PX Deq: Execution Msg
ICME6 PX Deq: Execute Reply
ICME6 PX Deq: Execution Msg
ICME6 latch: library cache
ICME6 PX Deq: Execution Msg
ICME6 PX Deq: Execution Msg
ICME6 PX Deq: Execution Msg

tip:
PX Deq: Execution Msg event
Occurs when a parallel slave is waiting to be told what to do. This is normally considered an idle event, but can cause excessive CPU in some cases.
Solution
Reduce the degree of parallelism in the query if excessive CPU usage is detected.

impdp process infomation:

…
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/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

MOS’s note ID 761848.1
”
Oracle Database provides a means for suspending, and later resuming,
the execution of large database operations in the event of space allocation failures.
This enables you to take corrective action instead of the Oracle Database server returning an error to the user.
After the error condition is corrected, the suspended operation automatically resumes.
This feature is called resumable space allocation. The statements that are affected are called resumable statements.
The time between suspending the execution till correction of the error is reported as
“statement suspended, wait error to be cleared” wait event.
”

打赏

微信扫一扫，打赏作者吧～