CODEWITHSUNDEEP
databaseoracle-databaseschematablespacelob
Maghio
Maghio

Reputation: 385

How to create a tablespace only for schema's LOBs in Oracle database

I have a schema S linked to a default tablespace T1 in an Oracle database.

I want to create another tablespace T2 only for LOBs linked to the same schema S.

Is it possible? How can I do it?

Upvotes: 0

Views: 5584

Answers (2)

gvenzl
gvenzl

Reputation: 1891

In Oracle you can specify the tablespace for each LOB column just like for each table. That means that you can create a different tablespace (or tablespaces) for your LOB data than your regular table data. Quite often this technique is used to store LOB data on cheaper storage than the table's data:

CREATE TABLESPACE DATATBS1 DATAFILE 'datatbs1.dbf';

CREATE TABLESPACE LOBTBS1 DATAFILE 'lobtbs1.dbf';

CREATE TABLE T1 (id NUMBER, text CLOB)
LOB (text) STORE AS SECUREFILE (TABLESPACE LOBTBS1)
TABLESPACE DATATBS1;

SELECT tablespace_name
 FROM user_lobs
  WHERE table_name = 'T1' AND column_name = 'TEXT';

TABLESPACE_NAME
------------------------------
LOBTBS1

SELECT tablespace_name
 FROM user_tables
  WHERE table_name = 'T1';

TABLESPACE_NAME
------------------------------
DATATBS1

For more information see LOB Storage Parameters in the Database SecureFiles and Large Objects Developer's Guide.

Upvotes: 2

Evgeniy K.
Evgeniy K.

Reputation: 1137

Yes you can but for all existing and new tables you should manually write lob tablespace. The same behavior is for lob indexes. How to do that see link https://docs.oracle.com/cd/B28359_01/appdev.111/b28393/adlob_tables.htm#CIHEBABG

Upvotes: 1

Related Questions