[관리] 오라클 DBA 기술 노트 -2

Oracle Database 주요 관리 기술 문서

<<파라미터 확인 및 동적 성능 뷰>>

NOMOUNT 단계에서 확인가능 뷰

  V$PARAMETER, V$SGA, V$OPTION, V$PROCESS

  V$VERSION, V$SESSION, V$INSTANCE

MOUNT 단계에서 확인가능 뷰

  V$THREAD, V$DATABASE, V$DATAFILE, V$DATAFILE_HEADER

  V$CONTROLFILE, V$LOGFILE, V$LOG

<<데이터베이스 생성>>

CREATE DATABASE DBNAME

[CONTROLFILE REUSE]

[LOGFILE] [GROUP #] FILE-SPEC

[MAXLOGFILES] #

[MAXLOGMEMBERS] #

[MAXLOGHISTORY] #

[DATAFILE] FILE-SPEC

[MAXDATAFILES] #

[MAXINSTANCES] #

[ARCHIVE|NOARCHIVE]

[EXCLUSIVE]

[CHARACTER SET] %

[NATIONAL CHARACTER SET] %

예문

CREATE DATABASE MKDB

LOGFILE GROUP 1 (‘C:\\ORACLE\\ORADATA\\MKDB\\REDO1A.LOG’,

                 ‘C:\\ORACLE\\ORADATA\\MKDB\\REDO1B.LOG’) SIZE 1024K,

        GROUP 2 (‘C:\\ORACLE\\ORADATA\\MKDB\\REDO2A.LOG’,

                 ‘C:\\ORACLE\\ORADATA\\MKDB\\REDO2B.LOG’) SIZE 1024K,

        GROUP 3 (‘C:\\ORACLE\\ORADATA\\MKDB\\REDO3A.LOG’,

                 ‘C:\\ORACLE\\ORADATA\\MKDB\\REDO3B.LOG’) SIZE 1024K

MAXLOGFILES 32

MAXLOGMEMBERS 3

MAXLOGHISTORY 1

DATAFILE ‘C:\\ORACLE\\ORADATA\\MKDB\\system01.dbf’ SIZE 58M REUSE

AUTOEXTEND ON NEXT 640K

MAXDATAFILES 254

MAXINSTANCES 1

CHARACTER SET KO16KSC5601

NATIONAL CHARACTER SET KO16KSC5601;

CF. SYSTEM TABLESPACE를 구성하는 DATAFILE

    SYSTEM이라는 이름의 ROLLBACK SEGMENT(SYSTEM TABLESPACE 내부에)

    CONTROL FILE

    REDO LOG FILE

    사용자 SYS/CHANGE_ON_INSTALL

    사용자 SYSTEM/MANAGER

    DATA DICTOIONARY VIEW(BY CATALOG.SQL, CATPROC.SQL)는 제외한 내부 테이블(BY SQL.BSQ)

CF. CATALOG.SQL

    일반적으로 생성되는 데이터 DICTIONARY VIEW를 생성

    기본 테이블의 뷰, 동적 성능 뷰에 대한 뷰, 그리고 그 동의어를 생성

    서버매니저 유틸리티, 감사, EXPORT, IMPORT 유틸리티,

    파티셔닝과 OBJECT 옵션을 위한 뷰와 오브젝트를 생성하는 스크립트를 실행

    STANDARD.SQL을 실행함으로써 기본적인 PL/SQL환경을 생성

CF. CATPROC.SQL

    서버에서 PL/SQL에 필요한 모든 스크립트를 실행

    PL/SQL기능을 사용할 수 있도록 설정하며 몇 개의 PL/SQL 패키지 생성

    ADVANCED QUENING OPTION, TABLESPACE POINT-IN-TIME RECOVERY,

    LOB 사용을 위한 추가 뷰 생성

<<컨트롤 파일>>

1. 컨트롤파일의 보유정보

데이터베이스 이름과 식별자, 생성시간

데이터파일과 리두로그파일의 이름과 위치

테이블스페이스의 이름

현재의 LOG SEQUENCE 정보

CHECKPOINT 정보

현재까지의 LOG 기록

RMAN(RECOVERY MANAGER)의 백업정보

2. 컨트롤파일에 대한 정보검색

SELECT NAME FROM V$CONTROLFILE;

SHOW PARAMETER CONTROL

SELECT VALUE FROM V$PARAMETER WHERE NAME = ‘control_files’;

3. 컨트롤 파일의 추가

데이터베이스 셧다운

운영체제 상에서 원하는 위치에 복사

오라클 파라미터 파일 수정

데이터베이스 스타트

즉, 운영체제 상에서 복사한 후 오라클 파라미터 파일 수정

control_files = (“C:\\ORACLE\\ORADATA\\MKDB\\ctrl01.ctl”,

                 “C:\\ORACLE\\ORADATA\\MKDB\\ctrl02.ctl”,

                 “C:\\ORACLE\\ORADATA\\MKDB\\ctrl03.ctl”)

<<리두로그파일관리>>

ALTER DATABASE [DBNAME]

ADD LOGFILE [GROUP #] FILE-SPEC [,[GROUP #] FILE-SPEC]

ADD LOGFILE MEMBER ‘FILENAME’ [REUSE] [,’FILENAME’ [REUSE]] TO GROUP #

DROP LOGFILE GROUP #

DROP LOGFILE MEMBER ‘FILENAME'[,’FILENAME’]

CLEAR [UNARCHIVED] LOGFILE

RENAME FILE ‘OLD_FILENAME'[,’OLD_FILENAME’] TO ‘NEW_FILENAME'[,’OLD_FILENAME’]

(RENAME FILE 명령어는 마운트 상태에서, 나머지 명령어는 마운트나 오픈상태에서)

ARCHIVELOG|NOARCHIVELOG (반드시 마운트 상태에서)

ALTER DATABASE (DBNAME)

[ADD|DROP] LOGFILE [MEMBER|GROUP] FILE-SPEC;

그룹추가시에는

ALTER DATABASE MKDB

ADD LOGFILE GROUP 4 (‘C:\\ORACLE\\ORADATA\\MKDB\\REDO4A.LOG’,

                     ‘C:\\ORACLE\\ORADATA\\MKDB\\REDO4B.LOG’) SIZE 1024K;

그룹을 생략하면 자동으로 그룹생성

ALTER DATABASE MKDB

ADD LOGFILE (‘C:\\ORACLE\\ORADATA\\MKDB\\REDO4A.LOG’,

             ‘C:\\ORACLE\\ORADATA\\MKDB\\REDO4B.LOG’) SIZE 1024K,

            (‘C:\\ORACLE\\ORADATA\\MKDB\\REDO5A.LOG’,

             ‘C:\\ORACLE\\ORADATA\\MKDB\\REDO5B.LOG’) SIZE 1024K;

데이터베이스가 변경되었습니다.

SQL> SELECT * FROM V$LOGFILE;

    GROUP# STATUS  MEMBER

———- ——- —————————————-

         1         C:\\ORACLE\\ORADATA\\MKDB\\REDO1A.LOG

         1         C:\\ORACLE\\ORADATA\\MKDB\\REDO1B.LOG

         2         C:\\ORACLE\\ORADATA\\MKDB\\REDO2A.LOG

         2         C:\\ORACLE\\ORADATA\\MKDB\\REDO2B.LOG

         3         C:\\ORACLE\\ORADATA\\MKDB\\REDO3A.LOG

         3         C:\\ORACLE\\ORADATA\\MKDB\\REDO3B.LOG

         4         C:\\ORACLE\\ORADATA\\MKDB\\REDO4A.LOG

         4         C:\\ORACLE\\ORADATA\\MKDB\\REDO4B.LOG

         5         C:\\ORACLE\\ORADATA\\MKDB\\REDO5A.LOG

         5         C:\\ORACLE\\ORADATA\\MKDB\\REDO5B.LOG

그룹에 멤버추가시에는(SIZE 명시하면 -ORA-00946: TO 키워드가 없습니다)

ALTER DATABASE MKDB

ADD LOGFILE MEMBER ‘C:\\ORACLE\\ORADATA\\MKDB\\REDO5B.LOG’ TO GROUP 5;

ALTER DATABASE MKDB

ADD LOGFILE MEMBER ‘C:\\ORACLE\\ORADATA\\MKDB\\REDO6B.LOG’ TO GROUP 6;

멤버삭제시에는

ALTER DATABASE MKDB

DROP LOGFILE MEMBER ‘C:\\ORACLE\\ORADATA\\MKDB\\REDO5B.LOG’;

그룹삭제시에는

ALTER DATABASE MKDB

DROP LOGFILE GROUP 4;

파일을 정리하려면

ALTER DATABASE MKDB

CLEAR LOGFILE ‘C:\\ORACLE\\ORADATA\\MKDB\\REDO5B.LOG’;

온라인리두로그파일을 재배치시키려면

데이터베이스를 정상적으로 셧다운

온라인리두로그파일을 새로운 위치에 복사 또는이동

데이터베이스를 마운트

ALTER DATABASE MKDB

RENAME FILE ‘C:\\ORACLE\\ORADATA\\MKDB\\REDO5A.LOG’

         TO ‘C:\\ORACLE\\ORADATA\\MKDB\\REDO5C.LOG’;

데이터베이스 오픈한다

강제로 로그시위치를 시키려면

ALTER SYSTEM SWITCH LOGFILE; (데이터베이스 오픈 상태에서)

CURRENT 상태의 로그파일은 변경이 안되는 거 같음..

<<LOG MINER>>

UTL_FILE_DIR

EXECUTE DBMS_LOGMNR_D.BUILD(

‘MYDBDICT.ORA’,’C:\\ORACLE\\ADMIN\\MYDB\\LOG’);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(

‘C:\\ORACLE\\ORADATA\\MYDB\\REDO01A.LOG’,

DBMS_LOGMNR.NEW);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(

‘C:\\ORACLE\\ORADATA\\MYDB\\REDO01A.LOG’,

DBMS_LOGMNR.ADDFILE);

EXECUTE DBMS_LOGMNR.START_LOGMNR(‘C:\\ORACLE\\ADMIN\\MYDB\\LOG\\MYDBDICT.ORA’);

EXECUTE DBMS_LOGMNR.END_LOGMNR;

V$LOGMNR_CONTENTS;

V$LOGMNR_DICTIONARY;

V$LOGMNR_PARAMETERS;

<CHECKPOINT가 발생되는 시점>

1. LOG SWITCH가 발생될 때마다

2. LOG_CHECKPOINT_TIMEOUT

3. LOG_CHECKPOINT_INTERVAL

4. 데이터베이스 정상종료시(즉, ABORT 옵션 제외)

5. 강제적으로(데이터베이스 오픈 상태에서 ALTER SYSTEM CHECKPOINT)

6. 테이블스페이스를 오프라인시키는 경우

<<테이블스페이스>>

영역할당을 제어하고 사용자에게 공간할당량(QUOTA)을 지정

개별적인 테이블스페이스를 온라인/오프라인으로 하여 데이터의가용성 제어

I/O 성능을 향상시키고 한 디스크에 대한 I/O 경합을 최소화하기 위해

장치간에 데이터 스토리지 분배

부분백업과 부분복구 작업 수행

읽기전용 테이블스페이스에 많은 양의 정적 데이터 유지

CF. TEMPORARY TABLESPACE

    TEMPORARY TABLESPACE는 정렬작업에 사용되며 영구적인 오브젝트를 포함할 수 없다

    기존의 테이블스페이스가 영구적인 오브젝트를 가지고 있지 않다면

    TEMPORARY TABLESPACE 로 변경할수 있다.

이러한 TEMPORARY TABLESPACE 내에 만들어지는 세그먼트는 사용자가 생성할 수 없으며

    정렬을 필요로 하는 작업 예로 ORDER BY, GROUP BY, INDEX 생성을 하는 경우에

    정렬을 위해 필요한 영역을 할당받아 세그먼트를 형성하게 된다.

    SMON 프로세스는 TEMPORARY TABLESPACE를 CLEAR 한다.

<테이블스페이스 관리 지침>

CF. 테이블스페이스 분리

    데이터 딕셔너리와 사용자 데이터를 분리하도록 테이블스페이스를 지정

    응용프로그램에서 사용하는 데이터를 분리

    다른 데이블스페이스의 데이터파일을 각각 별개의 디스크 드라이브에 저장하여

    입출력 경합을 감소시킨다.

    데이터 세그먼트와 롤백 세그먼트를 분리하여 영구적으로 데이터를 잃게되는

    단일 디스크 실패를 방지한다.

    특정 테이블스페이스 내의 일부의 데이터만 오프라인 상태로 변경가능하다.

    빈번한 읽기 작업, 임시 세그먼트의 저장 같은 특별 유형의 데이터를 위해

    테이블스페이스를 따로 생성한다.

    각 테이블스페이스를 분리함으로써 테이블스페이스 단위로 백업을 받을 수 있다.

CF. STORAGE 파라미터 지정

    테이블스페이스에 생성될 전형적인 오브젝트의 크기를 고려하여 기본파라미터 결정한다.

CF. 사용자에게 공간 할당량 지정

    테이터베이스사용자에게 필요한 만큼의 테이블스페이스 할당량을 지정한다.

<생성>

CREATE TABLESPACE TBNAME

[DATAFILE] FILE-SPEC

[AUTOEXTEND] CLAUSE

[LOGGING|NOLOGGING]

[DEFAULT STORAGE] CLAUSE

[ONLINE|OFFLINE]

[PERMANENT|TEMPORARY]

CREATE TABLESPACE DATA1

DATAFILE ‘C:\\ORACLE\\ORADATA\\MKDB\\DATA1.DB’ SIZE 6M

DEFAULT STORAGE (

INITIAL 50K NEXT 50K

MINEXTENTS 10 MAXEXTENTS 121

PCTINCREASE 0);

<수정>

데이터파일의 추가(ALTER TABLESPACE ADD DATAFILE ~)

데이터파일의 이름변경(ALTER TABLESPACE RENAME DATAFILE ~)

디폴트 스토리지의 내용 수정

테이블스페이스의 활성화 및 비활성화

읽기전용 또는 읽기쓰기 모드 전환

온라인백업의 시작 및 종료

CF. 테이블스페이스의 크기변경

    자동 변경: ALTER DATABASE DATAFILE FILE-SPEC ~ AUTOEXTEND ON

    수동 변경: ALTER DATABASE DATAFILE FILE-SPEC ~ RESIZE

CF. 읽기전용으로 변경하려면

    온라인백업을 실행하지 않는 온라인상태여야 하고,

    테이블스페이스에 현재 사용중인 롤백세그먼트가 없어야 한다.

CF. 비활성화 옵션

    NORMAL : CHECKPOINT ALL DATA FILE

    TEMPORARY : CHECKPOINT ONLINE DATA FILE

    IMMEDIATE : CHECKPOINT X

CF. 읽기 전용 테이블스페이스는 REUSE 옵션으로 재사용 불가.

CF. 사용중이거나 복구중인 파일은(온라인상태를 의미하는 듯함) 이름변경 불가(ORA-01121)

CF. 데이터파일의 이름을 변경하기 위해서는 테이블스페이스를

    오프라인으로 만들고, 운영체제상에서 바꿀 이름의 파일을 복사해서

    생성한 후 실행해야 하는 것 같음..

CF. 2M짜리 테이블스페이스를 생성하여

    DBA_DATA_FILES 로 확인해보면 2*1024*1024 BYTE인 2097152바이트가 표시되나

    DBA_FREE_SPACE 에서 보면 2097152-2048(1BLOCK)인 2095104 바이트를 사용할 수 있고

    생성된 파일을 운영체제상에서 보면 2097152+2048(1BLOCK)인 2099200 바이트가 나옴.

    이는 정보를 담고 있는 헤더에 의한 것으로 보임..

CF. DEFAULT STORAGE 절의 INITIAL과 MINEXTENTS는 온라인 상태에서 변경이 불가능.

CF. OFFLINE TABLESPACE

    데이터베이스의 나머지 부분은 접근가능하나 일부에 대해서 접근금지할 때 사용

    (예: 데이터파일을 옮기는 동안 테이블이나 응용프로그램을 사용할 수 없도록 지정)

    오라클서버는 테이블스페이스가 오프라인되기 전에 모든 데이터에 대한 체크포인트 수행

    오프라인 상태의 테이블스페이스로는 접근 불가

    시스템 테이블스페이스와 활성화된 롤백세그먼트(ACTIVE ROLLBACK SEGMENT)를

    가진 테이블스페이스는 오프라인 될 수 없다.

    테이블스페이스가 오프라인 되는 것은 딕셔너리와 콘트롤파일에 기록되고 데이터베이스

    종료시 테이블스페이스가 오프라인이었다면 다시 오픈될때 INTEGRITY, CONSISTENCY를 하지 않는다.

    테이블스페이스가 현재 온라인 백업중이면 안된다.

CF. READ ONLY TABLESPACE

    읽기 전용 테이블스페이스는 SELECT만 가능하고 DML작업은 되지 않는다.

    읽기 전용 테이블스페이스의 목적은 데이터의 변경작업이 없었음이 확실하므로

    많은 정적데이터를 백업하고 복구할 필요가 없다.

    테이블스페이스가 온라인 상태(테이블스페이스에 포함된 모든 데이터파일이 온라인상태)

    에서만 전환이 가능.

    테이블스페이스가 읽기전용이 되면 테이블스페이스 내의 모든 파일에 대해 체크포인트를 수행

    테이블스페이스가 활성 롤백 세그먼트가 없어야 한다.

    테이블스페이스가 현재 온라인 백업중이면 안된다.

CF. 데이터파일을 삭제하려면

CREATE TABLESPACE TESTSPACE

DATAFILE ‘C:\\ORACLE\\ORADATA\\MYDB\\TEST01.DBF’ SIZE 2M,

         ‘C:\\ORACLE\\ORADATA\\MYDB\\TEST02.DBF’ SIZE 1M;

SELECT TABLESPACE_NAME, STATUS, CONTENTS, LOGGING FROM DBA_TABLESPACES;

SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES

FROM DBA_DATA_FILES;

ALTER DATABASE MYDB

DATAFILE ‘C:\\ORACLE\\ORADATA\\MYDB\\TEST02.DBF’

OFFLINE DROP;

SELECT TABLESPACE_NAME, STATUS, CONTENTS, LOGGING FROM DBA_TABLESPACES;

SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES

FROM DBA_DATA_FILES;

ALTER TABLESPACE TBNAME

[ADD DATAFILE] FILE-SPEC

[RENAME DATAFILE] FILE-SPEC [TO] FILE-SPEC

[AUTOEXTEND CLAUSE]

[MINIMUM EXTENT]

[DEFAULT STORAGE]

[ONLINE][OFFLINE [NORMAL|IMMEDIATE|TEMPORARY]]

[[BEGIN|END] BACKUP]

[READ [ONLY|WRITE]]

[PERMANENT|TEMPORARY]

[COALESCE]

<조회>

DBA_TABLESPACES

– 데이터베이스의 모든 테이블스페이스 이름

– DEFAULT STORAGE 파라미터

– 테이블스페이스 유형(PERMANENT, TEMPORARY)

– 데이터 파일의 사용가능성 여부(ONLINE, OFFLINE)

DBA_DATA_FILES

– 데이터 파일이 속한 테이블스페이스 정보

– AUTOEXTEND 옵션

V$DATAFILE

V$TABLESPACE

******************************************************

SQL> SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES

  2  FROM DBA_DATA_FILES;

TABLESPACE_NAME         FILE_ID FILE_NAME                                               BYTES

——————– ———- ————————————————– ———-

SYSTEM                        1 C:\\ORACLE\\ORADATA\\MKDB\\SYSTEM01.DBF                  88342528

RBS                           2 C:\\ORACLE\\ORADATA\\MKDB\\RBS01.DBF                     10485760

USERS                         3 C:\\ORACLE\\ORADATA\\MKDB\\USERS01.DBF                    3145728

TEMP                          4 C:\\ORACLE\\ORADATA\\MKDB\\TEMP01.DBF                    10485760

TOOLS                         5 C:\\ORACLE\\ORADATA\\MKDB\\TOOLS01.DBF                    4194304

INDX                          6 C:\\ORACLE\\ORADATA\\MKDB\\INDX01.DBF                     2097152

6 개의 행이 선택되었습니다.

SQL> CREATE TABLESPACE DATA

  2  DATAFILE ‘C:\\ORACLE\\ORADATA\\MKDB\\DATA01.DBF’ SIZE 2M;

테이블 영역이 생성되었습니다.

SQL> CREATE TABLESPACE DATA3

  2  DATAFILE ‘C:\\ORACLE\\ORADATA\\MKDB\\DATA03.DBF’ SIZE 1M;

테이블 영역이 생성되었습니다.

SQL> SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES

  2  FROM DBA_DATA_FILES;

TABLESPACE_NAME         FILE_ID FILE_NAME                                               BYTES

——————– ———- ————————————————– ———-

SYSTEM                        1 C:\\ORACLE\\ORADATA\\MKDB\\SYSTEM01.DBF                  88342528

RBS                           2 C:\\ORACLE\\ORADATA\\MKDB\\RBS01.DBF                     10485760

USERS                         3 C:\\ORACLE\\ORADATA\\MKDB\\USERS01.DBF                    3145728

TEMP                          4 C:\\ORACLE\\ORADATA\\MKDB\\TEMP01.DBF                    10485760

TOOLS                         5 C:\\ORACLE\\ORADATA\\MKDB\\TOOLS01.DBF                    4194304

INDX                          6 C:\\ORACLE\\ORADATA\\MKDB\\INDX01.DBF                     2097152

DATA                          7 C:\\ORACLE\\ORADATA\\MKDB\\DATA01.DBF                     2097152

DATA3                         8 C:\\ORACLE\\ORADATA\\MKDB\\DATA03.DBF                     1048576

8 개의 행이 선택되었습니다.

SQL> ALTER TABLESPACE DATA3

  2  RENAME DATAFILE ‘C:\\ORACLE\\ORADATA\\MKDB\\DATA03.DBF’

  3  TO ‘C:\\ORACLE\\ORADATA\\MKDB\\DATA02.DBF’;

ALTER TABLESPACE DATA3

*

1행에 오류:

ORA-01525: 데이터 파일을 이름 변경시 오류가 생겼습니다

ORA-01121: 8 데이터베이스 파일 이름을 바꿀 수 없습니다 –  파일이 사용중이거나 복구 중 입니다

ORA-01110: 8 데이터 파일: ‘C:\\ORACLE\\ORADATA\\MKDB\\DATA03.DBF’

SQL> ALTER TABLESPACE DATA3

  2  OFFLINE NORMAL;

테이블 영역이 변경되었습니다. (이때 운영체제 상에서 다른 이름으로 복사함)

SQL> ALTER TABLESPACE DATA3

  2  RENAME DATAFILE ‘C:\\ORACLE\\ORADATA\\MKDB\\DATA03.DBF’

  3  TO ‘C:\\ORACLE\\ORADATA\\MKDB\\DATA02.DBF’;

테이블 영역이 변경되었습니다.

SQL> SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES

  2  FROM DBA_DATA_FILES;

TABLESPACE_NAME         FILE_ID FILE_NAME                                               BYTES

——————– ———- ————————————————– ———-

SYSTEM                        1 C:\\ORACLE\\ORADATA\\MKDB\\SYSTEM01.DBF                  88342528

RBS                           2 C:\\ORACLE\\ORADATA\\MKDB\\RBS01.DBF                     10485760

USERS                         3 C:\\ORACLE\\ORADATA\\MKDB\\USERS01.DBF                    3145728

TEMP                          4 C:\\ORACLE\\ORADATA\\MKDB\\TEMP01.DBF                    10485760

TOOLS                         5 C:\\ORACLE\\ORADATA\\MKDB\\TOOLS01.DBF                    4194304

INDX                          6 C:\\ORACLE\\ORADATA\\MKDB\\INDX01.DBF                     2097152

DATA                          7 C:\\ORACLE\\ORADATA\\MKDB\\DATA01.DBF                     2097152

DATA3                         8 C:\\ORACLE\\ORADATA\\MKDB\\DATA02.DBF                     1048576

8 개의 행이 선택되었습니다.

SQL> SELECT TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS,

  2         PCT_INCREASE, STATUS

  3  FROM DBA_TABLESPACES;

TABLESPACE_NAME      INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE STATUS

——————– ————– ———– ———– ———– ———— ——-

SYSTEM                        65536       65536           1  2147483645           50 ONLINE

RBS                           10240       10240           1         121           50 ONLINE

USERS                         10240       10240           1         121           50 ONLINE

TEMP                          10240       10240           1                       50 ONLINE

TOOLS                         10240       10240           1         121           50 ONLINE

INDX                          10240       10240           1         121           50 ONLINE

DATA                          10240       10240           1         121           50 ONLINE

DATA3                         10240       10240           1         121           50 OFFLINE

8 개의 행이 선택되었습니다.

SQL> ALTER TABLESPACE DATA3

  2  ONLINE;

테이블 영역이 변경되었습니다.

SQL> SELECT TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS,

  2         PCT_INCREASE, STATUS

  3  FROM DBA_TABLESPACES;

TABLESPACE_NAME      INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE STATUS

——————– ————– ———– ———– ———– ———— ——-

SYSTEM                        65536       65536           1  2147483645           50 ONLINE

RBS                           10240       10240           1         121           50 ONLINE

USERS                         10240       10240           1         121           50 ONLINE

TEMP                          10240       10240           1                       50 ONLINE

TOOLS                         10240       10240           1         121           50 ONLINE

INDX                          10240       10240           1         121           50 ONLINE

DATA                          10240       10240           1         121           50 ONLINE

DATA3                         10240       10240           1         121           50 ONLINE

8 개의 행이 선택되었습니다.

SQL> DROP TABLESPACE DATA3;

테이블 영역이 삭제되었습니다.

SQL> SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES

  2  FROM DBA_DATA_FILES;

TABLESPACE_NAME         FILE_ID FILE_NAME                                               BYTES

——————– ———- ————————————————– ———-

SYSTEM                        1 C:\\ORACLE\\ORADATA\\MKDB\\SYSTEM01.DBF                  88342528

RBS                           2 C:\\ORACLE\\ORADATA\\MKDB\\RBS01.DBF                     10485760

USERS                         3 C:\\ORACLE\\ORADATA\\MKDB\\USERS01.DBF                    3145728

TEMP                          4 C:\\ORACLE\\ORADATA\\MKDB\\TEMP01.DBF                    10485760

TOOLS                         5 C:\\ORACLE\\ORADATA\\MKDB\\TOOLS01.DBF                    4194304

INDX                          6 C:\\ORACLE\\ORADATA\\MKDB\\INDX01.DBF                     2097152

DATA                          7 C:\\ORACLE\\ORADATA\\MKDB\\DATA01.DBF                     2097152

7 개의 행이 선택되었습니다.

SQL> ALTER TABLESPACE DATA

  2  ADD DATAFILE ‘C:\\ORACLE\\ORADATA\\MKDB\\DATA02.DBF’ REUSE;

테이블 영역이 변경되었습니다.

SQL> SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES

  2  FROM DBA_DATA_FILES;

TABLESPACE_NAME         FILE_ID FILE_NAME                                               BYTES

——————– ———- ————————————————– ———-

SYSTEM                        1 C:\\ORACLE\\ORADATA\\MKDB\\SYSTEM01.DBF                  88342528

RBS                           2 C:\\ORACLE\\ORADATA\\MKDB\\RBS01.DBF                     10485760

USERS                         3 C:\\ORACLE\\ORADATA\\MKDB\\USERS01.DBF                    3145728

TEMP                          4 C:\\ORACLE\\ORADATA\\MKDB\\TEMP01.DBF                    10485760

TOOLS                         5 C:\\ORACLE\\ORADATA\\MKDB\\TOOLS01.DBF                    4194304

INDX                          6 C:\\ORACLE\\ORADATA\\MKDB\\INDX01.DBF                     2097152

DATA                          7 C:\\ORACLE\\ORADATA\\MKDB\\DATA01.DBF                     2097152

DATA                          8 C:\\ORACLE\\ORADATA\\MKDB\\DATA02.DBF                     1048576

8 개의 행이 선택되었습니다.

******************************************************

SQL> SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES

  2  FROM DBA_DATA_FILES

  3  WHERE TABLESPACE_NAME = ‘DATA’;

TABLESPACE_NAME         FILE_ID FILE_NAME                                               BYTES

——————– ———- ————————————————– ———-

DATA                          7 C:\\ORACLE\\ORADATA\\MKDB\\DATA01.DBF                     2097152

DATA                          8 C:\\ORACLE\\ORADATA\\MKDB\\DATA02.DBF                     1048576

SQL> ALTER DATABASE MKDB

  2  DATAFILE ‘C:\\ORACLE\\ORADATA\\MKDB\\DATA02.DBF’ RESIZE 3M;

데이터베이스가 변경되었습니다.

SQL> SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES

  2  FROM DBA_DATA_FILES

  3  WHERE TABLESPACE_NAME = ‘DATA’;

TABLESPACE_NAME         FILE_ID FILE_NAME                                               BYTES

——————– ———- ————————————————– ———-

DATA                          7 C:\\ORACLE\\ORADATA\\MKDB\\DATA01.DBF                     2097152

DATA                          8 C:\\ORACLE\\ORADATA\\MKDB\\DATA02.DBF                     3145728

SQL> ALTER DATABASE MKDB

  2  DATAFILE ‘C:\\ORACLE\\ORADATA\\MKDB\\DATA02.DBF’ RESIZE 1M;

데이터베이스가 변경되었습니다.

SQL> SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES

  2  FROM DBA_DATA_FILES

  3  WHERE TABLESPACE_NAME = ‘DATA’;

TABLESPACE_NAME         FILE_ID FILE_NAME                                               BYTES

——————– ———- ————————————————– ———-

DATA                          7 C:\\ORACLE\\ORADATA\\MKDB\\DATA01.DBF                     2097152

DATA                          8 C:\\ORACLE\\ORADATA\\MKDB\\DATA02.DBF                     1048576

SQL> ALTER DATABASE MKDB

  2  DATAFILE ‘C:\\ORACLE\\ORADATA\\MKDB\\DATA02.DBF’

  3  AUTOEXTEND ON;

데이터베이스가 변경되었습니다.

SQL> CREATE TABLESPACE APPL_DATA

  2  DATAFILE ‘C:\\ORACLE\\ORADATA\\MKDB\\APPL01.DBF’ SIZE 10K;

테이블 영역이 생성되었습니다.

SQL> SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES

  2  FROM DBA_DATA_FILES

  3  WHERE TABLESPACE_NAME = ‘APPL_DATA’;

TABLESPACE_NAME         FILE_ID FILE_NAME                                               BYTES

——————– ———- ————————————————– ———-

APPL_DATA                     9 C:\\ORACLE\\ORADATA\\MKDB\\APPL01.DBF                       10240

SQL> SELECT TABLESPACE_NAME, BYTES, BLOCKS

  2  FROM DBA_FREE_SPACE

  3  WHERE TABLESPACE_NAME = ‘APPL_DATA’;

TABLESPACE_NAME           BYTES     BLOCKS

——————– ———- ———-

APPL_DATA                  8192          4

SQL> CREATE TABLE TEST (NUM  NUMBER)

  2  TABLESPACE APPL_DATA

  3  STORAGE(INITIAL 50K);

CREATE TABLE TEST (NUM  NUMBER)

*

1행에 오류:

ORA-01658: APPL_DATA 테이블스페이스에 세그먼트에 대한 INITIAL 확장 영역을 작성할 수 없습니다

SQL> CREATE TABLE TEST (NUM  NUMBER)

  2  TABLESPACE APPL_DATA

  3  STORAGE(INITIAL 4K);

테이블이 생성되었습니다.

SQL> CREATE OR REPLACE PROCEDURE test_in IS

  2  BEGIN

  3     FOR ctr IN 1..10000 LOOP

  4       INSERT INTO Test VALUES (ctr);

  5     END LOOP;

  6  END;

  7  /

프로시저가 생성되었습니다.

SQL> EXECUTE TEST_IN

BEGIN TEST_IN; END;

*

1행에 오류:

ORA-01653: SYSTEM.TEST 테이블을 5(으)로 APPL_DATA 테이블스페이스에서 확장할 수 없습니다

ORA-06512: “SYSTEM.TEST_IN”, 줄 4에서

ORA-06512: 줄 1에서

SQL> ALTER TABLESPACE APPL_DATA

  2  ADD DATAFILE ‘C:\\ORACLE\\ORADATA\\MKDB\\APPL02.dbf’ SIZE 400K;

테이블 영역이 변경되었습니다.

SQL> EXECUTE TEST_IN

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> SELECT TABLESPACE_NAME, BYTES

  2  FROM DBA_FREE_SPACE

  3  WHERE TABLESPACE_NAME = ‘APPL_DATA’;

TABLESPACE_NAME           BYTES

——————– ———-

APPL_DATA                264192

SQL> SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES

  2  FROM DBA_DATA_FILES;

TABLESPACE_NAME         FILE_ID FILE_NAME                                               BYTES

——————– ———- ————————————————– ———-

SYSTEM                        1 C:\\ORACLE\\ORADATA\\MKDB\\SYSTEM01.DBF                  88342528

RBS                           2 C:\\ORACLE\\ORADATA\\MKDB\\RBS01.DBF                     10485760

USERS                         3 C:\\ORACLE\\ORADATA\\MKDB\\USERS01.DBF                    3145728

TEMP                          4 C:\\ORACLE\\ORADATA\\MKDB\\TEMP01.DBF                    10485760

TOOLS                         5 C:\\ORACLE\\ORADATA\\MKDB\\TOOLS01.DBF                    4194304

INDX                          6 C:\\ORACLE\\ORADATA\\MKDB\\INDX01.DBF                     2097152

DATA                          7 C:\\ORACLE\\ORADATA\\MKDB\\DATA01.DBF                     2097152

DATA                          8 C:\\ORACLE\\ORADATA\\MKDB\\DATA02.DBF                     1048576

APPL_DATA                     9 C:\\ORACLE\\ORADATA\\MKDB\\APPL01.DBF                       10240

APPL_DATA                    10 C:\\ORACLE\\ORADATA\\MKDB\\APPL02.DBF                      409600

10 개의 행이 선택되었습니다.

SQL> SELECT TABLESPACE_NAME, BYTES, BLOCKS

  2  FROM DBA_FREE_SPACE

  3  WHERE TABLESPACE_NAME = ‘APPL_DATA’;

TABLESPACE_NAME           BYTES     BLOCKS

——————– ———- ———-

APPL_DATA                264192        129

SQL> SELECT SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES, BLOCKS, EXTENTS

  2         INITIAL_EXTENT, NEXT_EXTENT

  3  FROM USER_SEGMENTS

  4  WHERE SEGMENT_NAME = ‘TEST’

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES  BLOCKS INITIAL_EXTENT NEXT_EXTENT

———— ———— ————— —— —— ————– ———–

TEST         TABLE        APPL_DATA       151552     74              6       83968

******************************************************

<삭제>

DROP TABLESPACE TBNAME

[INCLUDING CONTENTS [CASCADE CONSTRAINTS]]

CF. 삭제된 테이블스페이스를 디폴트스페이스로 가지는 사용자는 객체를 생성할 수 없다.

    디폴트스페이스를 존재하는 다른 스페이스로 변경 필요함..

    ORA-00959: 테이블 영역 ‘TEST’ 가 존재하지 않습니다

CF. 테이블스페이스가 비어있지 않으면 INCLUDING CONTENTS 옵션사용

    (ORA-01549 : 테이블스페이스가 비어있지 않다)

CF. 읽기전용 테이블스페이스도 삭제가능.

CF. 테이블스페이스 삭제

    데이터를 가지고 있으면 INCLUDING CONTENTS 없이는 삭제불가.

    테이블스페이스가 삭제되면 데이터파일은 컨트롤파일내에서 파일에 대한 포인터만

    삭제되므로 운영체제 상에서 명시적으로 파일을 삭제해야 함.

    테이블스페이스가 READ ONLY 라도 세그먼트와 함께 삭제가능.(이는 DROP 명령어가

    테이블스페이스를 구성하는 물리적 파일이 아닌 데이터 딕셔너리만 갱신하므로가능)

    테이블스페이스를 삭제하기 전에이 안에 포함된 어떤 세그먼트에도 트랜잭션이

    발생할 수 없도록 테이블스페이스를 오프라인으로 놓으면 좋다.

<<오라클 스토리지>>

<BLOCK>

CF. BLOCK구조

BLOCK HEADER(85~100 BYTES): 일반적인 BLOCK정보, BLOCK ID, BLOCK 위치, 세그먼트 형태

TABLE DIRECTORY : 클러스터시 테이블정보

ROW DIRECTORY(2BYTE/ROW) : BLOCK내의 ROW 관련 정보

FREE SPACE : NEW ROW INSERT 나 UPDATE 시 사용(PCTFREE, PCTUSED 에 영향)

ROW DATA : 테이블이나 인덱스 데이터

PCTFREE(10%)

PCTUSED(40%)

INITRANS(1~255:1)

MAXTRANS(1~255)

CF. ROW CHAINING

    ROW MIGRATION

<익스텐트>

INITIAL(10K)

NEXT(10K)

MINEXTENTS(1)

MAXEXTENTS(121)

PCTINCREASE(50)

FREELIST

OPTIMAL(NULL)

CF. 익스텐트의 할당

    생성, 확장, 변경(ALLOCATE)

CF. 익스텐트의 해제

    삭제(DROP), 변경(DEALLOCATE UNUSED), 절삭(TRUNCATE), 롤백세그먼트의 경우 자동적 해제

CF. FREELISTS 와 FREELISTS GROUP은 테이블스페이스에 할당할 수 없다.

CF. OPTIMAL 옵션은 롤백세그먼트에만 지정가능.

<세그먼트>

논리적 분류

1. DATA SEGMENT

원칙적으로 데이터행의 수는 제한 없다.

행체이닝이 발생한 데이터행은 여러 BLOCK에 나누어 저장된다.

데이터 행 사 이에는 빈 공간이 없이 연속적으로이어져 저장된다.

각각의 데이터 행에 대하여 그 컬럼이 지정하는 길이와 형태로 저장된다.

모든 데이터는 데이터의 길이를 나타내는 값을 가지고 있으며, 0은 NULL을 의미한다.

2. CLUSTER

클러스터 키(동일한 클러스터 키는 동일한 클러스터 BLOCK에 저장)

클러스터 인덱스(DBA만이 생성가능,미존재시에는 클러스터 테이블로의 DML(U,I,D)수행 안됨)

3. INDEX SEGMENT

테이블 또는 클러스터에 대한 인덱스가 만들어지면

각각의 인덱스에 대한 B*TREE 구조의 인덱스 세그먼트가

테이블 또는 클러스터 세그먼트와 물리적으로 다른 테이블스페이스에 생성된다.

4. TEMPORARY SEGMENT

CREATE TABLESPACE의 TEMPORARY 옵션을 사용하여 생성

SORTING 또는 JOIN에 사용되는 데이터 임시 보관

SMON가 관리

리두로그에 기록되지 않음

5. BOOTSTRAP SEGMENT = CACHE SEGMENT

DATA DICTIONARY VIEW 에 대한 정의 포함

데이터베이스 개방시 데이터 사전을 로드하기 위해 사용

6. ROLLBACK SEGMENT(DBA_ROLLBACK_SEGS, DBA_SEGMENTS, V$ROLLSTAT)

BEFORE IMAGE를 저장하는 CIRCULAR SEGMENT

ROLLBACK, READ CONSISTENCY, RECOVERY 에 사용

각 트랜잭션에는 하나의 롤백세그먼트를 지정받고,

지정된 롤백세그먼트에는 그 트랜잭션에 의해 변화가 생긴 데이터 저장.

하나의 롤백세그먼트에는 여러 트랜잭션에 의해 지정될 수 있다.

CF. 롤백세그먼트의 역할

    트랜잭션 롤백, 트랜잭션 복구, 읽기 일관성

CF. 트랜잭션과 롤백세그먼트

1. 트랜잭션이 시작되려면 롤백세그먼트가 반드시 있어야 한다.

2. 오라클은 트랜잭션을 여러 롤백세그먼트 중에서가장 적은 수의 트랜잭션이 지정된

   롤백세그먼트를 선택하여 할당해준다.

   만약 사용자가 특정 트랜잭션에 대해 롤백세그먼트를 지정하고 싶으면 다음 명령 수행

   SET TRANSACTION USE ROLLBACK SEGMENT RBSNAME;

3. 트랜잭션은 현재의 롤백세그먼트 내에 EXTENT가 꽉차면 다음 EXTENT로 옮겨가는

   ORDERED CIRCULAR FASHION 으로 EXTENT를 사용한다.

4. 롤백세그먼트의 한 EXTENT에 하나이상의 트랜잭션이 쓸 수 있지만

   롤백세그먼트 내의 한 BLOCK은 오직 하나의 트랜잭션에 대한 정보만을 기록

CF. 자동크기감소(할당해제) – OPTIMAL

    롤백세그먼트가 성장한 후 ACTIVE중이던 익스텐트가 트랜잭션의 완료로 INACTIVE되면

    OPTIMAL에 명시된 크기로 익스텐트를 반환하여 공간을 할당 해제

CF. 수동크기감소(할당해제)

    ALTER ROLLBACK SEGMENT RBSNAME

    SHRINK [TO #[K|M]];

종류:

PRIVATE(ROLLBACK_SEGMENTS 파라미터로 설정)

PUBLIC(오라클 병렬 서버의 경우 하나이상의 인스턴트가 공유하여 사용)

DEFERRED(테이블스페이스가 OFFLINE이면 -> 시스템테이블스페이스에 생성)

생성문법:

CREATE [PUBLIC] ROLLBACK SEGMENT RBSNAME

[TABLESPACE]

[STORAGE] (INITIAL|NEXT|MINEXTENTS|MAXEXTENTS|PCTINCREASE|FREELISTS|FREELIST GROUPS)

[OPTIMAL] [INTERGER[K|M]|NULL]

CF. STORAGE 절의 MINEXTENTS는 반드시 2이상이어야 함(기본값:2)

CF. MAXEXTENTS는 절대로 UNLIMITED를 사용해선 안 됨(기본값:121)

CF. PCTINCREASE는 반드시 0이여야 함(기본값:0)

CF. IF (롤백세그먼트 > OPTIMAL) {

        롤백세그먼트에 할당되었던 익스텐트들을 삭제(DE-ALLOCATE);

        그 크기를 OPTIMAL 에 맞춤;

    }

CF. 시스템테이블스페이스에 위치하는 시스템롤백세그럼트의 세그먼트ID는 0이다.

CF. PUBLIC, PRIVATE 값은 생성 후 변경불가

CF. MUST MINEXTENTS >= 2 (recommended value 20)

CF. MUST OPTIMAL >= INITIAL * MINEXTENTS

CF. PCTINCREASE는 지정불가, 항상 0

CF. 가능하면 INITIAL과 NEXT는 같은 크기로 하여 모든 익스텐트가 동일 크기를 갖도록

CF. 가급적 MAXEXTENTS를 UNLIMITED로 하지말 것

    (롤백세그먼트와 데이터파일이 사용공간을 다 쓸 수 있으므로)

CF. 경합과 단편화를 최소화하기 위해 롤백세그먼트를 항상 분리된 테이블스페이스에 위치

예제

CREATE ROLLBACK SEGMENT RBS01

TABLESPACE RBS;

(STORAGE는 10K, 10K, 2, 121, 0의 값을가짐)

생성절차

:  롤백세그먼트 생성

-> ROLLBACK_SEGMENTS 파라미터 수정(항상 온라인으로 유지해야할 롤백세그먼트 지정)

   (만약, 생성만 하고 명기하지 않으면 재시동후 OFFLINE 상태이고

   생성은 안하고 명기만 하면 데이터베이스를 열수 없음 ORA-1534)

-> DB RESTART

롤백세그먼트수정

ALTER ROLLBACK SEGMENT RBSNAME

[ONLINE|OFFLINE]

[STORAGE]

[SHRINK [TO #[K|M]];

CF. SHRINK TO는 온라인상태에서 명령가능.

    (오프라인이면 ORA-01598: 롤백 세그먼트가 온라인이 아닙니다)

CF. SHRINK TO 로 크기 줄인 후(10K에서 1K로,100K에서 10K로)

    DBA_ROLLBACK_SEGS 에 반영이 안 됨. DB RESTART 후에도 마찬 가지(???)

CF. STORAGE의 INITIAL을 수정하니

    (ORA-02203: 영역 옵션이 허가되지 않음) 발생

CF. STORAGE의 NEXT, MAXEXTENTS는 임의의 상태(줄이거나 늘리거나)로

    변경가능하고 DBA_ROLLBACK_SEGS 에 바로 반영됨.

CF. STORAGE의 MINEXTENTS를 현재의 2에서 3으로 바꿔보니

    (ORA-01570: MINEXTENTS는 현재 할당된 2보다 클 수 없습니다)가 발생함.

CF. 정의 수정시 수정된 정의는 수정이 발생한 시점이후로만 적용

    그러므로, INITIAL 과 MINEXTENTS의 값은 수정이 불가능한 것 같음.

CF. 롤백세그먼트 SHRINKAGE 시에

    지정한 크기로 줄일 때 EXTENT가 ACTIVE되어 있으면 수행되지 않음.

    INTEGER값을 지정하지 않으면 기본으로 OPTIMAL까지 크기를 줄이게 된다.

    지정된 INTEGER가 롤백세그먼트 크기보다 크다면이 명령을 무시된다.

롤백세그먼트삭제

OFFLINE 상태의 롤백세그먼트만 삭제가능.

DROP ROLLBACK SEGMENT RBSNAME;

CF. 롤백세그먼트의 선정

유형 : 시스템

       비시스템(PRIVATE|PUBLIC)

갯수 : OLTP(크기는 작지만 많은 수의 세그먼트, 트랜잭션 4개당 롤백세그먼트 1개)

       BATCH(크기는 크지만 적은 수의 세그먼트)

크기설정 : 롤백에 필요한 정보를 저장하는데 필요한 바이트 수는 실제 처리되는 데이터와

         트랜잭션의 유형(입력, 수정, 삭제)에 의한다.

           입력 – ROWID 만 저장(최소 사용)

           삭제 – 행 전체를 저장(최대 사용)

           수정 – 수정된 컬럼의 갯수에 의함(인덱스된 컬럼의 경우

                  OLD DATA VALUE, OLD INDEX VALUE, NEW INDEX VALUE 저장)

CF. 트랜잭션을 발생시킬 충분한 공간이 없을 때

ORA-01560 테이블스페이스에 공간이 없을 때

-> 테이블스페이스 내에 데이터 파일 확장

    데이터 파일에 AUTOEXTEND 설정

    테이블스페이스에 파일 추가

ORA-01628 롤백세그먼트의 EXTENT 수가 MAXEXTENTS 에 도달

-> 롤백세그먼트에 대한 MAXEXTENTS 증가

    더 큰 EXTENT 크기를 갖는 롤백세그먼트를 재생성

CF. 읽기 일관성 에러

ORA-01555 SNAPSHOT TOO OLD

-> MINEXTENTS를 보다 크게

    EXTENT 크기를 보다 크게

    OPTIMAL 값을 보다 크게 설정

CF. BLOCK킹 트랜잭션

-> SELECT S.SID, S.SERIAL#, T.START_TIME, T.XIDUSN, S.USERNAME

    FROM V$SESSION S, V$TRANSACTION T, V$ROLLSTAT R

    WHERE S.SADDR = T.SES_ADDR

    AND   T.XIDUSN = R.USN

    AND   ((R.CUREXT = T.START_UEXT-1)

    OR    ((R.CUREXT = R.EXTENTS-1) AND T.START_UEXT = 0));

  를 사용하여 트랜잭션을 알아낸 후, 트랜잭션 정상 종료 또는 강제로 사용자 SESSION KILL

CF. 테이블스페이스 오프라인시

ORA_01546 ACTIVE ROLLBACK SEGMENT 포함한 테이블스페이스를 오프라인시

-> DBA_ROLLBACK_SEGS 조회 -> 테이블스페이스 내의 모든 롤백세그먼트를 오프라인으로

-> V$TRANSACTION을 검사하여 현재 어느 트랜잭션이이들 롤백세그먼트를 사용하는지 확인

-> V$SESSION을이용하여 사용자 이름과 SESSION정보 입수

-> 필요시엔 SESSION 종료

-> 테이블스페이스를 오프라인으로..

CF. 롤백세그먼트 조회

데이터베이스 내의 롤백세그먼트 정보

  DBA_ROLLBACK_SEGS

롤벡세그먼트의 상태정보

  V$ROLLNAME

  V$ROLLSTAT

사용중인 롤백세그먼트

  V$SESSION

  V$TRANSACTION

CF. 롤백세그먼트의 일반적인 정보

    SELECT SEGMENT_NAME, TABLESPACE_NAME, MIN_EXTENTS, PCT_INCREASE, STATUS

    FROM DBA_ROLLBACK_SEGS;

CF. 현재 인스턴트가 사용하고 있는 롤백세그먼트에 대한 통계 검색

    SELECT N.NAME, S.EXTENTS, S.RSSIZE, S.OPTSIZE, S.HWMSIZE, S.XACTS, S.STATUS

    FROM V$ROLLNAME N, V$ROLLSTAT S

    WHERE N.USN = S.USN;

CF. 롤백세그먼트에 대한 경합

    SELECT N.NAME, ROUND(100*S.WAITS/S.GETS)

    FROM V$ROLLNAME N, V$ROLLSTAT S

    WHERE N.USN = S.USN;

<스토리지 관리>

하이워터마크(DBMS_SPACE.UNUSED_SPACE)이후의 미사용공간을 사용가능하게 하려면

ALTER TABLE TNAME DEALLOCATE UNUSED;

만약 세그먼트가 비어있다면 비어있는 세그먼트를 사용가능하게 하기 위하여

TRUNCATE TABLE TBNAME DROP STORAGE;

BLOCK단편화(FRAGMENTATION)가 발생하였을 때

테이블스페이스 내의 익스텐트를 합치기 위해

ALTER TABLESPACE APPL_DATA COALESCE; (비어있는 셀들의 병합과 같음)

(DBA_FREE_SPACE_COALESCED 로 확인)

<스토리지 관리 데이터 사전 뷰>

USER/DBA_EXTENTS

USER/DBA_SEGMENTS

USER/DBA_TABLESPACES

USER/DBA_FREE_SPACE

DBA_DATA_FILES

*************************************************************

<<오라클 사용자 생성 및 관리>>

1. 생성

CREATE USER USERNAME IDENTIFIED [BY PASSWORD|EXTERNALLY]

[DEFAULT TABLESPACE]

[TEMPORARY TABLESPACE]

[QUOTA] [#[K|M]|UNLIMITED] ON TABLESPACENAME ..

[PROFILE]

[PASSWORD EXPIRE]

[ACCOUNT [LOCK|UNLOCK]]

2. 수정

ALTER USER USERNAME IDENTIFIED [BY PASSWORD|EXTERNALLY]

[DEFAULT TABLESPACE]

[TEMPORARY TABLESPACE]

[QUOTA] [#[K|M]|UNLIMITED] ON TABLESPACENAME ..

[PROFILE]

[PASSWORD EXPIRE]

[ACCOUNT [LOCK|UNLOCK]]

[DEFAULT ROLE][ROLENAME,..|ALL[EXCEPT ROLENAME,..]|NONE]  –> 수정시에만..

3. 삭제

DROP USER USERNAME [CASCADE]

4. 사용자모니터링

USER/DBA/ALL_USERS;

USER/DBA_TS_QUOTAS; (MAX_BYTES와 MAX_BLOCKS의 값이 -1이면 UNLIMITED를 의미함)

5. 사용자 세션의 KILL

SELECT USERNAME, SID, SERIAL# FROM V$SESSION;

ALTER SYSTEM KILL SESSION ‘SID, SERIAL#’;

<<권한>>

1. 시스템권한의 부여

GRANT [SYSTEM_PRIVILEGE|ROLENAME]

TO [ROLENAME|USERNAME|PUBLIC]

[WITH ADMIN OPTION]

2. 객체권한의 부여

GRANT [OBJECT_PRIVILEGE|ALL [PRIVILEGES]] [(COLUMN_NAME)]

ON [SCHEMANAME.]OBJECTNAME

TO [USERNAME|ROLENAME|PUBLIC]

[WITH GRANT OPTION]

3. 시스템권한의 회수

REVOKE [SYSTEM_PRIVILEGE|ROLENAME]

FROM [USERNAME|ROLENAME|PUBLIC]

4. 객체권한의 회수

REVOKE [OBJECT_PRIVILEGE|ALL [PRIVILEGES]]

ON [SCHEMANAME.]OBJECTNAME

FROM [USERNAME|ROLENAME|PUBLIC]

[CASCADE CONSTRAINTS | FORCE]

5. 조회

시스템 권한 : GRANTEE,PRIVILEGE,ADMIN_OPTION

            DBA/USER_SYS_PRIVS; (ALL_SYS_PRIVS 없음)

객체 권한 : GRANTOR,GRANTEE,OWNER,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,PRIVILEGE,GRANTABLE

            DBA/USER/ALL_TAB_PRIVS;

            USER/ALL_TAB_PRIVS_MADE;

            USER/ALL_TAB_PRIVS_RECD;

            DBA/USER/ALL_COL_PRIVS;

            USER/ALL_COL_PRIVS_MADE;

            USER/ALL_COL_PRIVS_RECD;

            TABLE_PRIVILEGES;

            COLUMN_PRIVILEGES;

6. OBJECT 권한

                   TABLE    VIEW    SEQUENCE    PROCEDURE    SNAPSHOT

    —————————————————————–

    SELECT           O        O         O                        O

    INSERT           O        O

    UPDATE           O        O

    DELETE           O        O

    ALTER            O                  O

    INDEX            O

    REFERENCES       O

    EXECUTE                                         O

<<역할>> — 부여는 권한과 같음..

1. 생성

CREATE ROLE ROLENAME

[NOT IDENTIFIED|IDENTIFIED [BY PASSWORD|EXTERNALLY]]

2. 변경(역할 인증의 변경)

ALTER ROLE ROLENAME

[NOT IDENTIFIED|IDENTIFIED [BY PASSWORD|EXTERNALLY|GLOBALLY]]

3. 활성화/비활성화(CURRENT SESSION에서)

SET ROLE ROLENAME [IDENTIFIED BY PASSWORD]

         [ALL [EXCEPT ROLENAME]]

         [NONE]

4. 디폴트역할

ALTER USER USERNAME

DEFAULT ROLE ROLENAME

             [ALL [EXCEPT ROLENAME]]

             [NONE]

5. 조회

DBA_SYS_PRIVS;

DBA/USER_ROLE_PRIVS;

ROLE_SYS_PRIVS;

ROLE_TAB_PRIVS;

ROLE_ROLE_PRIVS;

SESSION_ROLES;

DBA_ROLES;

CF. DBA_SYS_PRIVS = ROLE_SYS_PRIVS + SUM(USER_SYS_PRIVS);

    DBA_ROLE_PRIVS = ROLE_ROLE_PRIVS + SUM(USER_ROLE_PRIVS);

<<시스템 자원 관리>>

프로파일 : 여러 resource의 limits를 정의해 놓은 것

           사용자들에게 할당

           활성 또는 비활성

           RESOURCE 관리를 단순화

           사용자가 많은 대용량 시스템이나 보안 규칙상 필요한 곳

프로파일 활성화/비활성화

  파라미터파일 : RESOURCE_LIMIT = TRUE;

  즉시반영 : ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

시스템리소스

  CPU TIME

  I/O OPERATION

  IDLE TIME

  CONNECT TIME

  PRIVATE SQL AREA/MTS SERVER

  CONCURRENT SESSION

암호사용

  암호 aging과 기간만료

  암호이력

  암호 복잡성 검증

  계정 잠금

<SESSION LEVEL>

CPU_PER_SESSION : 세션에서 사용되는 총 CPU 시간(1/100초)

LOGICAL_READS_PER_SESSION : 물리적 및 논리적으로 읽은 데이터 블럭 수(디스크 독점 제한)

SESSIONS_PER_USER : 각 사용자에게 허용되는 동시 세션 수

CONNECT_TIME : CONNECT시 소요되는 시간(분)

IDLE_TIME : INACTIVE상태의 유휴시간(분)

PRIVATE_SGA : MTS 환경에서..

<CALL LEVEL>

CPU_PER_CALL : 한번의 CALL당 CPU시간(1/100초)

LOGICAL_READS_PER_CALL : 한번의 CALL당 물리적 및 논리적으로 읽은 데이터 블럭의 수

COMPOSITE_LIMIT == CPU_PER_SESSION+LOGICAL_READS_PER_SESSION+CONNECT_TIME+PRIVATE_SGA)

FAILED_LOGIN_ATTEMPTS : 계정을 잠그기전까지 로그인 시도하다 실패한 횟수

PASSWORD_LOCK_TIME : 암호가 기간 만료되어 계정이 잠긴채로 남아있는 날수

PASSWORD_LIFE_TIME : 날수로 표시되는 암호의 수명으로이 기간이 지나면 만료

PASSWORD_GRACE_TIME : 암호가 기간만료된 후 첫번째 성공적인 로그인부터

                      암호변경을 할 수 있도록 하는 유예기간

PASSWORD_REUSE_TIME : 암호가 재사용되기까지의 날수

PASSWORD_REUSE_MAX : 암호가 재사용되기까지 설정되는 암호의 수

PASSWORD_VERIFY_FUNCTION : 암호를 할당하기 전 복잡성 검사를 수행할 PL/SQL 함수

1. 프로파일 생성

CREATE PROFILE PROFILENAME LIMIT RESOURCE_PARAMETERS

                                 PASSWORD_PARAMETERS

<RESOURCE_PARAMETERS>

[SESSIONS_PER_USER]

[CPU_PER_SESSION]

[CPU_PER_CALL]

[CONNECT_TIME]

[IDLE_TIME]                              [#|UNLIMITED|DEFAULT]

[LOGICAL_READS_PER_SESSION]

[LOGICAL_READS_PER_CALL]

[COMPOSITE_LIMIT]

[PRIVATE_SGA]————————— [#[K|M]|UNLIMITED|DEFAULT]

<PASSWORD_PARAMETERS>

[FAILED_LOGIN_ATTEMPTS]

[PASSWORD_LOCK_TIME]

[PASSWORD_LIFE_TIME]

[PASSWORD_REUSE_MAX|PASSWORD_REUSE_TIME] [#|UNLIMITED|DEFAULT]

[PASSWORD_GRACE_TIME]

[PASSWORD_VERIFY_FUNCTION]————– [FUNCTIONNAME|NULL|DEFAULT]

EX) 프로파일 생성

    CREATE PROFILE NEW_PROFILE LIMIT

    SESSIONS_PER_USER 2

    CPU_PER_SESSION 20000

    IDLE_TIME 60;

    프로파일 지정

    ALTER USER SCOTT

    PROFILE NEW_PROFILE;

2. 프로파일 변경

ALTER PROFILE PROFILENAME LIMIT

LIMITATIONS…  [#|UNLIMITED|DEFAULT]

COMPOSITE_LIMIT [#|UNLIMITED|DEFAULT]

3. 프로파일 삭제

DROP PROFILE PROFILENAME [CASCADE]

4. DBA_USERS; DBA_PROFILES;

   USER_RESOURCE_LIMITS; USER_PASSWORD_LIMITS; RESOURCE_COST;

CF. SELECT P.PROFILE, P.RESOURCE_NAME, P.LIMIT

    FROM DBA_PROFILES P, DBA_USERS U

    WHERE P.PROFILE = U.PROFILE

    AND   U.USERNAME LIKE ‘%&USERNAME%’

    AND   P.RESOURCE_TYPE = ‘KERNEL’;

<<감사>>

AUDIT SQL_STATEMENTS : 문장/권한 감사

AUDIT SCHEMA_OBJECTS : 객체 감사

의심되는 활동 조사,

데이터베이스 활동 모니터링,

데이터베이스 활동에 대한 자료수집

1. 문장/권한 감사

AUDIT [STMT_OPTS|SYSTEM_PRIV|ALL]

[BY USERNAME]

[BY [SESSION|ACCESS]]    -> DEFAULT는 BY ACCESS

[WHENEVER [NOT] SUCCESSFUL]  -> 생략하면 성공/실패 모든 경우를 감사

CF. STATEMENT_OPTION

    CONNECT: SESSION

    RESOURCE: ALTER SYSTEM, CLUSTER, DATABASE LINK, PROCEDURE,

              ROLLBACK SEGMENT, SEQUENCE, SYNONYM, TABLE, TABLESPACE, VIEW

    DBA: SYSTEM AUDIT, PUBLIC DATABASE LINK,

         PUBLIC SYNONYM, ROLE, SYSTEM GRANT, USER

2. 문장/권한 감사 해지

NOAUDIT [STMT_OPTS|SYSTEM_PRIV|ALL]

[BY USERNAME]

[WHENEVER [NOT] SUCCESSFUL]

3. 객체 감사

AUDIT [OBJ_OPTS|ALL]

ON [[SCHEMA.]OBJECT|DEFAULT]  -> ALL_DEF_AUDIT_OPTS에서 조회

[BY [SESSION|ACCESS]]     -> DEFAULT는 BY SESSION

[WHENEVER [NOT] SUCCESSFUL]

4. 객체 감사 해지

NOAUDIT [OBJ_OPTS|ALL]

ON [[SCHEMA.]OBJECT|DEFAULT]

[WHENEVER [NOT] SUCCESSFUL]

5. 감사 추적

보존을 원하는 데이터를 임시테이블이나 파일로 복사.

SYS.AUD$ 테이블의 모든 데이터 삭제.

복사해놓았던 데이터를 SYS.AUD$ 로 로드.

6. 감사 조회(감사옵션검색 및 감사결과 기록 검색)

STMP_AUDIT_OPTION_MAP

AUDIT_ACTIONS

ALL_DEF_AUDIT_OPTS

DBA_STMT_AUDIT_OPTS

DBA_PRIV_AUDIT_OPTS

USER/DBA_OBJ_AUDIT_OPTS

USER/DBA_AUDIT_TRAIL

USER/DBA_AUDIT_SESSION

USER/DBA_AUDIT_STATEMENT

USER/DBA_AUDIT_OBJECT

DBA_AUDIT_EXISTS

CF. SELECT USERNAME, TIMESTAMP, ACTION_NAME

    FROM DBA_AUDIT_TRAIL;

CF. SELECT USERID, OBJ$NAME, SES$ACTIONS, TIMESTAMP#

    FROM SYS.AUD$

    WHERE OBJ$NAME LIKE ‘%&OBJECT_NAME%’;

CF. SELECT * FROM DBA_OBJ_AUDIT_OPTS

    WHERE OBJECT_NAME LIKE ‘%&OBJECT_NAME%’;

CF. 항상 AUDIT 대상인이벤트

    데이터베이스 STARTUP과 SHUTDOWN

      데이터베이스를 시작하고 종료하는

      OS사용자, 단말기식별자, 날짜, TIMESTAMP, AUDIT기능 활성여부

    ADMINISTRAION권한으로 데이터베이스에 접속

      ADMIN권한을 가진 사용자에게 책임을 제공하기 위하여

      SYSOPER 또는 SYSDBA로 접속하는 OS사용자를 상세히 AUDITING 하여 기록

CF. 초기화파라미터 내의 AUDIT_TRAIL = DB(TRUE) | OS | NONE(FALSE)

CF. AUDIT TRAIL이란

    AUDIT 에 의해 생성된 레코드를 저장하는 SYS.AUD$라는 데이터사전상의 테이블

      문장을 실행한 사용자

      실행된 문자의 유형이나 사용된 권한의 유형을 나타내는 ACTION CODE

      문장에서 참조된 오브젝트

      문장이 실행된 날짜/시간

CF. AUDIT TRAIL의 특징

    사용자의 트랜잭션과는 독립적

    트랜잭션 롤백시에도 기록은 남음

    BY ACCESS 별 AUDIT는 수행될 때마다 AUDIT TRAIL에 하나의 레코드를 기록

    BY SESSION에 의한 AUDIT는 오브젝트에 대해 SESSION당 하나의 레코드만 기록

CF. AUDIT TRAIL 모니터링

    AUDIT TRAIL의 크기와 증가상황을 모니터링

    AUDIT TRAIL이 FULL상태일때는 새로운 AUDIT 레코드는 입력될 수 없고 에러발생

    AUDIT TRAIL을 관리하기 위해 정기적으로 AUDIT 레코드들을 제거

    (DELETE FROM SYS.AUD$ WHERE TIMESTAMP# < SYSDATE – 90)

<<테이블>>

1. 생성

CREATE TABLE TABLE_NAME (

  COLUMN_NAME DATATYPE [DEFAULT EXPR] [COLUMN_CONSTRAINT][, …]

)

[PCTFREE][PCTUSED][INITRANS][MAXTRANS] -> BLOCK PARAMETER

[STORAGE](INITIAL NEXT MINEXTENTS MAXEXTENTS PCTINCREASE FREELISTS)

[TABLESPACE]

[CLUSTER]

[ENABLE|DISABLE]

[AS subquery]

[LOGGING|NOLOGGING]

[CACHE|NOCACHE]

2. 수정

ALTER TABLE TABLE_NAME

[ADD COLUMN_NAME DATATYPE [DEFAULT EXPR] [COLUMN_CONSTRAINT][, …]]

[MODIFY COLUMN_NAME [DATATYPE] [DEFAULT EXPR] [COLUMN_CONSTRAINT][, …]]

[PCTFREE][PCTUSED][INITRANS][MAXTRANS] -> BLOCK PARAMETER

[STORAGE](NEXT MINEXTENTS MAXEXTENTS PCTINCREASE)

[ALLOCATE EXTENT SIZE # [K|M] | DATAFILE ‘FILENAME’]

[DEALLOCATE UNUSED [KEEP # [K|M]]]

3. 삭제

DROP TABLE TABLE_NAME [CASCADE CONSTRAINTS];

DELETE [FROM] TABLE_NAME [WHERE CALUSE];

TRUNCATE TABLE TABLE_NAME [[DROP|REUSE] STORAGE];

CF. PCTFREE = (평균행크기-초기행크기)/평균행크기*100

CF. PCTUSED = 100-PCTFREE-평균행크기/사용가능데이터공간*100

CF. 오라클데이터타입

    SCALAR : CHAR, NCHAR, VARCHAR2, NVARCHAR2, LONG, RAW, LONG RAW

             NUMBER, DATE, BLOB, CLOB, NCLOB, BFILE

    COLLECTION : VARRAY, TABLE

    RELATIONSHIP : REF

CF. ROWID

    DATA OBJECT NUMBER | RELATIVE FILE NUMBER | BLOCK NUMBER | SLOT NUMBER

    OOOOOO             | FFF                  | BBBBBB       | SSS

    데이터베이스 각 행에 대한 고유한 식별자

    행의 위치를 알아내는데 사용(행의 물리적 위치를 가지지는 않음)

    테이블 내의 행에 접근하는가장 빠른 수단 제공

    주어진 키 값으로 행을 지정할 수 있도록 인덱스에 저장

CF. 행의 특성

    한 테이블에 저장되는 행의 수는 제한 없음

    한 행은 여러 BLOCK에 걸쳐 저장가능 -> CHAINED ROWS

    행들은가변 또는 고정으로 저장가능

    각 컬럼들은 MIGRATION 값의 오른쪽에 계속 저장되며, 컬럼의 길이와 값이 함께 저장

    컬럼길이가 0인것은 NULL컬럼

    ROW HEADER : 행의 컬럼수, 행체이닝 정보, 행의 LOCK 상태 저장

    HEADER BLOCK(!=BLOCK HEADER) : 한 세그먼트의 첫번째 익스텐트의 첫번째 BLOCK

                                   FREELIST 정보와 EXTENT 정보가 포함

CF. HIGH WATER MARK

    테이블에 사용되었던 마지막 BLOCK을가리킴

    데이터가 삽입됨에 따라 마지막으로 사용된 BLOCK을가리키기 위해이동

    테이블로부터 행이 삭제될 때는 재설정되지 않음

    테이블의 세그먼트 헤더(BLOCK 헤더??)에 저장

    서버는 FULL TABLE SCAN시 HIGH WATER MARK까지의 모든 BLOCK을 읽음

CF. HIGH WATER MARK 찾기

    테이블분석

      ANALYZE TABLE TABLE_NAME COMPUTE STATISTICS;(HWM, UNUSED BLOCK, AVERAGE ROW LENGTH)

      ANALYZE TABLE TABLE_NAME ESTIMATE STATISTICS SAMPLE [# ROWS|# PERCENT];

      SELECT BLOCKS, EMPTY_BLOCKS

      FROM USER_TABLES

      WHERE TABLE_NAME = ‘TABLE_NAME’;

    DBMS_SPACE 패키지이용

      HWM를 찾고 HWM위에 남은 BLOCK 검색,

      ANALYZE보다 최적화 영향주지 않고 더 빨리 정보 검색

CF. HIGH WATER MARK이후의 사용되지 않은 공간 할당 해제

    ALTER TABLE TABLE_NAME DEALLOCATE UNUSED;

    할당 해제 후에 익스텐트 증가는이전과 동일

    SEGMENT_NAME                    EXTENT_ID     BLOCKS

    —————————— ———- ———-

    TEST                                    0          5

    TEST                                    1          5

    TEST                                    2         10

    TEST                                    3          5  -> 공간할당해제

    TEST                                    4         20

    TEST                                    5         30

    TEST                                    6         45

    TEST                                    7         65

    SEGMENT_NAME                    EXTENT_ID     BLOCKS

    —————————— ———- ———-

    TEST                                    0          5

    TEST                                    1          5

    TEST                                    2         10

    TEST                                    3         15

    TEST                                    4         20

    TEST                                    5         30

    TEST                                    6         45

    TEST                                    7         65

CF. SELECT COUNT(DISTINCT SUBSTR(ROWID, 1, 15)) BLOCKS_CONTAIN_ROWS

    FROM TEST; (조금이라도 사용중인 블록)

CF. CHAINING & MIGRATION(UTLCHAIN.SQL 실행 – CHAINED_ROWS 테이블 생성)

    ANALYZE TABLE TABLE_NAME LIST CHAINED ROWS;

    SELECT * FROM CHAINED_ROWS;

    CHAINED ROWS를 임시테이블에 저장하고 원래 테이블에서 삭제

      CREATE TABLE TEMPTABLE

      AS

      SELECT * FROM TABLE_NAME

      WHERE ROWID IN (

        SELECT HEAD_ROWID

        FROM CHAINED_ROWS

        WHERE TABLE_NAME = ‘TABLE_NAME’

      );

      DELETE FROM TABLE_NAME

      WHERE ROWID IN (

        SELECT HEAD_ROWID

        FROM CHAINED_ROWS

        WHERE TABLE_NAME = ‘TABLE_NAME’

      );

    임시테이블에 저장한 CHAINED ROWS를 다시 원래의 테이블에 재저장

      INSERT INTO TABLE_NAME

      SELECT * FROM TEMPTABLE;

    CHAINED_ROWS 테이블을 CLEAR하고 테이블 다시 분석

    이상 없으면 임시테이블 삭제

      DELETE FROM CHAINED_ROWS;

      ANALYZE TABLE TABLE_NAME LIST CHAINED ROWS;

      SELECT * FROM CHAINED_ROWS;

      DROP TABLE TEMPTABLE;

<<인덱스>>

1. 생성

CREATE [UNIQUE|BITMAP] INDEX INDEXNAME ON TABLE_NAME (COLUMN_NAME [ASC|DESC], …)

[PCTFREE][INITRANS:2][MAXTRANS:255] -> BLOCK PARAMETER

[STORAGE](INITIAL NEXT MINEXTENTS MAXEXTENTS PCTINCREASE)

[TABLESPACE]

[CLUSTER]

[LOGGING|NOLOGGING]

[NOSORT|REVERSE]

2. 수정

ALTER INDEX INDEXNAME

[PCTFREE][INITRANS][MAXTRANS] -> BLOCK PARAMETER

[STORAGE](NEXT MINEXTENTS MAXEXTENTS PCTINCREASE)

[REBUILD [[REVERSE|NOREVERSE]|TABLESPACE TBSNAME]]

[ALLOCATE EXTENT [SIZE # [K|M] | DATAFILE ‘FILENAME’]]

[DEALLOCATE UNUSED [KEEP # [K|M]]]

3. 삭제

DROP INDEX INDEXNAME

CF. 컬림이 WHERE절 또는 JOIN식에 빈번하게 사용될 때

    컬럼값에 일정한 규칙이 없게 넓게 분포되어 있을 때

    컬럼값에 NULL값이 많이 포함되어 있을 때

    테이블 데이터의 총 크기가 큰 데 반해

    실제 질의하는 데이터의 양이 총 데이터의 10%이하일 때

CF. 인덱스 재구축

    ALTER INDEX INDEXNAME

    REBUILD [REVERSE|NOREVERSE][PHYSICAL_ATTRIB][TABLESPACE];

CF. ANALYZE INDEX INDEXNAME VALIDATE STRUCTURE;

CF. SELECT BLOCKS, BTREE_SPACE, USED_SPACE, PCT_USED, LF_ROWS, DEL_LF_ROWS

    FROM INDEX_STATS;

CF. 일반적인 B*TREE 인덱스의 개념

    테이블의 ROW와 하나씩 대응되는 별도의 객체

    OPTIMIZER가 최적의 실행경로를 설정하는데 중요한 FACTOR로 작용

    인덱스를 생성시킨 컬럼값+그 행의 논리적주소(ROWID)로 구성

    옵티마이저가 인덱스를 사용하는 실행계획을 수립했다면

      BTREE방식으로 조건을 만족하는 첫번째 인덱스행을 찾고(RANDOM ACCESS)

      액세스된 인덱스 행에 있는 ROWID를이용하여 테이블에 있는 행을 액세스한다.(RANDOM ACCESS)

      처리할 범위가 끝날 때까지 차례대로 다음 인덱스 행을 찾으면서(SCAN) 두번째 과정을 반복

    인덱스 구조를이용하면 필요한 데이터를 읽어 정렬한 수 데이터를 추출하지 않고도

    인덱스만을이용하여 별도의 정렬작업없이 동일한 결과를 추출

    인덱스에 있는 ROWID를이용하여 테이블에 있는 행이 액세스될 때 블럭 단위의

    I/O를 통해 메모리로 읽혀지게 된다. 만약 테이블에 다음 행이이미 읽혀진 BLOCK내에 있다면

    더이상 물리적으로 I/O를 일으키지 않는다.

CF. 인덱스 분류

    논리적 : 단일칼럼 / 결합컬럼

             UNIQUE / NONUNIQUE

    물리적 : PRTITIONED INDEX / NONPATITIONED INDEX

             B*TREE INDEX / BITMAP INDEX

             REVERSE KEY INDEX

CF. 인덱스 LEAF ENTRY의 구조

    ENTRY HEADER : 컬럼수, LOCKING 정보를 저장

    키컬럼 길이와 값 : 고유 혹은 비고유 인덱스인 경우에 각 컬럼의 길이 및 값이

                       차례로 정의된 쌍으로 된 값(이들 쌍이 각 인덱스에 들어갈 수 있는

                       컬럼의 최대치)

    ROWID : 키 값을 포함하는 행의 ROWID

CF. 인덱스에서 DML작업의 효과(유지 보수)

    INSERT : 적절한 인덱스BLOCK에 인덱스 ENTRY를 삽입

    DELETE : 인덱스 ENTRY의 논리적인 삭제

             삭제된 행에 의해 사용되는 공간은 BLOCK 내의 모든 ENTRY가 삭제될 때까지

             새 ENTRY를 저장하는데 사용될 수 없다.

             즉, 인덱스 BLOCK에 단 하나의 ENTRY만 존재해도이 BLOCK은 재사용 안됨.

    UPDATE : 인덱스에 논리적인 삭제와 삽입.

             PCTFREE의 설정은 인덱스 생성시에만 영향을 미치고

             후에 PCTFREE를 초과해도 새 ENTRY 추가가능.

CF. BITMAP INDEX

    LEAF NODE가 각 키 값에 대한 ROWID대신 비트맵을 저장하는 구조

    낮은 분포도(CARDINALITY)를 갖는 테이블에 적합

    키의 갱신 비용이 매우 큼(세그먼트 전체에 LOCK 설정)

    AND, OR를 사용하는 여러 개의 WHERE 절을 사용할 때 효율적

    DSS에 유리

    인덱스 공간 사용이 최소

CF. REVERSE KEY INDEX

    컬럼순서를 그대로 저장하는 것이 아니라 컬럼의 바이트를 역으로 뒤집어

    인덱스 갱신이나 삽입 분포를 분산시키는 구조

    = 질의에 대해서만 유용

    부분 범위 검색에 불리

CF. 조회

    DBA_INDEXES

    DBA_IND_COLUMNS

<<클러스터>>

1. 클러스터 생성

CREATE CLUSTER CLUSTERNAME (

  CLUSTERKEY DATATYPE, …

)

[PCTFREE][PCTUSED][INITRANS][MAXTRANS] -> BLOCK PARAMETER

[STORAGE](INITIAL NEXT MINEXTENTS MAXEXTENTS PCTINCREASE)

[TABLESPACE]

[INDEX]

[PARALLEL] PARALLEL_CLAUSE

[CACHE|NOCACHE]

[SIZE] : 하나의 데이터BLOCK에 저장될 수 있는 클러스터 키의 총 수

2. 클러스터 인덱스 생성

CREATE INDEX INDEXNAME ON CLUSTER CLUSTERNAME

[PCTFREE][INITRANS][MAXTRANS] -> BLOCK PARAMETER

[STORAGE](INITIAL NEXT MINEXTENTS MAXEXTENTS PCTINCREASE)

[TABLESPACE]

[NOSORT]

3. 클러스터테이블 생성

(storage_parameter, block_parameter, tablespace 지정 불가 -> 클러스터에서이미 지정)

CREATE TABLE TABLE_NAME#1 (

  COLUMNKEY#1 DATATYPE,

  COLUMN_NAME#1 DATATYPE,

  …

)

CLUSTER CLUSTERNAME (COLUMNKEY#1);

CREATE TABLE TABLE_NAME#2 (

  COLUMNKEY#2 DATATYPE,

  COLUMN_NAME#2 DATATYPE,

  …

)

CLUSTER CLUSTERNAME (COLUMNKEY#2);

4. 클러스터 수정

ALTER CLUSTER CLUSTERNAME

[PCTFREE][PCTUSED][INITRANS][MAXTRANS] -> BLOCK PARAMETER

[STORAGE](NEXT MINEXTENTS MAXEXTENTS PCTINCREASE)

[ALLOCATE EXTENT [SIZE NUM[K|M] | DATAFILE ‘FILENAME’ | INSTANCE #]]

[DEALLOCATE UNUSED [KEEP NUM[K|M]]]

[PARALLEL PARALLEL_CLAUSE | NOPARALLEL]

[SIZE]

5. 클러스터 삭제

DROP CLUSTER CLUSTERNAME

INCLUDING TABLES [CASCADE CONSTRAINTS]

———————————–

SQL> CREATE CLUSTER CLUSTER_EMP_DEPT(

  2  MAINID NUMBER(7)

  3  );

클러스터가 생성되었습니다.

SQL> CREATE INDEX CLUSTER_EMP_DEPT_IDX ON CLUSTER CLUSTER_EMP_DEPT

  2  TABLESPACE INDX;

인덱스가 생성되었습니다.

SQL> CREATE TABLE C_EMP (

  2  ID NUMBER(7),

  3  LAST_NAME VARCHAR2(25),

  4  FIRST_NAME VARCHAR2(25),

  5  USERID VARCHAR2(8),

  6  START_DATE DATE,

  7  COMMENTS VARCHAR2(255),

  8  MANAGER_ID NUMBER(7),

  9  TITLE VARCHAR2(25),

10  DEPT_ID NUMBER(7),

11  SALARY NUMBER(11,2),

12  COMMISSION_PCT NUMBER(4,2)

13  )

14  CLUSTER CLUSTER_EMP_DEPT (DEPT_ID);

테이블이 생성되었습니다.

SQL> CREATE TABLE C_DEPT (

  2  ID NUMBER(7),

  3  NAME VARCHAR2(25),

  4  REGION_ID NUMBER(7)

  5  )

  6  CLUSTER CLUSTER_EMP_DEPT (ID);

테이블이 생성되었습니다.

SQL> SELECT ROWID, ID, LAST_NAME, DEPT_ID FROM S_EMP;

ROWID                         ID LAST_NAME                    DEPT_ID

—— — —— — ———- ————————- ———-

AAAAw4 AAH AAAAAc AAA          1 Velasquez                         50

AAAAw4 AAH AAAAAc AAB          2 Ngao                              41

AAAAw4 AAH AAAAAc AAC          3 Nagayama                          31

AAAAw4 AAH AAAAAc AAD          4 Quick-To-See                      10

AAAAw4 AAH AAAAAc AAE          5 Ropeburn                          50

AAAAw4 AAH AAAAAc AAF          6 Urguhart                          41

AAAAw4 AAH AAAAAc AAG          7 Menchu                            42

AAAAw4 AAH AAAAAc AAH          8 Biri                              43

AAAAw4 AAH AAAAAc AAI          9 Catchpole                         44

AAAAw4 AAH AAAAAc AAJ         10 Havel                             45

AAAAw4 AAH AAAAAc AAK         11 Magee                             31

AAAAw4 AAH AAAAAc AAL         12 Giljum                            32

AAAAw4 AAH AAAAAc AAM         13 Sedeghi                           33

AAAAw4 AAH AAAAAc AAN         14 Nguyen                            34

AAAAw4 AAH AAAAAc AAO         15 Dumas                             35

AAAAw4 AAH AAAAAc AAP         16 Maduro                            41

AAAAw4 AAH AAAAAc AAQ         17 Smith                             41

AAAAw4 AAH AAAAAc AAR         18 Nozaki                            42

AAAAw4 AAH AAAAAc AAS         19 Patel                             42

AAAAw4 AAH AAAAAc AAT         20 Newman                            43

AAAAw4 AAH AAAAAc AAU         21 Markarian                         43

AAAAw4 AAH AAAAAc AAV         22 Chang                             44

AAAAw4 AAH AAAAAc AAW         23 Patel                             34

AAAAw4 AAH AAAAAc AAX         24 Dancs                             45

AAAAw4 AAH AAAAAc AAY         25 Schwartz                          45

25 개의 행이 선택되었습니다.

SQL> SELECT ROWID, ID, LAST_NAME, DEPT_ID FROM C_EMP;

ROWID                         ID LAST_NAME                    DEPT_ID

—— — —— — ———- ————————- ———-

AAAAxQ AAH AAAACU AAA          1 Velasquez                         50

AAAAxQ AAH AAAACU AAB          5 Ropeburn                          50

AAAAxQ AAH AAAACV AAA          2 Ngao                              41

AAAAxQ AAH AAAACV AAB          6 Urguhart                          41

AAAAxQ AAH AAAACV AAC         16 Maduro                            41

AAAAxQ AAH AAAACV AAD         17 Smith                             41

AAAAxQ AAH AAAACW AAA          3 Nagayama                          31

AAAAxQ AAH AAAACW AAB         11 Magee                             31

AAAAxQ AAH AAAACX AAA          4 Quick-To-See                      10

AAAAxQ AAH AAAACd AAA          7 Menchu                            42

AAAAxQ AAH AAAACd AAB         18 Nozaki                            42

AAAAxQ AAH AAAACd AAC         19 Patel                             42

AAAAxQ AAH AAAACe AAA          8 Biri                              43

AAAAxQ AAH AAAACe AAB         20 Newman                            43

AAAAxQ AAH AAAACe AAC         21 Markarian                         43

AAAAxQ AAH AAAACf AAA          9 Catchpole                         44

AAAAxQ AAH AAAACf AAB         22 Chang                             44

AAAAxQ AAH AAAACg AAA         10 Havel                             45

AAAAxQ AAH AAAACg AAB         24 Dancs                             45

AAAAxQ AAH AAAACg AAC         25 Schwartz                          45

AAAAxQ AAH AAAACh AAA         12 Giljum                            32

AAAAxQ AAH AAAACi AAA         13 Sedeghi                           33

AAAAxQ AAH AAAACj AAA         14 Nguyen                            34

AAAAxQ AAH AAAACj AAB         23 Patel                             34

AAAAxQ AAH AAAACk AAA         15 Dumas                             35

25 개의 행이 선택되었습니다.

SQL> SELECT ROWID, ID FROM S_DEPT;

ROWID                         ID

—— — —— — ———-

AAAAw1 AAH AAAAAN AAA         10

AAAAw1 AAH AAAAAN AAB         31

AAAAw1 AAH AAAAAN AAC         32

AAAAw1 AAH AAAAAN AAD         33

AAAAw1 AAH AAAAAN AAE         34

AAAAw1 AAH AAAAAN AAF         35

AAAAw1 AAH AAAAAN AAG         41

AAAAw1 AAH AAAAAN AAH         42

AAAAw1 AAH AAAAAN AAI         43

AAAAw1 AAH AAAAAN AAJ         44

AAAAw1 AAH AAAAAN AAK         45

AAAAw1 AAH AAAAAN AAL         50

12 개의 행이 선택되었습니다.

SQL> SELECT ROWID, ID FROM C_DEPT;

ROWID                         ID

—— — —— — ———-

AAAAxQ AAH AAAACU AAA         50

AAAAxQ AAH AAAACV AAA         41

AAAAxQ AAH AAAACW AAA         31

AAAAxQ AAH AAAACX AAA         10

AAAAxQ AAH AAAACd AAA         42

AAAAxQ AAH AAAACe AAA         43

AAAAxQ AAH AAAACf AAA         44

AAAAxQ AAH AAAACg AAA         45

AAAAxQ AAH AAAACh AAA         32

AAAAxQ AAH AAAACi AAA         33

AAAAxQ AAH AAAACj AAA         34

AAAAxQ AAH AAAACk AAA         35

12 개의 행이 선택되었습니다.

SQL> INSERT INTO S_EMP(ID, LAST_NAME, DEPT_ID, USERID)

  2  VALUES (26, ‘xxxxxxxxxxxxxxxxxxxxxxxxx’, 41, ‘BBB’);

1 개의 행이 만들어졌습니다.

SQL> INSERT INTO C_EMP(ID, LAST_NAME, DEPT_ID, USERID)

  2  VALUES (26, ‘xxxxxxxxxxxxxxxxxxxxxxxxx’, 41, ‘BBB’);

1 개의 행이 만들어졌습니다.

SQL> SELECT ROWID, ID, LAST_NAME, DEPT_ID FROM S_EMP;

ROWID                         ID LAST_NAME                    DEPT_ID

—— — —— — ———- ————————- ———-

AAAAw4 AAH AAAAAc AAA          1 Velasquez                         50

AAAAw4 AAH AAAAAc AAB          2 Ngao                              41

AAAAw4 AAH AAAAAc AAC          3 Nagayama                          31

AAAAw4 AAH AAAAAc AAD          4 Quick-To-See                      10

AAAAw4 AAH AAAAAc AAE          5 Ropeburn                          50

AAAAw4 AAH AAAAAc AAF          6 Urguhart                          41

AAAAw4 AAH AAAAAc AAG          7 Menchu                            42

AAAAw4 AAH AAAAAc AAH          8 Biri                              43

AAAAw4 AAH AAAAAc AAI          9 Catchpole                         44

AAAAw4 AAH AAAAAc AAJ         10 Havel                             45

AAAAw4 AAH AAAAAc AAK         11 Magee                             31

AAAAw4 AAH AAAAAc AAL         12 Giljum                            32

AAAAw4 AAH AAAAAc AAM         13 Sedeghi                           33

AAAAw4 AAH AAAAAc AAN         14 Nguyen                            34

AAAAw4 AAH AAAAAc AAO         15 Dumas                             35

AAAAw4 AAH AAAAAc AAP         16 Maduro                            41

AAAAw4 AAH AAAAAc AAQ         17 Smith                             41

AAAAw4 AAH AAAAAc AAR         18 Nozaki                            42

AAAAw4 AAH AAAAAc AAS         19 Patel                             42

AAAAw4 AAH AAAAAc AAT         20 Newman                            43

AAAAw4 AAH AAAAAc AAU         21 Markarian                         43

AAAAw4 AAH AAAAAc AAV         22 Chang                             44

AAAAw4 AAH AAAAAc AAW         23 Patel                             34

AAAAw4 AAH AAAAAc AAX         24 Dancs                             45

AAAAw4 AAH AAAAAc AAY         25 Schwartz                          45

AAAAw4 AAH AAAAAc AAZ         26 xxxxxxxxxxxxxxxxxxxxxxxxx         41

26 개의 행이 선택되었습니다.

SQL> SELECT ROWID, ID, LAST_NAME, DEPT_ID FROM C_EMP;

ROWID                         ID LAST_NAME                    DEPT_ID

—— — —— — ———- ————————- ———-

AAAAxQ AAH AAAACU AAA          1 Velasquez                         50

AAAAxQ AAH AAAACU AAB          5 Ropeburn                          50

AAAAxQ AAH AAAACV AAA          2 Ngao                              41

AAAAxQ AAH AAAACV AAB          6 Urguhart                          41

AAAAxQ AAH AAAACV AAC         16 Maduro                            41

AAAAxQ AAH AAAACV AAD         17 Smith                             41

AAAAxQ AAH AAAACV AAE         26 xxxxxxxxxxxxxxxxxxxxxxxxx         41

AAAAxQ AAH AAAACW AAA          3 Nagayama                          31

AAAAxQ AAH AAAACW AAB         11 Magee                             31

AAAAxQ AAH AAAACX AAA          4 Quick-To-See                      10

AAAAxQ AAH AAAACd AAA          7 Menchu                            42

AAAAxQ AAH AAAACd AAB         18 Nozaki                            42

AAAAxQ AAH AAAACd AAC         19 Patel                             42

AAAAxQ AAH AAAACe AAA          8 Biri                              43

AAAAxQ AAH AAAACe AAB         20 Newman                            43

AAAAxQ AAH AAAACe AAC         21 Markarian                         43

AAAAxQ AAH AAAACf AAA          9 Catchpole                         44

AAAAxQ AAH AAAACf AAB         22 Chang                             44

AAAAxQ AAH AAAACg AAA         10 Havel                             45

AAAAxQ AAH AAAACg AAB         24 Dancs                             45

AAAAxQ AAH AAAACg AAC         25 Schwartz                          45

AAAAxQ AAH AAAACh AAA         12 Giljum                            32

AAAAxQ AAH AAAACi AAA         13 Sedeghi                           33

AAAAxQ AAH AAAACj AAA         14 Nguyen                            34

AAAAxQ AAH AAAACj AAB         23 Patel                             34

AAAAxQ AAH AAAACk AAA         15 Dumas                             35

26 개의 행이 선택되었습니다.

CF. C_EMP와 C_DEPT의 SEGMENT_ID는 동일(AAAAxQ)

CF. C_EMP는 동일 클러스터 키(DEPT_ID)에 의한 동일 BLOCK 유지

CF. C_DEPT의 ID 컬럼의 ROWID는 C_EMP의 DEPT_ID의 첫번째 슬롯의 ROWID와 일치

—————————————

CF. 클러스터의 특성

    클러스터는 하나 또는 그이상의 테이블에서 연관된 일련의 행들을 저장하는 구조로

    반드시 함께 저장되어야 하는 행들을 구분하는데 사용되는 클러스터 키를가져야 한다.

    클러스터키는 하나이상의 컬럼으로 구성될 수 있으며, 클러스터된 테이블은

    클러스터키에 대응되는 컬럼을가져야 한다.

    클러스터된 테이블이라 하더라도 사용자에게는 투명한 처리방법을 제공한다.

    (클러스터 유무에 상관없이 사용자는 일반적인 테이블처럼 사용가능)

    클러스터는 검색 성능을 향상시킬 수 있지만 클러스터에 삽입,갱신,삭제,

    그리고 FULL TABLE SCAN(여러 테이블이 클러스터된 경우)시에는 더 느리다.

CF. 인덱스 클러스터

    인덱스 클러스터에 접근하기 위해서는 반드시 클러스터 인덱스가 있어야 한다.

    <클러스터 키>

    클러스터된 테이블에서 공유되고 있는 컬럼

    클러스터 키로써 좋은 것은 값의 범위가 넓은 것이 좋다.

    여러 테이블을 조인할때 조인 컬럼이 키가 되면 좋다.

    자주 업데이트되는 컬럼이나 컬럼값의 종류가 별로 없는 것(예: 이름)은 부적당하다.

    LONG이나 LONG RAW 타입의 컬럼은 클러스터 키가 될 수 없다.

    클러스터 키 컬럼을 선정하는 기준은 인덱스 선정 기준과 동일.

    한 클러스터 키 값에 너무 많은 행들이 있으면 여러 개의 BLOCK에 나뉘어 저장될 수 있다.

    그러므로, 컬럼값이 별로 없는 성별과 같은 컬럼은 클러스터 키로써 부적당하다.

    한 클러스터 키 값에 행들이 너무 없으면 테이블BLOCK의 공간을 낭비할 수 있다.

이 경우에는 공간의 낭비를 피하기 위해서 SIZE 파라미터를 작은 값으로 설정해 줄 수 있다.

    <클러스터 인덱스의 특징>

    클러스터에 대해 클러스터 키 컬럼에 정의된 인덱스를 말한다.

    클러스터 인덱스는 클러스터 키 값을 빠르게 찾기 위해 사용된다.

    클러스터 키 값을 포함하는 데이터BLOCK의 주소를 직접 갖고 있다.

    최소 2번의 I/O를 통해 원하는 행들을 액세스

    NULL ENTRY를 포함

    인덱스 클러스터로 생성되면 클러스터 인덱스 없이는 데이터에 액세스 불가.

    클러스터 인덱스는 데이터가 입력되기 전에 미리 생성시켜야 한다.

    인덱스키 당 오직 하나의 ENTRY만을 가지고 있으므로 인덱스를 위한 저장 공간도 절약됨.

    클러스터와 클러스터 인덱스는 서로 다른 테이블스페이스에 분리하여 저장.

CF. 클러스터 사용의 장단점

    디스크 I/O가 감소

    클러스터된 테이블은 조인시 성능 향상

    인덱스를 위한 필요영역을 줄여준다.

    클러스터는 검색속도를 향상시키지만, 삽입,갱신,삭제 및

    전체테이블스캔시 성능을 저하시킬 수 있다.

    DIRECT LOADING은 불가능

    같은 클러스터 키 값을 가진 행들의 수와 크기가 일정하지 않은 경우 보통 테이블보다

    더 많은 공간이 필요할 수도 있다.

*** HASH 클러스트 ***

: HASH FUNCTION를 사용하여 나온 HASH VALUE의 결과에 따라

  그 값이 같은 행들을 함께 저장하는 구조

1. 생성

CREATE CLUSTER CLUSTERNAME (

  COLUMN_NAME DATATYPE, …

)

[PCTFREE][PCTUSED][INITRANS][MAXTRANS] -> BLOCK PARAMETER

[STORAGE](INITIAL NEXT MINEXTENTS MAXEXTENTS PCTINCREASE)

[TABLESPACE]

[SIZE] : 같은 HASH 키값으로서 저장되는 모든 데이터행을 위한 스페이스 크기(기본:BLOCK 하나 크기)

[PARALLEL] PARALLEL_CLAUSE

[CACHE|NOCACHE]

[HASHKEYS # [HASH IS EXPR]]

2. 수정

SIZE, HASH IS, HASHKEYS는 수정 불가.

CF. HASH함수

    클러스터 키 값에 적용되어 HASH VALUE를 돌려주는 함수

    하나의 컬럼, 또는 COMPOSITE 키에 대해 적용가능

    HASH VALUE로 행들을 최대한 분산시킬 수 있어야 함

    충돌(COLLISION:여러 개의 클러스터 키 값이 같은 HASH VALUE로 HASHING될 때 발생)

   을 최소화하도록 해야 함

    사용자가 임의로 정의가능.

CF. HASH클러스터 테이블로 좋은 것

    테이블의 크기가 일반적으로 변하지 않는 경우

    검색 성능 최적화를 목적으로 하는 경우

    HASH 컬럼에 대해 EQUALITY 질의인 경우

    HASH KEY가 잘 분포되어 있는 경우

CF. HASH클러스터의 장단점

    인덱스 검색이 필요없기 때문에 단일 행 또는 단일 클러스터 키 값을 갖는 여러 개의

    행에 대해 매우 빠르게 액세스한다(적용가능한 경우 인덱스 클러스터보다 뛰어난 성능)

    EQUALITY 검색시에만 사용가능

    범위 검색에는 HASH 함수 사용 불가능

    HASH FUNCTION이 적절히 분산되어 있지 않으면 OVERFLOW가능성

    DIRECT LOADING 불가능

    전체 스캔시 불필요한 BLOCK까지 스캔해야 하므로 불리

    같은 클러스터 키 값을 가진 행들의 수와 크기가 일정하지 않은 경우

    보통 테이블보다 더 많은 공간이 필요할 수도 있다.

CF. HASHKEYS

    HASH VALUE의 갯수를 설정하며 최소값은 2이다

    HASHKEYS 100이면 0~100까지 101개의 HASH VALUE를 생성

    여러 클러스터 키 값이 HASH 함수에 적용했을 때 같은 HASH VALUE를가질 경우

    충돌이 발생하므로 충돌횟수를 최소화하도록 HASH VALUE 갯수를 지정하는 것이 중요

CF. HASH IS 컬럼 이름

    HASH VALUE로 사용할 컬럼을 명시하여 클러스터 키 중에서 단 하나의 컬럼 지정

    컬럼은 클러스터 키 상에서 유일한 컬럼이어야 한다.

    클서스터 키는 데이터 타입이 NUMBER인 단일 컬럼으로 음이 아닌 정수만 포함

    사용자 정의 HASH FUNCTION이 가능

    (예: HASH IS MOD(HOME_AREA_CODE+HOME_PREFIX+HOME_SUFFIX),101)

CF. 인덱스 클러스터에서 SIZE 파라미터

    평균 클러스터 키의 길이 및 해당 행들을 저장하기 위해 필요한 바이트수

    하나의 데이터BLOCK에 저장가능한 클러스터 키의 갯수를 결정

    SIZE를 너무 작게 하면 같은 키 값을 갖는 행들이 다른 클러스터 BLOCK에 저장

    SIZE를 너무 크게 하면 공간이 낭비

CF. HASH 클러스터에서 SIZE 파라미터

    하나의 HASH KEY에 대한 모든 행들을 저장하기 위한 AVERAGE SPACE

    데이터 BLOCK에 할당된 HASHKEY의 갯수를 제어한다

    HASH 클러스터가 단일 테이블로 구성되어 있고 키 값당 하나의 행이 존재할때

    SIZE 파라미터는 테이블의 AVERAGE ROW LENGTH로 설정될 수 있다.

    HASH 클러스터가 여러 개의 테이블로 구성될때 SIZE는 HASH VALUE에 대한

    모든 행들을 저장하기 위한 AVERAGE SPACE로 설정되어야 한다.

    SIZE 값을 크게 설정하면 클러스터로이용되지 않는 공간이 증가된다.

CF. 조회

    데이터베이스 내의 클러스터 정보

    DBA_CLUSTERS

    클러스터 내의 클러스터 키 정보

    DBA_CLU_COLUMNS

    테이블 내의 클러스터 키 정보

    DBA_TAB_COLUMNS

    해쉬 클러스터의 정보

    DBA_CLUSTER_HASH_EXPRESSIONS

<<IOT(INDEX ORGANIZED TABLE)>>

1. 생성

   CREATE TABLE TABLE_NAME (

   COLUMN_NAME DATATYPE [DEFAULT EXPR][COLUMN_CONSTRAINT]…)

   ORGANIZATION INDEX -> IOT 생성구문

   [PCTTHRESHOLD # [INCLUDING COLUMN_NAME]]

   [OVERFLOW [PHYSICAL_ATTRIBUTES_CLAUSE|BLOCK PARAMETER|STORAGE|TABLESPACE]]

2. 조회

   SELECT T.TABLE_NAME AS “IOT”, O.TABLE_NAME AS “OVERFLOW”,

          I.INDEX_NAME AS “INDEX”, O.TABLESPACE_NAME AS “OVERFLOW TBS”,

          I.TABLESPACE_NAME AS “INDEX TBS”, I.PCT_THRESHOLD

   FROM DBA_TABLES T, DBA_TABLES O, DBA_INDEXES I

   WHERE T.OWNER = O.OWNER

   AND   T.TABLE_NAME = O.IOT_NAME

   AND   T.OWNER = I.OWNER

   AND   T.TABLE_NAME = I.TABLE_NAME

   AND   T.OWNER LIKE ‘%&OWNER%’;

CF. 일반 테이블과 IOT의 비교

    일반 테이블은 ROWID로 행을 구별하고 IOT는 PK로 행을 구별

    IOT도 ROWID가 있으므로 보조인덱스 생성가능(8i 버전부터)

    일반 테이블의 FULL SCAN은 행이 출력되는 순서를 예측할 수 없지만

    IOT는 PK값의 순서에 따라 출력

    IOT에도 UNIQUE 제약조건을 설정할 수 있다(8i 버전부터)

    IOT에도 분산(DISTRIBUTION),복사(REPLICATION),분할(PARTITIONING)지원이 된다.(8i 버전부터)

CF. PCTTHRESHOLD는 0~50(디폴트:50)

    PCTTHRESHOLD를 지정하고 OVERFLOW를 지정하지 않으면

    PCTTHRESHOLD를 초과한 데이터는 입력되지 않는다.(생성시 에러 발생하지 않음)

CF. IOT 생성시 만들어지는 세그먼트

    1. 테이블

        CREATE TABLE 절에 정의된 이름을 가지는 논리적 테이블로

        모든 행이 인덱스에 저장되므로 이에 대응하는 세그먼트는 존재하지 않는다

    2. 인덱스

        PK 지정시 생성되는 인덱스로 CREATE TABLE 문에 지정된 테이블스페이스에 생성되며

        이때 지정된 스토리지와 공간 활용 파라미터를 가진다

        (PRIMARY KEY 이름 생략시 SYS_IOT_TOP_n의 이름을 가짐)

    3. OVERFLOW 행 조각을 수용할 테이블

        PCTTHRESHOLD를 초과할 경우 나머지 컬럼들이 저장되는 테이블로

        이름은 SYS_IOT_OVER_n으로 지정되며 n은 DBA_OBJECTS 에서 볼 수 있는

        IOT의 OBJECT_ID이다.

CF. IOT 테이블스페이스

        세그먼트: PK 이름

        유형: 인덱스

        PCTTHRESHOLD 내의 행 저장

    오버플로우 테이블스페이스

        세그먼트: SYS_IOT_OVER_n

        유형: 테이블

        PCTTHRESHOLD 보다 큰 행 저장

CF. IOT에서 발생하는 ERROR

    ORA-25175 : NO PRIMARY KEY CONSTRAINT FOUND

                IOT에 PK를 지정하지 않고 테이블 생성시

    ORA-01429 : INDEX – ORGANIZED TABLE : NO DATA SEGMENT TO STORE OVERFLOW ROW-PIECESCLAUSE

                PCTTHRESHOLD가 정의되고 오버플로우 세그먼트가 지정되지 않은 경우에

                임계값을 초과한 행이 입력되는 경우

(IOT 예제1)

SQL> CREATE TABLE REGULAR_TAB (

  2  ID NUMBER(5) CONSTRAINT REG_TAB_ID_PK PRIMARY KEY,

  3  NAME VARCHAR2(20));

SQL> INSERT INTO REGULAR_TAB VALUES (30, ‘AAA’);

SQL> INSERT INTO REGULAR_TAB VALUES (20, ‘BBB’);

SQL> INSERT INTO REGULAR_TAB VALUES (40, ‘CCC’);

SQL> INSERT INTO REGULAR_TAB VALUES (50, ‘DDD’);

SQL> INSERT INTO REGULAR_TAB VALUES (10, ‘EEE’);

SQL> SELECT ROWID, ID, NAME FROM REGULAR_TAB;

ROWID                      ID NAME

—————— ———- ——————————

AAAFqsAAHAAAAAIAAA         30 AAA

AAAFqsAAHAAAAAIAAB         20 BBB

AAAFqsAAHAAAAAIAAC         40 CCC

AAAFqsAAHAAAAAIAAD         50 DDD

AAAFqsAAHAAAAAIAAE         10 EEE

SQL> CREATE TABLE IOT_TAB (

  2  ID NUMBER(5) CONSTRAINT IOT_TAB_ID_PK PRIMARY KEY,

  3  NAME VARCHAR2(20))

  4  ORGANIZATION INDEX

  5  PCTTHRESHOLD 20 INCLUDING ID OVERFLOW TABLESPACE ORABIBLE;

SQL> INSERT INTO IOT_TAB VALUES (30, ‘AAA’);

SQL> INSERT INTO IOT_TAB VALUES (20, ‘BBB’);

SQL> INSERT INTO IOT_TAB VALUES (40, ‘CCC’);

SQL> INSERT INTO IOT_TAB VALUES (50, ‘DDD’);

SQL> INSERT INTO IOT_TAB VALUES (10, ‘EEE’);

SQL> SELECT ROWID, ID, NAME FROM IOT_TAB;(ID로 정렬되어 있음)

ROWID                ID NAME

—————— —- —–

*BAHAABcCwQv+        10 EEE

*BAHAABcCwRX+        20 BBB

*BAHAABcCwR/+        30 AAA

*BAHAABcCwSn+        40 CCC

*BAHAABcCwTP+        50 DDD

SQL> SELECT OBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE

  2  FROM USER_OBJECTS;

OBJECT_NAME                     OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE

—————————— ———- ————– ———–

IOT_TAB                             23216                TABLE

IOT_TAB_ID_PK                       23218          23218 INDEX

REGULAR_TAB                         23212          23212 TABLE

REG_TAB_ID_PK                       23213          23213 INDEX

SYS_IOT_OVER_23216                  23217          23217 TABLE

(IOT 예제2)

SQL> CREATE TABLE IOTTAB (

  2  ID NUMBER CONSTRAINT IOTTAB_INDEX PRIMARY KEY,

  3  NAME VARCHAR2(30))

  4  ORGANIZATION INDEX

  5  PCTTHRESHOLD 50

  6  OVERFLOW TABLESPACE ORABIBLE

  7  STORAGE (INITIAL 2K NEXT 6K PCTINCREASE 30);

테이블이 생성되었습니다.

SQL> ALTER TABLE IOTTAB

  2  STORAGE (NEXT 30K PCTINCREASE 50);

테이블이 변경되었습니다.

SQL> BEGIN

  2    FOR i IN 1..10000 LOOP

  3      INSERT INTO IOTTAB VALUES (i, ‘HELLO WORLD’);

  4    END LOOP;

  5  END;

  6  /

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> SELECT SEGMENT_NAME, EXTENT_ID, FILE_ID, BLOCK_ID, BLOCKS

  2  FROM DBA_EXTENTS

  3  WHERE TABLESPACE_NAME = ‘ORABIBLE’

  4  ORDER BY 4;

SEGMENT_NAME        EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS

—————— ———- ———- ———- ———-

SYS_IOT_OVER_23246          0          7          2          2

IOTTAB_INDEX                0          7          4          5

IOTTAB_INDEX                1          7          9         15

IOTTAB_INDEX                2          7         24         25

IOTTAB_INDEX                3          7         49         35

IOTTAB_INDEX                4          7         84         55

<<뷰>>

1. 생성

CREATE [OR REPLACE][FORCE|NOFORCE] VIEW VIEWNAME

AS SUB_QUERY

[WITH CHECK OPTION [CONSTRAINT CONSTRAINTNAME]]

[WITH READ ONLY]

2. 수정

ALTER VIEW VIEWNAME COMPILE

3. 삭제

DROP VIEW VIEWNAME

<<시퀀스>>

1. 생성

CREATE SEQUENCE SEQUENCENAME

[START WITH:1]

[INCREMENT BY:1]

[MINVALUE:1|NOMINVALUE]

[MAXVALUE:1028|NOMAXVALUE]

[CYCLE|NOCYCLE]

[CACHE:20|NOCACHE]

2. 수정

CREATE SEQUENCE SEQUENCENAME

[INCREMENT BY:1]

[MINVALUE:1|NOMINVALUE]

[MAXVALUE:1028|NOMAXVALUE]

[CYCLE|NOCYCLE]

[CACHE:20|NOCACHE]

3. 삭제

DROP SEQUENCE SEQUENCENAME;

<<시노님>>

1. 생성

CREATE [PUBLIC] SYNONYM SYNONYMNAME

FOR [SCHEMA.]OBJECT [@DBLINK]

2. 삭제

DROP [PUBLIC] SYNONYM SYNONYMNAME

<<데이터베이스 링크>>

1. 생성

CREATE [PUBLIC] DATABASE LINK LINKNAME

CONNECT TO USERNAME IDENTIFIED BY PASSWORD

USING SERVICENAME

2. 삭제

DROP DATABASE LINK LINKNAME

************************************************

<<아카이브 백업>>

1. 데이터베이스의 아카이드 모드 확인

SQL> V$DATABASE, V$ARCHIVE, V$LOG

SVRMGRL> ARCHIVE LOG LIST;

2. 아카이브모드 설정 및 해제

SHUTDOWN

-> (BACKUP)

-> STARTUP MOUNT;

-> ALTER DATABASE [ARCHIVELOG|NOARCHIVELOG];

-> ALTER DATABASE OPEN;

3. 아카이빙 수행

자동수행(LOG_ARCHIVE_START, LOG_ARCHIVE_DEST, LOG_ARCHIVE_FORMAT)

수동수행(ALTER SYSTEM ARCHIVE LOG ALL);

<<OS 명령어를 사용한 데이터베이스 백업>>

DATAFILE

SQL> SELECT FILE_NAME

     FROM DBA_DATA_FILES;

REDO LOG FILE

SQL> SELECT MEMBER

     FROM V$LOGFILE;

CONTROL FILE

SQL> SELECT VALUE

     FROM V$PARAMETER

     WHERE UPPER(NAME) = ‘CONTROL_FILES’;

PARAMETER FILE

SQL> SELECT VALUE

     FROM V$PARAMETER

     WHERE UPPER(VALUE) LIKE ‘%INIT%.ORA%’;

*********************************************

<<데이터 INTEGRITY>>

CF. 데이터 INTEGRITY

    ENTIRY INTEGRITY : PRIMARY KEY CONSTRAINT

    CUSTOM INTEGRITY : RULES, STORED PROCEDURES, STORED FUNCTIONS,

                       PACKAGES, DATABASE TRIGGERS

    DOMAIN INTEGRITY : DATA TYPE, CHECK CONSTRAINT

    REFERENTIAL INTEGRITY : FOREIGN KEY CONSTRAINT

CF. CONSTRAINT 종류

    NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK

CF. CONSTRAINT 정의 방법

    COLUMN LEVEL CONSTRAINT(IN-LINE CONSTRAINT)

      COLUMN_NAME [CONSTRAINT CONSTRAINT_NAME] CONSTRAINT_TYPE, …

    TABLE LEVEL CONSTRAINT(OUT-OF-LINE CONSTRAINT)

      [CONSTRAINT CONSTRAINT_NAME] CONSTRAINT_TYPE (COLUMN_NAME….), …

CF. CONSTRAINT 상태

    DISABLE

      ALTER TABLE ~ DISABLE CONSTRAINT ~

      ALTER TABLE ~ MODIFY CONSTRAINT ~ DISABLE

    ENABLE+NOVALIDATE

      ALTER TABLE ~ ENFORCE CONSTRAINT ~

      ALTER TABLE ~ ENABLE NOVALIDATE CONSTRAINT ~

      ALTER TABLE ~ MODIFY CONSTRAINT ~ ENFORCE

    ENABLE+VALIDATE

      ALTER TABLE ~ ENABLE CONSTRAINT ~

      ALTER TABLE ~ ENABLE VALIDATE CONSTRAINT ~

      ALTER TABLE ~ MODIFY CONSTRAINT ~ ENABLE

CF. DISABLE에서 ENABLE로 전환시 에러발생 항목 검출

    @ $ORACLE_HOME/RDBMS/ADMIN/UTLEXCPT.SQL(EXCEPTIONS 테이블 생성)

    에러가 발생이 안되게 하는 것이 아니라 에러의 원인을 찾아줌.

    SQL> DESC EXCEPTIONS

  이름       널?      유형

    ———- ——– ————-

    ROW_ID              ROWID

    OWNER               VARCHAR2(30)

    TABLE_NAME          VARCHAR2(30)

    CONSTRAINT          VARCHAR2(30)

    ALTER TABLE TABLE_NAME

    ENABLE CONSTRAINT CONSTRAINT_NAME EXCEPTIONS INTO EXCEPTION_TABLE;

CF. 제약조건 생성시 주는 옵션(상태(STATUS)와 DEFERRABLE, DEFERRED는 무관..)

    지연 전환가능한지 혹은 불가능한지 상태 결정(수정방법은???)

      NOT DEFERRABLE(DEFAULT) / DEFERRABLE

    현재 제약조건을 즉시 검사할 것인지 지연 검사할 것인지 결정

      INITIALLY IMMEDIATE(DEFAULT) / INITIALLY DEFERRED

CF. 초기에 DEFERRABLE 상태에서이를 지연된 혹은 즉각 검사로 전환

    (트랜잭션 또는 세션 단위, 즉 세션을 다시 시작한 후엔 안됨)

    (설정을 바꾸려면 ALTER TABLE 구문으로 바꾸어야하는 거 같음)

    ALTER SESSION SET [CONSTRAINT|CONSTRAINTS] = [IMMEDIATE|DEFERRED|DEFAULT]

    SET CONSTRAINT   CONSTRAINT_NAME  IMMIDIATE

        CONSTRAINTS  ALL              DEFERRED

CF. ALTER TABLE TABLE_NAME

    MODIFY CONSTRAINT CONSTRAINT_NAME CONSTRAINT_STATE

    예) ALTER TABLE CHILD_TAB

        MODIFY CONSTRAINT CHILD_TAB_TEL_CK INITIALLY DEFERRED;

CF. <CONSTRAINT_STATE>

    DEFERRABLE            INITIALLY IMMEDIATE

    NOT DEFERRABLE        INITIALLY DEFERRED

    –> 순서는 바뀌어도 되나, NOT DEFERRABLE INITIALLY DEFERRED는 안됨..

    ENABLE   VALIDATE

    DISABLE  NOVALIDATE

    EXCEPTIONS INTO (SCHEMA.)TABLE_NAME

EX)

SQL> CREATE TABLE HELLOWORLD (

  2  ID1 NUMBER CONSTRAINT CONSTNAME1 UNIQUE,

  3  ID2 NUMBER CONSTRAINT CONSTNAME2 UNIQUE NOT DEFERRABLE,

  4  ID3 NUMBER CONSTRAINT CONSTNAME3 UNIQUE DEFERRABLE,

  5  ID4 NUMBER CONSTRAINT CONSTNAME4 UNIQUE DEFERRABLE INITIALLY IMMEDIATE,

  6  ID5 NUMBER CONSTRAINT CONSTNAME5 UNIQUE DEFERRABLE INITIALLY DEFERRED,

  7  ID6 NUMBER CONSTRAINT CONSTNAME6 UNIQUE NOT DEFERRABLE INITIALLY IMMEDIATE,

  8  ID7 NUMBER CONSTRAINT CONSTNAME7 UNIQUE NOT DEFERRABLE INITIALLY DEFERRED

  9  );

ID7 NUMBER CONSTRAINT CONSTNAME7 UNIQUE NOT DEFERRABLE INITIALLY DEFERRED

           *

8행에 오류:

ORA-02447: 지연이 가능하지 않은 제약조건을 지연할 수 없습니다

(즉, NOT DEFERRABLE -> DEFERRED는 안됨)

SQL> CREATE TABLE HELLOWORLD (

  2  ID1 NUMBER CONSTRAINT CONSTNAME1 UNIQUE,

  3  ID2 NUMBER CONSTRAINT CONSTNAME2 UNIQUE NOT DEFERRABLE,

  4  ID3 NUMBER CONSTRAINT CONSTNAME3 UNIQUE DEFERRABLE,

  5  ID4 NUMBER CONSTRAINT CONSTNAME4 UNIQUE DEFERRABLE INITIALLY IMMEDIATE,

  6  ID5 NUMBER CONSTRAINT CONSTNAME5 UNIQUE DEFERRABLE INITIALLY DEFERRED,

  7  ID6 NUMBER CONSTRAINT CONSTNAME6 UNIQUE NOT DEFERRABLE INITIALLY IMMEDIATE);

테이블이 생성되었습니다.

SQL> SET CONSTRAINT CONSTNAME1 DEFERRED;

set CONSTRAINT CONSTNAME1 DEFERRED

*

1행에 오류:

ORA-02447: 지연이 가능하지 않은 제약조건을 지연할 수 없습니다

SQL> SET CONSTRAINT CONSTNAME4 DEFERRED;

제약조건이 설정되었습니다.

참고)

제약조건이름?     사용가능? 기존내용검증? 지연가능한가?  지연할건가?

CONSTRAINT_NAME   STATUS    VALIDATED     DEFERRABLE     DEFERRED

—————– ——— ————- ————– ———

DISABLE_NOT_DEFER DISABLED  NOT VALIDATED NOT DEFERRABLE IMMEDIATE

ENFORCE_DEFER_IMD ENABLE    NOT VALIDATED DEFERRABLE     IMMEDIATE

ENABLE_DEFER_DEF  ENABLE    VALIDATED     DEFERRABLE     DEFERRED

<<트리거>>

CREATE [OR REPLACE] TRIGGER TRIGGER_NAME

[BEFORE|AFTER] -> BY TIMING

[INSERT|UPDATE|DELETE] -> BY EVENT

OF COLUMN_NAME ON TABLE_NAME

[FOR EACH ROW]

PL/SQL BLOCK;

EX) CREATE OR REPLACE TRIGGER SCOTT.EMP_INITCAP

    BEFORE INSERT OR UPDATE

    OF ENAME ON SCOTT.EMP

    BEGIN

      :NEW.ENAME := INITCAP(:NEW.ENAME);

    END;

    /

CF. TRIGGER 하나를 DISABLE 혹은 ENABLE 하려면

    ALTER TRIGGER [SCHEMA.]TRIGGER_NAME

    [DISABLE|ENABLE];

CF. 테이블에 설정된 TRIGGER 전부를 DISABLE 하거나 ENABLE 하려면

    ALTER TABLE [SCHEMA.]TABLE_NAME

    [DISABLE|ENABLE] ALL TRIGGERS;

CF. TRIGGER 조회

    DBA_TRIGGERS/DBA_TRIGGER_COLS/DBA_OBJECTS

*********************************************

<<익스포트/임포트>>

C:\\download>EXP HELP=Y

Export: Release 8.1.7.0.0 – Production on 화 Apr 23 01:27:55 2002

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

EXP 명령어 뒤에 사용자명/암호를 입력하면 엑스포트할때 매개변수의

값을 입력하도록 할 수 있습니다:

     예: EXP SCOTT/TIGER

또는, EXP 명령어 뒤에 다양한 인수를 입력하여 엑스포트의 실행 방법을

제어할 수 있습니다. 매개변수를 지정하려면 키워드를 사용하십시오:

     형식: EXP KEYWORD=값 또는 KEYWORD=(값1,값2,…,값N)

     예  : EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)

               또는 TABLES=(T1:P1,T1:P2), 만일 T1이 분할된 테이블이라면

               USERID 명령어 줄에 첫번째 매개변수에 있어야 합니다.

키워드   설명(기본값)                 키워드       설명(기본값)

————————————————————————–

USERID   사용자명/암호                FULL         파일 전체를 엑스포트 (N)

BUFFER   데이터 버퍼의 크기           OWNER        소유 사용자명 목록

FILE     출력 파일 (EXPDAT.DMP)       TABLES       테이블명 목록

COMPRESS 하나의 익스텐트에 임포트 (Y) RECORDLENGTH I/O 레코드의 길이

GRANTS   권한부여 엑스포트 (Y)        INCTYPE      증분 엑스포트 타입

INDEXES  인덱스 엑스포트 (Y)          RECORD       트랙 증분. 엑스포트 (Y)

ROWS     데이터 행 엑스포트 (Y)       PARFILE      매개변수 파일명

CONSTRAINTS 제약조건 엑스포트(Y)      CONSISTENT   교차-테이블 일관성

LOG      화면 출력의 로그 파일        STATISTICS   객체 분석 (ESTIMATE)

DIRECT   직접 경로 (N)                TRIGGERS     트리거 엑스포트 (Y)

FEEDBACK x 행마다 진행을 보여줍니다 (0)

FILESIZE 각 덤프 파일의 최대 크기

QUERY    테이블의 서브집합을 엑스포트하기 위해 사용될 절을 선택합니다

이동가능한 테이블스페이스에이와 같은 키워드만이 적용됩니다

TRANSPORT_TABLESPACE이동가능한 테이블스페이스 메타데이터을 엑스포트합니다 (N)

TABLESPACES이동하기 위한 테이블스페이스의 목록입니다

엑스포트가 경고 없이 정상적으로 종료되었습니다.

*********************************************

IMP HELP=Y

Import: Release 8.1.7.0.0 – Production on 화 Apr 23 01:25:30 2002

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

IMP 명령어 뒤에 사용자명/암호를 입력하면 임포트 시에 매개변수의

값을 입력할 수 있도록 프롬프트를 나타나게 할 수 있습니다:

     예 제: IMP SCOTT/TIGER

또는, IMP 명령어 뒤에 여러 가지 인수를 입력하면 임포트의 실행 방법을

제어할 수 있습니다. 매개변수를 지정하려면 키워드를 사용하십시오:

     형식:  IMP KEYWORD=값 또는 KEYWORD=(값1,값2,…,값N)

     예  :  IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N

               또는 TABLES=(T1:P1,T1:P2), T1이 분할된 테이블일 경우에

               USERID 명령어 줄에 첫번째 매개변수에 있어야 합니다.

키워드      설명(기본값)             키워드          설명(기본값)

————————————————————————–

USERID   사용자명/암호               FULL         파일 전체 임포트 (N)

BUFFER   데이터 버퍼의 크기          FROMUSER     소유 사용자명 목록

FILE     입력 파일들 (EXPDAT.DMP)    TOUSER       사용자명의 목록

SHOW     파일 목차만 목록 (N)        TABLES       테이블명 목록

IGNORE   생성 오류 무시 (N)          RECORDLENGTH IO 레코드의 길이

GRANTS   권한부여 임포트 (Y)         INCTYPE      증분 임포트 유형

INDEXES  인덱스 임포트 (Y)           COMMIT       배열 삽입 커밋 (N)

ROWS     데이터 행 임포트 (Y)        PARFILE      매개변수 파일명

LOG      화면 출력의 로그 파일       CONSTRAINTS  제약조건을 임포트 (Y)

DESTROY  테이블스페이스 데이터 파일에 덮어 쓰기 (N)

INDEXFILE 테이블/인덱스 정보를 지정된 파일로 기록

SKIP_UNUSABLE_INDEXES  사용되지 않은 인덱스들의 유지를 건너뜁니다 (N)

ANALYZE  덤프 파일에 ANALYZE 문을 실행합니다 (Y)

FEEDBACK x 행마다 진행을 보여줍니다(0)

TOID_NOVALIDATE  지정된 유형 ID의 검증을 건너 뜁니다

FILESIZE 각 덤프 파일의 최대 크기

RECALCULATE_STATISTICS 통계를 다시 계산합니다 (N)

이와 같이 키워드만이이동가능한 테이블스페이스에 적용할 수 있습니다

TRANSPORT_TABLESPACE이동가능한 테이블스페이스 메타데이터을 임포트합니다 (N)

TABLESPACES 데이터베이스안에 테이블스페이스가이동되었습니다

DATAFILES 데이터베이스안에 데이터 파일들이이동되었습니다

TTS_OWNERS이동가능한 테이블스페이스 설정에 데이터를 소우하고 있는 사용자

임포트가 경고 없이 정상적으로 종료되었습니다.

*********************************************

<EXP/IMP의 사용목적>

데이터베이스 간의이동

서로 다른 플랫폼, 오라클의 다른 버전으로이동

한 사용자가 소유한 데이터를 다른 사용자에게이전

논리적 백업

테이블 재구성(테이블스페이스 내의 단편화 제거,

              할당받은 익스텐트의 하이워터마크 아래에 빈 블럭들에 대한 재구성,

              한 블록 내의 PCTUSED의 활용도에 따른 비어있는 공간에 대한 재구성)

<EXPORT의 두 가지 경로>

1. CONVENTIONAL PATH

    EXPORT의 일반적 방법으로 SQL SELECT 문을 직접 사용하여 테이블로부터

    데이터를가져다가 버퍼캐쉬를 거쳐 데이터를 읽은 후

    EVALUATION BUFFER 라는 버퍼를 거쳐 EXPORT 프로세스로 전송되고

    EXPORT 프로세스가 데이터를 EXPORT 파일에 옮기게 된다.

2. DIRECT PATH

    CONVENTIONAL PATH 보다 빠르게 데이터를 EXPORT

    BUFFER CACHE를 거친 데이터는 평가버퍼(EVALUATION BUFFER)를 거치지 않은채

    직접 EXPORT 프로세스로 전송되고 EXPORT파일을 생성

    평가버퍼(EVALUATION BUFFER)를 거치지 않으므로 행 조각들을 합치는 재구성은 생략됨.

<EXPORT의 모드>

테이블 모드 : 테이블 정의, 테이블 데이터, 소유자의 권한, 소유자의 인덱스

              테이블 제약조건, 테이블 트리거

사용자 모드 : 테이블 모드 +

              클러스터, 데이터베이스 링크, 뷰, 개인동의어, 시퀀스,

              스냅샷, 스냅샷 로그, 저장 프로시져

데이터베이스 모드 : 사용자 모드 +

                    롤, 모든 동의어, 시스템권한, 테이블스페이스 정의,

                    테이블스페이스 쿼터, 롤백세그먼트 정의, 시스템감사옵션,

                    모든 트리거, 프로파일

<IMPORT 수행 순서>

테이블 정의 -> 테이블 데이터 -> 데이블 인덱스 -> 무결성제약조건 트리거 & 비트맵인덱스

**********************************************

<SQL*LOADER의 특징>

여러 개의 물리적인 레코드는 로드시 하나의 논리 레코드로 조합될 수 있다.

물리적인 레코드는 로드시 여러 논리 레코드로 분할할 수 있다.

컬럼상에서 유일한 일련번호(SEQUENCE), 현재날짜(SYSDATE),

상수값(CONSTANT) 및 레코드번호(RECNUM)를 생성할 수 있다.

에러보고 능력이 있다.

필요한 파일은 데이터파일과 컨트롤파일이고,

생성되는 파일은 LOG 파일, BAD 파일, DISCARD 파일이 있다.

<CONVENTIONAL PATH>

1) CONVENTIONAL PATH에 의한 로드

    데이터베이스 자원에 대해 모든 다른 프로세스들과 경쟁

    DIRECT PATH보다 느림

    데이터베이스에 입력된 행들의 배열을 작성

    테이블에 행들을 입력하기 위하여 INSERT SQL 문장을 사용

2) CONVENTIONAL PATH를 사용하는 경우

    액세스가능한 상태로 남아야 할 인덱스된 테이블에 데이터를 로드할 때

    입력 또는 수정을 위해이용가능한 상태로 남아야 할 비인덱스된 테이블에 데이터 로드시

    SQL*NET에 의해 데이터를 로드할 때

    클러스터된 테이블에 데이터를 로드할 때

    인덱스된 대형 테이블에 상대적으로 적은 수의 행들을 로드시

    참조무결성 제약조건과 CHECK무결성 제약조건을 사용하여 대형 테이블에 상대적으로

    적은 수의 행들을 로드시

    데이터 필드에 SQL함수를 적용할 때

3) CONVENTIONAL PATH를 사용한 데이터 로드

    ROWS 파라미터는 데이터를 커밋하기 전에 얼마나 많은 행들을 로드시킬지 명시

    데이터베이스에 대한 모든 변경은 리두로그파일에 기록

    데이터를 로드하는 동안 테이블에 속하는 모든 인덱스를 유지관리

    (시간 많이 소요 -> 인덱스 제거한 후 데이터 로드, 인덱스를 다시 생성)

    여러 행들이 한번에 읽혀져서 배열에 저장된 다음 INSERT명령이 한번에

    배열의 내용을 입력하고 커밋한다.

    배열은 적어도 한개의 행들을 저장하기에 충분한 크기여야 함.

<DIRECT PATH>

1) DIRECT PATH에 의한 로드

    고도로 최적화

    비어있는 블록들이 사용되므로 CONVENTIONAL PATH 보다 빠름

    클러스터화 되지 않은 테이블에 데이터를 로드

    데이터베이스에 직접적으로 입력된 오라클 블록을 작성

    테이블에 행들을 입력하기 위해 SQL문장을 사용하지 않음

    다음 익스텐트를 할당받기 위해 SPACE관리루틴을 사용하고 HWM를 조정한다.

    테이블의 행들에 대한 몇 가지 무결성제약조건을 시행

    SQLLDR 명령 라인에서 DIRECT=TRUE 파라미터를 사용하여 명시한다.

2) DIRECT PATH를이용한 데이터 로드

    SQL문장들을 생성하지 않음

    배열을 사용하지 않음

    오직 한번만 테이블과 인덱스를 LOCKING 한다.

   가능한 비동기적인 I/O를 한다.

    SGA의 버퍼캐쉬를 BYPASS한다.

    NOACHIVELOG모드로 사용시 리두 로그 엔트리를 작성하지 않음

    인덱스는 데이터가 로드된 후 생성

    NOT NULL, UNIQUE, PRIMARY KEY 제약조건은 ENABLE상태로 남고

    CHECK, FOREIGN KEY 제약조건은 DISABLE상태로 됨

    제약조건을 자동으로 재활성화하기 위해서는 INTO TABLE 구의 REEANBLE 절을 사용

    INSERT TRIGGER 도 역시 비활성화됨

    비활성화 상태의 트리거는 행들이 로드되고 인덱스가 생성된 후에 활성화됨

<SQLLDR의 컨트롤파일> : 데이터파일의 형식 기술

: SQLLDR CONTROL=CONTROL_FILE DATA=DATEFILE_NAME

  LOAD DATA

  INFILE DATAFIEL_NAME

  [REPLACE|INSERT|APPEND]

  INTO TABLE TABLE_NAME

  FIELDS TERMINATED BY ‘TERMCHAR’

  [TRAILING NULLCOLS]

  (COLUMN_NAME COLUMN_TYPE)

  LOAD DATA

  INFILE DATAFIEL_NAME

  INTO TABLE TABLE_NAME

  (COLUMN_NAME POSITION(XX:YY) COLUMN_TYPE)

  COLUMN_TYPE: CHAR, INTEGER EXTERNAL, DECIMAL EXTERNAL, DATE “YY/MM/DD”,

               SEQUENCE(START,INCREASE), CONSTANT ‘XXX’, SYSDATE,

**********************************************

<<PARTITIONING>>

<오라클8의 파티션>

    테이블과 인덱스는 파티션이라 불리는 작은 조각으로 구성

    PARTITION KEY 라 불리는 값의 범위에 따라 데이터의 파티션이이루어짐

    VLDB(VERY LARGE DATABASE)에서 유용

    오라클8은 한개의 테이블이나 인덱스에 대해 64000개의 파티션 지원

<파티션 사용이점>

    HIGH AVAILABILITY

    EASE OF ADMINISTRATION

    IMPROVED PERFORMANCE

CF. PARTITION BY RANGE : PARTITION KEY를 정의

    VALUES LESS THAN : 데이터가 저장되는 파티션의 이름과 경계값 지정

    PHYSICAL ATTRIBUTE : 테이블스페이스 및 스토리지 등의 물리적 속성을 지정

EX) CREATE TABLE EMP (

    EMP_NO NUMBER(10) NOT NULL,

    EMP_NAME VARCHAR2(15),

    DEPT_NO NUMBER(10) NOT NULL,

    WEEK_NO NUMBER(2) NOT NULL

    )

    PARTITION BY RANGE (WEEK_NO) (

    PARTITION P1 VALUES LESS THAN (50) TABLESPACE DATA1,

    PARTITION P2 VALUES LESS THAN (99) TABLESPACE DATA2

    )

CF. 파티션의 논리적 속성(LOGICAL ATTRIBUTE)

    동일한 컬럼, 데이터타입, 제약조건 등

    파티션 테이블과 인덱스를 생성할 때에는 LOGICAL 속성을 지정해야 한다.

    테이블에 정의되어있는 한 개이상의 컬럼은 파티션을 구성하는데 사용되어야 한다.

CF. 파티션의 제약사항

    VALUES LESS THAN 안에 정의되어질 수 있는 SQL함수는 TO_DATE 뿐이다.

    파티션 키는 16개 컬럼까지 사용가능

    파티션 키에 의한 범위에 GAP을 둘 수 없다.

    파티션 테이블은 LONG, LONG RAW, LARGE OBJECT(LOB) 데이터타입을가질 수 없다.

    (8i버전에서는 LOB 데이터타입 지원됨)

    UPDATE가 자주 일어나는 컬럼은 파티션 키로 부적합하다.(파티션을 넘나드는 업데이트 불가)

CF. 파티션 인덱스

    PARTITIONED – PREFIXED    – GROBAL

                              – LOCAL

                – NONPREFIXED – LOCAL

    NONPATITIONED

    즉,

    NONPATITIONED INDEX

    GLOBAL PREFIXED INDEX

    LOCAL PREFIXED INDEX

    LOCAL NONPREFIXED INDEX

CF. GROBAL & LOCAL

    테이블과 인덱스의 분리 기준 동일 여부(즉, VALUES LESS THAN의 일치 여부)

    PREFIXED & NONPREFIXED

    파티션키와 인덱스 키의 일치 여부(즉, PARTITION BY RANGE의 일치 여부)

EX) CREATE TABLE EMP (

    EMP_NO NUMBER(3) NOT NULL,

    EMP_NAME VARCHAR2(12),

    DEPT_NO VARCHAR2(3) NOT NULL)

    PARTITION BY RANGE (EMP_NO) (

    PARTITION P1 VALUES LESS THAN (555),

    PARTITION P2 VALUES LESS THAN (999));

    CREATE INDEX EMP_I1 ON EMP (EMP_NO, EMP_NAME)

    GLOBAL

    PARTITION BY RANGE (EMP_NO) (

    PARTITION PI1 VALUES LESS THAN (500),

    PARTITION PI2 VALUES LESS THAN (MAXVALUE));

이 경우는 GLOBAL NONPREFIXED INDEX…

    CREATE INDEX EMP_I2 ON EMP (DEPT_NO, EMP_NAME)

    LOCAL

    (PARTITION PI1 TABLESPACE DATA2,

    PARTITION PI2 TABLESPACE DATE3);

이 경우는 LOCAL PREFIXED IDNEX..

CF. 인덱스 선정

    1) 테이블의 파티션 키 컬럼들이 인덱스 키 컬럼들과 동일한 순서를 갖는 경우

      (파티션 키 ID를 SELECT구문의 WHERE절에서 많이 사용할 때)

      LOCAL PREFIXED INDEX 사용 유리

      파티션이 제공하는 모든이점을 제공

    2) 인덱스가 파티션 키 안에 없는 컬럼의 데이터를 찾는 경우

      (파티션 키의 범위(0-100,101-200)가 SELECT구문의 WHERE절에서의 범위(90~120)와 다를 때)

      GLOBAL PREFIXED INDEX 사용 유리

      높은 성능, 그러나 파티션이 제공하는 관리상 편리성 상실

    3) 높은 데이터의가용성을 원하는 경우

      (예를 들면, 파티션 키(ID)이외의 컬럼(DEPT)을 SELECT구문의 WHERE절에서 많이 사용할 때)

      LOCAL NONPREFIXED INDEX 사용 유리

      DATA WAREHOUSING 과 같이 사용자들이 파티션 키의 순서를 따르지 않는

      임의의 검색조건들에 대한 QUERY문장들을 자주 사용할 때 유용

CF. 파티션 관련 SQL 명령어

    ALTER TABLE

      DROP PARTITION

      ADD PARTITION

      RENAME PARTITION

      MODIFY PARTITION

      TRUNCATE PARTITION

      SPLIT PARTITION

      MOVE PARTITION

      EXCHANGE PARTITION

    ALTER INDEX

      DROP PARTITION

      RENAME PARTITION

      REBUILD PARTITION

      MODIFY PARTITION

      SPLIT PARTITION

      UNUSABLE

CF. PARTITION 조회

    테이블 파티션

    – DBA_PART_TABLES

    – DBA_TAB_PARTITIONS

    – DBA_PART_KEY_COLUMNS

    인덱스 파티션

    – DBA_PART_INDEXES

    – DBA_IND_PARTITIONS

*********************************************

<<OBJECT 기능>>

기본 오브젝트 타입 선언

  CREATE [OR REPLACE] TYPE [SCHEMA.]TYPE_NAME AS OBJECT;

VARRAY 타입 선언

  CREATE TYPE TYPE_NAME AS VARRAY (LIMIT_VALUE) OF DATATYPE;

NESTED TABLE 선언

  CREATE TYPE TYPE_NAME AS TABLE OF DATETYPE;

확장 오브젝트 타입 선언

  CREATE [OR REPLACE] TYPE [SCHEMA.]TYPE_NAME AS OBJECT

  (ATTRIBUTE_NAME DATETYPE[, …]);

————————————

CREATE TYPE addr_type AS OBJECT (

gu VARCHAR2(10),

dong VARCHAR2(20));

/

CREATE TYPE name_type AS OBJECT (

name VARCHAR2(10),

phone VARCHAR2(10));

/

CREATE TYPE phonelist AS TABLE OF name_type;

/

CREATE TABLE person (

name VARCHAR2(10),

addr addr_type,

friend phonelist)

NESTED TABLE friend STORE AS per_fr_nst;

SQL> SELECT OBJECT_NAME, OBJECT_TYPE, OBJECT_ID, DATA_OBJECT_ID

  2  FROM USER_OBJECTS

  3  ORDER BY OBJECT_TYPE;

OBJECT_NAME OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID

———– ———– ——— ————–

SYS_C00996  INDEX           23323          23323

PERSON      TABLE           23321          23321

PER_FR_NST  TABLE           23322          23322

ADDR_TYPE   TYPE            23318

PHONELIST   TYPE            23320

NAME_TYPE   TYPE            23319

SQL> INSERT INTO person(name,addr,friend)

  2  VALUES (‘A’,addr_type(‘강남구’,’역삼동’),

  3          phonelist(name_type(‘친구1′,’1234’)));

1 개의 행이 만들어졌습니다.

SQL>

SQL> INSERT INTO person(name,addr,friend)

  2  VALUES (‘B’,addr_type(‘서초구’,’방배동’),

  3          phonelist(name_type(‘친구1′,’1234’),

  4                    name_type(‘친구2′,’3456’),

  5                    name_type(‘친구3′,’5678’)));

1 개의 행이 만들어졌습니다.

SQL> select * from person;

NAME

——————————

ADDR(GU, DONG)

——————————

FRIEND(NAME, PHONE)

——————————

A

ADDR_TYPE(‘강남구’, ‘역삼동’)

PHONELIST(NAME_TYPE(‘친구1’, ‘1234’))

B

ADDR_TYPE(‘서초구’, ‘방배동’)

PHONELIST(NAME_TYPE(‘친구1’, ‘1234’), NAME_TYPE(‘친구2’, ‘3456’), NAME_TYPE(‘친구3’, ‘5678’))

SQL> select addr from person;

ADDR(GU, DONG)

—————————–

ADDR_TYPE(‘강남구’, ‘역삼동’)

ADDR_TYPE(‘서초구’, ‘방배동’)

SQL> select * from the (select addr from person);

select * from the (select addr from person)

*

1행에 오류:

ORA-22905: 내포되지 않은 테이블 항목으로 부터 행을 가지고 올 수 없습니다

SQL> select gu, dong from the (select addr from person);

select gu, dong from the (select addr from person)

*

1행에 오류:

ORA-22905: 내포되지 않은 테이블 항목으로 부터 행을 가지고 올 수 없습니다

SQL> SELECT name, phone

  2  FROM THE (

  3  SELECT friend

  4  FROM person);

SELECT friend

*

3행에 오류:

ORA-01427: 단일 행 부속 질의에 2개이상의 행이 리턴되었습니다

SQL> SELECT name, phone

  2  FROM THE (

  3  SELECT friend

  4  FROM person

  5  WHERE name=’B’);

NAME                           PHONE

—————————— ———-

친구1                          1234

친구2                          3456

친구3                          5678

SQL> INSERT INTO THE (SELECT friend FROM person WHERE name=’A’)

  2  VALUES (name_type(‘친구6′,’8888’));

1 개의 행이 만들어졌습니다.

*********************************************

<<ORACLE PARAMETER>>

NAME                                 TYPE    VALUE

———————————— ——- ——————————

O7_DICTIONARY_ACCESSIBILITY          boolean TRUE

active_instance_count                integer

always_anti_join                     string  NESTED_LOOPS

always_semi_join                     string  standard

aq_tm_processes                      integer 0

audit_trail                          string  OS

background_core_dump                 string  partial

background_dump_dest                 string  C:\\ORACLE\\admin\\MKDB\\bdump

backup_tape_io_slaves                boolean FALSE

bitmap_merge_area_size               integer 1048576

blank_trimming                       boolean FALSE

buffer_pool_keep                     string

buffer_pool_recycle                  string

commit_point_strength                integer 1

compatible                           string  8.0.5

control_file_record_keep_time        integer 7

control_files                        string  C:\\ORACLE\\oradata\\MKDB\\CONTROL1.CTL,

                                             C:\\ORACLE\\oradata\\MKDB\\CONTROL2.CTL,

                                             C:\\ORACLE\\oradata\\MKDB\\CONTROL3.CTL

core_dump_dest                       string  %ORACLE_HOME%\\RDBMS\\TRACE

cpu_count                            integer 1

create_bitmap_area_size              integer 8388608

cursor_sharing                       string  EXACT

cursor_space_for_time                boolean FALSE

db_block_buffers                     integer 2048

db_block_checking                    boolean FALSE

db_block_checksum                    boolean FALSE

db_block_lru_latches                 integer 1

db_block_max_dirty_target            integer 2048

db_block_size                        integer 2048

db_domain                            string

db_file_direct_io_count              integer 64

db_file_multiblock_read_count        integer 8

db_file_name_convert                 string

db_files                             integer 1024

db_name                              string  MKDB

db_writer_processes                  integer 1

dblink_encrypt_login                 boolean FALSE

dbwr_io_slaves                       integer 0

disk_asynch_io                       boolean FALSE

distributed_transactions             integer 10

dml_locks                            integer 264

enqueue_resources                    integer 1308

event                                string

fast_start_io_target                 integer 0

fast_start_parallel_rollback         string  LOW

fixed_date                           string

gc_defer_time                        integer 10

gc_files_to_locks                    string

gc_releasable_locks                  integer 0

gc_rollback_locks                    string  0-128=32!8REACH

global_names                         boolean TRUE

hash_area_size                       integer 131072

hash_join_enabled                    boolean TRUE

hash_multiblock_io_count             integer 0

hi_shared_memory_address             integer 0

hs_autoregister                      boolean TRUE

ifile                                file    C:\\ORACLE\\admin\\MKDB\\pfile\\initMKDB.ora

instance_groups                      string

instance_name                        string  MKDB

instance_number                      integer 0

java_max_sessionspace_size           integer 0

java_pool_size                       string  32768

java_soft_sessionspace_limit         integer 0

job_queue_interval                   integer 60

job_queue_processes                  integer 0

large_pool_size                      string  0

license_max_sessions                 integer 0

license_max_users                    integer 0

license_sessions_warning             integer 0

lm_locks                             integer 12000

lm_ress                              integer 6000

local_listener                       string

lock_name_space                      string

lock_sga                             boolean FALSE

log_archive_dest                     string  C:\\ORACLE\\oradata\\MKDB\\archive

log_archive_dest_1                   string

log_archive_dest_2                   string

log_archive_dest_3                   string

log_archive_dest_4                   string

log_archive_dest_5                   string

log_archive_dest_state_1             string  enable

log_archive_dest_state_2             string  enable

log_archive_dest_state_3             string  enable

log_archive_dest_state_4             string  enable

log_archive_dest_state_5             string  enable

log_archive_duplex_dest              string

log_archive_format                   string  %%ORACLE_SID%%T%TS%S.ARC

log_archive_max_processes            integer 1

log_archive_min_succeed_dest         integer 1

log_archive_start                    boolean TRUE

log_archive_trace                    integer 0

log_buffer                           integer 8192

log_checkpoint_interval              integer 10000

log_checkpoint_timeout               integer 1800

log_checkpoints_to_alert             boolean FALSE

log_file_name_convert                string

max_commit_propagation_delay         integer 700

max_dump_file_size                   string  10240

max_enabled_roles                    integer 30

max_rollback_segments                integer 30

mts_circuits                         integer 0

mts_dispatchers                      string

mts_listener_address                 string

mts_max_dispatchers                  integer 5

mts_max_servers                      integer 20

mts_multiple_listeners               boolean FALSE

mts_servers                          integer 0

mts_service                          string  MKDB

mts_sessions                         integer 0

nls_calendar                         string

nls_comp                             string

nls_currency                         string

nls_date_format                      string

nls_date_language                    string

nls_dual_currency                    string

nls_iso_currency                     string

nls_language                         string  AMERICAN

nls_numeric_characters               string

nls_sort                             string

nls_territory                        string  AMERICA

nls_time_format                      string

nls_time_tz_format                   string

nls_timestamp_format                 string

nls_timestamp_tz_format              string

object_cache_max_size_percent        integer 10

object_cache_optimal_size            integer 102400

open_cursors                         integer 50

open_links                           integer 4

open_links_per_instance              integer 4

ops_interconnects                    string

optimizer_features_enable            string  8.1.7

optimizer_index_caching              integer 0

optimizer_index_cost_adj             integer 100

optimizer_max_permutations           integer 80000

optimizer_mode                       string  CHOOSE

optimizer_percent_parallel           integer 0

oracle_trace_collection_name         string

oracle_trace_collection_path         string  %ORACLE_HOME%\\OTRACE\\ADMIN\\CDF\\

oracle_trace_collection_size         integer 5242880

oracle_trace_enable                  boolean FALSE

oracle_trace_facility_name           string  oracled

oracle_trace_facility_path           string  %ORACLE_HOME%\\OTRACE\\ADMIN\\FDF\\

os_authent_prefix                    string

os_roles                             boolean FALSE

parallel_adaptive_multi_user         boolean FALSE

parallel_automatic_tuning            boolean FALSE

parallel_broadcast_enabled           boolean FALSE

parallel_execution_message_size      integer 2148

parallel_instance_group              string

parallel_max_servers                 integer 0

parallel_min_percent                 integer 0

parallel_min_servers                 integer 0

parallel_server                      boolean FALSE

parallel_server_instances            integer 1

parallel_threads_per_cpu             integer 2

partition_view_enabled               boolean FALSE

plsql_v2_compatibility               boolean FALSE

pre_page_sga                         boolean FALSE

processes                            integ

er 50

query_rewrite_enabled                boolean FALSE

query_rewrite_integrity              string  enforced

rdbms_server_dn                      string

read_only_open_delayed               boolean FALSE

recovery_parallelism                 integer 0

remote_dependencies_mode             string  TIMESTAMP

remote_login_passwordfile            string  EXCLUSIVE

remote_os_authent                    boolean FALSE

remote_os_roles                      boolean FALSE

replication_dependency_tracking      boolean TRUE

resource_limit                       boolean FALSE

resource_manager_plan                string

rollback_segments                    string

row_locking                          string  always

serial_reuse                         string  DISABLE

serializable                         boolean FALSE

service_names                        string  MKDB

session_cached_cursors               integer 0

session_max_open_files               integer 10

sessions                             integer 60

shadow_core_dump                     string  partial

shared_memory_address                integer 0

shared_pool_reserved_size            string  2820925

shared_pool_size                     string  56418508

sort_area_retained_size              integer 65536

sort_area_size                       integer 65536

sort_multiblock_read_count           integer 2

sql92_security                       boolean FALSE

sql_trace                            boolean FALSE

sql_version                          string  NATIVE

standby_archive_dest                 string  %ORACLE_HOME%\\RDBMS

star_transformation_enabled          string  FALSE

tape_asynch_io                       boolean TRUE

text_enable                          boolean FALSE

thread                               integer 0

timed_os_statistics                  integer 0

timed_statistics                     boolean FALSE

tracefile_identifier                 string

transaction_auditing                 boolean TRUE

transactions                         integer 66

transactions_per_rollback_segment    integer 5

use_indirect_data_buffers            boolean FALSE

user_dump_dest                       string  C:\\ORACLE\\admin\\MKDB\\udump

utl_file_dir                         string

———————————————–

CF. HIGH WATER MARK 찾기

    ANALYZE TABLE TABLE_NAME COMPUTE STATISTICS;(HWM, UNUSED BLOCK, AVERAGE ROW LENGTH)

    ANALYZE TABLE TABLE_NAME ESTIMATE STATISTICS SAMPLE [# ROWS|# PERCENT];

    SELECT BLOCKS, EMPTY_BLOCKS

    FROM USER_TABLES

    WHERE TABLE_NAME = ‘TABLE_NAME’;

CF. CHAINING & MIGRATION(UTLCHAIN.SQL -> CHAINED_ROWS 테이블 생성)

    ANALYZE TABLE TABLE_NAME LIST CHAINED ROWS;

    SELECT * FROM CHAINED_ROWS;

CF. ANALYZE INDEX INDEXNAME VALIDATE STRUCTURE;

    SELECT BLOCKS, BTREE_SPACE, USED_SPACE, PCT_USED, LF_ROWS, DEL_LF_ROWS

    FROM INDEX_STATS;

————————————————

ALTER SESSION ENABLE PARALLEL DML;

ALTER SESSION SET CONSTRAINT(S) = IMMEDIATE|DEFERRED|DEFAULT

ALTER SESSION SET SQL_TRACE=TRUE;

ALTER SESSION SET NLS_DATE_FORMAT=’DD.MM.YYYY’;

ALTER SESSION SET NLS_SORT=GERMAN;

ALTER SYSTEM SWITCH LOGFILE;

ALTER SYSTEM CHECKPOINT;

ALTER SYSTEM ARCHIVE LOG ALL;

ALTER SYSTEM ENABLE RESTRICTED SESSION;

ALTER SYSTEM KILL SESSION ‘SID, SERIAL#’;

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

ALTER SYSTEM SET TIMED_STATISTICS=TRUE;

ALTER SYSTEM SET SORT_AREA_SIZE=131072 DEFERRED;

서진우

서진우

슈퍼컴퓨팅 전문 기업 클루닉스/ 상무(기술이사)/ 정보시스템감리사/ 시스존 블로그 운영자

You may also like...

페이스북/트위트/구글 계정으로 댓글 가능합니다.