Convert CLOB to BLOB

declare
  l_blob        blob;
  l_amt         integer := dbms_lob.lobmaxsize;
  l_dest_offset integer := 1;
  l_src_offset  integer := 1;
  l_csid        integer := dbms_lob.default_csid;
  l_ctx         integer := dbms_lob.default_lang_ctx;
  l_warn        integer;
begin
    DBMS_LOB.CREATETEMPORARY(l_blob, TRUE);
    for rrr in (select stage_data from HR.CLOB_STAGE)
        loop
        DBMS_LOB.convertToBlob(l_blob,
                           rrr.stage_data,
                           l_amt,
                           l_dest_offset,
                           l_src_offset,
                           l_csid,
                           l_ctx,
                           l_warn );
            insert into HR.BLOB_STAGE (stage_data) values (l_blob);
        end loop;
        commit;
end;
/

Select numbers using conversional function

Problem statement: From a given set in a column of a character data type, select only rows containing numeric values.

SQL> create table t1 ( c1 varchar2(100));

Table created.

SQL> insert into t1 (c1) values ('123');

1 row created.

SQL> insert into t1 (c1) values (null);

1 row created.

SQL> insert into t1 (c1) values ('$$$');

1 row created.

SQL> commit;

Commit complete.

Option 1. Conversional functions

From Oracle version 12.2 there are functions CAST or TO_NUMBER with default value on conversion error

SQL> select cast(c1 as number default 0 on conversion error) from t1
  2  where cast(c1 as number default 0 on conversion error) <> 0;

CAST(C1ASNUMBERDEFAULT0ONCONVERSIONERROR)
-----------------------------------------
                                      123

Option 2. VALIDATE_CONVERSION function

Even more easier, for select / validate only numbers. The function returns 1 if conversion is successful or 0 if it failed.

SQL> select c1 from t1 where  validate_conversion(c1 as number) = 1;

C1
----------
123	

Inspired by article Conversion Function Enhancements in Oracle Database 12c Release 2

https://oracle-base.com/articles/12c/conversion-function-enhancements-12cr2

A quick note on Windows shell scripting

Inside the cmd script, you can get the file name and full path, it depends on what will be placed between it possible to get a file name, and full path, it depends of what will be placed between “% ~ ” and ‘0’

Available options:

d — drive
p — path
n — file name
x — extension
f — full path

For example, inside c:\tmp\spell.bat, %~nx0 gives “spell.bat”, and %~dpnx0 gives “c:\tmp\spell.bat”. Note, that all parts are in canonical order, so even %~xnpd0, gives “c:\tmp\my_spell.bat”

Origin from StackOverflow.com

Functions and procedures in the WITH Clause part#2

See Functions and procedures in the WITH Clause part#1 here

If required to return both values before and after update rows in a single SELECT statement, that could be achieved by an PL/SQL function in the WITH clause. The example below updates one line by adding a number (2)

SQL> CREATE TABLE a_test AS SELECT 1 x FROM DUAL;

Table created.

WITH FUNCTION with_function(p_val IN NUMBER) RETURN NUMBER IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  o_val number;
  BEGIN
   execute immediate 'update a_test set x = x + :numval
    where rownum = 1 returning x into :numval2'
    using in p_val, out o_val;
   commit;
    RETURN o_val;
  END;
SELECT with_function(2) new_value, x old_value FROM a_test;

ORA-00054 and look who attempts to hold my objects?

Oracle provides various methods for maintaining database tables and indexes. In most cases, the latest versions of Oracle provide syntax for online operations. However, in some cases it is necessary to perform offline maintenance operations that require exclusive access to a table or index. When doing so, an ORA-00054 error could occur.

Consider an example of reorganizing the index HR.EMP_EMP_PK.

Install MySQL database on Debian 10

First, go here and look for the most recent version of mysql-apt-config _ * _ all.deb. I had this file mysql-apt-config_0.8.17-1_all.deb. Download and install:

wget https://repo.mysql.com/mysql-apt-config_0.8.17-1_all.deb

sudo dpkg -i mysql-apt-config_0.8.17-1_all.deb

During the installation process, you can select the desired MySQL version, by default it was the 8th version. If you required to change it again, then the command will help:

sudo dpkg-reconfigure mysql-apt-config

Next installation:

sudo apt-get update

sudo apt install mysql-server mysql-client

Using subqueries

A subquery is quite intuitive operation and it is easy to understand its meaning. In a common, top-level statement is called an outer query and a query nested within another SQL statement is called a subquery.

Nested Subquery

A subquery in the FROM clause of a SELECT statement is also called an inline view. Subqueries are nested when they appear in the WHERE clause of the parent statement. When Oracle Database evaluates a statement with a nested subquery, it must evaluate the subquery portion multiple times and may overlook some efficient access paths or joins. You can nest up to 255 levels of subqueries in the a nested subquery. A nested subquery conceptually is evaluated once for each row processed by the parent statement.

Correlated Subquery

Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement one or more levels above the subquery or nested subquery.

Table alias

Table alias (t_alias) specified a correlation name (alias) followed by a period and the asterisk to select all columns from the object with that correlation name specified in the FROM clause of the same subquery. The object can be a table, view, materialized view, or subquery.

Basic Recommendations

If columns in a subquery have the same name as columns in the containing statement, then you must prefix any reference to the column of the table from the containing statement with the table name or alias. To make your statements easier to read, always qualify the columns in a subquery with the name or alias of the table, view, or materialized view.

Program and service_name using the ASH data

If you have a Diagnostic and Tuning Pack or an Enterprise license, you can track sessions activity in DB with Active Session History (ASH) data. Here are examples of aggregate SQL queries on ASH views, to use it replace sql_id, dbid, begin_snap_id and and_snap_id proper values.

SELECT A.INST_ID, S.NAME SERVICE_NAME, A.PROGRAM, COUNT(*) FROM GV$ACTIVE_SESSION_HISTORY A, GV$SERVICES S WHERE A.INST_ID = S.INST_ID AND A.SERVICE_HASH = S.NAME_HASH
AND A.SQL_ID='SQL_ID'
GROUP BY A.INST_ID, S.NAME, A.PROGRAM;

SELECT AH.INSTANCE_NUMBER, S.NAME SERVICE_NAME, AH.PROGRAM, COUNT(*) FROM DBA_HIST_ACTIVE_SESS_HISTORY AH, GV$SERVICES S WHERE AH.DBID = DBID#
AND AH.SNAP_ID BETWEEN BEGIN_SNAP_ID# AND END_SNAP_ID#
AND AH.INSTANCE_NUMBER = S.INST_ID AND AH.SERVICE_HASH = S.NAME_HASH
AND AH.SQL_ID='SQL_ID'
GROUP BY AH.INSTANCE_NUMBER, S.NAME, AH.PROGRAM;

Optimizer Statistics part #2

See Statistics gathering part #1 here

The more I think about optimizer statistics, the better I imagine their critical role to select the best execution plan for an SQL query. Statistics provide details about the database its object used by the optimizer, according to documentation. Categories include table statistics such as numbers of rows, index statistics such as B-tree levels, system statistics such as CPU and I/O performance, and column statistics such as number of nulls.

Without them, the optimizer will assume uniform data value distribution and will estimate row source size with no correlation to data, stored in a database. Hence keeping optimizer statistics up-to-date guarantees that they accurately represent the data that’s stored in the database

UNPIVOT query for High Version Count

All SQLs are implicitly sharable. After entering the SQL statement, Oracle Database will calculate a hash. A hash key usable to locate the cursor in the library cache. A cursor is the instance of query or dml in the library cache. If the SQL statement carries particular conditions, Oracle creates a version of the cursor with those conditions. A version of a cursor is also known as child cursor

According to Oracle Support Note Troubleshooting: High Version Count Issues (Doc ID 296377.1), there is no definitive definition of what a ‘High’ number of versions for a particular cursor is. Different systems may be able to deal with different ranges of versions. However, AWR reports start reporting versions over 20 for a particular cursor and so that is as good an indicator of a potential problem as any

Version count for a particular SQL could be tracked in GV$SQL_SHARED_CURSOR It explains why a particular child cursor is not shared with existing child cursors. Each column identifies a specific reason why the cursor cannot be shared

There are 64 columns with reasons in Oracle 18c and 19c, and it is convenient to use UNPIVOT to aggregate them and retrieving the most common of them for a particular cursor:

SQL> SELECT INST_ID, SQL_ID, REASON_NAME, REASON_VALUE, COUNT(*) FROM (
 SELECT * FROM GV$SQL_SHARED_CURSOR
 UNPIVOT INCLUDE NULLS
 (REASON_VALUE
     FOR REASON_NAME IN (ACL_MISMATCH AS 'ACL_MISMATCH',ANYDATA_TRANSFORMATION AS 'ANYDATA_TRANSFORMATION',AUTH_CHECK_MISMATCH AS 'AUTH_CHECK_MISMATCH',BIND_EQUIV_FAILURE AS 'BIND_EQUIV_FAILURE',BIND_LENGTH_UPGRADEABLE AS 'BIND_LENGTH_UPGRADEABLE',BIND_MISMATCH AS 'BIND_MISMATCH',BIND_PEEKED_PQ_MISMATCH AS 'BIND_PEEKED_PQ_MISMATCH',BIND_UACS_DIFF AS 'BIND_UACS_DIFF',BUFFERED_DML_MISMATCH AS 'BUFFERED_DML_MISMATCH',CROSSEDITION_TRIGGER_MISMATCH AS 'CROSSEDITION_TRIGGER_MISMATCH',CURSOR_PARTS_MISMATCH AS 'CURSOR_PARTS_MISMATCH',DESCRIBE_MISMATCH AS 'DESCRIBE_MISMATCH',DIFF_CALL_DURN AS 'DIFF_CALL_DURN',DIFFERENT_LONG_LENGTH AS 'DIFFERENT_LONG_LENGTH',EDITION_MISMATCH AS 'EDITION_MISMATCH',EXPLAIN_PLAN_CURSOR AS 'EXPLAIN_PLAN_CURSOR',FLASHBACK_ARCHIVE_MISMATCH AS 'FLASHBACK_ARCHIVE_MISMATCH',FLASHBACK_CURSOR AS 'FLASHBACK_CURSOR',FLASHBACK_TABLE_MISMATCH AS 'FLASHBACK_TABLE_MISMATCH',FORCE_HARD_PARSE AS 'FORCE_HARD_PARSE',HASH_MATCH_FAILED AS 'HASH_MATCH_FAILED',INCOMP_LTRL_MISMATCH AS 'INCOMP_LTRL_MISMATCH',INST_DRTLD_MISMATCH AS 'INST_DRTLD_MISMATCH',INSUFF_PRIVS AS 'INSUFF_PRIVS',INSUFF_PRIVS_REM AS 'INSUFF_PRIVS_REM',LANGUAGE_MISMATCH AS 'LANGUAGE_MISMATCH',LITERAL_MISMATCH AS 'LITERAL_MISMATCH',LITREP_COMP_MISMATCH AS 'LITREP_COMP_MISMATCH',LOAD_OPTIMIZER_STATS AS 'LOAD_OPTIMIZER_STATS',LOAD_RUNTIME_HEAP_FAILED AS 'LOAD_RUNTIME_HEAP_FAILED',LOCK_USER_SCHEMA_FAILED AS 'LOCK_USER_SCHEMA_FAILED',LOGICAL_STANDBY_APPLY AS 'LOGICAL_STANDBY_APPLY',LOGMINER_SESSION_MISMATCH AS 'LOGMINER_SESSION_MISMATCH',MULTI_PX_MISMATCH AS 'MULTI_PX_MISMATCH',MV_QUERY_GEN_MISMATCH AS 'MV_QUERY_GEN_MISMATCH',MV_REWRITE_MISMATCH AS 'MV_REWRITE_MISMATCH',MV_STALEOBJ_MISMATCH AS 'MV_STALEOBJ_MISMATCH',NO_TRIGGER_MISMATCH AS 'NO_TRIGGER_MISMATCH',OPTIMIZER_MISMATCH AS 'OPTIMIZER_MISMATCH',OPTIMIZER_MODE_MISMATCH AS 'OPTIMIZER_MODE_MISMATCH',OUTLINE_MISMATCH AS 'OUTLINE_MISMATCH',OVERLAP_TIME_MISMATCH AS 'OVERLAP_TIME_MISMATCH',PDDL_ENV_MISMATCH AS 'PDDL_ENV_MISMATCH',PDML_ENV_MISMATCH AS 'PDML_ENV_MISMATCH',PLSQL_CMP_SWITCHS_DIFF AS 'PLSQL_CMP_SWITCHS_DIFF',PLSQL_DEBUG AS 'PLSQL_DEBUG',PQ_SLAVE_MISMATCH AS 'PQ_SLAVE_MISMATCH',PURGED_CURSOR AS 'PURGED_CURSOR',PX_MISMATCH AS 'PX_MISMATCH',REMOTE_MAPPING_MISMATCH AS 'REMOTE_MAPPING_MISMATCH',REMOTE_TRANS_MISMATCH AS 'REMOTE_TRANS_MISMATCH',ROLL_INVALID_MISMATCH AS 'ROLL_INVALID_MISMATCH',SLAVE_QC_MISMATCH AS 'SLAVE_QC_MISMATCH',SQL_TYPE_MISMATCH AS 'SQL_TYPE_MISMATCH',STATS_ROW_MISMATCH AS 'STATS_ROW_MISMATCH',STB_OBJECT_MISMATCH AS 'STB_OBJECT_MISMATCH',TOP_LEVEL_DDL_MISMATCH AS 'TOP_LEVEL_DDL_MISMATCH',TOP_LEVEL_RPI_CURSOR AS 'TOP_LEVEL_RPI_CURSOR',TRANSLATION_MISMATCH AS 'TRANSLATION_MISMATCH',TYPCHK_DEP_MISMATCH AS 'TYPCHK_DEP_MISMATCH',TYPECHECK_MISMATCH AS 'TYPECHECK_MISMATCH',UNBOUND_CURSOR AS 'UNBOUND_CURSOR',USE_FEEDBACK_STATS AS 'USE_FEEDBACK_STATS',USER_BIND_PEEK_MISMATCH AS 'USER_BIND_PEEK_MISMATCH'))
 ORDER BY 2,1)
 WHERE SQL_ID='1q1spprb9m55h'
 GROUP BY INST_ID, SQL_ID, REASON_NAME, REASON_VALUE
 HAVING REASON_VALUE='Y'
 ORDER BY COUNT(*) DESC;

INST_ID SQL_ID        REASON_NAME            REASON_VALUE COUNT(*)
------- ------------- ---------------------- ------------ --------
      1 1q1spprb9m55h PQ_SLAVE_MISMATCH      Y                 191      
      1 1q1spprb9m55h DIFF_CALL_DURN         Y                  41
      1 1q1spprb9m55h ROLL_INVALID_MISMATCH  Y                  32      
      1 1q1spprb9m55h MULTI_PX_MISMATCH      Y                  31
      1 1q1spprb9m55h LANGUAGE_MISMATCH      Y                  20
      1 1q1spprb9m55h HASH_MATCH_FAILED      Y                   5
      1 1q1spprb9m55h PURGED_CURSOR          Y                   1

7 rows selected.

Also useful reporting tool could be used for diagnosis High SQL Version Counts – Script to determine reason(s) (Doc ID 438755.1)

Detailed explanation given in the Abel Macias blog