ORA-13530: invalid TOPNSQL 10, must be in the range

 

Error while modifying AWR snapshot settings parameter "TOPNSQL". SQL> EXECUTE  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (TOPNSQL => 10); BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (TOPNSQL => 10); END; * ERROR at line 1: ORA-13530: invalid TOPNSQL 10, must be in the range (30, 50000) ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 213 ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 265 ORA-06512: at line 1 #Check the existing value of TOPNSQL column. SQL> set lines 300 pages 3000 SQL> col SNAP_INTERVAL for a22 SQL> col RETENTION for a22 SQL> col TOPNSQL for a10 SQL> col SRC_DBNAME for a22 SQL> select DBID,SNAP_INTERVAL,RETENTION,TOPNSQL,SRC_DBNAME from dba_hist_wr_control;       DBID SNAP_INTERVAL     RETENTION         TOPNSQL    SRC_DBNAME ---------- ----------------- ----------------- ---------- ---------- 2447257321 +00000 00:15:00.0 +00015 00:00:00.0         50 test #Execute below command to modify TOPNSQL column value to the out of the range i.e.  (<30 and >50000). SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(TOPNSQL => 30); PL/SQL procedure successfully completed. SQL> col TOPNSQL for a10 SQL> select DBID,SNAP_INTERVAL,RETENTION,TOPNSQL,SRC_DBNAME from dba_hist_wr_control;       DBID SNAP_INTERVAL     RETENTION         TOPNSQL    SRC_DBNAME ---------- ----------------- ----------------- ---------- ---------- 2447257321 +00000 00:15:00.0 +00015 00:00:00.0         30 test SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(TOPNSQL => 50000); PL/SQL procedure successfully completed. SQL> select DBID,SNAP_INTERVAL,RETENTION,TOPNSQL,SRC_DBNAME from dba_hist_wr_control;       DBID SNAP_INTERVAL     RETENTION         TOPNSQL    SRC_DBNAME ---------- ----------------- ----------------- ---------- ---------- 2447257321 +00000 00:15:00.0 +00015 00:00:00.0      50000 test SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(TOPNSQL => 29); BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(TOPNSQL => 29); END; * ERROR at line 1: ORA-13530: invalid TOPNSQL 29, must be in the range (30, 50000) ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 213 ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 265 ORA-06512: at line 1 SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(TOPNSQL => 50001); BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(TOPNSQL => 50001); END; * ERROR at line 1: ORA-13530: invalid TOPNSQL 50001, must be in the range (30, 50000) ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 213 ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 265 ORA-06512: at line 1 You can see that you cannot set the value of the TOPNSQL column to out of the defined range i.e. <30 and >5000. You can only set the value in the range of 30 and 50000.

Thanks for reading this post ! Please comment if you like this post ! Click on FOLLOW to get future blog updates !