오라클 19c alter database default tablespace 명령어

OS 환경 : Oracle Linux 8.1 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

방법 : 오라클 19c alter database default tablespace 명령어

오라클 환경에서는 각각의 유저별로 default tablespace를 지정해줄 수 있음
하지만 db전체 일반 유저의 default tablespace도 변경할수 있는데 제목과 같이 "alter database default tablespace" 명령으로 변경해줄 수 있음
모든 일반 유저의 default tablespace를 변경하는것이기 때문에 잘 확인을 하고 써야함
테스트 결과 따로 롤백(이전값으로 되돌리는)하는 명령은 찾지 못했음

 

 

유저의 default 테이블스페이스 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48

SQL>  set lines 200 pages 1000 col username for a25 col tablespace for a25 select username, default_tablespace from dba_users;   USERNAME                  DEFAULT_TABLESPACE ------------------------- ------------------------------ SYS                       SYSTEM SYSTEM                    SYSTEM XS$NULL                   SYSTEM OJVMSYS                   SYSTEM LBACSYS                   SYSTEM OUTLN                     SYSTEM SYS$UMF                   SYSTEM DBSNMP                    SYSAUX APPQOSSYS                 SYSAUX DBSFWUSER                 SYSAUX GGSYS                     SYSAUX ANONYMOUS                 SYSAUX CTXSYS                    SYSAUX DVSYS                     SYSAUX DVF                       SYSAUX GSMADMIN_INTERNAL         SYSAUX MDSYS                     SYSAUX OLAPSYS                   SYSAUX XDB                       SYSAUX WMSYS                     SYSAUX GSMCATUSER                USERS MDDATA                    USERS SYSBACKUP                 USERS REMOTE_SCHEDULER_AGENT    USERS GSMUSER                   USERS SYSRAC                    USERS GSMROOTUSER               USERS SI_INFORMTN_SCHEMA        USERS AUDSYS                    USERS DIP                       USERS ORDPLUGINS                USERS SYSKM                     USERS ORDDATA                   USERS ORACLE_OCM                USERS SYSDG                     USERS ORDSYS                    USERS IMSI                      IMSITS TEST                      IMSITS   38 rows selected.

현재 imsi유저와 test 유저를 제외한 나머지 유저의 default tablespace는 users임, 이외 system, sysaux가 default tablespace인 시스템 유저들도 존재함
imsi, test 유저는 유저 생성시 default tablespace를 imsits로 지정해준 상태임

 

 

db의 default tablespace 확인

1 2 3 4 5 6 7

SQL>  col property_value for a20 select property_value from database_properties where property_name = 'DEFAULT_PERMANENT_TABLESPACE';   PROPERTY_VALUE -------------------- USERS

현재 db 자체의 default_tablespace는 users임

 

 

database의 default tablespace 를 imsits로 변경

1 2 3

SQL> alter database default tablespace imsits;   Database altered.

 

 

유저의 default 테이블스페이스 재확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48

SQL>  set lines 200 pages 1000 col username for a25 col tablespace for a25 select username, default_tablespace from dba_users;   USERNAME                  DEFAULT_TABLESPACE ------------------------- ------------------------------ SYS                       SYSTEM SYSTEM                    SYSTEM XS$NULL                   SYSTEM OJVMSYS                   SYSTEM LBACSYS                   SYSTEM OUTLN                     SYSTEM SYS$UMF                   SYSTEM DBSNMP                    SYSAUX APPQOSSYS                 SYSAUX DBSFWUSER                 SYSAUX GGSYS                     SYSAUX ANONYMOUS                 SYSAUX CTXSYS                    SYSAUX DVSYS                     SYSAUX DVF                       SYSAUX GSMADMIN_INTERNAL         SYSAUX MDSYS                     SYSAUX OLAPSYS                   SYSAUX XDB                       SYSAUX WMSYS                     SYSAUX GSMCATUSER                IMSITS MDDATA                    IMSITS SYSBACKUP                 IMSITS REMOTE_SCHEDULER_AGENT    IMSITS GSMUSER                   IMSITS SYSRAC                    IMSITS GSMROOTUSER               IMSITS SI_INFORMTN_SCHEMA        IMSITS AUDSYS                    IMSITS DIP                       IMSITS ORDPLUGINS                IMSITS IMSI                      IMSITS SYSKM                     IMSITS ORDDATA                   IMSITS ORACLE_OCM                IMSITS SYSDG                     IMSITS ORDSYS                    IMSITS TEST                      IMSITS   38 rows selected.

GSMCATUSER유저부터 SYSDG까지 오라클 내부유저들의 default tablespace가 imsits로 변경됨

 

 

다시 database의 default tablespace 를 users로 변경

1 2 3

SQL> alter database default tablespace users;   Database altered.

 

 

유저의 default 테이블스페이스 재확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48

SQL>  set lines 200 pages 1000 col username for a25 col tablespace for a25 select username, default_tablespace from dba_users;   USERNAME                  DEFAULT_TABLESPACE ------------------------- ------------------------------ SYS                       SYSTEM SYSTEM                    SYSTEM XS$NULL                   SYSTEM OJVMSYS                   SYSTEM LBACSYS                   SYSTEM OUTLN                     SYSTEM SYS$UMF                   SYSTEM DBSNMP                    SYSAUX APPQOSSYS                 SYSAUX DBSFWUSER                 SYSAUX GGSYS                     SYSAUX ANONYMOUS                 SYSAUX CTXSYS                    SYSAUX DVSYS                     SYSAUX DVF                       SYSAUX GSMADMIN_INTERNAL         SYSAUX MDSYS                     SYSAUX OLAPSYS                   SYSAUX XDB                       SYSAUX WMSYS                     SYSAUX GSMCATUSER                USERS MDDATA                    USERS SYSBACKUP                 USERS REMOTE_SCHEDULER_AGENT    USERS GSMUSER                   USERS SYSRAC                    USERS GSMROOTUSER               USERS SI_INFORMTN_SCHEMA        USERS AUDSYS                    USERS DIP                       USERS ORDPLUGINS                USERS IMSI                      USERS SYSKM                     USERS ORDDATA                   USERS ORACLE_OCM                USERS SYSDG                     USERS ORDSYS                    USERS TEST                      USERS   38 rows selected.

다시 모두 users 테이블스페이스로 변경됨

 

 

결론 :
"alter database default tablespace" 명령으로 db 전체의 default tablespace를 변경하게 되면
모든 일반 유저의 default tablespace가 변경되것이기 때문에 많은 유저를 가지고 있고, 다양한 default tablespace가 지정되어 있는경우 이 명령을 절대 사용하면 안됨
테스트 결과 따로 롤백(이전값으로 되돌리는)하는 명령은 찾지 못했기 때문에 본문과 같이 default tablespace를 변경해버리면 이전에 설정되어있던 default tablespace가 뭐였는지 찾을수 없게됨
백업본을 다시 복구해서 거기서 확인하거나, 각각의 유저에 들어가서 가장 많은 오브젝트가 담긴 tablespace가 뭔지 확인하고 수동으로 각각 변경해주어야함

 

 

참조 :