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 ORA-01489: result of string concatenation is too long in a spool operation weiterlesen

Advertisements

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: … How to resolve CRS-6706 weiterlesen

Procedure for sending mails with attachment 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 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 … SQL to get the redo volume historically weiterlesen

How to estimate the parallel degree for historical SQL

In analysis of SQL stats,  execution times are cumulated for all parallel servers. This makes it difficult to derive the correct execution times from the AWR. Create some test data: The test script:   Output:   Though the SQL runtime was 1,27s, the accumulated elapsed time in v$sqlstats is 17,07s, due to the fact that all parallel … How to estimate the parallel degree for historical SQL weiterlesen