Home » SQL & PL/SQL » SQL & PL/SQL » table partitioning (oracle 10g)
table partitioning [message #402012] Thu, 07 May 2009 05:06 Go to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Table creation script.

CREATE TABLE invoices
(invoice_no    NUMBER NOT NULL,
 invoice_date  DATE   NOT NULL,
 comments      VARCHAR2(500))
PARTITION BY RANGE (invoice_date)
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION invoices_q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY')) TABLESPACE users);


In the future data may grows as per date.
Now i want to make further partitions automatically.
How can i do that?

Thanks,
Re: table partitioning [message #402025 is a reply to message #402012] Thu, 07 May 2009 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You already asked this several times.
The answer is the same one: you can't, you have to do it manually (with a script for instance) until you migrate in 11g.

Regards
Michel

[Updated on: Thu, 07 May 2009 06:31]

Report message to a moderator

Re: table partitioning [message #402051 is a reply to message #402012] Thu, 07 May 2009 07:33 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Quote:
You already asked this several times.


When did i ask before?
I am posting this question 1st time.

BTW Thanks for your reply.
Re: table partitioning [message #402052 is a reply to message #402051] Thu, 07 May 2009 07:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ah! Sorry, Embarassed I read the same question several times from the same user and wrongly assumed it was you. My sincere apologies. Confused

Regards
Michel
Re: table partitioning [message #402054 is a reply to message #402012] Thu, 07 May 2009 07:42 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Smile
Re: table partitioning [message #402069 is a reply to message #402054] Thu, 07 May 2009 08:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To apologize for my error, here's the PL/SQL block that adds the new partition from the last one (assuming you add a partition each 3 months):
SQL> CREATE TABLE invoices
  2  (invoice_no    NUMBER NOT NULL,
  3   invoice_date  DATE   NOT NULL,
  4   comments      VARCHAR2(500))
  5  PARTITION BY RANGE (invoice_date)
  6  (PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')),
  7   PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')),
  8   PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')),
  9   PARTITION invoices_q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY')));

Table created.

SQL> col partition_name format a14
SQL> col high_value format a83
SQL> select partition_name, high_value 
  2  from user_tab_partitions 
  3  where table_name = 'INVOICES'
  4  order by partition_position;
PARTITION_NAME HIGH_VALUE
-------------- -----------------------------------------------------------------------------------
INVOICES_Q1    TO_DATE(' 2001-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
INVOICES_Q2    TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
INVOICES_Q3    TO_DATE(' 2001-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
INVOICES_Q4    TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

4 rows selected.

SQL> declare
  2    l_partnb  integer;
  3    l_lastval varchar2(100);
  4    l_newval  date;
  5    l_newpart varchar2(30);
  6    l_sql     varchar2(250);
  7  begin
  8    -- Get the last partition number
  9    select partition_count into l_partnb from user_part_tables where table_name = 'INVOICES';
 10    -- Get the current last value
 11    Select high_value into l_lastval from user_tab_partitions 
 12      where table_name = 'INVOICES' and partition_position = l_partnb;
 13    -- Calculate the new partition name
 14    l_newpart := 'INVOICES_Q' || to_char(l_partnb+1);
 15    -- Calculate the new high value
 16    l_sql := 'begin :o := add_months('||l_lastval||',3); end;';
 17    execute immediate l_sql using out l_newval;
 18    -- Build and execute alter table statement
 19    l_sql :=
 20      'alter table invoices add partition ' || l_newpart || ' values less than' ||
 21      ' (TO_DATE('''||to_char(l_newval,'DD/MM/YYYY')||''',''DD/MM/YYYY''))';
 22    dbms_output.put_line ('Executing: 
 23  '||l_sql);
 24    execute immediate l_sql;
 25  end;
 26  /
Executing:
alter table invoices add partition INVOICES_Q5 values less than (TO_DATE('01/04/2002','DD/MM/YYYY'))

PL/SQL procedure successfully completed.

SQL> select partition_name, high_value 
  2  from user_tab_partitions 
  3  where table_name = 'INVOICES'
  4  order by partition_position;
PARTITION_NAME HIGH_VALUE
-------------- -----------------------------------------------------------------------------------
INVOICES_Q1    TO_DATE(' 2001-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
INVOICES_Q2    TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
INVOICES_Q3    TO_DATE(' 2001-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
INVOICES_Q4    TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
INVOICES_Q5    TO_DATE(' 2002-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

5 rows selected.

Regards
Michel

[Updated on: Thu, 07 May 2009 08:35]

Report message to a moderator

Re: table partitioning [message #402183 is a reply to message #402012] Fri, 08 May 2009 00:30 Go to previous message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
I really appreciate your answer.
Thanks a lot.
Previous Topic: Different CharcterSet are not recongnisable
Next Topic: Data is set has Column name during Union Query
Goto Forum:
  


Current Time: Tue Dec 06 00:27:08 CST 2016

Total time taken to generate the page: 0.11224 seconds