[관리] 오라클 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$;