Home » SQL & PL/SQL » SQL & PL/SQL » Delete statement to retain last 90 days of data (Oracle SQL IDE 4.0.3.16 Win2k8 Server)
Delete statement to retain last 90 days of data [message #679075] Fri, 31 January 2020 09:11 Go to next message
Jay76
Messages: 5
Registered: January 2020
Junior Member
Hi

Thanks in advance for support with this. A service I take care of uses Oracle SQL databases to store data and I need to place a clean up job on one of the tables to keep the size down.

I'll provide what I think you need, if theres anything else please let me know and I'll do my best to provide it.

What I need doing is to keep a table called RADACCT down to only the last 90 days of data. There are two columns that store timestamps, one is for the start of the accounting session (acctstarttime) and the other is for the end of the accounting session (acctstoptime). Looking through the acctstoptime data I have found there can be a null entry in the column for acctstoptime. Maybe a network blip or such causing that. So I thought it might be wise to create a procedure that removes data using both acctstoptime and acctstoptime so the table isnt left with data where the accounting started after 90 days from system time but had no stop time value. You might have a different and better way....just my ideas.

Examples of data kept in the two columns are -

acctstarttime acctstoptime
2018-02-20 19:11:29 2018-02-20 19:21:41

The table (RADACCT) columns are like this -

COLUMN_NAME DATA_TYPE NULLABLE COLUMN_ID

RADACCTID NUMBER(38,0) No 1
ACCTSESSIONID VARCHAR2(64 BYTE) No 2
ACCTUNIQUEID VARCHAR2(32 BYTE) Yes 3
USERNAME VARCHAR2(64 BYTE) No 4
GROUPNAME VARCHAR2(32 BYTE) Yes 5
REALM VARCHAR2(30 BYTE) Yes 6
NASIPADDRESS VARCHAR2(15 BYTE) No 7
NASPORTID VARCHAR2(15 BYTE) Yes 8
NASPORTTYPE VARCHAR2(32 BYTE) Yes 9
ACCTSESSIONTIME NUMBER(19,0) Yes 10
ACCTAUTHENTIC VARCHAR2(32 BYTE) Yes 11
CONNECTINFO_START VARCHAR2(50 BYTE) Yes 12
CONNECTINFO_STOP VARCHAR2(50 BYTE) Yes 13
ACCTINPUTOCTETS NUMBER(19,0) Yes 14
ACCTOUTPUTOCTETS NUMBER(19,0) Yes 15
CALLEDSTATIONID VARCHAR2(50 BYTE) Yes 16
CALLINGSTATIONID VARCHAR2(50 BYTE) Yes 17
ACCTTERMINATECAUSE VARCHAR2(32 BYTE) Yes 18
SERVICETYPE VARCHAR2(32 BYTE) Yes 19
FRAMEDPROTOCOL VARCHAR2(32 BYTE) Yes 20
FRAMEDIPADDRESS VARCHAR2(15 BYTE) Yes 21
ACCTSTARTDELAY NUMBER(12,0) Yes 22
ACCTSTOPDELAY NUMBER(12,0) Yes 23
XASCENDSESSIONSVRKEY VARCHAR2(10 BYTE) Yes 24
ACCTSTARTTIME VARCHAR2(100 CHAR) Yes 25
ACCTSTOPTIME VARCHAR2(100 CHAR) Yes 26


Thanks
Jay
Re: Delete statement to retain last 90 days of data [message #679076 is a reply to message #679075] Fri, 31 January 2020 09:36 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Delete statement to retain last 90 days of data [message #679077 is a reply to message #679075] Fri, 31 January 2020 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

You just have to be clear with your requirements and specification, this is not a SQL problem.
What do you want to keep?
Rows with start time less than 90 days old or with stop time less than 90 days old?
What do you want to do with rows with no stop time?
Only you can answer this question.

Re: Delete statement to retain last 90 days of data [message #679078 is a reply to message #679075] Fri, 31 January 2020 10:49 Go to previous messageGo to next message
John Watson
Messages: 8348
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Looking through the acctstoptime data I have found there can be a null entry in the column for acctstoptime. Maybe a network blip or such causing that.
No. This is not caused by a network blip. It is caused by your software permitting insertion of rows where that column is null.
Re: Delete statement to retain last 90 days of data [message #679079 is a reply to message #679077] Fri, 31 January 2020 10:50 Go to previous messageGo to next message
Jay76
Messages: 5
Registered: January 2020
Junior Member
Hi Thanks

Sorry if I've got somethings wrong.

I checked the version and got these details.

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE 12.1.0.2.0 Production"
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

Yep like you said...we want to keep "with stop time less than 90 days old". Regarding the rows with no stop time...also remove them.

I had this put together "delete from radacct where to_date(ACCTSTOPTIME, 'YYYY-MM-DD hh24:mi:ss') <= systimestamp -90;" but something isn't right and it doesnt cover the rows with no stop time.

[Updated on: Fri, 31 January 2020 10:53]

Report message to a moderator

Re: Delete statement to retain last 90 days of data [message #679080 is a reply to message #679079] Fri, 31 January 2020 10:53 Go to previous messageGo to next message
John Watson
Messages: 8348
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Yep like you said...we want to keep "with stop time less than 90 days old". Regarding the rows with no stop time...also remove them.
Your column acctstoptime is varchar2, does it actually store some kind of date and time? You will have to parse it into a date and time and use it as a filter on a DELETE statement. Have a go, it should be straightforward to write.

--update:
I see you have had a go! Cool. How about adding an OR to the filter so that you catch the NULLs too?

[Updated on: Fri, 31 January 2020 10:55]

Report message to a moderator

Re: Delete statement to retain last 90 days of data [message #679081 is a reply to message #679078] Fri, 31 January 2020 10:54 Go to previous messageGo to next message
Jay76
Messages: 5
Registered: January 2020
Junior Member
"No. This is not caused by a network blip. It is caused by your software permitting insertion of rows where that column is null."

Good point and noted John Watson.

[Updated on: Fri, 31 January 2020 10:55]

Report message to a moderator

Re: Delete statement to retain last 90 days of data [message #679082 is a reply to message #679080] Fri, 31 January 2020 10:59 Go to previous messageGo to next message
Jay76
Messages: 5
Registered: January 2020
Junior Member
"Your column acctstoptime is varchar2, does it actually store some kind of date and time? You will have to parse it into a date and time and use it as a filter on a DELETE statement. Have a go, it should be straightforward to write."

Yep it does - here is an example of the date and time it stores...no idea why previous colleagues set it up as VARCHAR2.

acctstarttime acctstoptime
2018-02-20 19:11:29 2018-02-20 19:21:41

"I see you have had a go! Cool. How about adding an OR to the filter so that you catch the NULLs too?"

Could you maybe show me how? I've tried but failed Sad

Cheers
Re: Delete statement to retain last 90 days of data [message #679083 is a reply to message #679082] Fri, 31 January 2020 11:56 Go to previous messageGo to next message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
delete from radacct 
where to_date(ACCTSTOPTIME, 'YYYY-MM-DD hh24:mi:ss') <= systimestamp -90
or ACCTSTOPTIME IS NULL;
You should never store a timestamp as a string, That is what a date column is for

ACCTSTARTTIME date;
ACCTSTOPTIME date;

[Updated on: Fri, 31 January 2020 11:56]

Report message to a moderator

Re: Delete statement to retain last 90 days of data [message #679084 is a reply to message #679075] Sat, 01 February 2020 08:41 Go to previous messageGo to next message
EdStevens
Messages: 1250
Registered: September 2013
Senior Member
It's already been pointed out several times that your ACCTSTOPTIME and ACCTSTARTTIME should be DATE not VARCHAR2. This is a fundamental design flaw in your data model. If such a table design were given to me to implement, I would reject it. Since it has already been pointed out, I mention it just to help drive home the point that it is not just one (or two) man's opinion.

Aside from that I'd like to know what ACCTSTOPTIME and ACCTSTARTTIME represeent in real world terms. The fact that the 'stop time' is sometimes null suggests at least two possibilities, and you need to account for them. First, it could (as already mentioned) be a bug in the code. That needs to be identified and addressed. Second, it could be a legitimate indicator that the record is not yet 'complete' (whatever that may mean), in which case I'd think you do NOT want to delete them, in spite of your assertion otherwise.

Re: Delete statement to retain last 90 days of data [message #679086 is a reply to message #679084] Mon, 03 February 2020 04:11 Go to previous messageGo to next message
Jay76
Messages: 5
Registered: January 2020
Junior Member
EdStevens wrote on Sat, 01 February 2020 08:41
It's already been pointed out several times that your ACCTSTOPTIME and ACCTSTARTTIME should be DATE not VARCHAR2. This is a fundamental design flaw in your data model. If such a table design were given to me to implement, I would reject it. Since it has already been pointed out, I mention it just to help drive home the point that it is not just one (or two) man's opinion.

Aside from that I'd like to know what ACCTSTOPTIME and ACCTSTARTTIME represeent in real world terms. The fact that the 'stop time' is sometimes null suggests at least two possibilities, and you need to account for them. First, it could (as already mentioned) be a bug in the code. That needs to be identified and addressed. Second, it could be a legitimate indicator that the record is not yet 'complete' (whatever that may mean), in which case I'd think you do NOT want to delete them, in spite of your assertion otherwise.


Thanks Bill B and Ed Stevens

Good points and taken on board. Today I'm questioning the DBA's why this table was setup like this and how it can be changed accordingly. I noticed that the accounting tables in other databases are setup different - they have the acctstoptime and acctstarttime set as TIMESTAMP(6) WITH TIME ZONE.

Regarding the NULL issue - the vendor of the application said "It's very unlikely to be an issue with FreeRADIUS, more likely to be something is missing from the incoming Accounting-Request." I'll look into this further and maybe do some packet traces to find what could be causing it. Probably safer to not include them in the clean up procedure for the time being at least.

This is then the clean up procedure I'm thinking of -

delete from radacct where acctstoptime <= systimestamp - 90;
delete from radacct where acctstarttime <= systimestamp - 90;

Any issues with that anyone can point out?

Thanks
Jay
Re: Delete statement to retain last 90 days of data [message #679087 is a reply to message #679086] Mon, 03 February 2020 06:03 Go to previous message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
that delete wont work because the date is a string, Do it like this
DELETE FROM
    Radacct
      WHERE     TO_DATE (Acctstoptime, 'YYYY-MM-DD HH24:MI:SS') <=
                SYSDATE - 90
            AND Acctstoptime IS NOT NULL;

DELETE FROM
    Radacct
      WHERE     TO_DATE (Acctstarttime, 'YYYY-MM-DD HH24:MI:SS') <=
                SYSDATE - 90
            AND Acctstarttime IS NOT NULL;
Previous Topic: ORA-12801: error signaled in parallel query server PXXX (merged 3)
Next Topic: Combining multiple regexp_like statements & correct regex syntax
Goto Forum:
  


Current Time: Mon Sep 28 09:50:52 CDT 2020