Home » RDBMS Server » Server Administration » ORA-01536: space quota exceeded for tablespace 'TOOLS'
ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245370] Sat, 16 June 2007 11:12 Go to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
Hi,
All our application users are getting
ORA-01536: space quota exceeded for tablespace 'TOOLS'

when I try to run the following statement as sys user

alter user abc quota unlimed on tools;

I'm getting

ORA-00604: error occurred at recursive SQL level 1
ORA-01536: space quota exceeded for tablespace 'TOOLS'
ORA-06512: at line 2



even when I try to run

alter user xyz account unlock; as sys user am getting the same error.

Can anyone tell me what could be the problem is?


Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245371 is a reply to message #245370] Sat, 16 June 2007 11:41 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Too bad you chose to not read & follow the posting guidelines as enumerated in the #1 STICKY post at the top of this forum.
Too bad you decided to describe what you did rather than using CUT & PASTE so we could see for ourselves what happened.

>alter user abc quota unlimed on tools;
I seriously doubt that the SQL above produced the results you reported because you should have used UNLIMITED instead of "unlimed".
Please note you missed "it" between the "m" & the "e".

Needless to say it should work, but since it did not I have to believe the problem is PEBKAC induced.

Please post the results from SQL*Plus of the following SQL run as user SYS.
SQL> SHOW USER
SQL> SELECT * FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'TOOLS';

[Updated on: Sat, 16 June 2007 11:44] by Moderator

Report message to a moderator

Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245372 is a reply to message #245371] Sat, 16 June 2007 11:45 Go to previous messageGo to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
Hi,
That was a typo, let me cut and past the output:

SQL> alter user system quota unlimited on tools;
alter user system quota unlimited on tools
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01536: space quota exceeded for tablespace 'TOOLS'
ORA-06512: at line 2
Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245374 is a reply to message #245370] Sat, 16 June 2007 11:58 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
01536, 00000, "space quota exceeded for tablespace '%s'"
// *Cause:  The space quota for the segment owner in the tablespace has
//          been exhausted and the operation attempted the creation of a
//          new segment extent in the tablespace.
// *Action: Either drop unnecessary objects in the tablespace to reclaim
//          space or have a privileged user increase the quota on this
//          tablespace for the segment owner.
Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245376 is a reply to message #245370] Sat, 16 June 2007 12:04 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
It works for me.
sqlplus

SQL*Plus: Release 10.2.0.2.0 - Production on Sat Jun 16 09:53:42 2007

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

Enter user-name: / as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> alter user system quota unlimited on users;

User altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

FWIW - This database does not have a TOOLS tablespace which is why I used "USERS" instead.
Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245379 is a reply to message #245372] Sat, 16 June 2007 12:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For which user you execute this statement.

I bet you have a DDL trigger which tries to log the "alter user" in a user table that wants to extend but can't because of quota.

Post result (and format it otherwise I'll not read it):
select distinct trigger_type, triggering_event from dba_triggers where owner != 'SYS' order by 1,2;

Always post your Oracle version (4 decimals).
Read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS

Regards
Michel


Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245380 is a reply to message #245379] Sat, 16 June 2007 12:19 Go to previous messageGo to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member


Here is the output:


select distinct trigger_type, triggering_event from dba_triggers where owner != 'SYS' order by 1,2

AFTER EACH ROW DELETE
AFTER EACH ROW INSERT
AFTER EACH ROW INSERT OR UPDATE
AFTER EACH ROW INSERT OR UPDATE OR DELETE
AFTER EACH ROW UPDATE
AFTER EVENT DDL
AFTER EVENT LOGON
AFTER STATEMENT INSERT OR UPDATE OR DELETE
AFTER STATEMENT UPDATE
AFTER STATEMENT UPDATE OR DELETE
BEFORE EACH ROW DELETE
BEFORE EACH ROW INSERT
BEFORE EACH ROW INSERT OR UPDATE
BEFORE EACH ROW INSERT OR UPDATE OR DELETE
BEFORE EACH ROW UPDATE
BEFORE EACH ROW UPDATE OR DELETE
BEFORE EVENT DROP OR TRUNCATE
BEFORE STATEMENT INSERT
INSTEAD OF INSERT OR UPDATE OR DELETE

19 rows selected.
Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245383 is a reply to message #245380] Sat, 16 June 2007 12:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I previously said: if it is not formatted I don't have a single look.
You also don't answer the other questions.
We want copy and paste of your screen like Ana post. Use SQL*Plus.

For the moment, You're On You Own (YOYO).

Regards
Michel

Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245384 is a reply to message #245383] Sat, 16 June 2007 12:32 Go to previous messageGo to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
---here it is...
{
SQL> alter user system account unlock;
alter user system account unlock
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01536: space quota exceeded for tablespace 'TOOLS'
ORA-06512: at line 2


SQL>
}
Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245385 is a reply to message #245370] Sat, 16 June 2007 12:36 Go to previous messageGo to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
SQL> alter user system account unlock;
alter user system account unlock
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01536: space quota exceeded for tablespace 'TOOLS'
ORA-06512: at line 2
Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245386 is a reply to message #245370] Sat, 16 June 2007 12:38 Go to previous messageGo to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
SQL> select distinct trigger_type, triggering_event from dba_triggers where owner != 'SYS' order by 1,2
  2  ;
AFTER EACH ROW   DELETE
AFTER EACH ROW   INSERT
AFTER EACH ROW   INSERT OR UPDATE
AFTER EACH ROW   INSERT OR UPDATE OR DELETE
AFTER EACH ROW   UPDATE
AFTER EVENT      DDL
AFTER EVENT      LOGON
AFTER STATEMENT  INSERT OR UPDATE OR DELETE
AFTER STATEMENT  UPDATE
AFTER STATEMENT  UPDATE OR DELETE
BEFORE EACH ROW  DELETE
BEFORE EACH ROW  INSERT
BEFORE EACH ROW  INSERT OR UPDATE
BEFORE EACH ROW  INSERT OR UPDATE OR DELETE
BEFORE EACH ROW  UPDATE
BEFORE EACH ROW  UPDATE OR DELETE
BEFORE EVENT     DROP OR TRUNCATE
BEFORE STATEMENT INSERT
INSTEAD OF       INSERT OR UPDATE OR DELETE
 
19 rows selected.

  1* select distinct trigger_type, triggering_event from dba_triggers where owner = 'SYS' order by 1,2
  2  /
AFTER EVENT      DROP
AFTER EVENT      LOGON
AFTER EVENT      STARTUP
BEFORE EVENT     ALTER
BEFORE EVENT     CREATE
BEFORE EVENT     DDL
BEFORE EVENT     DROP
BEFORE EVENT     SHUTDOWN
 
8 rows selected.

Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245387 is a reply to message #245370] Sat, 16 June 2007 12:40 Go to previous messageGo to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
I login as SYS user and trying to execute the alter statement.
Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245388 is a reply to message #245370] Sat, 16 June 2007 12:58 Go to previous messageGo to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
Hi Michel,
I posted as per the instructions, hope this is what you expected.
Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245389 is a reply to message #245370] Sat, 16 June 2007 13:06 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Please post the results via CUT & PASTE from SQL*Plus of the following SQL run as user SYS & nicely formated.
SQL> SHOW USER
SQL> SELECT * FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'TOOLS';
SQL> SELECT SUM(BLOCKS) FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME = 'TOOLS';
Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245390 is a reply to message #245370] Sat, 16 June 2007 13:16 Go to previous messageGo to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
SQL> SHOW USER
USER is "SYS"
SQL> SELECT * FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'TOOLS'; 
 
TOOLS                               16384          65536                       1  2147483645  
                 65536 ONLINE    PERMANENT NOLOGGING NO  LOCAL          SYSTEM    NO  MANUAL DISABLED NOT APPLY   NO
 
SQL> SELECT SUM(BLOCKS) FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME = 'TOOLS'; 
 
     175100
 
SQL> 

[Updated on: Sat, 16 June 2007 14:48] by Moderator

Report message to a moderator

Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245392 is a reply to message #245388] Sat, 16 June 2007 13:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please, wait a minute I'm not always in front of my screen.
If you want to know, I took a shower.

Now, going on to my idea post:
select owner, trigger_name, triggering_event from dba_trigges where triggering_event in ('DDL','ALTER') order by 1,2;

Note: this will not prevent from posting what Ana asked you.

Regards
Michel
Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245393 is a reply to message #245370] Sat, 16 June 2007 13:25 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Please include column labels in future results(SQL> set pages 66)

Please post results from
SELECT COUNT(*) FROM dba_triggers where TRIGGERING_EVENT = 'DDL'
Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245394 is a reply to message #245370] Sat, 16 June 2007 13:28 Go to previous messageGo to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
 
SQL> show user
USER is "SYS"
SQL> l
  1* select owner, trigger_name, triggering_event from dba_triggers where triggering_event in ('DDL','ALTER') order by 1,2
SQL> /
 
no rows selected
 
SQL> 
Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245395 is a reply to message #245370] Sat, 16 June 2007 13:28 Go to previous messageGo to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
SQL> SELECT COUNT(*) FROM dba_triggers where TRIGGERING_EVENT = 'DDL' ;
 
         0
 
SQL> 
Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245397 is a reply to message #245370] Sat, 16 June 2007 13:34 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Please try
SELECT COUNT(*) FROM dba_triggers where TRIGGERING_EVENT LIKE 'DDL%' ;
Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245398 is a reply to message #245394] Sat, 16 June 2007 13:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ok, my fault, query is:
select owner, trigger_name, triggering_event 
from dba_trigges 
where triggering_event like 'DDL%'
   or triggering_event like 'ALTER%'
order by 1,2;

But if you read what you posted, you should see that.

In addition, put "set head on". I like having column header. Your answer to Ana is ununderstandable without column names.

Regards
Michel
Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245399 is a reply to message #245370] Sat, 16 June 2007 13:41 Go to previous messageGo to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
SQL> SELECT COUNT(*) FROM dba_triggers where TRIGGERING_EVENT LIKE 'DDL%' ;
 
         3
 
Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245400 is a reply to message #245398] Sat, 16 June 2007 13:43 Go to previous messageGo to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
  1  select owner, trigger_name, triggering_event
  2  from dba_triggers
  3  where triggering_event like 'DDL%'
  4     or triggering_event like 'ALTER%'
  5* order by 1,2
SQL> /
 
OWNER                          TRIGGER_NAME                   TRIGGERING_EVENT
------------------------------ ------------------------------ -------------------
ARAO                           DDLTRIGGER                     DDL
SYS                            NO_VM_ALTER                    ALTER
SYS                            TR_DDL_LOCK                    DDL
SYS                            TR_DDL_TRACK                   DDL
 

[Updated on: Sat, 16 June 2007 14:48] by Moderator

Report message to a moderator

Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245401 is a reply to message #245400] Sat, 16 June 2007 13:45 Go to previous messageGo to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
I inclueded STATUS column into it...

  1  select owner, trigger_name,status, triggering_event
  2  from dba_triggers
  3  where triggering_event like 'DDL%'
  4     or triggering_event like 'ALTER%'
  5* order by 1,2
SQL> /
 
OWNER                          TRIGGER_NAME                   STATUS   TRIGGERING_EVENT
------------------------------ ------------------------------ -------- --------------------
ARAO                           DDLTRIGGER                     ENABLED  DDL
SYS                            NO_VM_ALTER                    DISABLED ALTER
SYS                            TR_DDL_LOCK                    DISABLED DDL
SYS                            TR_DDL_TRACK                   DISABLED DDL

[Updated on: Sat, 16 June 2007 14:49] by Moderator

Report message to a moderator

Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245402 is a reply to message #245400] Sat, 16 June 2007 13:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
set long 10000
set longchunksize 100
set linesize 100
select trigger_body 
from dba_triggers 
where owner='ARAO' and trigger_name='DDLTRIGGER';
select bytes, max_bytes 
from dba_ts_quotas 
where tablespace_name='TOOLS' and username='ARAO';

Regards
Michel

[Updated on: Sat, 16 June 2007 13:48]

Report message to a moderator

Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245405 is a reply to message #245370] Sat, 16 June 2007 13:54 Go to previous messageGo to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
SQL> select trigger_body 
from dba_triggers 
where owner='ARAO' and trigger_name='DDLTRIGGER';
select bytes, max_bytes 
from dba_ts_quotas 
where tablespace_name='TOOLS' and username='ARAO';  2    3  BEGIN
 insert into
   ARAO.stats$ddl_log
 (
   user_name,
   ddl_date,
   ddl_type,
   object_type,
   owner,
   object_name
 )
 VALUES
 (
   ora_login_user,
   sysdate,
   ora_sysevent,
   ora_dict_obj_type,
   ora_dict_obj_owner,
   ora_dict_obj_name
 );
END;
 
Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245406 is a reply to message #245405] Sat, 16 June 2007 14:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the other one?

Regards
Michel
Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245408 is a reply to message #245370] Sat, 16 June 2007 14:14 Go to previous messageGo to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
SQL> set long 10000
set longchunksize 100
set linesize 100
SQL> SQL> SQL> 
SQL> l
  1  select bytes, max_bytes
  2  from dba_ts_quotas
  3* where tablespace_name='TOOLS' and username='ARAO'
SQL> /
 
no rows selected
 
SQL> 
Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245409 is a reply to message #245408] Sat, 16 June 2007 14:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select privilege from dba_sys_privs where grantee='ARAO' order by 1;

select granted_role from dba_role_privs where grantee='ARAO' order by 1;

Regards
Michel
Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245412 is a reply to message #245370] Sat, 16 June 2007 14:41 Go to previous messageGo to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
Hi Michal,
Thanks for your quick help, i was able to fix the problem by disabling the trigger after truncating the table ARAO.stats$ddl_log

Thanks again
Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245413 is a reply to message #245412] Sat, 16 June 2007 14:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes this is what I said in my first post:
Quote:
I bet you have a DDL trigger which tries to log the "alter user" in a user table that wants to extend but can't because of quota.

You also could give quota on the tablespace to the user as the error will happen again when the current allocated space for the table will be exhausted.

Regards
Michel
Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245598 is a reply to message #245370] Mon, 18 June 2007 03:38 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Michel , How I can be like you? SO MUCH CONFIDENT!!! Just amazing!!!
Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245607 is a reply to message #245598] Mon, 18 June 2007 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I just have almost 30 years more than you. ./fa/1601/0/
Come back here in 30 years and if I'm still there I'm pretty sure you'll know much more than I do.

Regards
Michel
icon9.gif  Re: ORA-01536: space quota exceeded for tablespace 'TOOLS' [message #245608 is a reply to message #245370] Mon, 18 June 2007 04:01 Go to previous message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I am in oracle for 1 month!!
Mad

[Updated on: Mon, 18 June 2007 04:02]

Report message to a moderator

Previous Topic: locating the init.ora
Next Topic: Do not answer my question as it is NOT urgent at all - How to kill a SYS session
Goto Forum:
  


Current Time: Fri Dec 09 03:55:27 CST 2016

Total time taken to generate the page: 0.15190 seconds