default_permanent_tablespace

* DEFAULT_PERMANENT_TABLESPACE / DEFAULT_TEMP_TABLESPACE 란
- 유저 생성 시 default tablespace 절 / temporary tablespace 절을 지정하지 않을 시
default 로 지정되는 tablespace 및 temporary tablespace 를 의미한다.
- 기존 유저들의 tablespace 가 변경되는 것은 아니다.

 

 

1. DEFAULT_PERMANENT_TABLESPACE / DEFAULT_TEMP_TABLESPACE 확인
SQL> set lines 500
SQL> set pages 500
SQL> col property_name for a40
SQL> col property_value for a25
SQL> select property_name, property_value
  2  from database_properties
  3  where property_name like '%TABLESPACE';

 

PROPERTY_NAME                             PROPERTY_VALUE
----------------------------------------     -------------------------
DEFAULT_TEMP_TABLESPACE                TEMP
DEFAULT_PERMANENT_TABLESPACE       USERS

2. 유저 생성

1) default tablespace 절을 생략하여 유저 생성
SQL> create user defuser identified by defuser;

User created.

2) default tablespace 절을 지정하여 유저 생성
SQL> create user tsuser identified by tsuser default tablespace  
  2  test_ts01;

 

User created.

 

3. 확인
SQL> select username, default_tablespace
  2  from dba_users
  3  where username in ('DEFUSER','TSUSER');

 

USERNAME                     DEFAULT_TABLESPACE
------------------------------ ------------------------------
DEFUSER                        USERS
TSUSER                          TEST_TS01

 

4. 유저에게 권한 부여
SQL> grant connect, resource to defuser, tsuser;

Grant succeeded.

 

5. 각각의 유저로 접속하여 테이블 생성 (테이블 생성 시 tablespace 절 생략)

SQL> conn defuser/defuser
Connected.

SQL> create table test01 (no number(15));

Table created.

SQL> insert into test01 values(10);

1 row created.

SQL> commit;

Commit complete.

SQL> conn tsuser/tsuser
Connected.

SQL> create table test01 (no number(15));

Table created.

SQL> insert into test01 values (10);

1 row created.

SQL> commit;

Commit complete.

 

6. 각각의 유저에서 만든 테이블이 저장된 tablespace 확인
SQL> conn /as sysdba
Connected.

SQL> set lines 500
SQL> set pages 500
SQL> col owner for a10
SQL> col segment_name for a15
SQL> col tablespace_name for a15
SQL> col extents for 99999
SQL> col blocks 99999
SQL> select owner, segment_name, tablespace_name, extents, blocks
  2  from dba_segments
  3  where segment_name='TEST01';

 

OWNER   SEGMENT_NAME    TABLESPACE_NAME EXTENTS     BLOCKS
---------- ---------------         ---------------         -------         ----------
DEFUSER  TEST01                 USERS                     1             8
TSUSER    TEST01                 TEST_TS01                1             8

7. DEFAULT_PERMANENT_TABLESPACE 변경
SQL> alter database default tablespace test_ts01;

Database altered.

SQL> set lines 500
SQL> set pages 500
SQL> col property_name for a40
SQL> col property_value for a25
SQL> select property_name, property_value
  2  from database_properties
  3  where property_name like '%TABLESPACE';

PROPERTY_NAME                             PROPERTY_VALUE
---------------------------------------- -------------------------
DEFAULT_TEMP_TABLESPACE               TEMP
DEFAULT_PERMANENT_TABLESPACE     TEST_TS01

 

8. DEFAULT_PERMANENT_TABLESPACE 삭제 시도
SQL> drop tablespace test_ts01;
drop tablespace test_ts01
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace
--> DEFAULT_PERMANENT_TABLESPACE는 삭제 안됨.

 

9. 다른 Tablespace 로 DEFAULT_PERMANENT_TABLESPACE를 변경
SQL> alter database default tablespace users;

Database altered.
 

10. temporary tablespace 만들고 확인하기

SQL> create user tmpuser identified by tmpuser default tablespace
  2  users temporary tablespace temp2;

User created.

SQL> select username, default_tablespace, temporary_tablespace
  2  from dba_users
  3  where username='TMPUSER';

USERNAME                     DEFAULT_TABLESPACE       TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
TMPUSER                        USERS                            TEMP2

 

SQL> col username for a15;
SQL> /

USERNAME DEFAULT_TABLESPACE            TEMPORARY_TABLESPACE
--------------- ------------------------------ ------------------------------
TMPUSER  USERS                                  TEMP2