Team:
The below demo was from Oracle 18c database, The document stats that "lock_partition_stats" method was possible on subpartition level.
However the below test case shows that it was not possible - could you please confirm, is it not possible to lock subpartition level stats in Oracle 18c database?
https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_STATS.html#GUID-C8437383-8D09-4BB1-936A-0B620E0B6A0E demo@XEPDB1> create table t(x int,y int, z date)
2 partition by list(x)
3 subpartition by hash(y)
4 subpartitions 2
5 ( partition p1 values (1) ,
6 partition p2 values (2) );
Table created.
demo@XEPDB1> select table_name,partition_name,subpartition_name,stattype_locked,stale_stats
2 from user_tab_statistics
3 where table_name ='T'
4 order by partition_name;
TABLE_NAME PARTITION_ SUBPARTITION_ STATT STALE_S
---------- ---------- ------------- ----- -------
T P1 SYS_SUBP376
T P1
T P1 SYS_SUBP377
T P2
T P2 SYS_SUBP379
T P2 SYS_SUBP378
T
7 rows selected.
demo@XEPDB1> exec dbms_stats.LOCK_PARTITION_STATS(user,'T','P1');
PL/SQL procedure successfully completed.
demo@XEPDB1> select table_name,partition_name,subpartition_name,stattype_locked,stale_stats
2 from user_tab_statistics
3 where table_name ='T'
4 order by partition_name;
TABLE_NAME PARTITION_ SUBPARTITION_ STATT STALE_S
---------- ---------- ------------- ----- -------
T P1 SYS_SUBP376 ALL
T P1 ALL
T P1 SYS_SUBP377 ALL
T P2
T P2 SYS_SUBP379
T P2 SYS_SUBP378
T
7 rows selected.
demo@XEPDB1> exec dbms_stats.UNLOCK_PARTITION_STATS(user,'T','P1');
PL/SQL procedure successfully completed.
demo@XEPDB1> select table_name,partition_name,subpartition_name,stattype_locked,stale_stats
2 from user_tab_statistics
3 where table_name ='T'
4 order by partition_name;
TABLE_NAME PARTITION_ SUBPARTITION_ STATT STALE_S
---------- ---------- ------------- ----- -------
T P1 SYS_SUBP376
T P1
T P1 SYS_SUBP377
T P2
T P2 SYS_SUBP379
T P2 SYS_SUBP378
T
7 rows selected.
demo@XEPDB1> exec dbms_stats.LOCK_PARTITION_STATS(user,'T','SYS_SUBP376');
PL/SQL procedure successfully completed.
demo@XEPDB1> select table_name,partition_name,subpartition_name,stattype_locked,stale_stats
2 from user_tab_statistics
3 where table_name ='T'
4 order by partition_name;
TABLE_NAME PARTITION_ SUBPARTITION_ STATT STALE_S
---------- ---------- ------------- ----- -------
T P1 SYS_SUBP376
T P1
T P1 SYS_SUBP377
T P2
T P2 SYS_SUBP379
T P2 SYS_SUBP378
T
7 rows selected.
demo@XEPDB1>