The last post was about extracting LOBS with PL/SQL. However, the operation I carried out was in a development environment and I suspect the demand for disk and high volume of the LOB data goes against the intermittent nature of the operating system and the not so great disk on the development server. What this means is that extracting lobs works and then doesn't work, without a clear pattern or reason, and it sometimes results in:BEGIN
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 20
The operation works and dumps a few hundred files, and then it fails. Then it will work. And so on and so on. This is on Windows and I have checked for locks, the database directory, the privileges and closed all references to the disk. Owing to time, I dropped this method and moved on to something I know will work well.
Before I proceed - the size of the LOB I want to shrink is as follows:
SELECT *
FROM (
SELECT ds.tablespace_name,
ds.segment_name,
sum(ds.bytes)/(1024*1024) total_mb
FROM dba_segments ds
WHERE ds.tablespace_name = upper('MYSCHEMA')
GROUP BY ds.tablespace_name,
ds.segment_name
ORDER BY total_mb desc
)
where rownum <= 20
TABLESPACE_NAME SEGMENT_NAME TOTAL_MB
------------------------------ ---------------------------- ----------
MYSCHEMA SYS_LOB0000092630C00005$$ 4080
........ ......................... ....
The table hosting this lob segment can be found using:
select table_name, column_name
from dba_lobs
where segment_name='SYS_LOB<LOBID>$$';
This is tiny in the grand scheme of things and I would like to keep it that way.
I ditched the PL/SQL powered extract in favour of the master of data extract: export data pump.
This is how space was released:
1.Export the table hosting the LOB segment.
2.Delete the data from the table.
3.Shrink the LOB segment.
1.Export the table hosting the LOB segment.
E:\oracle>expdp directory=MY_DMP_DIR dumpfile=myschema.table_containing_lobs.dmp logfile=myschema.table_containing_lobs.log tables=myschema.table_containing_lobs
Export: Release 11.2.0.3.0 - Production on Mon Jun 13 08:21:39 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA directory=MY_DMP_DIR dumpfile=myschema.table_containing_lobs.dmp logfile=myschema.table_containing_lobs.log tables=myschema.table_containing_lobs
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3.992 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "MYSCHEMA"."TABLE_CONTAINING_LOBS" 3.747 GB 41340 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
E:\ORACLE\MYSCHEMA.TABLE_CONTAINING_LOBS.DMP
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 08:28:29
E:\oracle>
2.Delete the data from the table.
I test all commands in a test environment before I execute on production.
delete from myschema.TABLE_CONTAINING_LOBS where TO_CHAR(CREATED_DATE,'YYYYMM') = '201507';
delete from myschema.TABLE_CONTAINING_LOBS where TO_CHAR(CREATED_DATE,'YYYYMM') = '201508';
SQL>
1703 rows deleted.
SQL>
1796 rows deleted.
SQL>
SQL> commit;
Commit complete.
Lets look at the size of the LOB after deleting data:
SELECT *
FROM (
SELECT ds.tablespace_name,
ds.segment_name,
sum(ds.bytes)/(1024*1024) total_mb
FROM dba_segments ds
WHERE ds.tablespace_name = upper('MYSCHEMA')
GROUP BY ds.tablespace_name,
ds.segment_name
ORDER BY total_mb desc
)
where rownum <= 20
TABLESPACE_NAME SEGMENT_NAME TOTAL_MB
------------------------------ ---------------------------- ----------
MYSCHEMA SYS_LOB0000092630C00005$$ 4080
........ ......................... ....
The high water mark is still at 4G. To reduce this, I need to shrink the LOB.
Shrinking a LOB is similar to shrinking a table but here we are applying the shrink operation on a LOB object alone.
3.Shrink the LOB segment.
I simply run the shrink command. I can run this on production because the size is fairly small and the operation will be done in no more than 30 seconds.
I would not run this on a LOB that is 40G - simply because the object will lock and all dependent operations will likely come to a halt. I would instead carry out a shrink in a planned maintenance window or in the middle of the night when the system is least active.
I can run the following to get my commands:
select 'ALTER TABLE '||owner||'.'||table_name||' MODIFY LOB ('||column_name||') (SHRINK SPACE);'
from dba_lobs
where segment_name IN ('SYS_LOB0000092630C00005$$');
which should produce:
ALTER TABLE MYSCHEMA.TABLE_CONTAINING_LOBS MODIFY LOB (MYLOB_COLUMN) (SHRINK SPACE);
Table altered.
FROM (
SELECT ds.tablespace_name,
ds.segment_name,
sum(ds.bytes)/(1024*1024) total_mb
FROM dba_segments ds
WHERE ds.tablespace_name = upper('MYSCHEMA')
GROUP BY ds.tablespace_name,
ds.segment_name
ORDER BY total_mb desc
)
where rownum <= 20
TABLESPACE_NAME SEGMENT_NAME TOTAL_MB
------------------------------ ---------------------------- ----------
GPUSERS1 SYS_LOB0000092630C00005$$ 574.9375
........ ......................... ....
No comments:
Post a Comment