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')
Thinking Out Loud 