Mike, For Oracle 9i, 10g you can move a table online, which has the effect of reorganizing the table. Since this operation is "online", the table is still available, since a copy of it is done in memory, and this copy is the one accessed, while the reorging occurs. You can also rebuild indexes online. So at least with Oracle, you definitely do not need to export data, remove the table, recreate it, and import the data.
10g also has a shrink segment feature that is faster than the table online reorg, so that option seems better for very large tables BTW, this online reorging of tables and rebuilding indexes has been available since Oracle 8i I believe Guillaume From: Mike White Sent: Fri 12/01/06 8:59 AM To: [email protected] Subject: Re: Archiving Data in Remedy I haven't seen discussion about disk space and indexes relative to archival, so thought I'd add it as a consideration. In most databases, such as Oracle and MS SQL, records aren't actually deleted, but rather are flagged for delete. Usually some "other activity" is used to reclaim disk space from deleted records and reorganize indexes/index pages. "Other activity" in the case of Oracle 9i and earlier is to "drop table" and re-add, although I've read that export/import can be used as well. Some databases have a "reorg" or "compact" utility for this purpose. For low-volume or low-access tables, this may be insignificant. It can be performance-impacting for larger tables, though, especially if there's a high record turnover due to archive. We have a table with nearly a million records, for example. We add roughly 80K records per month and archive 60K (and growing - to 80K eventually). Our application that reads and writes to the table became noticeably slower over time, then improved dramatically after table delete/re-import. May or may not be an issue, but it's worth thinking about table and application behavior and performance. Mike White Office: 813-978-2192 E-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] TENFALL.COM To: [email protected] Sent by: "Action cc: Request System Subject: Re: Archiving Data in Remedy discussion list(ARSList)" <[email protected]> 12/01/2006 05:09 Please respond to arslist ** Hi, we have done exactly the same, and it has been up and running i prod för about 3-4 years, we archive request which have been in staus closed for 13 months, 500 every night including attachments, escalations and push field action, works fine. /L ars -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] Behalf Of Webster, Basil Sent: Friday, December 01, 2006 11:03 AM To: [email protected] Subject: Re: Archiving Data in Remedy ** Hi Jase, We have tried to use the built in archiving on numerous occasions and it let us down badly. From the archive form not being accessible to adding of fields etc. These can lead to the loss of data. What we did was use a similar approach as Remedy but build our own. We took the original form and copied it, removed all the unnecessary fields like page fields, table fields and buttons, changed the permissions to public on all the fields that remained. Form permissions are reserved for admins or a specific group. We then have 2 fields that are used to control the copy of the data (which could be done by either push or DSO, depending on what fits our needs). These same fields are applied to the archive form. The one field is used as a trigger which is stepped through stages. An escalation selects the records and marks this field ready for archive. This triggers a copy to the archive form. From the archive form an escalation checks the staging field and sets the original form as copied and an escalation takes care of the delete from the original form. I forgot, we had to create another field to house the original ID (same as Remedy). Trust this gives you an idea. Regards, Basil Webster Remedy Developer Siemens Business Services (Pty) Ltd * E-Mail: [EMAIL PROTECTED] ( Tel: +27 11 652 7523 Ê Fax: +27 11 652-7501 ) Mobile: +27 82 452 9389 From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Jase Brandon Sent: 30 November 2006 19:11 To: [email protected] Subject: Archiving Data in Remedy Hello Listers, I am looking for best practices and ideas for the beginning of Remedy data archival. We have had Remedy here for almost a year and are beginning to look into data archival options. Can anyone share ideas or experiences? Thanks in Advance, ") Help Desk 6.0 Windows Jase Brandon Remedy Administration/Development Customer Support Systems Group Desk - (615) - 320-4494 Cell - (334) - 318-5426 [EMAIL PROTECTED] DaVita Inc. __20060125_______________________This posting was submitted with HTML in it___ __20060125_______________________This posting was submitted with HTML in it___ __20060125_______________________This posting was submitted with HTML in it___ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
