OS환경 : Oracle Linux 6.8 (64bit)
DB 환경 : Oracle Database 11.2.0.4
방법 : 오라클 11g R2 Temp Tablespace 재생성
현재 Default Temp Tablespace 확인
1 2 3 4 5 6 7 8 9 | SQL> col name for a25 col value$ for a15 col comment$ for a50 select * from sys.props$ where name = 'DEFAULT_TEMP_TABLESPACE'; NAME VALUE$ COMMENT$ ------------------------- --------------- -------------------------------------------------- DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace |
해당 Temp 파일 확인
1 2 3 4 5 6 7 8 | SQL> col file_name for a50 col tablespace_name for a15 select file_id, tablespace_name, file_name, bytes/1024/1024 mb, autoextensible from dba_temp_files; FILE_ID TABLESPACE_NAME FILE_NAME MB AUT ---------- --------------- -------------------------------------------------- ---------- --- 1 TEMP /oracle/app/oracle/oradata/ORCL11/temp01.dbf 500 NO |
500MB 크기로 자동증가 옵션 없이 사용중
유저들의 default Temp Tablespace 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SQL> select username, default_tablespace, temporary_tablespace from dba_users; USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ ------------------------------ WAITUSER WAIT_TS TEMP PERFSTAT STATSPACK_TS TEMP IMSI USERS TEMP MINSU USERS TEMP OPS$ORACLE USERS TEMP ORACLE_OCM USERS TEMP DIP USERS TEMP APPQOSSYS SYSAUX TEMP WMSYS SYSAUX TEMP DBSNMP SYSAUX TEMP SYSTEM SYSTEM TEMP SYS SYSTEM TEMP OUTLN SYSTEM TEMP 13 rows selected. |
현재모든 유저의 Temp Tablespace 가 'TEMP' tablespace임
새로운 Temp Tablespace TEMP2 생성
1 2 3 | SQL> create temporary tablespace temp2 tempfile '/oracle/app/oracle/oradata/ORCL11/temp02.dbf' size 500m; Tablespace created. |
해당 Temp Tablespace 확인
1 2 3 4 5 6 | SQL> select file_id, tablespace_name, file_name, bytes/1024/1024 mb, autoextensible from dba_temp_files; FILE_ID TABLESPACE_NAME FILE_NAME MB AUT ---------- --------------- -------------------------------------------------- ---------- --- 1 TEMP /oracle/app/oracle/oradata/ORCL11/temp01.dbf 500 NO 2 TEMP2 /oracle/app/oracle/oradata/ORCL11/temp02.dbf 500 NO |
정상적으로 생성됨
Default Temp Tablespace 를 TEMP 에서 TEMP2로 변경
1 2 3 | SQL> alter database default temporary tablespace temp2; Database altered. |
유저들의 default Temp Tablespace 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SQL> select username, default_tablespace, temporary_tablespace from dba_users; USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ ------------------------------ WAITUSER WAIT_TS TEMP2 PERFSTAT STATSPACK_TS TEMP2 IMSI USERS TEMP2 MINSU USERS TEMP2 OPS$ORACLE USERS TEMP2 ORACLE_OCM USERS TEMP2 DIP USERS TEMP2 APPQOSSYS SYSAUX TEMP2 WMSYS SYSAUX TEMP2 DBSNMP SYSAUX TEMP2 SYSTEM SYSTEM TEMP2 SYS SYSTEM TEMP2 OUTLN SYSTEM TEMP2 13 rows selected. |
정상적으로 Temp Tablespace 가 TEMP2 로 변경됨
개별 유저 Temp Tablespace 변경
IMSI 유저의 Temp Tablespace 를 TEMP2에서 TEMP로 변경
1 2 3 | SQL> alter user imsi temporary tablespace temp; User altered. |
유저들의 default Temp Tablespace 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SQL> select username, default_tablespace, temporary_tablespace from dba_users; USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ ------------------------------ IMSI USERS TEMP WAITUSER WAIT_TS TEMP2 PERFSTAT STATSPACK_TS TEMP2 MINSU USERS TEMP2 OPS$ORACLE USERS TEMP2 ORACLE_OCM USERS TEMP2 DIP USERS TEMP2 APPQOSSYS SYSAUX TEMP2 WMSYS SYSAUX TEMP2 DBSNMP SYSAUX TEMP2 SYSTEM SYSTEM TEMP2 SYS SYSTEM TEMP2 OUTLN SYSTEM TEMP2 13 rows selected. |
IMSI유저만 정상적으로 'TEMP' Temp Tablespace 를 사용함
이상태에서 'TEMP' Temp Tablespace 삭제
1 2 3 | SQL> drop tablespace temp including contents and datafiles; Tablespace dropped. |
정삭적으로 삭제는 가능함
IMSI 유저 테스트용 테이블, 데이터 생성
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 | SQL> create table imsi.ttest as select * from dba_objects; Table created. SQL> insert into imsi.ttest select * from imsi.ttest; 14391 rows created. SQL> / 28782 rows created. SQL> / 57564 rows created. SQL> / 115128 rows created. SQL> / 230256 rows created. SQL> commit; Commit complete. |
pga 사용을 줄이기 위해 pga_aggregate_target 10m로 변경
1 2 3 4 5 6 7 8 9 | SQL> alter system set pga_aggregate_target = 10m; System altered. SQL> show parameter pga NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_target big integer 10M |
IMSI 유저로 TEMP 사용 테스트
1 2 3 4 5 6 7 8 9 10 11 12 13 | SQL> conn imsi/imsi Connected. SQL> select * from ttest union all select * from ttest union all select * from ttest union all select * from ttest union all select * from ttest union all select * from ttest order by 11,12,9,10,7,8,5,6,3,4,1,2; * ERROR at line 1: ORA-00959: tablespace 'TEMP' does not exist |
'TEMP' tablespace 가 존재하지 않는다고 나옴
SYS로 다시 TEMP2 로변경
1 2 3 | SQL> alter user imsi temporary tablespace temp2; User altered. |
IMSI 유저로 TEMP 사용 테스트
1 2 3 4 5 6 7 | SQL> select * from ttest union all select * from ttest union all select * from ttest union all select * from ttest union all select * from ttest union all select * from ttest order by 11,12,9,10,7,8,5,6,3,4,1,2; |
동시에 사용량 확인
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 | SQL> set line 150 set concat "+" col username format a10 col osuser format a10 col tablespace format a15 SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#, c.spid, a.username, a.osuser, a.status, a.sql_hash_value FROM v$session a, v$sort_usage b, v$process c WHERE a.saddr = b.session_addr and a.paddr=c.addr ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks / TABLESPACE SEGFILE# SEGBLK# BLOCKS SID SERIAL# SPID USERNAME OSUSER STATUS SQL_HASH_VALUE --------------- ---------- ---------- ---------- ------ ---------- ------------------------ ---------- ---------- -------- -------------- TEMP2 201 63104 128 35 15597 4222 IMSI oracle ACTIVE 3498062838 |
정상적으로 사용중(STATUS가 ACTIVE 상태)
* Temp Tablespace 사용중에도 Default Temp Tablespace 변경이 가능함
참조 : https://positivemh.tistory.com/26
https://positivemh.tistory.com/481
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 11g R2 ADG 브로커 파라미터 변경 방법 (0) | 2020.12.22 |
|---|---|
| 오라클 19c begin (핫) 백업 시 expdp 백업 가능여부 (0) | 2020.11.27 |
| 오라클 설치 이후 uid, gid 변경 가이드 (재설치) (0) | 2020.10.07 |
| 오라클 11g R2 10GB update 중 세션 kill 시 발생하는 문제 (0) | 2020.09.26 |
| 오라클 11g R2 /etc/hosts 권한 부족시 발생하는 문제 (0) | 2020.09.18 |