HighLight in your life~

오라클 OPEN 순서 입니다.

첨부파일에서 확인 가능합니다.

거의 10년만에 DB관리를 다시하게 되었습니다.

아래의 책들을 읽어 봐야 할 듯합니다. DB의 길은 멀고도 다양한 것 같습니다.

1. 모델링

가. 관계형데이터모델링 프리미엄가이드: 김기창 [구입예정]

나. 관계형 데이터 모델링 노트: 김기창 [구입예정]

다. 데이터아키텍처솔루션1: 이화식 [도서관]

라. 데이터아키텍처전문가 가이드 2013 [구입예정]

마. 데이터아키텍처 자격검정실전문제 2013

바. 데이터아키텍처 전문가 자격검정시험대비 실전문제 181

사. 데이터아키텍처 전문가 자격검정시험대비 실전문제 362

2. SQL 튜닝

가. 실전 사례로 살펴보는 SQL 튜닝비법 [구입예정]

3.오라클 관리

가. 오라클 레벨업 (최고의 전문가 그룹이 알려주는 오라클 실전 100가지) [구입예정]

4. 읽어야 할 책

가. Logical Optimizer

나. SQL 튜닝의 시작

Oracle 9i Install(DB설치)

Oracle 9i Install(엔진설치)

http://laserbit.tistory.com/m/entry/%EC%98%A4%EB%9D%BC%ED%81%B4-FUNCTION-%EC%83%9D%EC%84%B1

The job of the DBA seems to be everything that everyone else either doesn't want to do,
 or doesn't have the ability to do. DBAs get the enviable task of figuring out all of the things no one else can figure out.
 More seriously though, here is a list of typical DBA responsibilities:

 Installation, configuration and upgrading of Oracle server software and related products
 Evaluate Oracle features and Oracle related products
 Establish and maintain sound backup and recovery policies and procedures
 Take care of the Database design and implementation
 Implement and maintain database security (create and maintain users and roles, assign privileges)
 Perform database tuning and performance monitoring
 Perform application tuning and performance monitoring
 Setup and maintain documentation and standards
 Plan growth and changes (capacity planning)
 Work as part of a team and provide 7x24 support when required
 Perform general technical trouble shooting and give consultation to development teams
 Interface with Oracle Corporation for technical support.
 Patch Management and Version Control

SELECT IDX.OWNER, IDX.INDEX_NAME, IDX.TABLE_OWNER,
IDX.TABLE_NAME, IDX.TABLESPACE_NAME, IDX_COL.COLUMN_NAME
FROM DBA_INDEXES IDX, DBA_IND_COLUMNS IDX_COL
WHRE IDX.TABLE_NAME = 'EMP'
AND IDX.INDEX_NAME=IDX_COL.INDEX_NAME
ORDER BY INDEX_NAME;

SQL> desc dba_indexes;
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 OWNER NOT NULL VARCHAR2(30)
 INDEX_NAME NOT NULL VARCHAR2(30)
 INDEX_TYPE VARCHAR2(27)
 TABLE_OWNER NOT NULL VARCHAR2(30)
 TABLE_NAME NOT NULL VARCHAR2(30)
 TABLE_TYPE VARCHAR2(11)
 UNIQUENESS VARCHAR2(9)
 COMPRESSION VARCHAR2(8)
 PREFIX_LENGTH NUMBER
 TABLESPACE_NAME VARCHAR2(30)
 INI_TRANS NUMBER
 MAX_TRANS NUMBER
 INITIAL_EXTENT NUMBER
 NEXT_EXTENT NUMBER
 MIN_EXTENTS NUMBER
 MAX_EXTENTS NUMBER
 PCT_INCREASE NUMBER
 PCT_THRESHOLD NUMBER
 INCLUDE_COLUMN NUMBER
 FREELISTS NUMBER
 FREELIST_GROUPS NUMBER
 PCT_FREE NUMBER
 LOGGING VARCHAR2(3)
 BLEVEL NUMBER
 LEAF_BLOCKS NUMBER
 DISTINCT_KEYS NUMBER
 AVG_LEAF_BLOCKS_PER_KEY NUMBER
 AVG_DATA_BLOCKS_PER_KEY NUMBER
 CLUSTERING_FACTOR NUMBER
 STATUS VARCHAR2(8)
 NUM_ROWS NUMBER
 SAMPLE_SIZE NUMBER
 LAST_ANALYZED DATE
 DEGREE VARCHAR2(40)
 INSTANCES VARCHAR2(40)
 PARTITIONED VARCHAR2(3)
 TEMPORARY VARCHAR2(1)
 GENERATED VARCHAR2(1)
 SECONDARY VARCHAR2(1)
 BUFFER_POOL VARCHAR2(7)
 USER_STATS VARCHAR2(3)
 DURATION VARCHAR2(15)
 PCT_DIRECT_ACCESS NUMBER
 ITYP_OWNER VARCHAR2(30)
 ITYP_NAME VARCHAR2(30)
 PARAMETERS VARCHAR2(1000)
 GLOBAL_STATS VARCHAR2(3)
 DOMIDX_STATUS VARCHAR2(12)
 DOMIDX_OPSTATUS VARCHAR2(6)
 FUNCIDX_STATUS VARCHAR2(8)
 JOIN_INDEX VARCHAR2(3)

 SQL> desc dba_ind_columns;
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 INDEX_OWNER NOT NULL VARCHAR2(30)
 INDEX_NAME NOT NULL VARCHAR2(30)
 TABLE_OWNER NOT NULL VARCHAR2(30)
 TABLE_NAME NOT NULL VARCHAR2(30)
 COLUMN_NAME VARCHAR2(4000)
 COLUMN_POSITION NOT NULL NUMBER
 COLUMN_LENGTH NOT NULL NUMBER
 CHAR_LENGTH NUMBER
 DESCEND VARCHAR2(4)

 SQL> desc v$object_usage;
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 INDEX_NAME NOT NULL VARCHAR2(30)
 TABLE_NAME NOT NULL VARCHAR2(30)
 MONITORING VARCHAR2(3)
 USED VARCHAR2(3)
 START_MONITORING VARCHAR2(19)
 END_MONITORING VARCHAR2(19)

 SQL> spool off

토드 사용시 OWNER.TABLE_NAME 에 커서를 위치시키고 F4 를 누르면 별도의 Description을 확인할 수 있습닏.

--컬럼조회
SELECT * FROM ALL_TAB_COLUMNS;

--테이블조회
SELECT * FROM ALL_TABLES;

--테이블주석조회
SELECT * FROM USER_TAB_COMMENTS;

--테이블컬럼주석조회
SELECT * FROM ALL_COL_COMMENTS;

--각 테이블과 컬럼 comment 조회
SELECT
T1.*
FROM (
SELECT
A.TABLE_NAME
,B.COMMENTS AS TABLE_COMMENTS
,C.COLUMN_NAME
,C.COMMENTS AS COLUMN_COMMENTS
FROM ALL_TABLES A
,ALL_TAB_COMMENTS B
,ALL_COL_COMMENTS C
WHERE 1=1
AND A.TABLE_NAME = B.TABLE_NAME
AND A.TABLE_NAME = C.TABLE_NAME
) T1
WHERE 1=1
AND T1.TABLE_COMMENTS LIKE ‘%’|| :V_LIKE_TABLE_COMMENTS || ‘%’
AND T1.COLUMN_COMMENTS LIKE ‘%’|| :V_LIKE_COLUMN_COMMENTS || ‘%’