[Oracle]오라클 어드민 팁
Oracle Administration을 정리하다가 간단히 찾아고 조금이나마 도움이 되시라고 정리해서 올립니다.
너무 단시간에 두서없이 써서 보기도 좋지 않지만 필요하신 분들 심심할때 하나씩
해보세요.(다들 아시는거지만~~)
아래 tips 는 하나의 database에서 작성한 것이 아니므로 각종 정보들(file들의 위치등)이
tip마다 다를 수 있습니다. 각 tip은 개개의 것으로 생각하시고 응용하시기 바랍니다.
혹시 틀린 내용 발견되면 mail주세요. 바로 수정하겠습니다.
편집 이쁘게 못해서 죄송합니다.
나름대로 사연있는 글입니다.
정리하다가 날려먹어서 한 몇일 더 고생해서 작성한겁니다.^^;
님들도 좋은 정보 있으시면 공유하시죠.
================================================================================================
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
================================================================================================
* 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
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
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하고 편집한다.
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
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' 카테고리의 다른 글
| [펌] 「MySQL 5.0」등장, 기업 사용자도 OK! (0) | 2005.10.27 |
|---|---|
| [펌] 동적 SQL의 축복과 저주 (0) | 2005.10.13 |
| [mssql] 계층적 구조 표현하기. (0) | 2005.10.07 |
| 오라클 클라이언트 툴... aquafold.... (0) | 2005.09.26 |
| [펌] window 2000, 2003에서 오라클 삭제하기 (0) | 2005.09.08 |