Identifying and extracting an old data over three years [message #204857] |
Wed, 22 November 2006 07:01 |
jaydba
Messages: 86 Registered: September 2006 Location: UK
|
Member |
|
|
Hi guys,
I am looking for a recommendation to shrink database of its current size to at least 60% percent by moving data over 3 years old to an archive area on a separate file system using Oracle 8.1.7.
Can you please provide me any ideas including concern scripts and data dictionary views where I can find out old data and transfer to the archive location as described.
Thanks in advance.
|
|
|
|
|
Re: Identifying and extracting an old data over three years [message #204886 is a reply to message #204885] |
Wed, 22 November 2006 08:52 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The timestamp only tells us the last time that object was updated or possibly when it was inserted.
Even this information is no use without knowing the application.
If you tell me that a record was last updated 5 years ago, this tells me nothing about whether it can be archived. For all I know, this record is a piece of vital lookup data that is accesses 3 times a second.
Auditing can provide this information, but you can't back date it.
As I say - you need to know the application. You need to know which tables contain static data that hasn't changed and can't be archived, and which is user generated data that can be archived.
|
|
|
|
Re: Identifying and extracting an old data over three years [message #205790 is a reply to message #204927] |
Mon, 27 November 2006 10:12 |
jaydba
Messages: 86 Registered: September 2006 Location: UK
|
Member |
|
|
Hi guys,
I could identify the coulmn as date for the above mention issue after discussion with deveoper from each concern tables.
We have written one function where c3 column as number type would dispaly as date in the format of dd-mon-yy from the concern tables.
SELECT b.c1, b.c2, inttodate(b.c3), b.*
FROM t141 b
Now, Can you please send me the sql statement or script to filter out archive(old) data over three years using one table?
Can you please suggest me how it can be filterd from each table and extracted at a time which starts with "T*"?
I hope that it would now be helpful to participants to achive this task successfully.
Thanks
|
|
|