OraFAQ Forum: SQL & PL/SQL » How To Delete older data less than 10 years

Home » SQL & PL/SQL » SQL & PL/SQL » How To Delete older data less than 10 years (Oracle11g)

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

How To Delete older data less than 10 years [message #667222]

Fri, 15 December 2017 05:44

ssyr
Messages: 65
Registered: January 2017

Member

Hi All,

Can you suggest me how can I delete data older than10 years from schema.

Thanks in advance.

Report message to a moderator

Re: How To Delete older data less than 10 years [message #667223 is a reply to message #667222]

Fri, 15 December 2017 06:02

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Pseudocode

delete
table_name
where CRITERA_COLUMN<=10yearsago

Report message to a moderator

Re: How To Delete older data less than 10 years [message #667224 is a reply to message #667223]

Fri, 15 December 2017 06:37

ssyr
Messages: 65
Registered: January 2017

Member

Thanks for your reply.

But my concern is if my table don't have any date column how I am going to check this?

and I want this should be done on schema level. So I want dynamic script.

Report message to a moderator

Re: How To Delete older data less than 10 years [message #667225 is a reply to message #667224]

Fri, 15 December 2017 06:45

John Watson
Messages: 8871
Registered: January 2010
Location: Global Village

Senior Member

If there is no date column, then you can't identify the rows. However, neither can anyone else. So no-one will know which rows should be deleted and which ones should remain: you could simply delete a random sample of rows.

Report message to a moderator

Re: How To Delete older data less than 10 years [message #667227 is a reply to message #667224]

Fri, 15 December 2017 07:15

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

ssyr wrote on Fri, 15 December 2017 12:37

But my concern is if my table don't have any date column how I am going to check this?

How is anyone going to measure the success or failure to meet this requirement then?

Schrodinger's rows.

[Updated on: Fri, 15 December 2017 07:15]

Report message to a moderator

Re: How To Delete older data less than 10 years [message #667232 is a reply to message #667222]

Fri, 15 December 2017 08:00

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

This is a terrible question as you gave no info, so I am going to give my answer.

Find the invoice number in the billing table that has an billing date of December 15th 2007. Then delete rows in your invoice table with any invoice number less than that number. QED.

Report message to a moderator

Re: How To Delete older data less than 10 years [message #667234 is a reply to message #667232]

Fri, 15 December 2017 08:05

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>and I want this should be done on schema level.
Some, many, most OLTP applications have "lookup" tables.
I doubt that it would be desirable to remove rows from any such lookup tables that are older than 10 years old.

Report message to a moderator

Re: How To Delete older data less than 10 years [message #667256 is a reply to message #667234]

Mon, 18 December 2017 06:35

Bill B
Messages: 1971
Registered: December 2004

Senior Member

personally when I make new tables in schema I always add a created and/or modified column. This is the only way to have a handle on the actual creation date.

Report message to a moderator

Re: How To Delete older data less than 10 years [message #667257 is a reply to message #667256]

Mon, 18 December 2017 06:38

ssyr
Messages: 65
Registered: January 2017

Member

Thanks for all suggestions.

If how can I analyzed such type of data (ie. older than 10 years) .I have to remove all transactional and PII data from whole database.

Please suggest.

Report message to a moderator

Re: How To Delete older data less than 10 years [message #667258 is a reply to message #667257]

Mon, 18 December 2017 07:08

cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester

Senior Member

We know nothing about your application, your tables or your data.
So we're not in a position in make suggestions. You need someone who understands the data model to determine how to work out what data to delete.
And if no-one knows how to do that then you've presumably got a very flawed DB design and are stuck.

Report message to a moderator

Re: How To Delete older data less than 10 years [message #667259 is a reply to message #667257]

Mon, 18 December 2017 07:58

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

ssyr wrote on Mon, 18 December 2017 07:38

Thanks for all suggestions.
If how can I analyzed such type of data (ie. older than 10 years) .I have to remove all transactional and PII data from whole database.

Do you expect that using different words to ask the SAME thing will get you an answer that you want?

Report message to a moderator

Re: How To Delete older data less than 10 years [message #667260 is a reply to message #667257]

Mon, 18 December 2017 08:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

ssyr wrote on Mon, 18 December 2017 04:38

Thanks for all suggestions.

If how can I analyzed such type of data (ie. older than 10 years) .I have to remove all transactional and PII data from whole database.

Please suggest.

What is PII data?
How is it different from non-PII data?

Report message to a moderator

Re: How To Delete older data less than 10 years [message #667261 is a reply to message #667227]

Mon, 18 December 2017 08:24

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Roachcoach wrote on Fri, 15 December 2017 13:15

How is anyone going to measure the success or failure to meet this requirement then?

The unethical answer is simply to tell them you've done it.

Report message to a moderator

Re: How To Delete older data less than 10 years [message #667284 is a reply to message #667261]

Tue, 19 December 2017 08:11

Bill B
Messages: 1971
Registered: December 2004

Senior Member

do you have any tables that have a date stamp that you can link back to the base tables. Oracle does not keep track of the order of entry or time of entry except when your running flashback tand that is normally only a week or too old.

Report message to a moderator

Re: How To Delete older data less than 10 years [message #667315 is a reply to message #667284]

Wed, 20 December 2017 08:28

msol25
Messages: 396
Registered: June 2011

Senior Member

Hi ssyr,

I Believe, If there is no date column in your database and you are expecting to delete the data then you should have design issues.

Report message to a moderator

Re: How To Delete older data less than 10 years [message #667316 is a reply to message #667315]

Wed, 20 December 2017 09:13

cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester

Senior Member

should have design issues?

Report message to a moderator

Re: How To Delete older data less than 10 years [message #667318 is a reply to message #667316]

Wed, 20 December 2017 10:55

Bill B
Messages: 1971
Registered: December 2004

Senior Member

lol. I think he meant "could have design issues"

Report message to a moderator

Re: How To Delete older data less than 10 years [message #667331 is a reply to message #667318]

Thu, 21 December 2017 02:52

cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester

Senior Member

If the OP really doesn't have the date columns necessary to identify data that's 10 years old then he does have design issues.

Report message to a moderator

Re: How To Delete older data less than 10 years [message #667359 is a reply to message #667331]

Sun, 24 December 2017 03:46

SmithXu
Messages: 5
Registered: December 2017

Junior Member

Yes, the database could have design issues if no date column.
What about analyzing the data? for example, if you know the order id 10000 was created 10 years ago.

Report message to a moderator

Previous Topic:	Find where exception occurred in oracle procedure
Next Topic:	Create a combination DML Trigger

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Sep 26 09:40:17 CDT 2023