부릉부릉~

[출처] 모르는것은 죄입니다. by 일각수 http://eunicon.egloos.com/1211811

$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jan 11 11:28:42 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
ERROR:
ORA-09817: Write to audit file failed.
SVR4 Error: 28: No space left on device
ORA-09945: Unable to initialize the audit trail file
SVR4 Error: 28: No space left on device

Enter user-name:


오라클에 접속하려하니 이런 황당한 경우가...
어제까지 잘 쓰고 있었는데 말이쥐...

그래서 믿을건 지식iN이다...해서 찾아보니,
bdump, cdump, udump 디렉토리에 파일이 가득찼다네.... <-- 요놈이 ora-09817에러 주범
그래서 이놈들이 모하는 놈들인지 알아보았다.
bdump : background process trace files
cdump : core dump files
udump : user SQL trace files

그래서 각 디렉토리의 트레이스 파일들을 몽땅 백업받고 지워버렸쥐..
참..bdump 디렉토리 내에 있는 alert_ora10.log 파일은 절대 삭제하면 안돼..!!!
왜냐하면 alert_ora10.log파일은 오라클의 현상태와 과거상태를 나타내는 대표적인 로그파일이기 때문이야.
이는 시스템 관리자나 DBA는 항시 살펴봐야 할 부분이야.
대부분의 alert_ora10.log파일은 background_dump_dest 파라미터에 지정된 위치에 있어.

Alert.log에 기록되는 정보는 다음과 같아.
1. DBMS open/shutdown 정보
2. 변경된 DBMS 이니셜 파라미터와 현재 지정값
3. 시스템 작업 처리내역(테이블스페이스, 롤백 ,생성, 삭제, 변경)
4. 유저의 치명적인 에러
5. 백그라운드 프로세서의 에러

데이터베이스 이상현상이나 에러의 내용은 이 Alert.log에 기록되니까 문제발생후 원인 분석시에는 꼭 이 파일을 참조해 봐야해.

추가적으로, 사용자 프로그램의 에러는 Alert.log에 기록되지 않아.
이런 정보들은 user_dump_dest파라미터에 지정된 디렉토리에 ora_xxxx.trc라고 기록되지.
그러니까 에러 발생시 alert.log와 .trc파일을 모두 확인해 볼 필요가 있어.

MERGE INTO table_name alias                       --테이블 명만 올수 있음(셀렉트 쿼리 No)
          USING (table|view|subquery) alias        --일반적으로 쓰는 쿼리 Ok
          ON (join condition)                              --where 절이라고 생각하면됨(조건에 서브 OK)
          WHEN MATCHED THEN                         --데이터 중복건이 있는경우 Update
                       UPDATE SET col1 = val1[, col2 = val2…]
          WHEN NOT MATCHED THEN                  -- 중복건이 없는 경우 처리 Insert
                       INSERT (컬럼리스트) VALUES (값들....);

Ex 1.
 MERGE INTO TABLE_NAME1 B
 USING (SELECT B.AA, A.*  FROM TABLE_NAME2 A, TABLE_NAME3 B
        WHERE A.BB = B.BB(+)) A
 ON (B.CC= A.CC AND B.AC IS NULL AND DD > SYSDATE - 24)
 WHEN MATCHED THEN
 UPDATE SET A = A.A,
                    B = A.B;
Ex 2.
 MERGE INTO CRCD_WF_CARD A             
 USING DUAL                            
    ON ( A.WF_NO = ? AND A.CRD_NO =? ) 
  WHEN MATCHED    THEN                 
       UPDATE  SET                     
               A.CRD_TYPE       =?     
             , A.FML_SSN        =?     
             , A.FMLHG_NM       =?     
             , A.FMLENG_NM      =?     
    WHEN NOT MATCHED THEN               
         INSERT (                       
                 A.WF_NO                
               , A.CRD_NO               
               , A.CRD_TYPE             
               , A.FML_SSN              
               , A.FMLHG_NM              
               , A.FMLENG_NM             
       ) VALUES ( ?, ?, ?, ?, ?, ?, ?,  
                  ?, ?, ?, ?, ?, ?, ?  )

Ex 3.
  MERGE INTO TC_CUSTAGENT A
  USING (SELECT BUSI_CODE, CUST_CODE, CHPE_NAME, CUST_PHNB, CUST_FANU
                FROM TB_ESTMAST
                WHERE BUSI_CODE = '___parameter___'
                    AND CUST_CODE = '____parameter___') B
    ON (A.BUSI_CODE = B.BUSI_CODE
           AND A.AFON_COMP = B.CUST_CODE
           AND A.SEQU_NUMB = (SELECT MAX(SEQU_NUMB)
                                            FROM TC_CUSTAGENT
                                            WHERE BUSI_CODE = A.BUSI_CODE
                                                  AND AFON_COMP = A.AFON_COMP))
   WHEN MATCHED THEN
         UPDATE
         SET  A.CHPE_NAME = B.CHPE_NAME
                ,A.CABL_PHNB = B.CUST_PHNB
                ,A.FAXM_NUMB = B.CUST_FANU
    WHEN NOT MATCHED THEN
         INSERT (  A.AFON_COMP
                       ,A.CHPE_NAME
                       ,A.CABL_PHNB
                       ,A.FAXM_NUMB     
                     )
         VALUES (  B.CUST_CODE
                        ,B.CHPE_NAME      
                        ,B.CUST_PHNB
                        ,B.CUST_FANU    
                      );

[출처] 맘편한넘님 블로그 http://blog.daum.net/idrlee/16099938

merge 문은 구조가 같은 두 개의 테이블을 비교하여 하나의 테이블로 합치기 위한 데이터 조작이다.
예를 들어, 하루에 수만건씩 발생하는 데이터를 하나의 테이블에 관리할 경우 대량의 데이터로 인해 질의문의 성능이 저하된다.
이런 경우, 지점별로 별도의 테이블에서 관리하다가 년말에 종합 분석을 위해 하나의 테이블로 합칠 때 merge 문을 사용하면 편리하다.

merge하고자 하는 소스 테이블의 행을 읽어 타킷 테이블에 매치되는 행이 존재하면 새로운 값으로 UPDATE를 수행하고, 만일 매치되는 행이 없을 경우 새로운 행을 타킷 테이블에서 INSERT를 수행한다.

Merge는 여러 데이터 소스로부터 다양한 데이터를 수집해서 작업을 수행하는 dataware housing등에 적용하면 유용하다.

merge 문에서 where 절은 사용할 수 없으며 대신 on이 사용된다. 또한 when matched then 절과 when not matched then 절에는 테이블명 대신 alias를 사용한다.

【형식】
    MERGE [hint] INTO [schema.] {table ¦ view} [t_alias]
      USING {{[schema.] {table ¦ view}} ¦
            subquery} [t_alias]
      ON (condition) [merge_update_clause] [merge_insert_clause] [error_logging_clause];

【merge_update_clause 형식】
   WHEN MATCHED THEN UPDATE SET {column = {expr ¦ DEFAULT},...}
     [where_clause] [DELETE where_clause]

【merge_insert_clause 형식】
   WHEN MATCHED THEN INSERT [(column,...)]
    VALUES ({expr,... ¦ DEFAULT}) [where_clause]
   
【where_clause 형식】
   WHERE condition

【error_logging_clause 형식】
   LOG ERROR [INTO [schema.] table] [(simple_expression!)]
     [REJECT LIMIT {integer ¦ UNLIMITED}]

[출처] http://basicjava.mireene.com/trackback/15

테이블스페이스 정보보기

테이블 스페이스의 데이터 파일과 테이블 스페이스의 크기 확인

DBA_DATA_FILES 데이터 사전을 이용 하면 됩니다.

SQL>
COL FILE_NAME FORMAT A40
COL TABLESPACE_NAME FORMAT A15

SELECT file_name, tablespace_name, bytes, status FROM DBA_DATA_FILES;

FILE_NAME T ABLESPACE_NAME BYTES STATUS
------------------------------------- --------------- ------------ ------------
C:ORACLEORADATAORACLESYSTEM01.DBF SYSTEM 248250368 AVAILABLE
C:ORACLEORADATAORACLERBS01.DBF RBS 545259520 AVAILABLE
C:ORACLEORADATAORACLEUSERS01.DBF USERS 113246208 AVAILABLE
C:ORACLEORADATAORACLETEMP01.DBF TEMP 75497472 AVAILABLE
C:ORACLEORADATAORACLETOOLS01.DBF TOOLS 12582912 AVAILABLE
C:ORACLEORADATAORACLEINDX01.DBF INDX 60817408 AVAILABLE
C:ORACLEORADATAORACLEDR01.DBF DRSYS 92274688 AVAILABLE

◎ FILE_NAME : DATAFILE의 물리적인 위치와 파일명을 알 수 있습니다.
◎ TABLESPACE_NAME : 테이블 스페이스의 이름을 알 수 있습니다.
◎ BYTES : 테이블 스페이스의 크기를 알수 있습니다.
◎ STATUS : 테이블 스페이스의 이용 가능 여부를 알 수 있습니다.



테이블 스페이스별 사용 가능한 공간의 확인

DBA_FREE_SPACE 데이터 사전


SQL> SELECT tablespace_name, SUM(bytes), MAX(bytes)
FROM DBA_FREE_SPACE
GROUP BY tablespace_name


TABLESPACE_NAME SUM(BYTES) MAX(BYTES)
--------------- ---------- ----------
DRSYS 88268800 88268800
INDX 60809216 60809216
RBS 524279808 498589696
SYSTEM 65536 65536
TEMP 75489280 74244096
TOOLS 12574720 12574720
USERS 113238016 113238016


◎ SUM을 사용한 이유는하나의 테이블 스페이스에 분산되어 있는 여유공간을 합한 것이며,
◎ MAX를 사용한 이유는 여유 공간중 가장 큰 공간의 SIZE를 의미 합니다.




데이타 화일에 대한 총 크기와 남아있는 공간, 사용한 용량, 남은 %율

DBA_FREE_SPACE, DBA_DATA_FILES 데이터 사전

SQL>
COL FILE_NAME FORMAT A40
COL TABLESPACE_NAME FORMAT A30
SET LINESIZE 150
SELECT b.file_name "FILE_NAME", -- DataFile Name
b.tablespace_name "TABLESPACE_NAME", -- TableSpace Name
b.bytes / 1024 "TOTAL SIZE(KB)", -- 총 Bytes
((b.bytes - sum(nvl(a.bytes,0)))) / 1024 "USED(KB)", -- 사용한 용량
(sum(nvl(a.bytes,0))) / 1024 "FREE SIZE(KB)", -- 남은 용량
(sum(nvl(a.bytes,0)) / (b.bytes)) * 100 "FREE %" -- 남은 %
FROM DBA_FREE_SPACE a, DBA_DATA_FILES b
WHERE a.file_id(+) = b.file_id
GROUP BY b.tablespace_name, b.file_name, b.bytes
ORDER BY b.tablespace_name


FILE_NAME TABLESPACE_NAME TOTAL SIZE(KB) USED(KB) FREE SIZE(KB) FREE %
------------------------------------- --------------- -------------- ------------- ------------- ----------
C:ORACLEORADATAORACLEDR01.DBF DRSYS 90112 3912 86200 95.6587358
C:ORACLEORADATAORACLEINDX01.DBF INDX 59392 8 59384 99.9865302
C:ORACLEORADATAORACLERBS01.DBF RBS 532480 20488 511992 96.1523438
C:ORACLEORADATAORACLETEMP01.DBF TEMP 73728 8 73720 99.9891493
C:ORACLEORADATAORACLETOOLS01.DBF TOOLS 12288 8 12280 99.9348958
C:ORACLEORADATAORACLEUSERS01.DBF USERS 110592 8 110584 99.9927662

[* 참조 *]
 
- 자주 사용되는 파라미터
DATA_ONLY - 이미 존재하는 테이블에 대해서만 데이터를 load 시킨다. 없던 객체를 생성 시키지 않는다.
METADATA_ONLY - DATA 없이 객체만 받는다.  EXPORT 에서 ROWS=N 과 같은 파라미터이다.

 
- DIRECTORY 확인 방법
 : 기존에 생성되어 있는 DIRECTORY NAME 의 실제 DIRECTORY 를 확인하는 방법
 
SQL> SELECT * FROM DBA_DIRECTORIES;

OWNER                DIRECTORY_NAME                 DIRECTORY_PATH
-------------------- ------------------------------ ------------------------------------------
SYS                          PUMP_TEST                         /PUMP_TEST/
 

 
[TEST] /PUMP_TEST/>  expdp help=y

Export: Release 10.2.0.3.0 - 64bit Production on Wednesday, 11 March, 2009 16:26:18

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

   Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Export runs by entering the 'expdp' command followed
by various parameters. To specify parameters, you use keywords:

   Format:  expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
   Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword               Description (Default)
------------------------------------------------------------------------------
ATTACH                Attach to existing job, e.g. ATTACH [=job name].
COMPRESSION           Reduce size of dumpfile contents where valid
                      keyword values are: (METADATA_ONLY) and NONE.
CONTENT               Specifies data to unload where the valid keywords are:
                      (ALL), DATA_ONLY, and METADATA_ONLY.
DIRECTORY             Directory object to be used for dumpfiles and logfiles.
DUMPFILE              List of destination dump files (expdat.dmp),
                      e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ENCRYPTION_PASSWORD   Password key for creating encrypted column data.
ESTIMATE              Calculate job estimates where the valid keywords are:
                      (BLOCKS) and STATISTICS.
ESTIMATE_ONLY         Calculate job estimates without performing the export.
EXCLUDE               Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FILESIZE              Specify the size of each dumpfile in units of bytes.
FLASHBACK_SCN         SCN used to set session snapshot back to.
FLASHBACK_TIME        Time used to get the SCN closest to the specified time.
FULL                  Export entire database (N).
HELP                  Display Help messages (N).
INCLUDE               Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME              Name of export job to create.
LOGFILE               Log file name (export.log).
NETWORK_LINK          Name of remote database link to the source system.
NOLOGFILE             Do not write logfile (N).
PARALLEL              Change the number of active workers for current job.
PARFILE               Specify parameter file.
QUERY                 Predicate clause used to export a subset of a table.
SAMPLE                Percentage of data to be exported;
SCHEMAS               List of schemas to export (login schema).
STATUS                Frequency (secs) job status is to be monitored where
                      the default (0) will show new status when available.
TABLES                Identifies a list of tables to export - one schema only.
TABLESPACES           Identifies a list of tablespaces to export.
TRANSPORT_FULL_CHECK  Verify storage segments of all tables (N).
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be unloaded.
VERSION               Version of objects to export where valid keywords are:
                      (COMPATIBLE), LATEST, or any valid database version.

The following commands are valid while in interactive mode.
Note: abbreviations are allowed

Command               Description
------------------------------------------------------------------------------
ADD_FILE              Add dumpfile to dumpfile set.
CONTINUE_CLIENT       Return to logging mode. Job will be re-started if idle.
EXIT_CLIENT           Quit client session and leave job running.
FILESIZE              Default filesize (bytes) for subsequent ADD_FILE commands.
HELP                  Summarize interactive commands.
KILL_JOB              Detach and delete job.
PARALLEL              Change the number of active workers for current job.
                      PARALLEL=<number of workers>.
START_JOB             Start/resume current job.
STATUS                Frequency (secs) job status is to be monitored where
                      the default (0) will show new status when available.
                      STATUS[=interval]
STOP_JOB              Orderly shutdown of job execution and exits the client.
                      STOP_JOB=IMMEDIATE performs an immediate shutdown of the
                      Data Pump job.

 

[ 3. IMPDP ]
expdp 받은 TABLE 을 impdp 를 진행한다.
조건은 아래와 같다.
RCHWIN 이라는 유저의 특정 TABLE 인 PUMP_TEST  와 그에따른  INDEX 를 RCHWIN_2  의 유저에 이관한다.
단 TABLESPACE 는 TABLE 과 INDEX  모두 TS_TABLE 과 TS_INDEX 에서  TS_PUMP  로 바뀐다. 
 

[ 4. IMPORT 확인 ]
RCHWIN 계정의 TABLE 및 INDEX 가 RCHWIN_2 로 정상적으로 이관이 되었는지 확인해보고
TABLESPACE 또한 지정한것으로 바뀌었는지 확인해보자.
 
SQL> select owner, segment_name, segment_type, tablespace_name, bytes/1024/1024 from dba_segments
where segment_name='PUMP_TEST';
OWNER       SEGMENT_NAME      SEGMENT_TYPE       TABLESPACE_NAME        BYTES/1024/1024
------------------------------ ---------------------------------------------------------------------------------------
RCHWIN             PUMP_TEST         TABLE              TS_TABLE                                608
RCHWIN_2         PUMP_TEST          TABLE               TS_PUMP                               656
2 rows selected.

 

SQL> select owner, index_name,  tablespace_name from dba_indexes
where table_name='PUMP_TEST';
OWNER                          INDEX_NAME                     TABLESPACE_NAME              
------------------------------ ------------------------------ ------------------------------
RCHWIN                         PUMP_TEST_2IX     TS_INDEX                     
RCHWIN                         PUMP_TEST_PK      TS_INDEX                     
RCHWIN                         PUMP_TEST_1IX     TS_INDEX                     
RCHWIN_2                     PUMP_TEST_2IX     TS_PUMP                    
RCHWIN_2                     PUMP_TEST_PK      TS_PUMP                     
RCHWIN_2                     PUMP_TEST_1IX     TS_PUMP             
 

 

1. command line 에서 expdp 사용
    결과물을 저장할 디렉토리를 지정한다.
        SQL> create directory data_pump_test as '/home/oracle/data_pump_test/';

    생성한 디렉토리 오브젝트에 대한 권한을 유저에게 부여한다.
        SQL> grant read, write on directory data_pump_test to hr;

    사용자에게는 EXP_FULL_DATABASE, IMP_FULL_DATABASE 롤을 부여한다.
    실제로는 resouce 롤만으로도 기본적인 expdp, impdp 동작을 수행할 수 있다.
   
    expdp 실행
    # expdp hr/hr directory=data_pump_test dumpfile=test_dump.dmp

    - interactive command line
        expdp 진행 중에 컨트롤+C 키를 누르면 Export> 프롬프트가 뜬다.
        여기서 help를 타이핑하면 사용할 수 있는 명령들을 확인할 수 있다.

    data_pump_test 디렉토리에 생성된 export.log 파일의 내용을 확인한다.

    ※ {exclude|include} object_type[: "name_expr"]
            ex)
            - # expdp hr/hr directory=data_pump_test dumpfile=test_dump.dmp exclude=index:"like 'EMP%'"
            - # expdp hr/hr directory=data_pump_test dumpfile=test_dump.dmp include=view
        exclude는 해당 오브젝트를 제외하고 추출/입력
        include는 해당 오브젝트만을 추출/입력
       
        contents={all | metadata_only | data_only}

2. 병렬 처리
    parallel 파라메터는 몇 개의 스레드로 병렬 처리 할 것 인지 지정한다.
        # expdp hr/hr tables=paratest directory=data_pump_test dumpfile=paratest%U.dmp parallel=3 job_name=parr_test;

    paratest01.dmp, paratest02.dmp, paratest03.dmp 세 개의 파일이 생성된다.   

        # impdp hr/hr directory=data_pump_test dumpfile=paratest%U.dmp job_name=parr_test;

실행 예

SQL> create user dpumpuser identified by dpumpuser;
SQL> grant connect, resource, exp_full_database, imp_full_database to dpumpuser;
SQL> create directory data_pump as '/home/oracle/data_pump';
SQL >grant read, write on directory data_pump to dpumpuser;

$ expdp dpumpuser/dpumpuser full=y directory=data_pump dumpfile=20081030.dmp
$ impdp dpumpuser/dpumpuser directory=data_pump dumpfile=20081030.dmp

SQL> revoke read, write on directory data_pump from dpumpuser;
SQL> revoke connect, resource, exp_full_database, imp_full_database from dpumpuser;
SQL> drop directory data_pump;
SQL> drop user dpumpuser;

※ 만약 impdp 가 퍼미션 문제로 실행되지 않는다면 SYSDBA 로 로그인한 후,
SQL> execute dbms_metadata_util.load_stylesheets;
을 실행한 후 재시도.

특정 스키마의 구조와 데이터 추출
$expdp dpumpuser/dpumpuser schema=PORTAL directory=data_pump dumpfile=portal_schema_20081031.dmp

content를 사용해 특정 스키마의 데이터만 추출 (all | metadata_only | data_only)
$expdp dpumpuser/dpumpuser schemas=PORTAL content=data_only directory=data_pump dumpfile=portal_schema_data_20081031.dmp

table_exists_action 옵션 사용, 기존의 데이터를 truncate 하도록 (skip | append | truncate | replace)
    skip - 존재하는 오브젝트에 대해 임포트 스킵
    append - 기존 오브젝트에서 업는 행만을 임포트
    truncate - 기존 테이블 truncate
    replace - drop & recreate
$ impdp dpumpuser/dpumpuser table_exists_action=truncate directory=data_pump dumpfile=portal_schema_data_20081031.dmp

데이터 파일, 테이블 스페이스, 유저는 각각 다음의 옵션으로 변경할 수 있다.
REMAP_DATAFILE=’C:\user01.dbf’:’/usr/data/user01.dbf’
REMAP_TABLESPACE=’users’:’user’
REMAP_SCHEMA=scott:stralth

다음 옵션으로 expdp에 사용되는 공간을 추정할 수 있다.
estimate_only=Y
$ expdp dpumpuser/dpumpuser full=y estimate_only=Y

/***********************************************************/

expdp 사용자/암호@SID  directory=디렉토리명 dumpfile=파일명  EXCLUDE=TABLE:\"IN \(\'테이블명1\', \'테이블명2\'\)\"

expdp testi/test@SIDTEST directory=dmp dumpfile=expdp_test.dmp EXCLUDE=TABLE:\"IN \(\'EMP1\', \'EMP2\'\)\"

여기서의 관건은 EXCLUDE 다음에 나오는 작은따옴표 앞에 역슬래시가 있어야한다는 사실이다!!!

이거 않넣고 하면 주구 장창

Value for EXCLUDE is badly formed. 이란 메시지를 징글징글하게 볼수 있을것이다.