ORA-1536 : space quota exceeded for tables

ora-1536 error 는 user가 해당 tablespace에 quota를 할당받을수 없어 생기는 error로

UNLIMITED TABLESPACE privilege와 관련이 깊다.

보통은 resouce   role 권한을 부여하게 되면 자동으로 UNLIMITED TABLESPACE privilege가 부여된다

하지만  UNLIMITED TABLESPACE 가 resource role안에 포함된 것은 아니다 다음 예제를 통해 확인해보자.

 

********************** 유저 생성 ***********************

sql> create user dbworks identified by dbworks;
User created. 

************** resource, connect 권한 부여 **************
sql> grant connect , resource to dbworks;
Grant succeeded.

 

************** 유저가 부여받은 role 조회 **********************

sql> conn dbworks/dbworks
Connected.
idel> select * from user_role_privs;

USERNAME             GRANTED_ROLE         ADMIN_ DEFAUL OS_GRA
-------------------- -------------------- ------ ------ ------
DBWORKS              CONNECT              NO     YES    NO
DBWORKS              RESOURCE             NO     YES    NO

 

*************** 유저가 부여받은 role의 privilege 조회 *****************

idel> select * from role_sys_privs; 
ROLE                                                         PRIVILEGE            ADMIN_
------------------------------------------------------------ -------------------- ------
RESOURCE                                                     CREATE TRIGGER       NO
RESOURCE                                                     CREATE SEQUENCE      NO
RESOURCE                                                     CREATE TYPE          NO
RESOURCE                                                     CREATE PROCEDURE     NO
RESOURCE                                                     CREATE CLUSTER       NO
CONNECT                                                      CREATE SESSION       NO
RESOURCE                                                     CREATE OPERATOR      NO
RESOURCE                                                     CREATE INDEXTYPE     NO
RESOURCE                                                     CREATE TABLE         NO

 

=> 다음을 보면 reource role에 UNLIMITED TABLESPACE privilege가 포함되지 않은것을 알 수 있다.

 

**************** 유저가 부여받은 privilege 조회 **************************
idel> select * from user_sys_privs;
 
USERNAME             PRIVILEGE            ADMIN_
-------------------- -------------------- ------
DBWORKS              UNLIMITED TABLESPACE NO

 

=> 다음을 통해 UNLIMITED TABLESPACE privilege 가 role이 아닌 privilege로 따로 존재하는 것을 알 수 있다.

    즉, resouce role을 부여하게되면 따로 privilege로 unlimited tablespace 부여된다.

 

 

*********************  유저에게 dba role 부여  ******************************

sql> conn /as sysdba
Connected.

sql> grant dba to dbworks
Grant succeeded.

sql>conn dbworks/dbworks
Connected.

sql>select * from user_role_privs;
 
USERNAME             GRANTED_ROLE         ADMIN_ DEFAUL OS_GRA
-------------------- -------------------- ------ ------ ------
DBWORKS              CONNECT              NO     YES    NO
DBWORKS              DBA                  NO     YES    NO
DBWORKS              RESOURCE             NO     YES    NO

 

 

********************* 유저로 부터 DBA role revoke  *********************
sql> conn /as sysdba
Connected.

sql> revoke dba from dbworks;
Revoke succeeded.

sql> conn dbworks/dbworks
Connected.

idel> select * from user_role_privs;
 
USERNAME             GRANTED_ROLE         ADMIN_ DEFAUL OS_GRA
-------------------- -------------------- ------ ------ ------
DBWORKS              CONNECT              NO     YES    NO
DBWORKS              RESOURCE             NO     YES    NO

 

****************** 유저의 privilege 확인 ************************

sql> select * from user_sys_privs;
 
no rows selected

 

=> DBA role을  revoke 했는데 resource 권한을 부여했을 때 받은  UNLIMITED TABLESPACE privilege가 없어진 것을 확인 할 수 있다.

 

결론 : 특정 user를 migration 할때 권한 문제로 인한 fail을 대비해 해당유저에게 DBA권한을 주는 경우가 종종있다.

         migration 작업을 한후에 dba 권한을 다시 revoke 할땐 다시 UNLIMITED TABLESPACE privilege을 넣어주어야 차후에

         ORA-1536 error을 예방할 수 있다.  다시 resource role을 부여하는 것도 한 방법이 될 수 있다.