이것저것
USER_OBJECTS 모든 USER 오브젝트들
USER_TABLES 테이블 정보
USER_TAB_COLUMNS(COLS) 테이블에 있는 컬럼들의 정보
USER_VIEWS 뷰 정보
USER_SYNONYMS(SYN) 시노님 정보
USER_SEQUENCES(SEQ) 시퀀스 정보
USER_CONSTRAINTS 각종 제약 조건
USER_CONS_COLUMNS 제약 조건을 가진 컬럼들에 대한 정보
USER_TAB_COMMENTS 테이블 주석 정보
USER_COL_COMMENTS 커럼 주석 정보
USER_INDEXES(IND) 인덱스 정보
USER_IND_COLUMNS 인덱스 컬럼 정보
USER_CLUSTERS(CLU) 클러스터 정보
USER_DB_LINKS 디비 링크 정보
USER_TRIGGERS 트리거 정보
USER_PROCEDURES 프로시저, 함수, 패키지 정보
USER_SOURCE 트리거, 함수, 프로시저, 패키지 소스
USER_TABLESPACES 테이블 스페이스 정보
USER_USERS 사용자 정보
USER_TAB_PRIVS 테이블 권한 정보
USER_COL_PRIVS 컬럼 권한 정보
USER_SYS_PRIVS 시스템 권한 정보
USER_TAB_PRIVS_MADE 내가 부여한 테이블 권한
USER_TAB_PRIVS_RECD 내가 부여받은 테이블 권한
USER_COL_PRIVS_MADE 내가 부여한 컬럼 권한
USER_COL_PRIVS_RECD 내가 부여받은 컬럼 권한
/*같은 DB내 다른 USER TABLE 접근 권한 */
GRANT SELECT ON "CCM"."EM_TRAN" TO CFM;
GRANT INSERT ON "CCM"."EM_TRAN" TO CFM;
/* 권한 해제 */
REVOKE SELECT ON "CCM"."EM_TRAN" FROM CFM;
REVOKE INSERT ON "CCM"."EM_TRAN" FROM CFM;
/*sqlplus 서비스 올리기 */-----------------------------------------------------------------------
[oracle@KRAdb]$ sqlplus
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup
/*
서비스내릴때 : shutdown immediate;(데이터베이스를 언마운트 하는작업이라 시간이 걸립니다.)
서비스올릴때 : startup;
*/
/*리스너 스타트 */-------------------------------------------------------------------------------
[oracle@KRAdb]$ lsnrctl start
--oracle 데이타 저장되는 최소 단위 순서
block -> extent -> segment -> tablespace -> database -> dataware house
※segment : table 또는 index 들의 최소 할당 단위.....
--MySql 데이터 저장 단위 순서...
page(8KB) -> extent(8page) -> table -> database
※extent : table 또는 index 들의 최소 할당 단위.....
page, block : 데이타 이동의 최소 단위
----ms sql--------------|-------oracle(9i)---------------------
자동 균일 증가 | 1block = (2,4,8,16,32KB)
1page = 8KB | 테이블 생성시 지정해 줄 수 있다...
1extent = 8pages = 64KB | 1extent = 8blocks
------------------------|--------------------------------------
sqlplus "&" <-- 치환
해당 접속 세션에서
set define off
후
data transection
/* LOCK 걸린 유저 검색
SELECT USERNAME, ACCOUNT_STATUS, LOCK_DATE
FROM DBA_USERS;
-- LOCK 풀기 (user:cntis일때)
ALTER USER CNTIS ACCOUNT UNLOCK;
/* 현재 데이타베이스 서버에 접속하고 있는 사용자(터미널) 검색하기 */------------------------------
SELECT * FROM V$SESSION;
/* 현재 접속하고 있는 세션 강제 죽이기 */---------------------------------------------------------
ALTER SYSTEM KILL session 'SID, SERIAL#'; --문법
ALTER SYSTEM KILL session '143, 559'; --예제
/* 테이블 스페이스 생성 */------------------------------------------------------------------------ -- dbf 파일 생성될 폴더가 사전에 준비되어야함.
create tablespace salestbs
datafile 'D:\oracle\product\10.2.0\oradata\TDMS\TDMS.dbf' size 500m
extent management local --EXTENT관리를 LOCAL 오라클 서버에서 자동으로 하게 함..
segment space management auto; --SEGMENT 공간관리도 자동으로 관리하게 함...
/* 테이블 스페이스 공간자동증가 */---------------------------------------------------------------
alter database datafile 'D:\oracle\product\10.2.0\oradata\TDMS\TDMS.dbf' autoextend on next 100M;
/* 테이블 스페이스 정보 */----------------------------------------------------------------------
select file_name, tablespace_name, bytes, autoextensible from dba_data_files
where tablespace_name='TS_AAAAAA';
/* 사용자 생성 및 권한 설정 */--------------------------------------------------------------------
create user USER_ID identified by USER_PWD default tablespace TABLE_SPACE_NAME;
grant resource, connect to USER_ID; --일반적인권한부여시
grant dba, resource, connect to USER_ID; --dba권한부여시
GRANT EXECUTE ON DBMS_CRYPTO TO USER_ID; --DBMS_CRYPTO권한부여시
/* 테이블 스페이스 및 파일 위치 확인하기 */-------------------------------------------------------
select * from dba_tablespaces; --개략적인 정보
select file_id, file_name, tablespace_name, bytes, status from dba_data_files; --구체적인 정보
desc dba_tablespaces; --테이블스페이스정보테이블의 구조조회
/* TEMP 테이블스페이스 정보 및 구조*/-------------------------------------------------------------
select file_id, file_name, tablespace_name, bytes, status from dba_temp_files; --정보조회
desc dba_temp_files; --temporary tablespace; --구조 조회
/* 테이블 스페이스 삭제 */------------------------------------------------------------------------
DROP tablespace CNTIS including contents;
/* 현재 사용중인 테이블스페이스 파일 공간 확인하기 */---------------------------------------------
select a.tablespace_name,
a.file_name,
a.bytes,
b."free byte",
(a.bytes - b."free byte") "사용중인 공간"
from dba_data_files a, (select tablespace_name, sum(bytes) "free byte"
from dba_free_space
group by tablespace_name
) b
where a.tablespace_name = b.tablespace_name
/* ATAMS라는 테이블스페이스 대한 모든 소유자들과 사용량 등 확인 */--------------------------------
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, BYTES, BLOCKS FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'ATAMS'
/* 추가로 사용할 수 있는 테이블스페이스 용량 */---------------------------------------------------
select tablespace_name, max_bytes - bytes "Free bytes" from user_ts_quotas
/* 계정에 할당 된 디스크 쿼터량 확인하기 */-------------------------------------------------------
SELECT * FROM USER_TS_QUOTAS;
/* 테이블스페이스 총 할당 크기 */-----------------------------------------------------------------
select * from user_tablespaces;
/* 사용 할 수 있는 빈 공간 테이블 스페이스 크기 */------------------------------------------------
select * from user_free_space;
/* 일반 사용자가 만든 세그먼트들 검색 */----------------------------------------------------------
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'ATAMS'
AND OWNER IN ( SELECT USERNAME --USER_ID가 60이상이면 일반사용자임...
FROM DBA_USERS
WHERE USER_ID > 60 OR USERNAME = 'ATAMS' );
/* 디폴트 테이블 스페이스 변경 */-----------------------------------------------------------------
ALTER USER SCOTT DEFAULT TABLESPACE USERS;
/* 일반사용자가 만든 세그먼트들을 유저스 네임스페이스로 옮기기 */---------------------------------
--하나하나 옮기기
ALTER TABLE SCOTT.DEPT MOVE TABLESPACE USERS
ALTER TABLE SCOTT.EMP MOVE TABLESPACE USERS
ALTER TABLE SCOTT.MEMBER MOVE TABLESPACE USERS
ALTER TABLE SCOTT.JUMUNOLD MOVE TABLESPACE USERS
ALTER TABLE SCOTT.JIKCODE MOVE TABLESPACE USERS
ALTER TABLE SCOTT.JIKWON MOVE TABLESPACE USERS
ALTER TABLE SCOTT.SALGRADE MOVE TABLESPACE USERS
ALTER TABLE SCOTT.BONUS MOVE TABLESPACE USERS
--결과 검색
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'USERS'
/* 권한 별도 설정 */------------------------------------------------------------------------------
--scott라는 USER의 각종 권한 설정 예)user생성권한 수정권한 삭제권한 주기
GRANT CREATE USER, ALTER USER, DROP USER TO scott
GRANT select, update, insert, delete on TABLE_NAME to USER_ID; --단일 테이블에 권한 부여
GRANT CREATE SESSION TO ORAUSER1; --로그인 할 수 있는(세션생성) 권한 주기
GRANT CREATE TABLE TO ORAUSER1; --CREATE TABLE 할 수 있는 권한 주기
/* 현재 세션유저의 부여된 권한 조회 */------------------------------------------------------------
select * from session_privs
where privilege like '%CREATE%'
/* 유저 계정 권한의 종류 확인 */------------------------------------------------------------------
select * from system_privilege_map where name like '%CREATE%';
/* 유저에 대한 시스템권한 확인 */-----------------------------------------------------------------
SELECT * FROM DBA_SYS_PRIVS
--WHERE GRANTEE='ORAUSER1'
/* 유저 생성 */-----------------------------------------------------------------------------------
create user CNTIS identified by vkaxn; --현재 테이블스페이스에 생성
create user CNTIS identified by vkaxn default tablespace CNTIS; --테이블스페이스지정하여생성 -- 생성시 상세조건 주기
CREATE USER ORAUSER2 IDENTIFIED BY TPDLF
DEFAULT TABLESPACE SALESTBS
TEMPORARY TABLESPACE TEMP
QUOTA 5M ON SALESTBS --SALESTBS 테이블스페이스는 5MB 할당
QUOTA UNLIMITED ON TEMP --임시 테이블스페이스는 모두 할당
/* 유저의 오브젝트 조회(table,index,시퀀스,뷰,프로시저,트리거,펑션 등등) */-----------------------
SELECT * FROM DBA_OBJECTS WHERE OWNER='CNTIS';
/* 유저삭제 */------------------------------------------------------------------------------------
DROP USER CNTIS CASCADE; -- 하위 오브젝트 포함 삭제시
DROP USER ORAUSER1; -- 해당 계정소유의 오브젝트가 있다면 바로 삭제가 불가
/* 오브젝트 정보 및 유저 소유의 삭제테이블 */-----------------------------------------------------
DESC DBA_OBJECTS; --DBA오브젝트 정보
DROP TABLE ORAUSER1.MYTAB; --삭제할 계정소유의 테이블 먼저 삭제함...
/* 유저비밀번호 변경 */---------------------------------------------------------------------------
ALTER USER scott IDENTIFIED BY lion;
/* 유저의 테이블 정보 및 구조 조회 */-------------------------------------------------------------
select * from dba_users --유저테이블 정보(일반유저사용불가)
--where username = upper('orauser1');
SELECT * FROM USER_USERS; --자기 소유의 내용만 조회 가능함 (일반계정도 사용 가능 - 자기 정보 조회)
--기본테이블스페이스가 system으로 잡혀있는 사용자가 테이블을 만들면 그 테이블들은
system 테이블스페이스에서 만들어지므로 성능의 저하가 발생됨
(시스템이 기본정보를 저장/조회하는 영역으므로 일반 사용자도 이곳을 사용하면 DISK IO 가 많아짐
desc dba_users; --유저테이블 구조
/* 특정 사용자가 만든 테이블 조회하기(테이블 대 테이블스페이스 정보확인) */-----------------------
SELECT * FROM DBA_TABLES WHERE OWNER = 'ORAUSER1'; --DBA권한
SELECT * FROM USER_TABLES; --일반 유저도 조회 가능함 (자기계정 확인)
/* 계정생성 순서 */-------------------------------------------------------------------------------
-> 계정생성 -> 세션(로긴)허용 -> 해당테이블스페이스 쿼터 할당
ALTER USER ORAUSER1 --SYSTEM TABLESPACE 에서 사용자에게 2MB 할당해주기
QUOTA 2M ON SYSTEM -- 10M, 5M, UNLIMITED 등등 할당 할 수 있음
/* 사용자의 생성에 대한 설명 */-------------------------------------------------------------------
새로운 USER를 생성하기 위해서는 CREATE USER문을 이용하면 됩니다.
USER를 생성하기 위해서는 USER생성 권한이 있는 사용자로 접속해야 합니다.
- user_name : 사용자 이름
- BY password : 사용자가 데이터베이스에 의해 인증되도록 지정하며,
데이터베이스 유저 로그온시 사용하는 비밀번호 입니다.
- EXTERNALLY : 사용자가 운영 체제에 의해서 인증되도록 지정합니다.
- DEFAULT TABLESPACE는 사용자 스키마를 위한 기본 테이블 스페이스를 지정 합니다.
- TEMPORARY TABLESPACE는 사용자의 임시 테이블 스페이스를 지정합니다.
- QUOTA절을 사용하여 사용자가 사용할 테이블 스페이스의 영역을 할당합니다.
- PASSWORD EXPIRE : 사용자가 SQL*PLUS를 사용하여 데이터베이스에 로그인할 때
암호를 재설정하도록 합니다.(사용자가 데이터베이스에 의해 인증될 경우에만 적합한 옵션입니다.)
- ACCOUNT LOCK/UNLOCK : 사용자 계정을 명시적으로 잠그거나 풀 때 사용할 수 있습니다.
(UNLOCK이 기본값입니다.)
- PROFILE: 자원 사용을 제어하고 사용자에게 사용되는 암호 제어 처리 방식을 지정하는데 사용됩니다.
/* DATA DICTIONARY 항목 보기 */-------------------------------------------------------------------
SELECT * FROM DICTIONARY;
/* 조건에 맞는 DIC 항목 보기 */-------------------------------------------------------------------
예제1)
SELECT * FROM DICT
WHERE UPPER(COMMENTS) LIKE '%USER%'
AND TABLE_NAME LIKE 'DBA_%';
예제2)
SELECT * FROM DICT
WHERE UPPER(COMMENTS) LIKE '%SYSTEM PRIVILEGE%'
AND TABLE_NAME LIKE 'USER_%';
/* TRIWEB 라는 유저의 table 오브젝트 검색 */
SELECT * FROM DBA_OBJECTS WHERE OWNER='TRIWEB'
AND OBJECT_TYPE = 'TABLE'
;
/* TRIWEB 라는 유저의 table 오브젝트와 코멘트 테이블명 순으로 order by 검색 */
SELECT
ta.TABLE_NAME,
tb.COMMENTS TABLE_NAME_KOR
FROM
USER_TABLES ta,
ALL_TAB_COMMENTS tb
WHERE
ta.TABLE_NAME = tb.TABLE_NAME
AND tb.OWNER='TRIWEB'
ORDER BY ta.TABLE_NAME
;
/* 휴지통에 있는 객체들 보기 */
select * from user_recyclebin;
select * from dba_recyclebin;
/* 휴지통 비우기 */
purge recyclebin; --사용자의 휴지통 비우기
purge dba_recyclebin; --휴지통내의 모든 놈들 비우기
purge tablespace users; --users테이블스페이스의 휴지통 비우기
purge table "휴지통내의 이름"; --휴지통의 하나의 객체만 제거시
/* 휴지통의 객체 복구 */
flashback table "휴지통내의 이름" to before drop;
/* 휴지통에 넣지 않고 drop */
drop table 테이블명 purge;
/* drop table ddl 만들기 */
SELECT
('DROP TABLE '||OWNER||'.'||OBJECT_NAME||' CASCADE CONSTRAINTS;') ddl
FROM DBA_OBJECTS WHERE OWNER='TRIWEB'
AND OBJECT_TYPE = 'TABLE'
;
==================================================================================================
### 테이블 설명 ###
USER_SYS_PRIVS --시스템 권한 부여 확인
USER_TABLES --해당 계정 테이블에 대한 정보 확인
USER_USERS --사용자 계정 정보 확인
USER_ROLE_PROVS --자기 롤 확인
USER_TS_QUOTAS --자기 테이블스페이스 쿼타 확인
==================================================================================================
### 참고 및 예제 자료 ###
[참고1]
- 임시 테이블스페이스를 지정해 주지 않으면 시스템 테이블스페이스가 기본으로 지정 되지만
시스템 테이블스페이스에 단편화가 발생할 수 있으므로 사용자를 생성할때 임시테이블스페이스를
따로 지정해 주는 것이 좋습니다.
- 또한 DEFAULT TABLESPACE도 사용자를 생성할때 지정해 주지 않으면 기본적으로 시스템
테이블스페이스가 지정이 됩니다. 하지만 사용자를 생성할때 DEFAULT TABLESPACE를 지정을 해서
사용자가 소유한 데이터와 객체들의 저장 공간을 별도로 관리를 해야 합니다.
- 시스템 테이블스페이스는 본래의 목적(모든 데이터 사전 정보와, 저장 프로시저, 패키지,
데이터베이스 트리거등을 저장)을 위해서만 사용되어져야 하지 일반사용자의 데이터 저장용으로
사용 되어서는 안됩니다.
[참고2]
테이블 스페이스란 ?
- 오라클 서버가 테이터를 저장하는 논리적인 구조입니다.
- 테이블스페이스는 하나 또는 여러개의 데이터파일로 구성되는 논리적인 데이터 저장 구조입니다.
[ALTER USER문으로 변경 가능한 옵션]
- 비밀번호
- 운영체제 인증
- 디폴트 테이블 스페이스
- 임시 테이블 스페이스
- 테이블 스페이스 분배 할당
- 프로파일 및 디폴트 역할
[사용자 수정 예제]
SQL>CONN SYSTEM/MANAGER -- SYSTEM USER로 접속합니다.
SQL>ALTER USER scott IDENTIFIED BY lion; -- scott USER의 비밀번호를 수정합니다.
사용자가 변경되었습니다.
SQL>conn scott/lion -- scott USER의 비밀번호가 바낀걸 확인할 수 있습니다.
접속되었습니다.
SQL>conn system/manager
접속되었습니다.
SQL>ALTER USER scott IDENTIFIED BY tiger; -- scott USER의 비밀번호를 처음처럼 수정합니다.
사용자가 변경되었습니다.
[사용자 삭제]
※ CASCADE를 사용하게 되면 사용자 이름과 관련된 모든 데이터베이스 스키마가 데이터 사전으로부터
삭제되며 모든 스키마 객체들 또한 물리적으로 삭제 됩니다.
[사용자 정보의 확인]
데이터베이스에 등록된 사용자를 조회하기 위해서는 DBA_USERS라는 데이터사전을 조회하면 됩니다.
SQL*Plus를 실행시켜 system/manager로 접속을 합니다.(비번사용자정의시 예:system/dbma)
SQL>SELECT username, default_tablespace, temporary_tablespace
FROM DBA_USERS;
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLES
---------------- ------------------- ----------------
SYS SYSTEM TEMP
SYSTEM TOOLS TEMP
OUTLN SYSTEM SYSTEM
DBSNMP SYSTEM SYSTEM
ORDSYS SYSTEM SYSTEM
ORDPLUGINS SYSTEM SYSTEM
MDSYS SYSTEM SYSTEM
CTXSYS DRSYS DRSYS
SCOTT SYSTEM SYSTEM
TEST TEST SYSTEM
STORM STORM SYSTEM
KJS SYSTEM SYSTEM
OEM OEM_REPOSITORY TEMP
위와 같이 유저와 테이블 스페이스에 대한 정보가 화면에 나옵니다.
오라클 시스템 권한에 대한 설명입니다. -
오라클에서 권한(Privilege)은 특정 타입의 SQL문을 실행하거나 데이터베이스나 데이터 베이스
객체에 접근할 수 있는 권리입니다.
[SYSTEM PRIVILEGES]
- 시스템 권한은 사용자가 데이터베이스에서 특정 작업을 수행 할 수 있도록 합니다
- 약 126개의 시스템 권한이 있으며 그 수는 계속 증가하고 있습니다.
- 권한의 ANY 키워드는 사용자가 모든 스키마에서 권한을 가짐을 의미 합니다.
- GRANT 명령은 사용자 또는 Role에 대해서 권한을 부여 합니다.
- REVOKE 명령은 권한을 삭제 합니다.
[시스템 권한의 종류 몇가지 ]
- CREATE SESSION : 데이터 베이스를 연결할 수 있는 권한
- CREATE ROLE : 오라클 데이터베이스 역할을 생성할 수 있는 권한
- CREATE VIEW : 뷰의 생성 권한
- ALTER USER : 생성한 사용자의 정의를 변경할 수 있는 권한
- DROP USER : 생성한 사용자를 삭제시키는 권한
- system_privilege : 부여할 시스템 권한의 이름
- role : 부여할 데이터베이스 역할의 이름
- user, role : 부여할 사용자 이름과 다른 데이터 베이스 역할 이름
- PUBLIC : 시스템 권한, 또는 데이터베이스 역할을 모든 사용자에게 부여할 수 있습니다.
- WITH ADMIN OPTION : 권한을 부여 받은 사용자도 부여 받은 권한을 다른 사용자 또는 역할로
부여할 수 있게 되며, 만약 사용자가 WITH ADMIN OPTION과 같이 역할을 부여 받는다면 부여된 역할은
그 사용자에 의해 변경 또는 삭제 될 수 있습니다.
[시스템 권한 부여 예제]
SQL>GRANT CREATE USER, ALTER USER, DROP USER TO scott
WITH ADMIN OPTION.
권한이 부여되었습니다.
*설명 : scott 사용자에게 사용자를 생성, 수정, 삭제 할 수 있는 권한을 부여하고,
scott 사용자도 다른 사용자에게 그 권한을 부여 할 수 있습니다.
[시스템 권한 철회 예제]
SQL>REVOKE CREATE USER, ALTER USER, DROP USER
FROM scott
권한이 회수되었습니다.
*설명 : scott 사용자에게 부여한 생성, 수정, 삭제 권한을 회수합니다,
WITH ADMIN OPTION을 사용하여 시스템 권한 취소
WITH ADMIN OPTION을 사용하여 시스템 권한을 부여했어도 시스템 권한을 취소 할 때는 연쇄적으로
취소 되지 않습니다.
[시나리오]
1. DBA가 STORM에게 WITH ADMIN OPTION을 사용하여 CREATE TABLE 시스템 권한을 부여 합니다.
2. STORM이 테이블을 생성 합니다.
3. STORM이 CREATE TABLE 시스템 권한을 SCOTT에게 부여 합니다.
4. SCOTT가 테이블을 생성 합니다.
5. DBA가 STORM에게 부여한 CREATE TABLE 시스템 권한을 취소 합니다.
결과
- STORM의 테이블은 여전히 존재하지만 새 테이블을 생성할 수 있는 권한은 없습니다.
- SCOTT는 여전히 테이블과 새로운 테이블을 생성 할 수 있는 CREATE TABLE권한을 가지고 있습니다.
-----------------------
-- 현재 쿼리 사용상태
select *
from (
select /*+ LEADING(u) USE_HASH(u s) */
u.username,
round(nvl(ratio_to_report(CPU_TIME) over (),0),3)*100 as CPU이용율,
sql_fulltext 쿼리,
EXECUTIONS 실행수,
ROWS_PROCESSED ROWSs,
DISK_READS,
PARSE_CALLS 커서호출수,BUFFER_GETS , FETCHES,
round(decode(executions,null,0,0,0,(nvl(ELAPSED_TIME,0)/executions)/ 1000000),5) 평균실행시간,
round(decode(executions,null,0,0,0,(nvl(CLUSTER_WAIT_TIME + USER_IO_WAIT_TIME + CONCURRENCY_WAIT_TIME + APPLICATION_WAIT_TIME,0)/executions)/ 1000000),5) 평균대기시간,
round(decode(executions,null,0,0,0,(nvl(CPU_TIME,0)/executions)/ 1000000),5) 평균CPU타임,
--round(decode(executions,null,0,0,0,(nvl(CLUSTER_WAIT_TIME,0)/executions)/ 1000000),5) CLUSTER_WAIT_PER_EXEC,
round(decode(executions,null,0,0,0,(nvl(USER_IO_WAIT_TIME,0)/executions)/ 1000000),5) 평균IO대기시간,
round(decode(executions,null,0,0,0,(nvl(CONCURRENCY_WAIT_TIME,0)/executions)/ 1000000),5) 평균경합대기시간,
--round(decode(executions,null,0,0,0,(nvl(APPLICATION_WAIT_TIME,0)/executions) / 1000000),5) APPLICATION_WAIT_PER_EXEC,
round(decode(executions,null,0,0,0,(nvl(buffer_gets,0)/executions)),0) 평균메모리블럭수,
round(decode(BUFFER_GETS,0,1,(BUFFER_GETS - DISK_READS)/BUFFER_GETS),1) BUF_HIT_RATIO,
--CPU_TIME,ELAPSED_TIME, CLUSTER_WAIT_TIME,USER_IO_WAIT_TIME , CONCURRENCY_WAIT_TIME, APPLICATION_WAIT_TIME, PLSQL_EXEC_TIME,JAVA_EXEC_TIME,
RANK() OVER (ORDER BY CPU_TIME DESC) cpu_usage_rank,
s.module
from dba_users u, v$sql s, v$session c
where s.parsing_user_id > 5
and s.parsing_user_id = u.user_id
and c.sql_address = s.address
)
where cpu_usage_rank <= 100;
-------------------------
-- 현재 실행중인 세션(쿼리) 확인, 죽이기
SELECT
'ALTER SYSTEM KILL SESSION ''' || S.SID || ', ' || S.SERIAL# || ''';'
, S.PROCESS, P.SPID, S.STATUS, S.SID, S.SERIAL#, S.TYPE, S.USERNAME
, S.OSUSER, S.MACHINE, S.MODULE, A.SQL_TEXT
FROM V$SQL A, V$SESSION S, V$PROCESS P, SYS.V_$SESS_IO SI
WHERE
S.PADDR=P.ADDR(+)
AND SI.SID(+)=S.SID
AND (S.USERNAME IS NOT NULL)
AND (NVL (S.OSUSER, 'X') <> 'SYSTEM')
--AND (S.TYPE <> 'BACKGROUND')
AND A.ADDRESS=S.SQL_ADDRESS;
-- oracle - 현재 실행중인 쿼리 확인하기
select a.osuser, a.status, b.sql_text
from v$session a, v$sqlarea b
where a.sql_address = b.address;
--1. Buffer Cache Hit Ratio
SELECT ROUND(((1-(SUM(DECODE(name, 'physical reads', value,0))/
(SUM(DECODE(name, 'db block gets', value,0))+
(SUM(DECODE(name, 'consistent gets', value, 0))))))*100),2) || '%' "Buffer Cache Hit Ratio"
FROM V$SYSSTAT;
--2. Library Cache Hit Ratio
SELECT (1-SUM (reloads)/SUM(pins))*100 "Library Cache Hit Ratio"
From V$LIBRARYCACHE;
--3. Data Dictionary Cache Hit Ratio
SELECT (1-SUM(getmisses)/SUM(gets))*100 "Data Dictionary Hit Ratio"
FROM V$ROWCACHE;
-- 테이블 스페이스 사용량
SELECT a.tablespace_name,
a.total "Total(Mb)",
a.total - b.free "Used(Mb)",
nvl(b.free,0) "Free(Mb)",
round((a.total - nvl(b.free,0))*100/total,0) "Used(%)"
from ( select tablespace_name,
round((sum(bytes)/1024/1024),0) as total
from dba_data_files
group by tablespace_name) a,
( select tablespace_name,
round((sum(bytes)/1024/1024),0) as free
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
order by a.tablespace_name;
--오라클서버의 메모리
select * from v$sgastat
select pool, sum(bytes) "SIZE"
from v$sgastat
where pool = 'shared pool'
group by pool
--cpu를 많이 사용하는 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기
select c.sql_text
,b.SID
, b.SERIAL#
,b.machine
,b.OSUSER
,b.logon_time --이 쿼리를 호출한 시간
from v$process a, v$session b, v$sqltext c
where a.addr = b.paddr
and b.sql_hash_value = c.hash_value
--and a.spid = '675958'
order by c.PIECE
--cpu를 많이 사용하는 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기
select c.sql_text
from v$process a, v$session b, v$sqltext c
where a.addr = b.paddr
and b.sql_hash_value = c.hash_value
and a.spid = '171'
order by c.PIECE
--프로세스 아이디를 이용하여 쿼리문 알아내기
select c.sql_text
,b.SID
, b.SERIAL#
,b.machine
,b.OSUSER
,b.logon_time --이 쿼리를 호출한 시간
from v$process a, v$session b, v$sqltext c
where a.addr = b.paddr
and b.sql_hash_value = c.hash_value
and a.spid = '1708032' --1912870/
order by c.PIECE
--세션 죽이기(SID,SERAIL#)
--ALTER SYSTEM KILL SESSION '8,4093'
--오라클 세션과 관련된 테이블*/
--select count(*) from v$session where machine ='머신이름' and schemaname ='스키마이름'
--현재 커서 수 확인
SELECT sid, count(sid) cursor
FROM V$OPEN_CURSOR
WHERE user_name = 'ilips'
GROUP BY sid
ORDER BY cursor DESC
SELECT sql_text, count(sid) cnt
FROM v$OPEN_CURSOR
GROUP BY sql_text
ORDER BY cnt DESC
select * from v$session_wait
select sid, serial#, username, taddr, used_ublk, used_urec
from v$transaction t, v$session s
where t.addr = s.taddr;
select * from sys.v_$open_cursor
--V$LOCK 을 사용한 잠금 경합 모니터링
SELECT s.username, s.sid, s.serial#, s.logon_time,
DECODE(l.type, 'TM', 'TABLE LOCK',
'TX', 'ROW LOCK',
NULL) "LOCK LEVEL",
o.owner, o.object_name, o.object_type
FROM v$session s, v$lock l, dba_objects o
WHERE s.sid = l.sid
AND o.object_id = l.id1
AND s.username IS NOT NULL
--락이 걸린 세션 자세히 알아보기
select a.sid, a.serial#,a.username,a.process,b.object_name,
decode(c.lmode,2,'RS',3,'RX',4,'S',5,'SRX',8,'X','NO') "TABLE LOCK",
decode (a.command,2,'INSERT',3,'SELECT',6,'UPDATE',7,'DELETE',12,'DROP TABLE',26,'LOCK TABLE','UNknown') "SQL",
decode(a.lockwait, NULL,'NO wait','Wait') "STATUS"
from v$session a,dba_objects b, v$lock c
where a.sid=c.sid and b.object_id=c.id1
and c.type='TM'
--락이 걸린 세션 간단히 알아보기
select a.sid, a.serial#, b.type, c.object_name, a.program, a.lockwait,
a.logon_time, a.process, a.osuser, a.terminal
from v$session a, v$lock b, dba_objects c
where a.sid = b.sid
and b.id1 = c.object_id
and b.type = 'TM';
select a.sid, a.serial#, a.username, a.process, b.object_name
from v$session a , dba_objects b, v$lock c
where a.sid=c.sid and b.object_id = c.id1
and c.type = 'TM'
--락이 걸린 세션을 찾아 내어 세션을 죽이려고 해도 죽지 않는 경우
--아래 쿼리문으로 OS단의 PROCESS ID를 찾아내어 OS에서 죽인다
--kill -9 프로세스아이디
select substr(s.username,1,11) "ORACLE USER", p.pid "PROCESS ID",
s.sid "SESSION ID", s.serial#, osuser "OS USER",
p.spid "PROC SPID",s.process "SESS SPID", s.lockwait "LOCK WAIT"
from v$process p, v$session s, v$access a
where a.sid=s.sid and
p.addr=s.paddr and
s.username != 'SYS'
--위 쿼리문의 결과가 있다면 락이 걸린 세션이 있다는것이므로 아래의 쿼리문으로 세션을 죽인다
ALTER SYSTEM KILL SESSION '11,39061'
alter session으로 죽지않는 프로세스 죽이기
1.oracle이 설치된 서버에 텔넷으로 root로 접속한다
2.su -오라클계정
3.sqlplus '/as sysdba''
4.connect system/sys
5.ALTER SYSTEM KILL SESSION '137,1723'
-----------------------------
--테이블 크기 확인
SELECT OWNER, TABLESPACE_NAME, TABLE_NAME, NUM_ROWS*AVG_ROW_LEN/1024/1024 as MB
FROM DBA_TABLES
WHERE OWNER IN ('DBADMIN')
ORDER BY OWNER, TABLESPACE_NAME, TABLE_NAME;
--인덱스 크기 확인
SELECT OWNER, TABLESPACE_NAME, INDEX_NAME, LEAF_BLOCKS*8192/1024/1024 as MB
FROM DBA_INDEXES
WHERE OWNER IN ('DBADMIN')
ORDER BY OWNER, TABLESPACE_NAME, INDEX_NAME;
--
ANALYZE TABLE THR_YYCOURSECORP COMPUTE STATISTICS;