ORA-1652: unable to extend temp segment by ... in tablespace ... Can be caused by a single TEMP-intensive query, and also by a parallel query whose parallel query slaves are consuming too much TEMP in total. To find the alert timestamps: select originating_timestamp, module_id, message_text from v$diag_alert_ext where message_text like '%ORA%1652%' order by originating_timestamp desc ; To …
How to clean up after ORA-27477
On a 2-node RAC: WITH a AS (SELECT SYS.DBMS_QOPATCH.get_opatch_bugs bugs_output FROM DUAL) SELECT * FROM a, XMLTABLE('bugInfo/bugs/*' PASSING a.bugs_output COLUMNS bug_id number path '@id' ) x; This sql produces a list of patched bugs. At times, it fails with ORA-27477. To collect the patch information, a scheduler job is created on each of the RAC nodes, …
ORA-28040: No matching authentication protocol
Directly after startup, I received: SQL> startup ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ERROR: ORA-28040: No matching authentication protocol After some checking, I found and offending entry in $TNS_ADMIN/sqlnet-ora: SQLNET.ALLOWED_LOGON_VERSION_SERVER=12A SQLNET.ALLOWED_LOGON_VERSION_CLIENT=12A This may look ok, but it really needs to be SQLNET.ALLOWED_LOGON_VERSION_SERVER=12a SQLNET.ALLOWED_LOGON_VERSION_CLIENT=12a
Projecting AWR space usage
The AWR had to be rebuilt, and at the same time new values for the retention time were set. How big will the SYSAUX tablespace grow? After some days of typical activity, I used this SQL to project the current AWR SYSAUX space usage into the future: select q1.awr_days, q3.awr_usage_mb, q2.retention_days, awr_usage_mb * retention_days/ awr_days awr_usage_projection_mb from …
How to resolve ORA-01489: result of string concatenation is too long in a spool operation
A CSV export script similar to this one resulted in "ORA-01489: result of string concatenation is too long": set heading off feed off verify off lines 8500 trimspool on long 8500 longchunksize 8500 termout off pages 0 spool out.csv SELECT '"' || a || '";"' || b || '";"' || c || '";"' || …
How to resolve CRS-6706
After applying 12.1.0.2.170418 to the GI HOME, creating the spfile in ASM was no longer possible. The ASM alert log showed CRS-6706: Oracle Clusterware Release patch level ('2809840508') does not match Software patch level ('3372807730'). Oracle Clusterware cannot be started. After stopping HAS, the cluster stack was not startable anymore. I found the solution here: …
Procedure for sending mails with attachment from ORACLE to multiple recipients
Here's a modified piece of code, taken from https://oracle-base.com/articles/misc/email-from-oracle-plsql , which can be used to send e-mails to a list of recipients. It allows for 1 attachment. The recipient list must be separated by semicolon. CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2, p_from IN VARCHAR2, p_subject IN VARCHAR2, p_text_msg IN VARCHAR2 …
Procedure for sending mails with attachment from ORACLE to multiple recipients weiterlesen
Beware of explain plan in combination with alter session set current_schema
The situation: do an explain plan for a SQL which contains table names without schema qualifiers, which reside in a schema other than my own. To explain the SQL without any modifications, alter session set current_schema was used. connect me@db alter session set current_schema=other explain plan for select this, that from there ; select * …
Beware of explain plan in combination with alter session set current_schema weiterlesen
SQL to get the redo volume historically
Redo volume is related to archive log volume, but not identical. Here is and SQL to get the actual cumulated redo sizes from the AWR. select * from ( SELECT TO_CHAR(begin_interval_time, 'yyyymmddhh24mi') tm, s2.instance_number, (s2.value - s1.value)/1024/1024 redo_size FROM dba_hist_sysstat s1 , dba_hist_sysstat s2, dba_hist_snapshot sn WHERE s1.stat_name = 'redo size' and s2.stat_name = 'redo …
