The forum will be down for maintenance at some point from Friday, November 16 at 19:00 EDT until Sunday, November 18 at 23:59 EDT. Downtime will be minimized but the exact timing is unknown.

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.

asked 15 Oct, 12:27

Volker%20Barth's gravatar image

Volker Barth
32.4k328476692
accept rate: 32%

edited 15 Oct, 12:28

> 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 :)

(19 Oct, 17:04) Breck Carter
Replies hidden
2

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...

(20 Oct, 13:47) Volker Barth
1

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.

(22 Oct, 08:49) Breck Carter

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.

permanent link

answered 15 Oct, 12:42

John%20Smirnios's gravatar image

John Smirnios
9.5k381121
accept rate: 39%

edited 16 Oct, 03:07

Volker%20Barth's gravatar image

Volker Barth
32.4k328476692

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, 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, 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, 07:54) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×233
×21
×10

question asked: 15 Oct, 12:27

question was seen: 86 times

last updated: 22 Oct, 08:49