BLOG ARTICLE Dev Note/DB | 10 ARTICLE FOUND

  1. 2008.03.06 Oracle에서 실행 된 쿼리문 조회하기
  2. 2008.03.03 Oracle 사용자 계정 생성
  3. 2008.03.03 Oracle Tablespace 생성

Dev Note/DB 2008.03.06 16:15

Oracle에서 실행 된 쿼리문에 대한 정보를 조회하기 위해서는 몇몇 테이블을 조인하여 Select를 하면 되며, 특정 User에 대해서도 조회가 가능하다.

SELECT
    A.SID, A.USERNAME, A.MACHINE, A.TERMINAL, C.ADDRESS, C.LAST_ACTIVE_TIME,
    C.LAST_LOAD_TIME, C.SQL_TEXT
FROM
    V$SESSION A, V$ACCESS B, V$SQL C
WHERE
    A.SERVICE_NAME = C.SERVICE AND A.SID = B.SID AND A.PREV_SQL_ID = C.SQL_ID AND C.SERVICE = A.SERVICE_NAME
    -- 특정 User에 대해 조회하기를 원할 때 추가
    AND A.USERNAME = [user]

쿼리를 실행하는 계정이 V$SESSION, V$ACCESS, V$SQL 테이블에 권한이 있어야 한다.


Dev Note/DB 2008.03.03 22:48

-- Oracle 사용자 계정 생성

CREATE USER [user_name] IDENTIFIED BY [password]
DEFAULT TABLESAPCE [tablespace_name]
TEMPORARY TABLESPACE [tablespace_name]

* 옵션 설명 *
USER : 생성 할 사용자 계정 이름을 설정한다.
IDENTIFIED BY : 사용자가 사용할 비밀번호를 설정한다.
DEFAULT TABLESPACE : 사용자가 기본으로 사용 할 tablespace를 지정한다.
TEMPORARY TABLESPACE : 사용자가 기본으로 사용할 temporary tablespace를 지정한다.

-- 등록 된 계정 목록 보기
SELECT username, user_id FROM dba_users;

-- 사용자 삭제
DROP USER [user_name];

-- 사용자 권한 설정
GRANT [role, ...] TO [user_name]

Dev Note/DB 2008.03.03 19:56

-- 오라클 테이블스페이스 생성

CREATE TABLESPACE [tablespace_name]
DATEFILE '[datafile_path]' SIZE 1000M
AUTOEXTEND OFF
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

* 옵션 설명 *
TABLESPACE : 테이블스페이스의 이름을 설정한다.
DATAFILE : 테이블스페이스를 저장 할 데이터파일의 위치와 이름을 설정한다.
SIZE : 데이터 파일의 사이즈를 설정한다.
AUTOEXTEND : 공간이 부족할 때 자동적으로 확장을 가능하게 할지 말지 결정한다.
ONLINE | OFFLINE : 테이블스페이스를 바로 사용할지 나중에 사용할지를 결정한다. (default online)
PERMANENT | TEMPORARY : 테이블스페이스에 저장 될 데이터가 영구적인 정보인지 임시 정보인지를 설정한다. (default permanent)
EXTENT MANAGEMENT DICTIONNARY | LOCAL AUTOALLOCATE :
- DICTIONARY :
  Tablespace 내의 extent들이 dictionary table에서 관리 됨.
  이 값을 지정하면 기존의 방식과 동일하게 운용되며, 이 값이 default이다.
- LOCAL :
  Tablespace 내의 extent들이 각 구성 datafile 내에서 bitmap으로 관리 됨.
  * AUTOALLOCATE : Tablespace 내의 각 extent size를 system이 관리 함.
                                      user가 extent의 size를 지정할 수 없음.
  * UNIFORM : Tablespace 내의 각 extent size는 user가 지정한 일정한 size로만 새성 됨.
                         이때 defult size는 1MB임.
  만약 LOCAL 절을 사용하면서 AUTOALLOCATE 또는 UNIFORM을 명시하지 않는다면 AUTOALLOCATE가 default로 이용 됨.
BLOCKSIZE : 인스턴스를 구성하는 블럭의 크기를 설정한다.
SEGMENT SPACE MENAGEMENT AUTO :
  - Oracle 9i부터는 PCTFREE, PCTUSED를 직접 지정하는 방법을 권하지 않고, SEGMENT SPACE MANAGEMENT AUTO 절을 넣어서 자동으로 관리하는 방법을 권장합니다.
  - 이 ASSM 방식은 segment에 할당 된 space를 bitmap으로 관리 합니다.
  - ASSM 방법을 이용하여 space를 관리하게 되면 free block에 대해서 좀 더 상세한 정보를 바탕으로 Space Utilization도 높아지고, freelist를 타고 다음 block을 access하는 대신 BMB를 참고로 적당한 block들을 선택하기 때문에 space에 관한 성능도 좋아집니다.
  - ASSM 방식을 이용하려면 반드시 LOCALLY MANAGED TABLESPACE여야 하며, 다음과 같이 SEGMENT SPACE MANAGEMENT AUTO를 지정하면 됩니다.
  - DBA_TABLESPACE view의 SEGMENT_SPACE_MANAGEMENT column을 통해 AUTO인지 MANUAL 방식인지 확인이 가능 합니다.
  - ASSM 방식으로 생성하면 BMB(BitMap Blocks)라는 것이 생기게 됩니다.
   이 BMB에는 할당 된 block들의 space정보를 4bit를 이용하여 여섯가지 상태를 나타내는 bitmap 정보를 가지고 관리를 합니다.
FLASHBACK : 특정한 과거시점의 질의를 실행할 수 있게 해준다. 데이터베이스에 구조적인 변화를 가하지 않고 과거 이정 시점의 데이타 상태를 확인할 수 있는 기능.

-- 테이블스페이스 목록 보기
SELECT tablespace_name, status, contents FROM dba_tablespaces;
-- 테이블스페이스 파일 목록 보기
SELECT file_name, bytes, status FROM dba_data_files;
-- 테이블스페이스의 사용 가능한 공간 보기
SELECT tablespace_name, bytes, blocks FROM dba_free_space;
-- 테이블스페이스 삭제
DROP TABLESPACE [tablespace_name];

-- 테이블 스페이스 Quota 확인
SELECT * FROM user_ts_quotas;
TABLESPACE_NAME      BYTES               MAX_BYTES      BLOCKS       MAX_BLOCKS    DRO

 ---------------------------------------------------------------------------------------------------------------------------
 TS_Name                         931463168       -1                         113704         -1                           NO
// 여기서 -1은 무제한을 의미한다.
-- 테이블 스페이스 Quota 설정
ALTER USER [USER_NAME] QUOTA [SIZE(100M)] ON [TABLESPACE_NAME];    // 100M로 설정
ALTER USER [USER_NAME] QUOTA UNLIMITED ON [TABLESPACE_NAME];      // 무제한 설정