User추가 스크립트

프로그램의 개발단계에서는 자잘한 기능의 테스트나 설계 변경으로 인해서 DB를 손 댈 일이 종종 생긴다.

그럴 경우에 해당 DB의 스키마를 문서로 정리하여 보관하고 있다면, 실제 DB와 문서상의 스펙을 맞추기 번거로워서 전체 테이블 또는 user를 drop한 후 다시 만드는 것이 효과적일 때가 있다.

그래서 내가 user를 건드릴 때 내 경험상으로 자주 사용하는 스크립트를 정리하였다.

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;    -- 비밀번호 만료기간을 무제한으로 변경(신규구축한 DB일 경우에 필요에 따라 설정)

-- 전용 테이블스페이스 생성
CREATE TABLESPACE QWERTY_SPACE
DATAFILE
'/datafile/path/here/DATA_01.dtf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 30G ,
'/datafile/path/here/DATA_02.dtf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 30G
SEGMENT SPACE MANAGEMENT AUTO
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;


-- USER생성
CREATE USER QWERTY IDENTIFIED BY QWERTY_PW
DEFAULT TABLESPACE "QWERTY_SPACE";


--권한 부여(필수)
GRANT CONNECT TO QWERTY; -- ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW
GRANT RESOURCE TO QWERTY; -- CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE

-- 권한부여(선택)
GRANT EXP_FULL_DATABASE TO QWERTY;
GRANT IMP_FULL_DATABASE TO QWERTY;
GRANT SELECT_CATALOG_ROLE TO QWERTY;
GRANT SELECT ANY DICTIONARY TO QWERTY; -- SELECT_CATALOG_ROLE < SELECT ANY DICTIONARY < DBA
GRANT DBA TO QWERTY; -- All system privileges WITH ADMIN OPTION
  
-- 개별 privilege (Role을 부여하면 너무 큰 권한을 가지므로 필요에 따라 선택하여 권한부여)
GRANT SELECT V$_VIEW_NAME TO QWERTY;

SELECT * FROM V$PROCESS; -- 프로세스 정보
SELECT * FROM V$SESS_TIME_MODEL; -- 세션별 리소스 사용량
SELECT * FROM V$LOCK WHERE block = 1; -- 락 정보
SELECT * FROM V$SQL; -- 실행 중인 SQL
SELECT * FROM V$SQL_PLAN; -- SQL 실행 계획
SELECT * FROM V$SQLSTATS; -- SQL 실행 통계
SELECT * FROM V$SYSSTAT; -- 시스템 통계
SELECT * FROM V$SYSTEM_EVENT; -- 시스템 대기 이벤트
SELECT * FROM V$SYSMETRIC; -- 시스템 메트릭
SELECT * FROM V$OSSTAT; -- OS 통계
SELECT * FROM V$SGA; -- SGA 정보
SELECT * FROM V$SGASTAT;
SELECT * FROM V$PGASTAT; -- PGA 정보
SELECT * FROM V$BUFFER_POOL_STATISTICS; -- 버퍼 캐시 통계
SELECT * FROM V$FILESTAT; -- 파일별 I/O 통계
SELECT * FROM V$TEMPSTAT; -- 테이블스페이스 I/O
SELECT * FROM V$IOSTAT_FILE; -- I/O 대기 시간
SELECT * FROM V$SESSTAT WHERE STATISTIC# IN (SELECT STATISTIC# FROM V$STATNAME WHERE NAME LIKE '%CPU%'); -- CPU 사용량
SELECT * FROM V$RESOURCE_LIMIT; -- 리소스 제한
SELECT * FROM V$RESOURCE_LIMIT WHERE resource_name IN ('SESSIONS', 'PROCESSES'); -- 리소스 사용량

-- V$VIEW 응용 
  
-- 장시간 실행 중인 SQL
SELECT sess.username, sql.sql_text, sql.elapsed_time/1000000 elapsed_seconds
FROM V$SESSION sess, V$SQL sql
WHERE sess.sql_id = sql.sql_id
  AND sess.status = 'ACTIVE'
  AND sess.username IS NOT NULL
ORDER BY sql.elapsed_time DESC;

-- 장시간 실행중인 SESSION
SELECT s.sid, s.serial#, s.username, s.status,
       s.machine, s.program,
       t.sql_id, t.sql_text,
       ROUND(t.elapsed_time/1000000,2) "Elapsed Time (s)",
       t.cpu_time, t.buffer_gets,
       t.disk_reads, t.executions
FROM v$session s, v$sqlarea t
WHERE s.sql_hash_value = t.hash_value
  AND s.status = 'ACTIVE'
  AND s.username IS NOT NULL
ORDER BY t.elapsed_time DESC;

-- DEAD LOCK CHECK
SELECT vh1.sid waiting_session, vh1.blocking_session blocking_session, vs1.username waiting_user, vs2.username blocking_user, vh1.wait_class, vh1.time_waited/100 time_waited_secs, vs1.sql_id waiting_sql_id
FROM v$session_wait_history vh1,
     v$session vs1,
     v$session vs2
WHERE vh1.session_id = vs1.sid
  AND vh1.blocking_session = vs2.sid(+)
  AND vh1.wait_class != 'Idle';
  
-- HIGH I/O SQL CHECK
SELECT sql_id, disk_reads, buffer_gets, rows_processed, executions, ROUND(disk_reads/DECODE(executions,0,1,executions),2) reads_per_exec, sql_text
FROM v$sqlarea
WHERE disk_reads > 1000
ORDER BY disk_reads DESC;

-- HIGH CPU SESSION CHECK
SELECT sess.sid, sess.serial#, sess.username, sess.program, stat.value/100 cpu_usage_seconds
FROM v$session sess, v$sesstat stat, v$statname statname
WHERE sess.sid = stat.sid
  AND stat.statistic# = statname.statistic#
  AND statname.name = 'CPU used by this session'
  AND sess.username IS NOT NULL
ORDER BY stat.value DESC;
  
-- MEMORY USAGE MONITORING
SELECT name, bytes/1024/1024 MB
FROM v$sgastat
WHERE pool = 'shared pool'
ORDER BY bytes DESC;
  



-- 전체 테이블스페이스 조회
select * from DBA_TABLESPACES;

-- 데이터 파일 추가
alter tablespace QWERTY_SPACE add datafile '/datafile/path/here/DATA_01.dtf' size 10M autoextend on maxsize 30G;


-- 테이블스페이스 파일별 조회
SELECT
    A.TABLESPACE_NAME "테이블스페이스명",
    A.FILE_NAME "파일경로",
    (A.BYTES - B.FREE)/1024/1024    "사용공간(MB)",
    B.FREE/1024/1024                 "여유 공간(MB)",
    A.BYTES/1024/1024                "총크기(MB)",
    TO_CHAR( (B.FREE / A.BYTES * 100) , '999.99')||'%' "여유공간"
FROM (
    SELECT
        FILE_ID,
        TABLESPACE_NAME,
        FILE_NAME,
        SUBSTR(FILE_NAME,1,200) FILE_NM,
        SUM(BYTES) BYTES
    FROM DBA_DATA_FILES
    GROUP BY FILE_ID,TABLESPACE_NAME,FILE_NAME,SUBSTR(FILE_NAME,1,200)) A,
    (
    SELECT TABLESPACE_NAME,
        FILE_ID,
        SUM(NVL(BYTES,0)) FREE
        FROM DBA_FREE_SPACE
        GROUP BY TABLESPACE_NAME,FILE_ID) B
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
    AND A.FILE_ID = B.FILE_ID
    ORDER BY 파일경로;