Home » SQL & PL/SQL » SQL & PL/SQL » How To Delete older data less than 10 years (Oracle11g)
How To Delete older data less than 10 years [message #667222] Fri, 15 December 2017 05:44 Go to next message
ssyr
Messages: 50
Registered: January 2017
Member
Hi All,

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

Thanks in advance.

Re: How To Delete older data less than 10 years [message #667223 is a reply to message #667222] Fri, 15 December 2017 06:02 Go to previous messageGo to next message
Roachcoach
Messages: 1571
Registered: May 2010
Location: UK
Senior Member
Pseudocode

delete
table_name
where CRITERA_COLUMN<=10yearsago
Re: How To Delete older data less than 10 years [message #667224 is a reply to message #667223] Fri, 15 December 2017 06:37 Go to previous messageGo to next message
ssyr
Messages: 50
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.
Re: How To Delete older data less than 10 years [message #667225 is a reply to message #667224] Fri, 15 December 2017 06:45 Go to previous messageGo to next message
John Watson
Messages: 7613
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.
Re: How To Delete older data less than 10 years [message #667227 is a reply to message #667224] Fri, 15 December 2017 07:15 Go to previous messageGo to next message
Roachcoach
Messages: 1571
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 Go to previous messageGo to next message
joy_division
Messages: 4899
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.
Re: How To Delete older data less than 10 years [message #667234 is a reply to message #667232] Fri, 15 December 2017 08:05 Go to previous messageGo to next message
BlackSwan
Messages: 26183
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.
Re: How To Delete older data less than 10 years [message #667256 is a reply to message #667234] Mon, 18 December 2017 06:35 Go to previous messageGo to next message
Bill B
Messages: 1799
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.
Re: How To Delete older data less than 10 years [message #667257 is a reply to message #667256] Mon, 18 December 2017 06:38 Go to previous messageGo to next message
ssyr
Messages: 50
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.

Re: How To Delete older data less than 10 years [message #667258 is a reply to message #667257] Mon, 18 December 2017 07:08 Go to previous messageGo to next message
cookiemonster
Messages: 13265
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.
Re: How To Delete older data less than 10 years [message #667259 is a reply to message #667257] Mon, 18 December 2017 07:58 Go to previous messageGo to next message
joy_division
Messages: 4899
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?
Re: How To Delete older data less than 10 years [message #667260 is a reply to message #667257] Mon, 18 December 2017 08:18 Go to previous messageGo to next message
BlackSwan
Messages: 26183
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?
Re: How To Delete older data less than 10 years [message #667261 is a reply to message #667227] Mon, 18 December 2017 08:24 Go to previous messageGo to next message
Roachcoach
Messages: 1571
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.
Re: How To Delete older data less than 10 years [message #667284 is a reply to message #667261] Tue, 19 December 2017 08:11 Go to previous messageGo to next message
Bill B
Messages: 1799
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.
Re: How To Delete older data less than 10 years [message #667315 is a reply to message #667284] Wed, 20 December 2017 08:28 Go to previous messageGo to next message
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.
Re: How To Delete older data less than 10 years [message #667316 is a reply to message #667315] Wed, 20 December 2017 09:13 Go to previous messageGo to next message
cookiemonster
Messages: 13265
Registered: September 2008
Location: Rainy Manchester
Senior Member
should have design issues?
Re: How To Delete older data less than 10 years [message #667318 is a reply to message #667316] Wed, 20 December 2017 10:55 Go to previous messageGo to next message
Bill B
Messages: 1799
Registered: December 2004
Senior Member
lol. I think he meant "could have design issues"
Re: How To Delete older data less than 10 years [message #667331 is a reply to message #667318] Thu, 21 December 2017 02:52 Go to previous messageGo to next message
cookiemonster
Messages: 13265
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.
Re: How To Delete older data less than 10 years [message #667359 is a reply to message #667331] Sun, 24 December 2017 03:46 Go to previous message
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.
Previous Topic: Find where exception occurred in oracle procedure
Next Topic: Create a combination DML Trigger
Goto Forum:
  


Current Time: Tue Oct 16 06:49:59 CDT 2018