Delete statement to retain last 90 days of data [message #679075] |
Fri, 31 January 2020 09:11 |
|
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 #679078 is a reply to message #679075] |
Fri, 31 January 2020 10:49 |
John Watson
Messages: 8937 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 |
|
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 |
John Watson
Messages: 8937 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 #679086 is a reply to message #679084] |
Mon, 03 February 2020 04:11 |
|
Jay76
Messages: 5 Registered: January 2020
|
Junior Member |
|
|
EdStevens wrote on Sat, 01 February 2020 08:41It'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 |
Bill B
Messages: 1971 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;
|
|
|