Re: BLOBs as separate tablespace

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Thu, 04 Apr 2002 04:40:47 +1000
Message-ID: <a8feu8$tp1$1@lust.ihug.co.nz>

Yaw wrote:

> Thanks for your replies. :) They're really good. :)
>
> All this time I was labouring under the belief that in order to create BLOBs
> in tables the BLOBs themselves "must" be stored in anther tablespace if they
> were larget than 4K (I think).
>
> From your reply, it seems that I was wrong.
>
> Yaw.
>
>
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
> news:3cab0bfc$0$8514$ed9e5944_at_reading.news.pipex.net...

>> sorry came in late.
>>
>> Yes using my example you do need two pre existing tablespaces. The

> rationale

>> for my approach is to seperate out the blob data from the bog standard
>> relational data. If you don't use the store as clause the lob segments

> will

>> get stored in tablespace USERS on my example. if you don't specify any
>> tablespace clause the whole lot will get stored in the creating users
>> default tablespace, and if you don't specify a storage clause for the

> table

>> you like as not have not specified a default storage clause for the user

> so

>> everything will end up in the SYSTEM tablespace.
>>
>> Or in other words lobs get stored
>>
>> in the tablespace specified in the store as clause of create table or if
>> this isn't specified
>> in the tablespace specified in the create table statement or if there

> isn't

>> one specified
>> in the creating users default tablespace. (as does the table).

What you are thinking of is whether or not BLOB and CLOB data are stored "in line" with the rest of a table's data, or whether they are stored "out of line"
in their own LOB segment, leaving a pointer behind in the row showing where the
actual data is stored.

It is true that if your LOB is smaller than 4K, it can be stored in line. Anything larger than that, and it must be stored out of line.

Once it's stored out of line, you can choose where to house it according to Niall's rules (ie, unless you explicitly declare it otherwise, the LOB segment
gets created in the same tablespace as the table).

--
Regards
HJR
--------------------------------------------
Resources for Oracle : http://www.hjrdba.com
============================================

Received on Wed Apr 03 2002 - 12:40:47 CST