웹 개발과 관련된 모든 것들

데이터 이관작업은 별로 많지는 않지만...

어제 exp 백업시 tablespace 단위로 이동하게 작업을 했다...

comment 부분이 이관이 정상적으로 되지 않았다.

comments 부분은 저장되는게 아래 쿼리와 같다...

  SELECT   o.name, c.name, co.comment$
     FROM   sys.obj$ o, sys.col$ c, sys.com$ co
    WHERE       o.owner# = USERENV ('SCHEMAID')
            AND o.type# IN (2, 4)
            AND o.obj# = c.obj#
            AND c.obj# = co.obj#(+)
            AND c.intcol# = co.col#(+)
            AND BITAND (c.property, 32) = 0

자 이 정보를 이관하려면

COMMENT ON TABLE ~ 명령을 사용 하면 아주 쉬워진다.

1. 테이블 comment 정보 이관하기 반드시 NULL이 없는것을 기준으로 한다 ~

SELECT
    'COMMENT ON TABLE '
    || TABLE_NAME
    || ' IS '
    || ''''
    || COMMENTS
    || ''';'
 FROM USER_TAB_COMMENTS
WHERE COMMENTS IS NOT NULL;

2.  컬럼 comment 정보 이관하기 위해서도 역시 반드시 NULL이 없는 데이터를 기준으로 합니다. ~

SELECT
    'COMMENT ON COLUMN '
    || TABLE_NAME
    || '.'
    || COLUMN_NAME
    || ' IS '
    || ''''
    || COMMENTS   
    || ''';'
 FROM USER_COL_COMMENTS WHERE COMMENTS IS NOT NULL;   

기존에는 테이블을 새로 작성(reorg)  작업을 통해

커진 테이블 스페이스 크기를 복구 가능하였으나

10g 부터는 shrink 기능을 이용하여 손쉽게 테이블을 줄일수 있게 되었다.

Shrink 기능은 HWM(High Water Mark)를 줄일수 있는 기능이다.


- 사전 확인 사항

init.ora 파일내에 compatible = 10.2 이상인것을 확인

ASSM (Automatic Segment Space Management) 테이블 스페이스여만 가능

각 테이블 조회 방법은

SELECT obj.owner
,obj.table_name
,(CASE WHEN NVL(idx.cnt, 0) < 1 THEN 'YES' ELSE 'NO' END) shrinkable
FROM dba_tables obj,
(SELECT table_name, COUNT(rownum) cnt
FROM dba_indexes
WHERE index_type LIKE 'FUN%'
GROUP BY table_name) idx
WHERE obj.table_name = idx.table_name(+)
AND obj.tablespace_name = upper('&1')

쿼리를 통해 (테이블 스페이스 명을 인자값으로)

확인 가능하다.

1. 테이블 검색 방법

SELECT * FROM TABLE(DBMS_SPACE.ASA_RECOMMENDATIONS());

아래와 같이 테이블의 DBA_SEGMENTS에서 볼수 있는 크기와

축소 가능 크기가 조회된다.

SEGMENT_OWNER	SEGMENT_NAME	SEGMENT_TYPE	PARTITION_NAME	ALLOCATED_SPACE	USED_SPACE	RECLAIMABLE_SPACE
MAX_TI	SMR_IF_LOG	TABLE		6761793074	5910149080	851643994
MAX_TI	SMR_IF_LOG_I01	INDEX		478455081	420139142	58315939
MAX_TI	SMR_IF_LOG_I02	INDEX		411921243	398204079	13717164

2. 축소하기

가장 먼저 행들을 이동 가능하도록 변경한다.

위의 SMR_IF_LOG 테이블을 기준으로 설명하겠다.

ALTER TABLE SMR_IF_LOG ENABLE ROW MOVEMENT;

--> 반대로는 ALTER TABLE SMR_IF_LOG DISABLE ROW MOVEMENT; 이다..

이제 SHRINK 기능을 수행할수 있으며

다음 3가지 형태로 가능하다

① ALTER TABLE SMR_IF_LOG SHRINK SPACE; 
    -- SMR_IF_LOG 테이블의 공간만 TABLESPACE로 환원

② ALTER TABLE SMR_IF_LOG SHRINK SPACE CASCADE;
    -- 모든 관련 OBJECT까지 TABLESPACE으로 공간 환원

③ ALTER TABLE SMR_IF_LOG SHRINK SPACE COMPACT;
   -- 행들만 이동시킴.



※ SHRINK 를 적용 시킬  없는 테이블
UNDO segments
temporary segments
clustered tables
tables with a column of datatype LONG
LOB indexes
IOT mapping tables and IOT overflow segmnets
tables with MVIEWS with ON COMMIT
tables with MVIEWS which are based on ROWIDs.

DBA_USERS                      : 데이터베이스 USER에 대한 정보를 보여준다
DBA_ROLES                      : ROLE에 대한 정보를 보여준다.
DBA_TAB_PRIVS                  : 테이블에 대한 권한이 설정된 정보를 보여 준다
DBA_SYS_PRIVS                  : SYSTEM 권한이 설정된 정보를 보여준다
DBA_ROLE_PRIVS                 : ROLE에 대한 권한이 설정된 정보를 보여 준다.
DBA_COL_PRIVS                  : 컬럼 단위로 권한이 설정된 정보를 보여준다.
DBA_SEGMENTS                   : 세그먼트(저장공간이 있는 오브젝트)에 대한 정보를 보여준다.
DBA_OBJECTS                    : 모든 오브젝트에 대한 정보를 보여준다.

DBA_TABLESPACES                : 테이블 스페이스에 대한 정보를 보여준다.
DBA_DATA_FILES                 : 테이블스페이스를 구성하고 있는 데이터 파일에 대한 정보를 보여준다.
DBA_FREE_SPACE                 : 아직 사용되지 않은 영역에 대한 정보를 보여준다.
DBA_EXTENTS                    : 할당된? EXTENT의 정보를 보여준다.
DBA_TS_QUOTAS                  : QUOTA가 설정된 정보를 보여준다
DBA_TABLES                     : 테이블에 대한 정보를 보여준다.
DBA_TAB_COLUMNS                : 테이블을 구성하는 컬럼에 대한 정보를 보여준다
DBA_TAB_COMMENTS               : 테이블의 설명에 대한 정보를 보여준다
DBA_PART_TABLES                : 파티션 테이블에 대한 정보를 보여준다.
DBA_PART_KEY_COLUMNS           : 파티션을 구성하는 기준 컬럼에 대한 정보를 보여준다
DBA_COL_COMMENTS               : 컬럼에 대한 설명에 대한 정보를 보여 준다
DBA_INDEXES                    : 인덱스에 대한 정보를 보여준다.
DBA_PART_INDEXES               : 파티션된 인덱스에 대한 정보를 보여준다
DBA_IND_COLUMNS                : 인덱스를 구성하는 컬럼에 대한 정보를 보여준다
DBA_CONSTRAINTS                : 테이블에 걸려있는 제약조건을 보여준다.
DBA_CONS_COLUMNS               : 제약조건을 구성하는 컬럼에 대한 조건을 보여준다.
DBA_VIEWS                      : VIEW를 정의한 정보를 보여준다.
DBA_SYNONYMS                   : 시노님에 대한 정보를 보여준다.
DBA_SEQUENCES                  : 시퀀스에 대한 정보를 보여준다.
DBA_DB_LINKS                   : DB 링크에 대한 정의를 보여준다
DBA_TRIGGERS                   : 트리거에 대한 정의를 보여준다.
DBA_TRIGGER_COLS               : 컬럼 단위로 작성된 트리거에 대한 정의를 보여준다.
DBA_ROLLBACK_SEGS              : 롤백세그먼트에 대한 정보를 보여 준다.
DBA_SOURCE                     : FUNCTION, PROCEDURE,PACKAGE를 구성하는 PL/SQL 소스코드를 보여준다

 

출처 : http://www.jakartaproject.com/board-read.do?boardId=dbtip&boardNo=119026265610890&command=READ&page=1&categoryId=-1

사용되지 않은 공간을 DEALLOCATE하는 방법

========================================

 

 

Purpose

-------

TABLE, INDEX, CLUSTER 등의 DATABASE OBJECT들은 입력되는 자료의 양이

증가함에 따라서 새로운 EXTENT들이 자동적으로 할당된다. 이 때 필요 이상

과도한 크기의 EXTENT가 할당되면 실제로 자료가 들어있지 않거나 들어갈

예정이 없는 죽은 공간이 생길 수 있게 된다.

이러한 죽은 공간은 DISK 전체의 사용 효율을 저하시키는 원인이 되므로

적절히 제거되어야 하는데 여기서는 그 중에서 DEALLOCATE라는 방법에

대해서 알아본다

 

 

Explanation

-----------

 

1. DEALLOCATE의 대상

 

TABLE, INDEX, CLUSTER들이 생성된 이후에 한번도 사용되어지지 않은 공간

ORACLE에서는 이렇게 한번도 사용되지 않은 공간을 HIGH WATER MARK라는

개념으로 설명하는데 이는 다음과 같다.

 

* HIGH WATER MARK

TABLE_A 의 EXTENT 구조

|-------------------|--------------------|-------------------|

EXTENT1 EXTENT2 | EXTENT3 |

| |

A.현재 DATA가 B.HIGH WATER MARK

차있는 위치

 

TABLE이 생성된 이후 DATA가 INSERT되어 B지점까지 공간을 점유하고

있었다고 가정을 하자. 이후 DATA가 DELETE되어 현재 DATA들이

점유하고 있는 위치는 A라고 하면 B지점이 HIGH WATER MARK가 된다.

 

DEALLOCATE 작업을 하면 HIGH WATER MARK 위의 공간만이 FREE되며,

HIGH WATER MARK 아래의 공간들(B-A)은 이후 DATA가 다시 INSERT될 것을

예상하여 그대로 빈 공간으로 남겨두게 된다.

 

 

2. DEALLOCATE의 대상이 되는 공간이 어느 정도 존재하는지 파악하는 방법

 

ANALYZE TABLE table_name COMPUTE STATISTICS ;

 

예1)

SELECT EMPTY_BLOCKS, BLOCKS

FROM DBA_TABLES

WHERE TABLE_NAME = 'table_name' ;

 

EMPTY_BLOCKS BLOCKS

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

6530 2400

 

EMPTY_BLOCKS - OBJECT에 할당된 총 SPACE중 HIGH WATER MARK 위의 공간

BLOCKS - OBJECT에 할당된 총 SPACE중 HIGH WATER MARK 아래 공간

 

여기서 EMPTY_BLOCKS + BLOCKS(해당 OBJECT의 총 SPACE)에 비해서

EMPTY_BLOCKS 이 과도하게 크고 앞으로 더 이상의 DATA INSERT도 일어나지

않을 계획이라면 DEALLOCATE를 하는 것이 좋다.

 

 

3. SYNTAX

 

ALTER TABLE table_name DEALLOCATE UNUSED [KEEP integerK] ;

ALTER INDEX index_name DEALLOCATE UNUSED [KEEP integerK] ;

ALTER CLUSTER cluster_name DEALLOCATE UNUSED [KEEP integerK] ;

 

KEEP 절을 사용하면 KEEP 절에 정의된 크기 만큼은 남겨 두고

DEALLOCATE를 한다.

 

예2)

위의 예1)에서

ALTER TABLE table_name DEALLOCATE UNUSED ;

를 수행하면 다음과 같이 SPACE가 FREE된다.

 

ANALYZE TABLE table_name COMPUTE STATISTICS ;

 

SELECT EMPTY_BLOCKS, BLOCKS

FROM DBA_TABLES

WHERE TABLE_NAME = 'table_name' ;

 

EMPTY_BLOCKS BLOCKS

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

0 2400

 

 

위의 조회 결과를 보면 HIGH WATER MARK 위 부분(EMPTY_BLOCKS)은

FREE되어 0으로 표시되고 HIGH WATER MARK 아래 부분(BLOCKS)은

그대로(2400) 남아 있다

 

 

 

 

 

ORA-01653 에러와 같은 경우입니다..

해당테이블이 단지 일반 테이블이 아니라

INDEX 테이블이라는 것만 빼고... ^^

그럼 도움이 되셨길....

 

코멘트 2을 처음 님이 의도한 방법이고

코멘트 3는 제가 말씀드린 방법입니다

No. 11875

====================================================================================================

ORA-1654 ERROR ON INDEX SEGMENT

====================================================================================================

 

PURPOSE

-------

 

이 자료는 ORA-1654 error를 해결하기 위한 방법에 대한 자료이다.

 

 

Problem Description

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

 

DML 작업 시에 다음과 같은 ORA-1654 error가 특정 tablespace에 대하여

발생하는 경우가 있다. 이에 대한 해결방법을 알아보기로 한다.

 

01654, 00000, "unable to extend index %s.%s by %s in tablespace %s"

예) unable to extend index owner.object by 40964 in tablespace INDEX;

 

 

Workaround

----------

none

 

 

Solution Description

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

 

1. tablespace에 남아 있는 공간 중 가장 큰 연속된 공간의 사이즈를 구합니다.

 

SELECT TO_CHAR(MAX(bytes), '999,999,999,999') free_space

FROM dba_free_space

WHERE tablespace_name = 'HDMF_TM_DATA_70';

 

 

ora-1654 에러가 났던 tablespace 이름을 대문자로 위에 써줍니다.

위에 나온 수치는 연속된 block들 가운데 가장 큰 사이즈의 extent를 보여주는

것인데, next extent를 할당하기 위해서는 위에 나온 수치보다 더 큰 사이즈를

필요로 하는 것입니다.

 

"The above query returns the largest available contiguous chunk of space."

 

 

2. index의 storage parameter인 next_extent 값과 pct_increase 값을 확인합니다.

 

SELECT next_extent, pct_increase

FROM dba_indexes

WHERE index_name = 'INDEX NAME' AND owner = 'OWNER';

 

< TABLE 일경우 >

SELECT TO_CHAR(next_extent, '999,999,999') NEXT_EXTENT, pct_increase

FROM dba_tables

WHERE table_name = 'TB_KYBSMST' AND owner = 'HDMF_PRD';

 

--> 파티션 테이블에 대한 파티션 인덱스인 경우는

SELECT next_extent, pct_increase

FROM dba_ind_partitions

WHERE index_name = 'INDEX NAME' AND owner = 'OWNER';

 

 

ora-1654 에러가 발생한 index의 next extent 값과 pct_increase 값이 얼마인지

확인해 보십시오.

위에서 나타난 next_extent 값과 max(bytes) 값을 비교해 보세요.

 

 

3. 인스턴스의 db_block_size를 확인합니다.

 

vi $ORACLE_HOME/dbs/initSID.ora

 

db_block_size = 2048 또는 4096 또는 8192일 것입니다.

 

ora-1654 에러에 나타난 by 다음의 수치(예:40964) * db_block_size 만큼의

사이즈가 next_extent(byte 단위) 값과 같을 것이며, 이 만큼의 extent 영역을

할당할 수 없다는 뜻입니다.

따라서 datafile을 추가 시 이 byte 값 이상의 사이즈를 추가해야 합니다.

 

 

4. ora-1654 에러를 해결하는 방법

 

There are several options for solving failure to extend.

 

Manually Coalesce Adjacent Free Extents

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

 

ALTER TABLESPACE <tablespace name> COALESCE;

 

The extents must be adjacent to each other for this to work.

 

Add a Datafile:

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

 

ALTER TABLESPACE <tablespace name> ADD DATAFILE '<full path and

file name>' SIZE <integer> < |k|m>;

 

 

Lower "next_extent" and/or "pct_increase" size:

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

 

For non temporary segment problem:

 

ALTER <object> <object name> STORAGE ( next <integer> < |k|m>

pctincrease <integer>);

 

For a temporary segment problem:

 

ALTER TABLESPACE <tablespace name> DEFAULT STORAGE

(initial <integer> next <integer> <|k|m> pctincrease <integer>);

 

 

Resize the Datafile:

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

 

ALTER DATABASE DATAFILE '<full path and file name>' RESIZE

<integer> <k|m>;

 

 

Reference Document

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

<Note:146595.1

오라클 10G부터는

SHRINK 기능을 사용하면 된다.

위의 기능은 HWM (HIGH WATER MARK) 윗부분의 사용하지 않은 공간만 반환함

토드를 사용하려 할때 항상 Oracle 클라이언트를 설치 해야만 하는 불편함이 있었는데

간단한 클라이언트 환경을 구성할 수 있는 방법을 오라클에서 제공해주고 있다..

 

 

instantclient 라고 10g 부터 제공해주는거 같은데..

우선 아래 사이트에 들어가서 본인이 사용하고자 하는 버전을 다운 받는다

 

http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html

 

다운받을 파일은 instantclient-basic , instantclient-sqlplus 두가지를 받으면 됩니다.

sqlplus는 접속 테스트용..

 

1. 먼저 Instant Client Basic을 적당한 디렉토리에 압축 푼다.
  ex) C:\oracle\instanceclient

2. instant Client sqlplus 를 같은 디렉토리에 압축을 푼다.

 

3. tnsnames.ora 파일을 적당한 곳에 위치 시킨다.
  ex) C:\oracle\network

 

4. Instant Client를 복사한 디렉토리를 Path 환경변수에 추가한다.
  ex) set Path=%Path%;C:\oracle\instanceclient

 

5. tnsnames.ora 파일이 있는 디렉토리를 TNS_ADMIN 환경변수에 지정하여 준다.
  ex) set TNS_ADMIN=C:\oracle\network

 

6. 레지스트리 편집기를 열고 NLS_LANG 추가 (한글 깨질경우)
    경로: HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
    문자열값 : KOREAN_KOREA.KO16MSWIN949

토드를 실행하고... 접속하면 됨.

 

참고로 sqlplus 를 실행할 때 msvcr71.dll 파일이 없다는 에러가 나오면(vista에서..)
해당 dll 파일을 찾아서 instance client를 설치한 디렉토리에 복사하여 준다.
 -> 구글에서 dll 명으로 검색하면 바로 다운받을 수 있다..

 

 

위와같이 설치 한다음에 toad 등을 실행하면 깔끔하게 동작한다..^^

 

 

^^ 무겁고, 귀찮고,, 내가 모르는것이 막 깔리는 기분이던 오라클 클라이언트를 이제 설치 안해도 된다는게 ... 깨운하다!~~ ㅎㅎ^^

출처 : http://blog.naver.com/jajakk93?Redirect=Log&logNo=100062623719

RAW 파일 한쪽만 권한을 바꾸어 주고 마운트시 정상적으로 마운트 되나

아래와 같은 오류 발생함...

ORA-01157: 데이터 28 파일을 식별 또는 잠금 할 수 없습니다- DBWR 추적 파일을 보십시오

ORA-01110: 28 데이터 파일: '/dev/rnvms06'

 

If you connect to sqlplus AS SYSDBA you could try

ALTER SYSTEM CHECK DATAFILES ; 
ALTER SYSTEM CHECKPOINT;

데이터 파일 복구 하도록 하고

양쪽 시스템 동기화 맞추기 ~

RAC 시스템 모두에서 위와 같은 명령을 턱 ~

원래는 에러를 발생하지 않게 하는게 정상임...

실행법

sqlldr id/password control=KB51_COL.CTL

 

[db1:/orabackup]$vi KB51_COL.CTL
"KB51_COL.CTL" 26 lines, 1165 characters
LOAD DATA
INFILE 'KB51_COL.LST'
BADFILE 'KB51_COL.BAD'
DISCARDFILE 'KB51_COL.DIS'
APPEND                  -- 추가로 삽입만 한다.
        INTO TABLE KB51_20090226
        fields terminated by ","
       (
                                    TELG_COM    CHAR(51),
                                    JOIN_NO    CHAR(15),
                                    JOIN_BSN_NO    CHAR(10),
                                    VCHR_CARD_ID    CHAR(16),
                                    APROV_NO    CHAR(8),
                                    APROV_DT    CHAR(8),
                                    SALE_DT    CHAR(8),
                                    PAY_DT    CHAR(8),
                                    SETTLE_AMT    CHAR(13),
                                    AGREE_AMT    CHAR(13),
                                    GOV_PAY_AMT    CHAR(13),
                                    INCM_TAX    CHAR(13),
                                    SOCI_TAX    CHAR(13),
                                    FEE    CHAR(13),
                                    CNCL_TP    CHAR(1),
                                    TX_GB    CHAR(1),
                                    VCHR_BLNC    CHAR(6) nullif VCHR_BLNC = blanks  -- 공백일경우 NULL로
        )

External Table

- Oracle 9i 부터 제공하는 기능으로, Table이 Database 내부의 Tablespace가 아닌 Database 외부의 OS 파일로 존재하게 하는 기능이다.

- 흔히 DW(Data Warehousing) 환경에서 기본적인 ETL(Extraction, Transformation, Loading) 작업에 사용된다.

- External table 의 데이터엔 읽기전용으로만 접근이 가능하고, 저장되는 형식은 일정한 포맷이 정해진 것이 아니라, 규칙적인 포맷으로만 저장이 되어 있으면 읽어올때 그 포맷을 지정하여 불러올 수 있다. 이렇게 일정한 규칙으로 생성되어 있기 때문에 다른 Database 에서도 이 데이터를 사용할 수가 있는 장점이 있다. → 플랫폼에 독립적이다.

- External Table의 데이터에 대해 select, join, sort 를 할 수 있고, view, synonym 도 생성할 수 있다. 그러나 데이터는 읽기전용이기 때문에 Update, Insert, Delete는 불가능하다. index 생성도 불가능하다.

※ Possible - select, join, sort, view, synonym

※ Impossible - update, insert, delete, index

 

- External Table 에 대한 metadata 정의는 아래 형식으로 한다.

SQL> CREATE TABLE table_name ORGANIZATION EXTERNAL ...

- External Table에 데이터를 처음 Unload 하게 되면, 자동으로 Select 문장의 데이터타입을 기준으로 metadata가 생성된다.

 

- External Table로 접근하는 방법

(1) ORACLE_LOADER : external 파일의 데이터를 읽어오는 기능. SQL*Loader 의 문법을 따른다.

(2) ORACLE_DATAPUMP : external 파일에 데이터를 쓰는 기능을 하고, 이를 다시 Database에 다시 reload 한다.

                                      Database로부터 데이터를 읽고 External Table에 insert 한다. 한번에 여러개 파일 가능.

 

- External Table의 장점

(1) 병렬 SQL 수행가능

(2) Table로 Loading 불필요

(3) Storage 절약

(4) Virtual Read-only Table

(5) External regular table 간 조인가능

 

- External Table의 단점

(1) index 생성 불가 (파일처리가 더 빠름)

(2) Join 이나 Filter 수행이 어려움

 

====================================================================================================================

CREATE EXTERNAL TABLE

 

- CREATE TABLE ... ORGANIZATION EXTERNAL 구문을 사용하여 생성하기는 하지만, 실제로 Table이 생성되는 것은 아니다. external table은 어떤 extents 와도 관련이 되어 있지 않다.

- External Data에 접근하려면 Data Dictionary 에 metadata를 생성해야 한다.

 

 

[예제] Creating External Tables

- 여기서 사용하는 employees table은 hr.employees table 구조를 그대로 사용하였다.

- 필요한 권한: CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE

- Directory 오브젝트의 권한 : READ, WRITE

 

 

1. 아래처럼 External Table을 사용할 디렉토리를 생성하고 oracle 관리자가 관련파일에 접근할 수 있도록 디렉토리에 대한 권한을 변경한다.

 

% cd /export/home/kangyw/oracle_test

% mkdir data log bad

% chmod -R 777 data log bad

 

 

 

 

2. data 디렉토리에 External Table의 데이터 파일 2개를 생성한다. (empxt1.dat, empxt2.dat)

 

% cd data

% vi empxt1.dat

360,Jane,Janus,ST_CLERK,121,17-MAY-2001,3000,0,50,jjanus
361,Mark,Jasper,SA_REP,145,17-MAY-2001,8000,.1,80,mjasper
362,Brenda,Starr,AD_ASST,200,17-MAY-2001,5500,0,10,bstarr
363,Alex,Alda,AC_MGR,145,17-MAY-2001,9000,.15,80,aalda

 

% vi empxt2.dat

401,Jesse,Cromwell,HR_REP,203,17-MAY-2001,7000,0,40,jcromwel
402,Abby,Applegate,IT_PROG,103,17-MAY-2001,9000,.2,60,aapplega
403,Carol,Cousins,AD_VP,100,17-MAY-2001,27000,.3,90,ccousins
404,John,Richardson,AC_ACCOUNT,205,17-MAY-2001,5000,0,110,jrichard

 

 

3. External Table의 디렉토리를 지정하고, kangyw user에게 해당 디렉토리에 대한 권한을 준다.

   

 

Data Dictionary 에 해당 디렉토리 정보가 등록되고, 이 디렉토리 접근은 kangyw 만 가능하다.

 

 

 

4. External Table에서 DB내부 Table로 Unload를 하기 위해 필요한 employees table은

hr user의 employees table 구조를 사용해야하므로, kangyw user 에게 hr.employees table과 동일한 구조로 employees table을  생성해준다. (subquery as 구문 사용)

 

SQL> CREATE TABLE kangyw.employees AS SELECT * FROM hr.employees WHERE EMAIL = ' ';

 

 

 

5. External Table admin_ext_employees을 생성한다.

dat 파일이 생성시 지정되기 때문에 지금 정의하는 External Table 을 Select 하게 되면, OS상의 dat 파일을 읽어들이게 된다.

 

CREATE TABLE admin_ext_employees (
       employee_id     NUMBER(4),
       first_name      VARCHAR2(20),
       last_name       VARCHAR2(25),
       job_id          VARCHAR2(10),
       manager_id      NUMBER(4),
       hire_date       DATE,
       salary          NUMBER(8,2),
       commission_pct  NUMBER(2,2),
       department_id   NUMBER(4),
       email           VARCHAR2(25))

      

       ORGANIZATION EXTERNAL (
              TYPE ORACLE_LOADER      ☞ 접근방식 지정 (ORACLE_LOADER / ORACLE_DATAPUMP)

                                                           default : ORACLE_LOADER

              DEFAULT DIRECTORY admin_dat_dir  
              ACCESS PARAMETERS)  ☞ ㅂ

 

       records delimited by newline
       badfile admin_bad_dir:'empxt%a_%p.bad'
       logfile admin_log_dir:'empxt%a_%p.log'
       fields terminated by ','                                       ☞ 필드구분자는 쉼표(,)로 함.

                missing field values are null (
                        employee_id, first_name, last_name, job_id, manager_id,
                        hire_date char date_format date mask "dd-mon-yyyy",
                        salary, commission_pct, department_id, email)
                )

                Location('empxt1.dat', 'empxt2.dat')         ☞ 데이터파일 위치 지정
        )

        PARALLEL       ☞ data 소스에서 parallel 쿼리가 가능하게 함. 많은 양의 데이터를 다룰때 사용.
        REJECT LIMIT UNLIMITED;     ☞ External Table 조회 시 발생하는 에러 수에 제한이 없음.

 

 

생성 후 바로 조회하면 데이터 확인이 가능하다.

 

 

 

6. External Table admin_ext_employees 의 내용(OS상 dat 파일)을 DB 내의 employees table에 Insert 한다.

 

 

 

employees table을 조회하면 데이터가 들어가있는 것을 확인할 수 있다.

 

 

 

7. 데이터가 정상적으로 Loading 되면 로그를 확인한다.

 

 

 

 

=====================================================================================================================

ALTER EXTERNAL TABLE

 

SQL> ALTER TABLE admin_ext_employees REJECT LIMIT 100;

SQL> ALTER TABLE admin_ext_employees PROJECT COLUMN REFERNCED;

SQL> ALTER TABLE admin_ext_employees PROJECT COLUMN ALL;

SQL> ALTER TABLE admin_ext_employees DEFAULT DIRECTORY admin_dat2_dir;

SQL> ALTER TABLE admin_ext_employees ACCESS PARAMETERS (FIELDS TERMINATED BY ';');

SQL> ALTER TABLE admin_ext_employees Location('empxt3.txt', 'empxt4.txt');

- PARALLEL, ADD COLUMN, MODIFY COLUMN, DROP COLUMN, RENAME TO 는 일반 테이블변경방식과 동일함.

 

--실수로 지운 데이터 살리기..

예 kfm08ot1이라는 테이블의 bnk_cd ='04' 인 데이터를 실수로 삭제를 했다.

commit; 도 완료된 상태라면..

앞이 막막할것이다.

이럴땐 이렇게 데이터를 불러보자..

SELECT * FROM KFM08OT1
 as of timestamp ( systimestamp - interval '10' minute)
where bnk_cd = '04'
조회후  파일을 txt나 엑셀로 저장후..

다시 임포트 해야 합니다.

물론 데이터를 삭제하기 전에는.. 반드시 백업을 받아두고 하는것이.. 제일

중요한 일이지만.. 사람인 이상.. 실수 할수도 있을거라 생각됩니다.

위에 쿼리가 어느정도 한계가 있으니..   지울때는 꼭 데이터 받는 것을 생활화

.. 해야 하지 않을까 합니다.

select * from emp as of timestamp( systimestamp-interval '10' minute) where deptno=20

select * from emp as of timestamp( systimestamp-interval '10' minute) where deptno=20

출처 : http://www.oracleclub.com/article/13624

CREATE OR REPLACE PACKAGE ORHOME.PC_LOG_PROCESSING AS
 PROCEDURE LOG_TRIGGER (LOG_ERROR VARCHAR2);
END PC_LOG_PROCESSING;

CREATE OR REPLACE PACKAGE BODY ORHOME.PC_LOG_PROCESSING AS -- BODY
 PROCEDURE LOG_TRIGGER (LOG_ERROR VARCHAR2) IS
  FILE_HANDLE UTL_FILE.FILE_TYPE;
  BEGIN
   FILE_HANDLE := UTL_FILE.FOPEN('/log','procedure_log','a');
   UTL_FILE.PUT_LINE(FILE_HANDLE,LOG_ERROR);
   UTL_FILE.FCLOSE(FILE_HANDLE);
  EXCEPTION
   WHEN UTL_FILE.INVALID_MODE THEN
   NULL;
   RAISE_APPLICATION_ERROR(-20322,'file invalid mode');
  WHEN UTL_FILE.INVALID_FILEHANDLE THEN
   NULL;
   RAISE_APPLICATION_ERROR(-20322,'file invalid filehandle');
   WHEN OTHERS THEN
   NULL;
 END LOG_TRIGGER;
END PC_LOG_PROCESSING;