Re: BLOBs as separate tablespace

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

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************
"Yaw" <yawi_at_bigpond.com> wrote in message
news:YICq8.15263$hi7.56526_at_news-server.bigpond.net.au...


> > create table t(id number,bincol Blob)

> > tablespace users

> > lob(bincol) store as (tablespace lob_data pctversion 0)

> >

> > which will store normal data in the users tablespace and lob data in a

> > dedicated lob tablespace. PCTVersion refers to the how much of the lob

> > segment is dedicated to storing old versions of the lob (for read

> > consistency reasons). 0 seems to me appropriate for lobs that are juzt

> > stored and never updated (though they could be deleted and inserted).

>

> Thanks, I'm a however, just a little confused.  Does that mean that there

> must exist already two tablespaces?  One for the normal tables, and

another


> where the LOBs will be stored?

>

> Using your example: we'll need to have a tablespace where the table 't' is

> stored.  And also another tablespace 'lob_data', where the LOB data will

be


> stored.

>

> TIA.

> Yaw.

>

>