Large Objects - Pro Oracle Database 12c Administration - page 285

Adding a LOB Column
If you have an existing table to which you want to add a LOB column, use the ALTER TABLE...ADD statement. The next
statement adds the INV_IMAGE column to a table:
SQL> alter table patchmain add(inv_image blob);
This statement is fine for quickly adding a LOB column to a development environment. For anything else, you
should specify the storage characteristics. For instance, this command specifies that a SecureFiles LOB be created in
the LOB_DATA tablespace:
alter table patchmain add(inv_image blob)
lob(inv_image) store as securefile(tablespace lob_data);
Removing a LOB Column
You may have a scenario in which your business requirements change, and you no longer need a column. Before
you remove a column, consider renaming it so that you can better identify whether any applications or users are still
accessing it:
SQL> alter table patchmain rename column patch_desc to patch_desc_old;
After you determine that nobody is using the column, use the ALTER TABLE...DROP statement to drop it:
SQL> alter table patchmain drop(patch_desc_old);
You can also remove a LOB column by dropping and recreating a table (without the LOB column). This, of
course, permanently removes any data as well.
Also keep in mind that in Oracle Database 10g and higher, if your recycle bin is enabled, then when you don't
drop a table with the PURGE clause, space is still consumed by the dropped table. If you want to remove the space
associated with the table, use the PURGE clause, or purge the recycle bin after dropping the table.
Caching LOBs
By default, when reading and writing LOB columns, Oracle doesn't cache LOBs in memory. You can change the
default behavior by setting the cache-related storage options. This example specifies that Oracle should cache a LOB
column in memory:
create table patchmain(
patch_id number
,patch_desc clob)
lob(patch_desc) store as (tablespace lob_data cache);
You can verify the LOB caching with this query:
SQL> select table_name, column_name, cache from user_lobs;