Re: using lobs system types to manually clear data
Fred Toussi <[email protected]>
| Newsgroups | gmane.comp.java.hsqldb.user |
|---|---|
| Message-ID | <[email protected]> |
The empty space sizes are in the second column of SYSTEM_LOBS.BLOCKS table. Each unit represents one unit of LOB SCALE which is 32K by default. In the .script file there are entries like this: INSERT INTO BLOCKS VALUES(25391,1,0) INSERT INTO BLOCKS VALUES(25392,1,0) In the above list there are two blocks each with one empty unit. The last entry has a very large value and indicates the space beyond the current file INSERT INTO BLOCKS VALUES(25918,2147457729,0) Therefore you can use something like this to calculate the number of free blocks within the file: SELECT SUM(BLOCK_COUNT) FROM SYSTEM_LOBS.BLOCKS WHERE BLOCK_COUNT < 10000000 Fred On Thu, May 22, 2014, at 2:28, Brian Young wrote: My only concern is that writing a lot of data would "stretch" the .lobs file even larger as it grows to accommodate an artificially large quantity of random data. File size is a major concern since this is part of a desktop application. Is there some way to calculate exactly how much random data I'd need to write to fill the unused areas of a given .lobs file so I can eliminate or at least minimize further enlarging the .lobs file? Thanks for the help. On Wed, May 21, 2014 at 2:22 PM, Fred Toussi <[1][email protected]> wrote: It is easier to delete the rows that contain the lobs. The spaces then become available for new lobs after a checkpoint. Create a a table with a lob column and insert random data in multiples of 32 GB. This will fill up the space. Then delete rows from this table and finally perform a checkpoint to release the space again. The LOB_IDS table is for the currently existing lobs that have a reference, not deleted ones. Fred On Wed, May 21, 2014, at 18:38, Brian Young wrote: Hello, in our usage of hsqldb, it is sometimes necessary to permanently delete blobs such that someone "in the know" couldn't recover said blob from the .lobs file. I am considering reading the system tables and zero out the blocks that are identified as having no active references. Obviously this would be done while the database is "offline", meaning the cleanup process is the owner and single user of the database. I am assuming LOB_IDS.LOB_LENGTH is in bytes, but how do I determine the offset a given LOB within the .lobs file? Are there any gotchas here? Thanks ----------------------------------------------------------------------- ------- "Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE Instantly run your Selenium tests across 300+ browser/OS combos. Get unparalleled scalability from the best Selenium testing platform available Simple to use. Nothing to install. Get started now for free." [2]http://p.sf.net/sfu/SauceLabs _______________________________________________ Hsqldb-user mailing list [3][email protected] [4]https://lists.sourceforge.net/lists/listinfo/hsqldb-user -------------------------------------------------------------------- ---------- "Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE Instantly run your Selenium tests across 300+ browser/OS combos. Get unparalleled scalability from the best Selenium testing platform available Simple to use. Nothing to install. Get started now for free." [5]http://p.sf.net/sfu/SauceLabs _______________________________________________ Hsqldb-user mailing list [6][email protected] [7]https://lists.sourceforge.net/lists/listinfo/hsqldb-user ----------------------------------------------------------------------- ------- "Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE Instantly run your Selenium tests across 300+ browser/OS combos. Get unparalleled scalability from the best Selenium testing platform available Simple to use. Nothing to install. Get started now for free." [8]http://p.sf.net/sfu/SauceLabs _______________________________________________ Hsqldb-user mailing list [9][email protected] [10]https://lists.sourceforge.net/lists/listinfo/hsqldb-user References 1. mailto:[email protected] 2. http://p.sf.net/sfu/SauceLabs 3. mailto:[email protected] 4. https://lists.sourceforge.net/lists/listinfo/hsqldb-user 5. http://p.sf.net/sfu/SauceLabs 6. mailto:[email protected] 7. https://lists.sourceforge.net/lists/listinfo/hsqldb-user 8. http://p.sf.net/sfu/SauceLabs 9. mailto:[email protected] 10. https://lists.sourceforge.net/lists/listinfo/hsqldb-user ------------------------------------------------------------------------------ "Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE Instantly run your Selenium tests across 300+ browser/OS combos. Get unparalleled scalability from the best Selenium testing platform available Simple to use. Nothing to install. Get started now for free." http://p.sf.net/sfu/SauceLabs _______________________________________________ Hsqldb-user mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/hsqldb-user