Character set conversion

Based on this

db1>SELECT value FROM nls_database_parameters WHERE parameter='NLS_CHARACTERSET';

VALUE
----------------------------------------------------------------
WE8MSWIN1252

To return in the national character set of the database use UNISTR function:

db1>SELECT dump(UNISTR('\00E4'), 1016), UNISTR('\00E4') FROM DUAL;

DUMP(UNISTR('\00E4'),1016)               U
---------------------------------------- -
Typ=1 Len=2 CharacterSet=AL16UTF16: 0,e4 ä

To return in the database character set use TO_CHAR:

db1>SELECT DUMP(TO_CHAR(UNISTR('\00E4')), 1016), TO_CHAR(UNISTR('\00E4')) FROM DUAL;

DUMP(TO_CHAR(UNISTR('\00E4')),1016)       T
----------------------------------------- -
Typ=1 Len=1 CharacterSet=WE8MSWIN1252: e4 ä

If characters are not shown correctly in SQL*Plus use chcp and NLS_LANG as explained here

db1>host chcp
Active code page: 1252

db1>host echo %NLS_LANG%
.WE8MSWIN1252

Jenkins: fire on change of specific file in specific branch in Bitbucket

Perhaps different set of plugins can execute this task, but I’ve implemented with Generic Webhook trigger.

Final version is for Bitbucket’s Pull request merged webhook, but it also may be adopted to Repository push Webhook

In the Bitbucket webhook is configured that calls Jenkins.

In Jenkins build trigger is Generic Webhook Trigger.
Post content parameters

Variable	Expression	Comment
merge_commit_hash	$.pullRequest.properties.mergeCommit.id	For pull request merge
pull_request_branch	$.pullRequest.toRef.id	For pull request merge
commit_hash	$.changes[0].toHash	For repository push
push_branch	$.changes[0].refId	For repository push

Filter in Jenkins’ Generic Webhook Trigger to process only changes in specific branch:

Expression	Text
refs/heads/dev	$push_branch$pull_request_branch

Example of simple Execute shell step :

#!/bin/bash

echo "Looking for $FILE_NAME in Commit $commit_hash $merge_commit_hash"
# list of changed files for a commit
#filelist=$(git diff-tree --no-commit-id --name-only -r $commit_hash)
#commit is merge commit - must use different tactics
filelist=$(git log -m -1 --name-only --pretty="format:" $merge_commit_hash)
if [[ $filelist == *"$FILE_NAME"* ]]; then
$ process file
else
	echo "Skip"
fi

Find all utl_ packages in source code

Using grep utility

grep -rohP "(^|\W)\Kutl_\w*\." . | sort -u

Database CI pipeline idea

1. Prod release
2. Remote online clone of Prod database using this technic. See the link above to the list of restrictions, but most influenceable are: source database must be in archivelog mode and have local undo (otherwise it should be in read-only mode), same endianness in source and target. The database link should exists from target to source.
3. Keep created clone as golden source. Use PDB snapshot copy to create databases for testing
   – parameter clonedb=true should be set
   – filesystem should supports sparse files (ext4, NTFS, … are ok)
   – as spare files are used, whole process is very fast and don’t consumes a lot of space

UTL_FILE_DIR AND ORA-29280

utl_file_dir parameter was deprecated at least in 12c and completely removed at least in 19c.

For UTL_FILE procedures like fopen there are 2 different ways to specify directory:

1. Using Oracle DIRECTORY object
2. Using string with full path. This approach work if and only if the specified directory is present in the parameter UTL_FILE_DIR, otherwise it raises ORA-29280 (see example below). This way is not working in 19c as UTL_FILE_DIR does not exists anymore
   The second approach is work regardless UTL_FILE_DIR and this is the only way in Oracle 19c

SELECT NAME, VALUE FROM v$parameter WHERE NAME = 'utl_file_dir';
NAME            VALUE
utl_file_dir    c:\temp\utl_file_dir

======================================================
Using folder name with full path.
UTL_FILE_DIR does NOT contain folder - NOT WORKING!!!
======================================================
DECLARE
   v_fp           sys.utl_file.file_type;
BEGIN
   v_fp := sys.utl_file.fopen('c:\temp\non_utl_file_dir', 'test_file.csv', 'w', 32000);
   utl_file.fclose(v_fp);
END;
/
DECLARE
   v_fp           sys.utl_file.file_type;
BEGIN
   v_fp := sys.utl_file.fopen('c:\temp\non_utl_file_dir', 'test_file.csv', 'w', 32000);
   utl_file.fclose(v_fp);
END;
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at line 4

======================================================
USING DIRECTORY OBJECT - WORKING
======================================================
CREATE OR REPLACE directory REPORT_DIRECTORY AS 'c:\temp\non_utl_file_dir';
Directory created
DECLARE
   v_fp           sys.utl_file.file_type;
BEGIN
   v_fp := sys.utl_file.fopen('REPORT_DIRECTORY', 'test_file.csv', 'w',32000);
   utl_file.fclose(v_fp);
END;
/
PL/SQL procedure successfully completed

======================================================
Using folder name with full path.
UTL_FILE_DIR contains folder - WORKING
======================================================
DECLARE
   v_fp           sys.utl_file.file_type;
BEGIN
   v_fp := sys.utl_file.fopen('c:\temp\utl_file_dir', 'test_file.csv', 'w',32000);
   utl_file.fclose(v_fp);
END;
/
PL/SQL procedure successfully completed

Set ssh private key folder in GIT

Set variable HOME to the folder where .ssh folder with keys is created and windows GIT will pick up private key from this folder:

set HOME=x:\

Test connectivity on Linux

Using curl

curl -v telnet://host:port

Using netcat

nc -z -v host port

cURL file send to Oracle cloud

curl --write-out '%{time_total}' -X POST --data-binary "@5M.csv" -H "Content-Type:text/csv" --user <user>:<password> "https://....adb.us-ashburn-1.oraclecloudapps.com/ords/<user>/huge_csv/batchload?batchRows=5000&errorsMax=20"

Taken from here

USE_SHARED_SOCKET

For old versions as pointed in the note Troubleshooting Connectivity Issues From a Remote Client to a Database (Doc ID 271852.1)

For 9.2 and below, if the Operating System is Microsoft Windows NT,
and you are using the dedicated server connection, a spawned Oracle
thread communicates with the client through a randomly allocated port
(called a REDIRECT). To force the oracle process to communicate only
through the port in which Listener is listening, set the key,
USE_SHARED_SOCKET = TRUE, in the Windows Registry
NOTE:
This is no longer the case with 10g and up as a REDIRECT does not take place for DEDICATED connections.

Или, если верить ноте 124140.1, начиная с версии 10.2

This technique only involves the version 8.1 to 10.1 Oracle Database versions. From 10.2 onward the default changed to allow port sharing.

Причина этого

Microsoft WINSOCK V1.1 API did not allow one process to pass a TCP socket to another process and as a result did not allow port sharing like on UNIX systems

Знание уже абсолютно бесполезное, ибо:

1. С версии 10.2 USE_SHARED_SOCKET=TRUE в реестре или переменной окружения
2. Уже 100 лет не видел Oracle на Windows за исключением собственного ноута

Use BLOB instead of CLOB for JSON

Advice, I’ve met couple of times:

Oracle performance tip: JSON is always UTF8 -Use ‘al32utf8’ character set if possible
Use BLOB instead of CLOB CLOB stores data as UCS2/UTF16
Use lob data api to insert/fetch data - getBytes/setBytes instead of - getBlob/setBlob!

login.sql is not executed from SQLPATH

A lot has been written, that login.sql is no more executed from current folder, but if SQLPATH variable has been set in registry or in env variable and login.sql is not executed (but other scripts do) - patch from 2274608.1 have to be installed.
This is applicable to at least Oracle 12.2 clients

SQL json_object VS PL SQL json_object_t

Comparing performance for simple json generation in Oracle 12.2
SQL’s json_object performs 1.5 times faster.
Result

SQL json_object: 41
PL/SQL JSON_OBJECT_T: 64

Test case

DECLARE
   l_result CLOB;
  
   l_cnt NUMBER := 10000;
   l_start_time NUMBER;
   
   l_key VARCHAR2(100) := 'testkey';
   l_val VARCHAR2(32767) := lpad('x', 5000, 'x');
   
   FUNCTION simple_json(p_name IN VARCHAR2, p_value IN CLOB) RETURN CLOB IS
      l_result JSON_OBJECT_T := JSON_OBJECT_T;
   BEGIN
      l_result.put(p_name, p_value);
      RETURN l_result.to_clob;
   END;
BEGIN
   l_start_time := dbms_utility.get_time;
   FOR i IN 1 .. l_cnt LOOP
      l_val := lpad('x', 5000, 'x') || systimestamp;
      SELECT json_object(key l_key VALUE l_val RETURNING CLOB) json INTO l_result FROM dual;  
      --dbms_output.put_line(l_result);     
   END LOOP;
   dbms_output.put_line('SQL json_object: ' || (dbms_utility.get_time - l_start_time)); 

   l_start_time := dbms_utility.get_time;
   FOR i IN 1 .. l_cnt LOOP
      l_val := lpad('x', 5000, 'x') || systimestamp;
      l_result := simple_json(l_key, l_val);
      --dbms_output.put_line(l_result);     
   END LOOP;
   dbms_output.put_line('PL/SQL JSON_OBJECT_T: ' || (dbms_utility.get_time - l_start_time)); 
END;

Add domain user to ORA_DBA group

Right after Oracle 12c installation and database creation I was able to connect to the database with username and password

sqlplus sys/pass as sysdba
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

but unable to connect with / as sysdba

sqlplus / as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied

Connection from local administrator account worked perfectly without user name and password, so I’ve checked ORA_DBA group with lusrmgr.msc console and found that I have to add currently connected domain user to ORA_DBA group.
But Add user dialog doesn’t allow search and add domain users. Solution for this is command line under Local Administrator account

net localgroup ORA_DBA /add domain\USERNAME

Proxy users

1. If we don’t want to share application user password to all the users who need to connect to our schema.
2. For better audit
   proxy users can be created

CREATE USER app_user_unknown_passwd IDENTIFIED BY unknown_password;
GRANT CREATE SESSION TO app_user_unknown_passwd;

CREATE USER connect_user_known_password IDENTIFIED BY known_password;
GRANT CREATE SESSION TO connect_user_known_password;

ALTER USER app_user_unknown_passwd GRANT CONNECT THROUGH connect_user_known_password;

Check through proxy_users view

SELECT * FROM proxy_users;

PROXY	CLIENT	AUTHENTICATION	FLAGS
CONNECT_USER_KNOWN_PASSWORD	APP_USER_UNKNOWN_PASSWD	NO	PROXY MAY ACTIVATE ALL CLIENT ROLES

Try to connect from SQL*Plus and check some context parameters

sqlplus connect_user_known_password[app_user_unknown_passwd]/known_password@db122

SQL> select USER, 
  sys_context('USERENV','PROXY_USER'), 
  sys_context('USERENV','CURRENT_SCHEMA') 
from dual;

USER
--------------------------------------------------------------------------------
SYS_CONTEXT('USERENV','PROXY_USER')
--------------------------------------------------------------------------------
SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
APP_USER_UNKNOWN_PASSWD
CONNECT_USER_KNOWN_PASSWORD
APP_USER_UNKNOWN_PASSWD

ORA-28040 using 32-bit PL SQL Developer

Given:

• 64-bit Oracle database 12.2.0.1 installed locally
• 32-bit PL/SQL Developer
• 32-bit Oracle client 12.2.0.1 (as 32-bit PL/SQL developer can’t use 64-bit client / JDBC)

When:
Trying to connect from 32-bit PL/SQL Developer get an error ORA-28040: No matching authentication protocol
Connection from 64-bit PL/SQL Developer is OK

Solution:
Add
SQLNET.ALLOWED_LOGON_VERSION_SERVER=12
to server sqlnet.ora file (not client or TNS_ADMIN file)

Batched update

If you UNDO is not big enough or you want to split transaction here is the simple script for batched update

-- Execute batched update 
-- UPDATE table_name SET set_parameter;
--
-- USAGE
-- 1. Set l_batch_size
-- 2. Set select WHERE clause
-- 3. Set update's SET clause
-- 4. invoke process_table(table_name) for which this update statement should be executed
DECLARE 
  TYPE tpt_rowid IS TABLE OF ROWID;
 
  --1.Batch size
  g_bacth_size NUMBER := 100000;
  
  --2. SELECT rowid FROM table_name t WHERE ...value of variable below...
  l_select_where_clause VARCHAR2(32000) := q'[column_a = 111]';
  
  --3. UPDATE table_name SET ...value of variable below... WHERE rowid=rid
  l_update_set_clause VARCHAR2(32000) := q'[column_a = 333]';
  
  PROCEDURE process_table(p_table_name VARCHAR2) IS

    l_rids tpt_rowid;
    cur SYS_REFCURSOR;
  BEGIN
    OPEN cur FOR 'SELECT /*+ PARALLEL(8)*/rowid FROM ' || p_table_name || ' t WHERE ' || l_select_where_clause;
    LOOP
      
      FETCH cur BULK COLLECT INTO l_rids LIMIT g_bacth_size;
      dbms_output.put_line('Fetched: ' || l_rids.count);
      EXIT WHEN l_rids.count = 0;
      
      FORALL i IN l_rids.FIRST .. l_rids.LAST
        EXECUTE IMMEDIATE 
          'UPDATE ' || p_table_name || ' t' ||
          ' SET ' || l_update_set_clause || 
          ' WHERE rowid = :1' 
          USING l_rids(i);
      dbms_output.put_line('Updated: ' || SQL%ROWCOUNT); 
      COMMIT;
    END LOOP;
  END;
BEGIN
  --4. Invoke for all tables
  process_table('BB');  
END;
/

Java: words count

Simple app to count number of words in the file

import java.io.IOException;  
import java.nio.file.Files;  
import java.nio.file.Paths;  
import java.util.*;  
import java.util.stream.Collectors;  
  
class Scratch {  
    public static void main(String[] args) throws IOException {  
        Map<String, Long> words =  
                Files.lines(Paths.get("American Beauty-English.srt"))  
                        .filter(str -> !str.trim().isBlank())  
                        .filter(str -> !str.matches("^\\d+.*"))  
                        .map(str -> str.split("[^\\w']"))  
                        .flatMap(x -> Arrays.stream(x))  
                        .filter(str -> !str.trim().isBlank())  
                        .collect(Collectors.groupingBy(  
                                s -> s.toLowerCase(),  
                                Collectors.counting()));  
  
        System.out.println("Words count: " + words.keySet().size());  
        System.out.println("20 most popular words: ");  
        PriorityQueue<Word> sortedWords = new PriorityQueue<>(Collections.reverseOrder());  
        words.forEach(  
                (k, v) -> sortedWords.add(new Word(v, k))  
        );  
  
        sortedWords.stream()  
                .limit(20)  
                .forEach(System.out::println);  
    }  
  
    static class Word implements Comparable<Word> {  
        public Word(long cnt, String word) {  
            this.cnt = cnt;  
            this.word = word;  
        }  
  
        public long getCnt() {  
            return cnt;  
        }  
  
        public String getWord() {  
            return word;  
        }  
  
        private long cnt;  
        private String word;  
  
        @Override  
  public int compareTo(Word word) {  
            return Comparator.comparing(Word::getCnt).thenComparing(Word::getWord).compare(this, word);  
        }  
  
        @Override  
  public String toString() {  
            return String.format("'%s' used %d times", word, cnt);  
        }  
    }  
}

PDB point in time recovery by Franck Pachot

Task
Source database has multiply PDBs. User wants to restore in specific point in time only one of them
Here is the Franck’s answer and script

There is no way other than point-in-time duplicate to an auxiliary CDB and unplug to put wherever you want. You can skip pluggable databases so that you restore only CDB$ROOT and your PDB. This process is automated by RMAN when restoring in-place (a temporary auxiliary database is created for CDB$ROOT) but not for your case.
Here is a script I used to demo it once on a lab:

---# create an init.ora  
cat > /tmp/CDB_TEMP.ora <<'CAT'  
db_name=CDB_TEMP  
# I set a temporary domain to avoid any conflict  
db_domain=[temp.dbi-services.com](http://temp.dbi-services.com/)  
enable_pluggable_database=true  
compatible=19.0.0.0  
db_block_size=8192  
db_files=200  
sga_target=1024M  
processes=150  
db_create_file_dest=/u02/oradata  
db_recovery_file_dest=/u90/fast_recovery_area  
db_recovery_file_dest_size=1G  
#_clone_one_pdb_recovery=true # this is used by automated in-place PDBPITR so it may be cool ;)  
#_system_trig_enabled=false  
CAT  
---# Start the instance  
ORACLE_SID=CDB_TEMP sqlplus / as sysdba <<<"startup force nomount pfile='/tmp/CDB_TEMP.ora';"  
---## RMAN duplicate  
---# RMAN connect to target and auxiliary  
ORACLE_SID=CDB_TEMP rman  
set echo on  
connect target sys/manager@//localhost:1521/[CDB1.it.dbi-services.com](http://cdb1.it.dbi-services.com/)  
alter system archive log current  
/  
connect auxiliary /  
---# list PDBs to exclude (remove the one you want to keep!)  
select listagg(pdb_name,',')within group(order by pdb_name) from dba_pdbs  
/  
---# duplicate skip PDBs(use no-open just to show funny name)  
duplicate database CDB1_SITE1 to CDB_TEMP skip pluggable database CDB1PDB02,CDB1PDB03 until restore point 'DEMO_OOP_PDB_PITR' noopen;  
quit;  
---# Open it (because we did a noopen duplicate)  
ORACLE_SID=CDB_TEMP sqlcl / as sysdba  
show pdbs  
alter database open resetlogs  
/  
show pdbs  

  

Cleanup:

  

---# remove the temporary CDB  
ORACLE_SID=CDB_TEMP rman target /  
startup dba mount force;  
drop database noprompt;  
quit;  
rm -rf /u??/?*/CDB_TEMP $ORACLE_BASE/diag/rdbms/cdb_temp $ORACLE_HOME/rdbms/log/cdb_temp* $ORACLE_HOME/rdbms/audit/CDB_TEMP* $ORACLE_HOME/dbs/?*CDB_TEMP* $ORACLE_BASE/admin/CDB_TEMP  

I doubt there will ever be another solution because you need a CDB$ROOT at same point-in-time in order to open the PDB and unplug it.

Note that, as an alternative, if you have a standby you may stop apply, flashback the pdb, convert to snapshot standby, clone the pdb, and get all back to physical standby.

DOP downgrade

Для версии 12 (по крайней мере 2) как написано тут DOP downgrade reason можно посмотреть в SQL Monitor в Other column for PX coordinator shows the downgrade reason, а сами причины расшифровать запросом

select qksxareasons, indx 
from x$qksxa_reason 
where qksxareasons like '%DOP downgrade%';

DOP downgrade due to adaptive DOP 351
DOP downgrade due to resource managermax DOP 352
DOP downgrade due to insufficient number of processes 353
DOP downgrade because slaves failed to join 354

В 11.2 такой таблички я не нашел, поэтому там придется пользоваться старым добрым

alter session set "_px_trace"=high,all;

который прекрасно описан тут

Execute as sysdba

One can use os_command package to create and execute script from database server as sysdba user. Oracle white paper about this package
Steps to implement:

1. Create directory to store file and grant permission to user

create or replace directory &tmpdir as '&tmppath';
grant read, write, execute on directory &tmpdir to &username;

2. Grant execute permissions

begin
  dbms_java.grant_permission
      (upper('&username'),
       'java.io.FilePermission',
       '<<ALL FILES>>',
       'read,write,execute');
  dbms_java.grant_permission
      (upper('&username'),
       'java.lang.RuntimePermission',
       '*',
       'writeFileDescriptor');
end;
/

3. Write file content with SQL or PL/SQL commands

--run sqlplus script
begin
  lob_writer_plsql.write_clob(
    '&tmpdir', '&scriptname',
q'[#!/bin/bash

ORACLE_SID=&dbname
ORACLE_HOME=/data/oracle/product/&dbver.&subver
PATH=/bin:/sbin:/usr/bin:${PATH}
export ORACLE_SID ORACLE_HOME

$ORACLE_HOME/bin/sqlplus -l -s / as sysdba <<!!!
--WRITE HERE WHAT TO DO

--example
SELECT * FROM sys.metaview\$;

--example (notice how to wrap handle "/" char)
BEGIN
  NULL;
END;
]'||q'[/

exit
!!!

exit 0
]'
  );
end;
/

&dbver and &subver can be extracted by query

column dbver new_value dbver
select regexp_substr(banner, 'Release (\d+.\d+.\d+.\d+).\d+', 1, 1, '', 1) dbver
from v$version
where banner like 'Oracle Database%';

4. Execute script

declare
  c clob;
begin
  c := os_command.exec_CLOB('chmod 755 &tmppath/&scriptname');
  c := os_command.exec_CLOB('&tmppath/&scriptname');
  dbms_output.put_line(c);
  c := os_command.exec_CLOB('rm &tmppath/&scriptname');
end;
/