Recently I was doing a POC on Oracle 21c, and I had to shrink an LOB secure file column, and I was aware about a new dynamic view added to Oracle 21c called V$SECUREFILE_SHRINK, which is very useful to monitor securefiles shrink operations. This gives you some great details like start time, end time, status of the operation etc. I shrank one of the required securefile LOB column of a table and I was interested to see what all was captured by V$SECUREFILE_SHRINK … But It was empty!! got ‘no rows selected’, Why is that ??
Let me try to replicate the scenario and explain what I was doing that day.
-- Let me create a test scenario
-- Will create a brand new playground to test all things
[oracle@witnessalberta ~]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Sun Aug 28 10:50:26 2022
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL>
SQL>
SQL>
SQL> CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY oracle90 CREATE_FILE_DEST='/opt/oracle/oradata';
Pluggable database created.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 MOUNTED
4 PDB2 MOUNTED
SQL> ALTER PLUGGABLE DATABASE pdb2 OPEN READ WRITE;
Pluggable database altered.
SQL> ALTER SESSION SET CONTAINER = PDB2;
Session altered.
SQL> SHOW CON_NAME;
CON_NAME
------------------------------
PDB2
SQL>
SQL> conn fatdba/oracle90@PDB2
Connected.
SQL>
SQL>
SQL>
SQL> show user
USER is "FATDBA"
SQL>
SQL>
SQL>
SQL>
-- Created a Table with a SECUREFILE LOB
SQL> CREATE TABLE secure_file_tab (rid NUMBER(5), bcol BLOB) LOB (bcol) STORE AS SECUREFILE bcol_lob
(TABLESPACE users DISABLE STORAGE IN ROW CHUNK 8192 RETENTION MIN 3600 KEEP_DUPLICATES NOCOMPRESS DECRYPT CACHE READS);
Table created.
SQL>
SQL>
-- Inserted some 100000 random rows into the table
insert into secure_file_tab VALUES(101, utl_raw.cast_to_raw('hello, this is the first review'));
insert into secure_file_tab VALUES(101, utl_raw.cast_to_raw('hello, this is the first review'));
insert into secure_file_tab VALUES(101, utl_raw.cast_to_raw('hello, this is the first review'));
insert into secure_file_tab VALUES(101, utl_raw.cast_to_raw('hello, this is the first review'));
insert into secure_file_tab VALUES(101, utl_raw.cast_to_raw('hello, this is the first review'));
insert into secure_file_tab VALUES(101, utl_raw.cast_to_raw('hello, this is the first review'));
...
......
........
SQL> select count(*) from secure_file_tab;
COUNT(*)
----------
100001
SQL>
SQL>
-- Lets check the size of the table.
SQL> select segment_name, bytes/1024 from dba_segments where segment_name='SECURE_FILE_TAB';
SEGMENT_NAME BYTES/1024
-------------------- ----------
SECURE_FILE_TAB 4096
-- Have deleted all rows from the table
SQL> delete from secure_file_tab where RID=101;
100000 rows deleted.
SQL> COMMIT;
SQL> select count(*) from secure_file_tab;
COUNT(*)
----------
1
-- Gathered table stats
SQL> exec dbms_stats.gather_table_stats(null, 'secure_file_tab');
PL/SQL procedure successfully completed.
SQL> select segment_name, bytes/1024 from dba_segments where segment_name='SECURE_FILE_TAB';
SEGMENT_NAME BYTES/1024
-------------------- ----------
SECURE_FILE_TAB 113
SQL> alter table secure_file_tab modify lob(bcol) (shrink space);
Table altered.
Now time to check if anything captured in the v$securefile_shrink or gv$securefile_shrink dynamic view about the securefile shrink operation.
-- Lets check if anything captured by the view
SQL> desc v$securefile_shrink
Name Null? Type
----------------------------------------- -------- ----------------------------
LOB_OBJD NUMBER
SHRINK_STATUS VARCHAR2(40)
START_TIME TIMESTAMP(3) WITH TIME ZONE
END_TIME TIMESTAMP(3) WITH TIME ZONE
BLOCKS_MOVED NUMBER
BLOCKS_FREED NUMBER
BLOCKS_ALLOCATED NUMBER
EXTENTS_ALLOCATED NUMBER
EXTENTS_FREED NUMBER
EXTENTS_SEALED NUMBER
CON_ID NUMBER
SQL> select * from v$securefile_shrink;
no rows selected
SQL> select * from gv$securefile_shrink;
no rows selected
SQL>
Nothing!!! Why ? Why ? Let me try the other way (MOVE LOB) and see if that populates anything in v$securefile_shrink or gv$securefile_shrink. Repopulated the same table again with 100000 rows, deleted all rows from the table, committed changes, regathered stats and verified table size, same what we did in Test 1.
-- Changed the LOB SHRINK technique this time.
SQL> ALTER TABLE secure_file_tab MOVE LOB(BCOL) STORE AS (TABLESPACE test);
Table altered.
-- Lets check if anything captured by the view
SQL> select * from v$securefile_shrink;
no rows selected
SQL> select * from gv$securefile_shrink;
no rows selected
SQL>
Nothing!!! Why ? Why ? Let me try the with one more way (SHRINK SPACE CASCADE) again and see if that populates anything in v$securefile_shrink or gv$securefile_shrink. Repopulated the same table again with 100000 rows, deleted all rows from the table, committed changes, regathered stats and verified table size, same what we did in Test 1.
--Let me try the other way
SQL> alter table secure_file_tab MODIFY LOB(BCOL) (SHRINK SPACE CASCADE);
Table altered.
-- Lets check if anything captured by the view
SQL> select * from v$securefile_shrink;
no rows selected
SQL> select * from gv$securefile_shrink;
no rows selected
SQL>
Nothing!!! Why ? Why ? This is super strange now. Then finally, I decided to check with Oracle support and asked them to try at their end in 21c and from their end also it was getting populated. I guess I have identified a BUG in 21.3.0.0.0!! 🙂 🙂 Currently the case is with development team and they are taking a look on this buggy behavior on Oracle 21.5 on Red Hat Linux 7 x86_64 … Let’s see how it goes from here, will post the solution or the cause behind this ill behavior soon.
Hope It Helped!
Prashant Dixit
Tales From A Lazy Fat DBA 