웹 개발과 관련된 모든 것들

레코드가 있으면 UPDATE하고 없으면 INSERT해라 였는데,
오라클9i에서 새로 추가된 MERGE INTO 구문을 사용하면

대략 구문은 아래와 같습니다.
pl/sql환경에서는 위와 같이 사용해도 되지만,
sql을 꼭 사용해야한다면 이 방법이 낫겠네요.

[SYNTAX]

MERGE INTO 테이블 A                        -- update 또는 insert할 테이블
USING (테이블명 | SUBQUERY | 뷰)  B  -- 비교 대상
ON(A와 B의 조인식)                             -- 대상테이블과 비교테이블의 조인식
WHEN MATCHED THEN                       -- on절에서 일치하는 로우가 있다면
    UPDATE SET = , , ....                       -- 업데이트하고,
WHEN NOT MATCHED THEN                -- 일치하는 로우가 없다면
    INSERT INTO(컬럼명)                       -- insert를
    VALUES(값, 값, ...)                           -- 이 값으로 넣어라.

f_u1:
   merge into tb_result a
        using dual             b
           on ( a.plt_no = '$in_plt_no' and
                a.srt_id = '$in_srt_id' )
   when matched then
      update set srt_qty     = srt_qty + to_number('$in_srt_qty'),
                 fin_chgr_id = '$in_user_id',
                 fin_chg_dtm = to_char(sysdate, 'yyyymmddhh24miss')
   when not matched then
      insert ( plt_no       , srt_id            , proj_no         , blk_no            ,
               sass_no      , prcd_sass_no      , ptlst_no        , pcs_gb            ,
               stype        , srt_qty           , uom_wgt         , bom_org_cmpo      ,
               io_gb        , req_gb            , ptlst_stat      , req_ser           ,
               lug_gb       , mpart_plt_no      , newprod_qty     , reprod_qty        ,
               imprv_yn     , plt_incd_yn       , fs_srt_usr_id   , fs_srt_dtm        ,
               fs_inpr_id )
      values ( '$in_plt_no' , '$in_srt_id'      , '$in_proj_no'   , '$in_blk_no'      ,
               '$in_sass_no', '$in_prcd_sass_no', '$in_ptlst_no'  , '$in_pcs_gb'      ,
               '$in_stype'  , to_number('$in_srt_qty'), to_number('$in_uom_wgt'), '$in_bom_org_cmpo',
               '$in_io_gb'  , '$in_req_gb'      , 'C'             , to_number('$in_req_ser'),
               '$in_lug_gb' , '$in_mpart_plt_no', to_number('$in_new_srt_qty'), to_number('$in_re_srt_qty'),
               'N'          , 'Y'               , '$in_user_id'   , to_char(sysdate, 'yyyymmddhh24miss'),
               '$in_user_id' )
   ;

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

f_u2:
   declare cursor csr1 is
      select a.lug_gb       as lug_gb  ,
             a.ptlst_no     as lug_type,
             '$in_bizcls'   as bizcls  ,
             sum(a.srt_qty) as srt_qty
      from   tb_result a
      where  a.plt_no = '$in_plt_no'
      group  by a.lug_gb, a.ptlst_no
      $in_semicolon

      begin
         for rec1 in csr1 loop
            update tb_result
            set    stk_qty     = stk_qty - rec1.srt_qty * decode('$in_out_gb', 'O', 1, -1),
                   fin_chgr_id = '$in_user_id',
                   fin_chg_dtm = to_char(sysdate, 'yyyymmddhh24miss')
            where  lug_gb      = rec1.lug_gb
            and    lug_type    = rec1.lug_type
            and    bizcls      = rec1.bizcls
            $in_semicolon

            if sql%notfound then
               insert into tb_stk
                         ( lug_gb    ,
                           lug_type  ,
                           bizcls    ,
                           stk_qty   ,
                           fs_inpr_id    )
                  values ( rec1.lug_gb  ,
                           rec1.lug_type,
                           rec1.bizcls  ,
                           rec1.srt_qty * decode('$in_out_gb', 'O', -1, 1),
                           '$in_user_id' )
               $in_semicolon
            end if $in_semicolon
         end loop $in_semicolon
      end $in_semicolon
   ;

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

테이터를 넣어야할 테이블에

값이 이미 있으면 Update

값이 없으면 Insert

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)

   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      =?    
               , A.OCRD_NO        =?    
               , A.DSN_CD         =?    
               , A.DSN_NM         =?    
               , A.BRD_CD         =?    
               , A.CRD_GRD        =?    
               , A.INTGLMT_AMT    =?    
               , A.FML_STLACCT_NO =?    
               , A.CRD_ISSUE_DT   =?    
    WHEN NOT MATCHED THEN               
         INSERT (                       
                 A.WF_NO                
               , A.CRD_NO               
               , A.CRD_TYPE             
               , A.FML_SSN              
               , A.FMLHG_NM             
               , A.FMLENG_NM            
               , A.OCRD_NO              
               , A.DSN_CD               
               , A.DSN_NM               
               , A.BRD_CD               
               , A.CRD_GRD              
               , A.INTGLMT_AMT          
               , A.FML_STLACCT_NO       
               , A.CRD_ISSUE_DT         
                                        
       ) VALUES ( ?, ?, ?, ?, ?, ?, ?,  
                  ?, ?, ?, ?, ?, ?, ?  )

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

ex2)

    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    
                      );

>>>>>>>>>>>>>>>>
 
 MERGE INTO
실제 적용하면서 발견한 문제점 (ORACLE 9.2.0.3.0 에서)

9.2.0.4.0 버젼에선 아래와 같은 문법으로도 MERGE INTO 가 정상적으로 동작하였다.

MERGE INTO POPULAR_KEYWORD
USING DUAL B
ON (FIX_YN = 'Y' AND CATEGORY = 'ALL' AND RANK = '1')
WHEN MATCHED THEN
  UPDATE SET REG_DATE = SYSDATE
WHEN NOT MATCHED THEN
  INSERT (CATEGORY,KEYWORD,RANK, COUNT, REG_DATE)
      VALUES ('ALL','TEST22','1','100',SYSDATE)

즉, ON 절에서 값이 NULL 이더라도 WHEN NOT MATCHED THEN 절을 정상적으로 수행하였는데

이상하게 다른서버(9.2.0.3.0) 에선 ON절의 값이 NULL 이면 정상적인 루틴을 밟지 못하드라..

즉, INSERT 문을 수행하지 못했다.

[ 0 rows Merged ]

그래서 아래와 같이 USING절에서 NULL값이 리턴하지 않게 변경해주고

ON 절에서 A 와 B 를 JOIN 해주니까 정상적으로 동작한다.

MERGE INTO POPULAR_KEYWORD2 A
      USING (SELECT 'ALL' CATEGORY, '4' RANK, 'Y' FIX_YN FROM   DUAL) B
      ON (A.CATEGORY = B.CATEGORY AND A.RANK = B.RANK AND A.FIX_YN = B.FIX_YN)
      WHEN MATCHED THEN
        UPDATE SET REG_DATE = SYSDATE
      WHEN NOT MATCHED THEN
        INSERT (CATEGORY,KEYWORD,RANK, COUNT, REG_DATE)
            VALUES ('ALL','TEST22','4','100',SYSDATE)

[ 1 rows Merged ]

결론 ==> USING 다음의 SELECT 문의 결과 데이타는 반드시 존재하여야 한다 (NULL 불가).

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

9i이상에서는 데이타가 존재할 경우 갱신 없으면 등록하는 명령어로

MERGE INTO ~ USING ~ ON ~ WHEN ~ 명령어가 있습니다.

MERGE INTO oc_whslr_trd_rslt A
USING (
SELECT std_yymm, cust_nbr, whlsr_cust_nbr, sol_tp_cd, sol_cd,chnn_rt,

src_tp_cd,org_cd, emp_nbr,sell_amt, qty
FROM oc_temp_whslr_trd_rslt
) B
ON (

A.std_yymm = B.std_yymm AND

A.cust_nbr = B.cust_nbr AND

A.whlsr_cust_nbr = B.whlsr_cust_nbr AND

A.sol_cd = B.sol_cd AND

A.sol_tp_cd = B.sol_tp_cd AND

A.chnn_rt = B.chnn_rt
)
WHEN MATCHED THEN

UPDATE SET A.sell_amt = A.sell_amt + B.sell_amt,
A.qty = A.qty + B.qty
WHEN NOT MATCHED THEN
INSERT (
A.std_yymm,
A.cust_nbr,
A.whlsr_cust_nbr,
A.sol_cd,
A.sol_tp_cd,
A.chnn_rt,
A.src_tp_cd,
A.org_cd,
A.emp_nbr,
A.sell_amt,
A.qty,
A.drtn_amt,
A.drtn_qty,
A.last_chng_emp,
A.last_chng_dt )
VALUES (
B.std_yymm,
B.cust_nbr,
B.whlsr_cust_nbr,
B.sol_cd,
B.sol_tp_cd,
B.chnn_rt,
B.src_tp_cd,
B.org_cd,
B.emp_nbr,
B.sell_amt,
B.qty,
0,
0,
'CRMSP',
TO_CHAR(sysdate,'YYYYMMDD')
);

MERGE into KAC_PERSON a USING
(select user_id, OPER_FG, VARDATA, NUMDATA from KAC_PERSON
where user_id='57CCF7ADE63F3AC1' AND OPER_FG = 'MAIL_CAPACITY') b
ON (a.user_id=b.user_id and a.OPER_FG=b.OPER_FG)
WHEN MATCHED THEN UPDATE set a.NUMDATA = 15
WHEN NOT MATCHED THEN INSERT (a.user_id,a.OPER_FG,a.VARDATA,a.NUMDATA)
VALUES ('57CCF7ADE63F3AC1','MAIL_CAPACITY','mail',50)

CREATE TABLE KAC_PERSON
(
  USER_ID    CHAR(16) NOT NULL,
  OPER_FG    VARCHAR2(20) NOT NULL,
  VARDATA    VARCHAR2(30),
  NUMDATA    NUMBER(4),
  CONSTRAINT PK_KAC_PERSON PRIMARY KEY (USER_ID, OPER_FG)
);

>>>>>>>>>>>>>>>>>>>>>>>>>

MERGE INTO tb_target a
USING (
   select proj_no, spl_no, tag_no, msch_actv, instl_plt_no, wk_ord_dt
   from   tb_source
   where  lot_no  = 'AJ'
   and    proj_no = '1697'
   ) b
ON (   a.proj_no = b.proj_no
   and a.spl_no  = b.spl_no
   and a.tag_no  = b.tag_no)
WHEN MATCHED THEN
   update
   set    a.mk_fndt   = b.wk_ord_dt,
          a.matdlv_dt = b.wk_ord_dt,
          a.gr_dt     = b.wk_ord_dt,
          a.stk_dt    = b.wk_ord_dt,
          a.gi_dt     = b.wk_ord_dt
WHEN NOT MATCHED THEN
   insert(a.proj_no, a.spl_no, a.tag_no, a.msch_actv, a.instl_plt_no,
          a.mk_fndt, a.matdlv_dt, a.gr_dt, a.stk_dt, a.gi_dt)
   values(b.proj_no, b.spl_no, b.tag_no, b.msch_actv, b.instl_plt_no,
          b.wk_ord_dt,b.wk_ord_dt,b.wk_ord_dt,b.wk_ord_dt,b.wk_ord_dt)

mom이라는 사용자의

A, B, C 라는 테이블을 사용하기 위해

다른 사용자들은 mom.A, mom.B 등으로 사용하는데

A, B 로 바로 쓸수 있게 하는

synonym을 일괄 생성하는 방법

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

sqlplus 로 접속해서

 spool make_synonyms.sql

SELECT 'create public synonym ' || table_name || ' for ' || table_name || ';'
  FROM user_tables;

spool off

@make_synonyms

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

다른 방법은 current schema를 쓰는 방법

alter session set current_schema=scott;

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

USER_B에게 USER_A.sample의 select 권한 부여

GRANT SELECT ON USER_A.sample_table TO USER_B;

테이블 스페이스 생성

SQL> CREATE TABLESPACE XINET_DATA
    DATAFILE /경로/xinet_01.dbf'
    SIZE 100M
    AUTOEXTEND ON
    NEXT 30M
    SEGMENT SPACE MANAGEMENT AUTO;

SEGMENT SPACE MANAGEMENT AUTO 설정을 해야 SHRINK를 나중에 사용 가능 ~~

 

테이블 스페이스 변경

ALTER USER XINET DEFAULT TABLESPACE XINET_DATA;

사용 테이블 스페이스 조회

SELECT username, default_tablespace, temporary_tablespace FROM DBA_USERS;

### oracle

sqlplus -SILENT 아이디/패스워드 <<-EOF
 WHENEVER SQLERROR EXIT 1
  WHENEVER OSERROR EXIT 1
   SET ECHO ON
   EXECUTE 호출패키지명();

   EXIT 0
   EOF

   EXITCODE=$?
   export EXITCODE

   if [ "$EXITCODE" -ne 0 ]
   then
     echo "ERROR: SQL*Plus exit code: $EXITCODE"
     echo ""
     echo "**** PROCEDURE RUN ABORTED at `date` !!! ****"
   fi
### end

클론 등록 방법 (클론은 root로 등록)

매일 아침 6시에 실행한다면

0 6 * * * su - oracle -c /home/test.sh

주석문을 볼수있는 data dictionary

dictionary 상에 정보 조회를 통해 현재 comment(주석)이 등록 되어 있는지 확인 가능하다.

서버 프로세스

** 서버 프로세스 상태

SELECT sid, serial#, username, status "Status of Session",
       server "ServerType", osuser, machine, program, process
FROM   v$session;

** PGA 상태(전체 세션)

SELECT MEM.value "PGA", DISK.value "Temporary 테이블스페이스",
       (disk.value/mem.value) * 100 "Ratio"
FROM   v$sysstat MEM, v$sysstat DISK
WHERE  MEM.name = 'sorts (memory)'
   AND DISK.name = 'sorts (disk)';

**  PGA 상태 (세션 별)

SELECT  A.process, A.username, B.block_gets, B.consistent_gets
        , B.physical_reads
FROM    v$sess_io B, v$session A
WHERE   B.sid=A.sid

**  UGA 상태 (Shared_Pool)

SELECT SUM(value) "Total Session Memory"
FROM   v$mystat A, v$statname B
WHERE  name = 'session uga memory'
   AND A.statistic# = B.statistic#;

데이터 버퍼 캐쉬

** 데이터 버퍼 캐쉬 구조

SELECT id, name, block_size, buffers
FROM   v$buffer_pool;

**  히트율

SELECT phy.value "Physical Read",cur.value+con.value
   "Buffer Cache Read",
   (1-((phy.value) / (cur.value+con.value)))*100 "히트율"
FROM   v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE  cur.name = 'db block gets'
   and con.name = 'consistent gets'
   and phy.name = 'physical reads';

** 대기상태

SELECT (SELECT total_waits FROM   v$system_event WHERE  event = 'free buffer waits') "Free Buffer Waits",
       (SELECT total_waits FROM   v$system_event WHERE  event = 'buffer busy waits') "Buffer Busy Waits"
FROM dual;

** 메모리 사용상태(세션 별)

SELECT A.process, A.username, B.block_gets, B.consistent_gets, B.physical_reads
FROM   v$sess_io B, v$session A
WHERE  B.sid = A.sid;

로그 버퍼 (히트율)

SELECT S1.value "Redo Alloc Retries",
       S2.value "Redo Entries",
       S1.value / S2.value * 100 "히트율"
FROM   v$sysstat S1, v$sysstat S2
WHERE  S1.name = 'redo buffer alLocation retries'
   AND S2.name = 'redo entries';

공유 풀 영역 (Shared Pool Area)

** Reload 상태

SELECT Gets, Gethits, Gethitratio*100 "히트율"
FROM   v$Librarycache
WHERE  namespace = 'SQL AREA';

** 히트율

SELECT SUM(pins) "Executions(PINS)",
       SUM(reloads) "Cache Misses(RELOADS) " ,
       SUM(reloads) / SUM(pins)*100 "Reload율"
FROM   v$Librarycache;

** Reserved Pool(응답상태)

SELECT requests, request_misses, request_failures, free_space,
       avg_free_size, max_free_size, used_space,avg_used_size
FROM   v$shared_pool_reserved;

** 파싱된 SQL문의 상태

SELECT cpu_time, elapsed_time,executions, loads, invalidations, version_count,
       parse_calls, sorts,  sql_text
FROM   v$sqlarea
WHERE  sql_text NOT LIKE '%$%' AND command_type IN (2,3,6,7)
   AND rownum <= 200
ORDER BY sql_text DESC;

** 파싱된 SQL문의(메모리 순)

SELECT buffer_gets, sql_text
FROM   v$sqlarea
WHERE  sql_text NOT LIKE '%$%'
   AND command_type IN(2,3,6,7)
ORDER BY buffer_gets DESC;

** 파싱된 SQL문의(I/O 순)

SELECT disk_reads, sql_text
FROM   v$sqlarea
WHERE  sql_text NOT LIKE '%$%'
   AND command_type IN(2,3,6,7)
ORDER BY disk_reads DESC;

Large Pool (free memory 상태)

SELECT pool, name, bytes
FROM   v$sgastat
WHERE  pool = 'large pool'; 

DB Writer 정보 

** DBWR 정보

SELECT spid, program, username, serial#, terminal
FROM   v$process
WHERE  program LIKE '%DB%';

** 대기상태

SELECT event, total_waits, total_timeouts, time_waited, average_wait
FROM   v$system_event
WHERE  event = 'free buffer waits';

CKPT 정보 

** CKPT  정보

SELECT spid, program, username, serial#, terminal
FROM   v$process
WHERE  program LIKE '%CKPT%';

** 대기상태

SELECT event, total_waits, time_waited, average_wait
FROM   v$system_event
WHERE  event LIKE 'log file switch (checkpoint%';

LGWR 정보 

** LGWR  정보

SELECT spid, program, username, serial#, terminal
FROM   v$process
WHERE  program LIKE '%LGWR%';

** 대기상태

SELECT sid, event, seconds_in_wait, state
FROM   v$session_wait
WHERE  event = 'log buffer space%'; 

PMON 정보 

** PMON  정보

SELECT spid, program, username, serial#, terminal
FROM   v$process
WHERE  program LIKE '%PMON%';

** 대기상태

SELECT event, total_waits, total_timeouts, time_waited, average_wait
FROM   v$system_event
WHERE  event = 'pmon timer';

SMON 정보 

** SMON  정보

SELECT spid, program, username, serial#, terminal
FROM   v$process
WHERE  program LIKE '%SMON%'; 

** 대기상태

SELECT event, total_waits, total_timeouts, time_waited, average_wait
FROM   v$system_event
WHERE  event = 'smon timer';

Data Files 

** Data-File 구조

SELECT B.file_id "File #", B.file_name,
       B.tablespace_name, B.bytes "Bytes",
       ((B.bytes - sum(nvl(A.bytes,0)))) "Used Space",
       (SUM(NVL(A.bytes,0)))  "Free Space",
       (SUM(NVL(A.bytes,0)) / (B.bytes)) * 100 "FreeSpace Ratio"
FROM   sys.dba_free_space A, sys.dba_data_files B
WHERE  A.file_id(+) = B.file_id
GROUP BY B.tablespace_name, B.file_id, B.file_name, B.bytes
ORDER BY B.file_id;

** Disk I/O 상태

select name, phyrds, phywrts
FROM   v$datafile DF, v$filestat FS
WHERE  DF.file# = FS.file#;

** Object 종류

SELECT A.owner, A.object_id, A.object_name, B.partition_name,
       B.tablespace_name, B.bytes, B.blocks, B.extents,
       B.initial_extent, B.next_extent, B.min_extents,
       B.max_extents, B.pct_increase, B.freelists,
       B.relative_fno, B.buffer_pool, A.created, A.status
FROM   dba_objects A, dba_segments B
WHERE  A.owner = 'SCOTT'
   AND A.object_type = 'TABLE'
ORDER BY A.object_name;

** 롤백 세그먼트(경합 상태)

SELECT SUM(waits) "Waits", SUM(gets) "Gets",
       100 * SUM(waits)/SUM(gets) "히트율"
FROM   v$rollstat;

** 롤백 세그먼트(대기 상태)

SELECT (SELECT count FROM   v$waitstat WHERE  class = 'undo header') "Undo Header",
       (SELECT count FROM   v$waitstat WHERE  class = 'undo block')  "Undo Block",
       (SELECT count FROM   v$waitstat WHERE  class = 'system undo header') "System Undo Header",
       (SELECT count FROM   v$waitstat WHERE  class = 'system undo block') "System Undo block"
FROM dual;

**  TEMP 세그먼트(경합 상태)

SELECT username, user, contents, segtype,
       extents, blocks
FROM   v$sort_usage;

** Lock 상태 (Holder & Waiter)

SELECT LPAD(' ',DECODE(request,0,0,1))||sid sess,
       id1, id2, lmode, request, type
FROM   v$lock
WHERE  id1 IN (SELECT id1 FROM v$lock WHERE lmode = 0)
ORDER BY id1, request;

** Lock 상태 (Waiters)

SELECT LPAD(' ',DECODE(C.request,0,0,1))||C.sid sess, B.username,
       B.program, C.type, A.sql_text SQL
FROM   v$sqlarea A,v$session B, v$lock C, v$access D
WHERE  C.id1 IN (SELECT id1 FROM v$lock WHERE lmode = 0 OR lmode = 6)
   AND A.address(+) = B.sql_address
   AND A.hash_value(+) = B.sql_hash_value
   AND B.sid = D.sid
   AND B.sid = C.sid
   AND D.sid = C.sid
   AND D.owner != 'SYS'
ORDER BY C.id1, C.request;

** DB 사용자

SELECT username, default_tablespace, temporary_tablespace,
       account_status, expiry_date, lock_date, profile
FROM   dba_users;

 Control-file 구조

SELECT *
FROM   v$controlfile; 

 Redo log files

** Redo-Log Files 구조

SELECT A.group# col1, bytes / 1024 col2,
       members col3, member col4 ,
       A.status col5, sequence#
FROM   v$log A, v$logfile B
WHERE  A.group# = B.group#;

** I/O 대기상태

SELECT event, total_waits, time_waited, average_wait
FROM   v$system_event
WHERE  event LIKE 'log file switch completion%';

Archived log files

SELECT name, sequence#, first_change#, first_time, 
       next_change#, next_time, archived, status 
FROM   v$archived_log;

SELECT sid, serial#, osuser, process, machine, program, 
       to_char(logon_time, 'yyyy/mm/dd hh:mi:ss') as Logon_Time
FROM   v$session 
WHERE  (upper(server) <> 'PSEUDO' AND UPPER(program) NOT LIKE 'oracle.exe%')
       and serial# <> 1;

얼마전 UNDO TABLESPACE SIZE 가 비대하게 늘어나 ONLINE 중에 이를 줄이는 작업이 있었습니다.

다음과 같이 UNDO TABLESPACE 를 추가로 하나 만들고 SWITCH 하는 방법으로 ONLINE 에 영향 없이 UNOD SIZE 를 줄일수 있습니다.

참고하세요

1. UNDO TABLESPACE 생성

2. UNDO DATAFILE 추가

alter tablespace UNDOTBS2 add datafile  '/dev/vx/rdsk/dg_us2db06/df_pusdb2_16g_0085' size 16384m ;

3. AUTOEXTED ON 으로 변경

alter database datafile ‘/dev/vx/rdsk/dg_us2db06/df_pusdb2_16g_0085' autoextend on maxsize 16384m;

- 물리적 위치 조회