FAQ
Can someone tell me what I am missing here?

AS SYS USER:

1 CREATE OR REPLACE TRIGGER after_suspend
2 AFTER SUSPEND ON SCHEMA

3 DECLARE

4 BEGIN

5 EXECUTE IMMEDIATE 'ALTER user scott quota unlimited on tablespace
foo';
6* END;

/
trigger created.

AS SCOTT USER:

ALTER SESSION ENABLE RESUMABLE TIMEOUT 10;

create table foo(a number) tablespace foo;
*
ERROR at line 1:
ORA-30032: the suspended (resumable) statement has timed out
ORA-01536: space quota exceeded for tablespace 'FOO'

SELECT BANNER FROM V$VERSION

/
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

Am I forgetting something here? I've tried several versions of this trigger
including one that just raises and application error as soon as it fires but
nothing I have tried works. It seems the resumable part works fine but for
some reason the trigger never fires on the after suspend event.

Search Discussions

  • Stefan Knecht at Feb 20, 2007 at 8:40 pm
    You did grant resumable to scott ?

    Stefan
    On 2/20/07, John Darrah wrote:

    Can someone tell me what I am missing here?

    AS SYS USER:
    1 CREATE OR REPLACE TRIGGER after_suspend
    2 AFTER SUSPEND ON SCHEMA
    3 DECLARE
    4 BEGIN
    5 EXECUTE IMMEDIATE 'ALTER user scott quota unlimited on tablespace
    foo';
    6* END;
    /
    trigger created.

    AS SCOTT USER:
    ALTER SESSION ENABLE RESUMABLE TIMEOUT 10;
    create table foo(a number) tablespace foo;
    *
    ERROR at line 1:
    ORA-30032: the suspended (resumable) statement has timed out
    ORA-01536: space quota exceeded for tablespace 'FOO'


    SELECT BANNER FROM V$VERSION
    /
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
    PL/SQL Release 10.2.0.3.0 - Production
    CORE 10.2.0.3.0 Production
    TNS for Linux: Version 10.2.0.3.0 - Production
    NLSRTL Version 10.2.0.3.0 - Production


    Am I forgetting something here? I've tried several versions of this
    trigger including one that just raises and application error as soon as it
    fires but nothing I have tried works. It seems the resumable part works
    fine but for some reason the trigger never fires on the after suspend event.

    --
    http://www.freelists.org/webpage/oracle-l
  • John Darrah at Feb 20, 2007 at 8:47 pm
    Stephan,

    I just realized there was one important parameter not set correctly.
    _system_trig_enabled was set to false. Woops. Its working now after much
    head scratching. Thanks for the reply.
    On 2/20/07, Stefan Knecht wrote:

    You did grant resumable to scott ?

    Stefan
    On 2/20/07, John Darrah wrote:

    Can someone tell me what I am missing here?

    AS SYS USER:
    1 CREATE OR REPLACE TRIGGER after_suspend
    2 AFTER SUSPEND ON SCHEMA
    3 DECLARE
    4 BEGIN
    5 EXECUTE IMMEDIATE 'ALTER user scott quota unlimited on
    tablespace foo';
    6* END;
    /
    trigger created.

    AS SCOTT USER:
    ALTER SESSION ENABLE RESUMABLE TIMEOUT 10;
    create table foo(a number) tablespace foo;
    *
    ERROR at line 1:
    ORA-30032: the suspended (resumable) statement has timed out
    ORA-01536: space quota exceeded for tablespace 'FOO'


    SELECT BANNER FROM V$VERSION
    /
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
    PL/SQL Release 10.2.0.3.0 - Production
    CORE 10.2.0.3.0 Production
    TNS for Linux: Version 10.2.0.3.0 - Production
    NLSRTL Version 10.2.0.3.0 - Production


    Am I forgetting something here? I've tried several versions of this
    trigger including one that just raises and application error as soon as it
    fires but nothing I have tried works. It seems the resumable part works
    fine but for some reason the trigger never fires on the after suspend event.

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 20, '07 at 4:38p
activeFeb 20, '07 at 8:47p
posts3
users2
websiteoracle.com

2 users in discussion

John Darrah: 2 posts Stefan Knecht: 1 post

People

Translate

site design / logo © 2022 Grokbase