Skip to content
January 21, 2011 / doganay

CREATE PARTITIONS FOR DESIRED NUM OF DAYS (e.g. 1 YEAR)

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

Hi, you may use this sql to add partitions to your table:

col sqltext format a100
set lines 100
set pagesize 500

accept schema_name prompt SCHEMA_NAME…:
accept table_name prompt TABLE_NAME…:
accept tbs_name prompt TABLESPACE_NAME…:
accept begin_date prompt START_DATE_FOR_PARTITIONS(YYYYMMDD)…:
accept num_of_days prompt HOW_MANY_DAYS(PARTITIONS)_TO_ADD…:

select ‘ALTER TABLE &&schema_name’||’.’||’&&table_name ADD PARTITION P’||to_char(r,’YYYYMMDD’)||’ ‘||’VALUES LESS THAN ‘||
‘(TO_DATE(”’||to_char(r+1,’ YYYY-MM-DD HH24:MI:SS’)||”’, ”SYYYY-MM-DD HH24:MI:SS”, ”NLS_CALENDAR=GREGORIAN”)) TABLESPACE &&tbs_name LOGGING;’
from
(select to_date(‘&&begin_date’,’YYYYMMDD’) + level r from dual connect by level < &&num_of_days);

undefine schema_name
undefine table_name
undefine tbs_name
undefine begin_date
undefine num_of_days

try and pray 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: