[관리] 오라클 DBA 관리 팁 모음
오라클 관리 기본 팁 모음
1. sql*plus 사용시 컬럼을 적절히 조절하려면
$ORACLE_HOME\\sqlplus\\admin\\glogin.sql 파일에 다음 내용 추가합니다.
set pagesize 40
set linesize 126
COLUMN TABLE_NAME FORMAT A30
COLUMN CONSTRAINT_NAME FORMAT A30
COLUMN R_CONSTRAINT_NAME FORMAT A30
COLUMN SEARCH_CONDITION FORMAT A30
COLUMN TABLE_NAME FORMAT A30
COLUMN COLUMN_NAME FORMAT A30
COLUMN OBJECT_NAME FORMAT A30
COLUMN OBJECT_TYPE FORMAT A20
COLUMN DATA_TYPE FORMAT A20
COLUMN VIEW_NAME FORMAT A30
COLUMN TEXT FORMAT A60 WORD_WRAPPED
COLUMN TABLESPACE_NAME FORMAT A20
COLUMN DEFAULT_TABLESPACE FORMAT A20
COLUMN TEMPORARY_TABLESPACE FORMAT A20
COLUMN FILE_NAME FORMAT A50
COLUMN SEGMENT_NAME FORMAT A30
COLUMN SEGMENT_TYPE FORMAT A30
COLUMN PARAMETER FORMAT A30
COLUMN VALUE FORMAT A30
COLUMN MEMBER FORMAT A40
COLUMN NAME FORMAT A40
COLUMN FILE FORMAT A40
COLUMN OBJ$NAME FORMAT A30
A30의 경우 30이 폭이므로 자신의 취향에 따라 조절하세요..
보통 윈도우 환경에서 sql*plus의 화면을 최대로 하면 linesize는 126이 가장 적당한 거 같습니다.
리눅스에서 clean과 백묵폰트 조합의 경우 linesize는 더 크게 잡아줘도 됩니다.
2. Windows환경에서 sql*plus의 메뉴 및 출력되는 메시지를 한글 또는 영문으로 바꾸려면
regedit를 실행한 후 HKEY_LOCAL_MACHINE\\SOFTWARE\\ORACLE\\HOME0\\NLS_LANG 의 항목을
KOREAN_KOREA.KO16KSC5601 또는 AMERICAN_AMERICA.US7ASCII 로 수정합니다.
3. Windows환경에서 부팅시 자동으로 오라클이 기동되는 것을 방지하려면 regedit를 실행한 후
HKEY_LOCAL_MACHINE\\SOFTWARE\\ORACLE\\HOME0\\ORA_ADMDB_AUTOSTART의 항목을
TRUE에서 FALSE로 수정합니다.
4. oracle 8i에서 데이터베이스를 디폴트로 생성한 후 보면 여러 사용자들의 default tablespace가
system tablespace를 사용하고 있습니다. 다음을 실행시켜서 적당한 테이블스페이스로 이동시키세요..
select username, default_tablespace, temporary_tablespace
from dba_users; 로 먼저 확인
alter table scott.emp move tablespace users;
alter table scott.dept move tablespace users;
alter table scott.emp move tablespace users;
alter table scott.bonus move tablespace users;
alter table scott.salgrade move tablespace users;
alter table scott.account move tablespace users;
alter table scott.receipt move tablespace users;
alter index scott.pk_dept rebuild tablespace indx;
alter index scott.pk_emp rebuild tablespace indx;
alter user SCOTT default tablespace users;
alter user ADAMS default tablespace users;
alter user JONES default tablespace users;
alter user CLARK default tablespace users;
alter user BLAKE default tablespace users;
alter user OUTLN temporary tablespace temp;
alter user DBSNMP temporary tablespace temp;
alter user AURORA$JIS$UTILITY$ temporary tablespace temp;
alter user OSE$HTTP$ADMIN temporary tablespace temp;
alter user AURORA$ORB$UNAUTHENTICATED temporary tablespace temp;
alter user ORDSYS temporary tablespace temp;
alter user ORDPLUGINS temporary tablespace temp;
alter user MDSYS temporary tablespace temp;
alter user CTXSYS temporary tablespace temp;
alter user SCOTT temporary tablespace temp;
alter user ADAMS temporary tablespace temp;
alter user JONES temporary tablespace temp;
alter user CLARK temporary tablespace temp;
alter user BLAKE temporary tablespace temp;
alter user MTSSYS temporary tablespace temp;
select username, default_tablespace, temporary_tablespace
from dba_users; 다시 확인
5. RMAN을 사용한 백업을 위한 준비사항
create tablespace rmants
datafile ‘c:\\oracle\\oradata\\admdb\\rmants01.dbf’ size 20m
default storage (initial 100k next 100k pctincrease 0);
create user rmanusr identified by rmanusr
default tablespace rmants
temporary tablespace temp
quota unlimited on rmants;
grant recovery_catalog_owner to rmanusr;
grant connect, resource to rmanusr;
6. 오라클 데이터베이스 open과 shutdown 배치파일
(경로와 sys의 패스워드는 수정해야 합니다.)
<startmydb.bat>
c:\\oracle\\oracle8i\\bin\\svrmgrl @c:\\oracle\\startmydb.sql
<startmydb.sql>
connect sys/{sys의 password}@mydb as sysdba
startup pfile=”c:\\oracle\\admin\\mydb\\pfile\\initmydb.ora”
<stopmydb.bat>
c:\\oracle\\oracle8i\\bin\\svrmgrl @c:\\oracle\\stopmydb.sql
<stopmydb.sql>
connect sys/{sys의 password}@mydb as sysdba
shutdown
7. Dictionary를 조회할 때 매우 유용한 SQL문
select * from dict
where lower(comments) like lower(‘%&dictionary_comments%’);
————————————–
select * from dict
where lower(table_name) like lower(‘%&dictionary_name%’);
————————————–
column table_name format a30
column column_name format a30
column comments format a60 word_wrapped
select * from dict_columns
where lower(table_name) like lower(‘&dictionary_name’)
and lower(column_name) like lower(‘&column_name’);
————————————–
select * from dict_columns
where lower(table_name) like lower(‘&dictionary_name’);
8. 유용한 DBA View 모음
SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE, R_CONSTRAINT_NAME, SEARCH_CONDITION
FROM USER_CONSTRAINTS;
———————————–
SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS, VALIDATED, DEFERRABLE, DEFERRED
FROM USER_CONSTRAINTS;
———————————–
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
FROM USER_CONS_COLUMNS;
———————————–
SELECT A.TABLE_NAME, B.COLUMN_NAME, A.CONSTRAINT_NAME,
A.CONSTRAINT_TYPE, A.R_CONSTRAINT_NAME, A.SEARCH_CONDITION
FROM USER_CONSTRAINTS A, USER_CONS_COLUMNS B
WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME;
———————————–
SELECT T.TABLE_NAME, T.CONSTRAINT_NAME, C.COLUMN_NAME,
T.DEFERRABLE, T.DEFERRED, T.VALIDATED
FROM DBA_CONSTRAINTS T, DBA_CONS_COLUMNS C
WHERE T.CONSTRAINT_NAME = C.CONSTRAINT_NAME
AND T.OWNER LIKE ‘%&OWNER%’
AND T.TABLE_NAME LIKE ‘%&TABLE_NAME%’;
———————————–
SELECT OBJECT_NAME, OBJECT_TYPE, OBJECT_ID, DATA_OBJECT_ID, CREATED, TIMESTAMP, LAST_DDL_TIME
FROM USER_OBJECTS
ORDER BY OBJECT_TYPE;
———————————-
SELECT VIEW_NAME, TEXT
FROM USER_VIEWS;
———————————–
SELECT *
FROM USER_SEQUENCES;
———————————–
SELECT SYNONYM_NAME, TABLE_OWNER, TABLE_NAME
FROM USER_SYNONYMS;
———————————–
SELECT TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS,
PCT_INCREASE, MIN_EXTLEN, STATUS, CONTENTS, ALLOCATION_TYPE
FROM DBA_TABLESPACES;
———————————-
롤백세그먼트의 일반적인 정보
SELECT SEGMENT_NAME, OWNER, TABLESPACE_NAME,
INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, STATUS
FROM DBA_ROLLBACK_SEGS;
———————————-
현재 인스턴트가 사용하고 있는 롤백세그먼트에 대한 통계 검색
SELECT N.NAME, S.EXTENTS, S.RSSIZE, S.OPTSIZE, S.HWMSIZE, S.XACTS, S.STATUS
FROM V$ROLLNAME N, V$ROLLSTAT S
WHERE N.USN = S.USN;
———————————-
롤백세그먼트에 대한 경합
SELECT N.NAME, ROUND(100*S.WAITS/S.GETS)
FROM V$ROLLNAME N, V$ROLLSTAT S
WHERE N.USN = S.USN;
———————————-
블록킹 트랜잭션
SELECT S.SID, S.SERIAL#, T.START_TIME, T.XIDUSN, S.USERNAME
FROM V$SESSION S, V$TRANSACTION T, V$ROLLSTAT R
WHERE S.SADDR = T.SES_ADDR
AND T.XIDUSN = R.USN
AND ((R.CUREXT = T.START_UEXT-1)
OR ((R.CUREXT = R.EXTENTS-1) AND T.START_UEXT = 0));
———————————-
인덱스에 대한 유효성 확인
SELECT BLOCKS, BTREE_SPACE, USED_SPACE, PCT_USED, LF_ROWS, DEL_LF_ROWS
FROM INDEX_STATS;
———————————-
SELECT USERNAME, USER_ID, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, PROFILE
FROM DBA_USERS;
———————————-
SELECT TABLESPACE_NAME, BYTES, MAX_BYTES, BLOCKS, MAX_BLOCKS
FROM USER_TS_QUOTAS;
———————————–
SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES,
AUTOEXTENSIBLE, INCREMENT_BY, MAXBYTES
FROM DBA_DATA_FILES;
———————————–
SELECT FILE#, STATUS, RFILE#, BYTES, BLOCKS, CREATE_BYTES, BLOCK_SIZE, NAME
FROM V$DATAFILE;
———————————–
SELECT FILE#, STATUS
FROM V$BACKUP;
———————————–
SELECT *
FROM DBA_FREE_SPACE
ORDER BY BLOCK_ID;
———————————–
SELECT A.TABLESPACE_NAME, A.BYTES, A.STATUS, B.STATUS, B.ENABLED, B.NAME
FROM DBA_DATA_FILES A, V$DATAFILE B
WHERE A.FILE_ID = B.FILE# AND A.TABLESPACE_NAME LIKE ‘%&TABLESPACE_NAME%’;
———————————–
SELECT TABLESPACE_NAME, EXTENTS_COALESCED, TOTAL_BYTES, BYTES_COALESCED
FROM DBA_FREE_SPACE_COALESCED;
———————————–
SELECT A.TABLESPACE_NAME “TABLESPACE”,
B.FILE_NAME “FILE”,
B.BYTES “TOTAL SIZE”,
C.BYTES “SIZE LEFT”
FROM DBA_TABLESPACES A, DBA_DATA_FILES B, DBA_FREE_SPACE C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME AND
A.TABLESPACE_NAME = C.TABLESPACE_NAME AND
A.TABLESPACE_NAME LIKE ‘%&TABLESPACE_NAME%’;
———————————–
SELECT SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES, BLOCKS, EXTENTS, INITIAL_EXTENT, NEXT_EXTENT
FROM USER_SEGMENTS;
———————————–
SELECT INDEX_NAME, TABLE_NAME, UNIQUENESS, INDEX_TYPE, TABLESPACE_NAME
FROM USER_INDEXES;
———————————–
SELECT LINE, POSITION, TEXT
FROM USER_ERRORS
ORDER BY SEQUENCE;
———————————–
SELECT *
FROM USER_SOURCE;
———————————–
SELECT SEGMENT_NAME, PARTITION_NAME, TABLESPACE_NAME, EXTENT_ID, BYTES, BLOCKS
FROM USER_EXTENTS
ORDER BY SEGMENT_NAME, EXTENT_ID;
———————————–
SELECT TABLESPACE_NAME, SEGMENT_NAME, FILE_ID, EXTENT_ID, BLOCK_ID, BLOCKS
FROM DBA_EXTENTS
ORDER BY EXTENT_ID;
———————————–
SELECT OPERATION, OPTIONS, OBJECT_NAME, ID, PARENT_ID, POSITION
FROM PLAN_TABLE
ORDER BY ID;
———————————–
SELECT NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN
FROM USER_TABLES;
———————————–
SELECT COLUMN_NAME, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, DENSITY
FROM USER_TAB_COLUMNS;
———————————–
SELECT TABLE_NAME, TABLE_TYPE_NAME, PARENT_TABLE_NAME, PARENT_TABLE_COLUMN
FROM USER_NESTED_TABLES;
———————————–
SELECT *
FROM DBA_PROFILES;
———————————–
SELECT *
FROM USER_RESOURCE_LIMITS;
———————————–
SELECT *
FROM USER_PASSWORD_LIMITS;
———————————–
SELECT *
FROM V$OPTION;
———————————–
SELECT *
FROM V$PARAMETER;
———————————–
SELECT DISTINCT OBJECT_TYPE
FROM DBA_OBJECTS;
———————————–
SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_TYPE, CREATED
FROM DBA_OBJECTS
WHERE OWNER LIKE ‘%&OWNER%’
AND OBJECT_TYPE LIKE ‘%&OBJECT_TYPE%’
ORDER BY OBJECT_NAME;
———————————–
SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME LIKE ‘%&TABLE_NAME%’
ORDER BY OWNER, TABLE_NAME;
———————————–
SELECT VIEW_NAME, TEXT
FROM DBA_VIEWS
WHERE VIEW_NAME LIKE ‘%&VIEW_NAME%’;
———————————–
SELECT *
FROM DBA_SYS_PRIVS
WHERE GRANTEE LIKE ‘%&ROLE%’;
———————————–
SELECT OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME
FROM DBA_SYNONYMS
ORDER BY 2, 1, 3;
———————————–
9. <클러스터>
클러스터의 일반적인 정보(블록파라미터..)
SELECT * FROM DBA_CLUSTERS
WHERE OWNER LIKE ‘%&OWNER%’;
클러스터 테이블 및 클러스터 키 조회
SELECT OWNER, CLUSTER_NAME, CLU_COLUMN_NAME, TABLE_NAME
FROM DBA_CLU_COLUMNS
WHERE OWNER LIKE ‘%&OWNER%’;
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE
FROM DBA_TAB_COLUMNS
WHERE OWNER LIKE ‘%&OWNER%’;
———————————–
IOT 조회
SELECT T.TABLE_NAME AS “IOT”, O.TABLE_NAME AS “OVERFLOW”,
I.INDEX_NAME AS “INDEX”, O.TABLESPACE_NAME AS “OVERFLOW TBS”,
I.TABLESPACE_NAME AS “INDEX TBS”, I.PCT_THRESHOLD
FROM DBA_TABLES T, DBA_TABLES O, DBA_INDEXES I
WHERE T.OWNER = O.OWNER
AND T.TABLE_NAME = O.IOT_NAME
AND T.OWNER = I.OWNER
AND T.TABLE_NAME = I.TABLE_NAME
AND T.OWNER LIKE ‘%&OWNER%’;
———————————–
SELECT USERNAME, TIMESTAMP, ACTION_NAME
FROM DBA_AUDIT_TRAIL;
———————————–
SELECT USERID, OBJ$NAME, SES$ACTIONS, TIMESTAMP#
FROM SYS.AUD$
WHERE OBJ$NAME LIKE ‘%&OBJECT_NAME%’;
———————————–
SELECT SESSIONID, STATEMENT, TIMESTAMP#, USERID, TERMINAL, ACTION#,
OBJ$CREATOR, OBJ$NAME, SES$ACTIONS, COMMENT$TEXT, SPARE1
FROM SYS.AUD$;
![[관리] oracle open backup 예제1](http://nblog.syszone.co.kr/wp-content/themes/hueman/assets/front/img/thumb-medium-empty.png)
