Optimizing Storage Structures - Oracle Database 11gR2 Performance Tuning - page 90

In the next screenshot, we can see the calculated Used and Allocated bytes:

We can experiment with different input values for the previous procedure, obtaining what will be
the wasted space in our table using a different PCT_FREE parameter in the storage options.

Using LOBs

LOBs (Large OBjects) are a particular data type, used to store large binary or character
objects inside the database or outside the database when using BFILEs . In this recipe, we
will see how to use LOB fields to avoid performance degradation and space wasting.

Getting ready

The following steps have to be performed initially;

1. Connect to the database as SYSDBA :

CONNECT /@TESTDB AS SYSDBA

2. Grant the following permission to user SH :

GRANT CREATE ANY DIRECTORY TO SH;

3. Create a tablespace for LOBs:

CREATE TABLESPACE ASSM_TS DATAFILE 'ASSM_TS.DBF' SIZE 100M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;