What we wanted to achieve:
We wanted reclaim the space , what DBFS is using in ASM. but we didn't want to deconfigure the DBFS , so we can use it any time in future.
Standard steps would be :
1.Delete all the files
2.Unmount the dbfs mount point (stop the crs resource)
3.Resize the datafile to minimum value .
4.Shut down the DBFS database
But on step 3 we hit the road block:
SQL> alter database datafile '+DATA_PROD/dbfs/datafile/dbfsts.532.817432735' resize 100g;
alter database datafile '+DATA_PROD/dbfs/datafile/dbfsts.532.817432735' resize 100g
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
Looking into the segments of the tablespace , I noticed
SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024/1024
-------------------------------------------------- ------------------------------------ --------------------
T_FS2 TABLE .000061035
IG_SFS$_FST_1731 INDEX .000061035
SYS_IL0000140011C00007$$ LOBINDEX .000061035
IG_SFS$_FST_1823 INDEX .000061035
SYS_IL0000140348C00007$$ LOBINDEX .000061035
LOB_SFS$_FST_1823 LOBSEGMENT 1230.59357
IP_SFS$_FST_1823 INDEX .000061035
IPG_SFS$_FST_1823 INDEX .000061035
SYS_IL0000148710C00007$$ LOBINDEX .074035645
SYS_LOB0000148710C00007$$ LOBSEGMENT 87.8859863
T_DUMP TABLE .00012207
INTERIM TABLE .000183105
LOB_SFS$_FST_1731 LOBSEGMENT .00012207
IP_SFS$_FST_1731 INDEX .000061035
IPG_SFS$_FST_1731 INDEX .000061035
SFS$_FSTP_1731 TABLE .000061035
SYS_C0039855 INDEX .000061035
T_FS3 TABLE .000061035
IG_SFS$_FST_1777 INDEX .000061035
SYS_IL0000140075C00007$$ LOBINDEX .000061035
LOB_SFS$_FST_1777 LOBSEGMENT .00012207
IP_SFS$_FST_1777 INDEX .000061035
IPG_SFS$_FST_1777 INDEX .000061035
SFS$_FSTP_1777 TABLE .000061035
SYS_C0039985 INDEX .000061035
We see the LOB segments which are occupying the space in tablespace , We learned that we have to perform a reorg to reclaim the space. We used online dbms_redef to accomplish this.
This Note Id is very helpful -How to Shrink a SECUREFILE LOB Using Online Redefinition (DBMS_REDEFINITION)? (Doc ID 1394613.1)
Step 1.DETERMINE IF THE TABLE CAN BE REDEFINED ONLINE
SET SERVEROUTPUT ON
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('DBFS_USER','T_DUMP', DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/
PL/SQL procedure successfully completed.
Step 2. CREATE THE INTERIM TABLE
SQL> CREATE TABLE DBFS_USER.INTERIM AS SELECT * FROM DBFS_USER.T_DUMP WHERE 1=2;
Table created.
Step 3 : START THE REDEFINITION
this step can take some time
SQL> BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'DBFS_USER',
orig_table => 'T_DUMP',
int_table => 'INTERIM',
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/ 2 3 4 5 6 7 8
PL/SQL procedure successfully completed.
Step 4: FINISH THE REDEFINITION
SQL> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('DBFS_USER','T_DUMP','INTERIM');
PL/SQL procedure successfully completed.
Step 5 : DROP INTERIM TABLE (this is not there in the note ID , but its pretty much self explanatory)
SQL> drop table dbfs_user.INTERIM cascade constraints;
Table dropped.
Purge recyclebin if you have it enabled.
Thats it, go ahead and resize the file ,
SQL> alter database datafile '+DATA_PROD/dbfs/datafile/dbfsts.532.817432735' resize 10g;
Database altered.
