Skip to main content

Posts

Showing posts from 2020

Oracle Database 19c Bugs and Workarounds

Analytic Workspace Manager Known Bugs * BUG 28532773 - CUBE STORAGE ADVISOR FAILS TO RUN UNLESS USER CAN QUERY V$PARAMETER Cube Storage Advisor fails with the following error: ORA-00942 table or view not found Workaround: None * BUG 28937717 - CALCULATIONS MISSING FROM' CREATE CALCULATION MEASURES' Measures cannot be calculated in the Create Calculation Measure wizard. Workaround: None Issues Affecting Linux for Oracle Database 19c BUG 26708302 - UNABLE TO DELETE "DEINSTALL" EMPTY FOLDER ON SHARED RAC HOME ON NAS Deinstallation of Oracle Real Application Clusters (Oracle RAC) home on shared Network Attached Storage (NAS) fails to delete the directory $ORACLE_HOME/deinstall and returns the following error: Failed to delete the directory $ORACLE_HOME/deinstall. Either user has no permission to delete or it is in use. Workaround: Manually delete the $ORACLE_HOME/deinstall folder as either the Oracle RAC owner or as root. Known Issues and Bug...

How to identify the SQL_ID of a statement

The SQL_ID of a statement can be found in an AWR or ASH report or by selecting it from the database data dictionary using the V$SQL view. If the SQL can be identified with a particular identifiable string or by some kind of unique comment such as /* TARGET SQL */ then this will make it easier to locate. For Example: SELECT /* TARGET SQL */ * FROM dual; SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text FROM v$sql WHERE sql_text like 'SELECT /* TARGET SQL */%' SQL_ID PLAN_HASH_VALUE SQL_TEXT ------------- --------------- ---------------------------------------- 0xzhrtn5gkpjs 272002086 SELECT /* TARGET SQL */ * FROM dual The plan_hash_value is included here for convenience. You can also find the SQL_ID in the V$SQL view using a substitution variable: SELECT sql_id, plan_hash_value, SUBSTR(sql_text,1,40) Text FROM v$sql WHERE sql_text LIKE '%&An_Identifiable_String%'; If SQL is no longer available in v$sql, you can find i...

ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

ORA-30013 is the error reported when the specified Undo tablespace is in use. In other words, this implies there are some background processes still using the undo tablespace.   This error can be reported in both space management or transaction management.   Error ORA-30013 reported while dropping the Undo tablespace.   In this case, the Undo tablespace is used by other active transactions, leading to the error. In such cases, we have to wait until the transactions are committed or rolled back before dropping the Undo tablespace. This situation is similar to that of the ORA-1548 case. Error ORA-30013 reported while bringing up the database.   This happens for RAC databases while the undo tablespace mentioned for an instance is already in use. The solution will be to set separate Undo tablespace for all instances.   Since undo_tablespace is an instance local parameter, we have to ensure that the "Alter system set undo_tablespace" is being invo...