웹 개발과 관련된 모든 것들

 LAST_VALUE() OVER() -> 전체 값들 중 마지막값을 반환합니다

 COUNT() OVER() -> 조건을 만족하는 행의 수를 반환합니다

 SUM() OVER() -> 특정 값을 누적하여 결과를 반환합니다

 DENSE_RANK() OVER() -> RANK()와는 달리 같은 순위가 둘 이상 있어도 다음 순위는 1증가

 AVG() OVER() -> 분석 함수로 사용된 예

 RANK() WITHIN GROUP() -> 전체 값을 대상으로 특정 값의 순위를 반환합니다

 RANK() OVER() -> 특정 순위가 아닌 전체 순위를 반환합니다

 SUM() -> 하나 이상의 행으로 부터 합계를 반환합니다

 MAX() -> 하나 이상의 행으로 부터 최대값을 반환합니다

 MIN() -> 하나 이상의 행으로 부터 최소값을 반환합니다

 COUNT() -> 전체 행의 수를 반환합니다

        CREATE OR REPLACE TRIGGER change_hist        -- Store code in hist table
               AFTER CREATE ON SCOTT.SCHEMA          -- Change SCOTT to your schema name
        DECLARE
        BEGIN
          if DICTIONARY_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',
                          'PACKAGE', 'PACKAGE BODY', 'TYPE') then
             -- Store old code in SOURCE_HIST table
             INSERT INTO SOURCE_HIST
                SELECT sysdate, user_source.* FROM USER_SOURCE
                WHERE  TYPE = DICTIONARY_OBJ_TYPE
                  AND  NAME = DICTIONARY_OBJ_NAME;
          end if;
        EXCEPTION
          WHEN OTHERS THEN
               raise_application_error(-20000, SQLERRM);
        END;
        /
        show errors

CHR(9) => DBMS.output에서 탭값 출력하기

엑셀에 바로 붙여넣기 귀찮아서 ... ㅋㅋ;

값을 쉽게 구하자..

 또한 & 표시는 CHR(38) 번.

PL/SQL 구문은 & 표시는 값을 입력받도록 되어 있어

REPLACE 구문으로 입력받은 값을 치환할 때 컴파일이 정상적으로 되지 않을수 있음...

1. pkg_backup.sh 파일내용

###  오라클 환경 설정(.profile 참조)

###

sqlplus -SILENT 아뒤/패스워드

@/oracle/backup_pkg/get_pkg_name.sql > names.txt

yd=`date +%Y%m%d`
mkdir -p /oracle/backup_pkg/pkgs/$yd
for get_name in `cat names.txt`
do
        echo "$get_name Package Header Backup"
        echo "set heading off ;" > get_pkg.sql

        echo "set linesize 10000;" >> get_pkg.sql
        echo "set pagesize 50000; " >> get_pkg.sql

        echo "set feedback off; " >> get_pkg.sql
        echo "set verify off;" >> get_pkg.sql

        echo "SELECT TEXT FROM ALL_SOURCE " >> get_pkg.sql
        echo "WHERE TYPE='PACKAGE'" >> get_pkg.sql
        echo "AND   OWNER=''"    >> get_pkg.sql
        echo "AND   NAME='$get_name';" >> get_pkg.sql

        echo "exit; " >> get_pkg.sql

        chmod +x get_pkg.sql

        echo "CREATE OR REPLACE " > /oracle/backup_pkg/pkgs/$yd/$get_name.pks

        sqlplus -SILENT 아뒤/패스워드 @get_pkg.sql >> /oracle/backup_pkg/pkgs/$yd/$get_name.pks

        echo "$get_name Package Body Backup"
        echo "set heading off ;" > get_pkg.sql

        echo "set linesize 10000;" >> get_pkg.sql
        echo "set pagesize 50000; " >> get_pkg.sql

        echo "set feedback off; " >> get_pkg.sql
        echo "set verify off;" >> get_pkg.sql

        echo "SELECT TEXT FROM ALL_SOURCE " >> get_pkg.sql
        echo "WHERE TYPE='PACKAGE BODY'" >> get_pkg.sql
        echo "AND   OWNER=''"    >> get_pkg.sql
        echo "AND   NAME='$get_name';" >> get_pkg.sql

        echo "exit; " >> get_pkg.sql

        chmod +x get_pkg.sql

        echo "CREATE OR REPLACE " > /oracle/backup_pkg/pkgs/$yd/$get_name.pkb

        sqlplus -SILENT 아뒤/패스워드 @get_pkg.sql >> /oracle/backup_pkg/pkgs/$yd/$get_name.pkb

done
cd -

2. get_pkg_name.sql  파일내용

-- 헤더 정보 출력되지 않게 설정
set heading off;

-- 페이지 사이즈를 최대한으로
set pagesize 10000;

-- 출력되는 행수 삭제
set feedback off;

set verify off;

-- 패키지의 이름을 가지고 옴
SELECT NAME FROM ALL_SOURCE
WHERE TYPE='PACKAGE'
AND   OWNER=''
GROUP BY NAME;

-- 종료
exit;

프로시져백업.zip

테이블 스페이스 생성

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;

주석문을 볼수있는 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;