Link to home
Create AccountLog in
Avatar of el123
el123Flag for United States of America

asked on

Oracle Partition: Data Archival Strategy

Data Archival Strategy for partioned tables.

We have some partition tables (range), we would like to purge old data out of them, what we are looking for:
- should be able to purge data on date, weekly, monthly or even yearly basis too

Looking for some kind of guidance, advice or assistance, what should be the best route to implement this and also it should be bi-direction, means if they want to bring back some data, should be able too.

Avatar of sventhan
Flag of United States of America image

Do you have these range partitions by monthly or daily?

You could use Sliding window technique to exchange /archive the DATA into the other table.

What do you like to do with the old DATA?

How often would you like to Archive?
Avatar of sventhan
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of el123


they are right now on date basis, but would like to suggest monthly or weekly or even yearly too, as dont see a lot of rows, just for the end part of 2009;

partition_name      num_rows
ORCL_20090102      0
ORCL_20090902      0
ORCL_20090930      31518
ORCL_20091026      31259
ORCL_20091030      31201
ORCL_20091103      30582
ORCL_20091104      30582
ORCL_20091110      30661
ORCL_20091111      30661
ORCL_20091112      30661
ORCL_20091113      49426
ORCL_20091116      49426
ORCL_20091117      49426
ORCL_20091118      49426
ORCL_20091119      49426
ORCL_20091120      51406
ORCL_20091123      51406
ORCL_20091124      51406
ORCL_20091125      51406
ORCL_20091126      51406
ORCL_20091127      51406
ORCL_20091130      51464
ORCL_20091201      51638
ORCL_20091202      51638
ORCL_20091203      51638
ORCL_20091204      51638
ORCL_20091207      51638
ORCL_20091208      51638
ORCL_20091209      51638
ORCL_20091210      51638
ORCL_20091211      51638
ORCL_20091214      51638
ORCL_20091215      51638
ORCL_20091216      51638
ORCL_20091217      51638
ORCL_20091218      51638
ORCL_20091221      51638
ORCL_20091222      51638
ORCL_20091223      51638
ORCL_20091224      51638
ORCL_20091228      51638
ORCL_20091229      51638
ORCL_20091230      51638
ORCL_20091231      51638

Not very clear about the purge data, what they are planning to do with it, but I think, looking to build a warehouse kind of env, but this is not in the scope right now, current deliverable is to purge/archive data.

I'm also thinking on exchanging partition technique, but what and how, because currently it is on date.
Please advice, suggest, guide ... thanks.
Avatar of el123


what they said, want to do it monthly, but this could change to weekly or even daily too, all depends on data arrival.
Hows your partitions being mapped with the tablespaces?

For example if its a monthly partitions its a good idea to keep the partitions in a Quarterly tablespaces. Based on your retention period you could just dropped the tablespaces after taking the backup.

Avatar of el123


no all the partitions are in one tablespace :-(
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Since the partition are date based and you know which ones you want then just export the specific partitions then drop them.
I've a warehouse environment and we do partition exchange all the time and it works the best. When you get time you go thru the document I've provided.

For partition exchange.

1) you need to create a TEMP table exactly the same structure as your partition table (but this is not partitioned). It should have the same indexes and constraints
2) if you're not familiar with the partition exchange concept please play around with it creating temp tables and its very easy
3) You need a metadata table which you can have all the partition table information, schema names, retention period, partition intervals etc., This will be used when you automate the sliding window technique process.
4) You've a write a stored procedure to do the real archive

Here is just a sample that I've posted earlier to drop the partition.

Personally I would not recommend the exp method, because we can handle this using PL/SQL itself and its very efficient/flexible.  
Avatar of el123


excellent ... thanks sventhan & slightwv too.

we are looking into both the options: data pump or pl/sql, will update here soon, about the outcome.

Thanks again for your help and guidance.
Best regards & happy holidatys :-)
Avatar of el123


I want to copy this huge partition table within the same schema, enable to test my procedure, can you please advice, how I can copy this huge table, basically don't want to copy the whole table, if I can be able to just copy one year of data (2009) that should be fine too.

I've created another partition table, having only 2009 partitions, but how to copy data from main table to this testing table ... need your assistance, kind of struggling.

Thanks for your assistance.
Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I would probably not 'test' in the same schema or even the same database.  It's way to easy to slip up and destroy your production system.

You can access partitions directly in queries or the exp/imp utilities but since you have a new table in the same database why not just insert into new_table (select * from old_table where date_stamp between ...);

Selecting by the date range you want should only access the 2009 partition.

Or even select straight from the partition:
Avatar of el123


thanks guys, need your suggestion/advice, here is what I'm doing and all seems to be going good too.

the process or strategy:
- a metadata table: having all the info about all the cadidate tables/partitions for this exchange/archive activity
- creation of a proc, calling this metadata data into a cursor
1. creation of a new regular table, having some naming convention as table+partition name
2. disabling of foreign key constraints on the source partitioned table
3. exchanging of partition with the regular table

till here all worked great, thanks for all your advices and assistance.

Ok now the question, need to create an archive table, which'll store these regular tables (#1)
- what should be the strategy
~> Yearly partitioned + subpartion on months
~> just one partition having the name telling the year+ month

do suggest/advice, what should be the best approach, as right now it is just partition on Name:
MKVH_20090109 .... not consistent, means we don't have all the days/months for 2009
MKVH_20101123 ...  and so on, so forth.

Please advice/guide, thanks.
How are you going to export/archive this data out of the system?
What are the possibilities of retrieving the data if needed at a later date?

For example:  A year from now if you will be asked to just bring back a months worth of data, I might to with single non-partitioned tables.  But it really depends on the process you need to set up.
Avatar of el123


tough question, trying to get these answers, but kind of struggling, still haven't got clear clear understanding, but have to move, so trying to do whatever I can.

right now at the same time, working on a U Turn too, yes one proc to move out and another proc to bring back too.

till this stage all looks good, but now have to create a table with all the archived data, what should be best possible solution, so we can go back easily too, where as from one person, whatever hearing that they'll move it out to another db (planning to use datapump) and that db will be used if in case, some one request this (old) data again, so things are not very clear right now.

But right now the task is what should be new table partition strategy: Year+Month, or just one; name will tell: Year+Month; no subpartition.
If depends on how you'll be using the archived data to decide if you want to partition that data.  Assuming of course you licensed partitioning on that server as well.

I'm still not 100% sure why you are wanting to create the intermediate table locally.  If you are going to use exp/imp to migrate the data from one server to another, just export the specific partition, import it into the archive then drop the partition.
Avatar of el123


basically no access at unix level, have to live with this procedure ... pl/sql for now :-(
>>pl/sql for now

Then how are you going to get the new temp tables out of the database?  

You typically 'archive' data to reduce overall storage and for performance.  Taking data out of a partition and load it into a table in the same database will not decrease space.

As far as performance goes unless you are doing a lot of full table scans I really don't see this helping much here either.
Avatar of el123


what I can see ... for now, they just want to take out old data from the current table ... that's it and about the next step of moving out to another db ... not in the scope ... for now.

Now need to clean the existing/current table and move this old stuff out to another table/s ... that's it.

right now the step is till
- create new regular table
- exchange partition to new regular table
- exchange these regular table data to a new archived partitioned table
>>right now the step is till

I would probably not worry about partitioning the 'temp' tables but I'm not sure how they will be used until they are moved to the new database.
Avatar of el123


how to convert long to char or date or any thing, want to check the partition high value, which is a long & date too, need to extract or can say, leave 6 months of data and purge/archive the rest
What tables/columns are you looking at?  

It's been a while since I've actually used Oracle partitions but I think the dba_tab_partitions view has the range values.
Avatar of el123


yes it is dba_tab_partitions ... high_value column
I'm confused about what you are asking.

What do you see when you execute:
select partition_name,high_value from dba_tab_partitions where table_name='SOME_TABLE_NAME';
Avatar of el123


------------------------------ ---------------------------------------------------------------------
MVCH_20091124                  TO_DATE(' 2009-11-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGOR

this getting, but the problem is I want to extract other than 6 months of partitions, means leave 6 months in the master/main table, purge/move out every thing else ...

how to write a where clause for this high_value column?
Just select from that specific partition using the link I posted way back up in http:#a34471827.

select * from table_name partition(MVCH_20091124);

Avatar of el123


no you are not getting, here is one example, give me a query to look/fetch for only partitions belong to Nov 11, 2009.

------------------------------ ---------------------------------------------------------------------
MVCH_20091124                  TO_DATE(' 2009-11-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGOR
MVCH_20100618                  TO_DATE(' 2010-06-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGOR
MVCH_20100621                  TO_DATE(' 2010-06-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGOR
MVCH_20091125                  TO_DATE(' 2009-11-26 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGOR
MVCH_20091103                  TO_DATE(' 2009-11-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGOR

Elapsed: 00:00:00.21
Avatar of el123


on the basis of high_value not partition_name ;-)
You are correct.  I don't understand the full requirement.

>>fetch for only partitions belong to Nov 11, 2009.

And that will get you what?  

Since that date isn't a break for a partition you can extract all the rows for that date and still have rows in the partition.  So you still will not be able to drop the partition once you've archived that data.
since high_value is LONG, you cannot get it straight.  You have to right a function and use it in query.

I did this few days ago

You may convert return value VARCHAR2 to TO_NUMBER
      v_var              VARCHAR2 (1000);

      v_partn_position   PLS_INTEGER := 0;
      v_partn_position := FN_GET_PARTN_POSITION (p_sch_name, p_tbl_name);

      SELECT   high_value
        INTO   v_var
        FROM   all_tab_partitions
       WHERE       table_owner = p_sch_name
               AND table_name = p_tbl_name
               AND partition_position = v_partn_position;

      --AND ROWNUM < 2;

      RETURN v_var;  -- convert to TO_NUMBER

Open in new window

Avatar of el123


thanks ajexpert for the function, I got it resolved too, using this simple proc:

v1 varchar2(100);
v_cmd varchar2(100);
cursor c1 is
select high_value from user_tab_partitions
where table_name = 'MKT_VOL_CURVE_HISTORY';
for c0 in c1 loop
exit when c1%notfound;
end loop;

worked good and was able insert data to my config table, from where the process is running.

But thanks again for your help.