My Oracle Support Banner

ORA-01536 After Revoking DBA Role (Doc ID 465737.1)

Last updated on AUGUST 06, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 8.1.7.4 to 11.2.0.4 [Release 8.1.7 to 11.2]
Information in this document applies to any platform.


Symptoms


ORA-01536: space quota exceeded for tablespace '<Tablespace_Name>'
After revoking DBA or Resource Role from a user

Example:

SQL> conn /as sysdba
Connected.
SQL> create user testrights identified by testos;
User created.
SQL> grant connect, resource to testrights;
Grant succeeded.
SQL> connect testrights/testos;
Connected.

SQL> CREATE TABLE "TESTRIGHTS"."TESTTAB" ( "TESTFIELD" VARCHAR2(200) NOT NULL
, CONSTRAINT "TESTPK" PRIMARY KEY ("TESTFIELD") VALIDATE ) TABLESPACE "USERS" STORAGE ( INITIAL 64M) ;
Table created.

SQL> conn /as sysdba
Connected.
SQL> grant dba to testrights;
Grant succeeded.
SQL> revoke dba from testrights;
Revoke succeeded.
SQL> show user
USER is "SYS"
SQL> drop table testrights.testtab;
Table dropped.
SQL> conn testrights/testos;
Connected.
SQL> CREATE TABLE "TESTRIGHTS"."TESTTAB" ( "TESTFIELD" VARCHAR2(200) NOT NULL
, CONSTRAINT "TESTPK" PRIMARY KEY ("TESTFIELD") VALIDATE ) TABLESPACE "USERS"  STORAGE ( INITIAL 64M) ;

CREATE TABLE "TESTRIGHTS"."TESTTAB" ( "TESTFIELD" VARCHAR2(200) NOT NULL ,
CONSTRAINT "TESTPK" PRIMARY KEY ("TESTFIELD") VALIDATE ) TABLESPACE "USERS"
 STORAGE ( INITIAL 64M)
*
ERROR at line 1:
ORA-1536: space quota exceeded for tablespace 'USERS'

SQL> conn /as sysdba
Connected.
SQL> grant connect, resource to testrights;
Grant succeeded.

SQL> conn testrights/testos;
Connected.
SQL>
SQL> CREATE TABLE "TESTRIGHTS"."TESTTAB" ( "TESTFIELD" VARCHAR2(200) NOT NULL , CONSTRAINT "TESTPK" PRIMARY KEY ("TESTFIELD") VALIDATE ) TABLESPACE "USERS"
STORAGE ( INITIAL 64M) ;

Table created.

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.