오라클 9i / 10g용 관리 명령어(sql) 요약

순전히 이기적인 용도로 사용하기 위해 대충 만든 요약본입니다.
필요하신 분은 유용하게 사용하세요.
혹시나 옮겨가실 분은 댓글이라도 달아주세요. ^^

작성자 : mirsya
http://mirsya.tistory.com
최종수정 2007년 2월 18일 일요일

※틀린 부분이나 수정이 필요 한 부분은 가차없이 알려주세요.
--------------------------------------------------------

Oracle 9i / 10g 관리자 명령어 요약

1. Startup / Shutdown
2. Session
3. Parameter File
4. Control File
5. Redo Log
6. Tablespace
7. Temporary Tablespace
8. Undo Tablespace
9. Database Buffer Cache
10. Row Migration / Chaining
11. Partitioned Table
12. Deferred Constraints
13. User
14. Profile
15. Privileges
16. Role
17. Export
18. Import
19. Direct Load
20. Oracle Net - Host Naming
21. Oracle Net - Local Naming
22. Create DB - 9i
23. Create DB - 10g
24. Archive Log

STARTUP / SHUTDOWN

STARTUP

startup [ nomount | mount | open [ read only ]]

SHUTDOWN

shutdown [ immediate | transactional | normal | abort ]

상태 변경

alter database [ mount | open [ read only ]];

STARTUP 상태 조회

select status from v$instance;

OPEN 상태 조회

select open_mode from v$database;

>>Index<<

SESSION

제한 상태로 변경

alter system enable restricted session;

제한 상태 조회

select logins from v$instance;

사용자 SESSION 조회

select sid, serial#, username, status from v$session;

사용자 SESSION 강제종료

alter system kill session 'SID,SERIAL#';

RESTRICTED SESSION 권한 조회

select * from dba_sys_privs where privilege like '%RESTRICT%';

>>Index<<

PARAMETER FILE

파일구분

spfile : binaryfile, open 상태에서 수정

pfile : textfile, shutdown 상태에서 수정

파일생성

shutdown 상태에서 수행

create spfile from pfile;

create pfile from spfile;

SPFILE

alter system set parameter_name = 'value' [ comment 'text' ]

[ scope = memory | spfile | both ] [ sid = 'sid' | '*' ];

PFILE

shutdown 상태에서 편집기로 편집

>>Index<<

CONTROL FILE

SPFILE 사용시

open 상태에서 명령수행
alter system set control_files = '경로1', '경로2' scope = spfile;
콘트롤 파일 복사 후 DB 재기동

PFILE 사용시

shutdown 상태에서 pfile 파라미터 수정
콘트롤 파일 복사 후 DB 기동

>>Index<<

REDO LOG

LOGSWITCH

현재 사용하는 로그파일을 변경

alter system switch logfile;

CHECKPOINT

active 상태의 로그파일을 inactive로 변경

alter system checkpoint;

LOG FILE 상태 조회

select a.group#, a.member, b.bytes, b.status
from v$logfile a, v$log b
where a.group# = b.group#;

GROUP 추가

alter database add logfile group 그룹번호 '파일경로' size 크기;

alter database add logfile group 그룹번호 ('파일경로1', '파일경로2') size 크기;

MEMBER 추가

alter database add logfile member '파일경로' to group 그룹번호;

GROUP / MEMBER 삭제

alter database drop logfile group 그룹번호;

alter database drop logfile member '파일경로';

※삭제 명령시 파일은 삭제되지 않음

>>Index<<

TABLESPACE

TABLESPACE 조회

select tablespace_name, status, contents, extent_management, segment_space_management
from dba_tablespaces;

DATAFILE 조회

select tablespace_name, bytes, file_name from dba_data_files;

TEMPFILE 조회

select tablespace_name, bytes, file_name from dba_temp_files;

일반 TABLESPACE 생성

create tablespace 테이블스페이스명 datafile '파일경로' size 크기
[ blocksize 크기] // 해당 블럭 사이즈의 db_nk_cache_size 설정 필요
[ extent management local ] // 8i 이전 필수 옵션
[ segment space management auto ] // 9i 이후 필수 옵션;

UNDO TABLESPACE 생성

create undo tablespace 테이블스페이스명 datafile '파일경로' size 크기;

TEMPORARY TABLESPACE 생성

create temporary tablespace 테이블스페이스명 tempfile '파일경로' size 크기;

TABLESPACE 확장

alter tablespace 테이블스페이스명 add datafile '파일경로' size 크기;

alter database datafile '파일경로' resize 크기;

TABLESPACE 관리

alter tablespace 테이블스페이스명 offline;

alter tablespace 테이블스페이스명 online;

alter tablespace 테이블스페이스명 rename datafile '원본파일경로' to '파일경로';

TABLESPACE 삭제

drop tablespace 테이블스페이스명 including contents and datafile cascade constraints;

문법

CREATE TABLESPACE 테이블스페이스명
DATAFILE '파일경로1' SIZE integer [M/K], '파일경로2' SIZE integer [M/K]
[ MINIMUM EXTENT integer [M/K]]
[ BLOCKSIZE integer [K]] [ DEFAULT STORAGE (
INITIAL integer [M/K]
NEXT integer [M/K]
MAXEXTENTS integer
MINEXTENTS integer
PCTINCREASE integer)]
[ ONLINE | OFFLINE ]
[ PERMANENT | TEMPORARY ]
[ EXTENT MANAGEMENT [ DICTIONARY | LOCAL
[ AUTOALLOCATE | UNIFORM [ SIZE integer [M/K]]]]]
[ SEGMENT SPACE MANAGEMENT [ MANUAL | AUTO]]

OPEN 상태에서 DATAFILE 이동

alter tablespace 테이블스페이스명 offline;
offline 된 T/S에 대해 복사/이동 후
alter tablespace 테이블스페이스명 rename datafile '파일경로' to '파일경로';
alter tablespace 테이블스페이스명 online;

MOUNT 상태에서 DATAFILE 이동

startup mount;
해당 T/S에 대해 복사/이동 후
alter database rename file '파일경로' to '파일경로';
alter database open;

모든 데이타 파일은 mount상태에서 복사/이동 가능

system 파일은 mount상태에서만 복사/이동 가능

>>Index<<

TEMPORARY TABLESPACE

TEMPORARY TABLESPACE 관리

TEMPORARY T/S는 READ ONLY 설정 불가, nologgin 상태이며 rename불가, 복구대상이 아님
READ ONLY DATABASE 에서도 TEMPORARY 파일은 필요

DEFAULT TEMPORARY TABLESPACE 확인

select * from database_properties where property_name like '%TEMP%';

TEMPORARY TABLESPACE 변경

create temporary tablespace 테이블스페이스명_신 tempfile '파일경로' size 크기;
alter database default temporary tablespace 테이블스페이스명_신;
drop tablespace 테이블스페이스명_구;

>>Index<<

UNDO TABLESPACE

PARAMETER 설정 / 9i

UNDO_MANAGEMENT = AUTO [ MANUAL ]
UNDO_TABLESPACE = UNDOTBS1
UNDO_SUPPRESS_ERRORS = TRUE // 10g 에서는 쓰이지 않음
UNDO_RETENTION = integer (초)

PARAMETER 확인 / 9i

show parameter undo;

DEFAULT UNDO TABLESPACE 설정

alter system set undo_tablespace = 테이블스페이스명;
parameter 'UNDO_TABLESPACE' 수정

설정 조회

select segment_name, owner, tablespace_name, status
from dba_rollback_segs;

>>Index<<

DATABASE BUFFER CACHE

DBWR 기동 이벤트

Checkpoint - 일반적인 ckpt는 어디까지 내려썼는지만 확인 immediate ckpt시 즉시 내려씀
Dirty Block 임계값 도달
LRU List 의 Free Block 이 부족할 때
Time out
T/S offline (9i부터는 online시), read only, begin backup
Table Drop, Truncate
RAC ping

STANDARD BLOCK SIZE

System과 Temporary tablespace는 스탠다드 사이즈만 사용 가능

DB생성시 설정되는 표준 사이즈, 수정 불가(system T/S 가 이미 사용중이므로)

show parameter db_block_size

사용 가능한 BLOCK SIZE 조회

show parameter cache_size

db_nk_cache_size / n = '2, 4, 8, 16, 32'

DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE

hit rate 향상을 위한 parameter
db_keep_cache_size : 자주 호출되는 data를 pinning 할 때 쓰임
db_recycle_cache_size : 차후 호출될 가능성의 희박한 data를 읽을 때 쓰임

SGA 크기 조회

show parameter sga
show parameter sga_max

nk BLOCK SIZE의 TABLESPACE 생성

alter system set db_cache_size = 크기[M]; // SGA영역의 공간 확보를 위해 db_cache_size를 줄임
alter system set db_nk_cache_size = 크기[M]; // 줄여진 db_cache_size 만큼 할당 가능
create tablespace 테이블스페이스명 datafile '파일경로' size 크기 blocksize nk;

nk 블럭의 T/S가 존재하면 해당 db_nk_cache_size 를 0으로 설정 불가

>>Index<<

ROW MIGRATION / CHAINING

migration 은 해소 가능 chaining 은 해소 불가
오라클은 이 두 가지 경우를 구분하지 않음

TABLE 상태 확인

select owner, table_name, tablespace_name from dba_tables
where owner = '유저명' and table_name = '테이블명';

TABLE ANALYZE

analyze table 스키마.테이블명 compute statistics;

dictionary의 통계정보를 갱신시켜 주는 작업

CHAIN COUNT 조회

select num_rows, chain_cnt from dba_tables where table_name = '테이블명';

TABLE 이동

alter table 테이블명 move
[ tablespace 테이블스페이스명]; // 생략시 현재 사용중인 T/S 내에서 옮겨짐

INDEX 조회

select table_name, index_name, status from dba_indexes where table_name = '테이블명'

INDEX REBUILD

alter index 스키마.인덱스명 rebuild;

TABLE MOVE 명령후 ROWID가 변경됐으므로 INDEX를 REBUILD 해주어야 함

TABLE의 공간 사용량 조회

select num_rows, blocks, empty_blocks, avg_space, avg_row_ren from dba_tables
where owner = '유저명' and table_name = '테이블명';

BLOCKS : H/M 왼쪽 블럭 수
EMPTY_BLOCKS : 미사용 블럭 , H/M 오른쪽 블럭 수
AVG_SPACE : 사용중인 블럭의 평균 빈공간
AVG_ROW_LEN : row의 평균 길이

TABLE의 EXTENT설정 조회

select table_name, initial_extent, min_extents from dba_tables
where owner = '유저명' and table_name = '테이블명';

TABLESPACE의 EXTENT설정 조회

select tablespace_name, block_size, initial_extent, min_extents from dba_tablespaces
where tablespace_name = '테이블스페이스명';

>>Index<<

PARTITIONED TABLE

LIST 분할

create table table_name (column_1 type( ), column_2 type( ) ... )
partition by list (column_2) (
partition partition_name values ('value_1') tablespace tablespace_name,
partition partition_name values ('value_2') tablespace tablespace_name);

multi column partition 지원 안함
NULL 값 지정 가능, MAXVALUES 지정 불가
list를 구성하는 문자열은 4k 초과 불가

PARTITIONED TABLE 조회

select table_owner, table_name, partition_name, tablespace_name from dba_tab_partitions where table_owner = '유저명';

TABLE의 PARTITION 여부 조회

select owner, table_name, partitioned from dba_tables where owner = '유저명';

PARTITION 관리

alter table 테이블명 add partition partition_name values ('value') tablespace tablespace_name;

alter table 테이블명 drop partition partition_name;

RANGE 분할

create table table_name (column_1 type( ), column_2 type( ) ... )
partition by range (column_2) (
partition partition_name values less than (value_1),
partition partition_name values less than (value_2),
partition partition_name values less than ( MAXVALUE ) );

HASH 분할

create table table_name (column_1 type( ), column_2 type( ) ... )
partition by hash (column_2)
partitions integer store in (tablespace_name, tablespace_name);

>>Index<<

DEFERRED CONSTRAINTS

문법

CREATE TABLE table_name (column_1 type( ), column_2 type ( ), ...
CONSTRAINT constraint_name constraint_type (column)
[ NOT DEFERRABLE | DEFERRABLE [ INITIALLY [ IMMEDIATE | DEFERRED ]]]);

지연된 제약조건 활성화

ALTER SESSION SET CONSTRAINTS = [ IMMEDIATE | DEFERRED | DEFAULT ]

>>Index<<

USER

USER 생성

create user 유저명 identified by 패스워드
default tablespace 테이블스페이스명
temporary tablespace 임시테이블스페이스명
quota integer [M] on 유저명;

USER 변경

alter user 유저명 identified by 패스워드
default tablespace 테이블스페이스명
temporary tablespace 임시테이블스페이스명
quota integer [M] on 유저명
[ password expire ];

USER 의 TABLESPACE 할당량 조회

select * from dba_ts_quotas;

>>Index<<

PROFILE

PROFILE 조회

select distinct profile from dba_profiles;

USER 의 PROFILE 조회

select username, profile from dba_users;

PROFILE 생성

create profile profile_name limit
제한사항 value 제한사항 value ... ;

PROFILE 적용

alter user 유저명 profile profile_name;

PARAMETER 'resource_limit' 의 값이 TRUE로 설정되어 있어야 함

>>Index<<

PRIVILEGES

권한 부여 / SYSTEM PRIVS

grant 권한 to 유저명
[ with admin option ];

권한 부여 / OBJECT PRIVS

grant 권한 on 개체 to 유저명
[ with grant option ];

권한 조회

select * from dba_sys_privs where grantee like '유저명';

GRANT 조회 / TABLE

select * from all_tab_privs where table_name = '테이블명';

권한 취소 / SYSTEM PRIVS

revoke 권한 from 유저명;

권한 취소 / OBJECT PRIVS

revoke 권한 on 개체 from 유저명;

>>Index<<

ROLE

ROLE 의 PRIVS 조회

select * from dba_sys_privs where grantee = 'role_name';

ROLE 생성

create role role_name;

ROLE 에 SYSTEM PRIVS 부여

grant privs_name to role_name;

ROLE 에 OBJECT PRIVS 부여

grant privs_name on 개체 to role_name;

DEFAULT ROLE 지정

alter user user_name default role role_name;

ROLE 활성화

set role role_name;

set role all;

ROLE 조회

select * from session_roles;

>>Index<<

EXPORT

문법

]$ exp username/passwd option=(value1, value2, ... ) option=value ...

OPTION

• file
  : 백업 파일명 지정 (default : expdat.dmp)
• rows
  : 테이블의 row의 포함 여부 지정
• full
  : 전체 DB에 대한 익스포트 지정
• owner
  : 익스포트할 사용자 지정 (사용자모드)
• table
  : 익스포트할 테이블 지정 (테이블모드)
• tablespace
  : 익스포트할 테이블스페이스 지정 (T/S 모드)
• inctype
  : 전체 백업 레벨 지정 (8i까지만 사용됨)
• indexes
  : 인덱스 익스포트 지정

full, owner, table, tablespace 는 동시 사용 불가

익스포트시 sys로 작업은 지양 (dictionary data까지 포함되므로)

>>Index<<

IMPORT

문법

]$ imp username/passwd option=(value1, value2, ... ) option=value ...

OPTION

• file
  : 입력 파일명 지정
• ignore
  : 임포트 실행중 입력 오류 무시
• rows
  : 테이블의 row의 포함 여부 지정
• full
  : 전체 DB에 대한 임포트트 지정
• fromuser
  : 익스포트된 객체를 소유한 사용자중 임포트 대상이 되는 사용자
• touser
  : 임포트할 대상이 되는 사용자
• table
  : 임포트할 테이블 지정
• tablespace
  : 임포트할 테이블스페이스 지정

임포트 작업시 실행 순서 : 새로운 테이블생성 / 데이터 입력, 인덱스 리빌드 / 제약조건 활성화

>>Index<<

DIRECT LOAD

DIRECT LOAD SAMPLE

sample.ctl
LOAD DATA INFILE * INTO TABLE table_name
FIELDS TERMINATED BY ',' (column1, column2, column3)
BEGINDATA
111,aa,95
112,ab,86
...
...

]$ sqlldr username/passwd sample.ctl

>>Index<<

ORACLE NET / HOST NAMING

HOST NAMING

port 번호등의 정보를 Client에게 제공하지 않음
다수의 DB를 운용하는 경우는 사용할 수 없음
GLOBAL_DBNAME은 되도록이면 도메인 형식을 사용
(호스트명만 기입시 Windows Client 에서만 이용가능)

SERVER 설정 / LINUX

$ORACLE_HOME/network/admin/listener.ora

• ora10g =
• (ADDRESS_LIST =
• (ADDRESS = (PROTOCOL = TCP) (HOST = xxx.xxx.xxx.xxx) (PORT = 1521))
• )
• SID_LIST_ora10g =
• (SID_LIST =
• (SID_DESC = (GLOBAL_DBNAME = ora10g.xxx.xxx)
• (ORACLE_HOME = /app/ora10g/10g)
• (SID_NAME = DB09)
• )
• )

LISTENER 구동

]$ lsnrctl start ora10g

CLIENT 설정 / WINDOWS

GLOBAL_DBNAME 으로 ping이 되는지 확인, 필요시 hosts나 DNS에 등록

%ORACLE_HOME%\network\admin\sqlnet.ora

• SQLNET.AUTHENTICATION_SERVICES= (NTS)
• NAMES.DIRECTORY_PATH= (HOSTNAME)

C:\>sqlplus username/passwd@ora10g.xxx.xxx

CLIENT 설정 / LINUX

GLOBAL_DBNAME 으로 ping이 되는지 확인, 필요시 hosts나 DNS에 등록

$ORACLE_HOME/network/admin/sqlnet.ora

• NAMES.DIRECTORY_PATH= (HOSTNAME)

]$ sqlplus username/passwd@ora10g.xxx.xxx

>>Index<<

ORACLE NET / LOCAL NAMING

LOCAL NAMING

port 번호등 서버정보를 Client가 가지고 있음

SERVER 설정 / LINUX

$ORACLE_HOME/network/admin/listener.ora

• ora10g =
• (ADDRESS_LIST =
• (ADDRESS = (PROTOCOL = TCP) (HOST = xxx.xxx.xxx.xxx) (PORT = 1521))
• )
• SID_LIST_ora10g =
• (SID_LIST =
• (SID_DESC = (ORACLE_HOME = /app/ora10g/10g)
• (SID_NAME = DB09)
• )
• )

다수의 DB가 존재 할 경우 각 DB의 listner port번호는 다르게 설정한다

LISTENER 구동

]$ lsnrctl start ora10g

각각의 DB에 해당하는 listener.ora 파일을 생성하고 listener를 각각 구동한다

CLIENT 설정 / NAMES.DEFAULT_DOMAIN 미설정 시

sqlnet.ora

• NAMES.DIRECTORY_PATH= (TNSNAMES)

tnsnames.ora

• ora9i =
• (DESCRIPTION =
• (ADDRESS = (PROTOCOL = TCP) (HOST = xxx.xxx.xxx.xxx) (PORT = 1529))
• (CONNECT_DATA = (SID = DB09))
• )
• ora10g =
• (DESCRIPTION =
• (ADDRESS = (PROTOCOL = TCP) (HOST = xxx.xxx.xxx.xxx) (PORT = 1521))
• (CONNECT_DATA = (SID = DB10))
• )

]$ sqlplus username/passwd@ora10g
]$ sqlplus username/passwd@ora9i

CLIENT 설정 / NAMES.DEFAULT_DOMAIN 설정 시

TCP/IP 에서의 DOMAIN과 관계 없음

sqlnet.ora

• NAMES.DEFAULT_DOMAIN= webdb.co.kr
• NAMES.DIRECTORY_PATH= (TNSNAMES)

tnsnames.ora

• ora9i.webdb.co.kr =
• (DESCRIPTION =
• (ADDRESS = (PROTOCOL = TCP) (HOST = xxx.xxx.xxx.xxx) (PORT = 1529))
• (CONNECT_DATA = (SID = DB09))
• )
• ora10g.webdb.co.kr =
• (DESCRIPTION =
• (ADDRESS = (PROTOCOL = TCP) (HOST = xxx.xxx.xxx.xxx) (PORT = 1521))
• (CONNECT_DATA = (SID = DB10))
• )

]$ sqlplus username/passwd@ora10g
]$ sqlplus username/passwd@ora9i
]$ sqlplus username/passwd@ora10g.webdb.co.kr

>>Index<<

DATABASE 생성 / 9i

ENV CHECK

]$ env | grep ORACLE

기존 파일 삭제

$ORACLE_BASE/oradata/
$ORACLE_BASE/admin/$ORACLE_SID/

PARAMETER FILE 편집

$ORACLE_HOME/dbs/initSID_name.ora

DB 생성

createdb.sql

• CREATE DATABASE $ORACLE_SID
• LOGFILE
• GROUP 1 ('$ORACLE_BASE/oradata/disk4/redo01.log') size 1M,
• GROUP 2 ('$ORACLE_BASE/oradata/disk4/redo02.log') size 1M,
• GROUP 3 ('$ORACLE_BASE/oradata/disk4/redo03.log') size 1M
• MAXLOGFILES 5
• MAXLOGMEMBERS 5
• MAXDATAFILES 100
• DATAFILE
• '$ORACLE_BASE/oradata/disk3/system01.dbf' size 300M
• EXTENT MANAGEMENT LOCAL
• UNDO TABLESPACE undo DATAFILE
• '$ORACLE_BASE/oradata/disk3/undo01.dbf' size 10M
• DEFAULT TEMPORARY TABLESPACE temp TEMPFILE
• '$ORACLE_BASE/oradata/disk3/temp01.dbf' size 10M
• CHARACTER SET KO16KSC5601
• ;
• @$ORACLE_HOME/rdbms/admin/catalog.sql
• @$ORACLE_HOME/rdbms/admin/catproc.sql
• conn system/manager;
• @$ORACLE_HOME/sqlplus/admin/pupbld.sql

]$ sqlplus '/as sysdba'
SQL> startup nomount
SQL> @createdb.sql

>>Index<<

DATABASE 생성 / 10g

ENV CHECK

]$ env | grep ORACLE

기존 파일 삭제

$ORACLE_BASE/oradata/
$ORACLE_BASE/admin/$ORACLE_SID/

PARAMETER FILE 편집

$ORACLE_HOME/dbs/initSID_name.ora

DB 생성

createdb.sql

• CREATE DATABASE $ORACLE_SID
• LOGFILE
• GROUP 1 ('$ORACLE_BASE/oradata/disk4/redo01.log') size 4M,
• GROUP 2 ('$ORACLE_BASE/oradata/disk4/redo02.log') size 4M,
• GROUP 3 ('$ORACLE_BASE/oradata/disk4/redo03.log') size 4M
• MAXLOGFILES 5
• MAXLOGMEMBERS 5
• MAXDATAFILES 100
• DATAFILE
• '$ORACLE_BASE/oradata/disk3/system01.dbf' size 300M
• EXTENT MANAGEMENT LOCAL
• SYSAUX DATAFILE
• '$ORACLE_BASE/oradata/disk3/sysaux01.dbf' size 200M
• UNDO TABLESPACE undo DATAFILE
• '$ORACLE_BASE/oradata/disk3/undo01.dbf' size 10M
• DEFAULT TEMPORARY TABLESPACE temp TEMPFILE
• '$ORACLE_BASE/oradata/disk3/temp01.dbf' size 10M
• CHARACTER SET KO16KSC5601
• ;
• @$ORACLE_HOME/rdbms/admin/catalog.sql
• @$ORACLE_HOME/rdbms/admin/catproc.sql
• conn system/manager;
• @$ORACLE_HOME/sqlplus/admin/pupbld.sql

]$ sqlplus '/as sysdba'
SQL> startup nomount
SQL> @createdb.sql

>>Index<<

ARCHIVE LOG MODE

DB 종료

SQL> shutown immediate

Parameter File 수정

• log_archive_start = true
• log_archive_dest = destination
• log_archive_format = %S.arc

다수의 아카이빙

• log_archive_duplex_dest = destination
• log_archive_min_succed_dest = [ 1 | 2 ]

• log_archive_dest_# = "location = destination"
• log_archive_dest_# = "service = tnsname"

DB 기동 / 아카이브 모드 변경

SQL> startup mount
SQL> alter database archivelog; SQL> startup open

아카이브 모드 확인 후 Close Backup
SQL> archive log list

>>Index<<