STEP TO PARTITION A NON PARTITIONED TABLE

STEP TO PARTITION A NON PARTITIONED TABLE

I have a table that is not partitioned and I need to make into a
partitioned table.
To solve this issue its 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

Ive 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 ?

Ive 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.

download file now