Hi All,
First off I am using Oracle Database 11.2.0.2 on AIX 5.3.
We have a table that is partitioned monthly.
In this table there is a partition (LOWER), this lower partition is 1.5TB in size due to a BLOB column called (ATTACHMENT).
The rest of the table is not that big, about 30GB, its the BLOB column that is using up all the space.
The lower partition is in its own default tablespace (DefaultTablespace), the BLOB column in the lower partition is also in its own tablespace(TABLESPACE_LOB) - 1.5TB
I've been asked to free up some space by moving the TABELSPACE_LOB(from the lower partition) to an archive database, confirming the data is there and then removing the lower partition from production.
I don't have enough free space (or time) to do an expdp, I don't think its doable with so much data.
CREATE TABLE tablename
(
xx VARCHAR2(14 BYTE),
xx NUMBER(8),
xx NUMBER,
ATTACHMENT BLOB,
xx DATE,
xx VARCHAR2(100 BYTE),
xx INTEGER,
)
LOB (ATTACHMENT) STORE AS (
TABLESPACE DefaultTablespace
ENABLE STORAGE IN ROW
NOCOMPRESS
TABLESPACE DefaultTablespace
RESULT_CACHE (MODE DEFAULT)
PARTITION BY RANGE (xx)
(
PARTITION LOWER VALUES LESS THAN ('xx')
LOGGING
COMPRESS BASIC
TABLESPACE DefaultTablespace
LOB (ATTACHMENT) STORE AS (
TABLESPACE TABLESPACE_LOB
ENABLE STORAGE IN ROW
)
...>>
My idea was to take an datapump export of the table excluding the column ATTACHMENT, using external tables.
Then to create the table on the archive database "with" the column ATTACHMENT.
Import the data only, from what I understand if you use a dump file that has too many columns Oracle will handle it, i'm hoping it will work the other way round.
Then on production make the TABLESPACE_LOB read only and move it to the new file system.
This is a bit more complicated than a normal tablespace move due to how the table is split up.
Any advice would be very much appreciated.