Thinking Out Loud

July 6, 2011

Renaming Interval Partitions

Filed under: 11g,oracle — mdinh @ 3:56 am

I wrote a stored procedure to RENAME USER system generated name for interval partition tables and LOCAL indexes.

One pertinent information on >>Interval Partitioning

  • Can only partition on one key column and must be NUMBER or DATE type

>>NUMTOYMINTERVAL must resolve to the following values: ‘YEAR’,’MONTH’
>>NUMTODSINTERVAL must resolve to the following values: ‘DAY’,’HOUR’,’MINUTE’,’SECOND’

System generated partitions.

Renamed partitions.

Example for renaming interval partition.

Update ONE table: exec mdinh_pkg.ren_interval_part('&table');

Update ALL tables: exec mdinh_pkg.ren_interval_part;

Package to rename interval partition.

CREATE OR REPLACE PACKAGE mdinh_pkg AUTHID CURRENT_USER
IS
--
--  RENAME USER system generated name for interval partition tables and LOCAL indexes
--
--  NUMERIC interval partition format: Pnnn
--    where nnn: ROUND(user_tab_partitions.high_value/user_part_tables.interval)
--
--  DATE interval partition format
--    YEAR:  Pyyyy
--    MONTH: Pyyyymm
--    DAY:   Pyyyymmdd
--
--  mdinh: 2011-Jul-06
--
    PROCEDURE ren_interval_part(p_table VARCHAR2 DEFAULT NULL);
END;
/
CREATE OR REPLACE PACKAGE BODY mdinh_pkg
AS
PROCEDURE ren_interval_part(p_table VARCHAR2 DEFAULT NULL)
IS
    l_sql VARCHAR2 (4000);
	l_fmt VARCHAR2 (30);
    l_par VARCHAR2 (30);
BEGIN
    FOR x IN (
        SELECT a.table_name, NULL index_name, a.partition_name, a.partition_position, a.high_value, b.interval
        FROM   user_tab_partitions a, user_part_tables b
        WHERE  a.table_name = b.table_name
        AND    a.interval = 'YES' AND REGEXP_LIKE(b.interval,'^[0-9]|DAY|MONTH|YEAR','i')
        AND    a.table_name = NVL(UPPER(p_table),a.table_name)
        AND    partition_name LIKE 'SYS\_P%' ESCAPE '\'
        UNION ALL
        SELECT d.table_name, c.index_name, c.partition_name, c.partition_position, c.high_value, d.interval
        FROM   user_ind_partitions c, user_part_indexes d
        WHERE  c.index_name = d.index_name
        AND    c.interval = 'YES' AND REGEXP_LIKE(d.interval,'^[0-9]|DAY|MONTH|YEAR','i')
        AND    d.table_name = NVL(UPPER(p_table),d.table_name)
        AND    partition_name LIKE 'SYS\_P%' ESCAPE '\'
        ORDER BY 1,2 NULLS FIRST,4 ASC
    ) LOOP
        IF INSTR(x.interval,'YEAR') > 0 THEN
            l_fmt := q'#,'"P"yyyy'#';
            l_sql := 'SELECT TO_CHAR('|| x.high_value||'-'||x.interval||l_fmt||') from dual';
        ELSIF INSTR(x.interval,'MONTH') > 0 THEN
            l_fmt := q'#,'"P"yyyymm'#';
            l_sql := 'SELECT TO_CHAR('|| x.high_value||'-'||x.interval||l_fmt||') from dual';
        ELSIF INSTR(x.interval,'DAY') > 0 THEN
            l_fmt := q'#,'"P"yyyymmdd'#';
            l_sql := 'SELECT TO_CHAR('|| x.high_value||'-'||x.interval||l_fmt||') from dual';
        ELSE
            l_fmt := 'P'||LPAD(ROUND(TO_NUMBER(x.high_value)/TO_NUMBER(x.interval)),3,0);
            l_sql := 'SELECT '''||l_fmt||''' from dual';
        END IF;
--
        EXECUTE IMMEDIATE l_sql INTO l_par;
--        
        IF x.index_name IS NULL THEN
            l_sql := 'ALTER TABLE '|| x.table_name|| ' RENAME PARTITION '|| x.partition_name|| ' TO '|| l_par;
        ELSE
            l_sql := 'ALTER INDEX '|| x.index_name|| ' RENAME PARTITION '|| x.partition_name|| ' TO '|| l_par;
        END IF;
--
        EXECUTE IMMEDIATE l_sql;
--        
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN RAISE;
END;
--
END;
/

Please node the original stored procedure above contains BUG where index for the table is owned by a different owner. BUG fixed in below PL/SQL code.

set echo off
set lines 300 pages 200 serveroutput on size unlimited trimsp on tab off feedb off verify off echo on
DECLARE
  l_sql VARCHAR2 (4000);
  l_fmt VARCHAR2 (30);
  l_par VARCHAR2 (30);
BEGIN
  FOR x IN (
    SELECT a.table_name, NULL index_name, a.partition_name,
           a.partition_position, a.high_value, b.interval
    FROM   dba_tab_partitions a, dba_part_tables b
    WHERE  a.table_name = b.table_name
    AND    a.interval = 'YES'
    AND    REGEXP_LIKE(b.interval,'^[0-9]|DAY|MONTH|YEAR','i')
    AND    a.table_owner = UPPER('&&owner')
    AND    a.table_name = UPPER('&&table')
    AND    partition_name LIKE 'SYS\_P%' ESCAPE '\'
    UNION ALL
    SELECT d.table_name, c.index_owner||'.'||c.index_name index_name,
           c.partition_name, c.partition_position, c.high_value, d.interval
    FROM   dba_ind_partitions c, dba_part_indexes d
    WHERE  c.index_name = d.index_name
    AND    c.interval = 'YES'
    AND    REGEXP_LIKE(d.interval,'^[0-9]|DAY|MONTH|YEAR','i')
    AND    d.table_name = UPPER('&&table')
    AND    partition_name LIKE 'SYS\_P%' ESCAPE '\'
    ORDER BY 1,2 NULLS FIRST,4 ASC
  )
  LOOP
    IF INSTR(x.interval,'YEAR') > 0 THEN
      l_fmt := q'#,'"P"yyyy'#';
      l_sql := 'SELECT TO_CHAR('|| x.high_value||'-'||x.interval||l_fmt||') from dual';
    ELSIF INSTR(x.interval,'MONTH') > 0 THEN
      l_fmt := q'#,'"P"yyyymm'#';
      l_sql := 'SELECT TO_CHAR('|| x.high_value||'-'||x.interval||l_fmt||') from dual';
    ELSIF INSTR(x.interval,'DAY') > 0 THEN
      l_fmt := q'#,'"P"yyyymmdd'#';
      l_sql := 'SELECT TO_CHAR('|| x.high_value||'-'||x.interval||l_fmt||') from dual';
    ELSE
      l_fmt := 'P'||LPAD(ROUND(TO_NUMBER(x.high_value)/TO_NUMBER(x.interval)),3,0);
      l_sql := 'SELECT '''||l_fmt||''' from dual';
    END IF;
    EXECUTE IMMEDIATE l_sql INTO l_par;
    IF x.index_name IS NULL THEN
      l_sql := 'ALTER TABLE '|| x.table_name|| ' RENAME PARTITION '|| x.partition_name|| ' TO '|| l_par;
    ELSE
      l_sql := 'ALTER INDEX '|| x.index_name|| ' RENAME PARTITION '|| x.partition_name|| ' TO '|| l_par;
    END IF;
    EXECUTE IMMEDIATE l_sql;
--  dbms_output.put_line(l_sql);
  END LOOP;
END;
/

Query to check partitions. Column numtoyminterval will display interval.

set lines 200 pages 10000 tab off trimspool off timing off verify off feedback off
col table_name for a20
col partition_name for a21
col high_value for a56 trunc
col compress_for for a12
col pos for 999999
col numtoyminterval for a27
set echo on
select p.table_name, p.partition_name, p.partition_position pos, p.interval, p.compression, p.compress_for,  p.high_value, t.interval numtoyminterval
from dba_tab_partitions p, dba_part_tables t
where p.table_owner=t.owner
and p.table_owner = UPPER('&owner')
and p.table_name = UPPER('&table')
and t.table_name=p.table_name
and regexp_like(t.interval,'^[0-9]|DAY|MONTH|YEAR')
order by partition_position asc
;
set echo off

Sample results from query:

TABLE_NAME           PARTITION_NAME        PART_POS INT COMPRESS COMPRESS_FOR HIGH_VALUE                                               NUMTOYMINTERVAL
-------------------- --------------------- -------- --- -------- ------------ -------------------------------------------------------- ------------------------------
TRANSACTION          P201112                      1 NO  ENABLED  ADVANCED     TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
TRANSACTION          P201201                      2 NO  ENABLED  ADVANCED     TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
TRANSACTION          P201202                      3 NO  ENABLED  ADVANCED     TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
TRANSACTION          P201203                      4 NO  ENABLED  ADVANCED     TO_DATE(' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
TRANSACTION          P201204                      5 NO  ENABLED  ADVANCED     TO_DATE(' 2012-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
TRANSACTION          P201205                      6 NO  ENABLED  ADVANCED     TO_DATE(' 2012-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
TRANSACTION          P201206                      7 NO  ENABLED  ADVANCED     TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
TRANSACTION          P201207                      8 NO  ENABLED  ADVANCED     TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
TRANSACTION          P201208                      9 NO  ENABLED  ADVANCED     TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
TRANSACTION          P201209                     10 NO  ENABLED  ADVANCED     TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
TRANSACTION          SYS_P35716                  11 YES ENABLED  ADVANCED     TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
TRANSACTION          SYS_P35715                  12 YES ENABLED  ADVANCED     TO_DATE(' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')

Create a free website or blog at WordPress.com.