ORA-13757: Can’t drop SQL Tuning Set

The following errors occur when dropping a SQL tunning set:

SQL> exec dbms_sqltune.drop_sqlset('STS_TEST','SYSTEM');
BEGIN dbms_sqltune.drop_sqlset('STS_TEST','SYSTEM'); END;
*
ERROR at line 1:
ORA-13757: "SQL Tuning Set" "STS_TEST" owned by user "SYSTEM" is active.
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 13226
ORA-06512: at "SYS.DBMS_SQLTUNE", line 4563
ORA-06512: at line 1

OR

On OEM console when you try to remove it, you get the error as follows:

Error
This SQL Tuning Set cannot be deleted because there are advisors tasks dependent on it.

CAUSE

The SQL Tuning Advisor have been created under the STS, and hence it is not allowing to drop the STS.

SOLUTION

1.Check for the STS view references to the sqlset:

SQL> select description, created, owner
    from DBA_SQLSET_REFERENCES
    where sqlset_name = 'STS_TEST';

DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATED           OWNER
----------------- ------------------------------
created by: SQL Tuning Advisor - task: SQL_TUNING_1664512487897
20220930-14:35:51 SYSTEM

2.Drop the dependent SQL tasks under the STS:

SQL>  exec DBMS_SQLTUNE.DROP_TUNING_TASK('SQL_TUNING_1664512487897');

PL/SQL procedure successfully completed.

3. Now you can drop the STS.

SQL> exec dbms_sqltune.drop_sqlset('STS_TEST','SYSTEM');

PL/SQL procedure successfully completed.

ORA-15041: diskgroup “FRA” space exhausted

The following errors occur in Oracle database alert log :

...
..
.
Unable to create archive log file '+FRA'
Errors in file /u01/app/oracle/diag/rdbms/testdb/TESTDB1/trace/TESTDB1_arc1_12300.trc:
ORA-19816: WARNING: Files may exist in db_recovery_file_dest that are not known to database.
ORA-17502: ksfdcre:4 Failed to create file +FRA
ORA-15041: diskgroup "FRA" space exhausted

CAUSE

Diskgroup FRA had been overallocated, the sum of db_recovery_file_dest_size of all the databases is more than the size of diskgroup FRA.

SQL> select NAME,TOTAL_MB,FREE_MB from v$asm_diskgroup
;

NAME                             TOTAL_MB    FREE_MB
------------------------------ ---------- ----------
...
..
.
FRA                               1024142       120

RESOLUTION

Decrease ‘db_recovery_file_dest_size” for some or all databases, and make sure the diskgroup FRA is not overallocated.

ORA-03206: maximum file size of (4194304) blocks in AUTOEXTEND clause is out of range (DBD ERROR: OCIStmtExecute) from Corrective Action of Add Space to Tablespace

OEM add space to tablespace corrective action failed with the following ORA-03206 error:

...
..
.
OMF directory: +DATAC1
OMF disk group: DATAC1
ASM projected safely usable free space (MB) is: 28548641.667
ASM projected Space Used (%) is: 59.11
Add datafile SQL is: ALTER TABLESPACE "TEST" ADD DATAFILE SIZE 7291M AUTOEXTEND ON NEXT 100M MAXSIZE 32768M
Execute(ALTER TABLESPACE "TEST" ADD DATAFILE SIZE 7291M AUTOEXTEND ON NEXT 100M MAXSIZE 32768M) failed:ORA-03206: maximum file size of (4194304) blocks in AUTOEXTEND clause is out of range (DBD ERROR: OCIStmtExecute)
Disconnected from database

CAUSE

For small datafile tablespace, the maximum datafile size is 32GB ( 32768M ) in theory. But in actual situation, the given size should be little less than 32GB.

SOLUTION

Explicitly specify the maximum datafile size to 31GB ( 31744 M ), for example, instead of the default 32GB ( 32768M ) in corrective action parameters configuration.

Before

Maximum Datafile Size (GB)

After

Maximum Datafile Size (GB)31
...
..
.
OMF directory: +DATAC1
OMF disk group: DATAC1
ASM projected safely usable free space (MB) is:  28548641.667
ASM projected Space Used (%) is: 59.11
Add datafile SQL is: ALTER TABLESPACE "TEST" ADD DATAFILE SIZE 7291M AUTOEXTEND ON NEXT 100M MAXSIZE 31744M
Successfully added new datafile

“ORA-01031: insufficient privileges” from SYS User Creating Materialized Views

A client try to create a materialized view by SYS user with the following errors:

SQL> show user
USER is "SYS"

SQL> create materialized view USER1.MV_TABLE2
BUILD IMMEDIATE
REFRESH FAST ON commit
with primary key
as select * from USER2.TABLE2;


ERROR at line 6:
ORA-01031: insufficient privileges

SOLUTION

Subscribe to get access

Read more of this content when you subscribe today.

“ORA-06533: Subscript beyond count” from DBMS_STATS

DBMS_STATS threw the following errors:

SQL> EXEC DBMS_STATS.gather_table_stats('USERNAME','TABLENAME');
BEGIN DBMS_STATS.gather_table_stats('USERNAME','TABLENAME'); END;

*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at "SYS.DBMS_STATS", line 34634
ORA-06512: at line 1

WORKAROUND

Use a numeric value for estimate_percent instead of default dbms_stats.auto_sample_size:

SQL> exec dbms_stats.gather_table_stats( ownname=>'USERNAME',tabname=>'EVENT',estimate_percent=>100);

PL/SQL procedure successfully completed.