databaseoracle-databaseschematablespacelob

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?

Solution

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