달력

122021  이전 다음

  •  
  •  
  •  
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  •  

[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.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; <

Posted by Tornado tornado

댓글을 달아 주세요