partitioned table.
To solve this issue it's possible to partition a non-partitioned table using three different ways:
A) export/import method
B) Insert with a subquery method
C) Partition exchange method
I've used the second method that is:
1) Create the partitioned table:
create table hr.fact_navigation_new
( date_events date not null
)
partition by range (date_events)
(
partition part_2008_01_01 values less than (to_date('2008-01-02', 'yyyy-mm-dd)) tablespace part_2008_01_usr_dwh ,
partition part_future values less than (MAXVALUE) tablespace part_future
) enable row movement;
With user SYS I created first two tablespaces:
create tablespace part_2008_01_usr_dwh
datafile '+dwh_db/dwhdb/datafile/dtf_part_2008_01_usr_dwh_01.ora' size 1024m reuse autoextend on next 51200k maxsize 32767m, '+dwh_db/dwhdb/datafile/dtf_part_2008_01_usr_dwh_02.ora' size 1024m reuse autoextend on next 51200k maxsize 32767m,
'+dwh_db/dwhdb/datafile/dtf_part_2008_01_usr_dwh_03.ora' size 512m reuse autoextend on next 51200k
maxsize 32767m extent management local segment space management auto;
create tablespace part_future
datafile '+dwh_db/dwhdb/datafile/dtf_part_future_usr_dwh_01.ora' size 512m reuse
autoextend on next 51200k maxsize 32767m, '+dwh_db/dwhdb/datafile/dtf_part_future_usr_dwh_02.ora' size 512m reuse autoextend on next 51200k
maxsize 32767m extent management local segment space management
2) Insert into the partitioned table with a subquery from the
non-partitioned table:
insert into hr.fact_navigation_new
select * from hr.fact_navigation;
3) To have the same name as the original table,
then rename the original table and the new table:
alter table hr.fact_navigation rename to hr.fact_navigation_ori;
alter table hr.fact_navigation_new rename to hr.fact_navigation
Now I have a new partitioned by range table with two partitions. What about if I need to add new daily partitions between part_2008_01_01 and part_future ?
I've solved with a PL/SQL procedure and a job executed by the DBMS_SCHEDULER utility.
As SYS user execute the following procedure:
create or replace PROCEDURE CHECK_PARTITIONS AS
l_curr_max_part varchar2(16); /* current max partition */
l_today_part varchar2(16); /* today+interval partition */
l_last_day_part varchar2(16); /* last partition from today + two months */
l_diff_part number;
l_exist_tab number;
l_tab_name varchar2(30); /* tablespace name */
l_date date;
l_stmt varchar2(2048);
l_part_name varchar2(16); /* partition name to be created */
l_range_month number; /* interval between created partitions and to_be_created partitions : 2 months is my default */
BEGIN
l_diff_part := 0;
l_exist_tab := 0;
l_range_month := 2;
/**
* Current max partition
*/
select substr(max(partition_name),6)
into l_curr_max_part
from dba_tab_partitions
where table_name = 'FACT_NAVIGATION'
and TABLE_OWNER = 'HR'
and partition_name <> 'PART_FUTURE';
/**
* Today+interval partition
*/
select to_char(add_months(sysdate, l_range_month), 'YYYY_MM_DD')
into l_today_part
from dual;
if (l_curr_max_part < l_today_part)
then
/**
* If max partition is the least compared with Today+interval partition,
* I need to detect the latest day of the partition to create
*/
select to_char(last_day(add_months(sysdate, l_range_month)), 'YYYY_MM_DD') LD
into l_last_day_part
from dual;
/**
* I need to detect the number of partitions to create starting
* from the current available max partition
* untill the latest
*/
select to_date(l_last_day_part, 'YYYY_MM_DD')-to_date(l_curr_max_part, 'YYYY_MM_DD') RET
into l_diff_part
from dual ;
for ii IN 1..l_diff_part
loop
/**
* Tablespace name to create
*/
select 'PART_' || to_char(to_date(l_curr_max_part, 'YYYY_MM_DD')+ii, 'YYYY_MM') || '_USR_DWH'
into l_tab_name
from dual;
/**
* I need to verify if tablespace name alreadyu exists. Otherwise I create it.
*/
select count(*)
into l_exist_tab
from dba_tablespaces
where tablespace_name = l_tab_name;
if (l_exist_tab = 0)
then
/**
* I need to create the tablespace
*/
execute immediate('CREATE TABLESPACE ' || l_tab_name || ' DATAFILE ''+DWH_DB/dwhdb/datafile/dtf_' || l_tab_name || '_01.ora''' || ' SIZE 1024M REUSE AUTOEXTEND ON NEXT 51200K MAXSIZE 32767M, ' || ' ''+DWH_DB/dwhdb/datafile/dtf_' || l_tab_name || '_02.ora''' || ' SIZE 1024M REUSE AUTOEXTEND ON NEXT 51200K MAXSIZE 32767M, ' || ' ''+DWH_DB/dwhdb/datafile/dtf_' || l_tab_name || '_03.ora''' || ' SIZE 512M REUSE AUTOEXTEND ON NEXT 51200K MAXSIZE 32767M ' || ' EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO');
execute immediate ('ALTER USER USR_SUPER_DWH QUOTA UNLIMITED ON ' || l_tab_name);
end if;
select to_date(l_curr_max_part, 'YYYY_MM_DD')+ii+1
into l_date
from dual;
execute immediate ('ALTER TABLE USR_SUPER_DWH.fact_navigation SPLIT PARTITION PART_FUTURE AT(to_date(''' || to_char(l_date, 'YYYY-MM-DD') || ''', ''YYYY-MM-DD'')) ' || ' INTO (PARTITION PART_' || l_part_name || ' tablespace '|| l_tab_name ||', PARTITION PART_FUTURE)');
end loop;
end if;
END CHECK_PARTITIONS;
The following is the script to create the job using the DBMS_SCHEDULER:
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'SPLIT_PARTITION',
job_type => 'STORED_PROCEDURE',
job_action => 'CHECK_PARTITIONS',
start_date =>to_date('13-02-2008 15:05:00', 'DD-MM-YYYY hh24:mi:ss'),
repeat_interval =>'FREQ=WEEKLY;BYDAY=WED',
enabled => TRUE,
auto_drop => FALSE
);
END;
My job starts every wednesday at 15:05 every week.
If you want to see the status of this job, simply execute:
SELECT * FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = 'SPLIT_PARTITION';
If you want to start it immediately, run the following statement:
BEGIN
DBMS_SCHEDULER.RUN_JOB (
job_name => 'SPLIT_PARTITION',
use_current_session => FALSE);
END;
In the following pictures you can see how I manually run the job and see the status RUNNING
in DBA_SCHEDULER_JOBS table, after scheduled the job at 15:05.
When the job finished, new partitions are created (from PART_2008_03_31 to PART_2008_04_30) and
the status of the job become again SCHEDULED.
No comments:
Post a Comment