Adding partition dynamically

sunilbains used Ask the Experts™
Hi All,
Can you Please advise on implementing below logic.

I need to create partition dynamically through script like adding partition to a table for that sysdate such as add partition to table A and partition name has convention like P1_yyyymmdd where yyyymmdd will be the date when script is running. After partition is created, then I need to load data to the table A for that day's data. Once data is loaded, I need to extract partition name which is equal to (sysdate - 2weeks) from Table A and load that partition's data(sysdate-2 weeks) to historical table. Once the count of rows for 2 weeks older data is matched from historical table (A_HIST ) with Regular table A, then drop that 2 week older partition in regular table A. This way retention of table A will have only 10 days of data at a time and older data will be archived to historical table(A_HIST).
Eg- Script run on 23rd oct 2009, so partition name will be P1_20091023 and data will be loaded for that day into table A. Now the date older than 2 weeks from this day(which is 9th oct) will be loaded from Table A to historical table(A_HIST).
Say 100 rows belong to 9thoct2009 partition(which is P1_20091009) which we checked from Table A and now the same 100 rows should be loaded to historical table from Table A. Once the number of rows 100 are loaded to historical table (A_HIST),the older partition P1_20091009 should be dropped from regular table A.
This way, we will always keep 10 days of data in Table A and historical data in Table A_HIST.
 Thanks in advance
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Geert GOracle dba
Top Expert 2009

if you only want the last 10 days of data, why not create a view ?

create view last10days as
select * from table where datefield >= sysdate-10

put a index on that datefield

as for the moving of the data to a other hist table
will any select happen on the hist table ?
if so and there is over 10mill records, you could consider partitioning the hist table
and use a nightly job to move the data to the other table

it is of no use and only causes a lot of overhead to keep partitioning like this
besides that you will have to keep creating new datafiles on the server
you will not gain speed with this approach
your approach will give a very high maintenance

You've asked collections of question in a single thread. I would like to handle this one by one.
First., lets just create the partitions.
1) Is that going to e a new Partition table from scratch or, already exists?
2) if Yes, do you want to do ADD PART or SPILT PART from max values?
3) can you have the date, owner and table_name as the parameters to create the partitions?
all you have to do is create a dynamic sql  by writing plsql sp.
alter table tab,ename add partitions partiton_name -- if you want to add
--if you want to spilit
alter table tablename SPLIT PARTITION ptn_name AT (value, value...)
      INTO (ptn_spec, ptn_spec)


Hello Sventhan,
Here are my responses-
1) New partition to create from scratch for this table as at present it is unpartitioned table.
2) Add partition daily based on sysdate when sql is executed such as , if it is execute on 1st Nov,2009, the partition name should be created as A1_20091101 (convention tablename_YYYYMMDD)
3) Yes ,I need to have date , owner and table name as parameter.


please modify the attached script for your needs.

here is the simple way of doing it.

CREATE OR REPLACE PROCEDURE add_partitions (in_owner IN sys.dba_tab_partitions.table_owner%TYPE,
in_table IN all_tab_partitions.table_name%TYPE)
v_cr_part VARCHAR2 (3000);
v_cr_part :=
'alter table '
|| in_owner
|| '.'
|| in_table
|| ' add partition p'
|| ' values less than (to_date('''
|| ''',''YYYYMMDD'')) tablespace ta_dm_'
EXECUTE IMMEDIATE v_split_par_cmd;
DBMS_OUTPUT.put_line ('Partition has been created successfully ');
DBMS_OUTPUT.put_line ('Error in creating partitions');

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial