Shulou(Shulou.com)06/01 Report--

Recently, in order to use the powerful inmemory function of 12c, we have made a new OGG data synchronization from 11gR2 RAC to 12c, sharing the olap business of the main library.

Among them, there are several partition tables, which are self-increasing by month, caching data for only half a year, so that on the 1st of each month, you need to cache the partition of the new month, and at the same time, you need to remove the partition from 6 months ago, so write a process and make a simple record.

Click (here) to collapse or open

CREATE OR REPLACE PROCEDURE PARTITION_CACHE

IS

TYPE REF_CURSOR_TYPE IS REF CURSOR

C1 REF_CURSOR_TYPE;-dynamic cursor, which is used to cycle through five tables that need to be cached

V_HIGHVALUE DBA_TAB_PARTITIONS.HIGH_VALUE%TYPE;-since the partition name is uncertain, you need to obtain the partition name according to the maximum value of the partition

V_PARTNAME DBA_TAB_PARTITIONS.PARTITION_NAME%TYPE;-- Partition name

V_HIGHMONTH NVARCHAR2 (10);-- the maximum partition value is intercepted into month format

V_CURRMONTH NVARCHAR2 (10);-current month

V_OLDMONTH NVARCHAR2 (10);-month 6 months ago

V_SQL1 NVARCHAR2 (1000);-- remove the sql of the old partition

V_SQL2 NVARCHAR2 (1000);-- cache the sql of the new partition

V_SQL VARCHAR2 (1000);-- get the partition name and the sql of the maximum partition value

BEGIN

V_CURRMONTH: = TO_CHAR (ADD_MONTHS (SYSDATE,1), 'YYYY-MM')

V_OLDMONTH: = TO_CHAR (ADD_MONTHS (SYSDATE,-6), 'YYYY-MM')

DECLARE CURSOR C2 IS SELECT * FROM DBA_TABLES WHERE TABLE_NAME IN ('TABLE1','TABLE2','TABLE3','TABLE4','TABLE5') ORDER BY TABLE_NAME

BEGIN

FOR TABNAME IN C2-5 tables for cyclic operation

LOOP

V_SQL: = 'SELECT HIGH_VALUE,PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME =''| | TABNAME.TABLE_NAME | |''ORDER BY PARTITION_POSITION DESC'

OPEN C1 FOR V_SQL

FETCH C1 INTO V_HIGHVALUE,V_PARTNAME

WHILE (C1%FOUND) LOOP

V_HIGHMONTH: = SUBSTR (Vendor HIGHVALUE 12pr 7)

IF (V_HIGHMONTH = V_OLDMONTH) THEN

DBMS_OUTPUT.PUT_LINE ('Table name:' | TABNAME.TABLE_NAME | | 'partition name to be removed:' | | V_PARTNAME | | 'maximum partition:' | | V_OLDMONTH)

V_SQL1: = 'ALTER TABLE CCPS.' | | TABNAME.TABLE_NAME | |' MODIFY PARTITION'| | V_PARTNAME | |'NO INMEMORY'

DBMS_OUTPUT.PUT_LINE ('SQL:' | | V_SQL1)

ELSIF (V_HIGHMONTH = V_CURRMONTH) THEN

DBMS_OUTPUT.PUT_LINE ('Table name:' | TABNAME.TABLE_NAME | | 'cache partition name:' | | V_PARTNAME | | 'maximum partition:' | | V_HIGHMONTH)

V_SQL2: = 'ALTER TABLE CCPS.' | | TABNAME.TABLE_NAME | |' MODIFY PARTITION'| | V_PARTNAME | | 'INMEMORY PRIORITY CRITICAL'

DBMS_OUTPUT.PUT_LINE ('SQL:' | | V_SQL2)

END IF

FETCH C1 INTO V_HIGHVALUE,V_PARTNAME

END LOOP

CLOSE C1

DBMS_OUTPUT.PUT_LINE ('')

DBMS_OUTPUT.PUT_LINE ('* *')

END LOOP

END

END; here is just a test to print sql, and change it online to OK.

Just contact with PLSQL, cursor and so on, just make a simple record.

Example of code running result:

SQL > exec partition_cache

Table name: TABLE1 needs cache partition name: SYS_P1167 partition maximum value: 2018-07

SQL: ALTER TABLE CCPS.TABLE1 MODIFY PARTITION SYS_P1167 INMEMORY PRIORITY CRITICAL

Table name: TABLE1 to remove partition name: SYS_P388 partition maximum value: 2017-12

SQL: ALTER TABLE CCPS.TABLE1 MODIFY PARTITION SYS_P388 NO INMEMORY

* * *

Table name: TABLE2 to remove partition name: SYS_P588 partition maximum value: 2017-12

SQL: ALTER TABLE CCPS.TABLE2 MODIFY PARTITION SYS_P588 NO INMEMORY

* * *

Table name: TABLE3 needs cache partition name: SYS_P1168 partition maximum value: 2018-07

SQL: ALTER TABLE CCPS.TABLE3 MODIFY PARTITION SYS_P1168 INMEMORY PRIORITY CRITICAL

Table name: TABLE3 to remove partition name: SYS_P328 partition maximum value: 2017-12

SQL: ALTER TABLE CCPS.TABLE3 MODIFY PARTITION SYS_P328 NO INMEMORY

* * *

Table name: TABLE4 to remove partition name: SYS_P368 partition maximum value: 2017-12

SQL: ALTER TABLE CCPS.TABLE4 MODIFY PARTITION SYS_P368 NO INMEMORY

* * *

Table name: TABLE5 needs cache partition name: SYS_P1166 partition maximum value: 2018-07

SQL: ALTER TABLE CCPS.TABLE5 MODIFY PARTITION SYS_P1166 INMEMORY PRIORITY CRITICAL

Table name: TABLE5 to remove partition name: SYS_P428 partition maximum value: 2017-12

SQL: ALTER TABLE CCPS.TABLE5 MODIFY PARTITION SYS_P428 NO INMEMORY

* * *

PL/SQL procedure successfully completed

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.