database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Tutorials 8853 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 8853
Oracle 8 - Database Administration (1)
작성자
정재익(advance)
작성일
2001-12-12 21:45
조회수
14,343

02. Admin Tool 이용하기 (Using the Server Manager)

 

[b]Database Administration Tools[/b]

 - server manager line mode 
 - oracle enterprise manager
 - SQL*Loader
 - export or import utility
 - password file utility

[b]Starting Server Manager in Line Mode[/b]

 - on unix : svrmgrl
 - on nt    : svrmgr30

[b]Server Manager Commands [/b]

Command             Description
EXIT                    서버메니저 종료나 SQL워크시트 종료
REMARK              보통 SQL스크립트파일안에 명령문 삽입
SET                     현재의command line의 특성을 바꾸거나 설정 session
SHOW                  현재 영향받은 셋팅을 표시
SPOOL                특정파일로 스풀이 되게, 또는 안되게 함
CONNECT/ DISCONNECT    데이터베이스의 접속을 하거나,끊는다
DESCRIBE           function이나 package, package body, procedure, table, view등의 object를 표시
EXECUTE           PL/SQL문을 실행
SHOW ERRORS   procedure나 package, function의 컴파일시에 발생되는 에러 표시
SHOW PARAMETER   하나 이상의 초기parameters값 표시
SHOW SGA   현재의 인스턴스에대한SGA의 정보 표시 
CONNECT/AS SYSDBA   database에 dba권한으로 접속
ARCHIVE LOG   수동으로 온라인 리두로그파일의 자동적인 아카이빙의 여부설정
RECOVER DATABASE   하나이상의 테이블스페이스의 복구나 전체 데이터베이스의 복구를 수행
STARTUP/SHUTDOWN   현재돌아가고 있는Oracle instance의 시작 종료

 

03. 오라클 인스턴스 다루기 (Managing an Oracle Instance)

 

[b]dba user 로 연결[/b]

 $ svrmgrl

    SVRMGR> CONNECT / AS SYSDBA
    SVRMGR> startup


[b]Password file을 이용하여 권한주기[/b]

 - password file을 만듬.
     $ orapwd file=$ORACLE_HOME/dbs/orapwSID password=aaa1234 entries=5

 - $ORACLE_HOME/dbs/initSID.ora 파일을 수정.
     REMOTE_LOGIN_PASSWORD_FILE=EXCLUSIVE

 - sysoper, sysdba 권한을 준다.
    SVRMGR> grant sysdba, sysoper to asdf ;

 - V$PWFILE_USERS 로 password file의 member을 확인 할 수 있음.
    SVRMGR> select * from v$pwfile_users ;

 - 이제 접속하는 방법은 아래와 같이.
    SVRMGR> connect asdf/aaa1234 as sysdba

[b]Initialization Parameter File 생성하기[/b]

  SVRMGR> CONNECT / AS SYSDBA
  SVRMGR> STARTUP PFILE=$ORACLE_HOME/dbs/initSID.ora

[b]Parameter File Example[/b]

 # Initialization Parameter File : initDBA001.ora
    db_name                 = DBA001
    control_file               = (/DISK1/control01.con, /DISK2/control02.con)
    db_block_size           = 8192
    db_block_buffers       = 2000
    shared_pool_size       = 30000000
    log_buffer                = 64K
    processes                = 50
    db_files                   = 100
    log_files                   = 10
    max_dump_file_size   = 10240
    background_dump_dest    = (/home/disk3/dba001/BDUMP)
    user_dump_dest          = (/home/disk3/dba001/UDUMP)
    core_dump_dest          = (/home/disk3/dba001/CDUMP)
    rollback_segments       = (r01, r02, r03, r04, r05, r07, r07, r08)
    ...


[b]단계별 Startup[/b]

 - SVRMGR> startup nomount   -- instance생성됨, parameter file을 읽음.
 - SVRMGR> startup mount       -- instance에 대한 control file을 연다.
 - SVRMGR> startup open        -- instance에 대한 모든 data file과 redo log file을 연다.

  * startup nomount 상태에서 mount 로 가려면
    ALTER DATABASE MOUNT 명령한다.
  * nomount 상태에서 open 상태로 바로 갈 수 없음
  * mount 상태에서 open 으로 가려면
    ALTER DATABASE OPEN 명령하면된다.

[b]Starup command[/b]

  SVRMGR> STARTUP PFILE=/DISK1/initSID.ora


[b]Shutdown command[/b]

 - SVRMGR> SHUTDOWN NORMAL
     : 모든 user들이 disconncet 할 때 까지 기다린다.

 - SVRMGR> SHUTDOWN TRANSACTIONAL
     : user들이 commit 이나 rollback 할 때 까지 기다린다.

 - SVRMGR> SHUTDOWN IMMEDIATE
     : 진행중이던 transaction을 rollback 하면서 shutdown. 가장 많이 사용한다.

 - SVRMGR> SHUTDWON ABORT
     : 강제로 모든 것을 종료(정전과 같은 상태)한다. startup 속도가 가장느리고, shutdown 속도는 가장 빠르다.

  위의 사항중 첫째부터 세번째까지는 정상적인 종료이므로 check point를 수행한다.

[b]현재의 Parameter 값들을 보기[/b]


 SVRMGR> SHOW PARAMETER control
    : control 이 들어간 parameter 들만 보여준다.


 SVRMGR> SELECT name FROM v$parameter
             2  WHERE name LIKE '%control%' ;


[b]Dynamic Initialization Parameters[/b]


 - ALTER SESSION SET SQL_TRACE=true ;
      : 현재 session에서만 적용된다.

 - ALTER SYSTEM SET TIMED_STATISTICS=true ;
      : shutdown 할 때까지 적용된다.

 - ALTER SYSTEM SET SORT_AREA_SIZE=131072 DEFERRED ;
      : 이미 접속되어있는 user는 적용이 안되고 이 명령 이후에 접속한 user에만 적용함

[b]database access를 제한하기.[/b]

  SVRMGR> STARTUP RESTRICT


  * restrict enable, disable 하기
  SVRMGR> ALTER ENABLE RESTRICTED SESSION ;


  * restrict 상태를 볼 수 있는 view
  SVRMGR> SELECT logins FROM v$instance ;


[b]SESSION 죽이기[/b]

 - V$SESSION의 sid, serial#를 확인한다.
    SVRMGR> SELECT sid, serial# FROM v$session
               2  WHERE username='ASDF' ;


 2) sid=7, serial#=15 이면 아래와 같이 죽인다.
     ALTER SYSTEM KILL SESSION '7, 15' ;

 

04. 데이터베이스 생성하기 (Creating Databse)

 

[b]새로운 init file을 만들고 수정한다.[/b]

 $ cp init.ora $ORACLE_HOME/dbs/initSID.ora

[b]Instance를 startup 한다.[/b]

 $ svrmgrl
   SVRMGR> STARTUP NOMOUT PFILE=initSID.ora

   ORACLE instance started.

[b]DATABASE를 생성한다.[/b]

   CREATE DATABASE SID
     MAXLOGFILES 5
     MAXLOGMEMBERS 5
     MAXDATAFIlES 100
     MAXLOGHISTORY 100

   LOGFILE
     GROUP 1 ('/DISK3/log1a.rdo','/DISK4/log1b.rdo') SIZE 2 M,
     GROUP 2('/DISK3/log2a.rdo','/DISK4/log2b.rdo') SIZE 2 M

   DATAFILE
     '/DISK1/system01.dbf' size 100M autoextend on

   CHARACTER SET KO16KSC5601 

   NATIONAL CHARACTER SET KO16KSC5601;

 

05. 데이터 딕셔너리 뷰와 표준 패키지들 (Data Dictionary Views and Stand Packages)

 

Base Tables and Data Dictionary Views

 

- Data Dictionary Views

base table 에 대한 간단한 정보를 갖고 있다.

catalog.sql file로 생성한다.

 

- Base Tables

sql.bsq file로 생성한다.

 

Data Dictionary 의 구성

 

DBA_xxx : object of the entire database

ALL_xxx : object can be accessed by the user

USER_xxx : objects owned by the use

 

아래로 갈수록 하위임.

 

Data Dictionary: Views Examples and Categories

 

Views Description

dictionary 일반적인 overview

dict_columns 칼럼에 대한 overview

dba_tables Information related to the user objects such as

dba_objects tables, constraints, large objects and columns

dba_lobs

dba_tab_columns

dba_constraints

dba_users Information about user privileges and roles

dba_sys_privs

dba_roles

 

SVRMGR> SELECT *

2 FROM dictionary

3 WHERE table_name LIKE '%TABLE%' ;

: TABLE에 관련된 모든 data dictionary 정보를 보여줌.

 

SVRMGR> SELECT column_name, comments

2 FROM dict_columns

3 WHERE table_name='DBA_TABLES' ;

: Dictionary에 대한 정보를 보여줌.

 

* Data Dictionary View를 생성하려면...

$ORACLE_HOME/rdbms/admin 에 있는 caelog.sql, catproc.sql을 실행 시킨다.

 

Administrative Scripts

 

$ORACLE_HOME/rdbms/admin 에 있다.

 

cat*.sql : catalog and data dictionary information

dbms*.sql : database package specifications

prvt*.plb : wrapped database package code

utl*.sql : Views and tables for database utilities

 

Oracle-Supplied Packages

 

- DBMS_LOB : Provides routines for operations on BLOB and CLOB datatypes

- DBMS_SESSION : Generates SQL commands like ALTER SESSION or SET ROLE

(Package procedures : SET_ROLE, SET_SQL_TRACE, SET_NLS)

- DBMS_UTILITY : Provides various utility routines

(Package procedures : ANALYZE_SCHEMA, COMPILE_SCHEMA, DB_VERSION)

- DBMS_SPACE : Provides segment space availability information

(Package procedures : UNUSED_SPACE, FREE_BLOCKS)

- DBMS_ROWID : Provides ROWID information

(Package procedures : ROWID_INFO)

- DBMS_SHARED_POOL : Keeps and unkeeps information in the shared pool

(Package procedures : KEEP, UNKEEP)

 

Stored Objects에 관한 정보를 갖고 있는 Data Dictionary

 

DBA_OBJECTS (owner, object_name, object_type, status)

 

SVRMGR> SELECT object_name, object_type, status

2 FROM dba_objects

3 WHERE object_name like 'DBMS_%' ;

 

* DESCRIBE command

SVRMGR> DESC dbms_session.set_role procedure SET_ROLE (ROLE_CMD VARCHAR2) ;

[/pre]

 

06. 컨트롤 파일 다루기 (Maintaining the Control File)

 

[b]Control File의 구성요소는 아래와 같다.[/b]

   - Database의 이름
   - Data file의 위치
   - Redo log file의 위치
   - Tablespace의 이름
   - Current log sequence number(현재 log 순서의 수) ==> Redo log File의 번호
   - Checkpoint에 대한 정보
   - Log history의 정보
   - Backup 정보

   cf) Control File의 2가지
     Reusable      : 다시 사용 가능한 정보. Recovery Manager(RMAN)을 사용하기 위한 backup정보 저장한다.
     Not reusable : 다시 사용 가능하지 못한 정보

[b]Control file의 size를 변경하기위한  parameter들은 아래와 같다.[/b]

   - MAXLOGFILES : 로그파일의 최대겟수
   - MAXLOGMEMBERS : 로그맴버의 최대갯수
   - MAXLOGHISTORY
   - MAXDATAFILES : 데이터파일의 최대갯수
   - MAXINSTANCES: 인스턴스의 최대갯수

[b]Conrol file의 정보를 갖고있는 Data Dictionary[/b]

   - V$CONTROLFILE : name

     SVRMGR> SELECT name  FROM v$controlfile ;
          
   - V$PARAMETER : name, value

     SVRMGR> SELECT name, value FROM v$parameter
                 2   WHERE name = 'control_files' ;
          
   - V$CONTROLFILE_RECORD_SECTION : type, records_size, records_total, records_used

     SVRMGR> SELECT type, records_size, records_total, records_used
                 2  FROM v$controlfile_record_section
                 3  WHERE type = 'DATAFILE' ;

 oracle7에는 v$controlfile_record_section view가 없습니다.

[b]Multiplexing the Control file[/b]

  - control file이 손상되었을 때를 대비해서 control file은 2개이상 만드는 것을 권유한다.
     control file을 2개로 setting해놓으면 기록될 때 똑같은 내용을 2개의 file에 기록하는데,이는 backup의 한 방법이다.
     
  - 방법
     1. shutdown immediate; ( 데이터베이스를 shutdown 시킨다. )
     2. host로 가서 file을 copy한다.( unix상태에서 작업 )
     3. init.ora file에 있는 control_files parameter를 수정한다.
         control_files=(/DISK1/control01.ctl, /DISK2/control02.ctl)
     4. startup ( 데이터베이스를 가동 시킨다. )

[b]연습[/b]

1. 현재 database의 control file의 구성을 확인.

    SVRMGR> select * from v$controlfile ;

    - 데이터 딕셔너리 참조 : v$controlfile

2. control file이 없거나 손상되었다고 가정하고, 오라클을 기동

   SVRMGR> shutdown immediate;

   SVRMGR> host
    - ( unix 상태로 전환 )

   $ mv $ORACLE_HOME/DATA/DISK1/cntrlSID.ctl   $ORACLE_HOME/DATA/DISK1/cntrlSID.bak 
     - (  control file을 move시키며 파일명을 cntrlSID.ctl --> cntrlSID.bak 으로 바꾼다. 이는 손상 된 것 같이 만든다. )

   $ exit

   SVRMGR> startup    ==> Database mount error! (ORA-00205)
     - ( 데이터베이스가 기동할 때 control file을 찾는데 이를 move시켜기 때문에 찾지 못하여 에러가 난다. )

   SVRMGR> shutdown immediate;

   SVRMGR> host

   $ mv $ORACLE_HOME/DATA/DISK1/cntrlSID.bak $ORACLE_HOME/DATA/DISK1/cntrlSID.ctl
     - ( 데이터베이스가 기동할 때 control file을 찾는데 이를 다시 원위치하여 기동할 수 있도록 조치한다. )

   SVRMGR> startup
     - ( 성공적을 수행 된다. )

3. control file을 하나 더 추가하여 비상시 대비 하자.

   SVRMGR> shutdown immediate;

   SVRMGR> host

   $ cp $ORACLE_HOME/DATA/DISK1/cntrlSID.ctl $ORACLE_HOME/DATA/DISK1/cntrl2SID.ctl
   $ vi  $ORACLE_HOMD/dbs/initSID.ora

     control_files = ($ORACLE_HOME/DATA/DISK1/cntrlSID.ctl,
                           $ORACLE_HOME/DATA/DISK1/cntrl2SID.ctl)
     --> initSID.ora를 수정한다.
   $ exit

   SVRMGR> startup
     - ( 데이터베이스를 기동한다. )

   SVRMGR> select * from v$controlfile ;

4. database에 생성할 수 있는 data file의 maximum 개수를 확인.


   SVRMGR> select records_total from v$controlfile_record_section
               2  where type = 'DATAFILE' ;
     - ( 데이터 딕셔너리 참조 : v$controlfile_record_section )

 

07. 리두 로그파일 다루기 (Maintaining the Redo Log File)

 

[b]Archiving Redo Log Files[/b]

   Redo log file이 완전히 찼을 때, 모든 정보를 rollback하고 처음으로 되돌아가서 기록되지만 archive log mode이면
   복사파일을 먼저 만들어 보존하고 log file에 다시 기록한다.

   SVRMGR> ALTER DATABASE ARCHIVELOG ;     ( archive log mode로 설정 )

 - archive log 정보를 보는 방법
   SVRMGR> ARCHIVE LOG LIST ;
   SVRMGR> SELECT name, log_mode FROM v$database ;
   SVRMGR> SELECT archiver FROM v$instance ;

V$THREAD : Group에 대한 정보

   SVRMGR> SELECT groups, current_gruop#, sequence#
               2  FROM v$thread ;

V$LOG : gruop별 정보

   SVRMGR> SELECT group#, sequence#, bytes, members, status
               2  FROM v$log ;

V$LOGFILE : group과 member에 대한 정보

   SVRMGR> SELECT group#, status, member 
               2  FROM v$logfile ;

[b]Log switch와 check point 조작[/b]

   SVRMGR> ALTER SYSTEM SWITCH LOGFILE; - Log switch를 강제로 시킴

   SVRMGR> ALTER SYSTEM CHECKPOINT; - checkpoint를 강제로 발생시킴

[b]Redo Log group 추가[/b]

   SVRMGR> ALTER DATABASE ADD LOGFILE
               2  ('/DISK3/log3a.rdo', '/DISK4/log3b.rdo') 
               3  size 1M ;

[b]Redo Log Member 추가[/b]

   SVRMGR> ALTER DATABASE ADD LOGFILE MEMBER
               2  '/DISK4/log1b.rdo' TO GROUP 1,
               3  '/DISK4/lgo2b.rdo' TO GROUP 2 ;

[b]Redo Log File 위치 변경 방법[/b]

   1. shutdown immediate
   2. redo log file을 새로운 위치에 copy한다.
   3. startup mount
   4. alter database rename file '_____' to '______' ;
   5. alter database open ;

[b]DROP LOGFILE GROUP : Redo log group 제거[/b]

   SVRMGR> ALTER DATABASE DROP LOGFILE
               2  GROUP 3 ;
   --> 현재 group 3이 사용 중이면 삭제 불가! shutdown 시킨 후 삭제해야 한다.

[b]DROP LOGFILE MEMBER : Redo log member 제거[/b]

   SVRMGR> ALTER DATABASE DROP LOGFILE MEMBER
               2  '/DISK4/log2b.dbf' ;

[b]CREAR LOGFILE[/b]

  - log file이 깨졌을 때, log file은 2개 이상있어야 하기 때문에..2개의 log file이 있다면 drop이
     되지 않는다. 그래서 crear로 만들어 줘야 한다.
  - file을 새로 만들면서 clear한다.

   ALTER DATABASE [database]
     CLEAR [UNARCHIVED] LOGFILE
       {GROUP integer | ('filename'[, 'filename']...)}
     [,{GROUP integer | ('filename'[, 'filename']...)}]...

   SVRMGR> ALTER DATABASE CLEAR LOGFILE
        2  'DISK/3/log2a.rdo' ;

[b]연습[/b]

1. 현재 database에의 redo log group의 존재수

   SVRMGR> select * from v$logfile ;
   SVRMGR> select * from v$log ;
     - ( 데이터 딕셔너리 참조 : v$logfile, v$log )

2. 데이터베이스는 어떤 데이터베이스 모드로 구성 있는지? archiving이 enable 되어 있는지 확인

   SVRMGR> select log_mode from v$database ;
   SVRMGR> select archiver from v$instance ;
   
   또는,
   SVRMGR> archive log list
     - ( 데이터 딕셔너리 참조 : v$database, v$instance )

3. 새로운 Redo log 멤버들을 각각의 그룹에 추가하고 만약 group 1이 log1a.rdo라는 기존의 파일을 가지고 있고 log1b.rdo라는 새로운 멤버를 추가하려고 한다.

   SVRMGR> alter database add logfile member
              2  '$ORACLE_HOME/DATA/DISK3/log1b.rdo' to group 1,
              3  '$ORACLE_HOME/DATA/DISK3/log2b.rdo' to group 2 ;
   SVRMGR> select * from v$logfile ;


4. 새로은 Redo log 그룹을 directory DISK4에 추가한다.

   SVRMGR> alter database add logfile
               2  ('$ORACLE_HOME/DATA/DISK4/log3a.rdo',
               3   '$ORACLE_HOME/DATA/DISK4/log3b.rdo')
               4  size 150k ;
   SVRMGR> select * from v$logfile ;
   SVRMGR> select * from v$log ;


5. Redo log 멤버 log1b.rdo 와 log2b.rdo를 directory DISK4로 옮기자

   SVRMGR> shutdown
   SVRMGR> host
   $ cp $ORACLE_HOME/DATA/DISK3/log1b.rdo $ORACLE/HOME/DATA/DISK4/log1b.rdo
   $ cp $ORACLE_HOME/DATA/DISK3/log2b.rdo $ORACLE/HOME/DATA/DISK4/log2b.rdo
   $ exit
   SVRMGR> startup mount
   SVRMGR> alter database rename file
               2  '$ORACLE_HOME/DATA/DISK3/log1b.rdo','$ORACLE_HOME/DATA/DISK3/log2b.rdo' to
               3  '$ORACLE_HOME/DATA/DISK4/log1b.rdo','$ORACLE_HOME/DATA/DISK4/log2b.rdo' ;
   SVRMGR> select * from v$logfile ;
   SVRMGR> alter database open ;
   SVRMGR> host
   $ rm $ORACLE_HOME/DATA/DISK3/log1b.rdo
   $ rm $ORACLE_HOME/DATA/DISK3/log2b.rdo


6. log switch를 강제로 실행하자

   SVRMGR> select * from v$log ;   ==> current log file 확인
   SVRMGR> alter system switch logfile ;
   SVRMGR> select * from v$log ;   ==> current log file 확인


7. 문제 4에서 생성된 로그 그룹을 제거

   SVRMGR> alter database drop logfile group 3 ;
      - ( drop을 했지만 에러가 나면 group 3가 현재 사용중일것이다. shutdown하고 다시 startup한다음 drop한다. )
   SVRMGR> select * from v$log ;
   SVRMGR> host
   $ rm $ORACLE_HOME/DATA/DISK4/log3a.rdo
   $ rm $ORACLE_HOME/DATA/DISK4/log3b.rdo

 

09. 테이블 스페이스와 데이터 파일 다루기 (Maintaining Tablespace and Data files)

 

[b]Logical Database Structure[/b]

 - database
 - tablespace
 - segment
 - extent
 - block

[b]SYSTEM and NON-SYSTEM TABLESPACE[/b]

 - SYSTEM Tablespace : data dictionary 정보와 SYSTEM rollback segment을 가진다.
 - NON-SYSTEM Tablespace : Rollback segments, Temporary segments, data, index등을 가진다.

[b]CREATE TABLESPACE[/b]

   CREATE TABLESPACE tablespace
      DATAFILE filespec [autoextend_clause],filespec [autoextend_clause]]...
      [MINIMUM EXTENT integer [K|M]]
      [DEFAULT storage_clause]
      [PERMANENT|TEMPOARY]    --> default PERMANENT
      [ONLINE|OFFLINE]               --> default ONLINE


   예) CREATE TABLESPACE data
        DATAFILE '/DISK4/app01.dbf' SIZE 100M,
                       '/DISK5/app02.dbf' SIZE 100M
        MINIMUM  EXTENT 500K
        DEFAULT  STORAGE (INITIAL 500K NEXT 500K
                       MAXEXTENTS 500 PCTINCREASE 0) ;


[b]  - STORAGE PARAMETERS[/b]

     * INITIAL : 첫째 extent의 크기를 정한다. 최소 크기는 2 blocks ( 2 * DB_BLOCK_SIZE )
           default는 5 bolcks ( 5 * DB_BLOCK_SIZE )
     * NEXT : 다음 extent의 크기를 정한다. 최소 크기는 1 block
           default는 5 bolcks ( 5 * DB_BLOCK_SIZE )
     * MINEXTENTS : segment가 생성되었을 때 할당되는 extent의 갯수.
           default는 1개
     * PCTINCREASE n : 다음에 extent가 생성될 때 이전 extent보다  n% 증가된 크기 ( PCT: percent )
           default는 50
     * MAXEXTENTS : segment가 갖을 수 있는 extent의 최대 수

[b]Temporary Tablespace[/b]

   CREATE TABLESPACE DATA
            DATAFILE '/DISK2/DATA01.dbf' SIZE 50M
            MINIMUM EXTENT 1M
            DEFAULT STORAGE (INITIAL 2M NEXT 2M
                          MAXEXTENTS 500 PCTINCREASE 0)
            TEMPORARY ;

[b]TABLESPACE의 크기 설정 (data file을 추가)[/b]

   ALTER TABLESPACE DATA
      ADD DATAFILE '/DISK5/DATA02.dbf' SIZE 200M ;

[b]DATA FILE이 FULL되면 자동으로 DATAFILE을 증가한다.[/b]

   ALTER TABLESPACE DATA
      ADD DATAFILE '/DISK6/app04.dbf' SIZE 200M
             AUTOEXTEND ON NEXT 10M
             MAXSIZE 500M ;


   * 3가지 방법이 있다.
     1. CREATE DATABASE을 한다.
     2. CREATE TABLESPACE DATAFILE을 한다.
     3. ALTER TABLESPACE ADD DATAFILE을 한다.

[b]현재 DATAFILE의 크기를 다시바꾸는(resize) 방법[/b]

  ALTER DATABASE DATAFILE '/DISK5/app02.dbf' RESIZE 200M ;

[b]Changing the Storage Settings[/b]

   ALTER TABLESPACE DATA
             MINIMUM EXTENT 2M ;


   ALTER TABLESPACE DATA
        DEFAULT STORAGE (INITIAL 2M NEXT 2M
                                      MAXEXTENTS 999) ;


[b]Tablespace OFFLINE/ONLINE[/b]

   - tablespace가 만들어지면 초기값(default)이 ONLINE이다.
   - OFFLINE이 되면 다른 사용자의 access가 불가능하다.
   - SYSTEM tablespace는 OFFLINE이 불가능하다.
   - transaction이 끝나지 않은 tablespace는 OFFLINE 불가능하다.

  ALTER TABLESPACE tablespace
     { ONLINE | OFFLINE [NORMAL|TEMPORARY|IMMEDIATE] }
   - Normal : checkpoint를 적용시키고 offline한다.
   - Temporary : datafile 중에서 online datafile에만 checkpoint를 적용시키고 offline한다.
   - Immediate : checkpoint 없이 offline한다.


  예) ALTER TABLESPACE DATA OFFLINE NORMAL; 특별한 경우가 아닌이상 이렇게 써라.NORMAL은 default이므로 생략가능

[b]Moving Data File : ALTER TABLESPACE[/b]

   - 반드시 offline 한 상태에서 한다.
   - target data file이 반드시 존재해야 한다.

   ALTER TABLESPACE DATA
             RENAME DATAFILE '/DISK4/DATA01.dbf'
             TO '/DISK5/DATA01.dbf' ;

[b]Moving Data File : ALTER DATABASE[/b]

   - 반드시 database가 mount 상태이어야 한다.
   - target data file이 반드시 존재해야 한다.
   - shutdown하고 host상태에서 datafile을 제거해야 한다.

   ALTER DATABASE 
          RENAME FILE '/DISK1/system01.dbf'
          TO '/DISK2/system01.dbf' ;

[b]READ-ONLY Tablespace 상태[/b]

   - 오직 select만 할 수 있다.
   - CREATE는 안되고... DROP은 할 수 있다.
   - 사용자들이 data변경을 못함, backup과 recovery가 쉽다.

   ALTER TABLESPACE DATA READ ONLY;

   ALTER TABLESPACE DATA READ WRITE;   -- read only 상태를 다시 read write상태로 바꿔준다.

  * 주의해야 하는점
     - tablespace가 반드시 online상태여야 한다.
     - active transaction이 허용되지 않아야 한다.
     - tablespace가 active rollback segment를 갖고 있으면 안된다.
     - online backup중엔 못한다.

[b]DROP TABLESPACE[/b]

   - file 삭제는 host에 나가서 삭제를 해야 한다.

   DROP TABLESPACE DATA
   INCLUDING CONTENTS ;

   * including contents를 안썼을 때, tablespace가 비워져 있어야만 drop이 된다.
      including contents는 데이터가 들어 있어도 tablespace를 삭제하겠다는 뜻이다.

[b]DBA_TABLESPACES : tablespace 정보를 갖고 있음[/b]

   - TABLESPACE_NAME,
      NEXT_EXTENT,
      MAX_EXTENTS,
      PCT_INCREASE,
      MIN_EXTLEN,
      STATUS,
      CONTENTS

   SVRMGR> SELECT tablespace_name, initial_extent, next_extent,
               2  max_extents, pct_increase, min_extlen
               3  FROM dba_tablespaces ;

[b]DBA_DATA_FILES : FILE에 관한 정보를 갖고 있다.[/b]

   - FILE_NAME, TABLESPACE_NAME, BYTES, AUTOEXTENSIBLE, MAXBYTES, INCREMENT_BY


   SVRMGR> SELECT file_name, tablespace_name, bytes, autextensible, maxbytes, increment_by
               2  FROM dba_data_files ;

[b]Contol File 정보[/b]

   - V$DATAFILE      : ts#, name, file#, rfile#, status, enabled, bytes, create_bytes
   - V$TABLESPACE : ts#, name

   SVRMGR> SELECT d.file#, d.name, d.status, d.enabled, d.bytes, d.create_bytes, t.name
               2  FROM v$datafile d, v$tablespace t
               3  WHERE t.ts# = d.ts# ;

[b]연습[/b]

1. 현재의 Tablespace와 Data file들을 확인

$ sqlplus system/manager
SQL> select * from dba_tablespaces ;
SQL> select file_name, tablespace_name, bytes
     2  from dba_data_files ;


2. DATA01 tablespace의 size를 늘이기 위해, datafile을 하나 추가

SQL> alter tablespace data01
     2  add datafile '$ORACLE_HOME/DATA/DISK6/data01b.dbf' size 500k ;
SQL> select file_name, tablespace_nmae, bytes
     2  from dba_data_files ;


3. 문제2 에서 추가한 datafil의 size를 1M 로 resize 

SQL> alter database datafile
     2  '$ORACLE_HOME/DATA/DISK6/data01b.dbf'
     3  resize 1M ;
SQL> select file_name, tablespace_name, bytes
     2  from dba_data_files ;


4. 문제2 에서 추가한 datafile의 size가 자동적으로 extend 될 수 있도록 하자

SQL> alter database datafile
     2  '$ORACLE_HOME/DATA/DISK6/data01b.dbf'
     3  autoextend on next 100k maxsize 2m ;
SQL> select file_name, tablespace_name, bytes, autoextensible
     2  from dba_data_files ;

5. INDX01 tablespace의 datafile을 DISK6으로 옮기기

SQL> alter tablespace indx01 offline ;
SQL> select name, status from v$datafile ;
SQL> host
$ mv $ORACLE_HOME/DATA/DISK3/indx01.dbf $ORACLE_HOME/DATA/DISK6/indx01.dbf
$ exit
SQL> alter tablespace indx01 rename datafile
     2  '$ORACLE_HOME/DATA/DISK3/indc01.dbf'
     3  to '$ORACLE_HOME/DATA/DISK6/indx01.dbf' ;
SQL> alter tablespace indx01 online ;
SQL> select name, status from v$datafile ;


6. RONLY Tablespace를 read only로 바꾸고, 추가적인 테이블을 생성하고 무슨 일이 발생하며 이유는 무엇인가?

SQL> create table t1(t1 number) tablespace ronly ;
SQL> alter tablespace ronly read only ;
SQL> select name, enabled, status from v$datafile ;
SQL> create table t2(t2 number) tablespace ronly ;    ==> error 발생 확인!


7. RONLY Tablespace를 삭제

SQL> drop tablespace ronly including contents ;
SQL> select * from v$tablespace ;
SQL> host
$ rm $ORACLE_HOME/DATA/DISK1/ronly.dbf

 

11. 롤백 세그먼트 다루기 (Managing Rollback Segments)

 

- Rollback Segment : before image 저장

 

Rollback Segment의 목적

 

- Transaction Rollback

- Transaction Recovery

- Read Consistency : 일괄성을 위해서...

 

Read-Consistency

 

* Select 문장이 실행되는 도중에 데이터가 변경되더라도 변경전 data를 불러오게 일괄성을 준다.

 

* SET TRANSACION READ ONLY 명령으로...

Read only 모드이면서 일괄성있는 데이터를 보여주게된다.

 

--> 이 모드 일 때, select 시간이 너무 길어서 그 시간중에 transaction이 너무 많이 일어나면

ORA_01555 SNAPSHOT TOO OLD 에러가 발생할 수 있다.

이 에러가 발생하면 rollback segment의 갯수를 늘려주면 된다.

 

Creating Rollback Segments

 

CREATE [PUBLIC] ROLLBACK SEGMENT rollback_segment

[TABLESPACE tablespace ]

[STORAGE ([INITIAL integer [K|M]]

[NEXT integer [K|M]]

[MINEXTENTS integer]

[MAXEXTENTS ]

[OPTIMAL {integer [K|M]|NULL}] )

]

* PCTINCREASE 는 사용 못함.

* MINEXTENTS >= 2

 

예) CREATE ROLLBACK SEGMENT rbs01

TABLESPACE rbs

STORAGE (INITIAL 100K NEXT 100K OPTIMAL 4M

MINEXTENTS 20 MAXEXTENTS 100) ;

 

Rollback Segments ONLINE 방법

 

- Rollback Segment는 create하면 offline 상태이다.

 

1) ALTER ROLLBACK SEGMENT rbs01 ONLINE ;

--> shutdown 하면 다시 OFFLINE으로 된다.

 

2) initSID.ora file에서 ROLLBACK_SEGMENTS=(rbs01)을 추가하면 된다.

--> startup할 때마다 항상 적용된다.

 

Rollback Segment Storage 변경

 

ALTER ROLLBACK SEGMENT rollback_segment

[STORAGE ( [NEXT integer [K|M]]

[MINEXTENTS integer]

[MAXEXTENTS ]

[OPTIMAL ] )]

 

예) ALTER ROLLBACK SEGMENT rbs01

STORAGE (MAXEXTENTS 200) ;

 

Rollback Segment Deallocate 하기

 

예) ALTER ROLLBACK SEGMENT rbs01

SHRINK TO 4M ;

 

Rollback Segment OFFLINE 방법

 

- transaction이 끝날 때까지 대기하고 있다가 모든 transaction이 끝나면 OFFLINE이 된다.

- 새로운 transaction을 허용하지 않는다.

 

예) ALTER ROLLBACK SEGMENT rbs01 OFFLINE ;

 

Rollback Segment DROP 하기

 

- DROP하기 전에는 반드시 OFFLINE을 해야 한다.

 

예) DROP ROLLBACK SEGMENT rbs01 ;

 

DBA_ROLLBACK_SEGS view : Rollback Segment 정보를 갖고 있는 view

 

column :

SEGMENT_ID

SEGMENT_NAME

TABLESPACE_NAME

OWNER (PUBLIC or SYS)

STATUS (ONLINE or OFFLINE)

 

SVRMGR> SELECT segment_name, tablespace_name, owner, status

2 FROM dba_rollback_segs ;

 

Rollback Segment Statistics

 

V$ROLLNAME : usn, name

V$ROLLSTAT : usn, extents, rssize, xacts, optsize, hwmsize, aveactive, status, curext, curblk

 

- XACTS : 현재 rollback segment를 사용하고 있는 transaction 수

 

SVRMGR> SELECT n.name, s.extents, s.rssize, s.optsize,

2 s.hwmsize, s.xacts, s.status

3 FROM v$rollname n, v$rollstat s

4 WHERE n.usn = s.usn ;

 

Rollback Segment: Current Activity

 

V$SESSION : saddr, username, sid, serial#

V$TRANSACTION : ses_addr, xidusn, ubafil, ubablk, ubasqn, ubarec, status, used_ublk, used_urec

 

SVRMGR> SELECT s.username, t.xidusn, t.ubafil, t.ubablk, t.used_ublk

2 FROM v$session s, v$transaction t

3 WHERE s.saddr = t.ses_addr ;

 

Blocking Session 찾는 방법

 

SVRMGR> SELECT s.sid, s.serial#, t.start_time, t.xidusn, s.username

2 FROM v$session s, v$transaction t, v$rollstat r

3 WHERE s.saddr = t.ses_addr

4 AND t.xidusn = r.usn

5 AND ((r.curext = t.start_uext-1) OR

6 ((r.curext = r.extents-1) AND t.start_uext=0)) ;

--> 첫 번째 extent가 blocking 되었을 땐 5LINE에서의 조건이 성립되지 않기 때문에...

6LINE 조건을 추가한다.

 

연습

 

현재 database에 몇개의 rollback segment가 존재하는지 조회하십시오.

 

$ sqlplus system/manager

SQL> select * from dba_rollback_segs ;

 

rbs tablespace에 rbs03, rbs04 란 이름으로 rollback segment를 생성하십시오. (storage는 initial 10k next 10k minextents 2 optimal 20k 를 이용하십시오)

 

SQL> create rollback segment rbs03

2 tablespace rbs

3 storage (initial 10k next 10k minextents 2 optimal 20k) ;

SQL> create rollback segment rbs04

2 tablespace rbs

3 storage (initial 10k next 10k minextents 2 optimal 20k) ;

 

rollback segment들의 status를 조회하고, offline 상태인 rollback segment들을 online 상태로 만들어 보십시오.

 

SQL> select segment_name, tablespace_name, status

2 from dba_rollback_segs ;

SQL> alter rollback segment rbs03 online ;

SQL> alter rollback segment rbs03 online ;

 

scott user 로 접속한 후 transaction 을 수행하고, 어느 rollback segment가 그 transaction 에 의해서 사용되고 있는지 확인하십시오.

 

$ sqlplus scott/tiger

SQL> create table test(name char(30)

2 storage (initial 100k)

3 tablespace data01 ;

SQL> insert into test values('aaaaaaaa') ;

 

다른 터미날 윈도우를 열어서,

 

$sqlplus system/manager

SQL> select name, extents

2 from v$rollname n, v$rollstat s

3 where n.usn = s.usn

4 and s.xacts > 0 ;

 

scott user로 접속한 후, transaction을 수행하여 rollback segment가 shrink 되는지 확인하라.

 

$ sqlplus scott/tiger

SCOTT.SQL> set transaction use rollback segment rbs03 ;

SCOTT.SQL> begin

2 for i in 1..500 loop

3 insert into test values('aaaaaaaaaa') ;

4 end loop ;

5 end ;

6 /

 

다른 터미날 윈도우를 열어서, rollback segment의 사용을 monitoring 한다.

 

$ sqlplus system/manager

SYSTEM.SQL> select name, extents

2 from v$rollname n, v$rollstat s

3 where n.usn = s.usn

4 and s.xacts > 0 ;

 

SCOTT.SQL> rollback ;

 

SYSTEM.SQL> select name, extents, xacts, shrinks

2 from v$rollname n, v$rollstat s

3 where n.usn = s.usn ;

==> rbs03의 xacts와 extents 확인!!!

SYSTEM.SQL> alter rollback segment rbs03 shrink ;

SYSTEM.SQL> select name, extents, xacts, shrinks

2 from v$rollname n, v$rollstat s

3 where n.usn = s.usn ;

==> rbs03의 extents가 shrink 되었는지 확인!!!

 

rbs03 와 rbs04 rollback segment 에 active transaction 이 없는지 확인한 후, DROP 하십시오.

 

$ sqlplus system/manager

SQL> select name, extents, xacts, shrinks

2 from v$rollname n, v$rollstat s

3 where n.usn = s.usn ;

==> xacts 가 0 인지 확인!!!

SQL> alter rollback segment rbs03 offline ;

SQL> alter rollback segment rbs04 offline ;

SQL> drop rollback segment rbs03 ;

SQL> drop rollback segment rbs04 ;

[/pre]

 

11. 템포러리 세그먼트 다루기 (Managing Temporary Segments)

 

[b]Temporary Segment Usage[/b]

   ; Temporary segment는 아래의 문장을 수행할 때 사용된다.
     SELECT ... ORDER BY
     CREATE INDEX
     SELECT DISTINCT
     SELECT ... GROUP BY
     SELECT ... UNION
     
   * size는 initial parameter SORT_AREA_SIZE 에서 설정한다.

[b]Temporary Segment 의 type (TEMPORARY, PERMANENT)[/b]

   1) Temporary segments in a PERMANENT tablespace
      - transaction이 필요할 때 생성된다.
      - 문장이 완전하게 실행되었을 때 SMON이 작업한다.
      
      ALTER TABLESPACE tablespace_name PERMANENT
      
   2) Temporary segments in a TEMPORARY tablespace
      - sort segment 라고도 한다.
      - instance가 startup 한 후에 생성된다.
      - instance가 shutdown 될 때까지 살아있다.
      - Sort Extent Pool의 정보를 기초로 한다.
      
      ALTER TABLESPACE tablespace_name TEMPORARY

[b]V$SORT_SEGMENT view : sort extent pool의 상태 정보[/b]


   column :
     tablespace_name
     extent_size
     total_extents
     total_blocks
     used_extents
     used_blocks
     free_extents
     free_blocks
     max_sort_size


     max_sort_blocks
     
   SVRMGR> SELECT tablespace_name, extent_size,
        2  total_extents, max_sort_blocks
        3  FROM v$sort_segment ;

[b]V$SORT_USAGE view : 현재 active sort 정보[/b]

   column :
     session_addr
     tablespace
     contents
     extents
     blocks
     
   SVRMGR> SELECT s.username, u.tablespace,
        2  u.contents, u.extents, u.blocks
        3  FROM v$session s, v$sort_usage u
        4  WHERE s.saddr = u.session_addr ;

[b] 연습[/b]

TEMP 테이블스페이스를 TEMPORARY 로 변경하고, 변경이 되었는지를 검증하십시오.

SQL> alter tablespace temp temporary ;
SQL> select tablespace_name, contents
  2  from dba_tablespaces ;
SQL> select username, temporary_tablespace
  2  from dba_users ;
     ==> system 의 temporary tablespace가 temp로 되어있는지 확인하고, 만약 아니면 바꾼다.
         ( alter user system temporary tablespace temp ;)

인스턴스를 종료하지 않고 SORT_AREA_SIZE 를 2Kb로 변경하십시오.

SQL> alter system set sort_area_size = 2048 deferred ;

   * deferred : 이 명령을 실행하고 나서 다음에 접속할 때 부터 사용하라!

새롭게 사용자 SYSTEM 으로써 데이터베이스에 두 개의 session 을 오픈하십시오. 하나의 세션에서 srt_dd.sql 을 실행하고 나머지 세션에서 정렬 작업을 모니터 하십시오.

========== srt_dd.sql ===========
-- srt_dd.sql
-- Use for Lab 11 Q3 O8DBA class
SELECT object_name
FROM dba_objects
UNION
SELECT segment_name
FROM dba_segments
/
=================================

$ sqlplus system/manager
SQL> @ srt_dd

다른 터미날 윈도우를 열고,

$ sqlplus system/manager
SQL> select tablespace_name, total_extents, total_blocks
  2  from v$sort_segment ;

SORT_AREA_SIZE 를 재설정 하십시오.

SQL> alter system set sort_area_size = 65536 deferred ; 

 

12. 테이블 다루기 (Managing Tables)

 

[b]table 종류[/b]

   - Regular table
   - Partitioned table : 대용량의 데이터를 처리하고자 할 때 table을 쪼개서 처리한다.
   - Index-organized table
   - Clustered table

[b]Row의 구조[/b]

 Header
 Length
 Value
 Length
 value
 .....
 
 * null은 value가 없고 length만 0 이다.
 * null값을 많이 갖는 column은 뒤쪽에 놓는게 performance가 좋다.

[b]Long RAW와 LOB의 비교[/b]

LONG, LONG RAW                      LOB
Single column per table               Multiple columns per table
Up to 2 gigabytes                        Up to 4 gigabytes
SELECT returns data                  SELECT returns locator
Data stored in-line                      Data stored in-line or out-of-line
No object type support                Supports object types
Sequential access to chunks       Random acess to chunks

 * chunk : block보다 큰 단위

[b]ROWID data type[/b]

Data object
number

 Relative file
number
 Block
number
 Row
number

 SQL> SELECT deptno, ROWID
    2  FROM dept ;

[b]Creating a Table[/b]

   [syntax]
   CREATE TABLE [schema.]table
   (column datatype [, column datatype] ...)
   [TABLESPACE tablespace ]
      [PCTFREE integer ]
      [PCTUSED integer ]
      [INITRANS integer ]
      [MAXTRANS integer ]
      [STORAGE storage-clause ]
      [LOGGING | NOLOGGING]  -- redo log file에 정보를 남긴다.
      [CACHE | NOCACHE]   --  자주 사용되는 테이블은 cache option을 주는게 좋다.

   CREATE TABLE employees(
   empno NUMBER(4),
   last_name VARCHAR2(30)
   deptno NUMBER(2))
   PCTFREE 20 PCTUSED 50
   STORAGE(INITIAL 200K NEXT 200K
   PCTINCREASE 0 MAXEXTENTS 50)
   TABLESPACE data01 ;

[b]테이블 copy 하기[/b]

   CREATE TABLE new_emp
   STORAGE(INITIAL 200K NEXT 200K
   PCTINCREASE 0 MAXEXTENTS 50)
   NOLOGGING
   TABLESPACE data01
   AS
   SELECT * FROM scott.employees ;

   * not null만 copy 되고 기타 constraint, trigger, table 권한등은 copy 되지 않는다.

[b]Storage Parameter 변경[/b]

   ALTER TABLE scott.employees
   PCTFREE 30
   PCTUSED 50
   STORAGE(NEXT 500K
   MINEXTENTS 2
   MAXEXTENTS 100 ) ;

[b]Manually Allocating Extents[/b]

   ALTER TABLE scott.employees
   ALLOCATE EXTENT(SIZE 500K
   DATAFILE '/disck3/data01.dbf') ;

[b]High Water Mark[/b]

 - 어디까지 data가 차있는지를 표시한다.
 - high water mark 이후의 공간은 한번도 쓴적이 없다.
 - deallocate 는 high water mark  이후의 공간만 된다.

[b]High Water Mark를 찾을 수 있는 package procedure[/b]

   - DBMS_SPACE.UNUSED_SPACE

   DECLARE
      v_owner VARCHAR2(30) := 'SCOTT' ;
      v_segment_name VARCHAR2(30) := 'EMPLOYEES' ;
      v_segment_type VARCHAR2(30) := 'TABLE' ;
      v_total_blocks NUMBER ;
      v_total_bytes NUMBER ;
      v_unused_blocks NUMBER ;
      v_unused_bytes NUMBER ;
      v_last_used_extent_file_id NUMBER ;
      v_last_used_extent_block_id NUMBER ;
      v_last_used_block NUMBER ;

   BEGIN
      dbms_space.unused_space
         (v_owner,
          v_segment_name,
          v_segment_type,
          v_total_blocks,
          v_total_bytes,
          v_unused_blocks,
          v_unused_bytes,
          v_last_used_extent_file_id,
          v_last_used_extent_block_id,
          v_last_used_block
         ) ;
      dbms_output.put_line(INITCAP(v_segment_type)||' : '||v_owner||'.'||v_segment_name) ;
      dbms_output.put_line('Total Blocks : '||TO_CHAR(v_total_blocks) ) ;
      dbms_output.put_line('Blocks above HWM :'||TO_CHAR(v_unused_blocks) ) ;
   END ;
   /
   ===========================
   Statement processed.
   Table : SCOTT.EMPLOYEES
   Total Blocks : 25
   Blocks above HWM : 23

[b]사용되지 않은 공간 Deallocation 하기[/b]

  ; High water mark 이후의 공간

   ALTER TABLE scott.employees
   DEALLOCATE UNUSED ;

   ALTER TABLE scott.employees
   DEALLOCATE UNUSED KEEP 30K
   --> high water mark 이후에 있는 30K공간만 남기고 Deallocate 한다.

[b]Truncating a Table[/b]

   TRUNCATE TABLE scott.employees ;
   --> 테이블에 있는 데이터가 모두 삭제되고 high water mark가 0이 된다.

[b]Dropping Tables[/b]

   DROP TABLE scott.departments
   CASCADE CONSTRAINTS ;

[b]Table 구조를 검증하는 명령[/b]

   ANALYZE TABLE scott.employees
   VALIDATE STRUCTURE ;

   ANALYZE TABLE scott.employees
   VALIDATE STRUCTURE CASCADE ;
   --> 연결된 모든 table들 까지 검증하라!

[b]현재 테이블에 migration이 얼마나 발생했나를 조회하기[/b]

   SELECT chain_cnt
   FROM DBA_TABLES
   WHERE table_name='EMPLOYEES'
   AND owner='SCOTT' ;

[b]통계정보를 생성[/b]

   ANLAYZE TABLE scott.employees
   ESTIMATE STATISTICS ;
   --> 표본통계 생성

   ANLAYZE TABLE scott.employees
   COMPUTE STATISTICS ;
   --> 전체통계 생성

[b]테이블 정보를 보는 data dictionary[/b]

   DBA_OBJECTS : owner, object_name, object_id, data_object_id, created
   DBA_SEGMENTS : owner, segment_name, tablespace_name, header_file, header_block
   DBA_TABLES : owner, table_name, pct_free, pct_used, initial_extent, next_extent,
                min_extents, max_extents, pct_increase, cache, blocks, empty_blocks,
                chain_cnt

   SQL> SELECT t.table_name, o.data_object_id,
     2         s.header_file, s.header_block
     3  FROM dba_tables t, dba_objects o, dba_segments s
     4  WHERE t.owner=o.owner
     5  AND t.table_name=o.object_name
     6  AND t.owner=s.owner
     7  AND t.table_name=s.segment_name
     8  AND t.owner='SCOTT' ;

   * Block Usage and Chaining Information
   SQL> SELECT blocks AS HWM, empty_blocks, chain_cnt AS "Chained Blocks"
     2  FROM dba_tables
     3  WHERE owner='SCOTT'
     4  AND table_name='EMPLOYEES' ;

[b]Extent 정보를 갖고 있는 data dictionary[/b]

   DBA_EXTENTS : owner, segment_name, extent_id, file_id, block_id, blocks

   SQL> SELECT file_id, COUNT(*) AS Extents, SUM(blocks) AS blocks
     2  FROM dba_extents
     3  WHERE owner='SCOTT'
     4  AND segment_name='EMPLOYEES'
     5  GROUP BY file_id ;

[b]DBMS_ROWID package[/b]

    ROWID_CREATE : Creates a ROWID from individual components
    ROWID_OBJECT : Returns the object Identifier for a ROWID
    ROWID_RELATIVE_FNO : Returns the relative file number for a ROWID
    ROWID_BLOCK_NUMBER : Returns the block number for a ROWID
    ROWID_ROW_NUMBER : Returns the row number for a ROWID
    ROWID_TO_ABSOLUTE_FNO : Returns the absolute file number for a ROWID
    ROWID_TO_EXTENDED : Converts a ROWID from restricted to extended
    ROWID_TO_RESTRICTED : Converts a ROWID from extended to restricted

    * Getting ROWID Components
    SQL> SELECT deptno, ROWID,
      2  DBMS_ROWID.ROWID_OBJECT(ROWID) AS "Object",
      3  DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) AS "Relative File",
      4  DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AS "Block"
      5  FROM scott.departments ;

    * Finding Absolute File Number
    SQL> SELECT deptno, ROWID,
      2  DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(ROWID,'SCOTT','DEPARTMENT')
      3  AS "File"
      4  FROM scott.departments ;

[b] 연습 [/b]

주문 입력 시스템을 위해 다음의 테이블을 생성하십시오.

   CUSTOMERS table
   CUST_CODE     VARCHAR(3)
   NAME          VARCHAR2(50)
   REGION        VARCHAR2(5)

   ORDERS table
   ORD_ID        NUMBER(3)
   ORD_DATE      DATE
   CUST_CODE     VARCHAR2(3)
   DATE_OF_DELY  DATE

   $sqlplus system/manager
   SQL> create table customers
        (cust_code   varchar2(3),
         name        varchar2(50),
         region      varchar2(5))
        tablespace data01 ;
   SQL> create table orders
        (ord_id      number(3),
         ord_date    date,
         cust_code   varchar2(3),
         date_of_dley  date)
        tablespace data01
        pctfree 35 ;

행을 테이블에 삽입하기 위해서 스크립트 ins_cord.sql을 실행하십시오.

===================================== ins_cord.sql ===========================================
INSERT INTO system.orders(ord_id,ord_date,cust_code) VALUES(610,'11-NOV-97','A01');    
INSERT INTO system.orders(ord_id,ord_date,cust_code) VALUES(611,'15-NOV-97','A02');    
INSERT INTO system.orders(ord_id,ord_date,cust_code) VALUES(612,'19-NOV-97','A04');    
INSERT INTO system.orders(ord_id,ord_date,cust_code) VALUES(601,'05-MAR-97','A06');    
INSERT INTO system.orders(ord_id,ord_date,cust_code) VALUES(602,'09-APR-97','A02');    
INSERT INTO system.orders(ord_id,ord_date,cust_code) VALUES(600,'05-MAR-97','A03');    
INSERT INTO system.orders(ord_id,ord_date,cust_code) VALUES(604,'19-APR-97','A06');    
INSERT INTO system.orders(ord_id,ord_date,cust_code) VALUES(605,'18-MAY-97','A06');    
INSERT INTO system.orders(ord_id,ord_date,cust_code) VALUES(607,'22-MAY-97','A04');    


INSERT INTO system.orders(ord_id,ord_date,cust_code) VALUES(608,'29-MAY-97','A04');    
INSERT INTO system.orders(ord_id,ord_date,cust_code) VALUES(603,'09-APR-97','A02');    
INSERT INTO system.orders(ord_id,ord_date,cust_code) VALUES(613,'06-DEC-97','A08');    
INSERT INTO system.orders(ord_id,ord_date,cust_code) VALUES(614,'06-DEC-97','A02');    
INSERT INTO system.orders(ord_id,ord_date,cust_code) VALUES(616,'08-DEC-97','A03');    
INSERT INTO system.orders(ord_id,ord_date,cust_code) VALUES(619,'27-DEC-97','A04');    
INSERT INTO system.orders(ord_id,ord_date,cust_code) VALUES(617,'10-DEC-97','A05');    
INSERT INTO system.orders(ord_id,ord_date,cust_code) VALUES(615,'06-DEC-97','A07');    
INSERT INTO system.orders(ord_id,ord_date,cust_code) VALUES(618,'20-DEC-97','A02');    
INSERT INTO system.customers VALUES('A01','TKB SPORT SHOP','West');
INSERT INTO sy

stem.customers VALUES('A02','VOLLYRITE','North');
INSERT INTO system.customers VALUES('A03','JUST TENNIS','North');
INSERT INTO system.customers VALUES('A04','EVERY MOUNTAIN','South');
INSERT INTO system.customers VALUES('A05','SHAPE UP','South');
INSERT INTO system.customers VALUES('A06','SHAPE UP','West');
INSERT INTO system.customers VALUES('A07','WOMENS SPORTS','South');
INSERT INTO system.customers VALUES('A08','NORTH WOODS HEALTH AND FITNESS SUPPLY CENTER','East');
commit ;
===============================================================================================

SQL> @ ins_cord

CUST_CODE=A04인 고객으로부터의 주문을 포함하는 파일과 블록을 찾으십시오.

SQL> select distinct dbms_rowid.rowid_relative_fno(rowid) as "File",
  2         dbms_rowid.rowid_block_number(rowid) as "Block"
  3  from orders
  4  where cust_code = 'A04' ;

(a) PCTFREE를 5로 줄이도록 테이블 CUSTOMERS를 변경하십시오.

   SQL> alter table customers pctfree 5 ;

(b) CUSTOMERS 테이블에 데이터를 삽입하고 갱신하기 위해 스크립트 upd_cust.sql을 사용하십시오.

===================================== upd_cord.sql ===========================================
BEGIN
  FOR i IN 1..10
  LOOP
     INSERT INTO system.customers(cust_code,name) 
     VALUES('C01','JOCKSPORTS');                
     INSERT INTO system.customers(cust_code,name) 
     VALUES('B12','STADIUM SPORTS');            
     INSERT INTO system.customers(cust_code,name) 
     VALUES('B02','HOOPS');                     
     INSERT INTO system.customers(cust_code,name) 
     VALUES('B03','REBOUND SPORTS');            
     INSERT INTO system.customers(cust_code,name) 
     VALUES('B04','THE POWER FORWARD');         
     INSERT INTO system.customers(cust_code,name) 
     VALUES('B05','POINT GUARD');               
     INSERT INTO system.customers(cust_code,name) 
     VA

LUES('B06','THE COLISEUM');              
     INSERT INTO system.customers(cust_code,name) 
     VALUES('B07','FAST BREAK');                
     INSERT INTO system.customers(cust_code,name) 
     VALUES('B08','AL AND BOB''S SPORTS');       
     INSERT INTO system.customers(cust_code,name) 
     VALUES('B11','AT BAT');                    
     INSERT INTO system.customers(cust_code,name) 
     VALUES('B12','ALL SPORT');                 
     INSERT INTO system.customers(cust_code,name) 
     VALUES('B13','GOOD SPORT');                
     INSERT INTO system.customers(cust_code,name) 
     VALUES('B14','AL''S PRO SHOP');             
     INSERT INTO system.customers(cust_code,name) 
     VALUES('B15','BOB''S FAMILY SPORTS');       
     INSERT INTO system.customers(cust_code,name) 


     VALUES('B16','THE ALL AMERICAN');          
     INSERT INTO system.customers(cust_code,name) 
     VALUES('B17','HIT, THROW, AND RUN');       
     INSERT INTO system.customers(cust_code,name) 
     VALUES('B18','THE OUTFIELD');              
     INSERT INTO system.customers(cust_code,name) 
     VALUES('B21','WHEELS AND DEALS');          
     INSERT INTO system.customers(cust_code,name) 
     VALUES('B22','JUST BIKES');                
     INSERT INTO system.customers(cust_code,name) 
     VALUES('B23','VELO SPORTS');               
     INSERT INTO system.customers(cust_code,name) 
     VALUES('B24','JOE''S BIKE SHOP');           
     INSERT INTO system.customers(cust_code,name) 
     VALUES('B25','BOB''S SWIM, CYCLE, AND RUN');
     INSERT INTO system.customers(cust_code,name) 


     VALUES('B26','CENTURY SHOP');              
     INSERT INTO system.customers(cust_code,name) 
     VALUES('B27','THE TOUR');                  
     INSERT INTO system.customers(cust_code,name) 
     VALUES('B28','FITNESS FIRST');             
  END LOOP;
END;
/

UPDATE system.customers SET region='West' WHERE cust_code='C01';                       
UPDATE system.customers SET region='East' WHERE cust_code='B12';                       
UPDATE system.customers SET region='East' WHERE cust_code='B02';                       
UPDATE system.customers SET region='East' WHERE cust_code='B03';                       
UPDATE system.customers SET region='East' WHERE cust_code='B04';                       
UPDATE system.customers SET region='West' WHERE cust_code='B05';                       
UPDATE system.customers SET region='North' WHERE cust_code='B06';                      
UPDATE system.customers SET region='South' WHERE cust_code='B07';                      
UPDATE system.customers SET region='North' WHERE cust_code='B08';                      
UPDATE system.customers SET region='North' WHERE cust_code='B11';                      


UPDATE system.customers SET region='West' WHERE cust_code='B12';                       
UPDATE system.customers SET region='East' WHERE cust_code='B13';                       
UPDATE system.customers SET region='North' WHERE cust_code='B14';                      
UPDATE system.customers SET region='West' WHERE cust_code='B15';                       
UPDATE system.customers SET region='North' WHERE cust_code='B16';                      
UPDATE system.customers SET region='East' WHERE cust_code='B17';                       
UPDATE system.customers SET region='East' WHERE cust_code='B18';                       
UPDATE system.customers SET region='East' WHERE cust_code='B21';                       
UPDATE system.customers SET region='East' WHERE cust_code='B22';                       
UPDATE system.customers SET region='North' WHERE cust_code='B23';                      


UPDATE system.customers SET region='South' WHERE cust_code='B24';                      
UPDATE system.customers SET region='South' WHERE cust_code='B25';                      
UPDATE system.customers SET region='South' WHERE cust_code='B26';                      
UPDATE system.customers SET region='North' WHERE cust_code='B27';                      
UPDATE system.customers SET region='North' WHERE cust_code='B28';                      
===============================================================================================

   SQL> @ upd_cust

(c) 테이블에 row migration 이 존재하는지 확인하십시오.

   SQL> analyze table customers compute statistics ;
   SQL> select chain_cnt
     2  from dba_tables
     3  where table_name='CUSTOMERS'
     4  and owner='SYSTEM' ;
   SQL> delete from system.customers
     2  where cust_code >= 'B01' ;
   SQL> commit ;

ORDERS 테이블에 의해 사용된 extent의 수를 검사하십시오.

SQL> select count(*)
  2  from dba_extents
  3  where segment_name='ORDERS'
  4  and owner='SYSTEM' ;

ORDERS 테이블에 대해 수동으로 extent를 디폴트 크기로 할당하고 확인하십시오.

SQL> alter table orders allocate extent ;
SQL> select count(*)
  2  from dba_extents
  3  where segment_name='ORDERS' and owner='SYSTEM' ;
SQL> select segment_name, extents, initial_extent, next_extent
  2  from dba_segments
  3  where segment_name='ORDERS' ;

ORDERS 테이블을 복사하여 ORDERS2 테이블을 생성하되 MINEXTENTS=10으로 하십시오. 테이블이 명시된 extent 의 수만큼 생성되었는지를 검증하십시오.

SQL> create table orders2
  2  tablespace data01
  3  storage (minextents 10)
  4  as
  5    select * from orders ;
SQL> select segment_name, extents, initial_extent, next_extent
  2  from dba_segments
  3  where segment_name='ORDERS2' ;

(a) 주문 입력 어플리케이션에 대해서, 다음의 column을 가지는 PRODUCTS 테이블을 테이블스페이스

   DATA02에 extent 크기를 50K로 일정하게 생성하십시오.

   PRODUCTS table
   PROD_CODE    NUMBER(6)
   DESCRIPTION  VARCHAR2(30)
   PRICE        NUMBER(8,2)

   SQL> create table products
     2  (prod_code   number(6),
     3   description varchar2(30),
     4   price       number(8,2) )
     5  storage (initial 50k next 50k pctincrease 0)
     6  tablespace data02 ;

(b) 이 테이블에 대해 extent 의 크기를 검사하십시오.

   SQL> select segment_name, extent_id, blocks, bytes
     2  from dba_extents
     3  where segment_name='PRODUCTS' ;
[Top]
No.
제목
작성자
작성일
조회
8859Oracle 8 - Backup & Recovery (1)
정재익
2001-12-12
9667
8858Oracle 8 - Network Administration
정재익
2001-12-12
11684
8854Oracle 8 - Database Administration (2)
정재익
2001-12-12
28246
8853Oracle 8 - Database Administration (1)
정재익
2001-12-12
14343
8827Oracle 8 - SQL & PL/SQL (2)
정재익
2001-12-11
15399
8825Oracle 8 - SQL & PL/SQL (1)
정재익
2001-12-11
14599
8772특정 TABLE을 다른 TABLESPACE로 옮기는 방법 [1]
정재익
2001-12-08
5232
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2022 DSN, All rights reserved.
작업시간: 0.013초, 이곳 서비스는
	PostgreSQL v13.3으로 자료를 관리합니다