Oracle 19c cannot shrink blob column

bloboracleoracle-19cshrink

I deleted a bunch of old data from a schema, including BLOB data, and afterwards I tried to shrink and compact everything. I managed to shrink all tables (enable row movement, shrink space compact, disable row movement), except the one BLOB column, which is stored on a separate segment:

SELECT
    segment_name,
    SUM(bytes) / 1024 / 1024 / 1024 AS "GB_SIZE"
FROM
    dba_segments
WHERE
    owner = 'MY_OWNER'
GROUP BY
    segment_name
ORDER BY
    SUM(bytes) / 1024 / 1024 / 1024 DESC;

Segment in question:

SEGMENT_NAME                GB_SIZE
SYS_LOB0000072887C00005$$   0,35955810546875

Note: this is a test DB, in prod DB, this is about 660 GB.

When I try to shrink it, I get this error:

ALTER TABLE my_table MODIFY LOB ( my_blob ) ( SHRINK SPACE );

Error starting at line : 1 in command -
alter table my_table modify lob (my_blob) (shrink space)
Error report -
ORA-10635: Invalid segment or tablespace type
10635. 00000 -  "Invalid segment or tablespace type"
*Cause:    Cannot shrink the segment because it is not in auto segment space
           managed tablespace or it is not a data, index or lob segment.
*Action:   Check the tablespace and segment type and reissue the statement

Segment details:

SELECT
    *
FROM
    dba_segments
WHERE
    segment_name = 'SYS_LOB0000072887C00005$$';

Relevant details:

Segment type: LOBSEGMENT
Segment subtype: SECUREFILE
Tablespace name: MY_DATA

Tablespace details:

SELECT
    *
FROM
    dba_tablespaces
WHERE
    tablespace_name = 'MY_DATA';

Relevant details:

Extent management: LOCAL
Allocation type: SYSTEM
Segment space management: AUTO

Exact Oracle version:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

As far as I can tell, everything is as it should be, so I'm not sure what is missing.

Best Answer

The SHRINK SPACE option is not supported for Securefile LOBs.

You may use DBMS_REDEFINITION (mostly online) or ALTER TABLE ... MOVE LOB (...) (offline, table is locked during move).

ORA-10635: Invalid Segment or Tablespace Type Error When Attempting To Shrink SOA-INFRA LOB [To Free Tablespace] (Doc ID 2382484.1)

Related Solutions

Encountering exception ORA-01555

Gaius is right, use DBA_TABLES for NUM_ROWS and DBA_SEGMENTS for size:

select owner,table_name,num_rows,last_analyzed from dba_tables;

The num_rows count is as of LAST_ANALYZED date, which should be close enough even without running DBMS_STATS.

For sizing:

select owner,segment_type,sum(bytes)/1024/1024 size_mb
from dba_segments
group by owner,segment_type;

or (depending what level of details you need):

select owner,segment_name,sum(bytes)/1024/1024 size_mb
from dba_segments
group by owner,segment_name;

For LOBs you'll need to join it to DBA_LOBS, for indexes to DBA_INDEXES, for tables to DBA_TABLES. A lot will depend on your specific requirement. Since you mentioned you are trying to figure out used space in tablespace it might be as simple as:

select sum(bytes)/1024/1024 size_mb
from dba_segments
where tablespace_name='YOUR_TBLSP_NAME';

Your solution would really work only with small DB as it would not be feasible to read all data on large DB.

I am hesitant to post how to address ORA-1555 as in this case this it NOT your primary issue but just for completeness - you are on 10g using automatic undo management so your DBA would have to increase undo_retention in your database (the ixora link is relevant to a DB without auto undo management).

Oracle 11g: Part Copy of Production data to Development Environment

Your initial idea was not bad at all. What you can do is store the unwanted partitions with their local indexes in separate tablespaces. Use rman for the cloning but use the SKIP TABLESPACE option to not clone the tablespaces with the unwanted partitions. (assuming online backup) After the clone, the skipped tablespaces have datafiles with status RECOVER.

see RMAN DUPLICATE DATABASE: Options

In the end you just drop the unwanted partitions. To be able to do that you first have to get rid of things like constraints and indexes that need to be re-created later on. This worked in 10gR2. Make sure that you don't drop the last partition of a table, in that case drop the table.

It is a bit of work but certainly possible. If the difference in Volume is huge or there are lots of copies, it might be worth spending some time for it.

Best Answer

Related Solutions

Encountering exception ORA-01555

Oracle 11g: Part Copy of Production data to Development Environment

Related Question