OraFAQ Forum: Server Administration » Identifying and extracting an old data over three years

Home » RDBMS Server » Server Administration » Identifying and extracting an old data over three years

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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.

Report message to a moderator

Re: Identifying and extracting an old data over three years [message #204875 is a reply to message #204857]

Wed, 22 November 2006 08:02

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

I'm afraid 'Old Data' is something that is entirely application dependent, and I can't think of a way to identify it without knowing your application.

Report message to a moderator

Re: Identifying and extracting an old data over three years [message #204885 is a reply to message #204857]

Wed, 22 November 2006 08:39

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

Can it idetify through date coulmn or timestampe from each tables?

I could find out 471 tables in my database. Can you please provide me the tips or script to find out date coulmn from each table for finding out old or archive data.

Is there any other way to find out the name of tables or objects which has not accessed over 3 years?

Thanks in advance.

Report message to a moderator

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.

Report message to a moderator

Re: Identifying and extracting an old data over three years [message #204927 is a reply to message #204886]

Wed, 22 November 2006 11:31

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

I will follow your suggestion. But, I am also new for application perspective. I have an access to investigate this issue with DBA privilege.

From where I have to start?

What are the concern views or queries to find out static data and other required information for resolving this issue.

I will check that which data cannot be archived with application designer or developer.

Meanwhile, I will appreciate if you can steps to follow technically to identify old data.

Thanks in advance.

Report message to a moderator

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

Report message to a moderator

Previous Topic:	db_block_buffer
Next Topic:	RAC

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 15:40:22 CDT 2024