웹 개발과 관련된 모든 것들

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;

- 물리적 위치 조회