Dave, it’s normal behavior of an oracle database to not release any space if you delete records. It’s also not an issue specific to LOBs. But there are ddl commands to reorganize the table online to reclaim “wasted” space.
alter table T63 enable row movement; alter table T63 shrink space cascade; and don’t forget to rebuild the indexes as well. Regards Conny Von: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] Im Auftrag von Dave Barber Gesendet: Donnerstag, 1. September 2016 15:39 An: arslist@ARSLIST.ORG Betreff: Re: EXTERNAL: Application-Query-Delete-Entry ** All, Many thanks - I'm going with the archive/delete from source option. The messages form is already down to a "manageable" number of records (2 million, we're sending about 10,000 messages per day!) Interesting aside - this hasn't actually freed up any space on the database. Its apparently something in the nature of LOBs (which are used in the To/Cc/Bcc fields) which means that Oracle doesn't release the space as you may expect. One of our DBAs has some thoughts on resolving this that involves creating a copy of T63, copy records over, then replace T63 with the copy. That leaves me feeling a little uncomfortable with the H/B tables and database integrity. Regards Dave On 31 August 2016 at 17:22, Brittain, Mark <mbritt...@navisite.com<mailto:mbritt...@navisite.com>> wrote: ** Sort of like Todd says, I had a similar situation but with a much larger form in 6.3 using archive, delete from source. What I did was a little bit each night changing the archive query each morning sort of like this ‘Create Date’ < $DATE$ - (60 * 60 *24 * 720) ‘Create Date’ < $DATE$ - (60 * 60 *24 * 713) ‘Create Date’ < $DATE$ - (60 * 60 *24 * 706) Took me about a month to get where I wanted to be. Slow but if you’re worried about performance, that works Mark From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG>] On Behalf Of Dave Barber Sent: Wednesday, August 31, 2016 2:55 AM To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG> Subject: Re: EXTERNAL: Application-Query-Delete-Entry ** Todd, Regardless of the method used (archiving, or application-query-delete-entry), I realised this could be a problem - I've already (manually) cleared down the email messages form. Awkward though, as I had set it to run overnight and delete a small data set (should have only been 21 records), and it doesn't appear to have done anything. Regards Dave On 30 August 2016 at 16:39, Arner, Todd <tar...@glhec.org<mailto:tar...@glhec.org>> wrote: ** Be cautious when adding the archiving, especially if the first night it runs if it will be trying to archive a large number of records. We ran into some major performance issues. We ended up slow adjusting the archive criteria so that a smaller amount of records were archived each night until the back log was processed. Todd Arner Great Lakes From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG>] On Behalf Of Dave Barber Sent: Tuesday, August 30, 2016 10:29 AM To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG> Subject: Re: EXTERNAL: Application-Query-Delete-Entry ** I had completely forgotten about the archiving options (my bad, etc. etc) Much appreciated, I've put that in place to run overnight, with luck it should work! On 30 August 2016 at 15:11, Reiser, John J <john.j.rei...@lmco.com<mailto:john.j.rei...@lmco.com>> wrote: ** Dave, Have you tried using the Archive function to just “Delete from Source”? It sets up just like an escalation with a timing cycle to run it and a Qualification to select which data to delete. Thank you, --- John J. Reiser Building 760-J202 Remedy AR System Developer Senior Software Development Analyst Lockheed Martin - MST Moorestown Region The star that burns twice as bright burns half as long. Pay close attention and be illuminated by its brilliance. - paraphrased by me From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG>] On Behalf Of Dave Barber Sent: Tuesday, August 30, 2016 9:49 AM To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG> Subject: EXTERNAL: Application-Query-Delete-Entry ** All, I'm trying to figure out the syntax required for Application-Query-Delete-Entry .... (Server is running ARS 7.5) Trying to keep an "archive" of emails - we have a requirement to keep 6 months worth in the system (audit trail, proof of sending, etc.), and the form was actually turning out to be the largest on the system (over 7 million records - I manually deleted the older ones via the user tool, that is not fun!). In an active link, just trying a simple $PROCESS$ @@:Application-Query-Delete-Entry "AR System Email Messages" '3' < ($TIMESTAMP$ - (86400 * 75)) The 75 is related to a test system and the data that is on it (would obviously be changed on live). The query itself is fine against the email messages form, it just comes up with a qualification error when run via the active link. Any suggestions? I could try an alternative using Application-Delete-Entry <form> <record ID> and table walking, but that'll be horrendously inefficient in comparison to Application-Query-Delete-Entry. Regards Dave Barber _ARSlist: "Where the Answers Are" and have been for 20 years_ _ARSlist: "Where the Answers Are" and have been for 20 years_ _ARSlist: "Where the Answers Are" and have been for 20 years_ ________________________________ The information contained in this communication may be confidential, is intended only for the use of the recipient(s) named above, and may be protected under state or federal law. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication, or any of its contents, is strictly prohibited. If you have received this communication in error, please forward the communication to no...@glhec.org<mailto:no...@glhec.org> immediately and destroy or delete the original message and any copy of it from your computer system. If you have any questions concerning this message, please contact the sender. _ARSlist: "Where the Answers Are" and have been for 20 years_ _ARSlist: "Where the Answers Are" and have been for 20 years_ _ARSlist: "Where the Answers Are" and have been for 20 years_ _ARSlist: "Where the Answers Are" and have been for 20 years_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
