클라우드 데이터베이스 Oracle 12c 가이드 (2016년)
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
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.