유저의 디폴트 테이블 스페이스

Q :
유저를 생성하고 default tablespace를 줬습니다. 그런데 예상하고 다르게 SYSTEM tablespace에 쌓인 데이터가 있습니다.
SYSTEM tablespace에는 일반 유저의 데이터는 쌓이지 못하도록 하는 방법은 없을까요?


버전은 다음과 같습니다.

SQL> select * from v$version where rownum <= 1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

테스트 할 테이블 스페이스를 생성하고, 유저를 생성하고 디폴트 테이블스페이스를 지정해줍니다.
그리고 권한을 부여합니다.

SQL> create tablespace test_ts
  2  datafile '/u01/app/test_ts01.dbf' size 10m;

Tablespace created.

SQL> create user test identified by test
  2  default tablespace test_ts;

User created.

SQL> grant resource, connect to test;

Grant succeeded.

테스트용 테이블과 인덱스를 생성합니다.
테이블은 디폴트 테이블 스페이스에 저장되도록 하고, 인덱스는 SYSTEM 테이블스페이스에 저장되도록 지정합니다.

SQL> conn test/test
Connected.

SQL> create table t1(col1 number, col2 varchar2(100));

Table created.

SQL> insert into t1        
  2  select level, 'test'
  3  from dual
  4  connect by level <= 100;

100 rows created.

SQL> commit;

Commit complete.

SQL> create index t1_col1_idx on t1(col1)
  2  tablespace system;

Index created.

test 유저의 segment가 어느 테이블 스페이스에 저장되었는지 확인해 봅니다. T1_COL1_IDX 인덱스는 SYSTEM에 저장되었습니다.
이렇게 되는 것을 방지하기 위한 방법이 어떤 것일까가 이 질문의 요지입니다.

SQL> select tablespace_name, segment_name
  2  from user_segments;

TABLESPACE_NAME         SEGMENT_NAME
------------------------------ --------------------
SYSTEM          T1_COL1_IDX
TEST_TS          T1

그러면 SYSTEM 테이블스페이스에 저장되지 않기 위해 주는 옵션을 이제부터 알아보죠.

새로운 유저를 생성하고. 권한을 부여합니다.

SQL> create user test2 identified by test2
  2  default tablespace test_ts
  3  quota 0M on system;        --> system 테이블 스페이스에는 이 유저에게 할당된 공간은 주지 않는다 라는 의미입니다.

User created.

SQL> grant resource, connect to test2;

Grant succeeded.

test2유저로 접속하여, 테이블과 인덱스를 생성합니다.

SQL> conn test2/test2
Connected.

SQL> create table t1 (col1 number, col2 varchar2(10));

Table created.

SQL> insert into t1
  2  select level, 'test'
  3  from dual
  4  connect by level <= 100;

100 rows created.

SQL> commit;

Commit complete.

SQL> create index t1_col1_idx on t1(col1)
  2  tablespace system;

Index created.

생성된 세그먼트 들이 어느 테이블 스페이스에 존재하는지 확인해보자.

SQL> select tablespace_name, segment_name from user_segments;

TABLESPACE_NAME         SEGMENT_NAME
------------------------------ --------------------
SYSTEM          T1_COL1_IDX
TEST_TS          T1

뚜둥~~!!
SYSTEM 테이블 스페이스에 생성되었습니다. 이상하네요.
이론대로 한다면 SYSTEM 테이블 스페이스에는 생성되지 않아야 하는데....

원인은 다음과 같습니다. RESOURCE 롤을 부여할 경우 유저에 UNLIMITED TABLESPACE 권한이 자동으로 부여됩니다.
확인해 볼까요?

SQL> select * from dba_sys_privs where grantee in ('RESOURCE','TEST');

GRANTEE          PRIVILEGE    ADM
------------------------------ ---------------------------------------- ---
RESOURCE         CREATE TRIGGER    NO
RESOURCE         CREATE SEQUENCE    NO
RESOURCE         CREATE TYPE    NO
RESOURCE         CREATE PROCEDURE    NO
RESOURCE         CREATE CLUSTER    NO
RESOURCE         CREATE OPERATOR    NO
RESOURCE         CREATE INDEXTYPE    NO
RESOURCE         CREATE TABLE    NO
TEST          UNLIMITED TABLESPACE   NO

이 유저의 UNLIMITED TABLESPACE 권한을 박탈하고 다시 오브젝트를 생성하면 에러가 발생할 것 같습니다.

TEST유저의 UNLIMITED TABLESPACE 권한을 뺏습니다.

SQL> revoke unlimited tablespace from test;

Revoke succeeded.

그리고 다시 오브젝트를 생성합니다. 저장되는 테이블 스페이스는 system으로 지정해주고요.

SQL> create table t1_after_change (col1 number)
  2  tablespace system;
create table t1_after_change (col1 number)
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'SYSTEM'

예상했던 대로 system 테이블 스페이스에는 할당된 공간이 없다는 메시지가 나오네요.


A :
유저 생성시에 옵션을 주면 됩니다.

SQL> create user test2 identified by test2
  2  default tablespace test_ts
  3  quota 0M on system;  

단, 현재 유저에게 unlimited tablespace 권한이 없어야 가능합니다.