Resumable space allocation in 10g

 

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.

In  Oracle 10gR1 the parameter RESUMABLE_TIMEOUT
can also be set on system level for all sessions.
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Mar 31 11:58:08 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user test identified by test
  2  default tablespace example
  3  quota 1m on example;

User created.

SQL> grant create session ,create table to test;

Grant succeeded.

SQL> conn test
Enter password:
Connected.
SQL> create table t1 (eno number);

Table created.
SQL> BEGIN FOR i IN 1..10000 LOOP
  2   INSERT INTO t1 VALUES(i);
  3  END LOOP;
  4   COMMIT;
  5   END;
  6  /

PL/SQL procedure successfully completed.
SQL>  insert into t1 select * from t1;

10000 rows created.

SQL>  insert into t1 select * from t1;

20000 rows created.

SQL>  insert into t1 select * from t1;
 insert into t1 select * from t1
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace ‘EXAMPLE’

 

SQL> SELECT COUNT(*) FROM t1;

  COUNT(*)
———-
     40000

SQL> ALTER SESSION ENABLE RESUMABLE TIMEOUT 600;
ERROR:
ORA-01031: insufficient privileges
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> grant resumable to test;

Grant succeeded.
SQL> conn test
Enter password:
Connected.
SQL>  insert into t1 select * from t1;
 insert into t1 select * from t1
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace ‘EXAMPLE’
SQL> ALTER SESSION ENABLE RESUMABLE TIMEOUT 600;

Session altered.

SQL>  insert into t1 select * from t1;(this statement will wait )

 

————————from Another session (session 2) ——————-

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Mar 31 12:19:46 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Enter user-name: / as sysdba
SQL> select sid,state,event from v$session_wait where sid=159;

       SID STATE         EVENT
———- ———– ——————————————-

       159 WAITING     statement suspended, wait error to be cleared

 

SQL> alter user test quota unlimited on example;

User altered.
————————Now check the SESSION 1—————————-
SQL>  insert into t1 select * from t1;

40000 rows created.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s