/* ======================================================== NAME : 오라클 SQL_PLUS DATE : 2015.11.11 E-MAIL : rf114@daum.net URL : http://www.rf114.com [LISENCE] This program is free web source from RF114.com This is not commercial lisence and as modify as possiable to this contents include. [SITE] http://www.oneone.kr/?mid=linuxetc ======================================================== */ - 접속 - sqlplus Userid/Password@1.2.3.4:1526/sid - 접속포트 - SELECT dbms_xdb.gethttpport() FROM dual; exec dbms_xdb.sethttpport(8088); - 데이타베이스명 - SELECT NAME, DB_UNIQUE_NAME FROM v$database; - SID - SELECT instance FROM v$thread; - 테이블스페이스 - SELECT tablespace_name, status FROM dba_tablespaces; SELECT tablespace_name, status, contents, extent_management, segment_space_management FROM dba_tablespaces; DROP TABLESPACE KITESHIELD INCLUDING CONTENTS AND DATAFILES; alter database datafile 'file path' offline drop; - 테이블스페이스_경로변경 - alter tablespace TIRS offline; alter tablespace TIRS rename datafile '/mnt/hospital/TIRS01.dbf' to '/D2/oradata/TIRS01.dbf'; alter tablespace TIRS online; - 테이블스페이스_용량관리 - set line 200; col file# for 999; col ts_name for a10; col total_blocks for 9999999; col used_blocks for 9999999; col pct_used for a10; select distinct d.file_id file#, d.tablespace_name ts_name, d.bytes /1024/1024 MB, d.bytes /8192 total_blocks, sum(e.blocks) used_blocks, to_char(nvl(round(sum(e.blocks)/(d.bytes/8192), 4),0) *100, '09.99') || '%' pct_used from dba_extents e, dba_data_files d where d.file_id = e.file_id(+) group by d.file_id , d.tablespace_name , d.bytes order by 1,2; - 경로확인 - SELECT status,enabled, t.name ,d.name FROM V$DATAFILE d, V$TABLESPACE t WHERE t.ts#=d.ts#; SELECT * FROM DBA_DATA_FILES; SELECT tablespace_name, bytes/1024/1024 MB , file_name FROM dba_data_files ; - Data file 크기 수동증가 시키기 - alter tablespace haksa add datafile '/app/oracle/oradata/testdb/haksa02.dbf' size 20M; - Data file 크기 자동증가 시키기 - alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' autoextend on; - 대소문자구분해제 - 설정 : alter system set sec_case_sensitive_logon = FALSE; 확인 : show parameter sec_case_sensitive_logon; ----------------------------------------------------------- [오라클 DB의 리스너 포트 변경하는 법] ----------------------------------------------------------- 리스너 포트가 listener.ora 파일만 변경한다고 해서 변경 되는게 아닙니다. 이것 때문에 얼마나 고생을 했는지.... 근데 오라클 메뉴얼에 있다는게 함정.... 먼저 lsnrctl stop 으로 리스너를 중지합니다 그리고 listener.ora에서 1521 포트를 변경하고자하는 포트로 변경합니다. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) (ADDRESS = (PROTOCOL = TCP)(HOST = DB서버호스트네임 or ip)(PORT = 1523)) ) ) 이렇게 설정해주고 나서 lsnrctl start 명령으로 재시작 합니다. 오라클로 접속한 다음에 $ sqlplus / as sysdba SQL> ALTER SYSTEM SET LOCAL_LISTENER = "(ADDRESS=(PROTOCOL=TCP)(HOST=DB서버호스트네임 or ip)(PORT=1523))"; SQL> ALTER SYSTEM REGISTER; lsnrctl status 명령과 netstat -nlp | grep 1523 으로 확인해보면 정상 변경이 확인 될 겁니다. # su - oracle $ cd ~ $ vi .bash_profile ----------------------------------------------------------- [오라클 환경변수] ----------------------------------------------------------- 아래 내용 추가 # oracle 10g setting TMP=/tmp; export TMP TMPDIR=$TMP; export TMPDIR ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME ORACLE_SID=orcl; export ORACLE_SID ORACLE_TERM=xterm; export ORACLE_TERM PATH=/usr/sbin:$PATH; export PATH PATH=$ORACLE_HOME/bin:$PATH; export PATH export LANG=C 추가후 저장하고 아래 실행 $ source .bash_profile ----------------------------------------------------------- Tablespace 의 종류 및 특징 ----------------------------------------------------------- (1) SYSTEM tablespace - 데이터 딕셔너리 정보들이 저장되어 있어서 이 Tablespace 가 손상될 경우 Oracle 서버가 시작이 안됨 데이터 딕셔너리 : 오라클 서버의 모든 정보를 저장하고 있는 아주 중요한 테이블이나 뷰들 - Static Dictionary : 내용이 실시간으로 변경 안됨 USER_XXX , ALL_XXX , DBA_XXX = DB 상태가 OPEN 일때만 조회가능 - Dynamic Performance View : 실시간으로 변경되는 내용을 볼 수 있음, Control File / 메모리로 가서 정보를 가져옴 , V $ XXX = DB상태 모든 상태에서 조회가능 데이터 딕셔너리에 들어있는 주요 정보 - 데이터베이스의 논리적인 구조와 물리적인 구조 정보들 - 객체의 정의와 공간 사용 정보들 - 제약조건에 관련된 정보들 - 사용자에 관련된 정보들 - Role, Privilege 등에 관련된 정보들 - 감사 및 보안등에 관련된 정보들 (2) SYSAUX tablespace 10g 버전부터 등장한 tablespace 로 oracle 서버의 성능 튜닝을 위한 데이터들이 저장되어 있음 (3) 일반 Tablespace 가장 일반적으로 많이 사용되는 tablespace로 관리자가 필요에 의해 만드는 tablespace DBA 에 의해 얼마든지 생성하고 삭제할 수 있음 실습 1. 일반 Tablespace 생성 및 조회하기 SQL> create tablespace haksa 2 datafile '/app/oracle/oradata/testdb/haksa01.dbf' size 1m ; Tablespace created. SQL> select tablespace_name, status, contents, extent_management, 2 segment_space_management 3 from dba_tablespaces ; TABLESPACE_NAME STATUS CONTENTS EXTENT_MANAGEMENT SEGMENT_SPAC ------------------- -------- -------- -------------------- -------------- SYSTEM ONLINE PERMANENT LOCAL MANUAL SYSAUX ONLINE PERMANENT LOCAL AUTO UNDOTBS1 ONLINE UNDO LOCAL MANUAL TEMP ONLINE TEMPORARY LOCAL MANUAL USERS ONLINE PERMANENT LOCAL AUTO EXAMPLE ONLINE PERMANENT LOCAL AUTO HAKSA ONLINE PERMANENT LOCAL AUTO 7 rows selected. SQL> select tablespace_name, bytes/1024/1024 MB , file_name 2 from dba_data_files ; TABLESPACE_NAME MB FILE_NAME -------------------- ---- ---------------------------------------- EXAMPLE 346 /app/oracle/oradata/testdb/example01.dbf USERS 8 /app/oracle/oradata/testdb/users01.dbf UNDOTBS1 90 /app/oracle/oradata/testdb/undotbs01.dbf SYSAUX 570 /app/oracle/oradata/testdb/sysaux01.dbf SYSTEM 710 /app/oracle/oradata/testdb/system01.dbf HAKSA 1 /app/oracle/oradata/testdb/haksa01.dbf 6 rows selected. 실습 2. 각 Data file 의 실제 사용량 확인하는 방법 SQL> set line 200; SQL> col file# for 999 ; SQL> col ts_name for a10 ; SQL> col total_blocks for 9999999 ; SQL> col used_blocks for 9999999 ; SQL> col pct_used for a10 ; SQL> select distinct d.file_id file#, 2 d.tablespace_name ts_name, 3 d.bytes /1024/1024 MB, 4 d.bytes /8192 total_blocks, 5 sum(e.blocks) used_blocks, 6 to_char(nvl(round(sum(e.blocks)/(d.bytes/8192), 4),0) *100, '09.99') || '%' pct_used 7 from dba_extents e, dba_data_files d 8 where d.file_id = e.file_id(+) 9 group by d.file_id , d.tablespace_name , d.bytes 10 order by 1,2 ; FILE# TS_NAME MB TOTAL_BLOCKS USED_BLOCKS PCT_USED ----- ---------- ---- ------------ ----------- ---------- 1 SYSTEM 710 90880 89992 99.02% 2 SYSAUX 570 72960 68784 94.28% 3 UNDOTBS1 90 11520 2720 23.61% 4 USERS 8 960 736 76.67% 5 EXAMPLE 346 44240 39568 89.44% 6 HAKSA 1 128 00.00% 6 rows selected. 실습 3. Tablespace 용량 관리 하기 SQL> col tablespace_name for a10 SQL> col file_name for a50 SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files ; TABLESPACE MB FILE_NAME ---------- ---- -------------------------------------------------- EXAMPLE 346 /app/oracle/oradata/testdb/example01.dbf USERS 8 /app/oracle/oradata/testdb/users01.dbf UNDOTBS1 90 /app/oracle/oradata/testdb/undotbs01.dbf SYSAUX 570 /app/oracle/oradata/testdb/sysaux01.dbf SYSTEM 710 /app/oracle/oradata/testdb/system01.dbf HAKSA 1 /app/oracle/oradata/testdb/haksa01.dbf 6 rows selected. ## haksa Tablespace 에 iphak table을 만들어서 일부로 가득 차게해서 장애생기게 만듬 SQL> create table scott.iphak (studno number ) tablespace haksa ; Table created. SQL> begin 2 for i in 1..50000 loop 3 insert into scott.iphak values (i) ; 4 end loop ; 5 commit ; 6 end ; 7 / PL/SQL procedure successfully completed. SQL> / begin * ERROR at line 1: ORA-01653: unable to extend table SCOTT.IPHAK by 8 in tablespace HAKSA <-- 용량부족으로 에러발생 ORA-06512: at line 3 조치방법 : 1. Data file 을 하나 더 추가해 주는 방법 2. Data file 을 크게 늘려주는 방법 (수동증가와 자동증가가 있다) 조치방법 1. 수동으로 Tablespace 에 Data file 을 추가하는 방법 SQL> alter tablespace haksa 2 add datafile '/app/oracle/oradata/testdb/haksa02.dbf' size 20M ; <-- data file 추가 Tablespace altered. SQL> select tablespace_name, bytes/1024/1024 MB , file_name from dba_data_files ; TABLESPACE MB FILE_NAME ---------- ---- -------------------------------------------------- EXAMPLE 346 /app/oracle/oradata/testdb/example01.dbf USERS 8 /app/oracle/oradata/testdb/users01.dbf UNDOTBS1 90 /app/oracle/oradata/testdb/undotbs01.dbf SYSAUX 570 /app/oracle/oradata/testdb/sysaux01.dbf SYSTEM 710 /app/oracle/oradata/testdb/system01.dbf HAKSA 1 /app/oracle/oradata/testdb/haksa01.dbf HAKSA 20 /app/oracle/oradata/testdb/haksa02.dbf 7 rows selected. 조치방법 2. Data file 크기 수동 증가 시키기 SQL> alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf'resize 20M ; <--data file 크기 로증가 Database altered. 조치방법 3. Data file 크기 자동 증가 시키기 SQL> alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' autoextend on ; Database altered. data file 은 자동 증가하게 되며 ORACLE 이 32 비트용일 경우 최대 파일 1개의 크기는 16GB까지 가능하며 ORACLE 이 64비트용일 경우 최대 크기가 32GB 까지 가능 SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files ; TABLESPACE MB FILE_NAME ---------- ---- -------------------------------------------------- EXAMPLE 346 /app/oracle/oradata/testdb/example01.dbf USERS 8 /app/oracle/oradata/testdb/users01.dbf UNDOTBS1 90 /app/oracle/oradata/testdb/undotbs01.dbf SYSAUX 570 /app/oracle/oradata/testdb/sysaux01.dbf SYSTEM 710 /app/oracle/oradata/testdb/system01.dbf HAKSA 20 /app/oracle/oradata/testdb/haksa01.dbf <-- 용량이 증가됨 HAKSA 20 /app/oracle/oradata/testdb/haksa02.dbf 7 rows selected. ## 각 data file 들의 autoextend 유무 확인하기 SQL> set line 200 SQL> col tablespace_name for a10 SQL> col file_name for a50 SQL> select tablespace_name, bytes/1024/1024 MB, file_name, autoextensible "AUTO", online_status 2 from dba_data_files ; TABLESPACE MB FILE_NAME AUTO ONLINE_STATUS ---------- ---- ------------------------------------ ---- ---------------- EXAMPLE 346 /app/oracle/oradata/testdb/example01.dbf YES ONLINE USERS 8 /app/oracle/oradata/testdb/users01.dbf YES ONLINE UNDOTBS1 90 /app/oracle/oradata/testdb/undotbs01.dbf YES ONLINE SYSAUX 570 /app/oracle/oradata/testdb/sysaux01.dbf YES ONLINE SYSTEM 710 /app/oracle/oradata/testdb/system01.dbf YES SYSTEM HAKSA 20 /app/oracle/oradata/testdb/haksa01.dbf YES ONLINE HAKSA 20 /app/oracle/oradata/testdb/haksa02.dbf NO ONLINE 7 rows selected. SQL> alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf'autoextend on ; Database altered. SQL> select tablespace_name,bytes/1024/1024 MB, file_name, autoextensible "AUTO", online_status 2 from dba_data_files ; TABLESPACE MB FILE_NAME AUTO ONLINE_STATUS ---------- ---- ------------------------------------ ------ -------------- EXAMPLE 346 /app/oracle/oradata/testdb/example01.dbf YES ONLINE USERS 8 /app/oracle/oradata/testdb/users01.dbf YES ONLINE UNDOTBS1 90 /app/oracle/oradata/testdb/undotbs01.dbf YES ONLINE SYSAUX 570 /app/oracle/oradata/testdb/sysaux01.dbf YES ONLINE SYSTEM 710 /app/oracle/oradata/testdb/system01.dbf YES SYSTEM HAKSA 20 /app/oracle/oradata/testdb/haksa01.dbf YES ONLINE HAKSA 20 /app/oracle/oradata/testdb/haksa02.dbf NO ONLINE 7 rows selected. SQL> begin 2 for i in 1..500000 loop 3 insert into scott.iphak values (i) ; 4 end loop; 5 commit ; 6 end ; 7 / PL/SQL procedure successfully completed. SQL> / PL/SQL procedure successfully completed. SQL> select tablespace_name,bytes/1024/1024 MB, file_name,autoextensible "AUTO",online_status 2 from dba_data_files; TABLESPACE MB FILE_NAME AUTO ONLINE_STATUS ---------- ---- --------------------------------------- ------- -------------- EXAMPLE 346 /app/oracle/oradata/testdb/example01.dbf YES ONLINE USERS 8 /app/oracle/oradata/testdb/users01.dbf YES ONLINE UNDOTBS1 265 /app/oracle/oradata/testdb/undotbs01.dbf YES ONLINE SYSAUX 570 /app/oracle/oradata/testdb/sysaux01.dbf YES ONLINE SYSTEM 710 /app/oracle/oradata/testdb/system01.dbf YES SYSTEM HAKSA 29 /app/oracle/oradata/testdb/haksa01.dbf YES ONLINE HAKSA 20 /app/oracle/oradata/testdb/haksa02.dbf NO ONLINE 7 rows selected. 실습 4. Tablespace Offline Tablespace를 offline 한다는 것은 더이상 tablespace에 접근을 못한다는 의미로 해당 tablespace만 shutdown 시키는것 특정 tablespace 의 데이터파일의 위치를 이동하거나 장애가 나서 복구할때 유용하게 사용 Offline 하는 3가지 방법 1. Normal Mode - 아무런 문제가 없을때 정상적으로 수행하는 방법 SQL> alter tablespace haksa offline ; Database altered. 2. Temporary Mode - 현재 offline 시키고자 하는 tablespace 의 data file 이 하나라도 이상이 생기게 되었을때 사용 명령어 - offline temporary 3. Immediate Mode - archive log mode 일 경우에만 사용. data file에 장애가 나서 데이터를 내려쓰지 못하는 상황에 tablespace를 offline 해야 할 경우에 사용. archive log mode 에서 사용 SQL> alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' offline ; no archive log mode 에서 사용 -> 노 아카이브 모드에서 오프라인할 경우 recovery 하라는 메세지가 나옴 SQL> alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' offline drop ; data file 이 online 인지 offline 인지 확인하는 방법 SQL> select file#, name, status from v$datafile; FILE# NAME STATUS ----- -------------------------------------- -------- 1 /app/oracle/oradata/testdb/system01.dbf SYSTEM 2 /app/oracle/oradata/testdb/sysaux01.dbf ONLINE 3 /app/oracle/oradata/testdb/undotbs01.dbf ONLINE 4 /app/oracle/oradata/testdb/users01.dbf ONLINE 5 /app/oracle/oradata/testdb/example01.dbf ONLINE 6 /app/oracle/oradata/testdb/haksa01.dbf ONLINE 7 /app/oracle/oradata/testdb/haksa02.dbf ONLINE 7 rows selected. no archive log mode 에서 offline 하고 조회 SQL> alter database datafile '/app/oracle/oradata/testdb/haksa02.dbf' offline drop ; Database altered. SQL> select file# , name , status from v$datafile ; FILE# NAME STATUS ----- ------------------------------------ --------- 1 /app/oracle/oradata/testdb/system01.dbf SYSTEM 2 /app/oracle/oradata/testdb/sysaux01.dbf ONLINE 3 /app/oracle/oradata/testdb/undotbs01.dbf ONLINE 4 /app/oracle/oradata/testdb/users01.dbf ONLINE 5 /app/oracle/oradata/testdb/example01.dbf ONLINE 6 /app/oracle/oradata/testdb/haksa01.dbf ONLINE 7 /app/oracle/oradata/testdb/haksa02.dbf RECOVER 7 rows selected. < 강제로 datafile 을 offline 시키면 위와 같이 복구가 필요한 상태로 변함 > SQL> alter tablespace example offline ; Tablespace altered. SQL> select file# , name , status from v$datafile ; FILE# NAME STATUS ----- ----------------------------------- ---------- 1 /app/oracle/oradata/testdb/system01.dbf SYSTEM 2 /app/oracle/oradata/testdb/sysaux01.dbf ONLINE 3 /app/oracle/oradata/testdb/undotbs01.dbf ONLINE 4 /app/oracle/oradata/testdb/users01.dbf ONLINE 5 /app/oracle/oradata/testdb/example01.dbf OFFLINE 6 /app/oracle/oradata/testdb/haksa01.dbf ONLINE 7 /app/oracle/oradata/testdb/haksa02.dbf RECOVER 7 rows selected. Tablespace 를 offline 하게되면 그 파일들에는 새로운 정보가 저장되지 않는다. 그래서 offline 하고 online을 한다면 반드시 체크포인트를 발생시켜 data file 간의 동기화 작업을 해줘야 한다. SQL> select a.file# , a.ts# , b.name , a.status , a.checkpoint_change# 2 from v$datafile a, v$tablespace b 3 where a.ts# = b.ts# ; from v#datafile a, v$tablespace b FILE# TS# NAME STATUS CHECKPOINT_CHANGE# ----- ---- ------ ------- ------------------ 1 0 SYSTEM SYSTEM 1228512 2 1 SYSAUX ONLINE 1228512 3 2 UNDOTBS1 ONLINE 1228512 4 4 USERS ONLINE 1228512 5 6 EXAMPLE OFFLINE 1229265 <-- (checkpoint scn 이 다른파일과 다른것을 알수 있다 6 7 HAKSA ONLINE 1229084 여기서 online 을 해도 scn 은 여전히 다르다 ) 7 7 HAKSA RECOVER 1229084 7 rows selected. SQL> alter tablespace example online ; Tablespace altered. SQL> select a.file# , a.ts# , b.name , a.status , a.checkpoint_change# 2 from v$datafile a, v$tablespace b 3 where a.ts# = b.ts# ; FILE# TS# NAME STATUS CHECKPOINT_CHANGE# ----- ---------- ------------------- ---------- ------------------ 1 0 SYSTEM SYSTEM 1228512 2 1 SYSAUX ONLINE 1228512 3 2 UNDOTBS1 ONLINE 1228512 4 4 USERS ONLINE 1228512 5 6 EXAMPLE ONLINE 1229323 <-- 여전히 다름 6 7 HAKSA ONLINE 1229084 7 7 HAKSA RECOVER 1229084 7 rows selected. 이 상태에서 data file 을 백업 받는다면 백업파일 자체가 문제가 생기게 되고, 향후 복구에 문제가 될 수 있음 이럴 경우 alter system checkpoint ; 명령어를 사용해서 수동으로 체크포인트를 발생시킨후 모두 동기화 시킨 다음 백업을 받아야 함 SQL> alter system checkpoint ; <-- 강제로 checkpoint 발생시킴 System altered. SQL> alter tablespace haksa offline ; alter tablespace haksa offline * ERROR at line 1: ORA-01191: file 7 is already offline - cannot do a normal offline <-- 이미 하나가 offline 상태여서 에러남 ORA-01110: data file 7: '/app/oracle/oradata/testdb/haksa02.dbf' SQL> alter tablespace haksa offline temporary ; <-- temporary offline 을 사용해서 offline 시켜줌 Tablespace altered. SQL> recover tablespace haksa ; <-- recovery 필요한 haksa 파일을 복구 Media recovery complete. SQL> alter tablespace haksa online ; <-- haksa 파일 online 시켜줌 Tablespace altered. SQL> select a.file# , a.ts# , b.name , a.status , a.checkpoint_change# 2 from v$datafile a, v$tablespace b 3 where a.ts# = b.ts# ; from v$data_file a, v$tablespace b FILE# TS# NAME STATUS CHECKPOINT_CHANGE# ----- --- ------- -------- --------------------- 1 0 SYSTEM SYSTEM 1229584 2 1 SYSAUX ONLINE 1229584 3 2 UNDOTBS1 ONLINE 1229584 4 4 USERS ONLINE 1229584 5 6 EXAMPLE ONLINE 1229584 6 7 HAKSA ONLINE 1229630 <-- 복구했지만 scn이 다름 7 7 HAKSA ONLINE 1229630 <-- 복구했지만 scn이 다름 7 rows selected. SQL> select a.file# , a.ts# , b.name , a.status , a.checkpoint_change# 2 from v$datafile a, v$tablespace b 3* where a.ts# = b.ts# FILE# TS# NAME STATUS CHECKPOINT_CHANGE# ----- ---------- -------------------------------------------------- ------- 1 0 SYSTEM SYSTEM 1229674 2 1 SYSAUX ONLINE 1229674 3 2 UNDOTBS1 ONLINE 1229674 4 4 USERS ONLINE 1229674 5 6 EXAMPLE ONLINE 1229674 6 7 HAKSA ONLINE 1229674 <-- SCN 같아짐 7 7 HAKSA ONLINE 1229674 <-- SCN 같아짐 7 rows selected. 실습 5. Data file 이동시키는 작업 data file 을 이동시킬 때 순서가 가장 중요함. data file 이 사용중일 때 절대 이동시키거나 복사를 하면 안된다. offline 과 shutdown 을 활용해서 사용안함으로 만들자 1. Offline 되는 Tablespace 의 Data file 이동하기 <순서 요약> 1. 해당 Tablespace offline <- 반드시 해야함!!! 2. Data file 을 대상 위치로 복사 3. 컨트롤 파일 내의 해당 Data file 위치 변경 4. 해당 Tablespace online haksa Tablespace 의 Data file haksa01.dbf 를 이동 /app/oracle/disk1/haksa01.dbf 로 이동시켜보겠음 먼저 이동시킬 디렉토리를 생성 SQL>!mkdir /app/oracle/disk1 SQL> alter tablespace haksa offline ; <-- 변경전에 반드시 offline SQL> !cp /app/oracle/oradata/testdb/haksa01.dbf /app/oracle/disk1/ SQL>select name from v$datafile ; <-- 컨트롤 파일에 기록된 data file 의 위치확인 NAME --------------------------------------------- /app/oracle/oradata/testdb/system01.dbf /app/oracle/oradata/testdb/sysaux01.dbf /app/oracle/oradata/testdb/undotbs01.dbf /app/oracle/oradata/testdb/users01.dbf /app/oracle/oradata/testdb/example01.dbf /app/oracle/oradata/testdb/haksa01.dbf <-- 요놈 /app/oracle/oradata/testdb/haksa02.dbf /app/oracle/oradata/testdb/undo01.dbf SQL>alter tablespace haksa rename 2 datafile '/app/oracle/oradata/testdb/haksa01.dbf' <-- 원래 있던 경로와 파일명 3 to '/app/oracle/disk1/haksa01.dbf' ; <-- 바뀐 경로와 파일명 SQL>select name from v$datafile ; NAME --------------------------------------------- /data2/disk3/system01.dbf /data2/disk3/sysaux01.dbf /data2/disk4/undotbs01.dbf /data2/disk5/users01.dbf /data2/disk4/example01.dbf /app/oracle/disk1/haksa01.dbf <-- 바뀐것을 확인할 수 있다 /app/oracle/oradata/testdb/haksa02.dbf /app/oracle/oradata/testdb/undo01.dbf SQL>alter tablespace online <-- 다시 online 해주면 완료 2. Offline 안되는 Tablespace 의 Data file 이동하기 Offline 안되는 Tablespace 3가지 - system tablespace - undo tablespace - default tablespace 위 3가지를 이동할 때는 offline 이 안되기 때문에 db를 종료하고 작업을 해야함 <순서요약> 1. DB를 종료 2. MOUNT 상태로 시작 3. Data file 을 복사 4. 컨트롤 파일의 내용을 변경 5. DB OPEN system01.dbf 파일을 /app/oracle/disk3/system01.dbf 로 이동시켜보겠음 SQL> shutdown immediate ; SQL> startup mount ; SQL> !mkdir /app/oracle/disk3 SQL> !cp /app/oracle/oradata/testdb/system01.dbf /app/oracle/disk3/ SQL> select name from v$datafile ; NAME --------------------------------------------- /app/oracle/oradata/testdb/system01.dbf <-- 요놈 /app/oracle/oradata/testdb/sysaux01.dbf /app/oracle/oradata/testdb/undotbs01.dbf /app/oracle/oradata/testdb/users01.dbf /app/oracle/oradata/testdb/example01.dbf /app/oracle/disk3/haksa01.dbf /app/oracle/oradata/testdb/haksa02.dbf /app/oracle/oradata/testdb/undo01.dbf SQL> alter database rename 2 file '/app/oracle/oradata/testdb/system01.dbf' <--변경전 경로와 이름 3 to '/app/oracle/disk3/system01.dbf' ; <--변경후 경로와 이름 SQL> select name from v$datafile ; NAME --------------------------------------------- /app/oracle/disk3/system01.dbf <-- 변경 되었다 /app/oracle/oradata/testdb/sysaux01.dbf /app/oracle/oradata/testdb/undotbs01.dbf /app/oracle/oradata/testdb/users01.dbf /app/oracle/oradata/testdb/example01.dbf /app/oracle/disk3/haksa01.dbf /app/oracle/oradata/testdb/haksa02.dbf /app/oracle/oradata/testdb/undo01.dbf SQL> alter database open ; DB MOUNT상태면 모든 Data file 및 Redo log 파일도 이동시킬 수 있다. 3. Redo log file 이동하기 Redo log file 은 offline 안되기 때문에 반드시 사용을 안하게 만들기 위해 D.B를 MOUNT 상태로 두고 작업 1. 현재상태 확인 SQL> col member for a50 SQL> select a.group# , a.member , b.bytes/1024/1024 MB , b.archived , b.status 2 from v$logfile a , v$log b 3 where a.group#=b.group# 4 order by 1,2 ; GROUP# MEMBER MB ARC STATUS ------ --------------------------------------------- ---- --- -------- 1 /app/oracle/oradata/testdb/redo01.log 50 NO INACTIVE 2 /app/oracle/oradata/testdb/redo02.log 50 NO CURRENT 3 /app/oracle/oradata/testdb/redo03.log 50 NO INACTIVE 이 redo log 파일들을 /app/oracle/disk4/redo01_a.log , redo02_a.log , redo03_a.log /app/oracle/disk5/redo01_b.log , redo02_b.log , redo03_b.log 로 이동 SQL> select status from v$instance <-- 시작전에 반드시 Instance 상태부터 확인 STATUS ------- OPEN <-- redo log 파일을 옮기려면 반드시 mount 상태여야 한다 SQL> shutdown immediate ; SQL> startup mount ; SQL> !mkdir /app/oracle/disk4 SQL> !mkdir /app/oracle/disk5 SQL>!cp /app/oracle/oradata/testdb/redo01.log /app/oracle/disk4/redo01_a.log <-- 이름 바꾸며 복사 SQL>!cp /app/oracle/oradata/testdb/redo02.log /app/oracle/disk4/redo03_a.log SQL>!cp /app/oracle/oradata/testdb/redo03.log /app/oracle/disk4/redo03_a.log SQL> alter database rename 2 file '/app/oracle/oradata/testdb/redo01.log' <--변경 전 이름과 위치 3 to '/app/oracle/disk4/redo01_a.log' ; <-- 변경 후 이름과 위치 SQL> alter database rename 2 file '/app/oracle/oradata/testdb/redo02.log' <--변경 전 이름과 위치 3 to '/app/oracle/disk4/redo02_a.log' ; <-- 변경 후 이름과 위치 SQL> alter database rename 2 file '/app/oracle/oradata/testdb/redo03.log' <--변경 전 이름과 위치 3 to '/app/oracle/disk4/redo03_a.log' ; <-- 변경 후 이름과 위치 SQL> select member from v$logfile ; MEMBER --------------------------------------- /app/oracle/disk4/redo01_a.log <-- 바뀌었음 /app/oracle/disk4/redo02_a.log <-- 바뀌었음 /app/oracle/disk4/redo03_a.log <-- 바뀌었음 SQL> alter database add logfile member 2 '/app/oracle/disk5/redo01_b.log' to group 1 , 3 '/app/oracle/disk5/redo02_b.log' to group 2 , 4 '/app/oracle/disk5/redo03_b.log' to group 3 ; SQL> select member from v$logfile ; MEMBER --------------------------------------- /app/oracle/disk4/redo01_a.log /app/oracle/disk4/redo02_a.log /app/oracle/disk4/redo03_a.log /app/oracle/disk5/redo01_b.log <-- 추가되었음 /app/oracle/disk5/redo02_b.log <-- 추가되었음 /app/oracle/disk5/redo03_b.log <-- 추가되었음 SQL> alter database open ; 실습 6 . Tablespace 삭제하기 SQL> drop tablespace haksa ; <-- table이 하나라도 있으면 그냥 안지워 진다 SQL> drop tablespace haksa including contents and datafiles ; <-- 옵션을 줘서 삭제해야함 drop 은 가급적 사용하지 않음 ----------------------------------------------------------- [1. SQL, SQL PLUS] ----------------------------------------------------------- ● 기본 문법 SELECT empno, ename, job FROM emp WHERE job='MANAGER'; // 기본 셀렉트 문 SELECT empno, TO_CHAR(sal*12, '999,999,999,999') "ANNUAL SALARY" FROM emp; // 연봉 정보를 세단위씩 끊어서 표시 INSERT INTO emp (empno, ename, job) VALUES (1200, 'JAMES', 'CLERK'); // 기본 인서트 문 UPDATE emp SET sal=sal*1.1, comm=100 WHERE dept=20; // 기본 업데이트 문 DELETE FROM emp WHERE comm<1000; // 기본 딜리트 문 COMMIT; // INSERT, UPDATE, DELETE 사용 이후에 DB에 반영함 ROLLBACK; // 방금 작업한 INSERT, UPDATE, DELETE의 작업을 취소함 ● 기본적 테이블 생성 CREATE TABLE emp_family ( empno NUMBER(4) NOT NULL, fname VARCHAR2(10) NOT NULL, relation VARCHAR2(10) NOT NULL, age NUMBER(3), job VARCHAR2(20), CONSTRAINT emp_family_pk PRIMARY KEY (empno, fname) ); ● 기본 명령 DESC emp // 테이블의 구조를 디스플레이 R // 가장 최근에 실행했던 SQL 문장을 실행 ED // SQL 문장 편집 C // 특정 문자열 변경 C / WHERE empno=1011 / WHERE ename='JAMES' A // 현재 라인의 끝에 문자열 추가 A / WHERE empno=1011 CLEAR BUFFER // 버퍼에 저장된 SQL 문장 삭제 GET // SQL 문장을 버퍼로 읽어들여옴 GET C:\실습\work.sql START(또는 @) // 외부 파일에 있는 SQL 문장를 바로 실행 STA C:\실습\work.sql SPOOL // 작업을 로그에 기록 SPOOL C:\실습\work.txt 시작 SPOOL ON / 종료 SPOOL OFF [2. 데이터 제한 및 정렬] ● 문자 스트링 SELECT empno, ename, job, sal FROM emp WHERE ename='JAMES'; // 대소문자 구분 'JAMES'와 'james'를 다르게 인식 SELECT empno, ename, job, sal FROM emp WHERE hiredate='1982/12/09'; // 날짜 형식에 따라 다르게 표기 ● BETWEEN SELECT empno, ename, job, sal FROM emp WHERE sal BETWEEN 1300 AND 1500; // 급여가 1300과 1500 사이인 사람. BETWEEN에서는 두 개의 비교값중에 작은 값을 앞에 쓰고 큰 값을 뒤에 쓴다 ● IN SELECT * FROM emp WHERE empno IN (7521, 7839, 7934); // = SELECT * FROM emp WHERE empno=7521 OR empno=7839 OR empno=7934; ● LIKE SELECT empno, ename, job, sal FROM emp WHERE ename LIKE 'J%'; // ename 컬럼에서 J로 시작하는 모든 데이터 표시 '%LA%' -> 가운데에 LA가 들어가는 모든 것 '_A%' -> 첫번째는 모르겠고 두번째가 A인 모든 것 'A_C' -> 중간에 한 글자를 모를 때 'A\_C' ESCAPE '\' -> \ 이후의 _는 하나의 값으로 인식하라 ● IS NULL SELECT empno, ename, job, sal FROM emp WHERE mgr IS NULL; // mgr 컬럼에서 NULL인 것만 표시 SELECT empno, ename, job, sal FROM emp WHERE mgr IS NOT NULL; // mgr 컬럼에서 NULL이 아닌 것만 표시 ● 연산자 우선 순위 비교 연산자, NOT, NULL, OR 순 1981년에 입사한 사원 중에 부서가 10 또는 20인 사원 찾기 SELECT * FROM emp WHERE hiredate LIKE '81%' AND (deptno=10 OR deptno=20); ● ORDER BY SELECT empno, ename, job, sal FROM emp ORDER BY ASC; // 어센딩으로 소트(어센딩과 디센딩을 명시하지 않으면 어센딩이 디폴트값) SELECT empno, ename, job, sal FROM emp ORDER BY DESC; // 디센딩으로 소트 SELECT empno, ename, job, sal FROM emp ORDER BY hiredate DESC, ename; ● DISTINCT 행의 중복된 값 제거 SELECT DISTINCT deptno FROM emp; [3. 함수] ● 함수의 종류 단일행 함수 - 하나의 행 당 하나의 결과 값을 반환하는 함수(문자함수, 숫자함수, 날짜함수, 변환함수) 복수형 함수 - 여러 개의 행 당 하나의 결과 값을 반환하는 함수(COUNT, SUM, MAX/MIN, AVG) [4. 조인] ● Equijoin SELECT emp.empno, emp.ename, emp.deptno, dept.dname FROM emp, dept WHERE emp.deptno=dept.deptno AND sal>2000; // 컬럼 앞에 테이블명을 적어서 같은 컬럼명이 중복될 수 있는 상황을 피한다 ● 테이블 엘리어스 SELECT emp.empno, emp.ename, emp.deptno, dept.dname FROM emp e, dept d WHERE emp.deptno=dept.deptno ; ● NON-Equijoin SELECT e.empno, e.ename, e.sal, s.grade, FROM emp e, salgrade s WHERE e.sal BETWEEN losal and hisal; ● OUTER join SELECT emp.empno, emp.ename, emp.deptno, dept.dname FROM emp e, dept d WHERE emp.deptno(+)=dept.deptno ; // 정보가 부족한 쪽에 (+)를 붙인다 ● SELF join SELECT w.ename worker, m.ename manager FROM emp w, emp m WHERE w.mgr=m.empno; [5. 서브 쿼리] ● JONES 보다 급여를 많이 받는 사원은? SELECT sal FROM emp WHERE ename='JONES'; SELECT empno, ename, sal FROM emp WHERE sal>2975; SELECT empno, ename, sal FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='JONES'); ● 그룹 함수를 사용한 서브 쿼리 SELECT empno, ename, sal FROM emp WHERE sal = (SELECT MIN(sal) FROM emp); ● HAVING 절에서의 서브 쿼리 SELECT job, MAX(sal) FROM emp GROUP BY job HAVING MAX(sal) > (SELECT MAX(sal) FROM emp WHERE job='MANAGER'); ● PAIRWISE 컬럼 비교 SELECT empno, ename, deptno, sal, comm FROM emp WHERE (sal, NVL(comm, -1)) IN (SELECT sal, NVL(comm, -1) FROM emp WHERE deptno=30); ● NON-PAIRWISE 컬럼 비교 SELECT empno, ename, deptno, sal, comm FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno=30) AND NVL(comm, -1) IN (SELECT NVL(comm, -1) FROM emp WHERE deptno=30); ● FROM 절에서의 서브 쿼리 SELECT a.empno, a.ename, a.sal, a.deptno, b.avg FROM emp a, (SELECT deptno, ROUND(AVG(sal)) avg FROM emp GROUP BY deptno) b WHERE a.deptno = b.deptno AND a.sal > b.avg; ● 급여를 많이 받는 사원 순으로 앞에 랭킹을 붙여서 조회하시오 SELECT rownum, empno, ename, sal FROM (SELECT empno, ename, sal from emp order by sal desc); ● 급여를 많이 받는 사원 순으로 6위부터 10위까지 앞에 랭킹을 붙여서 조회하시오 SELECT ranking, empno, ename, sal FROM (SELECT rownum, empno, ename, sal FROM (SELECT empno, ename, sal from emp order by sal desc)) WHERE ranking BETWEEN 6 and 10;