Unique Columns For V$Sql

Published on: 2022-11-11 by Mustafa Comment

Hi,

Until yesterday I was saying SQL_ID and CHILD_NUMBER is enough to get a sql child cursor from V$SQL and today I know I was wrong.

Probably many times you needed the sql statement that a session is running. To do that you query V$session and find the session information and then use SQL_ID column to reach out V$Sql. if you write a query like this:

select * 
from v$session ses
       join v$sql sql on ses.sql_id = sql.sql_id and
--where username = :some_user -- you filter
;

select *

from v$session ses

join v$sql sql on ses.sql_id = sql.sql_id and

--where username = :some_user -- you filter

;

you can write any filter you want here but you will immediately realized that some session rows are multiplied. Why? Because you didn’t use “CHILD_NUMBER” while reaching v$sql. V$SQL is not main data source for unique sql statements in your database. V$SQLAREA is. So instead of v$sql, you can use v$sqlarea in this query but if you need specific information about the exact sql cursor that session is currently running then you must use v$sql.

Note: I will not go deep Parent-Child cursors here. basically, every sql statements has child cursors. they all same sql but for some reason they have different properties and your session is running a “child cursor”.

so your query should be like this:

select * 
from v$session ses
       join v$sql sql on ses.sql_id = sql.sql_id and
                         ses.sql_child_number = sql.child_number
--where username = :some_user -- you filter
;

select *

from v$session ses

join v$sql sql on ses.sql_id = sql.sql_id and

ses.sql_child_number = sql.child_number

--where username = :some_user -- you filter

;

and now you reached to exact correct sql child cursor that is running for that session. right? well, I did know so but I find out, it is not! On one of my clients system, I discovered that this query returns 2 rows from v$sql which means for same SQL_ID and CHILD_NUMBER there are 2 rows in v$sql. I couldn’t find anything on docs or google (maybe couldn’t search enough) and started to dig about this sql statements.

here is the necessary information about this sql:

select Address, child_address, hash_value, sql_id, child_number,PLAN_HASH_VALUE,FULL_PLAN_HASH_VALUE ,IS_ROLLING_INVALID, IS_ROLLING_REFRESH_INVALID
from v$sql
where sql_id = '6r317atx6ny3u' and child_number = 469;

ADDRESS          CHILD_ADDRESS    HASH_VALUE SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE I I
---------------- ---------------- ---------- ------------- ------------ --------------- -------------------- - -
00000001AAFF9FF0 00000000C3F40750 2053771386 6r317atx6ny3u          469      3635437843             11086287 N N
00000001AAD5C000 0000000139C4E8C0 2053771386 6r317atx6ny3u          469      3635437843             11086287 N N

select Address, child_address, hash_value, sql_id, child_number,PLAN_HASH_VALUE,FULL_PLAN_HASH_VALUE ,IS_ROLLING_INVALID, IS_ROLLING_REFRESH_INVALID

from v$sql

where sql_id = '6r317atx6ny3u' and child_number = 469;

ADDRESS CHILD_ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE I I

---------------- ---------------- ---------- ------------- ------------ --------------- -------------------- - -

00000001AAFF9FF0 00000000C3F40750 2053771386 6r317atx6ny3u 469 3635437843 11086287 N N

00000001AAD5C000 0000000139C4E8C0 2053771386 6r317atx6ny3u 469 3635437843 11086287 N N

almost everything is same but ADDRESS and CHILD_ADDRESS. CHILD_ADDRESS is the memory address that this cursor resides on so it must be different obviously. Only thing that create this difference is ADDRESS column. ADDRESS column in V$sql is the address of the PARENT cursor which is in V$SQLAREA view. So let’s check this view:

Select Address, Hash_Value, Sql_Id, Plan_Hash_Value,Full_Plan_Hash_Value
From V$sqlarea
Where Address In ('00000001AAFF9FF0', '00000001AAD5C000');

ADDRESS          HASH_VALUE SQL_ID        PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE
---------------- ---------- ------------- --------------- --------------------
00000001AAD5C000 2053771386 6r317atx6ny3u      3635437843             11086287

Select Address, Hash_Value, Sql_Id, Plan_Hash_Value,Full_Plan_Hash_Value

From V$sqlarea

Where Address In ('00000001AAFF9FF0', '00000001AAD5C000');

ADDRESS HASH_VALUE SQL_ID PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE

---------------- ---------- ------------- --------------- --------------------

00000001AAD5C000 2053771386 6r317atx6ny3u 3635437843 11086287

there is only one of them exists. So some of those child cursors in v$sql are not (I am not sure the term) “valid”. they might be used before (and they were in my example) but now they are remaining of a past sql. So which child cursor in v$sql is the sql statements that session is currently running? Now we need to use ADDRESS column too. SQL_ADDRESS column in v$session is the parent address of the sql statement. So, I should add it into my queries too:

select * 
from v$session ses
       join v$sql sql on ses.sql_id           = sql.sql_id and
                         ses.sql_child_number = sql.child_number and
                         ses.sql_Address      = sql.address
--where username = :some_user -- you filter
;

-- OR for the parent cursor only

select /*+ ordered */ * 
from v$session ses
       join v$sqlarea sql on ses.sql_id           = sql.sql_id and
                             ses.sql_Address      = sql.address
--where username = :some_user -- you filter
;

select *

from v$session ses

join v$sql sql on ses.sql_id = sql.sql_id and

ses.sql_child_number = sql.child_number and

ses.sql_Address = sql.address

--where username = :some_user -- you filter

;

-- OR for the parent cursor only

select /*+ ordered */ *

from v$session ses

join v$sqlarea sql on ses.sql_id = sql.sql_id and

ses.sql_Address = sql.address

--where username = :some_user -- you filter

;

thanks for reading. comments are welcome. wish you healthy happy days.

How to Check Empty Space in a Securefile LOB

Published on: 2022-09-272022-10-12 by Mustafa Comment

Hi,

PS: please check last sql statement in this post for shorter and faster solution. first sql is for showing all information about the lob.

Yesterday, while checking segment space usage data in a client’s database and some of the LOB segments were absurdly big. So, I want to check what is in it and how much free space I can gain.

To do that, I cannot use just DBMS_LOB.GetLength function because for CLOB data type, it takes much more space than original data because of charsets (especially if you use AL32UTF8 charset) so I just want to check currently empty space in lob segment. if you need that kind of data then, DBMS_SPACE is the solution. DBMS_SPACE has a SPACE_USAGE procedure which checks a segment and return space information but starting 12c, SECUREFILE is the default lob type and for securefile you cannot use same space_usage procedure with the one you use for tables.

space_usage has overloaded versions and second and third overloaded version (order in dbms_space package from top to bottom) is for SECUREFILE lob segments.

here is a sample script I wrote to check currently empty space in securefile lobs:

Caution: I check all LOBS in the database so, you might not want to do that, just modify sql and add necessary filters.

with function f_lob_space_info(p_segment_owner varchar2, p_segment_name varchar2, p_segment_type varchar2, p_partition_name varchar2) return varchar2 as
     segment_size_blocks number; segment_size_bytes  number; 
     used_blocks number; used_bytes number; 
     expired_blocks number; expired_bytes number; 
     unexpired_blocks number; unexpired_bytes number; 
    begin
      dbms_space.space_usage(p_segment_owner, p_segment_name, p_segment_type,
                             segment_size_blocks, segment_size_bytes,
                             used_blocks, used_bytes,
                             expired_blocks, expired_bytes,
                             unexpired_blocks, unexpired_bytes,
                             p_partition_name);
      return 'segment_size_blocks:'|| segment_size_blocks || ' used_blocks:' || used_blocks || ' expired_blocks:' ||expired_blocks ||' unexpired_blocks:' || unexpired_blocks;
    end;
    get_info as (
      select f_lob_space_info(owner, segment_name, 
                              decode(segment_type, 'LOBSEGMENT', 'LOB', segment_type), 
                              partition_name) as lob_info,
             segment_type, owner, segment_name, partition_name, bytes
      from   dba_segments  
      where segment_subtype='SECUREFILE'
        and segment_type in ('LOBSEGMENT', 'LOB PARTITION') 
    ),
    parse_info as (
      select to_number(regexp_substr(lob_info, 'segment_size_blocks:([0-9]+)',1,1,'i',1)) * t.block_size /1024/1024 as segment_size_mb,
             to_number(regexp_substr(lob_info, 'used_blocks:([0-9]+)',1,1,'i',1))* t.block_size /1024/1024          as used_size_mb,
             to_number(regexp_substr(lob_info, 'expired_blocks:([0-9]+)',1,1,'i',1))* t.block_size /1024/1024       as expired_size_mb,
             g.owner, l.table_name, l.column_name, g.segment_name lob_name
      from get_info g
             join dba_lobs l on g.owner = l.owner and g.segment_name = l.segment_name
               join dba_tablespaces t on t.tablespace_name = l.tablespace_name
    )
select segment_size_mb - used_size_mb empty_size, parse_info.*
from parse_info
order by empty_size desc 
/


EMPTY_SIZE	SEGMENT_SIZE_MB	USED_SIZE_MB	EXPIRED_SIZE_MB	OWNER	TABLE_NAME		COLUMN_NAME		LOB_NAME
90.203125	120.1875	29.984375	89.390625	MDSYS	SDO_COORD_OP_PARAM_VALS	PARAM_VALUE_FILE	SYS_LOB0000063832C00006$$
48.1875		48.1875		0		47.7109375	MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
48.1875		48.1875		0		47.7109375	MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
40.1875		40.1875		0		39.71875	MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
40.1875		40.1875		0		39.71875	MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
40.1875		40.1875		0		39.71875	MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
40.1875		40.1875		0		39.71875	MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
27.7265625	32.125		4.3984375	27.0703125	SYS	WRI$_EMX_FILES		DATA			SYS_LOB0000007424C00004$$
14.640625	24.1875		9.546875	14.015625	SYS	WRI$_EMX_FILES		BINARY_DATA		SYS_LOB0000007424C00005$$
11.1171875	16.1875		5.0703125	10.5		MDSYS	SDO_XSD_TABLE		XSD_DOC			SYS_LOB0000067098C00002$$

with function f_lob_space_info(p_segment_owner varchar2, p_segment_name varchar2, p_segment_type varchar2, p_partition_name varchar2) return varchar2 as

segment_size_blocks number; segment_size_bytes number;

used_blocks number; used_bytes number;

expired_blocks number; expired_bytes number;

unexpired_blocks number; unexpired_bytes number;

begin

dbms_space.space_usage(p_segment_owner, p_segment_name, p_segment_type,

segment_size_blocks, segment_size_bytes,

used_blocks, used_bytes,

expired_blocks, expired_bytes,

unexpired_blocks, unexpired_bytes,

p_partition_name);

return 'segment_size_blocks:'|| segment_size_blocks || ' used_blocks:' || used_blocks || ' expired_blocks:' ||expired_blocks ||' unexpired_blocks:' || unexpired_blocks;

end;

get_info as (

select f_lob_space_info(owner, segment_name,

decode(segment_type, 'LOBSEGMENT', 'LOB', segment_type),

partition_name) as lob_info,

segment_type, owner, segment_name, partition_name, bytes

from dba_segments

where segment_subtype='SECUREFILE'

and segment_type in ('LOBSEGMENT', 'LOB PARTITION')

parse_info as (

select to_number(regexp_substr(lob_info, 'segment_size_blocks:([0-9]+)',1,1,'i',1)) * t.block_size /1024/1024 as segment_size_mb,

to_number(regexp_substr(lob_info, 'used_blocks:([0-9]+)',1,1,'i',1))* t.block_size /1024/1024 as used_size_mb,

to_number(regexp_substr(lob_info, 'expired_blocks:([0-9]+)',1,1,'i',1))* t.block_size /1024/1024 as expired_size_mb,

g.owner, l.table_name, l.column_name, g.segment_name lob_name

from get_info g

join dba_lobs l on g.owner = l.owner and g.segment_name = l.segment_name

join dba_tablespaces t on t.tablespace_name = l.tablespace_name

)

select segment_size_mb - used_size_mb empty_size, parse_info.*

from parse_info

order by empty_size desc

EMPTY_SIZE SEGMENT_SIZE_MB USED_SIZE_MB EXPIRED_SIZE_MB OWNER TABLE_NAME COLUMN_NAME LOB_NAME

90.203125 120.1875 29.984375 89.390625 MDSYS SDO_COORD_OP_PARAM_VALS PARAM_VALUE_FILE SYS_LOB0000063832C00006$$

48.1875 48.1875 0 47.7109375 MUSTAFA TMP DD SYS_LOB0000073457C00085$$

40.1875 40.1875 0 39.71875 MUSTAFA TMP DD SYS_LOB0000073457C00085$$

27.7265625 32.125 4.3984375 27.0703125 SYS WRI$_EMX_FILES DATA SYS_LOB0000007424C00004$$

14.640625 24.1875 9.546875 14.015625 SYS WRI$_EMX_FILES BINARY_DATA SYS_LOB0000007424C00005$$

11.1171875 16.1875 5.0703125 10.5 MDSYS SDO_XSD_TABLE XSD_DOC SYS_LOB0000067098C00002$$

first with function block is to call dbms_space.space_usage procedure and return data in string, on second with clause (get_info) call the function and get lob information, by the way SEGMENT_TYPE for a lob segment is “LOB SEGMENT” but dbms_space.space_usage does not accept it as segment_Type, you must send “LOB” instead, then parse result as columns and list SEGMENT_SIZE_MB which is total size of LOB segment size in megabytes
USED_SIZE_MB which is actual used size in megabytes
EXPIRED_SIZE_MB which is expired data size (for versioning) in megabytes.

to calculate empty space just subtract used size from segment size. this is approximately empty size in your lob segment. if you have a partitioned table then you will get result for each partition (like mustafa.tmp table in the example). I hope this script helps in your job.

EDIT:

I forgot to mentioned why lob segments have empty spaces. biggest reason is delete operation of course. if you delete a big portion of the table (or lob segment) then deleted space will be considered as empty space. So, if you delete on a table, should you shrink the lob segment or table? NO! that space will be used by the table again. if this is your standard operation then empty spaces in table/lob segment will be reused but let’s say you inserted many rows by a mistake or because of a bug in your code and then deleted those rows. if you have enough disk space for the database then there is nothing to worry about (mostly). That table will get many inserts in time and it will use that space eventually but if too much space is reserved in the table / lob segment and maybe you have a disk space issues then you can reclaim it (by shrinking or moving table/lob segment). So, this is not a regular thing.

EDIT 2:

sql above is aiming to show all information about lob like used space, total space, expired space etc. probably , you will just need to see empty space in lob. so you can use this sql statement. this will work faster because no regexp usage and parsing operations. I also subtracted “unexpired bytes” from total size since this space is still in use.

with function f_lob_free_space_info(p_segment_owner varchar2, p_segment_name varchar2, p_segment_type varchar2, p_partition_name varchar2) return varchar2 as
     segment_size_blocks number; segment_size_bytes  number; 
     used_blocks number; used_bytes number; 
     expired_blocks number; expired_bytes number; 
     unexpired_blocks number; unexpired_bytes number; 
    begin
      dbms_space.space_usage(p_segment_owner, p_segment_name, p_segment_type,
                             segment_size_blocks, segment_size_bytes,
                             used_blocks, used_bytes,
                             expired_blocks, expired_bytes,
                             unexpired_blocks, unexpired_bytes,
                             p_partition_name);
      return segment_size_bytes - used_bytes - unexpired_bytes;
    end;
    get_info as (
      select f_lob_free_space_info(s.owner, s.segment_name, 
                                   decode(s.segment_type, 'LOBSEGMENT', 'LOB', s.segment_type), 
                                   s.partition_name)/1024/1024 as potential_empty_space_in_mb,
             s.segment_type, s.owner,
             l.table_name, l.column_name, s.segment_name lob_name, s.partition_name
      from   dba_segments s
               join dba_lobs l on s.owner = l.owner and s.segment_name = l.segment_name
      where s.segment_subtype='SECUREFILE'
        and s.segment_type in ('LOBSEGMENT', 'LOB PARTITION') 
    )
select *
from get_info
order by potential_empty_space_in_mb desc 
/

EMPTY_SPACE_IN_MB	OWNER	TABLE_NAME		COLUMN_NAME		LOB_NAME
90.203125		MDSYS	SDO_COORD_OP_PARAM_VALS	PARAM_VALUE_FILE	SYS_LOB0000063832C00006$$
48.1875			MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
48.1875			MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
40.1875			MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
40.1875			MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
40.1875			MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
40.1875			MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
27.7265625		SYS	WRI$_EMX_FILES		DATA			SYS_LOB0000007424C00004$$
14.640625		SYS	WRI$_EMX_FILES		BINARY_DATA		SYS_LOB0000007424C00005$$
11.1171875		MDSYS	SDO_XSD_TABLE		XSD_DOC			SYS_LOB0000067098C00002$$

with function f_lob_free_space_info(p_segment_owner varchar2, p_segment_name varchar2, p_segment_type varchar2, p_partition_name varchar2) return varchar2 as

segment_size_blocks number; segment_size_bytes number;

used_blocks number; used_bytes number;

expired_blocks number; expired_bytes number;

unexpired_blocks number; unexpired_bytes number;

begin

dbms_space.space_usage(p_segment_owner, p_segment_name, p_segment_type,

segment_size_blocks, segment_size_bytes,

used_blocks, used_bytes,

expired_blocks, expired_bytes,

unexpired_blocks, unexpired_bytes,

p_partition_name);

return segment_size_bytes - used_bytes - unexpired_bytes;

end;

get_info as (

select f_lob_free_space_info(s.owner, s.segment_name,

decode(s.segment_type, 'LOBSEGMENT', 'LOB', s.segment_type),

s.partition_name)/1024/1024 as potential_empty_space_in_mb,

s.segment_type, s.owner,

l.table_name, l.column_name, s.segment_name lob_name, s.partition_name

from dba_segments s

join dba_lobs l on s.owner = l.owner and s.segment_name = l.segment_name

where s.segment_subtype='SECUREFILE'

and s.segment_type in ('LOBSEGMENT', 'LOB PARTITION')

)

select *

from get_info

order by potential_empty_space_in_mb desc

EMPTY_SPACE_IN_MB OWNER TABLE_NAME COLUMN_NAME LOB_NAME

90.203125 MDSYS SDO_COORD_OP_PARAM_VALS PARAM_VALUE_FILE SYS_LOB0000063832C00006$$

48.1875 MUSTAFA TMP DD SYS_LOB0000073457C00085$$

40.1875 MUSTAFA TMP DD SYS_LOB0000073457C00085$$

27.7265625 SYS WRI$_EMX_FILES DATA SYS_LOB0000007424C00004$$

14.640625 SYS WRI$_EMX_FILES BINARY_DATA SYS_LOB0000007424C00005$$

11.1171875 MDSYS SDO_XSD_TABLE XSD_DOC SYS_LOB0000067098C00002$$

thanks for reading, your comments are always welcome. wish you healthy days.

catcon.pl cheat sheet

Published on: 2022-08-262022-09-01 by Mustafa Comment

Hello,

Oracle uses a container database architecture permanently anymore, you should be familiar with “catcon.pl” perl file. Let’s say you have container database with 10 different PDBs and you need to run a script file on all of those pdbs (maybe even in root and seed too) so what you need is catcon.pl.

catcon.pl will automatically execute your script file and run it on all or specified containers. Since it is a perl file it must be called with perl binary (perl in linux, perl.exe in windows) and you can find it under $ORACLE_HOME/perl/bin directory and catcon.pl itself is under $ORACLE_HOME/rdbms/admin .

so, you have a sql file to run under the /home/oracle/scripts directory called myfile.sql. let’s see how we can execute it in all pdbs:

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -d /home/oracle/scripts -b MyScript myfile.sql

1	$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -d /home/oracle/scripts -b MyScript myfile.sql

that is it. myfile.sql file will be run in all containers (including root and seed) with the command above. Let’s dissect the command:

$ORACLE_HOME/perl/bin/perl => PERL binary to run catcon.pl
$ORACLE_HOME/rdbms/admin/catcon.pl => catcon.pl path
-d /home/oracle/scripts => directory where script is located.
-b MyScript => Prefix for the log files so you can separate them from other scripts logs.
myfile.sql => actual script name

actual script file must be the last parameter. It is that easy. in this example ORACLE_HOME and ORACLE_SID should be set before you run the command otherwise you cannot connect to the database. This is a local connection and by default it is a SYSDBA connection. So whatever you run will be run under SYS user.

To add some more functionality:

-c : list of containers that you want to execute the script:
….catcon.pl -c ‘SALES PDB1 TEST’ => execute the script only at SALES, PDB1 and TEST pdbs.
-C : list of containers that you don’t want to execute the script.
-l : log directory
-s : spool the output of script files.
-S : (capital S) don’t execute the script in root and seed. (Edit: this option also don’t set “_oracle_scripts” parameter as true, so if you are creating a user it won’t be Oracle Maintained user)
-u : specify username/password.
-z : ez-connect string
-n : number of workers. if you want to execute a script for 100 pdbs then you can increase the number of workers to execute script simultaneously on different pdbs. default is 1

Can we use catcon.pl to run a script on a remote server? Yes with a combination of -u and -z

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl \
-d /home/oracle/scripts \
-b MyScript \
-l /home/oracle/logs \
-u mustafa/mustafa \
-z mydbserver.mydomain:1521/CDBX \
-n 2 \
-c 'PDB1 PDB2 PDB3 PDB4 PDB5 PDB6 PDB7 PDB8'
myfile.sql

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl \

-d /home/oracle/scripts \

-b MyScript \

-l /home/oracle/logs \

-u mustafa/mustafa \

-z mydbserver.mydomain:1521/CDBX \

-n 2 \

-c 'PDB1 PDB2 PDB3 PDB4 PDB5 PDB6 PDB7 PDB8'

myfile.sql

run myfile.sql script under /home/oracle/scripts directory on CDBX database and only specified pdbs (PDB1…PDB8) which is on mydbserver.mydomain server using mustafa user and create 2 worker process so script will be run on 2 pdbs at a time and write log files under /home/oracle/logs.

catcon has more parameters but I just write about the ones I used most.

thanks for reading.

How to Convert LONG to Clob PART 2

Published on: 2022-05-062022-05-06 by Mustafa Comment

Hi,

I just want to write a small post today. Some time ago, I shared a way to convert long to clob in a query directly: https://mustafakalayci.me/2020/04/06/how-to-convert-a-long-column-to-clob-in-a-query/ Even if this method works, it works really slow and anything slow annoys me!

So, I want to share another method which is much much faster than Sys_dburigen function but THIS IS UNDOCUMENTED and UNSUPPORTED! It is used by internally by oracle so it works fine but you must be careful. Since, no one uses LONG column in their application (right? you don’t! please don’t!) this kind of code is handy for mostly admins because for backward compatibility data dictionary has some LONG data type columns.

What we are going to use is a LIBRARY called UTL_XML_LIB . I almost never create objects under SYS user, so I will create this code under a privileged admin user. first we must grant privileges on UTL_XML_LIB library and then create an EXTERNAL procedure in plsql.

sqlplus sys/oracle@orcl as sysdba

create user mustafa identified by mustafa;
grant dba to mustafa;

grant all on UTL_XML_LIB to mustafa;

sqlplus sys/oracle@orcl as sysdba

create user mustafa identified by mustafa;

grant dba to mustafa;

grant all on UTL_XML_LIB to mustafa;

now create external procedure:

sqlplus mustafa/mustafa@orcl

create or replace procedure mustafa.my_long2cloc (stmt    in varchar2,
                          rowid   in rowid,
                          lobloc  in out nocopy clob
                         ) is
  external
  name "kuxLong2Clob"
  language c
  library sys.utl_xml_lib
  with context
  parameters (context,
              stmt     ocistring,      
              stmt     indicator sb4,
              rowid    ocistring,      
              rowid    indicator sb4,
              lobloc   ociloblocator,  
              lobloc   indicator sb4
             );
             /

sqlplus mustafa/mustafa@orcl

create or replace procedure mustafa.my_long2cloc (stmt in varchar2,

rowid in rowid,

lobloc in out nocopy clob

) is

external

name "kuxLong2Clob"

language c

library sys.utl_xml_lib

with context

parameters (context,

stmt ocistring,

stmt indicator sb4,

rowid ocistring,

rowid indicator sb4,

lobloc ociloblocator,

lobloc indicator sb4

);

“kuxLong2Clob” is a function which returns void (basically it is a procedure) written in C by Oracle. this procedure takes a select statement for your LONG column and rowid. procedure returns CLOB data as an out parameter. I created my_long2clob procedure but to be able to call it via SQL statement, it must be a function, so:

create or replace function mustafa.f_my_long2cloc (p rowid, p_table varchar2, p_col varchar2) return clob as
  retval clob;
begin
  dbms_lob.createtemporary(retval,true);
  mustafa.my_long2cloc('select ' ||p_col ||' from ' || p_table || ' where rowid = :1', p, retval);
  return retval;
end;
/

create or replace function mustafa.f_my_long2cloc (p rowid, p_table varchar2, p_col varchar2) return clob as

retval clob;

begin

dbms_lob.createtemporary(retval,true);

mustafa.my_long2cloc('select ' ||p_col ||' from ' || p_table || ' where rowid = :1', p, retval);

return retval;

end;

to test it, I won’t create a new table with a LONG column but instead I will use one of in the data dictionary (which I use these kind of codes against). Let’s use SYS.TRIGGER$ table which is the base table for DBA_TRIGGERS view. ACTION# column is a LONG data type. of course we must first grant select privilege on SYS.TRIGGER$ to mustafa user.

sqlplus sys/oracle@orcl as sysdba

 grant select on TRIGGER$ to mustafa;

sqlplus mustafa/mustafa@orcl

select  mustafa.f_my_long2cloc(rowid, 'SYS.TRIGGER$', 'ACTION#') 
from SYS.TRIGGER$; 

...
...
...

sqlplus sys/oracle@orcl as sysdba

grant select on TRIGGER$ to mustafa;

sqlplus mustafa/mustafa@orcl

select mustafa.f_my_long2cloc(rowid, 'SYS.TRIGGER$', 'ACTION#')

from SYS.TRIGGER$;

...

I suppressed the output but here it is. really fast long to clob conversion in SQL.

as a CON, it will only work against tables not VIEWs! you cannot use it as “f_my_long2cloc(rowid, ‘DBA_TRIGGERS’, ‘TRIGGER_BODY’)”. Since it requires ROWID, you must be referring underlying table and its rowid.

beyond that, if you want to get LONG as VARCHAR2, similarly you can use “kpdbLong2Varchar2” procedure in DBMS_PDB_LIB library. as UTL_XML_LIB it is very fast. only difference is, it returns VARCHAR2 and trims the overflow part. Where is it in use? Check how DBA_VIEWS shows TEXT_VC column data 😉

I hope this helps to fight against LONG columns in your codes.

Wish you all healthy, happy days.

Tables with Memoptimize for Read (fast lookup)

Published on: 2022-02-152022-02-15 by Mustafa Comment

Hi,

I would like to share my initial thoughts about memoptimized for read (fast lookup) tables in this post. fast lookup tables came with 18c and they are here to provide faster access to a table if you are using primary key with a equality condition. this is basically single table hash cluster. instead of using primary key index, Oracle creates a hash index and searching on a hash index should be much more faster than a unique index.

first, create the test environment.

sqlplus / as sysdba

alter system set memoptimize_pool_size=200M scope=spfile;

shutdown immediate;

startup;

sqlplus / as sysdba

alter system set memoptimize_pool_size=200M scope=spfile;

shutdown immediate;

startup;

to use a fast lookup table you must set memoptimize_pool_size paramter to a number higher than 0. this is a static memory component in sga to store hash index of the table. this memory component is not resizable which means it won’t grow or become smaller with automatic shared memory management.

create a table for fast lookup:

create table tmp_optimize (
  id number primary key,
  object_name varchar2(128),
  object_type varchar2(128),
  created date)
  segment creation immediate memoptimize for read ;

insert into tmp_optimize 
  select rownum, object_name, object_type, created 
  from dba_objects d, (select * from dual connect by level < 3;

commit;

create table tmp_optimize (

id number primary key,

object_name varchar2(128),

object_type varchar2(128),

created date)

segment creation immediate memoptimize for read ;

insert into tmp_optimize

select rownum, object_name, object_type, created

from dba_objects d, (select * from dual connect by level < 3;

commit;

segment creation must be immediate otherwise you will get an error! Your table must have a primary key as well, since whole structure is based on PK, it is logical to have one. I roughly inserted 146.000 rows into table.

EDIT: I also tried those tests with 14 million rows too but results are similar.

this is not enough! you must use dbms_memoptimize package to use this table as fast lookup table.

exec dbms_memoptimize.populate('MUSTAFA', 'TMP_OPTIMIZE');

1	exec dbms_memoptimize.populate('MUSTAFA', 'TMP_OPTIMIZE');

let’s see what is happening now:

sqlplus / as sysdba

set autotrace on

select * from tmp_optimize where id = 4689;

        ID OBJECT_NAME  OBJECT_TYPE CREATED
---------- ------------ ----------- ---------
      4689 DBA_EDITIONS VIEW        30-MAY-19                                                                                                                      30-MAY-19


Elapsed: 00:00:00.13

Execution Plan
----------------------------------------------------------
Plan hash value: 2577027856

-------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |              |     1 |   154 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID READ OPTIM| TMP_OPTIMIZE |     1 |   154 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN READ OPTIM         | SYS_C008357  |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=4689)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        810  bytes sent via SQL*Net to client
        404  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

sqlplus / as sysdba

set autotrace on

select * from tmp_optimize where id = 4689;

ID OBJECT_NAME OBJECT_TYPE CREATED

---------- ------------ ----------- ---------

4689 DBA_EDITIONS VIEW 30-MAY-19 30-MAY-19

Elapsed: 00:00:00.13

Execution Plan

----------------------------------------------------------

Plan hash value: 2577027856

-------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 154 | 1 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID READ OPTIM| TMP_OPTIMIZE | 1 | 154 | 1 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN READ OPTIM | SYS_C008357 | 1 | | 2 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("ID"=4689)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

0 consistent gets

0 physical reads

0 redo size

810 bytes sent via SQL*Net to client

404 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

I skipped first execution statistics! you will see a small consistent gets at the first execution but consecutive executions will be like above.

as you see there are new execution plan steps “TABLE ACCESS BY INDEX ROWID READ OPTIM” and “INDEX UNIQUE SCAN READ OPTIM”, so these steps tell us, an hash index is used to retrieve data like as in key-value pairs. there is no “consistent gets”. this is amazing. almost nothing has read for this query and that should make this query so much faster. Is it? well, I couldn’t find it, not as much as I expected at least. Actually, I found different blog posts mentioning about fast lookup tables but non of them has made a performance test.

So, I want to test the speed but there is a problem. for now, there are many limitations on fast lookup tables. one of them is “you cannot use it via plsql”. this means I can’t create a simple plsql procedure and run a query for 100000 times and compare timings. to do a test, I wrote a small python code.

Warning: I am no python developer, I just now python to solve my small problems, this code that I share below probably has many bugs but I just use it for this test purposes.

here is the python code I use:

import cx_Oracle
import time
from threading import Thread


def get_row():
    dsn_tns = cx_Oracle.makedsn('localhost', '1521', service_name='orcl')
    conn = cx_Oracle.connect(user=r'mustafa', password='mustafa', dsn=dsn_tns) # amazing security on password

    c = conn.cursor()
    start_time = time.time()

    for k in range(1, 100000):
        v = (k % 10000) + 1
        c.execute("select /*+ findme */ * from tmp_optimize where id = :myid", myid=v)
    print("Processing time of: {} seconds.".format(time.time() - start_time))
    conn.close()


t = list()

for i in range(1, 10):
    thread = Thread(target=get_row)
    t.append(thread)
    thread.start()

for thread in t:
    thread.join()

print('done!')

import cx_Oracle

import time

from threading import Thread

def get_row():

dsn_tns = cx_Oracle.makedsn('localhost', '1521', service_name='orcl')

conn = cx_Oracle.connect(user=r'mustafa', password='mustafa', dsn=dsn_tns) # amazing security on password

c = conn.cursor()

start_time = time.time()

for k in range(1, 100000):

v = (k % 10000) + 1

c.execute("select /*+ findme */ * from tmp_optimize where id = :myid", myid=v)

print("Processing time of: {} seconds.".format(time.time() - start_time))

conn.close()

t = list()

for i in range(1, 10):

thread = Thread(target=get_row)

t.append(thread)

thread.start()

for thread in t:

thread.join()

print('done!')

after changing code many times, I used this final code. this code creates 9 threads and every thread run “select * from tmp_optimize where id = :myid” query for 100.000 times and myid variable value is between 1 and 10000 in circular way in this loop. each threads print total execution time for 100.000 runs of the query. here is the result:

Processing time of: 16.03737473487854 seconds.
Processing time of: 16.03542184829712 seconds.
Processing time of: 16.06372594833374 seconds.
Processing time of: 16.021758556365967 seconds.
Processing time of: 16.089550113677979 seconds.
Processing time of: 16.11350131034851 seconds.
Processing time of: 16.094431495666504 seconds.
Processing time of: 16.009099006652832 seconds.
Processing time of: 16.085646486282349 seconds.

Processing time of: 16.03737473487854 seconds.

Processing time of: 16.03542184829712 seconds.

Processing time of: 16.06372594833374 seconds.

Processing time of: 16.021758556365967 seconds.

Processing time of: 16.089550113677979 seconds.

Processing time of: 16.11350131034851 seconds.

Processing time of: 16.094431495666504 seconds.

Processing time of: 16.009099006652832 seconds.

Processing time of: 16.085646486282349 seconds.

timing is changed between 16.0 and 16.1 seconds for this fast lookup tables. how about a normal table with normal primary key?

create table tmp_not_optimize (
  id number primary key,
  object_name varchar2(128),
  object_type varchar2(128),
  created date)
  segment creation immediate;

insert into tmp_not_optimize 
  select rownum, object_name, object_type, created 
  from dba_objects, (select * from dual connect by level < 3);
commit;

create table tmp_not_optimize (

id number primary key,

object_name varchar2(128),

object_type varchar2(128),

created date)

segment creation immediate;

insert into tmp_not_optimize

select rownum, object_name, object_type, created

from dba_objects, (select * from dual connect by level < 3);

commit;

I used same python code just changed table name from “tmp_optimize” to “tmp_not_optimize” and result is like this:

Processing time of: 17.14604425430298 seconds.
Processing time of: 17.22217082977295 seconds.
Processing time of: 17.25145125389099 seconds.
Processing time of: 17.258281707763672 seconds.
Processing time of: 17.267066955566406 seconds.
Processing time of: 17.27292227745056 seconds.
Processing time of: 17.186059951782227 seconds.
Processing time of: 17.22607660293579 seconds.
Processing time of: 17.190939903259277 seconds.

Processing time of: 17.14604425430298 seconds.

Processing time of: 17.22217082977295 seconds.

Processing time of: 17.25145125389099 seconds.

Processing time of: 17.258281707763672 seconds.

Processing time of: 17.267066955566406 seconds.

Processing time of: 17.27292227745056 seconds.

Processing time of: 17.186059951782227 seconds.

Processing time of: 17.22607660293579 seconds.

Processing time of: 17.190939903259277 seconds.

it is between 17.1 and 17.2 seconds. there is %6 performance gain (give or take).

I am not sure whether %6 is a good gain or not because to use fast lookup table actively, we need to separate serious amount of memory area. Also, there are few limitations like, cannot be used with partitioned or compressed tables, cannot be used in plsql (not yet anyway). only equality conditions on primary keys can advantage of fast lookup.

During my tests I didn’t check wait events. I expect that less latch waits on fast lookup table since no consistent gets occur. Maybe there is something I missed and I will do more tests on these tables (I didn’t even test “memoptimize for write” (fast ingest) yet).

wish you all healthy days.

Blockchain vs Immutable tables

Published on: 2021-08-202021-10-26 by Mustafa 2 Comments

Hi,

as you know 21c is here for linux and some new features are (as always) pretty exciting. two of them are Blockchain and Immutable tables. Let’s explain and test.

first of all, those are new features for 21c but they are backported to 19c. at 19.10 you are able to use BLOCKCHAIN tables and 19.11 IMMUTABLE tables. I will make my tests at 19.12

if you are familiar to Bitcoin or any other alt coin then you probably now what blockchain is. basically it is a distributed ledger. every transaction is a “block” and each block contains information about transaction like sender, receiver, amount etc and also a hash value of the previous transaction (using some hash functions). That way all new transactions are added to the end of the “chain” and that creates the chain. This ensures that chain is unbreakable you can not tamper with old records because that will breaks the chain (hash values won’t match with next blocks).

So, Oracle uses this technology to create a new table called Blockchain table. every inserted row in this table are stored with some additional information like current timestamp and most importantly, previous row’s hash values! Hash value operations are made at the “COMMIT” time not before. that way Oracle guarantees that every row is a part of chain and you can not tamper with those rows. if you do, chain will be broken and can not be verified. Also, Oracle won’t let you delete or update those rows using any DML statement like delete, update, merge etc.

Immutable tables are pretty similar to Blockchain tables. you can not update those table using any DML as blockchain tables. Difference is rows in the Immutable tables are not linked to each other. Rows does not store any hash value from previous rows.

both of them are designed to create “unchangeable”, Insert only tables. if you think that this is a real necessity especially auditing staff. for example if you store a log information for a credit card information you don’t want anyone to change them and you want to be sure that those rows are intact and unchanged on an investigation.

of course you just need to store table data for a specific amount of time like 5 years etc. No one can store them forever so at some point you must be able to delete old rows. This is something you can define while creating those tables. Also, even if I may not able to delete the rows what would happen if someone drops the table! this is of course forbidden and again you can define a drop policy while creating tables. Let’s start with a blockchain table:

CREATE BLOCKCHAIN TABLE bank_ledger_blockchain (bank VARCHAR2(128), deposit_date DATE, deposit_amount NUMBER)
NO DROP UNTIL 31 DAYS IDLE
NO DELETE until 30 days after insert
HASHING USING "SHA2_512" VERSION "v1";

CREATE BLOCKCHAIN TABLE bank_ledger_blockchain (bank VARCHAR2(128), deposit_date DATE, deposit_amount NUMBER)

NO DROP UNTIL 31 DAYS IDLE

NO DELETE until 30 days after insert

HASHING USING "SHA2_512" VERSION "v1";

you need BLOCKCHAIN keyword (or IMMUTABLE for immutable tables) at the create statement.

“NO DROP UNTIL 31 DAYS IDLE” defines how can you drop this table. if you have no actions on that table for 31 days then and only then you can drop it. By the way after creating table, if you don’t insert any row then you can drop it immediately. you can just use NO DROP to disable dropping of this table (only way is dropping database entirely).

“NO DELETE until 30 days after insert” defines how can you delete “old” rows. in my example you can delete a row after 31 days of it’s insertion. Again you can use NO DELETE LOCKED to disable deleting from table.

hashing part is the default and you have to write this down to define blockchain structure.

let’s insert a row and try to delete or update:

SQL> insert into bank_ledger_blockchain values ('dummy', sysdate, 1);

1 row created.

SQL> commit;

Commit complete.

SQL> delete bank_ledger_blockchain;
delete bank_ledger_blockchain
       *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table


SQL> update bank_ledger_blockchain set deposit_amount = 10;
update bank_ledger_blockchain set deposit_amount = 10
       *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table

SQL> insert into bank_ledger_blockchain values ('dummy', sysdate, 1);

1 row created.

SQL> commit;

Commit complete.

SQL> delete bank_ledger_blockchain;

delete bank_ledger_blockchain

ERROR at line 1:

ORA-05715: operation not allowed on the blockchain or immutable table

SQL> update bank_ledger_blockchain set deposit_amount = 10;

update bank_ledger_blockchain set deposit_amount = 10

ERROR at line 1:

ORA-05715: operation not allowed on the blockchain or immutable table

as you can see modifying is forbidden. you can not even delete rows that passes the retention time that you defined (no delete until…) with a delete statement. You must use DBMS_BLOCKCHAIN_TABLE.DELETE_EXPIRED_ROWS procedure to delete rows beyond the retention.

This is BLOCKCHAIN table. of course there are much more properties like user signs etc but I will leave it here for now. For IMMUTABLE tables, almost everything is the same:

create immutable table bank_ledger_immutable (bank VARCHAR2(128), deposit_date DATE, deposit_amount NUMBER)
no drop until 31 days idle
no delete until 30 days after insert;

create immutable table bank_ledger_immutable (bank VARCHAR2(128), deposit_date DATE, deposit_amount NUMBER)

no drop until 31 days idle

no delete until 30 days after insert;

just change “blockchain” keyword to “immutable” and remove hash clause. pretty straight. as expected you can not delete or update rows. if you want to delete old rows then you must use DBMS_IMMUTABLE_TABLE.DELETE_EXPIRED_ROWS procedure.

Common Feature of both tables is that they can not be modified by authorized db users not even SYS or db vault admins. No one in the database can change those rows. Basic difference of the both tables is Blockchain table has steps for cryptographic linking between the last row and new row but Immutable table has no option like that. what does this provide? Immutable tables can not be modified via Oracle Database Software but how about bypassing DB? if someone modify datafile using Operating system tools then your table data will be modified and you can not be aware of it. Blockchain tables, on the other hand, has a verify function. since every row linked to each other with some hash value, if you modify this data using any tool, like OS tools, verification will be failed because if a row changes then it’s hash value will be change and that hash value is stored on the next row.

Then why immutable tables are exists since blockchain tables are more secure? You might guess that, creating hash values and storing them on the next row causes some extra work which means performance issues.

Let’s make a test (with a flow on purpose):

set serveroutput on

declare
  start_time timestamp;
  end_time timestamp;
  inserted_row_number number := 100000;
  procedure print_elapsed_time(p_table_type varchar2) as
  begin
    end_time := systimestamp;
    dbms_output.put_line('spend time on ' || p_table_type || ' table: ' || (end_time-start_time));
    start_time := systimestamp;
  end;
begin
  start_time := systimestamp;
  
  insert into bank_ledger_blockchain
    select 'dummy bank', sysdate + level, rownum
    from dual 
    connect by level <= inserted_row_number;
  
  print_elapsed_time('BLOCKCHAIN BULK INSERT');
  
  for i in 1..inserted_row_number loop
    insert into bank_ledger_blockchain values ('dummy bank', sysdate + i, i);
  end loop;
  
  print_elapsed_time('BLOCKCHAIN ROW BY ROW');
  
  insert into bank_ledger_immutable
    select 'dummy bank', sysdate + level, rownum
    from dual 
    connect by level <= inserted_row_number;
  
  print_elapsed_time('IMMUTABLE BULK INSERT');

  
  for i in 1..inserted_row_number loop
    insert into bank_ledger_immutable values ('dummy bank', sysdate + i, i);
  end loop;
  
  print_elapsed_time('IMMUTABLE ROW BY ROW');
end;
/

spend time on BLOCKCHAIN BULK INSERT table: +000000000 00:00:00.402270000
spend time on BLOCKCHAIN ROW BY ROW table: +000000000 00:00:01.155506000
spend time on IMMUTABLE BULK INSERT table: +000000000 00:00:00.355110000
spend time on IMMUTABLE ROW BY ROW table: +000000000 00:00:02.073610000

set serveroutput on

declare

start_time timestamp;

end_time timestamp;

inserted_row_number number := 100000;

procedure print_elapsed_time(p_table_type varchar2) as

begin

end_time := systimestamp;

dbms_output.put_line('spend time on ' || p_table_type || ' table: ' || (end_time-start_time));

start_time := systimestamp;

end;

begin

start_time := systimestamp;

insert into bank_ledger_blockchain

select 'dummy bank', sysdate + level, rownum

from dual

connect by level <= inserted_row_number;

print_elapsed_time('BLOCKCHAIN BULK INSERT');

for i in 1..inserted_row_number loop

insert into bank_ledger_blockchain values ('dummy bank', sysdate + i, i);

end loop;

print_elapsed_time('BLOCKCHAIN ROW BY ROW');

insert into bank_ledger_immutable

select 'dummy bank', sysdate + level, rownum

from dual

connect by level <= inserted_row_number;

print_elapsed_time('IMMUTABLE BULK INSERT');

for i in 1..inserted_row_number loop

insert into bank_ledger_immutable values ('dummy bank', sysdate + i, i);

end loop;

print_elapsed_time('IMMUTABLE ROW BY ROW');

end;

spend time on BLOCKCHAIN BULK INSERT table: +000000000 00:00:00.402270000

spend time on BLOCKCHAIN ROW BY ROW table: +000000000 00:00:01.155506000

spend time on IMMUTABLE BULK INSERT table: +000000000 00:00:00.355110000

spend time on IMMUTABLE ROW BY ROW table: +000000000 00:00:02.073610000

I inserted rows using bulk insert and row by row on both table and here are the results. huh! blockchain table is almost faster than immutable table? Actually no! as I said earlier hashing operations are done at the commit time not before on the blockchain table! in my code there is no commit! that’s why we see the same performance result. how about adding commits before printing time:

declare
  start_time timestamp;
  end_time timestamp;
  inserted_row_number number := 100000;
  procedure print_elapsed_time(p_table_type varchar2) as
  begin
    end_time := systimestamp;
    dbms_output.put_line('spend time on ' || p_table_type || ' table: ' || (end_time-start_time));
    start_time := systimestamp;
  end;
begin
  start_time := systimestamp;
  
  insert into bank_ledger_blockchain
    select 'dummy bank', sysdate + level, rownum
    from dual 
    connect by level <= inserted_row_number;
  commit;
  print_elapsed_time('BLOCKCHAIN BULK INSERT');
  
  for i in 1..inserted_row_number loop
    insert into bank_ledger_blockchain values ('dummy bank', sysdate + i, i);
  end loop;
  commit;
  print_elapsed_time('BLOCKCHAIN ROW BY ROW');
  
  insert into bank_ledger_immutable
    select 'dummy bank', sysdate + level, rownum
    from dual 
    connect by level <= inserted_row_number;
  commit;
  print_elapsed_time('IMMUTABLE BULK INSERT');

  
  for i in 1..inserted_row_number loop
    insert into bank_ledger_immutable values ('dummy bank', sysdate + i, i);
  end loop;
  commit;
  print_elapsed_time('IMMUTABLE ROW BY ROW');
end;
/

spend time on BLOCKCHAIN BULK INSERT table: +000000000 00:00:12.555355000
spend time on BLOCKCHAIN ROW BY ROW table: +000000000 00:00:13.749063000
spend time on IMMUTABLE BULK INSERT table: +000000000 00:00:00.312838000
spend time on IMMUTABLE ROW BY ROW table: +000000000 00:00:02.053782000

declare

start_time timestamp;

end_time timestamp;

inserted_row_number number := 100000;

procedure print_elapsed_time(p_table_type varchar2) as

begin

end_time := systimestamp;

dbms_output.put_line('spend time on ' || p_table_type || ' table: ' || (end_time-start_time));

start_time := systimestamp;

end;

begin

start_time := systimestamp;

insert into bank_ledger_blockchain

select 'dummy bank', sysdate + level, rownum

from dual

connect by level <= inserted_row_number;

commit;

print_elapsed_time('BLOCKCHAIN BULK INSERT');

for i in 1..inserted_row_number loop

insert into bank_ledger_blockchain values ('dummy bank', sysdate + i, i);

end loop;

commit;

print_elapsed_time('BLOCKCHAIN ROW BY ROW');

insert into bank_ledger_immutable

select 'dummy bank', sysdate + level, rownum

from dual

connect by level <= inserted_row_number;

commit;

print_elapsed_time('IMMUTABLE BULK INSERT');

for i in 1..inserted_row_number loop

insert into bank_ledger_immutable values ('dummy bank', sysdate + i, i);

end loop;

commit;

print_elapsed_time('IMMUTABLE ROW BY ROW');

end;

spend time on BLOCKCHAIN BULK INSERT table: +000000000 00:00:12.555355000

spend time on BLOCKCHAIN ROW BY ROW table: +000000000 00:00:13.749063000

spend time on IMMUTABLE BULK INSERT table: +000000000 00:00:00.312838000

spend time on IMMUTABLE ROW BY ROW table: +000000000 00:00:02.053782000

here it is! at worst, immutable tables are faster more than 6 times (row by row insert) and at best, almost 40 times faster than blockchain tables. So, while choosing table type be careful. if immutable tables meet the need then use them.

this is just a glance for new table types. I will make more tests and come with new posts.

wish you healthy days.

Edit: I didn’t mentioned about COMPATIBLE parameter. it should be at least 19.10 to create blockchain table and 19.11 for immutable table.

Pragma UDF & Deterministic

Published on: 2021-04-09 by Mustafa Comment

Hello everyone,

I wanted to write about something simple and can improve the performance of your sql statements. Unfortunately, many database developers use too much plsql function in their SQL statements. I say too much because if you learn more about the SQL, you will use less plsql. SQL is very capable language to process data. Of course we don’t design and develop a whole system all the time. We start to work somewhere and use their standards and run old codes. Many of my clients have some basic plsql functions which does not interact with SQL like just doing basic calculations or conversions. there plsql functions are pure algorithms which can be also run by SQL too. for example:

create or replace function sf_add_vat (p number) return number as 
begin
  return p*1.18;
end;
/

create or replace function sf_add_vat (p number) return number as

begin

return p*1.18;

end;

this is a very simple VAT adding function. As you can see this calculation can be done by SQL too but let’s say someone writes this code 20 years ago and now it is used everywhere in database (maybe also in the application too). So you might not be able to change all of them but you can increase it’s performance by deterministic or pragma UDF.

let’s make a small example first:

drop table tmp purge;
create table tmp as select * from dba_tables where rownum <= 1000;

insert into tmp select tmp.* from tmp, (select * from dual connect by level <= 799) ;
commit;

drop table tmp purge;

create table tmp as select * from dba_tables where rownum <= 1000;

insert into tmp select tmp.* from tmp, (select * from dual connect by level <= 799) ;

commit;

this will create a table with 800.000 rows. now I will run a query against this table with and without function:

set timing on;

----------------------------------------------------------------
  
select max(sf_add_vat(pct_free)) from tmp;

MAX(SF_ADD_VAT(PCT_FREE))
-------------------------
                     11.8

Elapsed: 00:00:01.133

----------------------------------------------------------------
  
select max(pct_free*1.18) from tmp;

MAX(PCT_FREE*1.18)
------------------
              11.8

Elapsed: 00:00:00.134

set timing on;

----------------------------------------------------------------

select max(sf_add_vat(pct_free)) from tmp;

MAX(SF_ADD_VAT(PCT_FREE))

-------------------------

11.8

Elapsed: 00:00:01.133

----------------------------------------------------------------

select max(pct_free*1.18) from tmp;

MAX(PCT_FREE*1.18)

------------------

11.8

Elapsed: 00:00:00.134

it takes almost 10 times more to complete the query when you use a PLSQL function.

Reason is, whenever you use a plsql function inside a query (or vice versa) something called “context switch” is happening. you can think this as, SQL statements are run by SQL Engine and PLSQL statements are run by PLSQL engines and when ever these two engines meet with opposite statement, they have to call the other engine. this is context switching. You must avoid as much as you can from this but as I said if the system you work on is run the queries like this, you might not have to change all but you can, at least, increase the performance. that where pragma udf and deterministic come.

Deterministic tells Oracle, this function will return same result for same parameter all the time! if your plsql object does not interact with sql objects like tables or time variables like sysdate then probably it is a good candidate for deterministic. Also, if you want to index a user defined plsql function it must be deterministic. you can add deterministic clause to a function which is interact with sql objects or sysdate etc but this will only cause you headaches. so be careful about that.

Pragma udf is a way to reduce context switch overhead. it does not reduce the number of context switch but it makes it smaller and faster. that way if you use a plsql function in sql, context switch will be lighter.

Let’s make a small test now, my example is very good candidate for both deterministic and pragma udf:

create or replace function sf_add_vat (p number) return number as 
begin
  return p*1.18;
end;
/

create or replace function sf_add_vat_deterministic (p number) return number  deterministic as 
begin
  return p*1.18;
end;
/

create or replace function sf_add_vat_deterministic_udf (p number) return number  deterministic as 
  pragma udf;
begin
  return p*1.18;
end;
/

create or replace function sf_add_vat (p number) return number as

begin

return p*1.18;

end;

create or replace function sf_add_vat_deterministic (p number) return number deterministic as

begin

return p*1.18;

end;

create or replace function sf_add_vat_deterministic_udf (p number) return number deterministic as

pragma udf;

begin

return p*1.18;

end;

I created 3 version of my vat function. first is the base one, second is with a DETERMINISTIC clause and third is both deterministic and pragma udf. they all do the same work. let’s compare their performance:

set serveroutput on

declare
  s timestamp;
  
  procedure run(p_function varchar2) as
    v_result number;
  begin
    s := systimestamp;
    for i in 1..10 loop
      if p_function = 'BASE' then
        select max(sf_add_vat(pct_free)) into v_result from tmp;
      elsif p_function = 'BASE_DETERMINISTIC' then
        select max(sf_add_vat_deterministic(pct_free)) into v_result from tmp;
      elsif p_function = 'BASE_DETERMINISTIC_UDF' then
        select max(sf_add_vat_deterministic_udf(pct_free)) into v_result from tmp;
      elsif p_function = 'GO_SQL' then
        select max(pct_free*pct_free) into v_result from tmp;
      end if;
                              
    end loop;
    
    dbms_output.put_line(Rpad(P_Function,25,' ') || ': ' || ((systimestamp - s)/10));
  end;
begin
  run ('BASE');
  run ('BASE_DETERMINISTIC');
  run ('BASE_DETERMINISTIC_UDF');
  run ('GO_SQL');
end;
/

BASE                     : +000000000 00:00:01.150900000
BASE_DETERMINISTIC       : +000000000 00:00:00.280100000
BASE_DETERMINISTIC_UDF   : +000000000 00:00:00.192900000
GO_SQL                   : +000000000 00:00:00.127300000

PL/SQL procedure successfully completed.

set serveroutput on

declare

s timestamp;

procedure run(p_function varchar2) as

v_result number;

begin

s := systimestamp;

for i in 1..10 loop

if p_function = 'BASE' then

select max(sf_add_vat(pct_free)) into v_result from tmp;

elsif p_function = 'BASE_DETERMINISTIC' then

select max(sf_add_vat_deterministic(pct_free)) into v_result from tmp;

elsif p_function = 'BASE_DETERMINISTIC_UDF' then

select max(sf_add_vat_deterministic_udf(pct_free)) into v_result from tmp;

elsif p_function = 'GO_SQL' then

select max(pct_free*pct_free) into v_result from tmp;

end if;

end loop;

dbms_output.put_line(Rpad(P_Function,25,' ') || ': ' || ((systimestamp - s)/10));

end;

begin

run ('BASE');

run ('BASE_DETERMINISTIC');

run ('BASE_DETERMINISTIC_UDF');

run ('GO_SQL');

end;

BASE : +000000000 00:00:01.150900000

BASE_DETERMINISTIC : +000000000 00:00:00.280100000

BASE_DETERMINISTIC_UDF : +000000000 00:00:00.192900000

GO_SQL : +000000000 00:00:00.127300000

PL/SQL procedure successfully completed.

I run 4 versions, with basic function, deterministic function, deterministic & pragma udf and finally of course pure sql statement. As you can see BASE version is the worst and takes 1.15 seconds average to complete. just adding a deterministic clause decreases time spent almost 5 times and adding pragma udf decrease another 30-35% based on deterministic and finally pure sql is of course the fastest. pure sql is 10 times faster than BASE version. so, if you can use pure sql use it! if it is not possible at least you can check for deterministic candidates and use pragma udf.

Of course there is something called, “subquery caching” :

set timing on

select max( (select sf_add_vat(pct_free) from dual)) from tmp;

MAX((SELECTSF_ADD_VAT(PCT_FREE)FROMDUAL))
-----------------------------------------
                                     11.8

Elapsed: 00:00:00.15

set timing on

select max( (select sf_add_vat(pct_free) from dual)) from tmp;

MAX((SELECTSF_ADD_VAT(PCT_FREE)FROMDUAL))

-----------------------------------------

11.8

Elapsed: 00:00:00.15

as you see, subquery caching might be even faster than pragma udf and deterministic but it depends. this solution also requires changing the sql statement too.

So you can improve the performance just by small changes. find a plsql function that is so simply and used everywhere in your database (I am pretty sure there is one) and try the suggestions above. by the way, 19c has something even stronger called “SQL Macro” which I mentioned a little bit before:

http://mustafakalayci.me/2020/10/28/sql-macro-is-finally-here/

http://mustafakalayci.me/2020/10/30/sql-macro-part2-column_value-nvlparam-column_value/

I mostly mentioned for table sql macro but you can use sql macro to generate parts of sql statement too.

I hope this helps you to tune your sql & plsql statements.

wish you all healthy days.

PLSQL and Flashback Data Archive Problems.

Published on: 2021-01-302021-01-30 by Mustafa Comment

Hi,

I have written about flashback data archive before. if you didn’t read please check these post first.

http://mustafakalayci.me/2019/03/02/goodbye-log-triggers-welcome-flashback-data-archive/

http://mustafakalayci.me/2020/09/10/flashback-data-archive-problems/

I’ve already mentioned about some problems on my second post but these are not kind of problems which will stop your work. The problem I am about talk is that kind of problem.

From my perspective, it seems that flashback is designed to be used in SQL. track the history of your tables (even ddl changes) etc but if you start to use flashback in PLSQL, you will hit some walls!

PLSQL is not much dynamic language yet. Please don’t get me wrong. I am and Oracle Database Developer and Administrator. I am not interested any other databases and use SQL and PLSQL all the time but there some limits that cause to use very hard solutions some times.

I have tested the codes below at 19.8 database.

Flashback Data Archive(FDA) is a very useful option. Even if you add, modify or drop a column, you will be able to see old versions of a table and that is where my problem starts:

create flashback archive fda tablespace users retention 1 year;

create table tmp(id number, cust_id number, order_date date) flashback archive fda;

exec dbms_lock.sleep(15);

insert into tmp values (1, 10, sysdate-10);
insert into tmp values (2, 20, sysdate-20);
commit;

exec dbms_lock.sleep(7);

select sysdate from dual;

SYSDATE
-------------------
30/01/2021 14:49:42

--------------------------------------------------

create or replace procedure sp_X(p_main date) as 
  cursor C_DATA (p date) is
    select * from tmp as of timestamp (p);
  
  r_tmp C_DATA%rowtype;
begin
  open c_data(p_main);
  fetch c_data into r_tmp;
  close c_data;
end;
/

create flashback archive fda tablespace users retention 1 year;

create table tmp(id number, cust_id number, order_date date) flashback archive fda;

exec dbms_lock.sleep(15);

insert into tmp values (1, 10, sysdate-10);

insert into tmp values (2, 20, sysdate-20);

commit;

exec dbms_lock.sleep(7);

select sysdate from dual;

SYSDATE

-------------------

30/01/2021 14:49:42

--------------------------------------------------

create or replace procedure sp_X(p_main date) as

cursor C_DATA (p date) is

select * from tmp as of timestamp (p);

r_tmp C_DATA%rowtype;

begin

open c_data(p_main);

fetch c_data into r_tmp;

close c_data;

end;

I created a table and add it to a flashback data archive. I add some sleep codes to wait necessary internal tables to be created. in the end, at 14:49:42 (my local time) I have a table which contains 2 rows. a simple procedure called SP_X is getting a parameter as DATE and uses this date in flashback query.

exec sp_X(sysdate);

PL/SQL procedure successfully completed.


exec sp_X(to_date('30/01/2021 14:49:42', 'dd/mm/yyyy hh24:mi:ss'));

PL/SQL procedure successfully completed.

exec sp_X(sysdate);

PL/SQL procedure successfully completed.

exec sp_X(to_date('30/01/2021 14:49:42', 'dd/mm/yyyy hh24:mi:ss'));

PL/SQL procedure successfully completed.

as you can see this procedure works well. I didn’t demonstrate different data in different times but it works as well.

Let’s drop a column from this table:

alter table tmp drop column order_Date;

1	alter table tmp drop column order_Date;

and re run the procedure:

exec sp_X(sysdate);

Error starting at line : 20 in command -
BEGIN sp_X(sysdate); END;
Error report -
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at "MUSTAFA.SP_X", line 8
ORA-06512: at line 1
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:

exec sp_X(sysdate);

Error starting at line : 20 in command -

BEGIN sp_X(sysdate); END;

Error report -

ORA-00932: inconsistent datatypes: expected - got -

ORA-06512: at "MUSTAFA.SP_X", line 8

ORA-06512: at line 1

00932. 00000 - "inconsistent datatypes: expected %s got %s"

*Cause:

*Action:

as you see even if I am trying to get data from sysdate, I got an error. problem is even if I tried to query with previous dates cause same error starting now!

exec sp_X(to_date('30/01/2021 14:49:42', 'dd/mm/yyyy hh24:mi:ss'));

Error starting at line : 20 in command -
BEGIN sp_X(sysdate); END;
Error report -
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at "MUSTAFA.SP_X", line 8
ORA-06512: at line 1
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:

exec sp_X(to_date('30/01/2021 14:49:42', 'dd/mm/yyyy hh24:mi:ss'));

Error starting at line : 20 in command -

BEGIN sp_X(sysdate); END;

Error report -

ORA-00932: inconsistent datatypes: expected - got -

ORA-06512: at "MUSTAFA.SP_X", line 8

ORA-06512: at line 1

00932. 00000 - "inconsistent datatypes: expected %s got %s"

*Cause:

*Action:

even dropping and recreating the procedure is not working. as far as I understand, Oracle cannot decide exact definition of the query anymore. adding a column does not cause this problem but dropping a column does.

you might think of using FOR loop to fetch cursor but you will hit the same error.

by the way queries that we are trying to run are working correctly in sql:

MUSTAFA@orcl-1>select * from tmp as of timestamp (sysdate);

        ID    CUST_ID
---------- ----------
         1         10
         2         20

MUSTAFA@orcl-1>select * from tmp as of timestamp (to_date('30/01/2021 14:49:42', 'dd/mm/yyyy hh24:mi:ss'));

        ID    CUST_ID ORDER_DATE
---------- ---------- -------------------
         1         10 20/01/2021 14:49:29
         2         20 10/01/2021 14:49:29

MUSTAFA@orcl-1>select * from tmp as of timestamp (sysdate);

ID CUST_ID

---------- ----------

1 10

2 20

MUSTAFA@orcl-1>select * from tmp as of timestamp (to_date('30/01/2021 14:49:42', 'dd/mm/yyyy hh24:mi:ss'));

ID CUST_ID ORDER_DATE

---------- ---------- -------------------

1 10 20/01/2021 14:49:29

2 20 10/01/2021 14:49:29

so, using flashback queries in plsql seems a little bit dangerous. Actually one of my client also gets “ORA-01007: variable not in select list” error in plsql (sql is perfectly fine). so be careful while using flashback in plsql.

EDIT:

during my tests, I rename cust_id column and I started to got “ORA-00932: inconsistent datatypes: expected NUMBER got DATE” error. after cust_id column I got, order_date column so that make me think that, column order might be changing and I realized there is a greater problem in this!

here is sample code:

create table tmp(c1 number, c2 number, c3 number) flashback archive fda;

exec dbms_lock.sleep(15);

insert into tmp values (1, 2, 3);

commit;

exec dbms_lock.sleep(7);

alter table tmp rename column c2 to c20;

create table tmp(c1 number, c2 number, c3 number) flashback archive fda;

exec dbms_lock.sleep(15);

insert into tmp values (1, 2, 3);

commit;

exec dbms_lock.sleep(7);

alter table tmp rename column c2 to c20;

there columns c1,c2,c3 and they have ordered number in them (1,2,3).

select * from tmp;

        C1        C20         C3
---------- ---------- ----------
         1          2          3

select * from tmp;

C1 C20 C3

---------- ---------- ----------

1 2 3

column order is not changed but when I used this with flasback query:

select * from tmp as of timestamp(sysdate);

        C1         C3        C20
---------- ---------- ----------
         1          3          2

select * from tmp as of timestamp(sysdate);

C1 C3 C20

---------- ---------- ----------

1 3 2

column order has changed even if I asked for sysdate. in plsql this could cause problems because you might not aware of this and wrong variables might store your data:

set serveroutput on;

create or replace procedure sp_X(p_main date) as 
  cursor C_DATA (p date) is
    select * from tmp as of timestamp (p);
  
  r_tmp C_DATA%rowtype;
begin
  open c_data(p_main);
  fetch c_data into r_tmp;
  dbms_output.put_line(r_tmp.c1||'-'||r_tmp.c20||'-'||r_tmp.c3);
  close c_data;
end;
/

exec sp_x(sysdate);
1-3-2

set serveroutput on;

create or replace procedure sp_X(p_main date) as

cursor C_DATA (p date) is

select * from tmp as of timestamp (p);

r_tmp C_DATA%rowtype;

begin

open c_data(p_main);

fetch c_data into r_tmp;

dbms_output.put_line(r_tmp.c1||'-'||r_tmp.c20||'-'||r_tmp.c3);

close c_data;

end;

exec sp_x(sysdate);

1-3-2

as you see r_tmp.C20 variable is storing data of C3 column because it comes first in select list when flashback query is used.

so, very very careful about flashback query and plsql.

wish you healthy days.

ORA-24244: invalid host or port for access control list (ACL) assignment

Published on: 2020-12-24 by Mustafa Comment

Hello everyone,

So you wanted to migrate your ACLs to another database or you are trying to add a new acl entity but you got ORA-24244. There are many reasons for that error unfortunately. I wish Oracle provides us more detail about what is the error but I wanted to share what I have seen so far and how to generate acl records. What I am about to write is for 19c databases.

First of all, of course, you can get this error by setting an invalid port like -123 or 999999999. check them first.
secondly, port range you use might intersect with another definition. for example, there is an acl entry for www.example.com for 3000 and 3010 ports. you can not add a new port range for same host, same principal and same privileges with port range 3010-3015. since 3010 is already defined in first entry you can not use it again, second entry must start with 3011 at least.
thirdly, you might be trying to insert an entry for RESOLVE privilege with port definition! this is not also allowed. for resolve privilege, lower and upper ports must be null.

these are my basic steps to check. even expdp utility with “include=NETWORK_ACL” option might failed while importing them because of these 3 steps. Lately I was copying acls from a 12c db to 19c and after getting too much error I decided to generate a simple select statements to generate acls append command. you can either copy, paste and run the result of statement or run it in a loop and run the command with dynamic sql.

with maindata as (
    select /*+ materialize */ 
            t1.host, 
            Decode(t2.privilege, 'resolve', null, lower_port) lower_port,
            Decode(t2.privilege, 'resolve', null, upper_port) upper_port,
            t2.principal,
            t2.privilege,
            t2.is_grant
    from dba_network_acls t1
           join dba_network_acl_privileges t2 on t1.aclid = t2.aclid
    where  principal in (select username from dba_users where oracle_maintained = 'N')
  ),
  port_by_port as (
    /* generate rows for every port */
    select distinct host,
           principal,
           privilege,
           is_grant,
           p.port
    from maindata m
          left join lateral (select m.lower_port+level-1 port from dual connect by level <= m.upper_port-m.lower_port+1) p on 1=1
  ),
  port_groups as (
    /* assign a group id for consecutive port numbers.*/
    select f.*,
           port - row_number() over(partition by host, principal, privilege order by port) port_group_id
    from port_by_port f
  ),
  port_ranges as (
    select host, principal, privilege, port_group_id, min(port) lower_port, max(port) upper_port
    from port_groups
    group by host, principal, privilege, port_group_id
  )
select p.*,
'BEGIN
  DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host => '''|| host||''',
    lower_port=> '|| Decode(privilege, 'resolve', 'null', nvl(to_char(lower_port), 'null'))||',
    upper_port=> '|| Decode(privilege, 'resolve', 'null', nvl(to_char(upper_port), 'null'))||',
    ace => xs$ace_type(privilege_list => xs$name_list('''||privilege||'''),
                       principal_name => '''||principal||''',
                       principal_type => xs_acl.ptype_db)
  );
end;
/' acl_script
from port_ranges p;

with maindata as (

select /*+ materialize */

t1.host,

Decode(t2.privilege, 'resolve', null, lower_port) lower_port,

Decode(t2.privilege, 'resolve', null, upper_port) upper_port,

t2.principal,

t2.privilege,

t2.is_grant

from dba_network_acls t1

join dba_network_acl_privileges t2 on t1.aclid = t2.aclid

where principal in (select username from dba_users where oracle_maintained = 'N')

port_by_port as (

/* generate rows for every port */

select distinct host,

principal,

privilege,

is_grant,

p.port

from maindata m

left join lateral (select m.lower_port+level-1 port from dual connect by level <= m.upper_port-m.lower_port+1) p on 1=1

port_groups as (

/* assign a group id for consecutive port numbers.*/

select f.*,

port - row_number() over(partition by host, principal, privilege order by port) port_group_id

from port_by_port f

port_ranges as (

select host, principal, privilege, port_group_id, min(port) lower_port, max(port) upper_port

from port_groups

group by host, principal, privilege, port_group_id

)

select p.*,

'BEGIN

DBMS_NETWORK_ACL_ADMIN.append_host_ace (

host => '''|| host||''',

lower_port=> '|| Decode(privilege, 'resolve', 'null', nvl(to_char(lower_port), 'null'))||',

upper_port=> '|| Decode(privilege, 'resolve', 'null', nvl(to_char(upper_port), 'null'))||',

ace => xs$ace_type(privilege_list => xs$name_list('''||privilege||'''),

principal_name => '''||principal||''',

principal_type => xs_acl.ptype_db)

);

end;

/' acl_script

from port_ranges p;

Please don’t judge for not using match_recognize. I just wrote it fastest way I can think of. script is first dividing port ranges to row by row single ports and them regroup them (some old entries had intersected port ranges). also uses NULL for resolve privileges. I didn’t add all options of course. I hope this script helps you.

wish you healthy days.

export import ACL & ORA-24244 error during import

Published on: 2020-12-16 by Mustafa Comment

Hello everyone,

it’s been a while since my last post. So, I wanted to write about export and import ACL privileges. They always become a pain (at least for me). after 12c, Oracle introduce us a new and easy way of ACL copying from db to db. export&import.

so here is the basic command on your source database:

expdp system directory=DATA_PUMP_DIR logfile=ACL_EXPORT.log dumpfile=ACL_EXPORT.DMP full=y include=NETWORK_ACL

1	expdp system directory=DATA_PUMP_DIR logfile=ACL_EXPORT.log dumpfile=ACL_EXPORT.DMP full=y include=NETWORK_ACL

this will export all stored ACL privileges on your database and to import those ACLs to a new db:

impdp system directory=DATA_PUMP_DIR logfile=ACL_IMPORT.log dumpfile=ACL_EXPORT.DMP

1	impdp system directory=DATA_PUMP_DIR logfile=ACL_IMPORT.log dumpfile=ACL_EXPORT.DMP

so easy but during the import you can get ORA-24244: invalid host or port for access control list (ACL) assignment error.

I encountered this error while creating a new db for a client. In my case this has happened because RESOLVE privileges shouldn’t be used with port definition!

so, somehow previous db has port definitions for RESOLVE privileges and those ones wasn’t imported. to add them into new db, I simply used this sql:

select 
  'BEGIN
DBMS_NETWORK_ACL_ADMIN.append_host_ace (
host => '''|| t1.host||''',
ace => xs$ace_type(privilege_list => xs$name_list(''RESOLVE''),
principal_name => '''||t2.principal||''',
principal_type => xs_acl.ptype_db));
end;
/' run_Script
from dba_network_acls t1
join dba_network_acl_privileges t2 on t1.aclid = t2.aclid
where privilege='resolve' and lower_port is not null;

select

'BEGIN

DBMS_NETWORK_ACL_ADMIN.append_host_ace (

host => '''|| t1.host||''',

ace => xs$ace_type(privilege_list => xs$name_list(''RESOLVE''),

principal_name => '''||t2.principal||''',

principal_type => xs_acl.ptype_db));

end;

/' run_Script

from dba_network_acls t1

join dba_network_acl_privileges t2 on t1.aclid = t2.aclid

where privilege='resolve' and lower_port is not null;

run this select on the source db and execute the output on the target db.

wish you all happy and healthy days.