클라우드 데이터베이스 Oracle 12c 가이드 (2016년)
비즈니스 인텔리전트와 데이터 웨어하우징 0 0 42,026

by 구루비스터디 ILM IDA ADO Oracle12c [2018.05.22]


3. ILM (Information LifeCycle Management)



1. In-Database 아카이빙(IDA)

  • 레코드가 보이거나 보이지 않도록 함

CREATE TABLE EMPLOYEES_ARCHIVE_TEST AS
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEE
WHERE EMPLOYEE_ID IN (200,201,202,203,204,205);

ALTER TABLE EMPLOYEE_ARCHIVE_TEST ROW ARCHIVAL;

** 컬럼 정보 조회
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_ID, HIDDEN_COLUMN
FROM USER_TAB_COLS
WHERE TABLE_NAME = 'EMPLOYEE_ARCHIVE_TEST'
ORDER BY COLUMN_ID;

TABLE_NAME                COLUMN_NAME           COLUMN_ID HID
------------------------- -------------------- ---------- ---
EMPLOYEE_ARCHIVE_TEST     EMPLOYEE_ID                   1 NO
EMPLOYEE_ARCHIVE_TEST     FIRST_NAME                    2 NO
EMPLOYEE_ARCHIVE_TEST     LAST_NAME                     3 NO
EMPLOYEE_ARCHIVE_TEST     ORA_ARCHIVE_STATE               YES
EMPLOYEE_ARCHIVE_TEST     SYS_NC00004$                    YES


** 히든 컬럼(ORA_ARCHIVE_STATE) 조회
SELECT EMPLOYEE_ID, FIRST_NAME || ' ' || LAST_NAME AS FULL_NAME, ORA_ARCHIVE_STATE
FROM EMPLOYEE_ARCHIVE_TEST
ORDER BY EMPLOYEE_ID;

EMPLOYEE_ID FULL_NAME                      ORA_ARCHIV
----------- ------------------------------ ----------
        200 Jennifer Whalen                0
        201 Michael Hartstein              0
        202 Pat Fay                        0
        203 Susan Mavris                   0
        204 Hermann Baer                   0
        205 Shelley Higgins                0

6 행이 선택되었습니다.


** 히든 컬럼(ORA_ARCHIVE_STATE) 업데이트
UPDATE EMPLOYEE_ARCHIVE_TEST
   SET ORA_ARCHIVE_STATE = DBMS_ILM.ARCHIVESTATENAME(1)
WHERE EMPLOYEE_ID IN (200, 201)

----------------------------------------------------------
UPDATE EMPLOYEE_ARCHIVE_TEST
   SET ORA_ARCHIVE_STATE = 1
WHERE EMPLOYEE_ID IN (200, 201)

COMMIT;

** 테이블 정보 조회 시
SELECT EMPLOYEE_ID, FIRST_NAME || ' ' || LAST_NAME AS FULL_NAME, ORA_ARCHIVE_STATE
FROM EMPLOYEE_ARCHIVE_TEST
ORDER BY EMPLOYEE_ID;

EMPLOYEE_ID FULL_NAME                      ORA_ARCHIV
----------- ------------------------------ ----------
        202 Pat Fay                        0
        203 Susan Mavris                   0
        204 Hermann Baer                   0
        205 Shelley Higgins                0

4 행이 선택되었습니다.


ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

SELECT EMPLOYEE_ID, FIRST_NAME || ' ' || LAST_NAME AS FULL_NAME, ORA_ARCHIVE_STATE
FROM EMPLOYEE_ARCHIVE_TEST
ORDER BY EMPLOYEE_ID;

EMPLOYEE_ID FULL_NAME                      ORA_ARCHIVE_STATE
----------- ------------------------------ ------------------------------
        200 Jennifer Whalen                1
        201 Michael Hartstein              1
        202 Pat Fay                        0
        203 Susan Mavris                   0
        204 Hermann Baer                   0
        205 Shelley Higgins                0

6 행이 선택되었습니다.


ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE

SELECT EMPLOYEE_ID, FIRST_NAME || ' ' || LAST_NAME AS FULL_NAME, ORA_ARCHIVE_STATE
FROM EMPLOYEE_ARCHIVE_TEST
ORDER BY EMPLOYEE_ID;

EMPLOYEE_ID FULL_NAME                      ORA_ARCHIVE_STATE
----------- ------------------------------ ------------------------------
        202 Pat Fay                        0
        203 Susan Mavris                   0
        204 Hermann Baer                   0
        205 Shelley Higgins                0


** 로우가 아카이브되었다는 것은 실제로 로우가 이동하거나 없어진 것을 의미하진 않는다

ALTER TABLE EMPLOYEE_ARCHIVE_TEST ADD PRIMARY KEY (EMPLOYEE_ID)

INSERT INTO EMPLOYEE_ARCHIVE_TEST VALUES(200,'I Want', ' in ');

1행에 오류:
ORA-00001: 무결성 제약 조건(SYS.SYS_C0010074)에 위배됩니다

** 적절한 데이터가 archived로 표시될 수 있고 이후에 archived 로 표시된 데이터를 저가의 스토리지쪽에
생성된 테이블 스페이스로 이동시킬 수 있다


2. Temporal Validity

  • 데이터가 표시되는 것을 결정하는 Time 디멘전 컬럼을 추가하는 기능
  • 레코드가 특정 기간 동안만 보임

* 테이블 생성
CREATE TABLE EMPLOYEES_TEMPORAL_TEST AS
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEE
WHERE EMPLOYEE_ID IN (200,201,202,203,204,205);

ALTER TABLE EMPLOYEES_TEMPORAL_TEST ADD PERIOD FOR VALID_TIME;

SELECT COLUMN_NAME, HIDDEN_COLUMN, DATA_TYPE
FROM USER_TAB_COLS
WHERE TABLE_NAME = 'EMPLOYEES_TEMPORAL_TEST'
ORDER BY COLUMN_ID, COLUMN_NAME DESC;

COLUMN_NAME                    HIDDE DATA_TYPE
------------------------------ ----- ----------------------------------------
EMPLOYEE_ID                    NO    NUMBER
FIRST_NAME                     NO    VARCHAR2
LAST_NAME                      NO    VARCHAR2
VALID_TIME_START               YES   TIMESTAMP(6) WITH TIME ZONE
VALID_TIME_END                 YES   TIMESTAMP(6) WITH TIME ZONE
VALID_TIME                     YES   NUMBER

6 행이 선택되었습니다.

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
       TO_CHAR(VALID_TIME_START, 'DDMONYY') "Start",
       TO_CHAR(VALID_TIME_END, 'DDMONYY') "End"
FROM EMPLOYEES_TEMPORAL_TEST

EMPLOYEE_ID FIRST_NAME           LAST_NAME       Start        End
----------- -------------------- --------------- ------------ ------------
        200 Jennifer             Whalen
        201 Michael              Hartstein
        202 Pat                  Fay
        203 Susan                Mavris
        204 Hermann              Baer
        205 Shelley              Higgins

6 행이 선택되었습니다.


UPDATE EMPLOYEES_TEMPORAL_TEST
SET VALID_TIME_START = TO_DATE('20120101','YYYYMMDD'),
    VALID_TIME_END = TO_DATE('20130215','YYYYMMDD')
WHERE EMPLOYEE_ID IN (200,201);

UPDATE EMPLOYEES_TEMPORAL_TEST
SET VALID_TIME_START = TO_DATE('20130216','YYYYMMDD'),
    VALID_TIME_END = TO_DATE('20130321','YYYYMMDD')
WHERE EMPLOYEE_ID IN (202,203);

UPDATE EMPLOYEES_TEMPORAL_TEST
SET VALID_TIME_START = TO_DATE('20130420','YYYYMMDD')
WHERE EMPLOYEE_ID IN (204,205);


COMMIT;

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
       TO_CHAR(VALID_TIME_START, 'YYYYMMDD') "Start",
       TO_CHAR(VALID_TIME_END, 'YYYYMMDD') "End"
FROM EMPLOYEES_TEMPORAL_TEST;

EMPLOYEE_ID FIRST_NAME           LAST_NAME       Start    End
----------- -------------------- --------------- -------- --------
        200 Jennifer             Whalen          20120101 20130215
        201 Michael              Hartstein       20120101 20130215
        202 Pat                  Fay             20130216 20130321
        203 Susan                Mavris          20130216 20130321
        204 Hermann              Baer            20130420
        205 Shelley              Higgins         20130420

6 행이 선택되었습니다.


SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
       TO_CHAR(VALID_TIME_START, 'YYYYMMDD') "Start",
       TO_CHAR(VALID_TIME_END, 'YYYYMMDD') "End"
FROM EMPLOYEES_TEMPORAL_TEST
AS OF PERIOD FOR VALID_TIME TO_DATE('20130110')
ORDER BY 2;

** 참고 (10053 trace file)
WHERE ("T"."VALID_TIME_START" IS NULL OR SYS_EXTRACT_UTC("T"."VALID_TIME_START") <= SYS_EXTRACT_UTC(TO_DATE('20130110')))
AND   ("T"."VALID_TIME_END" IS NULL OR SYS_EXTRACT_UTC("T"."VALID_TIME_END") > SYS_EXTRACT_UTC(TO_DATE('20130110')))

EMPLOYEE_ID FIRST_NAME           LAST_NAME       Start    End
----------- -------------------- --------------- -------- --------
        200 Jennifer             Whalen          20120101 20130215
        201 Michael              Hartstein       20120101 20130215

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
       TO_CHAR(VALID_TIME_START, 'YYYYMMDD') "Start",
       TO_CHAR(VALID_TIME_END, 'YYYYMMDD') "End"
FROM EMPLOYEES_TEMPORAL_TEST
VERSIONS PERIOD FOR VALID_TIME
BETWEEN TO_DATE('20130217') AND TO_DATE('20130224')
ORDER BY 1;

EMPLOYEE_ID FIRST_NAME           LAST_NAME       Start    End
----------- -------------------- --------------- -------- --------
        202 Pat                  Fay             20130216 20130321
        203 Susan                Mavris          20130216 20130321

** 참고 (10053 trace file)
("T"."VALID_TIME_START" IS NULL OR
  SYS_EXTRACT_UTC("T"."VALID_TIME_START")<=SYS_EXTRACT_UTC(TO_DATE('20130224')))
AND ("T"."VALID_TIME_END" IS NULL OR
     SYS_EXTRACT_UTC("T"."VALID_TIME_END")>SYS_EXTRACT_UTC(TO_DATE('20130217')))


** 현재 날짜에 적합한 사원 조회

EXEC DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('CURRENT');

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
       TO_CHAR(VALID_TIME_START, 'YYYYMMDD') "Start",
       TO_CHAR(VALID_TIME_END, 'YYYYMMDD') "End"
FROM EMPLOYEES_TEMPORAL_TEST

EMPLOYEE_ID FIRST_NAME           LAST_NAME       Start    End
----------- -------------------- --------------- -------- --------
        204 Hermann              Baer            20130420
        205 Shelley              Higgins         20130420

EXEC DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('ALL');

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
       TO_CHAR(VALID_TIME_START, 'YYYYMMDD') "Start",
       TO_CHAR(VALID_TIME_END, 'YYYYMMDD') "End"
FROM EMPLOYEES_TEMPORAL_TEST;

EMPLOYEE_ID FIRST_NAME           LAST_NAME       Start    End
----------- -------------------- --------------- -------- --------
        200 Jennifer             Whalen          20120101 20130215
        201 Michael              Hartstein       20120101 20130215
        202 Pat                  Fay             20130216 20130321
        203 Susan                Mavris          20130216 20130321
        204 Hermann              Baer            20130420
        205 Shelley              Higgins         20130420

6 행이 선택되었습니다.

EXEC DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('ASOF', TO_DATE('20120102'));

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
       TO_CHAR(VALID_TIME_START, 'YYYYMMDD') "Start",
       TO_CHAR(VALID_TIME_END, 'YYYYMMDD') "End"
FROM EMPLOYEES_TEMPORAL_TEST;

EMPLOYEE_ID FIRST_NAME           LAST_NAME       Start    End
----------- -------------------- --------------- -------- --------
        200 Jennifer             Whalen          20120101 20130215
        201 Michael              Hartstein       20120101 20130215


3. 자동 데이터 최적화 (ADO Automatic Data Optimizition )

  • 세그먼트 레벨이나 로우 레벨에서 데이터에 어떤 작업이 일어나는지를 인식
  • 데이터의 액세스나 수정은 Heat Map으로 추적
  • Heat Map 사용하고, 데이터의 액세스나 수정을 기반으로 언제 어떤 작업을 할지 결정

ADO 테이블 이동

  • 특정 테이블스페이스로부터 지정된 임계값을 초과하면 다른 테이블 스페이스로 이동

ALTER SYSTEM SET HEAT_MAP = ON SCOPE=BOTH;

SELECT NAME, VALUE FROM V$PARAMETER
 WHERE NAME LIKE 'heat%';

NAME                           VALUE
------------------------------ ------------------------------
heat_map                       ON


** 테이블 스페이스 생성
CREATE TABLESPACE T1_ILM_TBS
DATAFILE 'C:\ORACLEXE\ORADATA\ORCL\T1_ILM_TBS.DBF'
SIZE 10M REUSE AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL;


CREATE TABLESPACE T2_ILM_TBS
DATAFILE 'C:\ORACLEXE\ORADATA\ORCL\T2_ILM_TBS.DBF'
SIZE 10M REUSE AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL;

SELECT TABLESPACE_NAME, FILE_NAME
  FROM DBA_DATA_FILES
 WHERE TABLESPACE_NAME LIKE '%ILM%';

TABLESPACE_NAME      FILE_NAME
-------------------- --------------------------------------------------
T1_ILM_TBS           C:\ORACLEXE\ORADATA\ORCL\T1_ILM_TBS.DBF
T2_ILM_TBS           C:\ORACLEXE\ORADATA\ORCL\T2_ILM_TBS.DBF

SELECT DF.TABLESPACE_NAME "TABLESPACE",
       DF.BYTES / (1024*1024) "SIZE (MB)",
       SUM(FS.BYTES) / (1024*1024) "FREE (MB)",
       NVL(ROUND(SUM(FS.BYTES) *100 /DF.BYTES),1) "% FREE",
       ROUND((DF.BYTES - SUM(FS.BYTES)) * 100 / DF.BYTES) "% USED"
  FROM DBA_FREE_SPACE FS,
       (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES
          FROM DBA_DATA_FILES
        GROUP BY TABLESPACE_NAME) DF
 WHERE FS.TABLESPACE_NAME(+) = DF.TABLESPACE_NAME
   AND DF.TABLESPACE_NAME LIKE '%ILM%TBS'
 GROUP BY DF.TABLESPACE_NAME, DF.BYTES
 ORDER BY 1;

TABLESPACE                      SIZE (MB)  FREE (MB)     % FREE     % USED
------------------------------ ---------- ---------- ---------- ----------
T1_ILM_TBS                             10          9         90         10
T2_ILM_TBS                             10          9         90         10


ALTER USER HR IDENTIFIED BY HR;
ALTER USER HR ACCOUNT UNLOCK;
/


** 테이블 생성
CREATE TABLE HR.ILM_MOVE_TEST TABLESPACE T1_ILM_TBS
AS SELECT * FROM HR.EMPLOYEES;

** 데이터 생성
DECLARE
STOP_LOOP PLS_INTEGER := 6;
SQL_TEST CLOB;
BEGIN FOR I IN 1 .. STOP_LOOP
 LOOP
     SQL_TEST := 'INSERT /*+ APPEND */ INTO HR.ILM_MOVE_TEST
                  SELECT * FROM HR.ILM_MOVE_TEST';
     EXECUTE IMMEDIATE SQL_TEST;
     COMMIT;
 END LOOP;
END;
/

** 통계정보 수집
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR','ILM_MOVE_TEST');

SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
  FROM DBA_TABLES
 WHERE OWNER = 'HR' AND TABLE_NAME = 'ILM_MOVE_TEST'

OWNER      TABLE_NAME                     TABLESPACE_NAME
---------- ------------------------------ ------------------------------
HR         ILM_MOVE_TEST                  T1_ILM_TBS


SELECT COUNT(*) FROM HR.ILM_MOVE_TEST;

** SEGMENT_READ_TIME : 세그먼트가 읽기가 수행된 최종 타임 스템프
SELECT OBJECT_NAME, SEGMENT_READ_TIME
  FROM DBA_HEAT_MAP_SEGMENT
 WHERE OWNER = 'HR'
   AND OBJECT_NAME = 'ILM_MOVE_TEST';

** 테이블에 POLICY 추가
ALTER TABLE HR.ILM_MOVE_TEST
ILM ADD POLICY TIER TO T2_ILM_TBS;

SELECT CAST(POLICY_NAME AS VARCHAR2(30)) POLICY_NAME, ACTION_TYPE,
       SCOPE, COMPRESSION_LEVEL,
       CAST(TIER_TABLESPACE AS VARCHAR2(30)) TIER_TBS,
       CONDITION_TYPE, CONDITION_DAYS
  FROM DBA_ILMDATAMOVEMENTPOLICIES
 ORDER BY POLICY_NAME;

POLIC ACTION_TYPE SCOPE   COMPR TIER_TBS                      CONDITION_TYPE         CONDITION_DAYS
----- ----------- ------- ----- ---------------------------------------------------- --------------
P1    STORAGE     SEGMENT       T2_ILM_TBS                                    0

SELECT * FROM DBA_ILMOBJECTS;

SELECT * FROM DBA_ILMPARAMETERS
WHERE NAME LIKE 'TBS%PERCENT%';

NAME                                VALUE
------------------------------ ----------
TBS PERCENT USED                       85
TBS PERCENT FREE                       25


EXEC DBMS_ILM_ADMIN.CUSTOMIZE_ILM(DBMS_ILM_ADMIN.TBS_PERCENT_FREE,95);

EXEC DBMS_ILM_ADMIN.CUSTOMIZE_ILM(DBMS_ILM_ADMIN.TBS_PERCENT_USED,5);

SELECT * FROM DBA_ILMPARAMETERS
WHERE NAME LIKE 'TBS%PERCENT%';

NAME                                VALUE
------------------------------ ----------
TBS PERCENT USED                        5
TBS PERCENT FREE                       95

** HR 계정 사용 **
** DBMS_ILM.EXECUTE_ILM 페키지 수행으로 바로 확인 가능

DECLARE
  V_EXECUTIONID NUMBER;
  BEGIN
       DBMS_ILM.EXECUTE_ILM(ILM_SCOPE=>DBMS_ILM.SCOPE_SCHEMA,
                            EXECUTION_MODE => DBMS_ILM.ILM_EXECUTION_OFFLINE,
                            TASK_ID => V_EXECUTIONID);
  END;
/


SELECT UIT.TASK_ID, UIR.JOB_NAME, UIR.JOB_STATE,
       UIT.CREATION_TIME,
       UIT.START_TIME,
       UIR.COMPLETION_TIME,
       UIR.COMPLETION_TIME-UIT.START_TIME
  FROM USER_ILMTASKS UIT, USER_ILMRESULTS UIR
 WHERE UIT.TASK_ID = UIR.TASK_ID
 ORDER BY UIT.CREATION_TIME DESC;


SELECT TABLE_NAME, TABLESPACE_NAME
  FROM DBA_TABLES
 WHERE TABLE_NAME = 'ILM_MOVE_TEST';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
ILM_MOVE_TEST                  T2_ILM_TBS

ADO Table Compression


CREATE OR REPLACE PROCEDURE ADJ_TIME (OBJECT_ID NUMBER, DATA_OBJECT_ID NUMBER,
N_DAYS NUMBER,
P_TS# NUMBER,
P_SEGMENT_ACCESS NUMBER)
AS
BEGIN
    INSERT INTO SYS.HEAT_MAP_STAT$(OBJ#,DATAOBJ#,TRACK_TIME,SEGMENT_ACCESS, TS#)
        VALUES (OBJECT_ID, DATA_OBJECT_ID, SYSDATE - N_DAYS, P_SEGMENT_ACCESS, P_TS#);
COMMIT;
END;
/

** HEAT_MAP 활성화
ALTER SYSTEM SET HEAT_MAP =ON SCOPE=BOTH;

** 테이블 생성
CREATE TABLE HR.ILM_COMPRESSION_TEST TABLESPACE USERS
AS SELECT * FROM HR.EMPLOYEES;

DECLARE
STOP_LOOP PLS_INTEGER := 6;
SQL_TEST CLOB;
BEGIN FOR I IN 1 .. STOP_LOOP
 LOOP
     SQL_TEST := 'INSERT /*+ APPEND */ INTO HR.ILM_COMPRESSION_TEST
                  SELECT * FROM HR.ILM_COMPRESSION_TEST';
     EXECUTE IMMEDIATE SQL_TEST;
     COMMIT;
 END LOOP;
END;
/

SELECT COUNT(*) FROM HR.ILM_COMPRESSION_TEST

SELECT SEGMENT_NAME, BYTES/1024/1024 MB
  FROM DBA_SEGMENTS
 WHERE OWNER = 'HR'
   AND SEGMENT_NAME = 'ILM_COMPRESSION_TEST';

SEGMENT_NAME                 MB
-------------------- ----------
ILM_COMPRESSION_TEST      .6875

** HEAT_MAP 에 의해서 추적되는 테이블인지 확인

SELECT OBJECT_NAME, FULL_SCAN
  FROM DBA_HEAT_MAP_SEGMENT
WHERE OWNER = 'HR'
  AND OBJECT_NAME = 'ILM_COMPRESSION_TEST';

OBJECT_NAME          FULL_SCA
-------------------- --------
ILM_COMPRESSION_TEST


** ACTIVITY : Low Access, No Access, no midification, creation

ALTER TABLE HR.ILM_COMPRESSION_TEST
ILM ADD POLICY ROW STORE COMPRESS
ADVANCED SEGMENT AFTER 30 DAYS OF NO MODIFICATION;


DECLARE
V_OBJECT_ID NUMBER;
V_DATAOBJ_ID NUMBER;
V_TS_NUM NUMBER;
BEGIN
  SELECT OBJECT_ID, DATA_OBJECT_ID INTO V_OBJECT_ID, V_DATAOBJ_ID
   FROM ALL_OBJECTS
  WHERE OBJECT_NAME = 'ILM_COMPRESSION_TEST'
    AND OWNER = 'HR';

  SELECT TS# INTO V_TS_NUM
   FROM SYS.TS$ A, DBA_SEGMENTS B
  WHERE A.NAME = B.TABLESPACE_NAME
    AND B.SEGMENT_NAME = 'ILM_COMPRESSION_TEST';
COMMIT;
SYS.ADJ_TIME
(OBJECT_ID => V_OBJECT_ID,
 DATA_OBJECT_ID => V_DATAOBJ_ID,
 N_DAYS => 30,
 P_TS# => V_TS_NUM,
 P_SEGMENT_ACCESS => 1);
END;
/

** 테이블에 압축 여부 확인
SELECT COMPRESSION, COMPRESS_FOR
  FROM DBA_TABLES
 WHERE OWNER = 'HR'
   AND TABLE_NAME = 'ILM_COMPRESSION_TEST';

COMPRESS COMPRESS_FOR
-------- ------------------------------
DISABLED

** 업데이트된 통계정보를 바로 보기 위해서 데이터베이스 재시작
SHUTDOWN IMMEDIATE
STARTUP

SELECT OWNER, OBJECT_NAME, SEGMENT_WRITE_TIME
  FROM DBA_HEAT_MAP_SEGMENT
 WHERE OBJECT_NAME = 'ILM_COMPRESSION_TEST';

OWNER                          OBJECT_NAME          SEGMENT_
------------------------------ -------------------- --------
HR                             ILM_COMPRESSION_TEST 16/12/19

DECLARE
V_EXECUTIONID NUMBER;
BEGIN
 DBMS_ILM.EXECUTE_ILM(ILM_SCOPE => DBMS_ILM.SCOPE_SCHEMA,
   EXECUTION_MODE => DBMS_ILM.ILM_EXECUTION_OFFLINE,
   TASK_ID => V_EXECUTIONID);
END;
/

SELECT UIT.TASK_ID, UIR.JOB_NAME, UIR.JOB_STATE,
       UIT.CREATION_TIME,
       UIT.START_TIME,
       UIR.COMPLETION_TIME,
       UIR.COMPLETION_TIME-UIT.START_TIME
  FROM USER_ILMTASKS UIT, USER_ILMRESULTS UIR
 WHERE UIT.TASK_ID = UIR.TASK_ID
 ORDER BY UIT.CREATION_TIME DESC;

** 통계정보 수집
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'ILM_COMPRESSION_TEST');

** 압축 여부 확인
SELECT COMPRESSION, COMPRESS_FOR
  FROM DBA_TABLES
 WHERE TABLE_NAME = 'ILM_COMPRESSION_TEST';

COMPRESS COMPRESS_FOR
-------- ------------------------------
DISABLED ADVANCED

SELECT SEGMENT_NAME, BYTES/1024/1024 MB
  FROM DBA_SEGMENTS
 WHERE SEGMENT_NAME = 'ILM_COMPRESSION_TEST';

SEGMENT_NAME                 MB
-------------------- ----------
ILM_COMPRESSION_TEST      .5


Temporal History

  • 특정 테이블에 대한 변경 이력을 저장하는 사이즈와 보존 기간을 정의
  • After the specified retention period expires, data is automatically purged to conserve space
  • If an archive quota is exceeded, new transactions are blocked.

1. 테이블 스페이스 생성
create tablespace fda_ts datafile
'/u01/app/oracle/oradata/fda_ts_od.dbf' size 15M;

2. Flashback Archive 생성
 CREATE FLASHBACK ARCHIVE DEFAULT
FLA01 TABLESPACE FDA_TS
QUOTA 10G RETENTION 1 YEAR;


3. 테이블 생성
CREATE TABLE HR.TEST123(COL1 VARCHAR2(10), COL2 VARCHAR2(10))
FLASHBACK ARCHIVE FLA01;


5.성능 개선 사항

"구루비 데이터베이스 스터디모임" 에서 2016년에 "클라우드 데이터베이스 Oracle 12c 가이드" 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3323

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입