|
I guess the answer is "yes" but are not sure how to check that... Running 16.0.0.2704, I have added a dbspace (*) to store blob data on that separate dbspace. Now it looks like the system dbspace has unexpectedly grown, too. However, according to SYSTAB and SYSIDX, the freshly created tables and their indexes are stored on the dbspace, as desired. Because most rows are stored in extension pages, I'm unsure whether they are stored in the dbspace, too. Is there a system procedure to check that? (*) I usually do not use separate dbspaces but in that case, it's quite likely the blob data will be purged lateron, and I do not want to have the main database file grown too much. |
|
The extension pages are stored in the same dbspace as the other table pages. Are you updating or deleting blobs? If so, perhaps the increased file size you are seeing is the checkpoint log. The checkpoint log at the end of the system dbspace is used for saving images from all dbspaces. You can use db_property('CheckpointLogSize') to find how many pages in the system dbpsace file are being used for the checkpoint log. Ah yes, the checkpoint log has grown that much... - although I have just used LOAD TABLE to insert these blobs, so there should not have been any blob updates...
(16 Oct '18, 03:11)
Volker Barth
Replies hidden
1
Insertion doesn't usually generate many checkpoint log pages since newly allocated pages don't get a preimage stored in the checkpoint log (there is no preimage for a newly allocated page). That's why I asked if you were updating or deleting. Index pages will get modified for inserts though.
(16 Oct '18, 07:00)
John Smirnios
Thanks again, John – Then I guess this is due to modified index pages because I created several indixes before loading the data, and loading will have led to modified index pages. So I'm thinking about delaying the index creation after the initial loading...
(16 Oct '18, 07:54)
Volker Barth
|
> do not want to have the main database file grown too much
Why is it ok to put the blobs in a secondary dbspace but not the system dbspace? (no, I am not accusing you of Fat Shaming :)
It's a particular situation: The CLOB data will only be needed for some time (probably a few weeks) and will then be discarded. As it needs a significant amount of space (almost half the size of the main database file), I just don't want to increase the main file that much. So, I can later simply drop the new dbspace and have a still fitting main file without the need to rebuild...
That is an excellent idea for dealing with huge amounts of data that must be stored in permanent tables on a temporary basis: DROP TABLE followed by DROP DBSPACE when it is no longer needed.
The need to store temporary data in permanent tables sounds like a contradiction but it is fairly common in practice: the words "temporary" and "permanent" describe different time frames, and permanent tables have some huge advantages like persistence and recoverability.
So thanks, Volker, I have just added this item to the Foxhound to-do list: Investigate the applicability of DROP DBSPACE to various Foxhound functions.