A reader, August 14, 2001 - 10:57 am UTC
Tom
I guess you have to substr the partition name to get the date only from your query.
question
bobjee, August 14, 2001 - 11:14 am UTC
can you explain this corelated subquery step wise. Never seen anything like this for this kind of a situation. What if you have multiple partitions for a single day i.e. if a single day has say 2 partitions, and you have to drop both of them. How will your query handle it.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
I.select partition_name
from user_tab_partitions t1
where table_name = 'T'
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
II.and 30 <= ( select count(*)
from user_tab_partitions t2
where table_name = 'T'
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
III. and t1.partition_name < t2.partition_name )
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
can you explain these 3 steps, especially II, and III.
A reader, August 14, 2001 - 2:44 pm UTC
How will you handle the same problem, if each day has 2 partitions attached to it. It is not a part of the question, but what if it is so..
One day offset
Still learning, August 14, 2001 - 2:54 pm UTC
Hi,
I think there is one day offset based on the order of the procedure---- delete the old ones before create a new one vs
delete the old ones after create a new one, that is 30 or 29 issue---- one day offset? Correct me if i'm wrong(the original question is create before delete of course).
Thanks
Dropping oldest partition...
Connor, August 15, 2001 - 7:17 am UTC
One easy way of identifying the "oldest" partition is that it is always has a partition_id of 1 (or is zero?). Either way, the partition id's are renamed to keep this in sync when partitions are split/added/dropped etc
A reader, August 15, 2001 - 10:30 am UTC
How will you handle the same problem, if each day has 2 partitions attached to it. It is not a part of the question, but what if it is so..
A reader, August 15, 2001 - 11:39 am UTC
Tom this is not clear
'
Simply make the partition names:
part_vk_YYYYMMDDHH24MISS
'
Can you show it to us. How will it sort as per the date, it has to be a date column to sort according to the date.
This is a bit confusing
I'm not sure what is "not clear"
Connor, August 15, 2001 - 12:40 pm UTC
Basically what Tom is suggesting is naming scheme where the name contains information that is important to you for partition maintenance. If you want to yield date type information, then you store date-type info in the partition name. By having it year-first/then-month/then-day etc, then you can sort in "date order". If you did not like that naming convention, you could substr out the date part of the name and sort on to_date() of that.
without seeing the structure of table t it is difficult
Berry, August 15, 2001 - 12:43 pm UTC
Instead of giving just the query , can you create the tables with multiple partitions for different days, and show us how you will finding these group of partitions for the oldest day and dropping them.
request
Tommy Waldrop, August 15, 2001 - 12:46 pm UTC
Connor
Woudnt doing a substr be more costly that doing it in just sql.
I repeat what berry requested.
Jerry
Jerry, August 15, 2001 - 12:48 pm UTC
Does the figure 30 include the max value partition.
iam not sure and confident that this will return exactly the partitions needed as per the question asked.
A reader, August 15, 2001 - 12:58 pm UTC
In my opinion only a date column can sort like that, not a varchar2 column
I doubt it, is just like others
Behen, August 15, 2001 - 1:35 pm UTC
Tom
We have always done a substr of the string , and converted it into date, and then carried out the sorting.
We have never done this
Can you prove that this works.
mark, August 15, 2001 - 1:48 pm UTC
this sort method has become very popular since y2k efforts required the need for the yyyy...i would like to see someone prove that it doesn't work...tom...great site!
Perfect
Still learning, August 15, 2001 - 2:27 pm UTC
Great interactive discussion and great site.
However, may i suggest adding sequence number to all the follow up comment please? Because it took me several times to fugure out where "comment 12" is(we have so many comment on this topic).
Tom, great answer!
Thanks
Perfect
Still learning, August 15, 2001 - 2:27 pm UTC
Great interactive discussion and great site.
However, may i suggest adding sequence number to all the follow up comment please? Because it took me several times to fugure out where "comment 12" is(we have so many comment on this topic).
Tom, great answer!
Thanks
AB, August 16, 2001 - 4:06 am UTC
I cannot believe so many "Oracle developers" are actually questioning the sorting of YYYYMMDD and wasting Tom's time. It has always been a standard way of sorting dates as strings for years, and not just in Oracle. On the question of getting rid of the oldest partitions, how about having a parameter of partitions to retain, determining whether there are any to drop and then dropping them as follows in pseudo-code:- (while max(partition_position) > partitions_to_retain drop the oldest partition off the bottom where partition_position = 1).......
Great Answer, Greater site
Wayne Phillips, August 16, 2001 - 3:05 pm UTC
I have been to your site once and I'm hooked for life.
Keep it up Tom, your'e explinations are thorough and methodical. Better than the "brush-offs" you get by most "Guru's" of your stature on the Internet.
Sorry but I have been giving your URL to all my developer friends when I am unable to assist them. I hope I haven't added to your woes but "when your'e good, your'e GOOD"
Thanks from Cape Town, South Africa
dropping and adding partitions periodically
AD, August 16, 2003 - 11:52 am UTC
Table A is truncated and then populated on a monthly basis by a user. The idea is to create a partitioned table B which will be populated on a monthly basis from table A. I want to create a process that will create a partition each month before inserting the data from table A into table B( I am not planning on to do exchange partition since the data on table A is still required to be there until the next month). There is no archiving requirement but the history table (table B) is required to retain data for 36 months.
My question is, should I create a package to achieve this or just few sql scripts to do each step like creating an empty partition, insert (direct load )data into the partitioned table and drop the oldest partition if required etc... Could you please suggest me what should be the best way to tackle this.
Thanks in advance for your time.
August 16, 2003 - 1:56 pm UTC
I would create a stored procedure to do this personally.
same as a script, easier to manage and run.
no. of partitions
jack, August 16, 2003 - 6:16 pm UTC
Tom
Are there are any limitations on number of partitions that can be created on a Table?
Also, pl. explain the disadvantages/advantages of having serveral partitions (eg : > 1000 or so ) on a particular table.
response for no.of partitions
Jack, August 19, 2003 - 2:51 pm UTC
Tom,
When you get a chance, pl.reply to the above question
thanks
Jack
dynamic paritioning
A reader, August 22, 2003 - 4:06 pm UTC
Our requirement is that we dynamically add partition
during a transaction of a job if required. Does this make
sense? My fear is that this compromises transaction integrity (due to impicit ddl commit).
What are your opinion and how would you deal with this?
Thanx!
August 23, 2003 - 9:59 am UTC
use an autonomous transaction and it will not affect your current transaction.
one of the FEW valid uses of an autonomous transaction perhaps if you truly must do this dynamically.
Still applicable in 9.2 and 10?
Jim Nasby, February 09, 2004 - 6:16 pm UTC
Is this still the best method to use for doing a 'rolling window' in 9.2? How about 10?
February 09, 2004 - 8:42 pm UTC
sure, 100%. there is no "rolling window" command as yet (doubt there will be - sort of "implementation dependent")
Simpler query to find partitions to drop
Jim Nasby, February 23, 2004 - 12:19 am UTC
Tom, would the following query work for finding what partitions to drop? It seems much simpler than what's show in your first response, at least to me. :)
SELECT *
FROM (
SELECT partition_name
FROM user_tab_partitions p
WHERE table_name = 'PARTITION'
AND partition_name <> 'THE_REST'
ORDER BY partition_name ASC
)
WHERE rownum > 30
/
It seems this would also run faster than the other version, though I don't have any data to back that up yet.
February 23, 2004 - 7:41 am UTC
run that and you'll find it never ever returns any rows.
Yes, there are probably an infinite number of ways to run any given query. I could do it with rownum (although not the way you do. where rownum > 30 is NEVER true -- in order for rownum to be greater than 30, it would have had to of been > 1, >2 and so on -- but it never was)..... I could do it with analytics, i could do it dozens of ways.
Partition position
Petr Bily, May 17, 2004 - 5:47 am UTC
Tom,
Can we use partition_position to implement "rolling window"? Is it guaranteed that partition_position corresponds to the order of range partitions?
May 17, 2004 - 7:51 am UTC
I'd rather rely on a naming convention personally.
You'd have to goto support to get such a guarantee -- they'd have to ask the developers if this is to be so forever.
It does appear that it re-numbers the partitions after every operation.
Why not use some PL/SQL ?
Matthias Rogel, May 17, 2004 - 12:01 pm UTC
Why not use some PL/SQL in maintaining your range-based
partitions ? (partitioned by current days)
for example
create package temp_part is
function high_value(vIndexPartitionName in varchar2) return date;
end temp_part;
/
create package body temp_part is
function eval_to_date(s in varchar2) return date is
ret date;
begin
execute immediate 'begin :ret := ' || s || '; end;' using out ret;
return ret;
exception when others then return null;
end eval_to_date;
function high_value(vIndexPartitionName in varchar2) return date is
l varchar2(1024);
begin
select high_value
into l
from user_ind_partitions
where partition_name = vIndexPartitionName;
return eval_to_date(replace(l, ', ''NLS_CALENDAR=GREGORIAN'''));
exception when no_data_found then
return null;
end;
end temp_part;
/
Then,
select partition_name,index_name,temp_part.high_value(partition_name) from user_ind_partitions
where index_name='SYS_IOT_TOP_28492' order by 3 asc
gives me a good overview of Partitions and their high_value
Advantage: I don't have to worry bout naming-conventions at all
analytic and rownum examples
Doug, June 03, 2004 - 1:56 pm UTC
Great thread! For those who (myself included) are finding it difficult to wrap our minds around the logic behind Tom's original query:
select partition_name
from user_tab_partitions t1
where table_name = 'PARTITIONED'
and partition_name <> 'THE_REST'
and 4 <= ( select count(*)
from user_tab_partitions t2
where t2.table_name = 'PARTITIONED'
and t2.partition_name <> 'THE_REST'
and t1.partition_name < t2.partition_name )
here are my attempts at a rownum-based:
select rn,
partition_name
from (select rownum rn,
partition_name
from (select partition_name
from user_tab_partitions
where table_name='PARTITIONED'
and partition_name != 'THE_REST'
order by partition_name desc)
)
where rn > 4
and an analytic dense_rank-based version:
select dr,
partition_name
from (select partition_name,
dense_rank() over(order by partition_name desc) dr
from user_tab_partitions
where table_name='PARTITIONED'
and partition_name != 'THE_REST')
where dr > 4
Of dubious benefit is the fact that on our real-world 130-partition table, the dense_rank() version does 913 consistent gets, versus the rownum version's 1156, and the original's 86491. And besides, analytics are really cool. :-)
Partition Corruption
Vivek Sharma, June 10, 2004 - 6:02 am UTC
Dear Tom,
I have a 100 Million Table which is partitioned with respect to Financial year Data. Suppose there are 4 partitions created for this table and all of them are stored in different tablespaces. Assuming that anyone partition got corrupted. Is there anyway to recover that particular partition or I will have to recover the entire table.
Please let me know how do I recover the complete data.
Regards
Vivek
June 10, 2004 - 7:59 am UTC
you can recover
o an individual block or set of blocks
o an individual file
o a set of files
in Oracle.
so if one block in one partition went bad -- you can fix that (without downtime)
so if one file in your database went bad -- and that file held data for that one partition -- you can fix that (that file and hence partition will be unavailable)
you would read the RMAN users guide to learn all about backup and recovery.
Partition by Sysdate
Umesh Kasturi, September 15, 2004 - 2:27 am UTC
Tom,
How can I achieve this ?
SQL> ed
Wrote file afiedt.buf
1 create table test
2 ( ofrdate date)
3 partition by list (ofrdate)
4 (partition p1 values (sysdate)
5* )
6 /
(partition p1 values (sysdate)
*
ERROR at line 4:
ORA-14308: partition bound element must be one
of: string, datetime or interval literal, number,
or NULL
I need to store daily data into one partition ; PLease suggest the best option
September 15, 2004 - 9:19 am UTC
you have to put the dates in there -- you have to have a constant date in there.
I doubt you want to partition by list
you want to range partition:
CREATE TABLE t
(
dt date,
x int,
y int
)
PARTITION BY RANGE (dt)
(
PARTITION p20040912 VALUES LESS THAN (to_date('13-sep-2004','dd-mon-yyyy')),
PARTITION p20040913 VALUES LESS THAN (to_date('14-sep-2004','dd-mon-yyyy')),
....
PARTITION p20040929 VALUES LESS THAN (to_date('30-sep-2004','dd-mon-yyyy')),
PARTITION p20040930 VALUES LESS THAN (to_date('01-oct-2004','dd-mon-yyyy')),
PARTITION junk VALUES LESS THAN (MAXVALUE)
)
;
You have to then automate the adding and dropping (aging) of partitions via a stored procedure or some other method according to your unique and custom needs.
Eg: once a week you'll have a job run that adds 7 more days into the future and perhaps drop the oldest 7 partitions or whatever you need to do.
Days and months in the same table
A reader, October 18, 2004 - 5:02 pm UTC
I want my (range) partitioning scheme to be such that it stores 30 (business)"daily" partitions and 24 "monthly" partitions.
How can I modify your query
select partition_name
from user_tab_partitions t1
where table_name = 'T'
and 30 <= ( select count(*)
from user_tab_partitions t2
where table_name = 'T'
and t1.partition_name < t2.partition_name )
to do this?
i.e. when I add a daily partition and want to delete the partition older than 30 days; when I add a monthly partition I want to delete the corresponding monthly partition older than 24 months?
I guess I can encode the type of partition in the partition name itself like table_dYYYYMMDD and table_mbYYYYMM and parse it out or something?
Thanks
October 19, 2004 - 8:44 am UTC
you would just query based on a template like that -- yes.
Partition names
A reader, October 19, 2004 - 9:25 am UTC
Since partition names are always in the context of a specific table, there is no harm in giving them generic names like part_yyyymmdd, right? i.e. all partitioned tables will have the same partition name for say 1/1/2004 (part_20040101)
October 19, 2004 - 9:31 am UTC
right.
Partition naming
Peter, October 19, 2004 - 11:05 am UTC
If you have lots of partitions to manage you might find easier to merge the table name with the partition_name (eg MYTABLE_yy_ddd or whatever) - it gives the potential of checking that you are about the drop the correct partition of the correct table.
On our production DW we have automated the whole partition management process to keep a rolling cycle of 812 days - each night we drop the oldest table and add a new one for each of 10 + tables. The partition names decode to a calendar date (year + day number) and always increase. The only thing we reuse are the tablespaces!
A reader, October 19, 2004 - 1:52 pm UTC
#b"If you have lots of partitions to manage you might find easier to merge the table name with the partition_name (eg MYTABLE_yy_ddd or whatever) - it gives the potential of checking that you are about the drop the correct partition of
the correct table"
Thats a good point. The only reason I thought of generic partition names is that some of our table names are pretty long and if I encode _yyyymmdd into the partition name, it leaves only 21 characters for the table name!
But in any case, like I said, its not really a problem because you will never do any operation on a partition without having the table name right next to it anyway! so that is your check right there
Dynamic SQL for partition management
A reader, October 19, 2004 - 1:55 pm UTC
I am planning to write a pakage containing dynamic SQL for all the partition management stuff i.e. add partition, drop partition, drop partitions older than N days and so on.
In all this dynamic SQL, I really cant use bind variables, can I? All this would be DDL and do bind variables are out?
I would have to do
execute immediate 'alter table t add partition '||l_partition_name'
instead of
execute immediate 'alter table t add partition :1' using l_partition_name
This would put many of these similar statements in shared pool. Any way to workaround this?
Thanks
October 19, 2004 - 1:57 pm UTC
DDL does not do binds (it is not shareable, nor reusable so it is OK)
Local indexes when loading partitions
A reader, October 19, 2004 - 2:33 pm UTC
Suppose I have
create table t (i int,j int,k int)
partition by range (i)
(partition t1 values less than (100),
partition t2 values less than (200),
partition max values less than (maxvalue));
create index i on t(i,j) local;
Now I want to load the table from some other data
insert into t
select ... from somewhere where i<100;
i.e. I know that I will be loading only one partition at a time. Loading the partition with the local index enabled will slow down the loading.
How can I disable the (local) index partition being loaded to speed up the load and then rebuild it later?
Thanks
October 19, 2004 - 2:50 pm UTC
ops$tkyte@ORA9IR2> alter index i modify partition t1 unusable;
Index altered.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select index_name, partition_name, status from user_ind_partitions where index_name = 'I';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
I T1 UNUSABLE
I T2 USABLE
I MAX USABLE
A reader, October 21, 2004 - 1:31 pm UTC
OK I set that partition unusable, now surprisingly, when I load that partition, I get ORA-1502! This is inspite of doing 'alter session set skip_unusable_indexes=true'.
Any idea why? Thanks
October 21, 2004 - 3:45 pm UTC
unique indexes cannot be skipped. if you plan on doing this lots - you would create unique/primary key constraints as DEFERRABLE (will use NON-unique indexes) and then they can be skipped.
Archive Database Records
PRS, April 23, 2005 - 4:54 pm UTC
Hi Tom,
I am developing the archival strategy for my database. This is an
Online CRM application for call centers with an extensive search based
Application on millions of records with 10 seconds max for any query
Return the output. All transaction tables have more than 10 millions
Rows in each table. We have 2000 concurrent users. Database reside on
HITACHI-9980V SAN with sun v480 four CPU based 1.2GHZ each. Currently
We have optimal performance for the queries. Tables/Indexes are analyzed
Properly every week. But volume is growing so high that we are thinking
Of archiving some of the transaction tables as we have customer front-end
Built on .NET has lots of complex searches such as 360 degree views, joins between
Two transaction tables, etc.Users are OK for the transaction tables to
Have only last two years of data. System has data of last 10 years. So we want
to archive for remaining 8 years. So Please provide your expert advice on
following archive strategy for each transaction table.
1. For each transaction table, I am creating partitioned table.
Example:
Transaction Table Name : RSF_OPPORTUNITY
(Has only current 2 Year Data and non-partitioned)
Partitioned History Table Name: RSF_OPPORTUNITY_HISTORY
(Has all archived data for all 8 years of data. Also every year it will
get the data from transaction table).
2. Write a PL/SQL procedure which is going to use bulk insert to HISTORY table and
Bulk delete from the current non-partitioned table.
3. In order to reset HWM for the current non-partioned transaction table, I am moving
the table to the different tablespace using alter table move command. Then again
I am bringing the table back to the original tablespace using alter table move command.
We use LMT for tablespaces. We are on ORACLE 9.2.0.4-64Bit.
4. Rebuild all indexes for the current non-partitioned transaction table.
I am planning to run this procedure for each transaction table at the end of year.
Any input is greatly appreciated.
Thanks,
PRS
April 23, 2005 - 5:06 pm UTC
why not partition the transactional tables so you don't have to do any DML to age? Just a simple DDL operation?
PRS, April 23, 2005 - 7:09 pm UTC
Problme is oracle cost based optimizer behaves so funny some of the queries goes for a toss. It takes 2 to 3 minutes for the quesry inspite of tables and index analyzed properly as well as parameters like index_cost_adj and other being set in INIT.ORA and we wanted to avoid any hints in the any query of the application.
That is why I had to define it as NON-PARTITIONED table.
Do you agree with the 4 points I have listed down previously. Please provide yor input on this.
Thanks,
PRS
April 23, 2005 - 7:21 pm UTC
i disagree with your above comments, I would (do) partition the table, purging like that at the year end using DML is not a good idea -- no.
If you are using the RBO, that is just not going to work.
To :PRS
Vinayak, April 23, 2005 - 11:26 pm UTC
PRS,
In one of our applications, developers were using DML to purge data older than 90 days and as their application data grew, they started facing performance problems while running their purge script, it was taking too long, was generating too much redo/undo ...
So then we decided to do partitioning on that table and run only DDL to drop the old partition which took only a couple of seconds ...
Rolling window partition (with special requirements)
Jay, April 29, 2005 - 12:46 pm UTC
Consider a table range partitioned (on a date column)
on a monthly basis with every partition belonging to a month. Partitions in this table are named ME, M01, M02, M03, M04, M05 with M05 being the latest month. ME, the first partition collects some data from partitions that roll off every month.
Requirement 1: To retain some data from partition to be rolled off
For example after adding a new partition M06, partition M01
is to be dropped off, but before that some data of M01 needs to be retained. This is implemented as follows:
1. Add partition M06
2. Create table M01_t using 'create table as select' from M01 for the data that needs to be retained
3. Exchange partition M01 with table M01_t
4. Drop table M01_t
5. Merge partitions ME and M01 resulting in ME_new
(which now has the data of M01 that needs to be retained)
Another minor irritant in this process is that the new partition during the merge cannot be named ME (but can be named M01).We would like to retain the name ME. To do this would require us to use the name ME_new during the merge and then rename it to ME in a seperate step. Is there a simpler way to retain the name ME ? Also any reason for this restriction ?
Is there any better approach for implementing Requirement 1 ?
Requirement 2: To roll off some data from the first partition
Every month some data needs to be rolled off from partition ME if the data is older than a certain number of months. The only exception to this is if the data belongs to quarter-end months i.e. if the data belongs to Jan or Feb and older than N months, then it can be rolled off, but data belonging to Mar cannot be rolled off even if older than N months.
What is the best way to implement Requirement 2 and integrate it with the implementation of Requirement 1.
April 29, 2005 - 1:01 pm UTC
why not just
insert /*+ append */ into ME select from m01;
drop partition m01;
?
requirement 2: sounds like ME should itself be a series of partitions?
Rolling window partition (with special requirements)
Jay, April 29, 2005 - 2:54 pm UTC
Requirement 1: 'why not just insert /*+ append */ into ME select from m01; drop partition m01;'
This is not possible since ME and M01 are partitions of the same table and rows selected from M01 would not map to partition bounds of ME.
Requirement 2: 'sounds like ME should itself be a series of partitions?'
This suggestion in effect means that partition ME is not required. When a month needs to be rolled off (but retaining some data), for ex. M01 then
1. Create table M01_t using 'create table as select' from M01 for the data that needs to be retained
2. Exchange partition M01 with table M01_t
3. Drop table M01_t
In this case, all data older than N months will be distributed in a number of monthly partitions with each such partition containing very less data (the retained data for each month is about 8% of the total monthly data).
This has the following downside:
1. Significantly more number of partitions for the table
Not sure whether this is really a downside (except for admin purposes). Maybe performance wise it is a downside, since partition pruning would need to scan through (very crudely put) so many partitions.
2. Each partition older than N months would be very thinly populated - which begs the question as to why we should retain it in seperate partitions in the first place. The only advantage would be to roll off non-quarter end months using just DDL and data dictionary updates.
One problem for thinly populated partitions is that the optimizer might prefer a full partition scan instead of using indexes even though using indexes might be faster (this statement might be up for debate though)
April 29, 2005 - 6:40 pm UTC
I'm confused as to why you want to merge m01 and me.
you want to later age data out of me by date, seems PERFECT reason to keep m01 "as it is", separate then.
1) no downside there, makes the ADMIN EASIER for your eventual purge.
2) if you really wanted to, create table m01_temp as select, truncate m01, merge m01 and me, insert /*+ append */ the data from m01_temp into me, drop m01_temp
Adding partition without split
Sami, April 29, 2005 - 5:03 pm UTC
Dear Tom,
1)
Is there any negative impact if I do add the partition(to teh range partition) in the following way (instead of split the maxvalue partition)? [provided no data in maxvalue partition]
alter table dwo.MY_PART drop PARTITION P2005LAST;
alter table dwo.MY_PART add PARTITION P20050611 VALUES LESS THAN (TO_DATE(' 2005-06-11 00:00:00','SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) NOLOGGING NOCOMPRESS TABLESPACE L_DW02;
alter table dwo.MY_PART add PARTITION P20050621 VALUES LESS THAN (TO_DATE(' 2005-06-21 00:00:00','SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) NOLOGGING NOCOMPRESS TABLESPACE L_DW02;
alter table dwo.MY_PART add PARTITION P2005LAST VALUES LESS THAN (MAXVALUE) NOLOGGING NOCOMPRESS TABLESPACE L_DW02;
alter index dwo.MY_PART_PK rebuild nologging;
Thanks in advance
April 29, 2005 - 6:46 pm UTC
adding an empty partition to the end should be a very fast, low impact operation.
Rolling window partition (with special requirements)
Jay, April 29, 2005 - 7:18 pm UTC
Here is the same requirement stated differently:
Requirement 1: To keep full months data for the past 4 months (apart from the current month's data). Hence the partitions M01, M02, M03, M04 and M05 in the example earlier. Once partition M06 is added (and becomes the current partition), M01 needs to be rolled off but retaining around 8% of M01 data in the table
Requirement 2: The retained data of each month (the 8% described in requirement 1) needs to be kept only if it is within 1 year (from current date). The only exception to this is that the retained data of Mar, Jun, Sep and Dec should not be aged out.
The implementation described earlier collects the retained data for rolled off months in the first partition ME. This satisfies requirement 1. But how can requirement 2 be implemented efficiently - i.e. how can data be aged out of ME efficiently ?
In light of this re-statement of the requirements, can you please suggest any improvements on the implementation for requirement 1 and an implementation for requirement 2 (that integrates with requirement 1 as efficiently as possible).
April 29, 2005 - 7:46 pm UTC
as i said -- I would keep m01 so me doesn't exist and we can roll the data off the end...
that is the only way to age data out of me, don't have it.
PL/SQL package for rolling date range partitions
Joe Szoke, May 24, 2005 - 11:44 am UTC
If anyone is interested, I have a PL/SQL package that will help automate the process of rolling date range partitions off and on. I attempted to get it published in Oracle Magazine, but no such luck. Send me an e-mail at Joseph_C_Szoke@KeyBank.Com, if you are interested. I posted this on Metalink as well.
Add and Drop Partition
Faisal, June 08, 2005 - 9:23 pm UTC
Hi Tom,
In DWH, we are designing the loading process. We have two databases HUB and EDW, everynight we refresh EDW from HUB i.e. insert or update dimensions and insert new facts. We are using Informatica as ETL. Now from recovery point of view, our requirement is as follows;
start load process
load Dimension table A
commit
load Dimension table B
commit
load Dimension table C
commit
load Fact table A
commit
load Fact table B
commit;
end load process.
Now at any point if there is any failure, we need to delete or rollback committed data that we have inserted, or updated i.e. we need to recover our tables at the same point which was before the load process. One of my friend told me that we can create new partition and if there is failure then drop the partition, but I don't know that logic will work for update as well because updated row exist in the old partition. Please let me know what is the best option to achive this?
Faisal
June 08, 2005 - 10:22 pm UTC
you need to remove all of the commits perhaps? why all of the commits if this need be atomic.
Size
Faisal, June 09, 2005 - 12:35 pm UTC
Hi Tom,
You mean one commit, but I am wonder if we hit rollback issue specially in history load which will be much bigger then our daily load.
Regards
June 09, 2005 - 6:19 pm UTC
you can size rollback, or you can do a point in time restore, or you can get 10g with flashback database...
the spectrum of "what" is wide, but is goes from easy to implement to not as easy to implement.
Help Needed
atul, September 22, 2005 - 7:27 am UTC
Hi,
Could you kindly help me for scripting for
creating & dropping partition weekly.
Atleast for a sql statement for weekly partition creation automatically.
Thanks,
Atul
September 22, 2005 - 2:01 pm UTC
this is a pretty simple script..... You can do it, I am sure. It is not very complex.
Help needed
atul, September 25, 2005 - 10:11 am UTC
Hi,
Please could you atleast tell me the sql statment for that.
I really struggled hard for this.
Thanks,
Atul
September 25, 2005 - 10:47 am UTC
you mean beyond alter table t drop partition??? and the corresponding add partition command?
Add Partition Question
Yoav, October 20, 2005 - 5:42 am UTC
Hi Tom
I have a table contain 60,000,000 rows that built like that :
CREATE TABLE WRHS_STOCK_PER_DAY
(
COMPANY_NO NUMBER(4) NOT NULL,
ACTIVE_YEAR DATE NOT NULL,
WRHS_NO NUMBER(4) NOT NULL,
ITEM_SEQ_NO NUMBER(8) NOT NULL,
INFO_TRANS_YN CHAR(1 BYTE) NOT NULL,
SIZE_SUB_GROUP_CD NUMBER,
COLOR_SUB_GROUP_CD NUMBER,
VALUE_DATE DATE,
STOCK_QTY NUMBER
)
PARTITION BY RANGE (ACTIVE_YEAR)
(
PARTITION P_2002 VALUES LESS THAN (TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
PARTITION P_2003 VALUES LESS THAN (TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
PARTITION P_2004 VALUES LESS THAN (TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
PARTITION P_2005 VALUES LESS THAN (MAXVALUE)
)
NOCACHE
NOPARALLEL;
How can i add new partitions to the year 2006, 2007 when the currently last partition is:
PARTITION P_2005 VALUES LESS THAN (MAXVALUE) ?
Thank You.
October 20, 2005 - 8:28 am UTC
ops$tkyte@ORA10GR2> CREATE TABLE t
2 (
3 dt date,
4 x int,
5 y varchar2(25)
6 )
7 PARTITION BY RANGE (dt)
8 (
9 PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
10 PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
11 PARTITION junk VALUES LESS THAN (MAXVALUE)
12 )
13 /
Table created.
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> alter table t
2 split partition junk
3 at (to_date('15-mar-2003','dd-mon-yyyy'))
4 into (partition part3, partition junk );
Table altered.
Getting date using Week,Month & Year
Hitesh Bajaj, December 24, 2005 - 9:44 am UTC
Hi Tom,
Given the Values of Year,Month & Week how can we find the date at which the week began?
Suppose Year is 2005, Month is 12 and Week is 51, how can we construct SQL Statement to find the date at which the week started?
Looking forward for the reply.
December 24, 2005 - 12:26 pm UTC
define "week" first - there are quite a few definitions for it.
Week Definition
Hitesh Bajaj, December 25, 2005 - 12:11 am UTC
Hi Tom,
The week definition is that we have 52 weeks in a year.
Given the Year 2005, the Week 52 we should get the starting date at which the data can be partitioned.
ie.24-DEC-2005 00:00:00
Another example would be the Ist day of the Week 1 for the year 2005 would be 1-JAN-2005.
Can you please suggest a SQL statement for getting the date when YEAR and WEEK parameters are passed.
December 25, 2005 - 8:44 am UTC
so, just to make sure we are saying the same thing
1-7 jan = week 1
8-14 jan = week 2
and so on?
ops$tkyte@ORA10GR2> select to_date( '01-jan-2005' ) + (52-1) * 7 from dual;
TO_DATE('
---------
24-DEC-05
bind in the 2005, the 52 - and there you go. But - now, what do we do with week 53 which consists of 31-dec-2005???
partition position
Rajesh, December 25, 2005 - 7:55 am UTC
In response of the original question in this thread ..will not the getting a partition for which partition_position (a column in user_tab_partitions and many other partition related views) is max will solve the purpose....that is if someone want to add a partition periodically or drop a partition ...why can not we use partition position - i guess it is a sequentially assigned number in the order partitions are
created.
Ajeet
December 25, 2005 - 2:41 pm UTC
it depends entirely on how they partition the data and whether the partition boundaries "sort" or not - in geneneral the answer is "probably yes" - but if they partition using MM for example ( and just keep rotating around )
Rolling Windows Without Partitioning
Su Baba, May 05, 2006 - 1:28 pm UTC
What's the most efficient way to achieve rolling windows implementation without using partitioning? I'd like to know what are the options to do this in Oracle Standard Edition which does not have partitioning option.
Space Constraints on the warehouse Box
J B Khanna, July 18, 2006 - 12:10 pm UTC
Hi Tom,
Need your expert advice.
We are managing a data warehouse where in we have storage limited to 3 TB. We have been running the application for the past 6 months under the concept of partitioning for many huge tables which is RANGE based.
All our tables are date based RANGE partition.
We were running with 88% usage on disk, so we decided to drop the old partitions after keeping the data safely on some other box for the past 5 months and retain the data for only 1 month. This way we used the TTS feature to replicate the other box and dropped the partitions from the main production box.
Now the management wants to push the data every 3 months there on the spare box and have some kind of mechanism to get that data on to tape(s) which can be later used in to restore if someone demands it.
How does the whole process work technically. Now we have added some more datafiles to the already existed tablespaces and have some new tablespace created for future storage.
I am even not sure how this whole thing will work effortlessly.Please help me understand how will the whole solution work.
Thanks
July 19, 2006 - 8:27 am UTC
you would have to script this - you seem to know the technique well enough. What part are you "missing"?
you know how to transport
you know how to move the data
you know how to reattach the data
I hope you know how to work with your tape drives
Partition Boundary Error
hitesh, July 19, 2006 - 1:16 pm UTC
SQL>CREATE TABLE "PR"
2 ( "I_TAG" VARCHAR2(16) NOT NULL ENABLE,
3 "U_ID" VARCHAR2(24) NOT NULL ENABLE,
4 "I_ID" NUMBER(3,0) NOT NULL ENABLE,
5 "I_CD" NUMBER(3,0) NOT NULL ENABLE,
6 "I_E" NUMBER(8,0) NOT NULL ENABLE,
7 "I_UPDATE_TIMESTAMP" TIMESTAMP (0) NOT NULL ENABLE,
8 "I_DATE" DATE
9 )
10 TABLESPACE "TS_DATA" ENABLE ROW MOVEMENT
11 PARTITION BY RANGE ("I_UPDATE_TIMESTAMP")
12 (PARTITION "P_20060425_IUT" VALUES LESS THAN (TO_TIMESTAMP('2006-05-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
13 TABLESPACE "TS_DATA")
14 /
(PARTITION "P_20060425_IUT" VALUES LESS THAN (TO_TIMESTAMP('2006-04-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS WITH TIMEZONE'))
*
ERROR at line 12:
ORA-30078: partition bound must be TIME/TIMESTAMP WITH TIME ZONE literals
I could not understand this error. What is the fix required here.
Thanks
July 22, 2006 - 4:05 pm UTC
well, I get a subtly DIFFERENT sort of thing:
ops$tkyte%ORA10GR2> CREATE TABLE "PR"
2 ( "I_TAG" VARCHAR2(16) NOT NULL ENABLE,
3 "U_ID" VARCHAR2(24) NOT NULL ENABLE,
4 "I_ID" NUMBER(3,0) NOT NULL ENABLE,
5 "I_CD" NUMBER(3,0) NOT NULL ENABLE,
6 "I_E" NUMBER(8,0) NOT NULL ENABLE,
7 "I_UPDATE_TIMESTAMP" TIMESTAMP (0) NOT NULL ENABLE,
8 "I_DATE" DATE
9 )
10 TABLESPACE "USERS" ENABLE ROW MOVEMENT
11 PARTITION BY RANGE ("I_UPDATE_TIMESTAMP")
12 (PARTITION "P_20060425_IUT" VALUES LESS THAN (TO_TIMESTAMP('2006-05-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
13 TABLESPACE "USERS")
14 /
(PARTITION "P_20060425_IUT" VALUES LESS THAN (TO_TIMESTAMP('2006-05-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
*
ERROR at line 12:
ORA-30078: partition bound must be TIME/TIMESTAMP WITH TIME ZONE literals
but basically, you are using to_timestamp, but you have "a date", use to_date
....
9 )
10 TABLESPACE "USERS" ENABLE ROW MOVEMENT
11 PARTITION BY RANGE ("I_UPDATE_TIMESTAMP")
12 (PARTITION "P_20060425_IUT" VALUES LESS THAN (TO_date('2006-05-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
13 TABLESPACE "USERS")
14 /
Table created.
Space Constraints on the warehouse Box
J B, July 20, 2006 - 7:45 am UTC
Hi Tom,
Right I know all the steps mentioned above. But my point is something like this:
We have used the TTS feature to put the 6 month old data in some other box and have taken a tape backup. During this time we have done some reorganization of the datafiles on the main warehouse box in terms of tablespace-datafile relationship.
Now when next month comes, how we will put the 1 month old data in that box again. If we are going to use TTS feature again then we need to remove the datafiles from the box which were earlier copied for 6 months, since the names of many datafiles and tablespace are the same.
That is one problem. Now if we erase them off and let the old 1 month of data transported to that box and some says that I need the 6 month old data, how we will have both of them on the same box.
I know how to restore from the tape but I think the problem is something which is very complex in nature. I think I have provided enough information.
Please help in your valuable advice
July 22, 2006 - 4:42 pm UTC
well, that is an administrative problem you have to sort of come up with a solution for.
the restored datafiles need not be placed into the same path by any means, so file names - no problem.
As for tablespaces - 10g will let you rename them, but before that - you would have to have ONLY one tablespace of that name at a time - something you would have to deal with somehow. If you are not using 10g, you should have definitely taken that into consideration as part of your overall design.
In 10g, you can just rename the existing tablespaces so as to not conflict with a tablespace you are restoring from your "backup"
Some more Help
J B, July 23, 2006 - 12:44 am UTC
Hi Tom,
I have understood the solution to the problem but I have some more questions based on this:
This is the server where the data has been moved to:
Lets suppose there is a table t partitioned based on DATE using Range based. The data for this table is lying under s tablespace TS with data files d1 and d2 located at /fs-01/a and /fs-02/a.
Now lets talk about the Live Server from where the next month of data has to be transferred:
This table t belongs to the same tablespace but data is spread out in 2 more datafiles d3 and d4 located at /fs-03/a and /fs-04/a.
Now tablespace renaming will happen on this server before using the TTS feature so that the metadata export knows by definition that it is a new tablespace which is going to get plugged in the server.But the problem is that this tablespace will have d1,d2,d3,d4 datafiles which will not have data for the previous months which has been already been put there.This will surely overwrite the datafiles d1 and d2.
Is that the tablespace needs to be renamed on the database box where the data needs to be transported rather the box from which it is being transported.
Please suggest.
July 23, 2006 - 9:31 am UTC
put the datafiles in /hey-i-am-not-fs-03/ and /hey-i-am-not-fs-04/
that is all, just put them where ever you want.
Archiving Strategy for Partitioned Tables
Paresh Gandhi, August 22, 2006 - 10:34 am UTC
Hi Tom,
This is a great site for Oracle professionals. Every time I visit your site for a query, I end up reading lot of other materials and they are all useful.
We have set of tables which are partitioned on a date column (range partition). These partitions are defined for each day and we intend to keep data for 15 months. The partitions for each month will reside in its own tablespace. So we will have just over 450 partitions in our system for each partitioned table. The number of tablespaces for each table will be around 15. When the data gets older than 15 months, we intend to drop the partitions and the corresponding tablespaces. We have managed to design and build the scripts for this. Ofcourse we will have daily full database backup scheduled when it goes live.
However, recently, there is a new requirement. They want to be able to restore data for a given month (older than 15 month), run some processes (that will update the restored data). For example, they may want to restore data for month number 28 in the past from now (just that one month data).
1. I wonder if we can achieve this by transportable tablespace? Please note that we use one tablespace for all the 30 partitions for a given month.
2. Please suggest better way of doing it.
I am really looking forward to your detailed reply. If you want any more details, please let me know.
Thanks,
Paresh
August 27, 2006 - 2:53 pm UTC
you can use transportable tablespaces for this.
what you'll do is, instead of dropping the old partitions, you'll EXCHANGE them with "empty dummy tables" (create a table and index it - no data is in it - to make it look "just like" the partitioned table - then exchange that empty table with the full partition. What you'll have is a full table and an empty partition (which you may not drop).
You can then make that tablespace with the tables (that were the partitions) read only and transport it off - and then drop that tablespace/tables. later if you need to reattach them - you can.
Archiving strategy for partitioned tables.
Paresh Gandhi, August 23, 2006 - 11:19 am UTC
Tom,
Could you reply to the queestion above. Please let me know if you think I have not posted the question in the right place?
Thanks,
Paresh
August 27, 2006 - 3:45 pm UTC
I was on....
v
a
c
a
t
i
o
n
:)
Archival/Retrieval of Partitioned Tables
Paresh Gandhi, August 29, 2006 - 10:33 am UTC
Tom,
I never thought of using exchange partitions. This is a very good idea and a very nice strategy. I do have following queries though:
1. As mentioned above, we have 30 daily partitions (for a 30 day month) in a single monthly tablespace. As we archive data for a month, I assume that you suggested to create 30 dummy tables using "a" dummy tablespace and then exchange each partition with the corresponsing dummy tables. Please clarify if I understood correctly.
2. In the future, the structure of the partitioned table(s) may change, say an extra column is added. In such case, would the export of transportable tablespaces (that was taken prior to the addition of the new column) be still valid i.e. can we use it to bring the data back in the system? If they wouldn't work in such cases, please advice alternate solution.
Once agains thanks to you.
Paresh
August 29, 2006 - 3:33 pm UTC
1) correct.
2) you would bring the data back in, alter it - adding the column(s) or whatever and then you could exchange it back in.
Thanks
Paresh Gandhi, September 06, 2006 - 8:32 am UTC
Tom,
Sorry, I didn't reply before as I am working on the scripts to test this strategy. However, I wanted to say a Big Thank to you for your brilliant suggestions. You are just the best.
Paresh
Values Clause
Zaheed, October 12, 2006 - 12:49 pm UTC
CREATE TABLE my_partitions (
.
.
. )
partition by list (create_date) (
partition yesterday values (to_date(trunc(sysdate-1),'DD-MON-YY')),
partition today values (to_date(trunc(sysdate),'DD-MON-YY')))
this is giving me ORA-14308: partition bound element must be one of: string, datetime or interval literal, number, or NULL
They say only literal values are possible in the values clause of a partition.
I want this to work in an automated fasion, so i need to use some variable , sysdate function etc.
There must be some workaround? Thank you for your help.
October 13, 2006 - 6:52 am UTC
of course, look at your partition clause, every day the data is supposed to do what? Magically migrate from place to place???
It does not work like that, think about what would be happening here, it could never work as Oracle would have to be constantly (and instantaneously !!!!) scanning all partitions looking for data to MOVE. We could never rely on the data we need being where we would need it (since it obviously cannot satisfy the "instantaneous" bit)
excellent..
A reader, February 16, 2007 - 3:33 pm UTC
Tom,
I want to take your approac one step higher. I want to find
the following:
1. the least partition
2. If less than xx days old
Can this be accomplished with your query?
thank you very much!
February 18, 2007 - 8:49 am UTC
"the least partition"
the least of what exactly????
if you name your partitions after the date, finding #2 is pretty trivial, just use substr and to_date on the partition name and you have - a date. where sysdate-to__date( f(partition_name) ) > value....
follow to the above comment
A reader, February 17, 2007 - 9:54 pm UTC
I want to find the LATEST partition...not the least...
February 18, 2007 - 9:47 am UTC
umm, please define "least" - i have no idea what the "least" partition is or means.
Thank you .....
A reader, February 19, 2007 - 12:16 am UTC
Tom,
The requirement is find the lastest partition(partition are based on dates) in a table and when the partition is less than 15 days old.
February 19, 2007 - 9:26 am UTC
and, if you had the date in the partition name, you would know how easy this is?
I mean, you have a DATE. It is just a simple "date" query??
am i missing something here?
OK
Kumar, April 03, 2007 - 5:14 am UTC
Hi Tom,
This is the existing partitions in a range partitoned table by country_code.
TABLE_NAME PARTITION_NAME HIGH_VALUE
--------------- -------------------- -------------------
PRODUCT_FACT PRODUCT_FACT_A 'AAAB'
PRODUCT_FACT PRODUCT_FACT_AUS 'AUSB'
PRODUCT_FACT PRODUCT_FACT_B 'BAAB'
PRODUCT_FACT PRODUCT_FACT_D 'DAAB'
PRODUCT_FACT PRODUCT_FACT_DK 'DKAB'
PRODUCT_FACT PRODUCT_FACT_E 'F'
PRODUCT_FACT PRODUCT_FACT_GBE 'GBH'
PRODUCT_FACT PRODUCT_FACT_GBH 'GBHB'
PRODUCT_FACT PRODUCT_FACT_IRLQ 'IRLR'
PRODUCT_FACT PRODUCT_FACT_P 'Q'
PRODUCT_FACT PRODUCT_FACT_SF 'SFAB'
TABLE_NAME PARTITION_NAME HIGH_VALUE
--------------- -------------------- -------------------
PRODUCT_FACT PRODUCT_FACT_ZA 'ZAAB'
I tried to add a partition as product_fact_br for country Brazil using following command which failed.
How to make this add partition statement successful?
SQL> alter table sales.product_fact
2 add partition product_fact_br
3 values less than ('BRAB')
4 /
add partition product_fact_br
*
ERROR at line 2:
ORA-14074: partition bound must collate higher than that of the last partition
April 03, 2007 - 11:37 pm UTC
you need to SPLIT, not add...
ops$tkyte%ORA10GR2> CREATE TABLE t
2 (
3 dt date,
4 x int,
5 y varchar2(30)
6 )
7 PARTITION BY RANGE (y)
8 (
9 PARTITION part1 VALUES LESS THAN ('BAAB'),
10 PARTITION part2 VALUES LESS THAN ('DAAB'),
11 PARTITION junk VALUES LESS THAN (MAXVALUE)
12 )
13 /
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select table_name, partition_name, high_value
2 from user_tab_partitions where table_name = 'T'
3 /
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
HIGH_VALUE
-------------------------------------------------------------------------------
T JUNK
MAXVALUE
T PART1
'BAAB'
T PART2
'DAAB'
ops$tkyte%ORA10GR2> alter table t
2 add partition product_fact_br
3 values less than ('BRAB')
4 /
add partition product_fact_br
*
ERROR at line 2:
ORA-14074: partition bound must collate higher than that of the last partition
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t
2 split partition part2 at ('BRAB' )
3 /
Table altered.
ops$tkyte%ORA10GR2> select table_name, partition_name, high_value
2 from user_tab_partitions where table_name = 'T'
3 /
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
HIGH_VALUE
-------------------------------------------------------------------------------
T JUNK
MAXVALUE
T PART1
'BAAB'
T SYS_P107
'BRAB'
T SYS_P108
'DAAB'
OK
A reader, April 04, 2007 - 8:28 am UTC
Hi Tom,
Thanks for your followup.
But it's not working for me.
SQL> select table_name,partition_name,partition_position
2 from user_tab_partitions where table_name = 'PRODUCT_FACT'
3 order by 3 desc
4 /
TABLE_NAME PARTITION_NAME PARTITION_POSITION
---------------- -------------------- ------------------
PRODUCT_FACT PRODUCT_FACT_ZA 12
PRODUCT_FACT PRODUCT_FACT_SF 11
PRODUCT_FACT PRODUCT_FACT_P 10
PRODUCT_FACT PRODUCT_FACT_IRLQ 9
PRODUCT_FACT PRODUCT_FACT_GBH 8
PRODUCT_FACT PRODUCT_FACT_GBE 7
PRODUCT_FACT PRODUCT_FACT_E 6
PRODUCT_FACT PRODUCT_FACT_DK 5
PRODUCT_FACT PRODUCT_FACT_D 4
PRODUCT_FACT PRODUCT_FACT_B 3
PRODUCT_FACT PRODUCT_FACT_AUS 2
TABLE_NAME PARTITION_NAME PARTITION_POSITION
---------------- -------------------- ------------------
PRODUCT_FACT PRODUCT_FACT_A 1
12 rows selected.
SQL> alter table product_fact split partition product_fact_b at ('BRAB')
2 /
alter table product_fact split partition product_fact_b at ('BRAB')
*
ERROR at line 1:
ORA-14080: partition cannot be split along the specified high bound
<< I even tried at the maximum partition position but both are not working >>
SQL> alter table product_fact split partition product_fact_za at ('BRAB')
2 /
alter table product_fact split partition product_fact_za at ('BRAB')
*
ERROR at line 1:
ORA-14080: partition cannot be split along the specified high bound
How to correct this?
April 04, 2007 - 10:39 am UTC
I gave you a full example, where is yours?
I don't feel like recreating your create table, give us a simple one (no tablespaces, no storage, just an EXAMPLE that demonstrates your issue - we'll fix your DDL or explain why it cannot work)
Oracle 10g - partition_position maintenance enhancements
A reader, July 04, 2007 - 3:05 am UTC
Tom,
We have a housekeeping routine which periodically drops table partitions that are older than a configured period.
The query that was used to find the names of the partitions to be dropped, made use of Oracle internal tables like obj$, tabpart$ etc. instead of using data dictionary view dba_tab_partitions (believe this was done as a part of performance tuning exercise). It used the part# column from tabpart$ internal table. This was working fine till 9i.
We are planning to migrate to 10g soon and have realized (rather accidentally!) that the query will no longer work the way it was supposed to work. This is because, I guess Oracle does not update part# column in tabpart$ table after dropping a partition in 10g, the way it used to do in 9i. The ordering as displayed by the partition_position column in dba_tab_partitions is because of row_number() analytic function being used. I remember reading this somewhere but am not able to find the relevant oracle documentation to confirm this.
It would be really helpful, if you could direct me to the relevant documentation, if this is indeed correct.
Might have to change the query which finds the partition names to be dropped :)
Kind Regards,
A
July 05, 2007 - 11:35 am UTC
use the dictionary views, period. I cannot imagine what problem you ran into prior to this, but never use the sys objects dirctly.
switching partitions from one table to another
Patrick Lelie, July 13, 2007 - 3:39 am UTC
Tom,
I got 2 partitioned tables (t1 and t1_hist) with the same structure.
They are partitioned on monthly base (with namings like t1_YYYYMM, t1_hist_YYYYMM).
I need to move on a monthly base the partitions older than 3 months from t1 to t1_hist.
While reading through this, I think I must do this as follows.
1) insert /*+ append */ into t1_hist select * from t1 partition(t1_YYYYMM);
2) alter table t1 drop partition t1_YYYYMM;
But, I wandered if there is no command/procedure to do this in one command ? Isn't it possible to move a partition from one table to another if the structure is the same ?
A Lazy DBA
July 13, 2007 - 10:31 am UTC
you can turn the full partition from t1 into a table
you can turn that table into a new full partition for t1_hist
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> CREATE TABLE t_hist
2 (
3 dt date,
4 x int,
5 y varchar2(30)
6 )
7 PARTITION BY RANGE (dt)
8 (
9 PARTITION part1 VALUES LESS THAN (to_date('11-mar-2003','dd-mon-yyyy')) ,
10 PARTITION part2 VALUES LESS THAN (to_date('12-mar-2003','dd-mon-yyyy'))
11 )
12 /
Table created.
ops$tkyte%ORA10GR2> CREATE TABLE t
2 (
3 dt date,
4 x int,
5 y varchar2(30)
6 )
7 PARTITION BY RANGE (dt)
8 (
9 PARTITION part3 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
10 PARTITION part4 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy'))
11 )
12 /
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table temp as select * from t where 1=0;
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t exchange partition part3 with table temp;
Table altered.
ops$tkyte%ORA10GR2> alter table t_hist add partition part3 values less than(to_date('13-mar-2003','dd-mon-yyyy'));
Table altered.
ops$tkyte%ORA10GR2> alter table t_hist exchange partition part3 with table temp;
Table altered.
ops$tkyte%ORA10GR2> alter table t drop partition part3;
Table altered.
ops$tkyte%ORA10GR2> select dbms_metadata.get_ddl( 'TABLE', 'T' ) from dual;
DBMS_METADATA.GET_DDL('TABLE','T')
-------------------------------------------------------------------------------
CREATE TABLE "OPS$TKYTE"."T"
( "DT" DATE,
"X" NUMBER(*,0),
"Y" VARCHAR2(30)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
PARTITION BY RANGE ("DT")
(PARTITION "PART4" VALUES LESS THAN (TO_DATE(' 2003-03-14 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS )
ops$tkyte%ORA10GR2> select dbms_metadata.get_ddl( 'TABLE', 'T_HIST' ) from dual;
DBMS_METADATA.GET_DDL('TABLE','T_HIST')
-------------------------------------------------------------------------------
CREATE TABLE "OPS$TKYTE"."T_HIST"
( "DT" DATE,
"X" NUMBER(*,0),
"Y" VARCHAR2(30)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
PARTITION BY RANGE ("DT")
(PARTITION "PART1" VALUES LESS THAN (TO_DATE(' 2003-03-11 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "PART2" VALUES LESS THAN (TO_DATE(' 2003-03-12 00:00:00', 'SYYYY-MM
-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "PART3" VALUES LESS THAN (TO_DATE(' 2003-03-13 00:00:00', 'SYYYY-MM
-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS )
THANKS
Patrick Lelie, July 13, 2007 - 10:49 am UTC
Thanks Tom for the example.
I suppose this will be
a) faster
b) generate no archiving/undo
I'll try it out after my Holidays which start in 30 Min
Dropping partition
Nantha, September 26, 2007 - 12:27 pm UTC
Hi Tom,
If I have partitioned parent table and a child tahle with foreign key (with cascade option), when I drop a partition from parent table, will it remove the child table's records too?
September 26, 2007 - 10:05 pm UTC
no, that only works on "delete"
truncate and drop are not delete.
is it possible to add a partition in the past ?
Alberto Pedretti, October 25, 2007 - 2:13 pm UTC
Tom I have a table partition by range using date.
I have month january and february of 2007.
Is it possible to add a partition in the past
for example December of 2006 whithout creating a new table and moving the existing data into it ?
--- create the table whith partition for january 2007
create table hernan (fecha date , descripcion varchar2(50)) partition by range (fecha)
(partition Enero_2007 values less than (to_date('31-01-2007','DD-MM-YYYY')) )
--- add partition for february 2007
alter table hernan add partition Febrero_2007 values less than (to_date('28-02-2007','DD-MM-YYYY')) ;
---Insert a few records on them just to have records
insert into hernan (fecha , descripcion) values ( to_date('06-01-2007','DD-MM-YYYY') , 'REGISTRO DE ENERO') ;
insert into hernan (fecha , descripcion) values ( to_date('07-01-2007','DD-MM-YYYY') , 'REGISTRO DE ENERO') ;
insert into hernan (fecha , descripcion) values ( to_date('07-02-2007','DD-MM-YYYY') , 'Registro de Febrero') ;
--- looks fine in dba_tab_partitions
PARTITION_NAME PARTITION_POSITION HIGH_VALUE
------------------------------ ------------------ --------------------------------------------------
ENERO_2007 1 TO_DATE(' 2007-01-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
FEBRERO_2007 2 TO_DATE(' 2007-02-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
---now add old partition December 2006
alter table hernan add partition Diciembre_2006 values less than (to_date('31-12-2006','DD-MM-YYYY')) ;
alter table hernan add partition Diciembre_2006 values less than (to_date('31-12-2006','DD-MM-YYYY')
*
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition <= here is the error.
I was expecting to have a new partition in position number 3 with the new date range
I tried this in Oracle Server 10.2.0.3
Thanks a lot.
Alberto Pedretti.
October 25, 2007 - 6:36 pm UTC
SPLIT the partition.
search for split on this page, there are examples.
It works
Alberto, October 26, 2007 - 9:35 am UTC
Thanks Tom it works fine.
Here is what I did just in case somebody else need it
alter table hernan split partition ENERO_2007 at (TO_DATE('31-12-2006','DD-MM-YYYY') )
into (partition DICIEMBRE_2006, partition ENERO_2007 ) ;
SQL> select partition_name , partition_position , high_value from dba_tab_partitions where table_name ='HERNAN' ;
PARTITION_NAME PARTITION_POSITION HIGH_VALUE
-------------------------------------------------
DICIEMBRE_2006 1 TO_DATE(' 2006-12-31 00:00:00',
ENERO_2007 2 TO_DATE(' 2007-01-31 00:00:00',
FEBRERO_2007 3 TO_DATE(' 2007-02-28 00:00:00',
A reader, June 12, 2008 - 3:37 pm UTC
Hi Tom
Is it possible to migrate non-partitioned table into partioned table by using
exchange clause?
but the rows of the nonpartitioned table refer more than one partition.
For example the exchange clause should be like:
alter table partioned_table
exchange partition part1,
exchange partition part2,
exchange partition part3
with table temp;
June 12, 2008 - 6:43 pm UTC
no, an exchange involves NO MOVEMENT OF DATA.
Only if the existing table is going to become a single partition can you do this.
If your existing table must be split into three, as you say, you will use INSERT to move data.
Exchange exists solely to make it so you don't have to move any data - since row 1 goes into partition 1, row 2 into 2, 3 into 3, 4 into 1 and so on - you have to MOVE (rewrite) the data to get the rows away from each other and into their respective partitions.
Partitions with clob columns
Raj, September 30, 2008 - 3:03 pm UTC
I used your example in partitioning a table and was able to get desired results. One problem is my table has a clob and splitting a partition with 440137 rows taking more than 1/2 hour or so. Is this expected? BTW, the partition is on a timestamp column. Tried indexing this column and same results.
Database details:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters... ....
October 01, 2008 - 11:33 am UTC
The number of rows is not really a good indication of "SIZE".
These 440k rows could be 4mb or 4000mb or more. The time to split will be a function of the SIZE of the segment(s) being split. You are basically going to rewrite the entire partition - and if it has clobs and they are big - well - that'll take a while.
Indexing would make a partition split SLOWER - a partition split reads the entire partition and rewrites it in two - and has to maintain the indexes. If you add an index it will not help the retrieval - it will slow down the split due to the additional work you are making it perform.
Less indexes would make this perform faster
The judicious use of the NOLOGGING attribute might be beneficial (just make sure to BACKUP these tablespaces afterwards)
Thanks.
A reader, October 02, 2008 - 10:20 am UTC
Tom,
Thanks for the feedback. Found that the size of the partition is around 5000GB. So, with logging, if its expected to take quite sometime for splitting as splitting locks the table/partition causing any inserts to this table to hang, then are partitions suitable for OLTP apps?
Thanks,
Ravi.
October 02, 2008 - 11:15 am UTC
you have a partition that is 5 terabytes in size?
Yes partitions are entirely suitable for OLTP apps - why are you splitting? It would be very "not common" to split a partition on a recurring basis. It is something you do once - because you made a mistake in the size of your range - and maybe not at all (just fix newly added partitions and leave the old ones be).
Instead of splitting the "last" partition, just add a new one above it.
Its 5000MB
A reader, October 02, 2008 - 2:49 pm UTC
Tom,
Thanks again. Sorry, its a typo. Its 5000MB. But how do we handle if a row comes in later than the current partition's max date as its a date range partition. Maxvalue partition can handle this scenario though.
Thanks,
Ravi.
Highest Partition
martina, October 03, 2008 - 9:47 am UTC
Hi,
Can your timestamp be much higher than Systimestamp? If not you would just have to take care that you added partitions in time.
regards, martina
Thanks ..
A reader, October 04, 2008 - 2:02 pm UTC
Yep, thats correct. Some reasons the automated process fails to add the additional partition I was worried about apps failing but, I guess I have to have more bells and whistles in the process to raise a visible flag,kinda, in case of failure. Thanks Martina...
Ravi..
merge partition question
Sita, October 17, 2008 - 1:36 am UTC
Tom:
question on merge partition.
a) When one should use merge partitions and whats the advantage of doing it ?
b) I have a requirement to merge all 2007 table partitions into one (partition table has local & global partitioned indexes), How to merge them without index invalidation?
Can you please show the steps to develop a script to merge 2007 partitions in all the partitioned tables in a SCHEMA.
Thanks!
October 17, 2008 - 9:22 pm UTC
a) when one wants to take two existing, adjacent partitions and make them become a single partition (I know, obvious). When do you want to use it? When your needs say "we didn't need, want, desire two partitions there, we want to get rid of one of them"
eg: you'll know when you need it, it is typically not likely you will need it - not everyone needs it.
b) read about the update indexes clause
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14231/partiti.htm#sthref2710 ...
merge partition question October 17, 2008 - 1am US/Eastern
Reviewer: Sita from NJ, USA
Tom:
question on merge partition.
a) When one should use merge partitions and whats the advantage of doing it ?
b) I have a requirement to merge all 2007 table partitions into one (partition
table has local & global partitioned indexes), How to merge them without index
invalidation?
...
Can you please show the steps to develop a script to merge 2007 partitions in
all the partitioned tables in a SCHEMA.
....
sure
step a: read documentation - learn the syntax and what the individual commands do
step b: design an approach, given you know your partitioning scheme (and we don't, only you do)
eg: maybe you partitioned into two partitions, we would take one approach.
maybe you partitioned into 365 partitions, we might consider something radically different.
Drop partitions of the Parent table tied to child table
A reader, November 10, 2008 - 4:55 pm UTC
Hi Tom,
We have two tables in question which are referenced thru' the foreign key relationship. When we use the drop partition logic for the Parent Oracle doesn't allow us to drop even if there is no data for the child table.
This part is still okay and we are temporarily disbaling the constraint before DROP happens and after drop modifying the constraint with enable novalidate clause.
We are locking the child table before modifying the constraint also, but still get ORA:xxxxx resource busy error.
Do we have to lock both Parent and child tables to avoid this error. Here is the piece of code which I have written:
l_start := dbms_utility.get_time;
for droppartitions in (Select partition_name
from dba_tab_partitions t1
where table_owner = l_table_owner AND table_name = main.table_name
and main.retention <= ( select count(*) from dba_tab_partitions t2
where table_owner = l_table_owner AND table_name = main.table_name
and t1.partition_name < t2.partition_name))
loop
drop_partition_flag := 'N';
While (drop_partition_flag = 'N')
loop
Min_Max_String := 'Select /*+ Parallel(x 16) */ Nvl(Min(Newspaper_Run_Id),0) , Nvl(Max(Newspaper_Run_Id),0) ' ||
'from ' || main.table_name || ' Partition (' || droppartitions.partition_name || ') x' ;
execute immediate Min_Max_String into p_Min_Run_Id, p_Max_Run_Id;
for tables in (Select Rolling_tables.table_name table_name,dba_tables.partitioned p_partition_type, short_name
from Rolling_tables,dba_tables
where Rolling_tables.table_name = dba_tables.table_name AND Rolling_tables.status = 'Y' and
dba_tables.owner = l_table_owner
order by process_order)
loop
p_table_name := tables.table_name;
Insert into Rolling_tables_daily (JOb,Running_No,Start_Date,End_Date,Time_Diff,No_Of_Rows,Table_Name,P_Type) Values ( v_job,p_running_no,p_start_date,sysdate,(sysdate-p_start_date) * 24 * 3600,0,tables.table_name, p_type);
p_running_no := p_running_no + 1;
If tables.p_partition_type = 'YES' then ---- Checking if the table has acquired a TM lock
p_start_date := sysdate;
constraint_disable := 'N';
for x in
(
Select table_name , constraint_name from dba_constraints where owner = l_table_owner and constraint_type = 'R'
and r_constraint_name =
(select constraint_name from dba_constraints where owner = l_table_owner and
table_name = tables.table_name and
constraint_type = 'P')
) loop
constraint_disable := 'Y';
execute immediate 'Lock table ' || l_table_owner || '.' || x.table_name || ' in exclusive mode';
execute immediate 'alter table ' || l_table_owner || '.' || x.table_name || ' disable constraint ' || x.constraint_name;
end loop;
execute immediate 'Select /*+ parallel(x 4) */ count(*) from ' || l_table_owner || '.' || tables.table_name || ' partition (P_' || Substr(droppartitions.partition_name,3,8) || '_' || tables.short_name || ') x' into p_cnt;
execute immediate 'Lock table ' || l_table_owner || '.' || tables.table_name || ' in exclusive mode';
execute immediate 'Alter table ' || l_table_owner || '.' || tables.table_name || ' drop partition P_' || Substr(droppartitions.partition_name,3,8) || '_' || tables.short_name || ' update indexes parallel 8';
If constraint_disable = 'Y' then
for x in
(
Select table_name , constraint_name from dba_constraints where owner = l_table_owner and constraint_type = 'R'
and r_constraint_name =
(select constraint_name from dba_constraints where owner = l_table_owner and
table_name = tables.table_name and
constraint_type = 'P')
) loop
execute immediate 'lock table ' || l_table_owner || '.' || x.table_name || ' in exclusive mode';
execute immediate 'alter table ' || l_table_owner || '.' || x.table_name || ' modify constraint ' || x.constraint_name || ' enable novalidate';
end loop;
End if;
End if;
end loop;
drop_partition_flag := 'Y';
end loop;
end loop;
end loop;
Need your valuable inputs! Thanks very much!
November 11, 2008 - 3:51 pm UTC
well, locking the tables won't do anything since DDL is really:
begin
commit; <<<=== locks be gone!
begin
do the ddl;
commit;
exception
when others
then rollback;
end;
you can use quiesce database or enable restricted session in order to do this (make everyone pause for a second, do it, resume)
Dropping oldest partition only if COUNT(*) on partition is 0
Peter Tran, January 16, 2009 - 1:03 pm UTC
Hi Tom,
I can get a record count(*) for each specific partition by including the partition name in the query. E.g.
select count(*) from test partition(test_20090101);
Is there a way for me to dynamically determine whether a partition is empty using count(*) and drop that partition?
My requirement is to drop all empty partitions starting at the earliest partition, but stop once I hit the first non-empty partition.
The logic I would like to use is something like the following...note, this doesn't actually work.
DECLARE
RECORD_COUNT NUMBER;
BEGIN
FOR REC IN (SELECT PARTITION_NAME
FROM USER_TAB_PARTITIONS
ORDER BY PARTITION_POSITION)
LOOP
SELECT COUNT(*) INTO RECORD_COUNT FROM TEST PARTITION(REC.PARTITION_NAME)
IF RECORD_COUNT = 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE TEST DROP PARTITION ' || REC.PARTITION_NAME;
ELSE
EXIT;
END IF;
END LOOP;
END;
/
Thanks for your help!
-Peter
January 16, 2009 - 6:23 pm UTC
you'd have to use dynamic sql.
execute immediate
'select 1 from test partition(' || x.pname || ') where rownum = 1'
into l_flag;
just hope that the count cannot change between the time you count and the time you drop :)
Automatic partitioning
A reader, May 25, 2009 - 9:40 am UTC
Hi Tom, Is there any specfic reason for oracle not providing an option to add partitions automatically in a table when ever new data gets inserted instead of throwing an error that the partition deos not exist.
It can defined while creating the table.
This may not be very useful in a few cases, but still oracle can give an option.
May 26, 2009 - 9:49 am UTC
you mean like this?
ops$tkyte%ORA11GR1> create table audit_trail
2 ( ts timestamp,
3 data varchar2(30)
4 )
5 partition by range(ts)<b>
6 interval (numtodsinterval(1,'day'))
7 store in (users, example )</b>
8 (partition p0 values less than
9 (to_date('23-feb-2007','dd-mon-yyyy'))
10 )
11 /
Table created.
ops$tkyte%ORA11GR1> select partition_name, tablespace_name, high_value
2 from user_tab_partitions
3 where table_name = 'AUDIT_TRAIL'
4 order by partition_position;
PARTITION_ TABLESPACE HIGH_VALUE
---------- ---------- -----------------------------------
P0 USERS TIMESTAMP' 2007-02-23 00:00:00'
ops$tkyte%ORA11GR1> insert into audit_trail (ts,data) values ( to_date('02-jun-2007'), 'xx' );
1 row created.
ops$tkyte%ORA11GR1> select partition_name, tablespace_name, high_value
2 from user_tab_partitions
3 where table_name = 'AUDIT_TRAIL'
4 order by partition_position;
PARTITION_ TABLESPACE HIGH_VALUE
---------- ---------- -----------------------------------
P0 USERS TIMESTAMP' 2007-02-23 00:00:00'
SYS_P221 USERS TIMESTAMP' 2007-06-03 00:00:00'
ops$tkyte%ORA11GR1> insert into audit_trail (ts,data) values ( to_date('16-sep-2007'), 'xx' );
1 row created.
ops$tkyte%ORA11GR1> select partition_name, tablespace_name, high_value
2 from user_tab_partitions
3 where table_name = 'AUDIT_TRAIL'
4 order by partition_position;
PARTITION_ TABLESPACE HIGH_VALUE
---------- ---------- -----------------------------------
P0 USERS TIMESTAMP' 2007-02-23 00:00:00'
SYS_P221 USERS TIMESTAMP' 2007-06-03 00:00:00'
SYS_P222 USERS TIMESTAMP' 2007-09-17 00:00:00'
ops$tkyte%ORA11GR1> insert into audit_trail (ts,data) values ( to_date('11-sep-2007'), 'xx' );
1 row created.
ops$tkyte%ORA11GR1> select partition_name, tablespace_name, high_value
2 from user_tab_partitions
3 where table_name = 'AUDIT_TRAIL'
4 order by partition_position;
PARTITION_ TABLESPACE HIGH_VALUE
---------- ---------- -----------------------------------
P0 USERS TIMESTAMP' 2007-02-23 00:00:00'
SYS_P221 USERS TIMESTAMP' 2007-06-03 00:00:00'
SYS_P223 EXAMPLE TIMESTAMP' 2007-09-12 00:00:00'
SYS_P222 USERS TIMESTAMP' 2007-09-17 00:00:00'
http://docs.oracle.com/docs/cd/B28359_01/server.111/b28279/chapter1.htm#FEATURENO07076
Automatic partitioning
A reader, May 25, 2009 - 9:50 am UTC
Just learned that oracle has provided it now... interval partitioning...sry...
Sometimes wit is underated
Rae Marvin, May 27, 2009 - 8:21 am UTC
Now that made me laugh..
Oh, and I did learn something new.
Cheers
Automatic partitioning in ORACLE 10.2
DaVich, June 04, 2009 - 5:14 am UTC
Is Automatic partitioning available in ORA 10.2?
I got ORA-00922.
If not:
We need new partition for each new value in column COL_X and we prefer naming convention for partition like TABLE_NAME||'_'||COL_X.
I was thinking about INSERT TRIGGER to create new partition, however I am afraid about performance.
It is possible to write insert trigger on specific partition? (E.g. on partition THE_REST, which is intended to by empty.)
Do you have some other suggestion?
Thank you
DaVich
-----------------------------------------------------------------------------------------------
Note: .........PARTITION THE_REST VALUES LESS THAN ( maxvalue )
June 08, 2009 - 11:22 am UTC
No, it was brand new in 11g.
The insert trigger would not work - the partition doesn't exist, the error would be raised already.
You could use an instead of trigger on a view, you would insert into the view, the view would attempt the insert into the real table (which does not have a "the_rest", it would be list partitioned by the sounds of it) and upon failure it would run an autonomous transaction to create the partition.
ops$tkyte%ORA10GR2> create table t
2 ( col_x varchar2(5),
3 x int,
4 y varchar2(30),
5 z date
6 )
7 partition by list(col_x)
8 ( partition T_X values ( 'X' )
9 )
10 /
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace view v
2 as
3 select col_x, x, y, z
4 from t
5 /
View created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure create_new_partition( p_col_x in varchar2 )
2 as
3 pragma autonomous_transaction;
4 begin
5 execute immediate '
6 alter table t add partition ' ||
7 dbms_assert.simple_sql_name( 't_' || p_col_x ) || '
8 values ( ' || dbms_assert.enquote_literal( p_col_x ) || ' )';
9 end;
10 /
Procedure created.
ops$tkyte%ORA10GR2> create or replace trigger instead_of_trigger
2 instead of insert on v
3 declare
4 no_partition exception;
5 pragma exception_init( no_partition, -14400 );
6 begin
7 insert into t(col_x,x,y,z)
8 values (:new.col_x,:new.x,:new.y,:new.z);
9 exception
10 when no_partition
11 then
12 create_new_partition( :new.col_x );
13 insert into t(col_x,x,y,z)
14 values (:new.col_x,:new.x,:new.y,:new.z);
15 end;
16 /
Trigger created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select partition_name, high_value from user_tab_partitions where table_name = 'T';
PARTITION_NAME HIGH_VALUE
------------------------------ ----------
T_X 'X'
ops$tkyte%ORA10GR2> insert into v (col_x,x,y,z) values ( 'X', 1, 'hello', sysdate );
1 row created.
ops$tkyte%ORA10GR2> insert into v (col_x,x,y,z) values ( 'Y', 1, 'hello', sysdate );
1 row created.
ops$tkyte%ORA10GR2> select partition_name, high_value from user_tab_partitions where table_name = 'T';
PARTITION_NAME HIGH_VALUE
------------------------------ ----------
T_X 'X'
T_Y 'Y'
you will have to evaluate if the obfuscation caused by going through a view to the table via an instead of trigger is
a) worth it (probably not, just my opinion..)
b) performant enough for you.
Thanks
DaVich, June 11, 2009 - 8:15 am UTC
Thank for prompt answer.
It is good idea to use instead trigger.
I am testing the performance now and I am sure that I will use this idea for some table (smaller).
Thanks
DaVich
Oldest Partition
Nick, October 26, 2009 - 1:19 pm UTC
Hi Tom,
I have been reading the thread and have tried the suggestions, but I am not getting the oldest partition to display. Basically I have a similar problem: My table is partitioned by range (month) storing data for 5 years, and I need to drop the oldest partition every month so that there is space for the new month's records. Ideally, I would like to write a stored procedure, but I can't quite find out how to get the oldest partition first... could you help please?
Thanks in advance.
October 26, 2009 - 3:26 pm UTC
is your partition name sortable?
look at the answer - the approach, modify it to meet YOUR data
you don't tell us how to find the oldest partition, what is the "logic" that turns your partition name into something we can sort on?
Oldest Partition
A reader, October 27, 2009 - 5:08 am UTC
I have named my partitions Transaction_JUL2008, Transaction_AUG2008, etc. according to the month and the year.
October 27, 2009 - 11:37 am UTC
and so you see they are not sortable right? J sorts above A, so sorting on them as a string won't work.
so, what magic function could you apply to them to make them sort??
eg:
select partition_name
from user_tab_partitions t1
where table_name = 'T'
and 30 <= ( select count(*)
from user_tab_partitions t2
where table_name = 'T'
and t1.partition_name < t2.partition_name )
In order to have that last line make sense, what function might you apply to strings like:
Transaction_JUL2008, Transaction_AUG2008,
so as to make they sortable?
(I know I know the answer - but come on - so do you - this is pretty darn basic. If you UNDERSTAND the technique, you would - no, rather you SHOULD, you BETTER BE able to do this)
System generated Partition names
Devinder, January 21, 2010 - 2:31 pm UTC
The issue that I am facing is related to system generated partition names in 11g. I have a partition table which is partitioned every hour and we need to keep only two days of data in the table. In 10g I was having a partition name with the date and hour attached to partition name and it was easy to generate a dynamic script to drop partitions older than two days. However I am testing interval partitioning in 11g and I am at a loss on how to come up with the partition name for the partition to be dropped. I guess one option would be to work using the partition position.
Can you suggest some alternatives if possible
January 25, 2010 - 1:39 am UTC
based on the high low range itself???? or the partition id - lots of ways to see what is the oldest partition and what data is in it.
System generated Partition names
Hirdey, September 26, 2010 - 2:44 pm UTC
I am having a similar issue. I have a job which runs multiple times in a month. The Table which is populated is paritioned by date and I am using Interval paritioning 11g new feature. The first step in the job is to truncate the existing parition for the month before loading. Now since the parition name is generated by system, I am not able to query the parition dynamically so i could truncate the parition.
I was reading an article which said i could query the table using for clause as shown below
select * from
table_data partition for (to_date('15-AUG-2007','dd-mon-yyyy'))
But i get error
"ORA-14763: Unable to resolve FOR VALUES clause to a partition number"
Please guide.
Thanks
September 27, 2010 - 11:55 am UTC
give me a for example cut and paste, just like this:
ops$tkyte%ORA11GR2> create table audit_trail
2 ( ts date,
3 data varchar2(30)
4 )
5 partition by range(ts)
6 interval (numtodsinterval(1,'day'))
7 store in (users, example )
8 (partition p0 values less than
9 (to_date('23-feb-2007','dd-mon-yyyy'))
10 )
11 /
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select * from audit_trail partition for (to_date('15-jan-2009','dd-mon-yyyy'));
no rows selected
ops$tkyte%ORA11GR2> insert into audit_trail values ( to_date( '15-jan-2009 12:01:02', 'dd-mon-yyyy hh24:mi:ss' ), 'hello world...' );
1 row created.
ops$tkyte%ORA11GR2> select * from audit_trail partition for (to_date('15-jan-2009','dd-mon-yyyy'));
TS DATA
--------- ------------------------------
15-JAN-09 hello world...
System generated parition names
Hirdey, October 04, 2010 - 3:02 pm UTC
Here's the script.
create table
part_data (
start_date DATE,
store_id NUMBER,
inventory_id NUMBER(6),
qty_sold NUMBER(3)
)
PARTITION BY RANGE (start_date)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION part_data_p2 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')),
PARTITION part_data_p3 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY'))
)
==>
SQL statement executed
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
insert into part_data (start_date, store_id, inventory_id, qty_sold)
values ( '15-AUG-07', 1, 1, 1)
==>
1 row(s) inserted
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT
TABLE_NAME,
PARTITION_NAME,
PARTITION_POSITION,
HIGH_VALUE
FROM
USER_TAB_PARTITIONS
WHERE
TABLE_NAME='PART_DATA'
ORDER BY
PARTITION_NAME
==>
TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE
------------------------------ ------------------------------ -------------------------------------- ----------
PART_DATA PART_DATA_P2 1 (LONG)
PART_DATA PART_DATA_P3 2 (LONG)
PART_DATA SYS_P23 3 (LONG)
-------- End of Data --------
3 row(s) fetched
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select * from part_data partition for (to_date('15-AUG-2007','dd-mon-yyyy'))
==>
ORA-14763: Unable to resolve FOR VALUES clause to a partition number
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
**** SCRIPT ENDED 10/4/2010 3:59:58 PM ****
October 05, 2010 - 11:56 am UTC
In 11gr1:
ops$tkyte%ORA11G> select * from v$version;
BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
ops$tkyte%ORA11G> create table
2 part_data (
3 start_date DATE,
4 store_id NUMBER,
5 inventory_id NUMBER(6),
6 qty_sold NUMBER(3)
7 )
8 PARTITION BY RANGE (start_date)
9 INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
10 (
11 PARTITION part_data_p2 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')),
12 PARTITION part_data_p3 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY'))
13 )
14 /
Table created.
ops$tkyte%ORA11G> insert into part_data (start_date, store_id, inventory_id, qty_sold) values ( '15-AUG-07', 1, 1, 1);
1 row created.
ops$tkyte%ORA11G>
ops$tkyte%ORA11G> select * from part_data partition for (to_date('15-AUG-2007','dd-mon-yyyy'));
START_DAT STORE_ID INVENTORY_ID QTY_SOLD
--------- ---------- ------------ ----------
15-AUG-07 1 1 1
I cannot reproduce
In 11gr2:
ops$tkyte%ORA11GR2> select * from v$version;
BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
ops$tkyte%ORA11GR2> create table
2 part_data (
3 start_date DATE,
4 store_id NUMBER,
5 inventory_id NUMBER(6),
6 qty_sold NUMBER(3)
7 )
8 PARTITION BY RANGE (start_date)
9 INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
10 (
11 PARTITION part_data_p2 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')),
12 PARTITION part_data_p3 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY'))
13 )
14 /
Table created.
ops$tkyte%ORA11GR2> insert into part_data (start_date, store_id, inventory_id, qty_sold) values ( '15-AUG-07', 1, 1, 1);
1 row created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select * from part_data partition for (to_date('15-AUG-2007','dd-mon-yyyy'));
START_DAT STORE_ID INVENTORY_ID QTY_SOLD
--------- ---------- ------------ ----------
15-AUG-07 1 1 1
I cannot reproduce.
I do not recognize the output format you posted, are you using sqlplus? It looks like it might be your tool...
try sqlplus - and if that works - time to look at your "tool" - it isn't submitting the sql you think it is.
System generated parition names
Hirdey, October 05, 2010 - 3:09 pm UTC
I got it.
There is an oracle bug(Bug 7419433) related to Version 11.1.0.7.0.
Patch is available and alternative solution is to use curosr_sharing = exact and then it works.
Thanks for you help.
October 06, 2010 - 4:42 am UTC
you didn't mention cursor_sharing.
The real bug is that you are using cursor_sharing=force or similar. I hate that parameter. Really sometimes wish it did not exist, it gives developers the feeling that they can write really bad code and it is "ok"
it isn't.
System generated parition names
Hirdey, October 06, 2010 - 9:35 pm UTC
I am not going to use cursor_sharing=exact in the program.
DBA is going to apply the patch which will fix the bug.
But this brought interesting point which i need to check with DBA is why we have cursor_sharing not equal to EXACT. From your opinion looks like cursor_sharing parameter should be EXACT right?
October 07, 2010 - 2:10 am UTC
yes, exact is the only setting i would permit in an init.ora parameter file.