Oracle Admin1 -2

6. control file.

 

create database DBA9Ixx

 maxlogfiles 32  : log group의 최대 갯수

 maxlogmembers 3 : group당 member 최대 갯수

 maxdatafiles 40 : datafile section의 초기 크기

                                ↓

                             *db_files 초기 parameter 까지 확장 가능

 

select * from v$controlifle_record_section;

 

-Controlfile

 

 *datafile              select * from v$datafile;

                                       _         _

/..../dbs/system01.dbf     |         |

/..../dbs/user01.dbf        |         |

/..../dbs/index01.dbf      _|         |   record_total

                        record_used       |

                                                 _|

 

 *redo logfile      select * from v$log;

               _                    _

group 1    |                    |

group 2  _|                                |   record_total

                        record_used   |

                                    _|

 

control file 내용

select * from v$database;

             "        v$tablespace;

             "        v$datafile;

             "        v$logfile;

             "        v$archived_log;

             "        v$backup;

 

6-9. Pfile 사용시 control file 다중화

 

* pfile

 1) shutdown

 2) copy

 3) parameter 수정

 4) startup

 

* spfile

 1) parameter 수정 (alter system ... scop=spfile)

 2) shutdown

 3) copy

 4) startup

 

기존 Controfile file의 위치 및 이름은?

select * from v$controlfile;

 

 

spfile 생성.

create spfile from pfile;

startup force

show parameter spfile

 

1) alter system

    set control_files='/dbaA/dba/dba9ixx/dbs/ctrl1DBA9Ixx.ctl',

                              '/dbaA/dba/dba9ixx/dbs/ctrl2DBA9Ixx.ctl',

                              '/dbaA/dba/dba9ixx/ORADATA/u02/ctrl02.ctl' scope=spfile;

 

2) shutdown immediate

3) cp dbs/ctrl1DBA9Ixx.ctl ORADATA/u02/ctrl02.ctl

4) startup

    select * from v$controlfile;

 

7. online redo log file

 

select * from log; (group)

select * from logifle; (member)

 

 

* current / active / inactive

 

* log switch wait event

 

 

 

7. 연습문제

 

alter database add logfile member

'/dbaA/dba/dba9i01/dbs/log1b.rdo' to group 1,

'/dbaA/dba/dba9i01/dbs/log2b.rdo' to group 2;

 

select * from v$logfile;

멤버 추가

 

 

alter database add logfile group 3

('/dbaA/dba/dba9i01/dbs/log3a.rdo',

 '/dbaA/dba/dba9i01/dbs/log3b.rdo')

size 1M;

그룹추가

 

 

 

 

8. Tablespace 및 Datafile

 

* Database 저장 단위

 

         logical                               physical

(ORACLE Software)                      (OS file 구조)

 

         database

       tablespace   ← data file

         segment

           extent

        data block    ← os block

 

 

* segment : row 단위의 data 저장 객체     (segment = table)

                    table, index, cluster

* extent : segment에 대한 공간 할당 단위

 

* data block : I/O의 최소 단위 / db_block_size = 40kb.

 

 

                                             _________-> tablespace 안에 data 까지 삭제.

drop tablespace userdata including contents and datafiles;

 

create tablespace userdata

datafile '?/dbs/userdata01.dbf' size 10M,

             '?/dbs/userdata02.dbf' size 10M

extent management local uniform size 16K;

 

create table emp

(

 no number(10),

 name varchar2(20)

) tablespace userdata

  storage (initial 160k);

 --------> extent 크기 재정의

 

create table dept

(

 no number(10),

 name varchar2(20)

) tablespace userdata

  storage (initial 160k);

 

select * from dba_tablespaces;

             "        dba_data_files;

             "        dba_temp_files;

 

 

* extent 할당 : create, insert

       "     해지 : drop, truncate

 예) extent size 16k

 

      create     insert     create      create

          ↓         ↓               ↓             ↓

_____________________________________________

  |     16k    |       16k  |   16k   |   16k      |     free....

---------------------------------------------

                                            ↓                               ↑(x)

                                        drop 하면     ↔  먼저 채우고 free 공간.

 

 

* free extent 관리 방법

 

 8i) dictionary 관리                               

_________________________________

         |▒▒▒|           |▒▒▒▒|                     

---------------------------------      

             free

 

 9i) locally 관리

                              free

__________________________________

  1010..| used  |▒▒▒|  used |▒▒▒|

----------------------------------

    0-free

: tablespace / extent map (bit map)

 

 

 

 

 

 

8-10.

 

select * from dba_tablespaces;

select * from dba_extents

where tablespace_name = upper('userdata');

 

 

 

 

 

 

* dictionary

drop tablespace userdata including contents and datafiles;

 

create tablespace userdata

datafile '?/dbs/userdata01.dbf' size 10M,

             '?/dbs/userdata02.dbf' size 10M

extent management dictionary default storage

(initial 16k next 16k pctincrease 0 maxextents 100 minextents 1);

 

create table emp

(

 no number(10),

 name varchar2(20)

) tablespace userdata;

 

create table dept

(

 no number(10),

 name varchar2(20)

) tablespace userdata;

 

OWNER  SEGMENT_NAME  PARTITION_NAME  SEGMENT_TYPE  TABLESPACE_NAME  EXTENT_ID  FILE_ID  BLOCK_ID  BYTES  BLOCKS  RELATIVE_FNO 

SYS  EMP     TABLE  USERDATA  0  5  2  16384  4  5 

SYS  DEPT     TABLE  USERDATA  0  5  6  16384  4  5 

 

 

create user hr

identified by hr

quota 10M on users (tablespace)

default tablespace users

temporary tablespace temp;

 

 

 

 

 

 

 

 

 

 

 

8-34.

* autoextend

 

alter database

datafile '?/dbs/users01.dbf' autoextend on next 5m maxsize 100M;

             or 3

 

select * from dba_data_files;

 

* resize

 

alter database

datafile 3 resize 20m;

 

* add datafile

 

alter tablespace users

add datafile '?/dbs/user02.dbf' size 10m;

 

 

 

 

 

 

8. 연습문제

1.

c.

create tablespace indexd01

datafile '------------' size 1m autoextend on next 500k maxsize 2m ← autoextned 는 datafile 에서 만드는 것.. 그래서 extend 절에서 만들면 안됨.

             .           

             .

             .