[관리] 오라클 DBA 기술 노트
Oracle Administraton Technical Documents ( 오라클 DBA 기술 노트 )
================================================================================================
1. DBMS = database(data file & control file & redo log file) +
instance(memory & background processes)
================================================================================================
2. Oracle Architecture Component
================================================================================================
* Oracle Instance 확인 : v$instance
SQL> select instance_name from v$instance;
INSTANCE_NAME
—————-
IBM
================================================================================================
* datafile들의 경로 및 정보 : v$datafile
SQL> select name from v$datafile;
NAME
——————————————————————————–
/oracle/ora_data/system/system01.dbf
/oracle/ora_data/data/tools01.dbf
/oracle/ora_data/data/rbs01.dbf
/oracle/ora_data/data/temp01.dbf
/oracle/ora_data/data/users01.dbf
================================================================================================
* control file의 경로 및 정보 : v$controlfile;
SQL> select name from v$controlfile;
NAME
——————————————————————————–
/oracle/ora_data/contr1/ora_control1
/oracle/ora_data/contr2/ora_control2
================================================================================================
* logfile의 경로 및 정보 : v$logfile
SQL> select member from v$logfile;
MEMBER
——————————————————————————–
/oracle/ora_data/redolog_a/redo1a.log
/oracle/ora_data/redolog_b/redo1b.log
/oracle/ora_data/redolog_a/redo2a.log
/oracle/ora_data/redolog_b/redo2b.log
/oracle/ora_data/redolog_a/redo3a.log
/oracle/ora_data/redolog_b/redo3b.log
================================================================================================
* System Global Area 내용을 조회
SQL> select * from v$sga;
NAME VALUE
——————– ———-
Fixed Size 108588
Variable Size 27631616
Database Buffers 2252800
Redo Buffers 77824
SQL> show sga
Total System Global Area 30070828 bytes
Fixed Size 108588 bytes
Variable Size 27631616 bytes
Database Buffers 2252800 bytes
Redo Buffers 77824 bytes
================================================================================================
* 현재 수행중인 background process들을 확인
SQL> select paddr,name,description from v$bgprocess where paddr>’00’;
PADDR NAME DESCRIPTION
—————- —– —————————————————————-
070000000139ABC0 PMON process cleanup
070000000139AFD0 DBW0 db writer process 0
070000000139B3E0 LGWR Redo etc.
070000000139B7F0 CKPT checkpoint
070000000139BC00 SMON System Monitor Process
070000000139C010 RECO distributed recovery
SQL> !ps -ef|grep ora|grep
oracle 25148 1 0 19:25:34 – 0:00 ora_reco_IBM
oracle 60576 1 0 19:25:34 – 0:00 ora_smon_IBM
oracle 60782 1 0 19:25:34 – 0:00 ora_pmon_IBM
oracle 70166 1 0 19:25:34 – 0:00 ora_lgwr_IBM
oracle 72248 1 0 19:25:34 – 0:00 ora_ckpt_IBM
oracle 84918 1 0 19:25:34 – 0:00 ora_dbw0_IBM
================================================================================================
* 초기화 파라미터 파일 : init.ora
================================================================================================
* database log 모드 확인
SQL> connect internal
Connected.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /oracle/app/oracle/product/8.1.6/dbs/arch
Oldest online log sequence 20
Current log sequence 22
SQL> select log_mode from v$database;
LOG_MODE
————
NOARCHIVELOG
================================================================================================
3. Managing an Oracle Instance
================================================================================================
단계별 :
shutdown : oracle이 내려가 있는 상태
nomount : instance started(SGA, B.G process를 시작 init.ora에서 읽어서)
alert, trace file open
– 이 단계에서 할 수 있는 것은
a. db creation
– 이 상태에서도 볼수있는 view
v$parameter
v$dga
v$option
v$process
v$session
v$version
v$instance
mount : control file opened for this instance
– 이 단계에서 할 수 있는 것은 control file의 내용을 변경하는것
a. archivelog mode로 변환
b. data file/redo log file rename시
c. recovery시
– SQL>alter database open read only;
로 하게되면 data file에 writing을 허용 안함.
open : control file에 기술된 모든 files open
================================================================================================
* parameter 변경 종류
a. init.ora 에서 변경
b. alter session set ~
c. alter system set ~ => shutdown 될때까지 변경된것 유효
alter system deffered set ~ => 현재 session에서만 변경된것 유효
================================================================================================
* 특정 session 죽이기
SQL> select sid, serial#,username,status from v$session; => (특정 user는 where username=’SCOTT’로)
SID SERIAL# USERNAME STATUS
———- ———- —————————— ——–
1 1 ACTIVE
2 1 ACTIVE
3 1 ACTIVE
4 1 ACTIVE
5 1 ACTIVE
6 1 ACTIVE
7 1 SYS ACTIVE
SQL> alter system kill session ‘7,3’ — 7은 sid, 3은 serial#
================================================================================================
* alert file 과 trace file
– alert file은 꼭 1개, 중요한사건,시간순으로 (startup,shutdown,recovery)
– trace file은 여러개 가능, background process는 background_dump_dest에 생기고 server process는
user_dump_dest에 생성된다.
================================================================================================
4. Creating a Database
================================================================================================
* Create a Database Manually
a. OS Environment setting
.profile에 ORACLE_HOME,ORACLE_SID,ORA_NLS33,PATH,(ORACLE_BASE) 등을 편집한다.
ex)
DISPLAY=swsvrctr:0.0
ORACLE_HOME=/oracle/app/oracle/product/8.1.7
PATH=$ORACLE_PATH/bin:/usr/ccs/bin:$PATH
NLS_LANG=AMERICAN_AMERICA.KO16KSC5601
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
ORACLE_SID=IBM
b. init.ora file을 copy하고 편집한다.
file
db_name=KYS
control_files = (/home/oracle/data02/control/control01.ctl,/home/oracle/data02/control/control02.ctl)
db_block_size = 8192
기본적으로 위 두개 parameter외에
rollback_segments=(rbs1,rbs2,..) =>나중에 rollback segment생성후 DB start시 Online되는 rbs지정
background_dump_dest=/home/oracle/data02/bdump
user_dump_dest=/home/oracle/data02/udump
core_dump_dest=/home/oracle/data02/cdump
c. Starting the Instance
SQL> startup nomount
SQL> startup nomount pfile=initKYS.ora
SQL> create database KYS
2 maxlogfiles 5
3 maxlogmembers 5
4 maxdatafiles 100
5 maxloghistory 100
6 logfile
7 group 1 (‘/home/oracle/data02/redolog/log1a.rdo’,’/home/oracle/data02/redolog2/log1b.rdo’) size 1m,
8 group 2 (‘/home/oracle/data02/redolog/log2a.rdo’,’/home/oracle/data02/redolog2/log2b.rdo’) size 1m
9 datafile
10 ‘/home/oracle/data02/data/system01.dbf’ size 50m autoextend on
11 character set “KO16KSC5601”;
일단 여기까지 database는 생성이 되었다.
이후부터는 추가적인 작업이다.
d. 추가 system rollback segment 생성
SQL> create rollback segment r0 tablespace system
2 storage (initial 16k next 16k minextents 2 maxextents 10);
e. rollback sement online
SQL> alter rollback segment r0 online;
f. rollback segment tablespace 생성 & datafile 저장위치, 크기 및 초기값 지정
SQL> create tablespace rbs
2 datafile ‘/home/oracle/data02/data/rbs01.dbf’ size 300m
3 default storage(
4 initial 4M
5 next 4M
6 pctincrease 0
7 minextents 10
8 maxextents unlimited);
g. rollback segment 생성
SQL> create rollback segment r01 tablespace rbs
2 storage (minextents 10 optimal 40M);
SQL> create rollback segment r02 tablespace rbs
2 storage (minextents 10 optimal 40M);
SQL> create rollback segment r03 tablespace rbs
2 storage (minextents 10 optimal 40M);
SQL> create rollback segment r04 tablespace rbs
2 storage (minextents 10 optimal 40M);
h. rollback segment online
SQL> alter rollback segment r01 online;
SQL> alter rollback segment r02 online;
SQL> alter rollback segment r03 online;
SQL> alter rollback segment r04 online;
i. 추가 system rollback segment off-line 및 삭제
SQL> alter rollback segment r0 offline;
SQL> drop rollback segment r0;
j. sorting 작업시 필요한 temporary tablespace 생성 & datafile 저장 위치, 크기 및 초기값 지정
SQL> create tablespace temp
2 datafile ‘/home/oracle/data02/data/temp01.dbf’ size 300 temporary
3 default storage(
4 initial 4M
5 next 4M
6 maxextents unlimited
7 pctincrease 0);
k. 추가 tablespace 생성 & data file 저장 위치 및 크기 지정
SQL> create tablespace tools
2 datafile ‘/home/oracle/data02/data/tools.dbf’ size 50m
3 default storage(
4 maxextents 505
5 pctincrease 0);
SQL> create tablespace users
2 datafile ‘/home/oracle/data02/data/user01.dbf’ size 30M
3 default storage(
4 maxextents 505
5 pctincrease 0);
l. 작업 환경에서 추가적으로 필요한 tablespace는 위의 방법으로 생성한다.
================================================================================================
5. Data Dictionary and Standard Package
================================================================================================
* database 생성후 돌려줘야 할 script
$ORACLE_HOME/rdbms/admin/catalog.sql ==> dictionary views, export utility views 생성
$ORACLE_HOME/rdbms/admin/catproc.sql ==> procedures, functions 생성
$ORACLE_HOME/rdbms/admin/catdbsyn.sql ==> synonyms 생성
================================================================================================
* Dictionary list 확인
SQL> col table_name format a30
SQL> col comments format a45
SQL> set pages 800
SQL> spool dictionary.lst
SQL> select * from dictionary order by 1 ==> 전체 dictionary의 list를 볼 수 있다.
SQL> spool off
SQL> ed sictionary.lst
SQL> select * from dictionary where table_name like ‘%TABLE%’; ==> table 관련 dictionary
SQL> select * from dictionary where table_name like ‘%INDEX%’; ==> index 관련 dictionary
================================================================================================
* 유용한 dictionary
TABLE_NAME COMMENTS
—————————— ———————————————
DBA_USERS Information about all users of the database
DBA_TABLESPACES Description of all tablespaces
DBA_DATA_FILES Information about database data files
DBA_FREE_SPACE Free extents in all tablespaces
DBA_OBJECTS All objects in the database
DBA_SEGMENTS Storage allocated for all database segments
DBA_ROLLBACK_SEGS Description of rollback segments
DBA_EXTENTS Extents comprising all segments in the database
DBA_TABLES Description of all relational tables in the d
atabase
DBA_INDEXES Description for all indexes in the database
DBA_VIEWS Description of all views in the database
DBA_TRIGGERS All triggers in the database
DBA_SOURCE Source of all stored objects in the database
================================================================================================
* sample Query
SQL> select username,default_tablespace,temporary_tablespace from dba_users;
SQL> select tablespace_name,bytes,file_name from dba_data_files;
SQL> select tablespace_name,count(*),sum(bytes) from dba_free_space
2 group by tablespace_name;
================================================================================================
6. Maintiaining the Contorol File
================================================================================================
* Control File 리스트 조회
SQL> select name from v$controlfile;
NAME
——————————————————————————–
/home/oracle/data01/oradata/IBM/control01.ctl
/home/oracle/data01/oradata/IBM/control02.ctl
/home/oracle/data01/oradata/IBM/control03.ctl
================================================================================================
* Control File 을 하나 추가해보자
a. database shutdown
SQL> shutdown immediate
b. control file 복사(os상 물리적인 복사)
/home/oracle/data01/oradata/IBM> cp control03.ctl control04.ctl ==> 실제는 다른 disk로 복사해야함
문제발생을 대비해 분리하는것임.
c. Parameter File 편집
control_files = (“/home/oracle/data01/oradata/IBM/control01.ctl”,
“/home/oracle/data01/oradata/IBM/control02.ctl”,
“/home/oracle/data01/oradata/IBM/control03.ctl”,
“/home/oracle/data01/oradata/IBM/control04,ctl”)
d. database startup & 확인
SQL> startup
SQL> select name from v$controlfile;
NAME
——————————————————————————–
/home/oracle/data01/oradata/IBM/control01.ctl
/home/oracle/data01/oradata/IBM/control02.ctl
/home/oracle/data01/oradata/IBM/control03.ctl
/home/oracle/data01/oradata/IBM/control04.ctl ==> 하나 더 추가되었지요…(실제는 다른disk로)
================================================================================================
7. Multiplexing Redo Log Files
================================================================================================
* Redo Log File 리스트 조회
SQL> select group#,sequence#,bytes,members,status from v$log;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
———- ———- ———- ———- ——————————–
1 862 512000 1 CURRENT
2 860 512000 1 INACTIVE
3 861 512000 1 INACTIVE
SQL> select * from v$logfile;
GROUP# STATUS MEMBER
———- ————– ————————————————–
1 /home/oracle/data01/oradata/IBM/redo03.log
2 /home/oracle/data01/oradata/IBM/redo02.log
3 /home/oracle/data01/oradata/IBM/redo01.log
================================================================================================
* Log Group 추가(기존 로그 파일과 동일한 사이즈로)
SQL> alter database add logfile
2 ‘/home/oracle/data01/oradata/IBM/redo04.log’ size 200k;
SQL> select group#,sequence#,bytes,members,status from v$log;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
———- ———- ———- ———- ——————————–
1 862 512000 1 CURRENT
2 860 512000 1 INACTIVE
3 861 512000 1 INACTIVE
4 0 204800 1 UNUSED
SQL> select * from v$logfile;
GROUP# STATUS MEMBER
———- ————– ————————————————–
1 /home/oracle/data01/oradata/IBM/redo03.log
2 /home/oracle/data01/oradata/IBM/redo02.log
3 /home/oracle/data01/oradata/IBM/redo01.log
4 /home/oracle/data01/oradata/IBM/redo04.log
================================================================================================
* Log Group 별 멤버 파일 추가 ==> backup 시 risk줄이기 위해 실제는 다른 disk에 해야함.
SQL> alter database add logfile member
2 ‘/home/oracle/data01/oradata/IBM/redo01b.log’ to group 1,
3 ‘/home/oracle/data01/oradata/IBM/redo02b.log’ to group 2,
4 ‘/home/oracle/data01/oradata/IBM/redo03b.log’ to group 3,
5 ‘/home/oracle/data01/oradata/IBM/redo04b.log’ to group 4;
================================================================================================
* 확인
SQL> !ls /home/oracle/data01/oradata/IBM/*.log
/home/oracle/data01/oradata/IBM/redo01.log /home/oracle/data01/oradata/IBM/redo03.log
/home/oracle/data01/oradata/IBM/redo01b.log /home/oracle/data01/oradata/IBM/redo03b.log
/home/oracle/data01/oradata/IBM/redo02.log /home/oracle/data01/oradata/IBM/redo04.log
/home/oracle/data01/oradata/IBM/redo02b.log /home/oracle/data01/oradata/IBM/redo04b.log
SQL> select group#,sequence#,bytes,members,status from v$log;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
———- ———- ———- ———- ——————————–
1 862 512000 2 CURRENT
2 860 512000 2 INACTIVE
3 861 512000 2 INACTIVE
4 0 204800 2 UNUSED ==> 아직 한번도 사용되지 않음
SQL> select * from v$logfile;
GROUP# STATUS MEMBER
———- ————– ————————————————–
1 /home/oracle/data01/oradata/IBM/redo03.log
2 /home/oracle/data01/oradata/IBM/redo02.log
3 /home/oracle/data01/oradata/IBM/redo01.log
4 /home/oracle/data01/oradata/IBM/redo04.log
1 INVALID /home/oracle/data01/oradata/IBM/redo01b.log
2 INVALID /home/oracle/data01/oradata/IBM/redo02b.log
3 INVALID /home/oracle/data01/oradata/IBM/redo03b.log
4 INVALID /home/oracle/data01/oradata/IBM/redo04b.log
==> 현재 사용되고 있는 log group 은 group 1이고 나중에 추가한 member들은 invalid 한 상태이다.
강제로 log switch를 일으켜서 valid하게 바꾸자.
SQL> alter system switch logfile;
SQL> select group#,sequence#,bytes,members,status from v$log;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
———- ———- ———- ———- ——————————–
1 862 512000 2 ACTIVE
2 860 512000 2 INACTIVE
3 861 512000 2 INACTIVE
4 863 204800 2 CURRENT ==> unused에서 바뀜.
SQL> select * from v$logfile;
GROUP# STATUS MEMBER
———- ————– ————————————————–
1 /home/oracle/data01/oradata/IBM/redo03.log
2 /home/oracle/data01/oradata/IBM/redo02.log
3 /home/oracle/data01/oradata/IBM/redo01.log
4 /home/oracle/data01/oradata/IBM/redo04.log
1 INVALID /home/oracle/data01/oradata/IBM/redo01b.log
2 INVALID /home/oracle/data01/oradata/IBM/redo02b.log
3 INVALID /home/oracle/data01/oradata/IBM/redo03b.log
4 /home/oracle/data01/oradata/IBM/redo04b.log ==> valid하게 바뀜
================================================================================================
Log Miner
================================================================================================
* Parameter File 의 utl_file_dir 편집
a. 확인
SQL> select name,value from v$parameter
2 where name=’utl_file_dir’;
NAME VALUE
——————– ——————————
utl_file_dir
SQL> !mkdir $ORACLE_HOME/LOG
b. LogMiner사용을 위해 init.ora file 편집
utl_file_dir=/oracle/app/oracle/product/8.1.7/LOG
c. restart
SQL> shutdown immediate
SQL> startup
확인
SQL> select name,value from v$parameter
2 where name=’utl_file_dir’;
NAME VALUE
——————– ——————————
utl_file_dir /oracle/app/oracle/product/8.1.7/LOG ==> LogMiner 준비를 위한 parameter set
d. LogMiner setting – 반드시 트랜잭션의 첫번째 명령이어야 함
SQL> commit;
SQL> exec dbms_logmnr_d.build(‘v817dict.ora’,’/oracle/app/oracle/product/8.1.7/LOG’);
BEGIN dbms_logmnr_d.build(‘v817dict.ora’,’/oracle/app/oracle/product/8.1.7/LOG’); END;
*
ERROR at line 1:
ORA-06532: Subscript outside of limit
ORA-06512: at “SYS.DBMS_LOGMNR_D”, line 793
ORA-06512: at line 1
SQL> !ls $ORACLE_HOME/LOG
SQL> exec dbms_logmnr.add_logfile(‘/home/oracle/data01/oradata/IBM/redo01.log’,DBMS_LOGMNR.NEW);
SQL> exec dbms_logmnr.add_logfile(‘/home/oracle/data01/oradata/IBM/redo02.log’,DBMS_LOGMNR.ADDFILE);
SQL> exec dbms_logmnr.add_logfile(‘/home/oracle/data01/oradata/IBM/redo03.log’,DBMS_LOGMNR.ADDFILE);
SQL> exec dbms_logmnr.add_logfile(‘/home/oracle/data01/oradata/IBM/redo04.log’,DBMS_LOGMNR.ADDFILE);
SQL> exec dbms_logmnr.start_logmnr(‘/oracle/app/oracle/product/8.1.7/LOG/v817dict.ora’);
e. 트랜잭션 수행
SQL> descc scott.dept
SQL> select * from scott.dept;
SQL> insert into scott.dept values(99,’test’,’test’);
SQL> update scott.dept set loc=’TEST’ where deptno=99;
SQL> commit;
f. log miner 정보 분석
SQL> select timestamp,username,sql_redo from v$logmnr_contents
2 where seg_name=’DEPT’;
g. 로그마이닝 종료
SQL> exec dbms_logmnr.end_logmnr;
================================================================================================
8. Managing TableSpace and Data Files
================================================================================================
* tablespace와 datafile 조회
SQL> col tablespace_name format a15
SQL> col file_name format a45
SQL> select tablespace_name,status,contents from dba_tablespaces;
TABLESPACE_NAME STATUS CONTENTS
————— —————— ——————
SYSTEM ONLINE PERMANENT
TOOLS ONLINE PERMANENT
RBS ONLINE PERMANENT
TEMP ONLINE TEMPORARY
USERS ONLINE PERMANENT
INDX ONLINE PERMANENT
DRSYS ONLINE PERMANENT
SQL> select tablespace_name,bytes,file_name from dba_data_files;
TABLESPACE_NAME BYTES FILE_NAME
————— ———- ———————————————
TOOLS 10485760 /home/oracle/data01/oradata/IBM/tools01.dbf
DRSYS 20971520 /home/oracle/data01/oradata/IBM/drsys01.dbf
USERS 20971520 /home/oracle/data01/oradata/IBM/users01.dbf
INDX 20971520 /home/oracle/data01/oradata/IBM/indx01.dbf
RBS 52428800 /home/oracle/data01/oradata/IBM/rbs01.dbf
TEMP 20971520 /home/oracle/data01/oradata/IBM/temp01.dbf
SYSTEM 283115520 /home/oracle/data01/oradata/IBM/system01.dbf
================================================================================================
* tablespace 생성 및 사이즈 변경
SQL> create tablespace data05
2 datafile ‘/home/oracle/data01/oradata/IBM/data05_01.dbf’ size 1m;
Tablespace created.
1m 짜리 datafile 하나를 가진 tablespace data05를 추가하였다. 확인.
SQL> select tablespace_name,bytes,file_name from dba_data_files
2 where tablespace_name=’DATA05′;
TABLESPACE_NAME BYTES FILE_NAME
————— ———- ———————————————
DATA05 1048576 /home/oracle/data01/oradata/IBM/data05_01.dbf
tablespace가 부족할때 늘리는 방법은 두가지가 있다.
하나는 datafile을 추가하는 방법이고 다른하나는 datafile의 size를 늘리는 방법이다.
a. datafile을 하나 추가해보자.
SQL> alter tablespace data05
2 add datafile ‘/home/oracle/data01/oradata/IBM/data05_02.dbf’ size 1m;
SQL> select tablespace_name,bytes,file_name from dba_data_files
2 where tablespace_name=’DATA05′;
TABLESPACE_NAME BYTES FILE_NAME
————— ———- ———————————————
DATA05 1048576 /home/oracle/data01/oradata/IBM/data05_01.dbf
DATA05 1048576 /home/oracle/data01/oradata/IBM/data05_02.dbf
제대로 추가되었다.
b. 그렇다면 하나의 사이즈를 변경해보자.
SQL> alter database datafile
2 ‘/home/oracle/data01/oradata/IBM/data05_02.dbf’ resize 2m;
SQL> select tablespace_name,bytes,file_name from dba_data_files
2 where tablespace_name=’DATA05′;
TABLESPACE_NAME BYTES FILE_NAME
————— ———- ———————————————
DATA05 1048576 /home/oracle/data01/oradata/IBM/data05_01.dbf
DATA05 2097152 /home/oracle/data01/oradata/IBM/data05_02.dbf
2m로 제대로 변경이 되었다.
다시 원상복구
SQL> alter database datafile
2 ‘/home/oracle/data01/oradata/IBM/data05_02.dbf’ resize 1m;
전체를 다시 확인해보자
SQL> select tablespace_name,bytes,file_name from dba_data_files;
TABLESPACE_NAME BYTES FILE_NAME
————— ———- ———————————————
TOOLS 10485760 /home/oracle/data01/oradata/IBM/tools01.dbf
DRSYS 20971520 /home/oracle/data01/oradata/IBM/drsys01.dbf
USERS 20971520 /home/oracle/data01/oradata/IBM/users01.dbf
INDX 20971520 /home/oracle/data01/oradata/IBM/indx01.dbf
RBS 52428800 /home/oracle/data01/oradata/IBM/rbs01.dbf
TEMP 20971520 /home/oracle/data01/oradata/IBM/temp01.dbf
SYSTEM 283115520 /home/oracle/data01/oradata/IBM/system01.dbf
DATA05 1048576 /home/oracle/data01/oradata/IBM/data05_01.dbf
DATA05 1048576 /home/oracle/data01/oradata/IBM/data05_02.dbf
================================================================================================
* tablespace 삭제 : Dictionary에서만 삭제되는것으로 실제 물리적으로 파일은 os command로 삭제해야한다.
SQL> select tablespace_name from dba_tablespaces
2 where tablespace_name like ‘DATA%’
3 minus
4 select distinct tablespace_name from dba_segments;
TABLESPACE_NAME
—————
DATA05
SQL> drop tablespace data05;
SQL> select tablespace_name,bytes,file_name from dba_data_files;
TABLESPACE_NAME BYTES FILE_NAME
————— ———- ———————————————
TOOLS 10485760 /home/oracle/data01/oradata/IBM/tools01.dbf
DRSYS 20971520 /home/oracle/data01/oradata/IBM/drsys01.dbf
USERS 20971520 /home/oracle/data01/oradata/IBM/users01.dbf
INDX 20971520 /home/oracle/data01/oradata/IBM/indx01.dbf
RBS 52428800 /home/oracle/data01/oradata/IBM/rbs01.dbf
TEMP 20971520 /home/oracle/data01/oradata/IBM/temp01.dbf
SYSTEM 283115520 /home/oracle/data01/oradata/IBM/system01.dbf
SQL> !ls //home/oracle/data01/oradata/IBM/*.dbf
//home/oracle/data01/oradata/IBM/data05_01.dbf //home/oracle/data01/oradata/IBM/system01.dbf
//home/oracle/data01/oradata/IBM/data05_02.dbf //home/oracle/data01/oradata/IBM/temp01.dbf
//home/oracle/data01/oradata/IBM/drsys01.dbf //home/oracle/data01/oradata/IBM/tools01.dbf
//home/oracle/data01/oradata/IBM/indx01.dbf //home/oracle/data01/oradata/IBM/users01.dbf
//home/oracle/data01/oradata/IBM/rbs01.dbf
dictionary에서는 삭제되었으나 여전히 물리적인 file은 존재한다. 삭제하면 된다.
(tablespace생성시에는 file이 그냥 생성되나 삭제시는 dictionary삭제후 강제로 삭제해줘야 한다.)
SQL> !rm /home/oracle/data01/oradata/IBM/data05*
================================================================================================
* tablespace 의 online/offline, read only/read write
SQL> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
———————————————————— ——————
SYSTEM ONLINE
TOOLS ONLINE
RBS ONLINE
TEMP ONLINE
USERS ONLINE
INDX ONLINE
DRSYS ONLINE
7 rows selected.
SQL> select tablespace_name from dba_tables
2 where table_name =’DEPT’ and owner=’SCOTT’;
TABLESPACE_NAME
————————————————————
SYSTEM
default로 생성시 scott user의 data가 system tablespace에 생성되었으나 이렇게 쓰면 안된다.
하나 생성해볼까?
SQL> create tablespace data01
2 datafile ‘/home/oracle/data01/oradata/IBM/data01.dbf’ size 1m;
Tablespace created.
SQL> connect scott/tiger
Connected.
SQL> create table dept_tmp tablespace data01
2 as select * from dept;
SQL> connect internal
Connected.
SQL> select tablespace_name from dba_tables
2 where table_name =’DEPT_TMP’ and owner=’SCOTT’;
TABLESPACE_NAME
————————————————————
DATA01
SQL> select * from scott.dept_tmp;
DEPTNO DNAME LOC
———- —————————- ————————–
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
제대로 된다. 그렇다면 tablespace를 offline으로…
SQL> alter tablespace data01 offline;
SQL> select tablespace_name, status from dba_tablespaces
2 where tablespace_name=’DATA01′;
TABLESPACE_NAME STATUS
———————————————————— ——————
DATA01 OFFLINE
SQL> select * from scott.dept_tmp;
select * from scott.dept_tmp
*
ERROR at line 1:
ORA-00376: file 8 cannot be read at this time
ORA-01110: data file 8: ‘/home/oracle/data01/oradata/IBM/data01.dbf’
위와 같이 error가 발생한다.
다시 online으로 해두자.
SQL> alter tablespace data01 online;
이번엔 read only로 변경
SQL> alter tablespace data01 read only;
SQL> select tablespace_name, status from dba_tablespaces
2 where tablespace_name=’DATA01′;
TABLESPACE_NAME STATUS
———————————————————— ——————
DATA01 READ ONLY
변경되었다.
SQL> insert into scott.dept_tmp values(80,’new_dept’,’new_loc’);
insert into scott.dept_tmp values(80,’new_dept’,’new_loc’)
*
ERROR at line 1:
ORA-00372: file 8 cannot be modified at this time
ORA-01110: data file 8: ‘/home/oracle/data01/oradata/IBM/data01.dbf’
insert같은 DML(write성) 수행시 위와 같은 error 발생
원상복구
SQL> alter tablespace data01 read write;
SQL> insert into scott.dept_tmp values(80,’test’,’test’);
제대로 된다.
================================================================================================
9. Storage Structure and Relationships
================================================================================================
* Extent 정보 조회 : 다음과 같이 각종 extent,segment 등의 정보를 조회해 볼 수 있다.
SQL> col owner format a10
SQL> col segment_type format a12
SQL> col segment_name format a12
SQL> col tablespace_name format a10
SQL> select owner,segment_name,segment_type, tablespace_name,max_extents,extents,pct_increase
2 from dba_segments
3 where max_extents – extents <= 10 and owner !=’SYS’;
no rows selected
SQL> select owner,segment_name,segment_type, tablespace_name,max_extents,extents,pct_increase
2 from dba_segments
3 where owner=’SCOTT’;
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE MAX_EXTENTS EXTENTS PCT_INCREASE
———- ———— ———— ———- ———– ———- ————
SCOTT DEPT_TMP TABLE DATA01 505 1 50
SCOTT DEPT TABLE SYSTEM 2147483645 1 50
SCOTT EMP TABLE SYSTEM 2147483645 1 50
SCOTT BONUS TABLE SYSTEM 2147483645 1 50
SCOTT SALGRADE TABLE SYSTEM 2147483645 1 50
SCOTT PK_DEPT INDEX SYSTEM 2147483645 1 50
SCOTT PK_EMP INDEX SYSTEM 2147483645 1 50
SQL> select segment_name,extents, initial_extent, next_extent,pct_increase
2 from dba_segments
3 where owner=’SCOTT’ and segment_name=’EMP’;
SEGMENT_NAME EXTENTS INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE
———— ———- ————– ———– ————
EMP 1 65536 65536 50
SQL> select segment_name,extent_id,block_id,bytes,blocks
2 from dba_extents
3 where owner=’SCOTT’ and segment_name=’EMP’;
4 order by 2,3;
SEGMENT_NAME EXTENT_ID BLOCK_ID BYTES BLOCKS
———— ———- ———- ———- ———-
EMP 0 33945 65536 8
================================================================================================
* Free space 관리
tablespace내에 free space를 먼저 확인해본다.
SQL> select * from dba_free_space
2 where tablespace_name =’DATA01′ order by 1,2,3;
TABLESPACE FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
———- ———- ———- ———- ———- ————
DATA01 8 7 999424 122 8
테이블을 여러개 생성해보자.
SQL> create table scott.dept2 tablespace data01 as select * from scott.dept;
SQL> create table scott.dept3 tablespace data01 as select * from scott.dept;
SQL> create table scott.dept4 tablespace data01 as select * from scott.dept;
SQL> create table scott.dept5 tablespace data01 as select * from scott.dept;
SQL> create table scott.dept6 tablespace data01 as select * from scott.dept;
SQL> select * from dba_free_space
2 where tablespace_name =’DATA01′ order by 1,2,3;
TABLESPACE FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
———- ———- ———- ———- ———- ————
DATA01 8 32 794624 97 8
사용함에 따라 tablespace내 free space 가 줄어듦을 알 수 있다.
SQL> drop table scott.dept2;
drop table dept2
*
ERROR at line 1:
ORA-04098: trigger ‘SYS.JIS$ROLE_TRIGGER$’ is invalid and failed re-validation
이건 또 뭐야 ? trigger가 걸려있네요…
table drop 을 위해
SQL> alter trigger SYS.JIS$ROLE_TRIGGER$ disable;
drop table scott.dept3; ==> dept4 만 빼고 전부 drop
drop table scott.dept5;
drop table scott.dept6;
SQL> select * from dba_free_space
2 where tablespace_name =’DATA01′ order by 1,2,3;
TABLESPACE FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
———- ———- ———- ———- ———- ————
DATA01 8 7 40960 5 8
DATA01 8 32 794624 97 8
tablespace의 free space가 늘긴 했는데 쪼개졌네요..
빈공간을 병합하자
SQL> alter tablespace data01 coalesce;
SQL> select * from dba_free_space
2 where tablespace_name =’DATA01′ order by 1,2,3;
TABLESPACE FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
———- ———- ———- ———- ———- ————
DATA01 8 7 40960 5 8
DATA01 8 32 794624 97 8
그래도 두개로 쪼개져 있는 이유는? 중간에 dept4 가 사용하는 space가 coalesce 되지 않았기 때문
SQL> drop table scott.dept4;
SQL> alter tablespace data01 coalesce;
완전히 병합되었다.
================================================================================================
10. Managing Rollback Segments
================================================================================================
* rollback segment의 정보 조회
SQL> col owner format a10
SQL> col segment_name format a12
SQL> col segment_type format a12
SQL> col tablespace_name format a10
SQL> col status format a7
SQL> select segment_name,tablespace_name,status,initial_extent,next_extent,min_extents
2 from dba_rollback_segs;
SEGMENT_NAME TABLESPACE STATUS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
———— ———- ——- ————– ———– ———–
SYSTEM SYSTEM ONLINE 57344 57344 2
RBS0 RBS ONLINE 524288 524288 8
RBS1 RBS ONLINE 524288 524288 8
RBS2 RBS ONLINE 524288 524288 8
RBS3 RBS ONLINE 524288 524288 8
RBS4 RBS ONLINE 524288 524288 8
RBS5 RBS ONLINE 524288 524288 8
RBS6 RBS ONLINE 524288 524288 8
================================================================================================
* rollback segment 생성
SQL> create rollback segment rbs99
2 tablespace rbs
3 storage(initial 20k next 20k minextents 2 optimal 80k);
Rollback segment created.
SQL> select segment_name,tablespace_name,status,initial_extent,next_extent,min_extents
2 from dba_rollback_segs;
SEGMENT_NAME TABLESPACE STATUS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
———— ———- ——- ————– ———– ———–
SYSTEM SYSTEM ONLINE 57344 57344 2
RBS0 RBS ONLINE 524288 524288 8
RBS1 RBS ONLINE 524288 524288 8
RBS2 RBS ONLINE 524288 524288 8
RBS3 RBS ONLINE 524288 524288 8
RBS4 RBS ONLINE 524288 524288 8
RBS5 RBS ONLINE 524288 524288 8
RBS6 RBS ONLINE 524288 524288 8
RBS99 RBS OFFLINE 24576 32768 2
추가되었다. online으로 전환하자.
SQL> alter rollback segment rbs99 online;
SQL> create table emp2 as select * from emp;
SQL> select name,extents,xacts,shrinks,optsize
2 from v$rollname n, v$rollstat s
3 where n.usn = s.usn;
NAME EXTENTS XACTS SHRINKS OPTSIZE
——————— ———– ———- ———- ———-
SYSTEM 9 0 0
RBS0 8 0 0 4194304
RBS1 8 0 0 4194304
RBS2 8 0 0 4194304
RBS3 8 0 0 4194304
RBS4 8 0 0 4194304
RBS5 8 0 0 4194304
RBS6 8 0 0 4194304
RBS99 2 0 0 81920 ==> extents,xacts의 변화 관찰
SQL> set transaction use rollback segment rbs99;
SQL> update emp2 set hiredate=sysdate;
SQL> select name,extents,xacts,shrinks,optsize
2 from v$rollname n, v$rollstat s
3 where n.usn = s.usn;
NAME EXTENTS XACTS SHRINKS OPTSIZE
————— ———- ———- ———- ———-
SYSTEM 9 0 0
RBS0 8 0 0 4194304
RBS1 8 0 0 4194304
RBS2 8 0 0 4194304
RBS3 8 0 0 4194304
RBS4 8 0 0 4194304
RBS5 8 0 0 4194304
RBS6 8 0 0 4194304
RBS99 2 1 0 81920 ==> transaction이 시작됨
SQL> update emp2 set hiredate=sysdate-1;
sql> insert into emp2 select * from emp2; ==> 엄청 많이 수행 하자.
SQL> select name,extents,xacts,shrinks,optsize
2 from v$rollname n, v$rollstat s
3 where n.usn = s.usn;
NAME EXTENTS XACTS SHRINKS OPTSIZE
————— ———- ———- ———- ———-
SYSTEM 9 0 0
RBS0 8 0 0 4194304
RBS1 8 0 0 4194304
RBS2 8 0 0 4194304
RBS3 8 0 0 4194304
RBS4 8 0 0 4194304
RBS5 8 0 0 4194304
RBS6 8 0 0 4194304
RBS99 3 1 0 81920 ==> extents 증가
SQL> rollback;
SQL> set transaction use rollback segment rbs99;
SQL> update emp2 set sal=1000;
SQL> select name,extents,xacts,shrinks,optsize
2 from v$rollname n, v$rollstat s
3 where n.usn = s.usn;
NAME EXTENTS XACTS SHRINKS OPTSIZE
————— ———- ———- ———- ———-
SYSTEM 9 0 0
RBS0 8 0 0 4194304
RBS1 8 0 0 4194304
RBS2 8 0 0 4194304
RBS3 8 0 0 4194304
RBS4 8 0 0 4194304
RBS5 8 0 0 4194304
RBS6 8 0 0 4194304
RBS99 3 1 0 81920 ==> automatic 하게 shrink되었는지 확인
이전 tranx은 종료되었고 새로운 tranx가 시작됨
================================================================================================
* rollback segment 삭제
SQL> rollback;
SQL> select name,extents,xacts,shrinks,optsize
2 from v$rollname n, v$rollstat s
3 where n.usn = s.usn; ==> xacts 가 ‘0’ 인지 먼저 확인
SQL> alter rollback segment rbs99 offline;
SQL> drop rollback segment rbs99;
SQL> select segment_name,tablespace_name,status,initial_extent,next_extent,min_extents
2 from dba_rollback_segs;
SEGMENT_NAME TABLESPACE STATUS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
———— ———- ——- ————– ———– ———–
SYSTEM SYSTEM ONLINE 57344 57344 2
RBS0 RBS ONLINE 524288 524288 8
RBS1 RBS ONLINE 524288 524288 8
RBS2 RBS ONLINE 524288 524288 8
RBS3 RBS ONLINE 524288 524288 8
RBS4 RBS ONLINE 524288 524288 8
RBS5 RBS ONLINE 524288 524288 8
RBS6 RBS ONLINE 524288 524288 8
확인해보니 삭제되었다.
================================================================================================
11.Managing Tables
================================================================================================
* Temporary Table
a. on commit perserve rows : session내에서 생성한 temp table에 대해서 지속적.
새로운 session 연결 되면 data 지워짐
Example : Creating a Session-Specific Temporary Table
CREATE GLOBAL TEMPORARY TABLE …
[ON COMMIT PRESERVE ROWS ]
b. on commit delete rows : tansaction이 끝나면 temp table 내의 data가 지워짐(commit,rollback등)
Example : Creating a Transaction-Specific Temporary Table
CREATE GLOBAL TEMPORARY TABLE …
[ON COMMIT DELETE ROWS ]
================================================================================================
* Using Temporary Tables to Improve Performance
You can use temporary tables to improve performance when you run complex queries.
Running multiple such queries is relatively slow because the tables are accessed multiple times
for each returned row. It is faster to cache the values from a complex query in a temporary table,
then run the queries against the temporary table.
For example, even with a view like this defined to simplify further queries,
the queries against the view may be slow because the contents of the view are recalculated each time:
CREATE OR REPLACE VIEW Profile_values_view AS
SELECT d.Profile_option_name, d.Profile_option_id, Profile_option_value,
u.User_name, Level_id, Level_code
FROM Profile_definitions d, Profile_values v, Profile_users u
WHERE d.Profile_option_id = v.Profile_option_id
AND ((Level_code = ‘USER’ AND Level_id = U.User_id) OR
(Level_code = ‘DEPARTMENT’ AND Level_id = U.Department_id) OR
(Level_code = ‘SITE’))
AND NOT EXISTS (SELECT 1 FROM PROFILE_VALUES P
WHERE P.PROFILE_OPTION_ID = V.PROFILE_OPTION_ID
AND ((Level_code = ‘USER’ AND
level_id = u.User_id) OR
(Level_code = ‘DEPARTMENT’ AND
level_id = u.Department_id) OR
(Level_code = ‘SITE’))
AND INSTR(‘USERDEPARTMENTSITE’, v.Level_code) >
INSTR(‘USERDEPARTMENTSITE’, p.Level_code));
A temporary table allows us to run the computation once,
and cache the result in later SQL queries and joins:
CREATE GLOBAL TEMPORARY TABLE Profile_values_temp
(
Profile_option_name VARCHAR(60) NOT NULL,
Profile_option_id NUMBER(4) NOT NULL,
Profile_option_value VARCHAR2(20) NOT NULL,
Level_code VARCHAR2(10) ,
Level_id NUMBER(4) ,
CONSTRAINT Profile_values_temp_pk
PRIMARY KEY (Profile_option_id)
) ON COMMIT PRESERVE ROWS ORGANIZATION INDEX;
INSERT INTO Profile_values_temp
(Profile_option_name, Profile_option_id, Profile_option_value,
Level_code, Level_id)
SELECT Profile_option_name, Profile_option_id, Profile_option_value,
Level_code, Level_id
FROM Profile_values_view;
COMMIT;
Now the temporary table can be used to speed up queries,
and the results cached in the temporary table are freed automatically by the database
when the session ends.
================================================================================================
* Row Migration Test
scott/tiger로 접속해서
SQL> create table chain_test(col1 varchar2(100));
Table created.
SQL> insert into chain_test values(‘a’);
1 row created.
SQL> insert into chain_test select * from chain_test; <====== 1 row created.
SQL> / <====== 2 rows created.
SQL> / <====== 4 rows created.
SQL> / <====== 8 rows created.
SQL> / <====== 16 rows created.
SQL> / <====== 32 rows created.
SQL> / <====== 64 rows created.
SQL> / <====== 128 rows created.
SQL> / <====== 256 rows created.
SQL> / <====== 512 rows created.
SQL> commit;
SQL> @$ORACLE_HOME/rdbms/admin/utlchain <====== chanined_rows table생성
Table created.
SQL> desc chained_rows
Name Null? Type
—————————————– ——– —————————-
OWNER_NAME VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
CLUSTER_NAME VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
SUBPARTITION_NAME VARCHAR2(30)
HEAD_ROWID ROWID
ANALYZE_TIMESTAMP DATE
SQL> analyze table chain_test list chained rows;
Table analyzed.
SQL> select count(*) from chained_rows;
COUNT(*)
———-
0 ======> 아직까지는 chaining이 하나도 없지…
SQL> update chain_test
2 set col1 = ‘aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa’;
1024 rows updated.
SQL> analyze table chain_test list chained rows;
Table analyzed.
SQL> select count(*) from chained_rows;
COUNT(*)
———-
995 ==============> row migration이 다량 발생
row migration이 일어나면 여러 block에서 읽어야 하므로 그만큼 performance가 떨어진다.
이를 해결하기 위해 주기적으로 analyze하여 확인해보고 insert를 다시 해주면 된다.
chaining이 일어난 row들의 rowid로 찾아서 임시table을 생성하고 원래 table에서 chaining이 일어난
data를 삭제하고 다시 insert하면 된다.
SQL> create table chain_tmp as select * from chain_test
2 where rowid in (select head_rowid from chained_rows where table_name=’CHAIN_TEST’);
SQL> delete from chain_test
2 where rowid in(select head_rowid from chained_rows where table_name=’CHAIN_TEST’);
SQL> insert into chain_test select * from chain_tmp;
SQL> commit;
다시 cahined_rows table 을 삭제하고 analyze해보자.
SQL> truncate table chained_rows;
Table truncated.
SQL> analyze table chain_test list chained rows;
Table analyzed.
SQL> select * from chained_rows; <====== no rows selected
chaining이 없어졌다.
================================================================================================
12. Managing Indexes
================================================================================================
1. B*Tree Index 생성, 확인
SQL> col table_name format a10
SQL> col index_name format a20
SQL> col index_type format a10
SQL> col column_name format a12
SQL> create index scott.dept_dname_ind on scott.dept(dname);
Index created.
SQL> create unique index scott.dept_deptno_uind on scott.dept(deptno);
create unique index scott.dept_deptno_uind on scott.dept(deptno)
*
ERROR at line 1:
ORA-01408: such column list already indexed
error가 난 이유는 column이 pk로 지정될때는 unique index가 자동으로 생성되기 때문.
user_constraints, user_cons_columns 등에서 확인해보면 알수있다.
SQL> select table_name,index_name,index_type,uniqueness
2 from dba_indexes
3 where owner=’SCOTT’;
TABLE_NAME INDEX_NAME INDEX_TYPE UNIQUENESS
———- ——————– ———- ——————
DEPT DEPT_DNAME_IND NORMAL NONUNIQUE ====> 생성한 index
AUDIT_ACTI I_AUDIT_ACTIONS NORMAL UNIQUE
ONS
DEPT PK_DEPT NORMAL UNIQUE
EMP PK_EMP NORMAL UNIQUE
DBMS_LOCK_ SYS_C001456 NORMAL UNIQUE
ALLOCATED
DBMS_ALERT SYS_C001457 NORMAL UNIQUE
_INFO
SQL> select table_name,index_name,column_position,column_name
2 from dba_ind_columns
3 where index_owner=’SCOTT’;
TABLE_NAME INDEX_NAME COLUMN_POSITION COLUMN_NAME
———- ——————– ————— ————
DEPT DEPT_DNAME_IND 1 DNAME ====> index가 걸린 column
AUDIT_ACTI I_AUDIT_ACTIONS 1 ACTION
ONS
AUDIT_ACTI I_AUDIT_ACTIONS 2 NAME
ONS
DEPT PK_DEPT 1 DEPTNO
EMP PK_EMP 1 EMPNO
DBMS_LOCK_ SYS_C001456 1 NAME
ALLOCATED
DBMS_ALERT SYS_C001457 1 NAME
_INFO
DBMS_ALERT SYS_C001457 2 SID
_INFO
================================================================================================
* Bitmap Index 생성, 확인
SQL> select count(*) from scott.emp;
SQL> select distinct job from scott.emp;
JOB
——————
ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN
SQL> create bitmap index scott.emp_job_bind on scott.emp(job);
SQL> select table_name,index_name,index_type,uniqueness
2 from dba_indexes
3 where owner=’SCOTT’;
TABLE_NAME INDEX_NAME INDEX_TYPE UNIQUENESS
———- ——————– ———- ——————
DEPT DEPT_DNAME_IND NORMAL NONUNIQUE
EMP EMP_JOB_BIND BITMAP NONUNIQUE =====>
AUDIT_ACTI I_AUDIT_ACTIONS NORMAL UNIQUE
…
SQL> select table_name,index_name,column_position,column_name
2 from dba_ind_columns
3 where index_owner=’SCOTT’;
TABLE_NAME INDEX_NAME COLUMN_POSITION COLUMN_NAME
———- ——————– ————— ————
DEPT DEPT_DNAME_IND 1 DNAME
EMP EMP_JOB_BIND 1 JOB =====>
AUDIT_ACTI I_AUDIT_ACTIONS 1 ACTION
ONS
================================================================================================
* Reverse Key Index 생성, 확인
SQL> create index scott.emp_hiredate_rind on scott.emp(hiredate) reverse;
SQL> select table_name,index_name,index_type,uniqueness
2 from dba_indexes
3 where owner=’SCOTT’;
TABLE_NAME INDEX_NAME INDEX_TYPE UNIQUENESS
———- ——————– ———- ——————
DEPT DEPT_DNAME_IND NORMAL NONUNIQUE
EMP EMP_HIREDATE_RIND NORMAL/REV NONUNIQUE ==>
EMP EMP_JOB_BIND BITMAP NONUNIQUE
….
SQL> select table_name,index_name,column_position,column_name
2 from dba_ind_columns
3 where index_owner=’SCOTT’;
TABLE_NAME INDEX_NAME COLUMN_POSITION COLUMN_NAME
———- ——————– ————— ————
DEPT DEPT_DNAME_IND 1 DNAME
EMP EMP_HIREDATE_RIND 1 HIREDATE ==>
EMP EMP_JOB_BIND 1 JOB
….
================================================================================================
* Funtion-Based Index 생성, 확인 <===== Query Rewrite권한 필요
SQL> create index scott.emp_sal_find on scott.emp(sal * 1.1);
SQL> select table_name,index_name,index_type,uniqueness
2 from dba_indexes
3 where owner=’SCOTT’;
TABLE_NAME INDEX_NAME INDEX_TYPE UNIQUENESS
———- ——————– ———- ——————
DEPT DEPT_DNAME_IND NORMAL NONUNIQUE
EMP EMP_HIREDATE_RIND NORMAL/REV NONUNIQUE
EMP EMP_JOB_BIND BITMAP NONUNIQUE
EMP EMP_SAL_FIND FUNCTION-B NONUNIQUE ==>
ASED NORMA
L
….
SQL> select table_name,index_name,column_position,column_name
2 from dba_ind_columns
3 where index_owner=’SCOTT’;
TABLE_NAME INDEX_NAME COLUMN_POSITION COLUMN_NAME
———- ——————– ————— ————
DEPT DEPT_DNAME_IND 1 DNAME
EMP EMP_HIREDATE_RIND 1 HIREDATE
EMP EMP_JOB_BIND 1 JOB
EMP EMP_SAL_FIND 1 SYS_NC00009$ ====> column name이 내부적으로 바뀐다.
….
================================================================================================
* Index drop
SQL> drop index scott.dept_dname_ind;
SQL> drop index scott.emp_hiredate_rind;
SQL> drop index scott.emp_job_bind;
SQL> drop index scott.emp_sal_find;
================================================================================================
13.Maintaining Data Integrity
================================================================================================
* PK/UK 와 Unique Index
SQL> desc scott.dept
Name Null? Type
—————————————– ——– —————————-
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
PK생성은 다음과 같이 할 수 있다.
SQL> alter table scott.dept
2 add constraint dept_deptno_pk primary key(deptno);
add constraint dept_deptno_pk primary key(deptno)
*
ERROR at line 2:
ORA-02260: table can have only one primary key
이미 pk가 설정이 되어있어서 한테이블에 두개의 pk를 설정할 수 없다는 error.
SQL> alter table scott.dept
2 add constraint dept_dname_uk unique (dname);
Table altered.
SQL> select table_name,constraint_name,constraint_type, status
2 from dba_constraints
3 where owner=’SCOTT’;
TABLE_NAME CONSTRAINT_NAME CO STATUS
——————– ————— — —————-
DEPT DEPT_DNAME_UK U ENABLED <=== 새로 생성한 UK
EMP FK_DEPTNO R ENABLED
DEPT PK_DEPT P ENABLED
EMP PK_EMP P ENABLED
AUDIT_ACTIONS SYS_C001454 C ENABLED
AUDIT_ACTIONS SYS_C001455 C ENABLED
DBMS_LOCK_ALLOCATED SYS_C001456 P ENABLED
DBMS_ALERT_INFO SYS_C001457 P ENABLED
SQL> select table_name,index_name,index_type,uniqueness
2 from dba_indexes
3 where owner=’SCOTT’;
TABLE_NAME INDEX_NAME INDEX_TYPE UNIQUENESS
———- ——————– ———- ——————
DEPT DEPT_DNAME_UK NORMAL UNIQUE <==
AUDIT_ACTI I_AUDIT_ACTIONS NORMAL UNIQUE
ONS
SQL> select table_name,index_name,column_position,column_name
2 from dba_ind_columns
3 where index_owner=’SCOTT’;
TABLE_NAME INDEX_NAME COLUMN_POSITION COLUMN_NAME
———- ——————– ————— ————
DEPT DEPT_DNAME_UK 1 DNAME <==
AUDIT_ACTI I_AUDIT_ACTIONS 1 ACTION
ONS
================================================================================================
* Constraint Check
SQL> insert into scott.dept values(50,’HR’,’SEOUL’);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into scott.dept values(50,’HR Dept’,’SEOUL’);
insert into scott.dept values(50,’HR Dept’,’SEOUL’)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated
UK 값에 같은 값 insert하려다가 error가 난다.
================================================================================================
* Constriaint 비활성화/활성화
SQL> alter table scott.dept
2 disable constraint dept_dname_uk;
SQL> alter table scott.dept
2 enable constraint dept_dname_uk;
================================================================================================
* Deferred Constraint(?) ==> 자료좀 찾아보자..
================================================================================================
* Constraint 삭제
alter table <테이블명> drop constraint
================================================================================================
14. Loading Data
================================================================================================
* 사용법
Usage: SQLLOAD keyword=value [,keyword=value,…]
Valid Keywords:
userid — ORACLE username/password
control — Control file name
log — Log file name
bad — Bad file name
data — Data file name
discard — Discard file name
discardmax — Number of discards to allow (Default all)
skip — Number of logical records to skip (Default 0)
load — Number of logical records to load (Default all)
errors — Number of errors to allow (Default 50)
rows — Number of rows in conventional path bind array or between direct path data saves
(Default: Conventional path 64, Direct path all)
bindsize — Size of conventional path bind array in bytes (Default 65536)
silent — Suppress messages during run (header,feedback,errors,discards,partitions)
direct — use direct path (Default FALSE)
parfile — parameter file: name of file that contains parameter specifications
parallel — do parallel load (Default FALSE)
file — File to allocate extents from
skip_unusable_indexes — disallow/allow unusable indexes or index partitions (Default FALSE)
skip_index_maintenance — do not maintain indexes, mark affected indexes as unusable (Default FALSE)
commit_discontinued — commit loaded rows when load is discontinued (Default FALSE)
readsize — Size of Read buffer (Default 1048576)
PLEASE NOTE: Command-line parameters may be specified either by
position or by keywords. An example of the former case is ‘sqlload
scott/tiger foo’; an example of the latter is ‘sqlload control=foo
userid=scott/tiger’. One may specify parameters by position before
but not after parameters specified by keywords. For example,
‘sqlload scott/tiger control=foo logfile=log’ is allowed, but
‘sqlload scott/tiger control=foo log’ is not, even though the
position of the parameter ‘log’ is correct.
================================================================================================
* case 별로 sqlldr을 사용하는법은
http://technet.oracle.com/doc/server.815/a67792/ch04.htm#1364
에 자세히 나와있다. 그중 두가지 정도의 case만 기본적으로 다루어보자.
================================================================================================
* case1 : data를 control file에 직접 입력하여 load하기
a. data 입력될 table 있어야함 (dept_tmp 라는 table을 생성하여 입력해보자)
SQL> create table dept_tmp as select * from dept;
Table created.
SQL> truncate table dept_tmp;
Table truncated.
b. control file을 구성한다.
load data
infile * ==> data 가 ctl file 끝에 있다는 의미
replace ==> 이게 없으면 빈테이블일때만 load된다.
into table dept_tmp
fileds terminated by ‘,’ optionally enclosed by ‘”‘ ==> field 구분자와 “가 들어가면 빼고 입력된다.
(deptno,dname,loc)
begindata
12,research,”saratoga”
10,”accounting”,cleveland
11,”art”,”salem”
13,finance,boston
c. 다음과 같이 sqlldr을 실행
oracle@swsvrctr:/home/oracle> sqlldr scott/tiger control=test.ctl log=test.log bad=test.bad
error가 생기면 log가 남고 loading 되지 않은 data만 bad file에 남는다.
================================================================================================
* case 2 : Fixed-format records의 Loading
a. data 입력될 table 있어야함 (dept_tmp 라는 table을 생성하여 입력해보자)
SQL> create table emp_tmp as select * from emp;
Table created.
b. data file의 내용을 보고 그에 맞게 control file을 구성한다.
data file은 다음과 같다고 하자.
1111 joo Manager 1111 19191.00 10
2222 hwang salesman 2222 294974.50 20
3333 test test 3333 4984.00 40
4444 kwon engineer 4444 49.90 50
control file을 만들어보자.
load data
infile ‘/home/oracle/test.dat’
replace
into table emp_tmp
(empno position(01:04) integer external, ==> position을 일일이 맞추어 준다.
ename position(09:14) char,
job position(17:24) char,
mgr position(33:36) integer external,
sal position(41:49) decimal external,
comm position(51:54) decimal external,
deptno position(57:58) integer external)
주의 : data file의 data가 공백이 아닌 tab 으로 되어있으면 position에서 한칸으로 인식되니까 주의
c. 다음과 같이 sqlldr을 실행
home/oracle> sqlldr scott/tiger control=test.ctl data=test.dat log=test.log bad=test.bad
================================================================================================
15. Reorganizing Data
================================================================================================
* 오래 사용한 table 주로 DML성 문장이 자주 일어나 performance에 영향을 미치므로 주기적으로
Reorganize 를 해주는것이 좋다.
Export => table drop => import 순으로 한다.
export 는 user 별로 table별로 받을 수 있다.
a. Export
oracle@swsvrctr:/home/oracle> exp scott/tiger tables=’dept,emp’ file=test.dmp
Export: Release 8.1.6.0.0 – Production on Wed Jul 4 14:32:21 2001
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0, 64 bit – Production
With the Partitioning option
JServer Release 8.1.6.0.0 – Production
Export done in KO16KSC5601 character set and WE8ISO8859P1 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified tables via Conventional Path …
. . exporting table DEPT 7 rows exported
. . exporting table EMP 14 rows exported
Export terminated successfully without warnings.
b. drop table
oracle@swsvrctr:/home/oracle> sqlplus scott/tiger
SQL> drop table emp;
SQL> drop table dept;
c. Import
oracle@swsvrctr:/home/oracle> imp scott/tiger tables=’dept,emp’ file=test.dmp
Import: Release 8.1.6.0.0 – Production on Wed Jul 4 14:34:25 2001
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0, 64 bit – Production
With the Partitioning option
JServer Release 8.1.6.0.0 – Production
Export file created by EXPORT:V08.01.06 via conventional path
import done in KO16KSC5601 character set and WE8ISO8859P1 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SCOTT’s objects into SCOTT
. . importing table “DEPT” 7 rows imported
. . importing table “EMP” 14 rows imported
About to enable constraints…
Import terminated successfully without warnings.
d. 제대로 되었나 조회
SQL> select * from dept;
SQL> select * from emp;
================================================================================================
16. Managing Password Security and Resources
================================================================================================
* verify_function 생성을 위해 돌려줘야 할 script : $ORACLE_HOME/rdbms/admin/utlpwdmg.sql
NAME
utlpwdmg.sql – script for Default Password Resource Limits
DESCRIPTION
This is a script for enabling the password management features
by setting the default password resource limits.
NOTES
This file contains a function for minimum checking of password
complexity. This is more of a sample function that the customer
can use to develop the function for actual complexity checks that the
customer wants to make on the new password.
MODIFIED (MM/DD/YY)
asurpur 04/17/97 – Fix for bug479763
asurpur 12/12/96 – Changing the name of password_verify_function
asurpur 05/30/96 – New script for default password management
asurpur 05/30/96 – Created
================================================================================================
* verify_function 생성 및 패스워드 관리기능 활성화
SQL> @$ORACLE_HOME/rdbms/admin/utlpwdmg
Function created.
Profile altered.
================================================================================================
* 사용자 생성
utlpwdmg.sql script를 돌려 verify_function을 생성후 user를 생성할때는 몇가지를 check하여 좀더
password 관리를 할 수 있도록 해준다.
다음과 같이 user/passwd를 같게 하면 error가 나서 생성되지 않는다.
SQL> create user myuser identified by myuser
2 default tablespace TS_USER1
3 temporary tablespace TEMP;
create user myuser identified by myuser
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20001: Password same as user
다시 시도
SQL> create user myuser identified by mypasswd9$
2 default tablespace ts_user1
3 temporary tablespace temp;
User created.
SQL> grant connect,resource to myuser;
확인
SQL> connect myuser/mypasswd9$
================================================================================================
* 패스워드 관리/ expire 시키기
SQL> alter user myuser password expire;
User altered.
admin이 강제로 expire시켰기 때문에 다음과 같이 password변경을 뭍는다.
SQL> connect myuser/mypassword9$
Changing password for test
New password:
Retype new password:
Password changed
Connected.
password를 또 규칙에 맞지 않게 넣으면 다음과 같은 error가 발생한다.
ERROR:
ORA-00988: missing or invalid password(s)
================================================================================================
* dictionary 조회
SQL> select resource_name,limit from dba_profiles
2 where profile=’DEFAULT’ and resource_type=’PASSWORD’;
RESOURCE_NAME LIMIT
——————– —————
FAILED_LOGIN_ATTEMPT 3
S
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 1800
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNC VERIFY_FUNCTION
TION
PASSWORD_LOCK_TIME .0006
PASSWORD_GRACE_TIME 10
RESOURCE_NAME LIMIT
——————– —————
FAILED_LOGIN_ATTEMPT 3
S
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 1800
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNC VERIFY_FUNCTION
TION
PASSWORD_LOCK_TIME .0006
PASSWORD_GRACE_TIME 10
================================================================================================
17. Managing Users
================================================================================================
* OS 인증 : os에 login 한 id/passwd로 oracle의 id/passwd로 함께 사용하기 위한방법
a. user를 생성하는데 identified externally로 생성한다.
oracle@swsvrctr:/home/oracle> sqlplus internal
SQL> create user oracle identified externally
2 default tablespace ts_user1
3 temporary tablespace temp;
User created.
SQL> grant connect,resource to oracle;
SQL> revoke unlimited tablespace from oracle;
SQL> shutdown immediate
b. init.ora 에서 다음을 편집한다.(추가)
os_authent_prefix=””
c. startup하고 확인해본다.
SQL> startup
SQL> exit
oracle@swsvrctr:/home/oracle> sqlplus / ==> id/passwd를 넣을 필요없이 접속
SQL> select user from dual;
USER
————————————————————
ORACLE
제대로 접속이 된다.
================================================================================================
* tablespace 사용량 통제
SQL> connect internal
Connected.
SQL> col tablespacename format a10
SQL> col username format a10
SQL> alter user oracle quota 20k on ts_user1; ==> oracle user는 ts_user1에 20k 만 사용 가능하다.
User altered.
SQL> select * from dba_ts_quotas;
TABLESPACE_NAME USERNAME
———————————————————— ———-
BYTES MAX_BYTES BLOCKS MAX_BLOCKS
———- ———- ———- ———-
TS_USER1 ORACLE
0 20480 0 5
SQL> connect /
Connected.
SQL> select user from dual;
USER
————————————————————
ORACLE
SQL> create table test
2 (id number(10))
3 (tablespace ts_user1
4 storage(initial 20k)
까지는 생성이 되나 다음과 같이 늘리려고 하면 error가 난다.
SQL> alter table test allocate extent(size 4k); ==> error
================================================================================================
18. Managing Privileges
================================================================================================
* with grant option과 with admin option
(둘다 실행 권한을 받은 user가 다시 실행 권한을 다른 user에게 줄 수 있게 해주는 option이다.)
— 차이는 with admin option으로 권한을 받은 user1이 다른 user2에게 권한을 부여한 후 user1으로부터
권한을 revoke하면 user1의 권한만 revoke되나
with grant option으로 부여하면 user1에게 revoke 될 시 user2의 권한도 cascade로 revoke된다.
oracle@swsvrctr:/home/oracle> sqlplus internal
SQL> col grantor format a10
SQL> col grantee format a10
SQL> col table_name format a10
SQL> col table_schema format a10
SQL> col privilege format a25
SQL> grant create user to scott with admin option; ==> with admin option으로 권한 부여후
SQL> connect scott/tiger
SQL> grant create user to oracle; ==> 다시 oracle 에게 같은 권한 부여후
SQL> connect internal
SQL> select * from dba_sys_privs
2 where grantee in (‘SCOTT’,’ORACLE’);
GRANTEE PRIVILEGE ADMIN_
———- ————————- ——
ORACLE CREATE USER NO
SCOTT CREATE USER YES
SCOTT UNLIMITED TABLESPACE NO
SQL> revoke create user from scott; ==> scott의 권한을 revoke
SQL> select * from dba_sys_privs
2 where grantee in (‘SCOTT’,’ORACLE’);
GRANTEE PRIVILEGE ADMIN_
———- ————————- ——
ORACLE CREATE USER NO ==> scott의 create user 권한만 revoke되었다.
SCOTT UNLIMITED TABLESPACE NO oracle권한은 그대로
SQL> grant select on dept to oracle with grant option; ==> with grant option 으로 권한 부여후
SQL> connect /
SQL> create user myuser identified by myuser1$
2 default tablespace ts_user1
3 temporary tablespace temp;
SQL> grant select on scott.dept to myuser; ==> 다시 같은 권한을 다른 myuser에게 부여
Grant succeeded.
SQL> select * from all_tab_privs
2 where table_name=’DEPT’;
GRANTOR GRANTEE TABLE_SCHE TABLE_NAME PRIVILEGE GRANTA
———- ———- ———- ———- ————————- ——
SCOTT ORACLE SCOTT DEPT SELECT YES
ORACLE MYUSER SCOTT DEPT SELECT NO
SQL> revoke select on dept from oracle; ==> oracle의 권한을 revoke
Revoke succeeded.
SQL> select * from all_tab_privs ==> with grant option으로 생성된 이하 myuser의 권한도
2 where table_name=’DEPT’; revoke 되었다.
no rows selected
================================================================================================
* Database Auditing : user 사용 시간정보 확인, 동시사용자측정 등 여러가지에 필요
SQL> connect internal
SQL> shutdown immediate
init.ora file에서 편집
audit_trail = true # if you want auditing ==> 주석기호(#) 삭제
SQL> startup
SQL> show parameter audit_trail
NAME TYPE VALUE
———————————— ————– ——————————
audit_trail string TRUE
SQL> audit connect;
SQL> select * from dba_stmt_audit_opts;
USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE
——————– ——————– ————— ———- ———-
CREATE SESSION BY ACCESS BY ACCESS
SQL> connect scott/tiger
SQL> connect scott/fail
SQL> connect internal
SQL> select username,timestamp,action_name,logoff_time,returncode
2 from dba_audit_session;
USERNAME TIMESTAMP ACTION_NAME LOGOFF_TI RETURNCODE
———- ——— ————— ——— ———-
SCOTT 05-JUL-01 LOGOFF 05-JUL-01 0 ==> login 성공하면 0 return
SCOTT 05-JUL-01 LOGON 1017 ==> login 실패한 returncode
SQL> shutdown
파라미터 이전대로 돌려두자(#audit_trail=true : 주석처리)
SQL> startup 하고
SQL> show parameter audit_trail
NAME TYPE VALUE
———————————— ————– ——————————
audit_trail string NONE
================================================================================================
19. Managing Roles
================================================================================================
* Role
resource role에 포함된 권한을 살펴보자
SQL> select * from dba_sys_privs
2 where grantee=’RESOURCE’;
GRANTEE PRIVILEGE ADMIN_
———- ——————– ——
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE INDEXTYPE NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TABLE NO
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE TYPE NO
8 rows selected.
다음은 dev라는 role을 만들어서
SQL> create role dev;
SQL> grant create table,create view to dev;
SQL> grant select on emp to dev;
SQL> connect internal
oracle이라는 user에게 dev,resource role, create session권한 부여
SQL> connect internal
SQL> grant dev to oracle;
SQL> grant resource to oracle;
SQL> grant create session to oracle;
SQL> alter user oracle default role resource; ==> session의 연결 끊김에 상관없이 지속적으로
SQL> grant select_catalog_role to oracle; logon 후 resource role이 enable되게 함
(set 할 필요 없이)
SQL> select segment_name,status from dba_rollback_segs; ==> 현재 session에서 select_catalog_role이
select segment_name,status from dba_rollback_segs disabled됨
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> set role select_catalog_role; ==> set은 현재session에서 role을 사용가능하게 해줌.
이를 위해 이 role은 이미 user에게 grant 되어있어야함.
SQL> select segment_name,status from dba_rollback_segs;
————— ——————————–
SEGMENT_NAME STATUS
SYSTEM ONLINE
RBS1 ONLINE
RBS2 ONLINE
================================================================================================
20. Using National Language Support
================================================================================================
* sysdate format 변경
SQL> connect internal
SQL> select sysdate from dual;
SYSDATE
———
05-JUL-01
SQL> alter session set nls_date_format=’YY/MM/DD:HH24:MI:SS’;
SQL> select sysdate from dual;
SYSDATE
—————–
01/07/05:12:25:37
================================================================================================
* Character set 변경하고 한글명 table 만들기
— 가급적 한글명 table은 만들지 않는것이 좋으나 만들수 없는것은 아니다.
SQL> create table scott.부서 as select scott.dept;
create table scott.부서 as select scott.dept
*
ERROR at line 1:
ORA-00911: invalid character ==> 테이블명이 한글이어서 error난다.
* Database Characterset을 변경해 보자. ==> 매우 조심스러운 작업
(DATA 보존 못할 위험성 있다.backup 필요)
SQL> select * from nls_database_parameters ==> nls_database_parameters 에서 현재 DB의
2 where parameter like ‘%CHARACTERSET%’; characterset관련을 parameter를 확인
PARAMETER VALUE
————————- ——————–
NLS_CHARACTERSET WE8ISO8859P1
NLS_NCHAR_CHARACTERSET WE8ISO8859P1
SQL> select value from v$nls_valid_values ==> 234건의 data가 있다.
2 where parameter like ‘%CHARACTERSET’;
— warning : character set을 변경 할수는 있지만 기존에 들어가있는 데이타에 대해서는 책임 못짐.
a.
SQL> shutdown immediate
SQL> connect internal
SQL> startup mount exclusive;
SQL> alter system enable restricted session;
SQL> alter database open;
b.
SQL> alter database character set ko16ksc5601;
SQL> alter database national chartacter charcter set ko16ksc5601;
— 확인
SQL> select * from nls_database_parameters ==> nls_database_parameters 에서 현재 DB의
2 where parameter like ‘%CHARACTERSET%’; characterset관련을 parameter를 확인
shutdown immediate;
c. .profile edit
NLS_LANG=Amerian_America.us7ascii; export NLS_LANG을
NLS_LANG=korean_korea.ko16ksc5601; export NLS_LANG로 변경
d. Database startup
![[관리] Control file 재생성하기](http://nblog.syszone.co.kr/wp-content/themes/hueman/assets/front/img/thumb-medium-empty.png)
