Skip to Main Content


Question and Answer

Tom Kyte

Thanks for the question, Azman.

Asked: April 08, 2001 - 10:25 pm UTC

Last updated: February 15, 2010 - 3:30 pm UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

Hi Tom,
I need your views on the above.

Currently we in the mist of developing a huge system. The system hinge around 3 huge tables. These tables are projected to store about 1 million records per month with ave size of each records is 500 bytes.

The requiremnts is to move records that is more that 6mths to a historical database whre it would be kept for th next 1.5 years. Thereafter it should be kept offline for 10 years.

Whaa we had done so far.

1. Crate two database - active and historical.

2. Create partition tables (range partition by month)for those 3 huge tables. - So that we can easily drop a partition monthly after six month.

3 Issue to consider

1. ALthough it is simple to drop a partition, we had yet to decide on how tpo move the partition form the active database to the historical database. We are considering import/export utilities , copying thru dblinks and transportable tablespace.

Any recommendations.

2. For archiving of historical data (records that are more than 2 years and to be kept offline for 10 years) what would you think should be the most appropriate format for these data to be stored offline. FYI , we are given 3 days lead time to make the archive data online. We are considering export files while by developers recommends xml format. Do you know what is the industry practicesot deal with huge databases. Any recommendations?


Azman Diron

and Tom said...

1) use transportable tablespaces.

</code> <code>

you can easily detach a tablespace(s) from one database and re-attach them to another. No reload, no rebuilds, just file copies of datafiles.

2) I would just keep the datafiles themselves (detach them and re-attach them later in minutes). Short of that, I would just use plain old flat files -- why explode the size of them with XML when a fixed width or delimited file would more then suffice.


  (16 ratings)

Is this answer out of date? If it is, please let us know via a Comment


Azman Diron, April 09, 2001 - 9:40 pm UTC

Clear and concise

What if I can't use transportable tablespaces

Alessandro Mariotti, July 23, 2004 - 4:55 am UTC

Hi Tom,

I unfortunately can't use this feature because I have a Standard Edition (8.1.7) and can't use export with transportable tablespaces.

Any other idea to archive historical data?

Thanks a lot,


Tom Kyte
July 23, 2004 - 8:38 am UTC

traditional exp with query= clause, delete, imp later. if needed.

Dave, July 23, 2004 - 9:12 am UTC

Tom, could you comment on the compatibility of exported tablespaces with future (or current) versions)? Is an exported tablespace from 9i importable to 10g, and would you expect 10g to be compatible into the future?

Tom Kyte
July 23, 2004 - 9:20 am UTC

that is the way it is supposed to work -- yes.

being a fairly "paranoid" person (reasonable level of paranoia i think :).... I would attach my tablespaces after upgrades on a test system to verify everything is OK - it would be a pain to find out 3 years after you erased 9i from your system that your 9i tablespace has an issue with version 22z. Best to find out sooner rather than later -- while I still have software installed and running that can definitely read those files.

just being cautious -- exp is to be backwards compatible.

What a pity...

Alessandro Mariotti, July 23, 2004 - 11:53 am UTC

... not being able to use EE feautures just because EE licences are too expensive for either our firm and our clients and we, (developers/dbas/designers....) can't use them to make our lives easier....!!!

Ok let's go for the old fashioned exp!!

Thanks a lot Tom


Data archival

Yogesh, November 03, 2004 - 11:36 am UTC

I've to archive old data from 25 tables ...

I've built one temporary table filter (id1,id2,......), this table have filtered data based on some conditions, which I'll be joining with tables for archiving. (I've filtered this data, as base tables used for building this temporary table are too huge. I want to avoid joining those tables).

I can use CTAS for data to be archived. For example

Create table archive_cat as select a.* from cat a, filter f where a.id1=f.id1 and a.id2=f.id2;

I'll exp this archive_cat table. But I have to delete data from these 25 original tables as well.

So could think of 2 options

1. Delete from archive where id1=val and id2=val OR
2. Create table new_cat as select a.* from cat a, filter b where a.id1<>f.id2 and a.id2<>f.id2

But I'm not convinced with both of these approaches.

Can you please suggest some better way of doing it?

Tom Kyte
November 04, 2004 - 1:33 am UTC

Yogesh, November 08, 2004 - 10:50 am UTC

So in short you are suggesting to use CTAS approach. But when I use <> in the where clause, my indexes are not going to be used and select queries will run for hours ...

Any solution for this problem?

Tom Kyte
November 08, 2004 - 5:11 pm UTC

a full table scan is something measured in minutes for most tables -- you have a simple, single table full tablescan. How many 10's of gigabytes are you talking about?

why are you afraid of a join? databases were born to join, they were born to process incredible amounts of data.

Parallel works well if you have hundreds of gigabytes.

I've got a feeling the "read" portion of your problem isn't the problem -- its going to be the "write" portition.

(and you want to use NOT IN i believe -- not <>)

archiving huge table -- Oracle

Baqir Hussain, May 17, 2005 - 2:36 pm UTC

We are running 2-Node RAC with Fiber Channel storage array in OLTP environment. The table “MESSAGE” contains both data types LONG and CLOB and used for storing text messages. This table has 5 years of historic data (text messages) and growing very fast every day. More than 65% of the schema consists of this “MESSAGE” table.

I would like to get your view on the following:

1. We would like to keep 6 months of data in fiber-channel storage array and rest of the data to be on less expensive (via nfs mount) storage (not part of a RAC). And the web application is able to read data from less expensive storage.
2. Is it possible to achieve such a scenario and HOW??

I would appreciate if you please help us in this regard.


Tom Kyte
May 17, 2005 - 3:04 pm UTC

stop using longs so we can partition. Then aging and purging becomes a simple DDL command (no logging, no undo, fast, easy)

figure out how you want to use the NFS stuff, since unless it is a netapp filer or some other certified solution, you won't be touching it with the database....

huge table archiving

Baqir Hussain, May 17, 2005 - 3:58 pm UTC

The table has grown so big that it will take 10-12 hours to convert to clob (it has 7.5 million row of text data and 120G pyhsical size). And the application is reading and writing to this table.
1.I would appreciate if please suggest any methodology to change long to clob with less down time in OLTP.
2. storage will be an Oracle's certified. Is it possible to split same table's data on different storage and HOW?


Tom Kyte
May 17, 2005 - 4:25 pm UTC

starting from NOW use a partitioned table -- this old stuff will be the old stuff (age it out in 6 months, set it read only -- age it later and it won't matter if it take a month)

it is all just disk to us, files, partitions go on files, we don't care what devices the file are on.

huge table archiving

Baqir Hussain, May 17, 2005 - 4:53 pm UTC

Thanks for the prompt response. Just one clarification.

What I understand is that

1. Craete a new partitioned table and make "MESSAGE" is read ONLY and let the developer make changes in the application in such a way to read both the new and old one.

2. I am very new to partitioned table concept. Please let me know where can I get detailed information about it.


Tom Kyte
May 17, 2005 - 6:19 pm UTC

1) if you don't want to reorg it, otherwise another concept would be:

a) create table partitioned by year_month. it has ONLY CLOBS
b) insert into this table select * (using to_lob to convert long to lob) from the existing table where date >= add_months(sysdate,-6) -- migrate only last 6 months
c) do whatever you want with old table, only use the new table

2) admin guide
data warehouseing guide
sql reference

huge tablespace archiving -- oracle 9206

Baqir Hussain, May 17, 2005 - 6:29 pm UTC

I would very much appreciate if you please let me know how can I "reorg" the existing table for CLOB.

Tom Kyte
May 18, 2005 - 8:22 am UTC

you would be rebuilding (create table as select using to_lob) for example

archiving huge table -- Oracle

Baqir Hussain, May 18, 2005 - 11:26 am UTC

Partitioning table is a great concept.

1. Is it possible to rebuild (reorg) table directly from non-partition to partition and HOW can we do it?

2. You suggested to have partition on "year-month". What do mean by this? Which patition scheme (range, list or composite) should we use ? Please show a small example would help a lot. Thanks

Tom Kyte
May 18, 2005 - 12:43 pm UTC

1) create table as select can do that, yes.

2) partition by the year and the month. So all data for jan-2005 goes into partition A, jan-2006 in partition B and feb-2005 into C and so on. Each year/month in a partition by itself.

it is a simple range partition on a date field

PARTITION jan2005 VALUES LESS THAN (to_date('01-feb-2005','dd-mon-yyyy')),
PARTITION feb2005 VALUES LESS THAN (to_date('01-mar-2005','dd-mon-yyyy')),

Master Detail records

Steve, August 28, 2006 - 9:26 pm UTC

So, if we wanted to partition master, detail tables for archiving purposes what approach would you take?

For example, you may have table A which is the master table and contains a date column on which you could easily partition - but it's child table (B), would not have this date column - how would you partition B - assuming that you wanted to keep the archived data plugged in for the time being but may want to drop the data off at some point in the future.

Tom Kyte
August 29, 2006 - 6:54 am UTC

currently, as of 10gr2 and before, you would have to propagate the date field down to the child if you wanted to equi-partition the child with the parent, they both need the same attribute to partition by.

Archiving huge database

Teymur, February 25, 2007 - 2:59 am UTC

Dear Tom.
Just may be my question sound repeatdly, but pls, I need your thought about "Concepts of archiving huge oracle database". As you everytime advice "Oracle concepts".

Just for 9i and 10g.

Thanks in advance.

Tom Kyte
February 26, 2007 - 1:23 pm UTC

why archive...

this question is a tad vague, ambiguous and unanswerable.

how long is a piece of string
how huge is huge
what are your goals with archiving

Reader, July 12, 2009 - 1:06 am UTC

We have begun to archive old data from our production database. Tables are partitioned. We have several referential integrity constraints in our schemas. After copying data to archive database, our plan is to drop old partitions and rebuild indexes. As we had to disable referential integrity constraints before dropping partitions, it needs an outage and can be done only in the weekend maintenance window.

(1) Our management is asking if there is a way to purge old data by scheduling a job that runs every hour purging old records withour waiting for the weekend outage? Is it even possible without disabling referential integrity constraints?

(2) If (1) is not possible, I was just wondering how old data can get purged without the need for an outage, as our application will truly become 24 by 7 soon.

Tom Kyte
July 14, 2009 - 5:31 pm UTC

In 11g, you will have "by reference" partitioning which permits this operation.

ops$tkyte%ORA11GR1> create table p
  2  (
  3    order#      number primary key,
  4    order_date  date,
  5    data       varchar2(30)
  6  )
  7  enable row movement
  8  PARTITION BY RANGE (order_date)
  9  (
 10    PARTITION part_2007 VALUES LESS THAN (to_date('01-jan-2008','dd-mon-yyyy')) ,
 11    PARTITION part_2008 VALUES LESS THAN (to_date('01-jan-2009','dd-mon-yyyy'))
 12  )
 13  /

Table created.

ops$tkyte%ORA11GR1> create table c1
  2  ( order#   number not null,   -- the NOT NULL is necessary, even though part of the
  3    line#    number,            -- key
  4    data     varchar2(30),
  5    constraint c1_pk primary key(order#,line#),
  6    constraint c1_fk_p foreign key(order#) references p
  7  )
  8  enable row movement
  9  partition by reference(c1_fk_p)
 10  /

Table created.

ops$tkyte%ORA11GR1> insert into p (order#, order_date, data)
  2  values ( 1, to_date('31-dec-2007'), 'order data' );

1 row created.

ops$tkyte%ORA11GR1> insert into p (order#, order_date, data)
  2  values ( 2, to_date('01-jan-2008'), 'order data' );

1 row created.

ops$tkyte%ORA11GR1> insert into c1 (order#, line#, data)
  2  values ( 1, 1, 'line data 1' );

1 row created.

ops$tkyte%ORA11GR1> insert into c1 (order#, line#, data)
  2  values ( 1, 2, 'line data 2' );

1 row created.

ops$tkyte%ORA11GR1> insert into c1 (order#, line#, data)
  2  values ( 2, 1, 'line data 1' );

1 row created.

ops$tkyte%ORA11GR1> insert into c1 (order#, line#, data)
  2  values ( 2, 2, 'line data 2' );

1 row created.

ops$tkyte%ORA11GR1> select * from p;

---------- --------- ------------------------------
         1 31-DEC-07 order data
         2 01-JAN-08 order data

ops$tkyte%ORA11GR1> select * from c1;

    ORDER#      LINE# DATA
---------- ---------- ------------------------------
         1          1 line data 1
         1          2 line data 2
         2          1 line data 1
         2          2 line data 2

ops$tkyte%ORA11GR1> alter table p truncate partition part_2007;
alter table p truncate partition part_2007
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

ops$tkyte%ORA11GR1> alter table c1 truncate partition part_2007;

Table truncated.

ops$tkyte%ORA11GR1> alter table p truncate partition part_2007;

Table truncated.

ops$tkyte%ORA11GR1> select * from p;

---------- --------- ------------------------------
         2 01-JAN-08 order data

ops$tkyte%ORA11GR1> select * from c1;

    ORDER#      LINE# DATA
---------- ---------- ------------------------------
         2          1 line data 1
         2          2 line data 2

prior to that, disabling the foreign key is the only option.

Regarding above..

Reader, July 14, 2009 - 9:18 pm UTC

If c1 is not a partitioned table, then the only way is to disable referential integrity constraints before truncate or drop partition. correct? In our environment, FK constraints also are from partitioned table to non-partitioned table as well.

Thank you
Tom Kyte
July 15, 2009 - 11:52 am UTC

correct - we would have no way of knowing that there exists NO CHILD data at all.

Try SOLIX database archviing

Srini, February 10, 2010 - 3:00 am UTC

The solution can help you partition, compress along with enabling incremental archviving based on uder defined policies/criterias. ( )
Tom Kyte
February 15, 2010 - 3:30 pm UTC


you should

a) not advertise on others sites without
b) saying "I'm advertising as an employee of said company"