If the tables are not many could they not break them out into their own tablespaces and setup retention policies using flashback data archive?

Hi Tom,

Thanks for the suggestion. We have to retain the data for 1 year in OLTP system and 7 years of offline storage and there is no archival db or online access to historical data. retrieval of data is on request basis and is a rare operation.

currently we are using oracle 11g r2 for db. Please suggest.

Regards,
Prasad.

If only partitioning wasn't licensed separately on top of Enterprise Edition. I once tried to replace a delicate and long-running archiving process littered with create-table-as-select-and-deletes. I wrote a nice little exchange-partition kind of archiving intending to put it into our end-of-month process before discovering that most of our customers couldn't use it.

Reviewer: David Weigel from Wayne, PA USA
"If only partitioning wasn't licensed separately on top of Enterprise Edition."

You can still use the Oracle 7 style Partitioned Views for very little extra effort. The data would exist in separate tables for say every month and a view exists across all the tables with constraints to guide the optimizer.

Tom has covered their usage well on this site I believe.

I regularly use features such as this, Statspack, manual archive log copying for standbys as it is pretty much a requirement for everything to work on SE/SE1/XE generally.

Hi Connor,
We have a table in our database which has gets updated ~50 million times a day.
The table is not partitioned or analysed (and management will not partition or analyze it for their own performance reasons)
This table has grown huge (size is > 3 billion rows )
It gets purged on daily basis but our purge program cannot keep up with this huge data volume.
We also have a special purge of this table once a quarter, where we create a new table, export data from old table to new table and then recreate indexes on new table.

Is there any way we can have more data purged on daily/weekly basis?
Our target is to make table size to ~ 1 billion so that normal purges can keep up.
Is there a way to make the table size manageable?

create table t as 
  select level c1 from dual
  connect by level <= 100;
  
select count (*) from t;

COUNT(*)   
        100 

alter table t
  move including rows 
  where c1 > 90;
  
select count (*) from t;

COUNT(*)   
         10

Hi Chris,
I am very happy to see a reply.

Apologies, some of the information i gave was not correct - i checked again and this is what i found >>

The huge table in our database is ~4 billion large and it is range partitioned.
It has 16 partitions and it was last analyzed in Oct 2014.
The management will not analyze it again because it might change the run plan in the optimizer and cause the selects to run poorly.

We cannot run daily purges often as it takes up lot of resources - this creates load on our primary DB.
We have a primary DB where this big table resides and it gets updated more than 50million times a day.
The data from primary DB gets replicated to 14 other read only DBs (continuous replication through Golden gate)
The external systems (clients) hit these 14 read only DBs to get the information they want.

During special purge, we create a replica of big table on a database say DB-old (DB-old doesn't receive requests from external systems)
(You suggested that we should use CTAS to create this replica. Yes, this will improve performance )
From DB-old, this big table is exported to our primary DB as well as 14 read only DBs.
But the real problem is the length of time it takes to import the big table to each of the database and build the indices.
This entire process takes ~2.5 days.
Our primary DB has to undergo a downtime of ~2.5 days during special purge activity.
Management won't let us take down updates for the length of time required to perform the special purge we've been running once in a while.
So, even though special purges help us to reduce the number of rows in our big table, we cannot have special purges more often due to huge downtime of our system.


Is there any way to bypass the import?
Is there any way to make the special purge fast and reduce overall system downtime?
We want to delete ~1.8 billion rows.
We delete inactive data from the big table.
I am not sure if we can drop/truncate partitions.

I don't have much knowledge or experience in Oracle DBs, but i am curious to know how things can be worked out in this situation.

Just forgot to mention that we are using Oracle 11g version of database.

Thank you.