0

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?

Maghio
  • 355
  • 1
  • 6
  • 18
  • You can do this, but are you sure you want to? What problem are you trying to solve? Usually it's better to put all objects in a schema in the same tablespace - there's no performance difference, and it's easier to manage. – Jon Heller Jul 18 '16 at 05:27

2 Answers2

2

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.

gvenzl
  • 1,861
  • 14
  • 22
1

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

Evgeniy K.
  • 1,137
  • 7
  • 11