Oracle Database 12c????Information Lifecycle Management ILM ?????????Storage Enhancements ????????
  Lifecycle Management ILM ????????? Automatic Data Placement ??????, ??ADP?
  ?????? 12c???????Datafile??? Online Move Datafile, ????????????????datafile???????,???????????????
  ????(12.1.0.1)Automatic Data Optimization?heat map????????:
   
  
    ????????? (CDB)?????Automatic Data Optimization?heat map
    Row-level policies for ADO are not supported for Temporal Validity. Partition-level ADO and compression are supported if partitioned on the end-time columns.
    Row-level policies for ADO are not supported for in-database archiving. Partition-level ADO and compression are supported if partitioned on the ORA_ARCHIVE_STATE column.
    Custom policies (user-defined functions) for ADO are not supported if the policies default at the tablespace level.
    ADO does not perform checks for storage space in a target tablespace when using storage tiering.
    ADO is not supported on tables with object types or materialized views.
    ADO concurrency (the number of simultaneous policy jobs for ADO) depends on the concurrency of the Oracle scheduler. If a policy job for ADO fails more than two times, then the job is marked disabled and the job must be manually enabled later.
    Policies for ADO are only run in the Oracle Scheduler maintenance windows. Outside of the maintenance windows all policies are stopped. The only exceptions are those jobs for rebuilding indexes in ADO offline mode.
    ADO has restrictions related to moving tables and table partitions.
  
   
   
  ??????row,segment???????????ADO??,?????create table?alter table?????? ????ADO??,??????????????,???????????????? storage tier , ?????????storage tier?????????, ??????????????ADO??????????? segment?row??group?
  ?CREATE TABLE?ALERT TABLE???ILM???,??????????????????ADO policy? ??ILM policy???????????????? ??????? ????ADO policy, ?????alter table  ???????,??????????????
   
  CREATE TABLE sales_ado
 (PROD_ID NUMBER NOT NULL,
  CUST_ID NUMBER NOT NULL,
  TIME_ID DATE NOT NULL,
  CHANNEL_ID NUMBER NOT NULL,
  PROMO_ID NUMBER NOT NULL,
  QUANTITY_SOLD NUMBER(10,2) NOT NULL,
  AMOUNT_SOLD NUMBER(10,2) NOT NULL )
  ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH SEGMENT
      AFTER 6 MONTHS OF NO ACCESS;
SQL> SELECT SUBSTR(policy_name,1,24) AS POLICY_NAME, policy_type, enabled
  2         FROM USER_ILMPOLICIES;
POLICY_NAME          POLICY_TYPE                ENABLED
-------------------- -------------------------- --------------
P41                  DATA MOVEMENT              YES
ALTER TABLE sales MODIFY PARTITION sales_1995 
     ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH SEGMENT 
     AFTER 6 MONTHS OF NO ACCESS;
SELECT SUBSTR(policy_name,1,24) AS POLICY_NAME, policy_type, enabled 
   FROM USER_ILMPOLICIES;
POLICY_NAME              POLICY_TYPE   ENABLE
------------------------ ------------- ------
P1                       DATA MOVEMENT YES
P2                       DATA MOVEMENT YES
/* You can disable an ADO policy with the following */
ALTER TABLE sales_ado ILM DISABLE POLICY P1;
/* You can delete an ADO policy with the following */
ALTER TABLE sales_ado ILM DELETE POLICY P1;
/* You can disable all ADO policies with the following */
ALTER TABLE sales_ado ILM DISABLE_ALL;
/* You can delete all ADO policies with the following */
ALTER TABLE sales_ado ILM DELETE_ALL;
/* You can disable an ADO policy in a partition with the following */
ALTER TABLE sales MODIFY PARTITION sales_1995 ILM DISABLE POLICY P2;
/* You can delete an ADO policy in a partition with the following */
ALTER TABLE sales MODIFY PARTITION sales_1995 ILM DELETE POLICY P2;
   
  ILM ???????:
   
  
   
  
   
  
   
  
  ?????ILM ADP????,???????:
  ?????? ???? activity tracking, ????2????????,???????????????????:
  
    SEGMENT-LEVEL????????????????????
    ROW-LEVEL????????,???????
  
   
  ????????:
  1??????? SEGMENT-LEVEL activity tracking
  ALTER TABLE interval_sales ILM  ENABLE ACTIVITY TRACKING SEGMENT ACCESS
  ???????INTERVAL_SALES??segment level  activity tracking,??????????????????
  2? ???????????
  ALTER TABLE emp ILM ENABLE ACTIVITY TRACKING (CREATE TIME , WRITE TIME);
   
  3?????????
  ALTER TABLE emp ILM ENABLE ACTIVITY TRACKING  (READ TIME);
  ?12.1.0.1.0?????? ??HEAT_MAP??????????, ?????system??session?????heap_map?????????????
  ?????????HEAT MAP??,?
  ALTER SYSTEM SET HEAT_MAP = ON;
  ?HEAT MAP??????,??????????????????????????  ??SYSTEM?SYSAUX?????????????
   
  ???????HEAT MAP??:
  ALTER SYSTEM SET HEAT_MAP = OFF;
  ????? HEAT_MAP????, ?HEAT_MAP??? ??????????????????????
   
  ?HEAT_MAP?????????Automatic Data Optimization (ADO)??? ??ADO??,Heat Map ??????????
   
  ????V$HEAT_MAP_SEGMENT ??????? HEAT MAP??
   
   
  SQL> select * from V$heat_map_segment;
no rows selected
SQL> alter session set heat_map=on;
Session altered.
SQL> select * from scott.emp;
     EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK          7902 17-DEC-80        800            20
      7499 ALLEN      SALESMAN          7698 20-FEB-81       1600        300       30
      7521 WARD       SALESMAN          7698 22-FEB-81       1250        500       30
      7566 JONES      MANAGER          7839 02-APR-81       2975            20
      7654 MARTIN     SALESMAN          7698 28-SEP-81       1250       1400       30
      7698 BLAKE      MANAGER          7839 01-MAY-81       2850            30
      7782 CLARK      MANAGER          7839 09-JUN-81       2450            10
      7788 SCOTT      ANALYST          7566 19-APR-87       3000            20
      7839 KING       PRESIDENT        17-NOV-81       5000            10
      7844 TURNER     SALESMAN          7698 08-SEP-81       1500      0       30
      7876 ADAMS      CLERK          7788 23-MAY-87       1100            20
      7900 JAMES      CLERK          7698 03-DEC-81        950            30
      7902 FORD       ANALYST          7566 03-DEC-81       3000            20
      7934 MILLER     CLERK          7782 23-JAN-82       1300            10
14 rows selected.
SQL> select * from v$heat_map_segment;
OBJECT_NAME          SUBOBJECT_NAME             OBJ#   DATAOBJ# TRACK_TIM SEG SEG FUL LOO     CON_ID
-------------------- -------------------- ---------- ---------- --------- --- --- --- --- ----------
EMP                                            92997      92997 23-JUL-13 NO  NO  YES NO           0
   
   
  ??v$heat_map_segment???,?v$heat_map_segment??????????????X$HEATMAPSEGMENT
   
  V$HEAT_MAP_SEGMENT displays real-time segment access information.
  
    
      
        
          Column
          Datatype
          Description
        
      
      
        
          OBJECT_NAME
          VARCHAR2(128)
          Name of the object
        
        
          SUBOBJECT_NAME
          VARCHAR2(128)
          Name of the subobject
        
        
          OBJ#
          NUMBER
          Object number
        
        
          DATAOBJ#
          NUMBER
          Data object number
        
        
          TRACK_TIME
          DATE
          Timestamp of current activity tracking
        
        
          SEGMENT_WRITE
          VARCHAR2(3)
          Indicates whether the segment has write access: (YES or NO)
        
        
          SEGMENT_READ
          VARCHAR2(3)
          Indicates whether the segment has read access: (YES or NO)
        
        
          FULL_SCAN
          VARCHAR2(3)
          Indicates whether the segment has full table scan: (YES or NO)
        
        
          LOOKUP_SCAN
          VARCHAR2(3)
          Indicates whether the segment has lookup scan: (YES or NO)
        
        
          CON_ID
          NUMBER
          The ID of the container to which the data pertains. Possible values include:
             
            
              0: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs.
              1: This value is used for rows containing data that pertain to only the root
              n: Where n is the applicable container ID for the rows containing data
            
            The Heat Map feature is not supported in CDBs in Oracle Database 12c, so the value in this column can be ignored.
          
        
      
    
  
   
   
  ??HEAP MAP??????????????????,????DBA_HEAT_MAP_SEGMENT???????? ???????HEAT_MAP_STAT$??????
   
  ??Automatic Data Optimization??????:
   
  
   
   
  ????1:
  SQL> alter system set heat_map=on;
??????
????????????? scott??
http://www.askmaclean.com/archives/scott-schema-script.html
SQL> grant all on dbms_lock to scott;
?????
 SQL> grant dba to scott;
?????
@ilm_setup_basic C:\APP\XIANGBLI\ORADATA\MACLEAN\ilm.dbf
@tktgilm_demo_env_setup 
SQL> connect scott/tiger ;
????
SQL> select count(*) from scott.employee;
  COUNT(*)
----------
      3072
??? 1 ??
SQL> set serveroutput on
SQL> exec print_compression_stats('SCOTT','EMPLOYEE');
Compression Stats
------------------
Uncmpressed           : 3072
Adv/basic compressed  : 0
Others                : 0
PL/SQL ????????
???????3072??????
????????? ????policy ????????????
alter table employee ilm 
      add policy row store compress advanced row 
      after 3 days of no modification 
/ 
SQL> set serveroutput on
SQL> execute list_ilm_policies;
--------------------------------------------------
Policies defined for SCOTT
--------------------------------------------------
Object Name------ : EMPLOYEE
Subobject Name--- :
Object Type------ : TABLE
Inherited from--- : POLICY NOT INHERITED
Policy Name------ : P1
Action Type------ : COMPRESSION
Scope------------ : ROW
Compression level : ADVANCED
Tier Tablespace-- :
Condition type--- : LAST MODIFICATION TIME
Condition days--- : 3
Enabled---------- :   YES
--------------------------------------------------
PL/SQL ????????
SQL> select sysdate from dual;
SYSDATE
--------------
29-7? -13
SQL> execute set_back_chktime(get_policy_name('EMPLOYEE',null,'COMPRESSION','ROW','ADVANCED',3,null,null),'EMPLOYEE',null,6);
Object check time reset ...
--------------------------------------
Object Name    : EMPLOYEE
Object Number  : 93123
D.Object Numbr : 93123
Policy Number  : 1
Object chktime : 23-7? -13 08.13.42.000000 ??
Distnt chktime : 0
--------------------------------------
PL/SQL ????????
?policy?chktime???6??, ????set_back_chktime???????????????“????”?,?????????,????????
??????
 alter system flush buffer_cache;
 alter system flush buffer_cache;
 alter system flush shared_pool;
 alter system flush shared_pool;
SQL> execute set_window('MONDAY_WINDOW','OPEN');
Set Maint. Window  OPEN
-----------------------------
Window Name   : MONDAY_WINDOW
Enabled?      : TRUE
Active?       : TRUE
-----------------------------
PL/SQL ????????
SQL> exec dbms_lock.sleep(60) ;
PL/SQL ????????
SQL> exec print_compression_stats('SCOTT', 'EMPLOYEE');
Compression Stats
------------------
Uncmpressed           : 338
Adv/basic compressed  : 2734
Others                : 0
PL/SQL ????????
??????????????? Adv/basic compressed  : 2734 ???????
SQL> col object_name for a20
SQL> select object_id,object_name from dba_objects where object_name='EMPLOYEE';
 OBJECT_ID OBJECT_NAME
---------- --------------------
     93123 EMPLOYEE
SQL> execute list_ilm_policy_executions ;
--------------------------------------------------
Policies execution details for SCOTT
--------------------------------------------------
Policy Name------ : P22
Job Name--------- : ILMJOB48
Start time------- : 29-7? -13 08.37.45.061000 ??
End time--------- : 29-7? -13 08.37.48.629000 ??
-----------------
Object Name------ : EMPLOYEE
Sub_obj Name----- :
Obj Type--------- : TABLE
-----------------
Exec-state------- : SELECTED FOR EXECUTION
Job state-------- : COMPLETED SUCCESSFULLY
Exec comments---- :
Results comments- :
---
--------------------------------------------------
PL/SQL ????????
ILMJOB48?????policy?JOB,?12.1.0.1??J00x????
?MMON_SLAVE???M00x???15?????????
select sample_time,program,module,action from v$active_session_history where    action  ='KDILM background EXEcution'  order by sample_time;
29-7? -13 08.16.38.369000000 ??	ORACLE.EXE (M000)	MMON_SLAVE	KDILM background EXEcution
29-7? -13 08.17.38.388000000 ??	ORACLE.EXE (M000)	MMON_SLAVE	KDILM background EXEcution
29-7? -13 08.17.39.390000000 ??	ORACLE.EXE (M000)	MMON_SLAVE	KDILM background EXEcution
29-7? -13 08.23.38.681000000 ??	ORACLE.EXE (M002)	MMON_SLAVE	KDILM background EXEcution
29-7? -13 08.32.38.968000000 ??	ORACLE.EXE (M000)	MMON_SLAVE	KDILM background EXEcution
29-7? -13 08.33.39.993000000 ??	ORACLE.EXE (M003)	MMON_SLAVE	KDILM background EXEcution
29-7? -13 08.33.40.993000000 ??	ORACLE.EXE (M003)	MMON_SLAVE	KDILM background EXEcution
29-7? -13 08.36.40.066000000 ??	ORACLE.EXE (M000)	MMON_SLAVE	KDILM background EXEcution
29-7? -13 08.37.42.258000000 ??	ORACLE.EXE (M000)	MMON_SLAVE	KDILM background EXEcution
29-7? -13 08.37.43.258000000 ??	ORACLE.EXE (M000)	MMON_SLAVE	KDILM background EXEcution
29-7? -13 08.37.44.258000000 ??	ORACLE.EXE (M000)	MMON_SLAVE	KDILM background EXEcution
29-7? -13 08.38.42.386000000 ??	ORACLE.EXE (M001)	MMON_SLAVE	KDILM background EXEcution 
select distinct action  from v$active_session_history where    action like 'KDILM%' 
KDILM background CLeaNup
KDILM background EXEcution
SQL> execute set_window('MONDAY_WINDOW','CLOSE');
Set Maint. Window  CLOSE
-----------------------------
Window Name   : MONDAY_WINDOW
Enabled?      : TRUE
Active?       : FALSE
-----------------------------
PL/SQL ????????
SQL> drop table employee purge ;
?????
???? ?????
spool ilm_usecase_1_cleanup.lst
@ilm_demo_cleanup ;
spool off