Shrinking BIG file tablespace
Version: 11.1.0.7.4 RAC & ASM
OUR DB size is 3TB and all the tablespaces are BIGFILE tablespaces, one datafile has grown to consumed all Free Space in ASM. We have dropped the table, but some LOB segments are at the end of the data file, so we could not shrink the datafile at this time as we are getting below error
ORA 3297 file contains <num> blocks of data beyond requested RESIZE value
Datpump export & re-import all the object in that tablespace will relieve the space. However at this time there is not empty ASM space to adopt this route.
So If we take "COMPRESSED RMAN BACKUP" of this tablespace and drop the tablespace and re-store it again, will the segments in that tablespace after re-store will be compact? so that we can re-size the datafile?
or
RMAN tablespace restore will have no effect on segments inside the tablespace evethough the RMAN backup is COMPRESSED ?