웹 개발과 관련된 모든 것들

EXPORT 시 QUERY OPTION에 대한 사용 예(ORACLE 8I 이상)
============================================

PURPOSE
============
oracle 8i에서 export 시 query option에 대한 사용 예

8i에서 export의 query syntax 를 이용하여 table data의 한 부분만 exporting 이 가능

UNIX syntax:

2. employee와 cust table에서 new york 주의 data 만 export ..
exp scott/tiger query=\"where st=\'NY\'\" tables=(employee,cust) file=exp.dmp log=exp.log

query 문장에서 UNIX reserved characters( ", ', ,< .. 등) 를 사용하는 경우에는 escape ('\') 을 반드시 사용해야 한다.
예)query=\"where JOB = \'SALESMAN\' and salary \< 1600\"

더 중요한 것은 command line에서 export option을 사용할때는 반드시 escape 이 있어야 하나
parfile을 사용할때는 eacape이 불필요하다.

예를 보면 .. p라는 이름의 file을 다음과 같이 생성
tables=emp query="where job='SALESMAN'"

parfile을 이용해서 export 를 실행해 보면
[rmtdchp6]/apac/rdbms/64bit/app/oracle/product/9.2.0> exp scott/tiger parfile=p

Export: Release 9.2.0.4.0 - Production on Wed Mar 17 00:12:34 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses KO16KSC5601 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table EMP 4 rows exported
와 같이 정상 처리 됨을 알수 있다.

만일 command line에서 위의 내용을 실행하게 되면 다음과 같이 error 를 만난다.
exp scott/tiger tables=emp query="where job='SALESMAN'"

LRM-00101: unknown parameter name 'job'

EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully

command line에는 query 내에 single(')나 double quotes(") 를 사용한다면 반드시 double quotes(") 를 사용하여
query 문을 묶어야 한다.그러나 query 내에서 single ('')나 double quotes(") 를 사용하지 않는다면 single quotes (')을 사용하여
query 문을 수행할 수도 있다..

다음 예를 보면..
1>exp scott/tiger tables=emp query=\'where deptno=20\'

Export: Release 9.2.0.4.0 - Production on Wed Mar 17 00:22:00 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses KO16KSC5601 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table EMP 4 rows exported

2>exp scott/tiger tables=emp query=\'where job=\'SALESMAN\'\'

LRM-00112: multiple values not allowed for parameter 'query'

EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully

즉.. 정리를 하자면
command line에서 query 내에 '," 을사용하지 않는 다면 '나 " 으로 query option을 사용할수 있다
query=\'where deptno = 20\'
query=\"where deptno = 20\"
query=\'where deptno \< 2\'
(이 경우 single 이나 double quotes 을 둘다 사용할수 있다.)

parfile을 사용하는 경우에는 다음과 같이 단순하게 사용이 가능하다.
query='where deptno = 20'
query="where deptno = 20"
query='where deptno < 20'

WINDOWS NT / 2000 와 NETWARE syntax:

Explanation

Reference Documents

서버 프로세스

** 서버 프로세스 상태

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;