dba_resumable impdp 超时7200

from dba_resumable;


----------
Purpose

This document explains the cases when the resumable space allocation feature can be useful.
In case a transaction is suspended for space allocation reasons,the resumable space allocation feature allows the transaction to be resumed.
Scope

 These are very common situations when a transaction is interrupted for a space allocation issue:

  * storage settings inappropriate for the table being inserted
  * storage settings inappropriate for the rollback segment being used for the transaction
  * lack of space on temporary tablespace
  * user quotas restricted
  * users privileges missing

The resumable statement feature allows the DBA, once having applied the appropriate solution to the space allocation issue, to resume the suspended
transaction which does not loose all the work done previously.
Details


Enter the Main Content

How to be able to resume a transaction facing a space allocation issue

1) The user running the transaction requires the RESUMABLE system privilege :

   SQL>  connect system/password
   Connected.

   SQL> grant resumable to <username>;
   Grant succeeded.

2) Set the session so that the following transactions might be resumed in case of interruption due to space allocation:

   SQL> alter session enable resumable;
   Session altered.

   This can be set automatically through an AFTER LOGON trigger.

   SQL> create or replace trigger logon_set_resumable
     2  after logon
     3  on <owner>.schema
     4  begin
     5  execute immediate 'alter session enable resumable timeout 1200';
     6  end;
     7  /

   Trigger created.

3) While inserting new rows into TEST_RESUMABLE table, the user session hangs,  but the transaction does not roll back:

   The DBA can retrieve the reason why the session of user <username> hangs in DBA_RESUMABLE view:

   a. Displaying the DBA_RESUMABLE view:

   SQL> select user_id,SESSION_ID, STATUS, START_TIME, SUSPEND_TIME,
     2         SQL_TEXT, ERROR_NUMBER, ERROR_MSG
     3  from dba_resumable;

      USER_ID SESSION_ID STATUS    START_TIME           SUSPEND_TIME
   ---------- ---------- --------- -------------------- --------------------
   SQL_TEXT
   -------------------------------------------------------------------------
   ERROR_NUMBER
   ------------
   ERROR_MSG
   -------------------------------------------------------------------------
           54          9 SUSPENDED 03/14/01 10:49:25    03/14/01 11:14:17
   insert into test_resumable select * from test_resumable
           1631
   ORA-01631: max # extents (5) reached in table <owner>.TEST_RESUMABLE


   b. In alert.log file:

      Wed Mar 14 11:14:17 2001
      statement in resumable session 'User <username>(54), Session 9, Instance 1' was
      suspended due to
       ORA-01631: max # extents (5) reached in table <owner>.TEST_RESUMABLE

   
   c. The statement may issue the following error when the timeout set for the
      session has expired:

      SQL> insert into test_resumable values (1);
      insert into test_resumable values (1)
               *
      ERROR at line 1:
      ORA-30032: the suspended (resumable) statement has timed out
      ORA-01536: space quota exceeded for tablespace 'EXAMPLE'

4) The DBA now knows why the session hangs, and needs to find which action to take to alleviate the ora-1631 error:

   SQL>  connect system/password
   Connected.

   SQL> alter table <owner>.test_resumable storage (maxextents 8);
   Table altered.

   In alert.log file:

      Wed Mar 14 11:24:02 2001
      statement in resumable session 'User <username>(54), Session 9, Instance 1' was
      resumed

   and no more errors in DBA_RESUMABLE view:

   SQL> select user_id,SESSION_ID, STATUS, START_TIME, RESUME_TIME,
     2         SQL_TEXT, ERROR_NUMBER, ERROR_MSG
     3  from dba_resumable;

      USER_ID SESSION_ID STATUS    START_TIME           RESUME_TIME  
   ---------- ---------- --------- -------------------- --------------------
   SQL_TEXT
   -------------------------------------------------------------------------
   ERROR_NUMBER
   ------------
   ERROR_MSG
   -------------------------------------------------------------------------
           54          9 NORMAL    03/14/01 10:49:25    03/14/01 11:24:02
   insert into test_resumable select * from test_resumable
              0

   While the status is NORMAL or the error_number is 0, the resumable statements keep on working correctly unless the timeout is expired.  This also means that there are sessions set in resumable state.
   As soon as an error_number <> 0 appears, then a resumable session has encountered a space allocation issue.

   Note:
   The DBA can cancel the resumable transaction by aborting the session by the procedure DBMS_RESUMABLE.ABORT(sid#). An ORA-1013 "user requested cancel of  current operation" is returned to the user.

5) If the session does not need to be in resumable state, the session can disable the resumable state:

   SQL> alter session disable resumable;
   Session altered.

   SQL> select user_id,SESSION_ID, STATUS, START_TIME, RESUME_TIME,
     2         SQL_TEXT, ERROR_NUMBER, ERROR_MSG
     3  from dba_resumable;

   no rows selected


Other space errors that suspend transactions


***
statement in resumable session 'User <username>(54), Session 9, Instance 1' was
suspended due to
ORA-01536: space quota exceeded for tablespace 'EXAMPLE'

***
statement in resumable session 'User <username>(54), Session 8, Instance 1' was
suspended due to
ORA-01562: failed to extend rollback segment number 11

***
statement in resumable session 'User <username>(54), Session 8, Instance 1' was
suspended due to
ORA-01628: max # extents (2) reached for rollback segment RS01
FULL status of rollback segment 11 set

***
statement in resumable session 'User <username>(54), Session 9, Instance 1' was
suspended due to
ORA-01631: max # extents (2) reached in table <owner>.TEST_RESUMABLE

***
statement in resumable session 'User SYSTEM(5), Session 8, Instance 1' was
suspended due to
ORA-01652: unable to extend temp segment by 32 in tablespace TEMP_TS

***
statement in resumable session 'User <username>(54), Session 9, Instance 1' was
suspended due to
ORA-01653: unable to extend table <owner>.TEST_RESUMABLE by 256 in tablespace USERS

***
statement in resumable session 'User <username>(34), Session 8, Instance 1' was
suspended due to
ORA-01654: unable to extend index <owner>.SYS_IOT_TOP_27956 by 8 in tablespace PERM_DICT_2K

***
statement in resumable session 'User SYSTEM(5), Session 11, Instance 1' was
suspended due to
ORA-01658: unable to create INITIAL extent for segment in tablespace LMT_1

***
statement in resumable session 'User SYSTEM(5), Session 11, Instance 1' was
suspended due to
ORA-01659: unable to allocate MINEXTENTS beyond 42 in tablespace LMT_1


Other messages in alert.log:
***
Wed Mar 14 10:43:52 2001
statement in resumable session 'User <username>(54), Session 9, Instance 1' was
aborted











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











Symptoms

The import DataPump session completes with the following errors:
ORA-31693: Table data object "[schema]"."[table-name]" failed to load/unload and is being skipped due to error:
ORA-30032: the suspended (resumable) statement has timed out
ORA-30036: unable to extend segment by 8 in undo tablespace '<UNDO_TABLESPACE>'
Job "[user]"."SYS_IMPORT_TABLE_01" completed with 141 error(s) at 01:15:34
This indicates that ROLLBACK was being performed during the time in which no progress was made. It appears there is excessive UNDO being generated.
 
Changes


Cause

Excess undo generation can occur when there is a Primary Key (PK) constraint present on the system. Import DataPump will perform index maintenance and this can increase undo usage especially if there is other DML occurring on the database. 这句话值得推敲，为什么Rollback期间产生的UNDO变多了，因为index块要额外去维护undo
 
Solution

Disable constraints for Primary Keys (PK) on the database during import datapump load. This will reduce undo as index maintenance will not be performed.







免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。