About DATABASE

NLS_LANG

US7ASCII

DBA가 프로젝트를 시작하면서 가장 먼저 결정해야 할 것은 Oracle Database Character Set이다.

현재 14장의 티스토리 초대장을  가지고 있습니다.

초대장을 원하시는 분들은 가능하면 방명록이나 댓글을 통하여 본인의 블로그 운영 목적을 밝히고, 또한 이메일 주소를 꼬옥 남겨주셔요


받으시는 분들 모두 알차고 즐거운  블로그 사용하시면 좋겠습니다.

즐겁게 블로깅하세요~*^.~*~~~

* ROLLUP 연산자
 - GROUP BY절에 있는 컬럼들을 오른쪽에서 왼쪽의 차례로 그룹들을 생성하고,
   각 그룹에 계산함수를 적용한다.
 - GROUP BY절의 결과는 누적 계산 결과이다.
 

* CUBE 연산자
 - GROUP BY절에 있는 모든 컬럼들에 대한 가능한 모든 조합을 그룹으로 생성한다.
 

* GROUPING 함수
 - 각 결과 행이 CUBE, ROLLUP 연산자들에 의해 계산된 것인지를 알기 위해 사용된다.
 - 해당컬럼에 대해 계산되었다면 0, 그렇지 않다면(컬럼값이 NULL) 1을 반환한다.
 - GROUP BY절에 나타나는 컬럼에 적용된다.

사용 예)

  -- table생성(사원이름,급여,부서,직위,입사년도)
  CREATE TABLE roll_test (
    name   VARCHAR2(10),
    sal    NUMBER,
    dept   VARCHAR2(10),
    duty   VARCHAR2(10),
    entYear NUMBER(4)
  );

INSERT INTO roll_Test VALUES('kim' , 1000, 'AA', '00', 2004);
INSERT INTO roll_Test VALUES('no' , 1500, 'AA', '00', 2004);
INSERT INTO roll_Test VALUES('choi', 2000, 'BB', '02', 2003);
INSERT INTO roll_Test VALUES('park', 2000, 'BB', '02', 2003);
INSERT INTO roll_Test VALUES('lee' , 3000, 'CC', '03', 2002);
INSERT INTO roll_Test VALUES('cho' , 4000, 'AA', '04', 2001);
INSERT INTO roll_Test VALUES('lyu' , 4000, 'DD', '04', 2001);
INSERT INTO roll_Test VALUES('ham' , 4000, 'AA', '04', 2001);
INSERT INTO roll_Test VALUES('kang', 7000, 'DD', '05', 2001);
COMMIT;

SELECT * FROM roll_Test;

-- 1. 각 부서에 대한 급여 소계를 구하고, 총계를 구하라
--    (하나의 Column Grouping)

-----일반-----------
SELECT dept, SUM(sal)
FROM   roll_Test
GROUP BY dept;
---------------------

----- ROLLUP ---------
SELECT dept, SUM(sal), GROUPING(dept)
FROM   roll_Test
GROUP BY ROLLUP(dept);
-----------------------

----- CUBE ----------
SELECT dept, SUM(sal), GROUPING(dept)
FROM roll_Test
GROUP BY CUBE(dept);
-----------------------

-- 일반적인 GROUP BY를 사용할 경우 급여 소계만 나오고, 총계는 따로 구해야 함
-- ROLLUP과 CUBE 차이점 없음

-- 2. 각 부서별, 직위별 급여 소계를 구하고, 총계를 구하라
--    (두개의Column Grouping)

-----NORMAL-----------
SELECT dept, duty, SUM(sal)
FROM   roll_Test
GROUP BY dept, duty;
-----------------------

----- ROLLUP ----------
SELECT dept, duty, SUM(sal), GROUPING(dept), GROUPING(duty)
FROM   roll_Test
GROUP BY ROLLUP(dept, duty);
-----------------------

----- CUBE ----------
SELECT dept, duty, SUM(sal), GROUPING(dept), GROUPING(duty)
FROM   roll_Test
GROUP BY CUBE(dept, duty);
-----------------------

-- ROLLUP은 부서에 대한 소계 / 부서에 대한 직위별 소계만 볼 수 있고,
-- CUBE는 부서에 대한 소계 / 부서에 대한 직위별 소계 / 직위별 소계를 볼 수 있음
-- GROUP BY 내의 왼쪽 컬럼부터 자동으로 오름차순 정렬 됨

-- 3. 각 부서별, 직위별, 입사년도별 급여 소계를 구하고, 총계를 구하라
--    (세개의 Column Grouping)

-----NORMAL-----------
SELECT dept, duty, entYear, SUM(sal)
FROM   roll_Test
GROUP BY dept, duty, entYear;
-----------------------

----- ROLLUP ----------
SELECT dept, duty, entYear, SUM(sal), GROUPING(dept), GROUPING(duty), GROUPING(entYear)
FROM   roll_Test
GROUP BY ROLLUP(dept, duty, entYear);
-----------------------

----- CUBE ----------
SELECT dept, duty, entYear, SUM(sal), GROUPING(dept), GROUPING(duty), GROUPING(entYear)
FROM   roll_Test
GROUP BY CUBE(dept, duty, entYear);
-----------------------

- ROLLUP 사용시 3개의 소계와 1개의 총계를 구할 수 있음
  (부서별, 부서*직위별, 부서*직위*입사년도별, 총계)
  ※ GROUP BY 내의 가장 왼쪽 컬럼을 기준으로 하여 순차적으로 하위 그룹 생성

- CUBE 사용시 7개의 소계와 1개의 총계를 구할 수 있음.
  (부서별, 직위별, 입사년도별, 부서*직위별, 부서*입사년도별, 직위*입사년도별, 부서*직위*입사년도별, 총계)
  ※ 생성 가능한 모든 경우를 그룹 생성
 

사용 예)

* 부서에 대한 소계를 보고 싶을 때
HAVING GROUPING(dept) = 0 AND GROUPING(duty) = 1
AND GROUPING(entYear) = 1;

* 각 부서에 대한 직위별 소계를 보고 싶을 때
HAVING GROUPING(dept) = 0 AND GROUPING(duty) = 0
AND GROUPING(entYear) = 1;

select SEGMENT_NAME ,segment_type ,sum(사용량)
from (
      SELECT SEGMENT_NAME , ROUND(SUM(BYTES)/1024/1024) AS "사용량", segment_type
      FROM USER_EXTENTS
      --WHERE SEGMENT_NAME LIKE 'TTXSTAT%'
      GROUP BY SEGMENT_NAME, segment_type)
GROUP BY rollup(SEGMENT_NAME),segment_type

1) export를 할때 데이터는 만들지 않고 schema image만 뽑아냅니다.
(만약 export를 받은 파일이 이미 있으시다면 1번 항목은 생략하셔도 됩니다.)

exp scott/tiger file=exp.dmp compress=n rows=n



2) 1번 스텝과 같이 해서 성공한 export dump파일을 이용하여 create script를 만드는 방법입니다.
이 방법은 import!라는 유틸리티를 사용하여 작업을 합니다.

imp scott/tiger file=exp.dmp indexfile=create_table.sql full=y



3) 요렇게 하면 create_table.sql이라는게 생성됩니다.
vi나 편집기로 여시면 REM이라는 코멘트로 앞부분이 막혀 있을 겁니다.
이걸 모두 없애시면 그야말로 DBMS내에 있는 형태 그대로 만들어진 Table Create Scripts입니다.

오라클 9i Release 2 기준으로 작성되었음.

◈ SGA 정보보기(정상 설치/실행 상태인가?)

$ sqlplus system/manager
SQL> show sga

◈ DBA로 접속

$ sqlplus "sys/passwd as sysdba"

◈ DB 시작
-- 일반 시작

$ sqlplus "sys/passwd as sysdba"
startup -- DB 인스턴스 시작
startup force -- DB가 실행중일 경우 강제로 종료했다 시작
startup restrict -- 일반 사용자 접근 금지 상태로 DB 시작

-- 단계별 시작

$ sqlplus "sys/passwd as sysdba"
startup nomount; -- NO Mount 단계
alter database mount; -- Mount 단계
alter database open; -- Open 단계

◈ DB 종료

$ sqlplus "sys/passwd as sysdba"
shutdown normal -- 세션,트랜잭션 종료시까지 대기후 종료
shutdown transactional -- 트랜잭션 종료시까지 대기후 종료
shutdown immediate -- 즉시 종료. 모든 DML 롤백
shutdown abort -- 비정상 즉시 종료. 백업과 복구시에만 사용.

◈ 로그인 없이 SQL*Plus 만 실행하기

$ sqlplus "/nolog"

◈ Table 생성 스크립트 뽑아내기

$ exp mlb/mlb file=결과덤프파일.dmp compress=n rows=n tables=테이블명

◈ & 기호 이용하기

1. 첫번째 방법
SELECT 'You '||Chr(38)||' Me' FROM DUAL;

2. 두번째 방법
SET DEFINE OFF
SELECT 'You & me' FROM DUAL;

◈ 편집기 및 SQL*Plus 공통 설정 지정하기
$ORACLE_HOME/sqlplus/admin/glogin.sql에 SQL*Plus 실행시 항상 지정되는 전체 설정을 할 수 있다.
여기서 "ed" 명령으로 실행되는 에디터는 다음 처럼 지정할 수 있다.

DEFINE_EDITOR=gvim.exe

◈ 프로시져 혹은 함수등의 소스 뽑아내기

SET NEWPAGE 0
SET TERMOUT OFF
SET VERIFY OFF
SET SPACE 0
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON
SET LINESIZE 500
SPOOL procedure_name.sql
SELECT TEXT FROM USER_SOURCE WHERE NAME='프로시져혹은함수이름' ORDER BY LINE;
SPOOL OFF

이렇게 저장된 procedure_name.sql 에서 불필요한 부분을 삭제하한다. 각 줄의 공백은 SET TRIMSPOOL ON 에 의해 제거된다.
VIM 공백 제거 : :%s/ *$//g
그리고 이렇게 생성된 소스 맨 앞에 CREATE OR REPLACE PROCEDURE 등을 붙여서 각 프로시져 등의 생성 스크립트로 사용할 수 있게 된다.

◈ 오류 내역출력

SHOW ERRORS

직전에 발생한 오류 내역 출력

◈ 숫자 출력 범위 늘리기

SET NUM 15

숫자를 15자리까지 출력

◈ 쿼리 결과를 셸 스크립트로 자동 저장하기

#!/bin/sh
# 오늘 날짜를 셸 변수로 지정
TODAY=`date +"%Y%m%d"`

sqlplus username/password << ENDSQL
-- 아래는 출력시 불필요한 형식 꾸미기가 안들어가게 한다.
SET ECHO OFF
SET NEWPAGE 0
SET SPACE 0
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON

SPOOL 저장할파일_${TODAY}_ORIG
SELECT COL1 || '|' || COL2 || '|' || COL3 -- 실행할 쿼리
FROM MYTABLE;
SPOOL OFF
QUIT
ENDSQL

# 처음과 마지막 쿼리 잔재 제거. 일단 스풀을 실행해보고나서 파일 위,아래에 추가되는
# 불필요한 줄수를 알아본다. (여기서는 위에 2줄과 맨 아래 1줄)
sed -e "1,2d" -e "\$d" 저장할파일_${TODAY}_ORIG > 저장할파일_${TODAY}

# 최종적으로 "저장할파일_${TODAY}"만 남겨둔다
rm 저장할파일_${TODAY}_ORIG

위와 같은 내용으로 셸 스크립트를 만들면 SQL*Plus 로 쿼리 결과를 특정한 형식(여기서는 각 컬럼을 세로 바(|)로 나눈 형식)으로 뽑아낼 수 있다.
그리고 이 경우 셸 환경 변수 값을 TODAY 처럼 SQL 쿼리 문장에 삽입하는 것이 가능하다.

◈ 쿼리 수행 시간 알아내기

SET TIMING ON

이렇게 설정하고 쿼리를 날리면 쿼리 수행시간도 함께 출력된다.

◈ DBMS_OUTPUT.* 으로 출력되는 양 조절

SET SERVEROUTPUT ON SIZE 4000;
Execute DBMS_OUTPUT.PUT_LINE(SYSDATE

1. 테이블에 특정컬럼에 중복된 값을 찾는 SQL
/*--------------------------------------------------------------------------*/
/* USAGE : @중복찾기.SQL [테이블명] [중복을조사할컬럼명]
/*
/* WARNING : 똑같은값이 2개 이상있을때 처음값은 출력 않되고 2번째
/* 값부터 출력됨. <>
/*--------------------------------------------------------------------------*/
SELECT * FROM &1 A
WHERE ROWID >
(SELECT MIN(ROWID) FROM &1 B
WHERE B.&2 = A.&2)
ORDER BY &2;

2. PK와 FK간의 연관관계를 찾아 보여주는 SQL
/*--------------------------------------------------------------------------*/
/*　 사용법　　 　 :> @SHOW_POSITIONS　 PARENT_TABLE　 CHILD_TABLE　　
/*　 DESCRIPTION　 :　 SHOWS PRIMARY AND FOREIGN KEY POSITIONS　　
/*　　
/*　 WARNING　　 :　 이 문장은 해당 TABLE의 CONSTRAINT생성시 NAMING　　　
/*　　　　　　　　　 CONVENTION을 따른 경우에 적용되도록 되어 있다.　　
/*--------------------------------------------------------------------------*/
SET VERIFY OFF　　
CLEAR BREAK　　
BREAK ON CONSTRAINT_NAME ON TABLES
SELECT SUBSTR(CONSTRAINT_NAME,1,27) CONSTRAINT_NAME,
SUBSTR(TABLE_NAME,1,15) TABLES,
SUBSTR(COLUMN_NAME,1,15) COL_NAME,
SUBSTR(POSITION,1,3) POSITION,
SUBSTR(OWNER,1,7) OWNER
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = UPPER('&1')
AND CONSTRAINT_NAME LIKE 'PK%'
UNION
SELECT SUBSTR(CONSTRAINT_NAME,1,27) CONSTRAINT_NAME,
SUBSTR(TABLE_NAME,1,15) TABLES,
SUBSTR(COLUMN_NAME,1,25) COL_NAME,
SUBSTR(POSITION,1,3) POSITION,
SUBSTR(OWNER,1,7) OWNER
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = UPPER('&2')
AND CONSTRAINT_NAME LIKE 'FK%'
ORDER BY 1 DESC,4 ASC;

3. 컬럼에 걸려있는 CONSTRAINT 를 보여주는 SQL.
/*--------------------------------------------------------------------------*/
/* USAGE : @SHOW_CONSTRAINTS TABLE_NAME COLUMN_NAME
/* DESCRIPTION: 해당 TABLE의 COLUMN에 걸려 있는 CONSTRAINT를 보여준다.
/* < 실행 예 >
/* SQL> @SHOW_CONSTRAINTS WIDGETS LENGTH
/*--------------------------------------------------------------------------*/
SET VERIFY OFF
CLEAR BREAK
BREAK ON TABLES ON COL_NAME
SELECT SUBSTR(TABLE_NAME,1,15) TABLES,
SUBSTR(COLUMN_NAME,1,15) COL_NAME,
SUBSTR(CONSTRAINT_NAME,1,25) CONSTRAINT_NAME
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = UPPER('&1')
AND COLUMN_NAME = UPPER('&2');

4. CONSTRAINT이름으로 해당 테이블과 컬럼찾는 SQL
/*--------------------------------------------------------------------------*/
/* USAGE : @SHOW_COLUMNS CONSTRAINT_NAME
/* DESCRIPTION : SHOWS THE COLUMNS BOUND BY A CONSTRAINT
/* 사용예 : SQL> @SHOW_COLUMNS PK_EMPNO
/*--------------------------------------------------------------------------*/
SET VERIFY OFF
CLEAR BREAK
BREAK ON CONSTRAINT_NAME ON TABLES

SELECT SUBSTR(CONSTRAINT_NAME,1,25) CONSTRAINT_NAME,
SUBSTR(TABLE_NAME,1,15) TABLES,
SUBSTR(COLUMN_NAME,1,15) COL_NAME
FROM ALL_CONS_COLUMNS
WHERE CONSTRAINT_NAME = UPPER('&1');


5. 컬럼명만 가지고 테이블과 설정상태를 찾아주는 SQL
/*--------------------------------------------------------------------------*/
/* 사용법 : SQL> @COL_FIND [컬럼명]
/*--------------------------------------------------------------------------*/
COL CNAME FORMAT A20
COL COLTYPE FORMAT A10
COL NULLS FORMAT A5
COL DEFAULTVAL FORMAT A10

SELECT TNAME, COLNO, CNAME, COLTYPE, WIDTH, NULLS, DEFAULTVAL
FROM COL
WHERE CNAME = UPPER('&1')


6. 딕셔너리에서 해당 키워드에 관한 뷰, 테이블을 찾아주는 SQL
/*--------------------------------------------------------------------------*/
/* 사용법 : SQL> @DIC_FIND [키워드(대소문자가림)]
/*--------------------------------------------------------------------------*/
TABLE_NAME FORMAT A15
COL COMMENTS FORMAT A100

SELECT * FROM DICTIONARY
WHERE COMMENTS LIKE ('%&1%')
/

7. DEAD LOCK이 발생했을때 발생시킨 유저와 SQL문을 찾아주는 SQL
/*--------------------------------------------------------------------------*/
/* 사 용 법　　:SQL> @FIND_DEADLOCK
/*　 DESCRIPTION : 데드락이 발생할 경우 LOCKING 된 유저와 SQL문을 보여준다.
/*　 데드락이 발생한 유저를 KILL 하려면.
/* ALTER SYSTEM KILL SESSION '{SERIAL#},{SID}';
/*--------------------------------------------------------------------------*/
SELECT A.SERIAL#, A.SID, A.USERNAME, B.ID1, C.SQL_TEXT
FROM V$SESSION A, V$LOCK B, V$SQLTEXT C
WHERE B.ID1 IN( SELECT DISTINCT E.ID1 FROM V$SESSION D, V$LOCK E
WHERE D.LOCKWAIT = E.KADDR)
AND A.SID = B.SID
AND C.HASH_VALUE = A.SQL_HASH_VALUE
AND B.REQUEST = 0;

8. 테이블 데이터의 사이즈를 계산해주는 SQL
/*--------------------------------------------------------------------------*/
/* TABLE DATA SIZE를 정확히 계산해주는 스크립트. <<박제용>>
/* 사용법 : @TAB_SIZE [TABLE_NAME]
/*--------------------------------------------------------------------------*/
ANALYZE TABLE &1 DELETE STATISTICS;
ANALYZE TABLE &1 COMPUTE STATISTICS;

SELECT GREATEST(4, CEIL(NUM_ROWS/
((ROUND(((1958-(INI_TRANS*23))*
((100-PCT_FREE)/100))/AVG_ROW_LEN)))) * 2048)
TABLESIZE_KBYTES
FROM USER_TABLES
WHERE TABLE_NAME = UPPER('&1');


9. 테이블을 복사해주는 스크립트 (V8.0 ONLY)
/*--------------------------------------------------------------------------*/
/* TABLE을 다른 스키마 혹은 TABLE로 복사 <<박제용>>
/* NOTICE) 1. ORACLE 8.0 이상에서만 지원.
/* 2. SQL*NET 이 설정되어 있어야만 한다.
/* 3. 테이블과 PK만 복사하고 인덱스는 모두 다시 생성해주어야 한다.
/* 따라서 테이블을 생성해 주고 입력하는것이 좋다.
/* 4. SQL*PLUS 에서만 실행된다.
/* 사용법) @TAB_COPY SCOTT/TIGER@LINK SOURCE_TABLE_NAME
TARGET_TABLE_NAME
/*--------------------------------------------------------------------------*/
COPY FROM &1 CREATE &3 USING SELECT * FROM &2

/* 다른 DB로 복사할때는
COPY FROM &1 TO &2 CREATE &4 USING SELECT * FROM &3
*/

/* 미리 만들어진 TABLE에 입력할때는
COPY FROM &1 INSERT &3 USING SELECT * FROM &2
*/

10.이미 컴파일된 프로시져소스를 보고싶을 때 사용하는 스크립트.
/*--------------------------------------------------------------------------*/
/* PL/SQL 소스를 보기위한 스크립트.. <박제용>
/* 사용법 : FIND_PLSQL [프로시져명칭]
/*--------------------------------------------------------------------------*/
SELECT TEXT
FROM USER_SOURCE
WHERE NAME = UPPER('&1')
ORDER BY LINE;

11. 테이블이 사용중인 블록 크기를 계산해주는 SQL
/*--------------------------------------------------------------------------*/
/* TABLE이 사용하는 블럭 크기를 구하는 스크립트... <<박제용>>
/* 사용법 : 1) DBA 권한으로 로그인한다.
/* 2) SQL> @TAB_BLOCK [TABLE명]
/* NOTICE : SUM(BLOCKS)는 사용하는 블럭의 갯수이며 사이즈는
/* DB_BLOCK_SIZE를 곱하여 얻을 수 있다.
/*--------------------------------------------------------------------------*/
SELECT OWNER, TABLESPACE_NAME, SEGMENT_NAME, SUM(BLOCKS)
FROM DBA_EXTENTS
WHERE SEGMENT_NAME = UPPER('&1')
GROUP BY OWNER, TABLESPACE_NAME, SEGMENT_NAME
/

12. SQL CURSOR를 보여주는 스크립트
/*--------------------------------------------------------------------------*/
/* SQL CURSOR를 조사하는 스크립트. <<박제용 99.11>>
/* SQL CURSOR 를 조사하여 부하가 많이 걸리는 SQL문과
/* 메모리를 조사한다.
/* LOADS : 캐쉬에서 나갔다 들어온 횟수(BEST=1).
/* INVALIDATIONS : LRU에서 무효화된 횟수. 이 값이 4이상이면
/* SHARED_POOL_AREA를 확장해야한다.
/* PARSE_CALLS : 이 커서의 호출 수.
/* SORTS : 수행된 소트횟수
/* COMMAND_TYPE: 2 - INSERT, 3-SELECT, 4-UPDATE, 7-DELETE
/*--------------------------------------------------------------------------*/
SELECT SQL_TEXT, LOADS, INVALIDATIONS, PARSE_CALLS, SORTS
FROM V$SQLAREA
WHERE SQL_TEXT NOT LIKE '%$%'
AND COMMAND_TYPE IN(2,3,6,7);

13. EXPAIN PLAN 결과를 보기 쉽게 출력해주는 스크립트
/*--------------------------------------------------------------------------*/
/* EXPAIN PLAN 결과를 보기 쉽게 출력해주는 스크립트.
/* 1) EXPAIN을 처음 사용할 경우엔 [ORACLE_HOME]/RDBMS/ADMIN/UTLXPLAN.SQL을 실행,
/* PLAN_TABLE을 생성한다.
/* 2) 처음 사용이 아니면 DELETE FROM PLAN_TABLE; 을 실행하여 이전 결과를 삭제.
/* 실행결과 파싱번호(ID)가 길면 SQL이 비효율적이거나, SHARED_POOL_SIZE가 작은것이다.
/* 기타 SQL문이 인덱스를 사용하는지 등등을 알수 있다.
/*--------------------------------------------------------------------------*/
COL OPERATION FORMAT A30
COL OPTIONS FORMAT A20
COL ID FORMAT 99

SELECT ID, LPAD(' ',2*LEVEL) || OPERATION ||
DECODE(ID, 0, ' COST= ' || POSITION )"OPERATION",
OPTIONS, OBJECT_NAME "OBJECT"
FROM PLAN_TABLE
CONNECT BY PRIOR ID=PARENT_ID
START WITH ID =0;

14. 과도한 DISK READ를 수행하는 SQL문을 V$SQLAREA 에서 검색해줌.
/*--------------------------------------------------------------------------*/
/* SQL QUERY 튜닝 스크립트.. <박제용>
/* 과도한 DISK READ를 수행하는 SQL문을 V$SQLAREA 에서 검색해줌.
/* 원인 => 1) SQL문이 최적화 되지 않아 DISK READ를 많이 할 수 밖에 없는 쿼리일경우.
/* (INDEX가 없거나 사용되지 않을때)
/* 2) DB_BLOCK_BUFFERS 또는 SHARED_POOL_SIZE 가 작은 경우. (메모리가 적음)
/*--------------------------------------------------------------------------*/
SELECT DISK_READS, SQL_TEXT FROM V$SQLAREA
WHERE DISK_READS > 10000
ORDER BY DISK_READS DESC;

15. 과도한 LOGICAL READ를 수행하는 SQL문을 V$SQLAREA 에서 검색해줌.
/*--------------------------------------------------------------------------*/
/* SQL QUERY 튜닝 스크립트.. <박제용>
/* 과도한 LOGICAL READ를 수행하는 SQL문을 V$SQLAREA 에서 검색해줌.
/*
/* 원인 => 1) 인덱스 컬럼에 DISTINCT한 값이 적은, 부적절한 인덱스의 사용.
/* (대체로 인덱스를 지워야 할 경우)
/* 2) 최적화 되지 않은 SQL 문장
/*--------------------------------------------------------------------------*/
SELECT BUFFER_GETS, SQL_TEXT FROM V$SQLAREA
WHERE BUFFER_GETS > 200000
ORDER BY BUFFER_GETS DESC;

16. 유저별로 과도한 LOGICAL READ를 수행하는 SQL 문 찾기
/*--------------------------------------------------------------------------*/
/* SQL QUERY 튜닝 스크립트.. <박제용>
/* 유저별로 과도한 LOGICAL READ를 수행하는 SQL 문 찾기
/*--------------------------------------------------------------------------*/
BREAK ON USER_NAME ON DISK_READS ON BUFFER_GETS ON ROWS_PROCESSED
SELECT A.USER_NAME, B.DISK_READS, B.BUFFER_GETS, B.ROWS_PROCESSED,
C.SQL_TEXT
FROM V$OPEN_CURSOR A, V$SQLAREA B, V$SQLTEXT C
WHERE A.USER_NAME = UPPER('&&USER') AND A.ADDRESS = C.ADDRESS
AND A.ADDRESS = B.ADDRESS
ORDER BY A.USER_NAME, A.ADDRESS, C.PIECE;

17. SHARED_POOL의 HIT RATIO보는 스크립트
/*--------------------------------------------------------------------------*/
** SHARED_POOL의 HIT RATIO보는 스크립트.. <박제용>
** 이 영역은 SQL 쿼리문이 저장되고, 유저별 사용 영역과, 데이터 딕셔너리등이 저장된다.
** 만일 적게 할당되면 유저의 접속이 많아질수록 THROUGHPUT에 큰 영향을 준다.
** HIT RATIO는 95% 이상을 유지시켜야 한다.
/*--------------------------------------------------------------------------*/
SELECT SUM(GETS) "GETS", SUM(GETMISSES) "MISSES",
(1-(SUM(GETMISSES) / (SUM(GETS)+SUM(GETMISSES))))*100
"HITRATE"
FROM V$ROWCACHE;

18. SHARED_POOL에 저장된 내용보기
/*--------------------------------------------------------------------------*/
/* SHARED_POOL에 저장된 내용보기 <박제용>
/* 프로시져나 패키지등은 SHARED_POOL에 저장되며 저장된 객체중
/* 그 크기가 100K 가 넘는것을 보여준다.
/*--------------------------------------------------------------------------*/
COL NAME FORMAT A30

SELECT NAME, SHARABLE_MEM
FROM V$DB_OBJECT_CACHE
WHERE SHARABLE_MEM > 100000
AND TYPE IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
AND KEPT = 'NO';

19. SHARED_POOL_SIZE의 현재 사용 현황을 보여줌
/*--------------------------------------------------------------------------*/
/* SHARED_POOL_SIZE의 현재 사용 현황을 보여줌. <박제용>
/* SHARED_POOL_SIZE의 현재의 사용현황을 보여준다.
/* 이 데이터를 주기적으로 보관하여 분석한다.
/*--------------------------------------------------------------------------*/
COL VALUE FOR 999,999,999,999 HEADING "SHARED POOL SIZE"
COL BYTES FOR 999,999,999,999 HEADING "FREE BYTES"
SELECT TO_NUMBER(V$PARAMETER.VALUE) VALUE, V$SGASTAT.BYTES,
(V$SGASTAT.BYTES/V$PARAMETER.VALUE)*100 "PERCENT FREE"
FROM V$SGASTAT, V$PARAMETER
WHERE V$SGASTAT.NAME = 'FREE MEMORY'
AND V$ PARAMETER .NAME = ‘SHARED_POOL_SIZE;

20. LIBRARY CACHE HITRATIO 출력 스크립트
/*--------------------------------------------------------------------------*/
/* LIBRARY CACHE HITRATIO 출력 스크립트
/* LIBRARY CACHE 의 HITRATIO 가 0.9 이하이면
/* SHARED POOL SIZE를 늘려주거나, SQL 문의 이상을
/* 조사해야 한다.
/*--------------------------------------------------------------------------*/
SELECT SUM(PINS) EXECUTIONS,
SUM(PINHITS) "EXECUTION HITS",
SUM(RELOADS) MISSES,
((SUM(PINS) / (SUM(PINS) + SUM(RELOADS))) * 100) HITRATIO
FROM V$LIBRARYCACHE;

20. ROWCACHE 의 MISSRATIO를 조사하는 스크립트
/*--------------------------------------------------------------------------*/
/* ROWCACHE 의 MISSRATIO를 조사하는 스크립트
/* ROW CHACHE 의 MISS RATIO는 15% 이하로 유지하는 것이 좋다.
/* 그렇지 않을경우 SHARED_POOL_SIZE를 늘리는것을 고려해야 한다.
/*--------------------------------------------------------------------------*/
SELECT SUM(GETS) "GETS",
SUM(GETMISSES) "MISSES",
(1-(SUM(GETMISSES)/(SUM(GETS)+SUM(GETMISSES))))*100 "HITRATE"
FROM V$ROWCACHE;

21. SHARED_POOL의 HIT RATIO보는 스크립트
/*--------------------------------------------------------------------------*/
/* SHARED_POOL의 HIT RATIO보는 스크립트.. <박제용>
/* 이 영역은 SQL 쿼리문이 저장되고, 유저별 사용 영역과, 데이터 딕셔너리등이 저장된다.
/* 만일 적게 할당되면 유저의 접속이 많아질수록 THROUGHPUT에 큰 영향을 준다.
/* HIT RATIO는 95% 이상을 유지시켜야 한다.
/*--------------------------------------------------------------------------*/
SELECT SUM(GETS) "GETS", SUM(GETMISSES) "MISSES",
(1-(SUM(GETMISSES) / (SUM(GETS)+SUM(GETMISSES))))*100
"HITRATE"
FROM V$ROWCACHE;

22. User의 object 정보 조회

- user_objects 데이터 사전을 이용하시면 됩니다.


SQL>col object_name format a25;
SQL>SELECT object_name, object_type
        FROM user_objects
        WHERE object_type = 'INDEX';

23. GRANT받은 권한을 조회하는 QUERY


GRANT받은 권한을 조회하는 QUERY
==============================
SELECT USERNAME, ROLENAME, PRIVILEGE
FROM (SELECT DECODE(SA1.GRANTEE#, 1, 'PUBLIC', U1.NAME) username,
             SUBSTR(U2.NAME,1,20) rolename,
             SUBSTR(SPM.NAME,1,27) PRIVILEGE
      FROM SYS.SYSAUTH$ SA1,
           SYS.SYSAUTH$ SA2,
           SYS.USER$ U1,
           SYS.USER$ U2,
           SYS.SYSTEM_PRIVILEGE_MAP SPM
      WHERE SA1.GRANTEE# = U1.USER#
      AND SA1.PRIVILEGE# = U2.USER#
      AND U2.USER# = SA2.GRANTEE#
      AND SA2.PRIVILEGE# = SPM.PRIVILEGE
      UNION
      SELECT U.NAME username, NULL rolename, SUBSTR(SPM.NAME,1,27) privilege
      FROM SYS.SYSTEM_PRIVILEGE_MAP SPM, SYS.SYSAUTH$ SA, SYS.USER$ U
      WHERE SA.GRANTEE#=U.USER#
      AND SA.PRIVILEGE#=SPM.PRIVILEGE)
WHERE USERNAME = 'SCOTT';



출처 : http://blog.empas.com/bjbjhong/7298879  and some additional query..

racle 10g에서는  내장 (Built-in) 롤 중 CONNECT 롤 및 RESOURCE 롤에 부여된 권한 변화