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.

Audits are not Purged

Published on: 2022-07-302022-07-30 by Mustafa Comment

Hello,

This week I realized that some of my databases’ audit data are not purged and audit tables get bigger. I already have a scheduler job to purge my audit records but somehow job has been completed successfully but audit records are not deleted. This is 19.15 version, NON-CDB databases.

I am using unified audit in all of my databases and purge audit records based on last archive timestamp. here is a sample code to purge unified audit records:

Declare
  L_Days Number := 31;
Begin 
  Dbms_Audit_Mgmt.Set_Last_Archive_Timestamp(Dbms_Audit_Mgmt.Audit_Trail_Unified, Trunc(Systimestamp)-L_Days);

  Dbms_Audit_Mgmt.Clean_Audit_Trail(Audit_Trail_Type       =>  Dbms_Audit_Mgmt.Audit_Trail_Unified,
                                    Use_Last_Arch_Timestamp=>  True);
  Commit;
  Dbms_Audit_Mgmt.Set_Last_Archive_Timestamp(Dbms_Audit_Mgmt.AUDIT_TRAIL_OS, Trunc(Systimestamp)-L_Days);

  Dbms_Audit_Mgmt.Clean_Audit_Trail(Audit_Trail_Type       =>  Dbms_Audit_Mgmt.AUDIT_TRAIL_OS,
                                    Use_Last_Arch_Timestamp=>  True);
  Commit;
End;
/

Declare

L_Days Number := 31;

Begin

Dbms_Audit_Mgmt.Set_Last_Archive_Timestamp(Dbms_Audit_Mgmt.Audit_Trail_Unified, Trunc(Systimestamp)-L_Days);

Dbms_Audit_Mgmt.Clean_Audit_Trail(Audit_Trail_Type => Dbms_Audit_Mgmt.Audit_Trail_Unified,

Use_Last_Arch_Timestamp=> True);

Commit;

Dbms_Audit_Mgmt.Set_Last_Archive_Timestamp(Dbms_Audit_Mgmt.AUDIT_TRAIL_OS, Trunc(Systimestamp)-L_Days);

Dbms_Audit_Mgmt.Clean_Audit_Trail(Audit_Trail_Type => Dbms_Audit_Mgmt.AUDIT_TRAIL_OS,

Use_Last_Arch_Timestamp=> True);

Commit;

End;

This code could belong to Tim Hall (www.oracle-base.com) not sure. This basically sets a date to delete audit records which are before that date. So, to understand the problem I started to digging but didn’t find much (before starting a trace, I found it out).

I checked what the saved last time stamp is:

select * from  DBA_AUDIT_MGMT_LAST_ARCH_TS;

AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS                   DATABASE_ID CONTAINER_GUID                   
-------------------- ------------ --------------------------------- ----------- ---------------------------------
OS AUDIT TRAIL	                1	28/06/2022 00.00.00          2158381708	B6AB832DC96B2686E0534406C40AC0E8
UNIFIED AUDIT TRAIL		0	28/06/2022 00.00.00          2158381708	B6AB832DC96B2686E0534406C40AC0E8
OS AUDIT TRAIL			1	27/10/2021 00.00.00          2128036467	B6AB832DC96B2686E0534406C40AC0E8
UNIFIED AUDIT TRAIL		0	27/10/2021 00.00.00          2128036467	B6AB832DC96B2686E0534406C40AC0E8

select * from DBA_AUDIT_MGMT_LAST_ARCH_TS;

AUDIT_TRAIL RAC_INSTANCE LAST_ARCHIVE_TS DATABASE_ID CONTAINER_GUID

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

OS AUDIT TRAIL 1 28/06/2022 00.00.00 2158381708 B6AB832DC96B2686E0534406C40AC0E8

UNIFIED AUDIT TRAIL 0 28/06/2022 00.00.00 2158381708 B6AB832DC96B2686E0534406C40AC0E8

OS AUDIT TRAIL 1 27/10/2021 00.00.00 2128036467 B6AB832DC96B2686E0534406C40AC0E8

UNIFIED AUDIT TRAIL 0 27/10/2021 00.00.00 2128036467 B6AB832DC96B2686E0534406C40AC0E8

that is interesting because this is NON-CDB database but it has 2 different database_id for same AUDIT_TRAIL type. One of them is actual id (2158381708) of current database but other one is unknown (at first). Also, for the rows with 2158381708 database id, last_archive_ts is correct but the other one was pretty old. So, some of my databases has this not purged audit problems and all of them has double rows in this data dictionary view. So, I thought there might be a bug and maybe code gets an too many rows error. Simply I deleted foreign database id rows. base table of DBA_AUDIT_MGMT_LAST_ARCH_TS is SYS.DAM_LAST_ARCH_TS$. I deleted rows:

-- this code is for NON-CDB database only! CDB database has more than one database and database id.
delete DAM_LAST_ARCH_TS$ where database_id != (select dbid from v$database);
commit;

-- this code is for NON-CDB database only! CDB database has more than one database and database id.

delete DAM_LAST_ARCH_TS$ where database_id != (select dbid from v$database);

commit;

then I re-run audit purge code and it worked!

Of course, I didn’t stop at here. Why there were different rows on this table? while checking database id’s I realized that all databases I had this problem are duplicated databases. I duplicated PROD to create a TEST or ACP for example and every time I duplicate a database I always change database id with “nid” tool.

I learned that after nid, those table remain to store old rows. I deleted old database id rows from all databases and problem solved. purge operation is a success. this is something new that I will add to my duplicate database steps. if you have duplicated database and didn’t check audit rows, you should.

thanks for reading. wish you all healthy, happy, peaceful days.

Hierarchical Blocking Session List

Published on: 2021-10-19 by Mustafa Comment

Hi,

Today, I needed to list all blocking sessions hierarchically. As you know, all information about sessions (and blocking sessions) are in gv$session (or just v$session if you are using single instance) view. I wanted to see who is blocking who and blocker at the top. you can write simple connect by (or with clause) query but it will list blockers at the bottom so I just developed it a little bit. if I remember correctly there were a small script under rdbms/admin but it was creating some tables etc etc. I just want to get it using a simple sql statements.

here is the sql:

with blockers as (
    select /*+ materialize */ blocking_session from gv$session where blocking_session is not null
  ),
  blocker_hierarchy as (
    select /*+ materialize */ sid, blocking_session, lpad(sid, 10 + level*2, ' '), CONNECT_BY_ISLEAF leaf
    from   gv$session
    connect by nocycle prior sid = blocking_session and prior inst_id = inst_id
    start with sid in (select blocking_session from blockers)
  ),
  Main_Blockers as (
    select username, sid, blocking_session, 
           lpad(' ', (level-1)*2, ' ') ||Decode(CONNECT_BY_ROOT sid, sid, 'BLOCKER', 'BLOCKED') type, 
           'SID: ' || lpad('>', (level-1)*3, '-') || rpad(to_char(sid), 6, ' ') || 
           'User:' || username || 
           '  Current SQL: ' ||(Select substr(Sql_Text, 1, 40) from v$sqlarea where sql_id = s.sql_id) Blocking_info
    from   gv$session s
    connect by nocycle prior sid = blocking_session and prior inst_id = inst_id
    start with sid in (select sid from blocker_hierarchy 
                       where  leaf = 0 
                         and  blocking_session is null 
                         and  type <> 'BACKGROUND')
    order siblings by sid)
select * from Main_Blockers;

with blockers as (

select /*+ materialize */ blocking_session from gv$session where blocking_session is not null

blocker_hierarchy as (

select /*+ materialize */ sid, blocking_session, lpad(sid, 10 + level*2, ' '), CONNECT_BY_ISLEAF leaf

from gv$session

connect by nocycle prior sid = blocking_session and prior inst_id = inst_id

start with sid in (select blocking_session from blockers)

Main_Blockers as (

select username, sid, blocking_session,

lpad(' ', (level-1)*2, ' ') ||Decode(CONNECT_BY_ROOT sid, sid, 'BLOCKER', 'BLOCKED') type,

'SID: ' || lpad('>', (level-1)*3, '-') || rpad(to_char(sid), 6, ' ') ||

'User:' || username ||

' Current SQL: ' ||(Select substr(Sql_Text, 1, 40) from v$sqlarea where sql_id = s.sql_id) Blocking_info

from gv$session s

connect by nocycle prior sid = blocking_session and prior inst_id = inst_id

start with sid in (select sid from blocker_hierarchy

where leaf = 0

and blocking_session is null

and type <> 'BACKGROUND')

order siblings by sid)

select * from Main_Blockers;

first, I get all blockers’ sids (blockers data source in the query) then I use them to generate the hierarchy (blocker_hierarcy data source in the query) then I remove some blockers from the hierarchy because they are also blocked by some other sessions and will be listed under them.

for example, let’s say sid 10 is blocking 2 different sessions (sid 20 and 30) and also sid 30 is blocking some other session (sid 40). so this query will produce a result like this:

SID: 10  User:MUSTAFA  Current SQL: 
SID: -->20  User:DAVID  Current SQL: update t set d = 3 where d = 2
SID: -->30  User:STEPHAN  Current SQL: update t set d = 1 where d = 2
SID: ----->40    User:JOOST  Current SQL: update t set d = 1 where d = 3

SID: 10 User:MUSTAFA Current SQL:

SID: -->20 User:DAVID Current SQL: update t set d = 3 where d = 2

SID: -->30 User:STEPHAN Current SQL: update t set d = 1 where d = 2

SID: ----->40 User:JOOST Current SQL: update t set d = 1 where d = 3

you can see the hierarch in a visual way and the first 40 character of their currently running sql statements.

Hope this will be useful for you.

wish you healthy days.

X Forwarding for an Oracle DBA

Published on: 2021-08-022021-08-02 by Mustafa Comment

Hi,

It’s been a long time. I want to write something about X Forwarding. you might feel more comfortable to use a GUI instead of silent installations sometimes. if you are connecting to a linux server remotely then you must enable X Forwarding to enable GUI for Oracle product like dbca, netca etc.

First of all, use MobaXterm to eliminate many problems. Moba sets some parameters for you and it also shows you if X Forwarding is enabled or disabled for that session:

just using MobaXterm is not enough of course:

1- create .Xauthority file under your linux user home directory:

touch ~/.Xauthority

2- check ssh config if X11Forwarding parameter is set to yes or not:

sudo vim /etc/ssh/sshd_config

it must be yes of course.

3- install xauth packages:

sudo yum install xorg-x11-xauth xorg-x11-utils xorg-x11-fonts-* xorg-x11-font-utils xorg-x11-fonts-Type1

(fonts are not mandatory)

4- create a magic cookie:

xauth add :${DISPLAY#*:} . `mcookie`

1	xauth add :${DISPLAY#*:} . `mcookie`

that is pretty much it. after these steps MobaXterm should be showing you X11Forwarding as enabled.

so far so good but there is a specific scenario. many company uses VPN accounts now and you are logging on to servers with your VPN account but oracle installation could be under another user like “oracle” and you might not have password of oracle user since it is also a directory user and managed by different teams on hosting company (or your own company). in that case you might be using “sudo su – oracle” command to switch to oracle user and if you do that X11 Forwarding will not work! as a quick hack you can use this:

my vpn user: mustafa

oracle installation user: oracle

login as mustafa, then copy .Xauthority file to oracle user’s home directory, then change owner of the .Xauthority file as oracle:

cp ~/.Xauthority /home/oracle/

chown oracle:oracle /local/oracle/.Xauthority

cp ~/.Xauthority /home/oracle/

chown oracle:oracle /local/oracle/.Xauthority

you can put this into a sh file and run. you must run these commands with sudo of course.

last step is $DISPLAY parameter. echo $DISPLAY parameter with mustafa user and after switching to oracle user set this parameter:

mustafa@prod ~$ echo $DISPLAY
localhost:10.0
mustafa@prod ~$ sudo su - oracle
[sudo] password for mustafa:
Last login: Fri Jul ********************
oracle@prod export $DISPLAY=localhost:10.0

mustafa@prod ~$ echo $DISPLAY

localhost:10.0

mustafa@prod ~$ sudo su - oracle

[sudo] password for mustafa:

Last login: Fri Jul ********************

oracle@prod export $DISPLAY=localhost:10.0

that’s it. you should be able to use X11 Forwarding.

hope this helps.

Note: in my country there is a huge fire at almost all sea side. Many beautiful towns and villages are burned. People died, dozens of hills are turned into ash and thousands of animals had died. I hope it will end soon and climate change is real and here. every human should be aware of this.

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.

Which Column has Changed in Every Row?

Published on: 2020-11-022020-11-03 by Mustafa Comment

Hi,

Edit: Thanks to Chris Saxon who is from asktom team, checked my code and warned me about a problem. Not to overload the PGA fetch_rows function only fetches 1024 rows at once so every row set chunk size is 1024. In this case for the 1025th row, previous row information is lost because fetch_rows procedure ended and recalled. He suggested to use XStore to store data from previous run of fetch_rows and I added it. While doing that I removed some unnecessary lines too. Thanks again Chris.

PS: this is not a Polymorphic Table Function (PTF) explanation or introduction, I just wanted to share a code that I wrote before and very useful (at least for me). if you want to learn more about PTFs check https://blog.sqlora.com/en/polymorphic-table-functions-example-transposing-columns-to-rows/

PTF is a great tool that enhances the sql. I think I only need a “pointer” definition in PLSQL after PTF and that’s all I would be very happy 🙂 even in this example, it would be nice if I can use a pointer to point data collections which makes the code shorter and more readable.

About a few months ago, I debugged a code for some unnecessary rows. It comes to an sql statement which has a MINUS set operator to eliminate rows but some rows are not eliminated as needed. I found the rows and try to find out what column has different values so that row is not eliminated in MINUS but this table has so many NUMBER columns which has many decimal points and it was really hard to find which column(s) has different values.

Since I am a so lazy person to check all column by column in two rows, I created a Polymorphic Table Function (PTF) to find the differences for me and it worked as I needed. I added a new varchar2 column to the base query (select … minus select …) called DIFFERENT_COLUMNS and I put different column names, old value and new value into that column and got all of them. After solving my problems, I realized that I can use this code for something even better!!!

While looking to a log data of table (trigger base or flashback or flashback data archive), I have been asked many times “what changed”. which column(s) has changed? it is not easy and mostly required to check row by row, column by column. So, my polymorphic table function is perfect solution for that! Just provide necessary table or select statement and find all columns changed every step.

Here is my basic code:

create or replace Package Pkg_Column_Value_Differencies as
  /* Written by Mustafa KALAYCI 
     mustafakalayci.me
     find different values of columns with previous rows in query order */
  Function SF_Find_Differences (Source_Table in Table,
                                Excluded_Columns IN DBMS_TF.COLUMNS_T := null) 
    Return Table Pipelined
      Row Polymorphic Using Pkg_Column_Value_Differencies;

  Function Describe(Source_Table in out dbms_tf.table_t,
                    Excluded_Columns IN DBMS_TF.COLUMNS_T := null) return dbms_tf.describe_t;
  Procedure Fetch_Rows;
End;
/

create or replace Package Body Pkg_Column_Value_differencies As
  /* Written by Mustafa KALAYCI 
     mustafakalayci.me
     find different values of columns with previous rows in query order */
  Function Describe(Source_Table in out dbms_tf.table_t,
                    Excluded_Columns IN DBMS_TF.COLUMNS_T := null) return dbms_tf.describe_t As
    Type T_Excluded_Columns is table of Pls_Integer index By Varchar2(128);
  Begin

    For I in 1..Source_Table.Column.Count Loop
      Source_Table.Column(I).For_Read := True;
      Source_Table.Column(I).Pass_Through := True;

      if Source_Table.column(I).description.name member of excluded_columns then
        Source_Table.Column(I).For_Read := False;
      End if;

    End Loop;

    -- add a new column to output of query/table
    -- different values will be listed as string in this column
    Return Dbms_Tf.Describe_T(New_Columns => Dbms_Tf.Columns_New_T(1 => Dbms_Tf.Column_Metadata_T(Name =>'DIFFERENT_COLUMS')));   
  End Describe;

  /*****************************************************************/

  Procedure Fetch_Rows as
    V_Row_Set           Dbms_Tf.Row_Set_T;
    V_Row_Count         PLS_Integer;
    V_New_Column_Values Dbms_Tf.Tab_Varchar2_T;
    V_Column_Count      PLS_Integer;
    V_Data              Varchar2(4000);
    V_Result            PLS_Integer;
    V_Current_Column_Value Varchar2(4000);

    Type T_Arr_Previous_Column_values is table of Varchar2(4000);
    V_Previous_Column_Values T_Arr_Previous_Column_values := T_Arr_Previous_Column_values();

    -- Local Procedure to check differences and add to a variable
    Procedure LP_Add_Different_Value_Data(P_Column varchar2, P_Old_Value varchar2, P_New_Value varchar2, P_Column_Number PLS_Integer) as
    Begin
      if P_Old_Value is Null     And P_New_Value is not null Or
         P_Old_Value is Not Null And P_New_Value is null Or
         P_Old_Value <> P_New_Value
      Then
        V_Data := Substr(V_Data || P_Column || ':' || Nvl(P_Old_Value, 'NULL') || '=>' || Nvl(P_New_Value, 'NULL') || ',', 1, 4000);
      End if;

      V_Previous_Column_Values(P_Column_Number) := P_New_Value;
    End LP_Add_Different_Value_Data;

    Function LF_Init_Previous_Row_Values(P_Row_Set Dbms_Tf.Row_Set_T) Return PLS_Integer As
      V_Result PLS_Integer;
    Begin      
      if Dbms_Tf.XStore_Exists('C1') then
        /* if this is not the first row set chunk 
           then read last row value from previous row set chunk */
        For i in 1..P_Row_Set.Count Loop
          Dbms_Tf.XStore_Get('C' || i, V_Previous_Column_Values(i));
        End Loop;

        V_Result := 1; -- there were a previous row on previous row set chunk
      Else
        /*if this is the first row set chunk,
          then assign current row value as previous row */
        V_New_Column_Values(1) := Null;
        for k in 1..V_Previous_Column_Values.Count Loop
          Case P_Row_Set(k).Description.Type
            When Dbms_Tf.Type_Varchar2 Then
              V_Previous_Column_Values(k) := P_Row_Set(k).Tab_Varchar2(1);
            When Dbms_Tf.Type_Char Then
              V_Previous_Column_Values(k) := P_Row_Set(k).Tab_Varchar2(1);
            When Dbms_Tf.Type_Number Then
              V_Previous_Column_Values(k) := To_Char(P_Row_Set(k).Tab_Number(1));
            When Dbms_Tf.Type_Date Then
              V_Previous_Column_Values(k) := To_Char(P_Row_Set(k).Tab_Date(1));
            When Dbms_Tf.Type_Timestamp Then
              V_Previous_Column_Values(k) := To_Char(P_Row_Set(k).Tab_Timestamp(1));
          End Case;
        End Loop;
        
        V_Result := 0; -- this is the first row set chunk
      End if;    

      Return V_Result;
    End LF_Init_Previous_Row_Values;
    
    Function LF_Get_Current_Column_Value(P_Col Dbms_Tf.Column_Data_T, P_Row_Number PLS_Integer) Return Varchar2 As
    Begin
      Return Case P_Col.Description.Type
               When Dbms_Tf.Type_Varchar2  Then P_Col.Tab_Varchar2(P_Row_Number)
               When Dbms_Tf.Type_Char      Then P_Col.Tab_Varchar2(P_Row_Number)
               When Dbms_Tf.Type_Number    Then To_Char(P_Col.Tab_Number(P_Row_Number))
               When Dbms_Tf.Type_Date      Then To_Char(P_Col.Tab_Date(P_Row_Number))
               When Dbms_Tf.Type_Timestamp Then To_Char(P_Col.Tab_Timestamp(P_Row_Number))
             End;
    End;

  Begin
    /* get result of the query and generate new column values */ 
    Dbms_Tf.Get_Row_Set(V_Row_Set, V_Row_Count, V_Column_Count);

    V_Previous_Column_Values.Extend(V_Column_Count);

    -- loop for every row
    for i in 1..V_Row_Count Loop
      V_Data := Null;
      
      if i = 1 Then
        V_Result := LF_Init_Previous_Row_Values(V_Row_Set);

        Continue When V_Result = 0; -- this is the first row set chunk so this is the first row, no previous
      End if;

      --loop for all columns in base table/query 
      --and check if value is different in previous row
      for k in 1..V_Column_Count Loop
        --get current column value as varchar2
        V_Current_Column_Value := LF_Get_Current_Column_Value(V_Row_Set(k), i);

        --check column type and use related collection to read data 
        --and if previous row data is different than current add it to string
        LP_Add_Different_Value_Data(P_Column        => V_Row_Set(k).Description.Name, 
                                    P_Old_Value     => V_Previous_Column_Values(k),
                                    P_New_Value     => V_Current_Column_Value,
                                    P_Column_Number => k);
      End Loop; -- column loop

      V_New_Column_Values(i) := V_Data;
    End Loop; -- row loop

    /* set last row data into xstore so it can be read in next row set chunk */
    For i in 1..V_Column_Count Loop
      Dbms_Tf.XStore_Set('C' || i, V_Previous_Column_Values(i));
    End Loop;

    --assign generated column values to first new column.
    Dbms_Tf.Put_Col(1, V_New_Column_Values);
  exception
    when others then
      dbms_output.put_line('ERROR: ' || sqlerrm || dbms_utility.format_error_Backtrace);
      Raise;
  End Fetch_Rows;

end;
/

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

create or replace Package Pkg_Column_Value_Differencies as

/* Written by Mustafa KALAYCI

mustafakalayci.me

find different values of columns with previous rows in query order */

Function SF_Find_Differences (Source_Table in Table,

Excluded_Columns IN DBMS_TF.COLUMNS_T := null)

Return Table Pipelined

Row Polymorphic Using Pkg_Column_Value_Differencies;

Function Describe(Source_Table in out dbms_tf.table_t,

Excluded_Columns IN DBMS_TF.COLUMNS_T := null) return dbms_tf.describe_t;

Procedure Fetch_Rows;

End;

create or replace Package Body Pkg_Column_Value_differencies As

/* Written by Mustafa KALAYCI

mustafakalayci.me

find different values of columns with previous rows in query order */

Function Describe(Source_Table in out dbms_tf.table_t,

Excluded_Columns IN DBMS_TF.COLUMNS_T := null) return dbms_tf.describe_t As

Type T_Excluded_Columns is table of Pls_Integer index By Varchar2(128);

Begin

For I in 1..Source_Table.Column.Count Loop

Source_Table.Column(I).For_Read := True;

Source_Table.Column(I).Pass_Through := True;

if Source_Table.column(I).description.name member of excluded_columns then

Source_Table.Column(I).For_Read := False;

End if;

End Loop;

-- add a new column to output of query/table

-- different values will be listed as string in this column

Return Dbms_Tf.Describe_T(New_Columns => Dbms_Tf.Columns_New_T(1 => Dbms_Tf.Column_Metadata_T(Name =>'DIFFERENT_COLUMS')));

End Describe;

/*****************************************************************/

Procedure Fetch_Rows as

V_Row_Set Dbms_Tf.Row_Set_T;

V_Row_Count PLS_Integer;

V_New_Column_Values Dbms_Tf.Tab_Varchar2_T;

V_Column_Count PLS_Integer;

V_Data Varchar2(4000);

V_Result PLS_Integer;

V_Current_Column_Value Varchar2(4000);

Type T_Arr_Previous_Column_values is table of Varchar2(4000);

V_Previous_Column_Values T_Arr_Previous_Column_values := T_Arr_Previous_Column_values();

-- Local Procedure to check differences and add to a variable

Procedure LP_Add_Different_Value_Data(P_Column varchar2, P_Old_Value varchar2, P_New_Value varchar2, P_Column_Number PLS_Integer) as

Begin

if P_Old_Value is Null And P_New_Value is not null Or

P_Old_Value is Not Null And P_New_Value is null Or

P_Old_Value <> P_New_Value

Then

V_Data := Substr(V_Data || P_Column || ':' || Nvl(P_Old_Value, 'NULL') || '=>' || Nvl(P_New_Value, 'NULL') || ',', 1, 4000);

End if;

V_Previous_Column_Values(P_Column_Number) := P_New_Value;

End LP_Add_Different_Value_Data;

Function LF_Init_Previous_Row_Values(P_Row_Set Dbms_Tf.Row_Set_T) Return PLS_Integer As

V_Result PLS_Integer;

Begin

if Dbms_Tf.XStore_Exists('C1') then

/* if this is not the first row set chunk

then read last row value from previous row set chunk */

For i in 1..P_Row_Set.Count Loop

Dbms_Tf.XStore_Get('C' || i, V_Previous_Column_Values(i));

End Loop;

V_Result := 1; -- there were a previous row on previous row set chunk

Else

/*if this is the first row set chunk,

then assign current row value as previous row */

V_New_Column_Values(1) := Null;

for k in 1..V_Previous_Column_Values.Count Loop

Case P_Row_Set(k).Description.Type

When Dbms_Tf.Type_Varchar2 Then

V_Previous_Column_Values(k) := P_Row_Set(k).Tab_Varchar2(1);

When Dbms_Tf.Type_Char Then

V_Previous_Column_Values(k) := P_Row_Set(k).Tab_Varchar2(1);

When Dbms_Tf.Type_Number Then

V_Previous_Column_Values(k) := To_Char(P_Row_Set(k).Tab_Number(1));

When Dbms_Tf.Type_Date Then

V_Previous_Column_Values(k) := To_Char(P_Row_Set(k).Tab_Date(1));

When Dbms_Tf.Type_Timestamp Then

V_Previous_Column_Values(k) := To_Char(P_Row_Set(k).Tab_Timestamp(1));

End Case;

End Loop;

V_Result := 0; -- this is the first row set chunk

End if;

Return V_Result;

End LF_Init_Previous_Row_Values;

Function LF_Get_Current_Column_Value(P_Col Dbms_Tf.Column_Data_T, P_Row_Number PLS_Integer) Return Varchar2 As

Begin

Return Case P_Col.Description.Type

When Dbms_Tf.Type_Varchar2 Then P_Col.Tab_Varchar2(P_Row_Number)

When Dbms_Tf.Type_Char Then P_Col.Tab_Varchar2(P_Row_Number)

When Dbms_Tf.Type_Number Then To_Char(P_Col.Tab_Number(P_Row_Number))

When Dbms_Tf.Type_Date Then To_Char(P_Col.Tab_Date(P_Row_Number))

When Dbms_Tf.Type_Timestamp Then To_Char(P_Col.Tab_Timestamp(P_Row_Number))

End;

Begin

/* get result of the query and generate new column values */

Dbms_Tf.Get_Row_Set(V_Row_Set, V_Row_Count, V_Column_Count);

V_Previous_Column_Values.Extend(V_Column_Count);

-- loop for every row

for i in 1..V_Row_Count Loop

V_Data := Null;

if i = 1 Then

V_Result := LF_Init_Previous_Row_Values(V_Row_Set);

Continue When V_Result = 0; -- this is the first row set chunk so this is the first row, no previous

End if;

--loop for all columns in base table/query

--and check if value is different in previous row

for k in 1..V_Column_Count Loop

--get current column value as varchar2

V_Current_Column_Value := LF_Get_Current_Column_Value(V_Row_Set(k), i);

--check column type and use related collection to read data

--and if previous row data is different than current add it to string

LP_Add_Different_Value_Data(P_Column => V_Row_Set(k).Description.Name,

P_Old_Value => V_Previous_Column_Values(k),

P_New_Value => V_Current_Column_Value,

P_Column_Number => k);

End Loop; -- column loop

V_New_Column_Values(i) := V_Data;

End Loop; -- row loop

/* set last row data into xstore so it can be read in next row set chunk */

For i in 1..V_Column_Count Loop

Dbms_Tf.XStore_Set('C' || i, V_Previous_Column_Values(i));

End Loop;

--assign generated column values to first new column.

Dbms_Tf.Put_Col(1, V_New_Column_Values);

exception

when others then

dbms_output.put_line('ERROR: ' || sqlerrm || dbms_utility.format_error_Backtrace);

Raise;

End Fetch_Rows;

end;

this package has it’s own polymorphic table functions (SF_Find_Differences) which has two parameter, first is the table or query that you want to find differences with previous rows. By saying “previous” I am using default order what you provided to my PTF I am not ordering data in any way. I will explain second parameter later. let’s make an example:

/********** SAMPLE DATA **************/
drop table emp purge;
exec dbms_lock.sleep(10);

create table emp as select * from hr.employees;
commit;
exec dbms_lock.sleep(10);
update emp set first_name = 'Mustafa' where employee_id = 100;
commit;
exec dbms_lock.sleep(10);
update emp set last_name = 'KALAYCI' where employee_id = 100;
commit;
exec dbms_lock.sleep(10);
update emp set salary = 35000, commission_pct = 0.6 where employee_id = 100; 
commit;

/********** SAMPLE DATA **************/

drop table emp purge;

exec dbms_lock.sleep(10);

create table emp as select * from hr.employees;

commit;

exec dbms_lock.sleep(10);

update emp set first_name = 'Mustafa' where employee_id = 100;

commit;

exec dbms_lock.sleep(10);

update emp set last_name = 'KALAYCI' where employee_id = 100;

commit;

exec dbms_lock.sleep(10);

update emp set salary = 35000, commission_pct = 0.6 where employee_id = 100;

commit;

this will be my sample data (flashback query). one problem is while you are looking for a historical data mostly you will be checking one specified row history not all rows. so you must filter rows using primary key or something. Unfortunately you can not pass a “subquery” to PTF but you can pass a WITH data source.

So, I just created a dummy emp table and then updated employee with id 100. first change first_name then last_name then salary and commission_pct both. here is my query:

with history_data as (
    select versions_starttime, versions_endtime, 
           e.employee_id as id, e.first_name, e.last_name,
           e.salary, e.commission_pct
    from emp versions between scn minvalue and maxvalue e 
    where employee_id = 100 order by versions_endtime
  )
select * 
from Pkg_Column_Value_Differencies.SF_Find_Differences(history_data);


VERSIONS_STARTTIME     VERSIONS_ENDTIME       ID  FIRST_NAME  LAST_NAME  SALARY COMMISSION_PCT  DIFFERENT_COLUMS
NULL                   01-NOV-20 11.58.31 PM  100 Steven      King       24000  NULL            NULL
01-NOV-20 11.58.31 PM  01-NOV-20 11.58.40 PM  100 Mustafa     King       24000  NULL            "VERSIONS_STARTTIME":NULL=>01-NOV-20 11.58.31.014177000 PM,"VERSIONS_ENDTIME":01-NOV-20 11.58.31.000000000 PM=>01-NOV-20 11.58.40.000000000 PM,"FIRST_NAME":Steven=>Mustafa,
01-NOV-20 11.58.40 PM  01-NOV-20 11.58.52 PM  100 Mustafa     KALAYCI    24000  NULL            "VERSIONS_STARTTIME":01-NOV-20 11.58.31.014177000 PM=>01-NOV-20 11.58.40.014177000 PM,"VERSIONS_ENDTIME":01-NOV-20 11.58.40.000000000 PM=>01-NOV-20 11.58.52.000000000 PM,"LAST_NAME":King=>KALAYCI,
01-NOV-20 11.58.52 PM  NULL                   100 Mustafa     KALAYCI    35000  .6              "VERSIONS_STARTTIME":01-NOV-20 11.58.40.014177000 PM=>01-NOV-20 11.58.52.014177000 PM,"VERSIONS_ENDTIME":01-NOV-20 11.58.52.000000000 PM=>NULL,"SALARY":24000=>35000,"COMMISSION_PCT":NULL=>.6,

with history_data as (

select versions_starttime, versions_endtime,

e.employee_id as id, e.first_name, e.last_name,

e.salary, e.commission_pct

from emp versions between scn minvalue and maxvalue e

where employee_id = 100 order by versions_endtime

)

select *

from Pkg_Column_Value_Differencies.SF_Find_Differences(history_data);

VERSIONS_STARTTIME VERSIONS_ENDTIME ID FIRST_NAME LAST_NAME SALARY COMMISSION_PCT DIFFERENT_COLUMS

NULL 01-NOV-20 11.58.31 PM 100 Steven King 24000 NULL NULL

01-NOV-20 11.58.31 PM 01-NOV-20 11.58.40 PM 100 Mustafa King 24000 NULL "VERSIONS_STARTTIME":NULL=>01-NOV-20 11.58.31.014177000 PM,"VERSIONS_ENDTIME":01-NOV-20 11.58.31.000000000 PM=>01-NOV-20 11.58.40.000000000 PM,"FIRST_NAME":Steven=>Mustafa,

01-NOV-20 11.58.40 PM 01-NOV-20 11.58.52 PM 100 Mustafa KALAYCI 24000 NULL "VERSIONS_STARTTIME":01-NOV-20 11.58.31.014177000 PM=>01-NOV-20 11.58.40.014177000 PM,"VERSIONS_ENDTIME":01-NOV-20 11.58.40.000000000 PM=>01-NOV-20 11.58.52.000000000 PM,"LAST_NAME":King=>KALAYCI,

01-NOV-20 11.58.52 PM NULL 100 Mustafa KALAYCI 35000 .6 "VERSIONS_STARTTIME":01-NOV-20 11.58.40.014177000 PM=>01-NOV-20 11.58.52.014177000 PM,"VERSIONS_ENDTIME":01-NOV-20 11.58.52.000000000 PM=>NULL,"SALARY":24000=>35000,"COMMISSION_PCT":NULL=>.6,

I think this is so cool 🙂 if you check the last column it will be listing what is changed (which columns). As you can see I use a WITH clause to define specific historical data and order it using VERSIONS_ENDTIME. One problem is, I wanted to see when these changes happend (versions_starttime and endtime) but since they are also columns in this query my DIFFERENT_COLUMNS has those changed columns too! that is where the second parameter gets involved. second parameter is EXCLUDED column list from change control. so:

with history_data as (
    select versions_starttime, versions_endtime, 
           e.employee_id as id, e.first_name, e.last_name,
           e.salary, e.commission_pct
    from emp versions between scn minvalue and maxvalue e 
    where employee_id = 100 order by versions_endtime
  )
select * 
from Pkg_Column_Value_Differencies.SF_Find_Differences(history_data, columns(versions_starttime, versions_endtime));


VERSIONS_STARTTIME     VERSIONS_ENDTIME       ID  FIRST_NAME  LAST_NAME  SALARY COMMISSION_PCT  DIFFERENT_COLUMS
NULL                   01-NOV-20 11.58.31 PM  100 Steven      King       24000  NULL            NULL
01-NOV-20 11.58.31 PM  01-NOV-20 11.58.40 PM  100 Mustafa     King       24000  NULL            "FIRST_NAME":Steven=>Mustafa,
01-NOV-20 11.58.40 PM  01-NOV-20 11.58.52 PM  100 Mustafa     KALAYCI    24000  NULL            "LAST_NAME":King=>KALAYCI,
01-NOV-20 11.58.52 PM  NULL                   100 Mustafa     KALAYCI    35000  .6              "SALARY":24000=>35000,"COMMISSION_PCT":NULL=>.6,

with history_data as (

select versions_starttime, versions_endtime,

e.employee_id as id, e.first_name, e.last_name,

e.salary, e.commission_pct

from emp versions between scn minvalue and maxvalue e

where employee_id = 100 order by versions_endtime

)

select *

from Pkg_Column_Value_Differencies.SF_Find_Differences(history_data, columns(versions_starttime, versions_endtime));

VERSIONS_STARTTIME VERSIONS_ENDTIME ID FIRST_NAME LAST_NAME SALARY COMMISSION_PCT DIFFERENT_COLUMS

NULL 01-NOV-20 11.58.31 PM 100 Steven King 24000 NULL NULL

01-NOV-20 11.58.31 PM 01-NOV-20 11.58.40 PM 100 Mustafa King 24000 NULL "FIRST_NAME":Steven=>Mustafa,

01-NOV-20 11.58.40 PM 01-NOV-20 11.58.52 PM 100 Mustafa KALAYCI 24000 NULL "LAST_NAME":King=>KALAYCI,

01-NOV-20 11.58.52 PM NULL 100 Mustafa KALAYCI 35000 .6 "SALARY":24000=>35000,"COMMISSION_PCT":NULL=>.6,

much clear. on the second row, Steven becomes Mustafa, on the third row, King becomes KALAYCI and on the last one, salary and commission_pct are changed. it is more easier to track it or listing in your application.

Of course this is not a complete code, I just checked for varchar2, number, date and timestamp columns but not the others. Also didn’t work on “formatting” like date and timestamp data types which can be done but still pretty nice.

I wanted to share this with whole community. I hope this helps.

Edit: while checking whether old value and new value are different or not, I used if conditions but I would like to use SYS_OP_MAP_NONNULL function. unfortunately SYS_OP_MAP_NONNULL is not defined in plsql.

Edit: I made a change about excluding columns. instead of for loop I used “member of” clause.

wish you healthy corona & earthquake free days. Unfortunately, an earthquake happened in my country and neighbor countries. Rescue teams are still trying to save people. I hope this never happens in any place and no one experience this pain.

CSV Data in Clob to Columns Using Select

Published on: 2020-07-142020-07-14 by Mustafa Comment

Hi,

Thanks to one of my customers, I am dealing with csv data in clob lately a lot. so they asked me if I can get a csv data in a clob column as separated columns using select statement. They wanted to show this data on their applicaitons. I suggested different methods but they said this won’t be used much just once or twice on a month. so I created this code and wanted to share with you:

drop function Sf_Csv_To_Table;
drop  type t_csv_to_table_arr ;
drop type t_csv_to_table_row; 


create type t_csv_to_table_row is object (
  c1 varchar2(1500),
  c2 varchar2(1500),
  c3 varchar2(1500),
  c4 varchar2(1500),
  c5 varchar2(1500),
  c6 varchar2(1500),
  c7 varchar2(1500),
  c8 varchar2(1500),
  c9 varchar2(1500),
  c10 varchar2(1500),
  c11 varchar2(1500),
  c12 varchar2(1500),
  c13 varchar2(1500),
  c14 varchar2(1500),
  c15 varchar2(1500),
  c16 varchar2(1500),
  c17 varchar2(1500),
  c18 varchar2(1500),
  c19 varchar2(1500),
  c20 varchar2(1500),
  c21 varchar2(1500),
  c22 varchar2(1500),
  c23 varchar2(1500),
  c24 varchar2(1500),
  c25 varchar2(1500),
  c26 varchar2(1500),
  c27 varchar2(1500),
  c28 varchar2(1500),
  c29 varchar2(1500),
  c30 varchar2(1500),
  c31 varchar2(1500),
  c32 varchar2(1500),
  c33 varchar2(1500),
  c34 varchar2(1500),
  c35 varchar2(1500),
  c36 varchar2(1500),
  c37 varchar2(1500),
  c38 varchar2(1500),
  c39 varchar2(1500),
  c40 varchar2(1500),
  c41 varchar2(1500),
  c42 varchar2(1500),
  c43 varchar2(1500),
  c44 varchar2(1500),
  c45 varchar2(1500),
  c46 varchar2(1500),
  c47 varchar2(1500),
  c48 varchar2(1500),
  c49 varchar2(1500),
  c50 varchar2(1500),
  c51 varchar2(1500),
  c52 varchar2(1500),
  c53 varchar2(1500),
  c54 varchar2(1500),
  c55 varchar2(1500),
  c56 varchar2(1500),
  c57 varchar2(1500),
  c58 varchar2(1500),
  c59 varchar2(1500),
  c60 varchar2(1500),
  c61 varchar2(1500),
  c62 varchar2(1500),
  c63 varchar2(1500),
  c64 varchar2(1500),
  c65 varchar2(1500),
  c66 varchar2(1500),
  c67 varchar2(1500),
  c68 varchar2(1500),
  c69 varchar2(1500),
  c70 varchar2(1500),
  c71 varchar2(1500),
  c72 varchar2(1500),
  c73 varchar2(1500),
  c74 varchar2(1500),
  c75 varchar2(1500),
  c76 varchar2(1500),
  c77 varchar2(1500),
  c78 varchar2(1500),
  c79 varchar2(1500),
  c80 varchar2(1500),
  CONSTRUCTOR FUNCTION t_csv_to_table_row(SELF IN OUT NOCOPY t_csv_to_table_row) return self as result
);
/
  
Create Type Body  t_csv_to_table_row as
  CONSTRUCTOR FUNCTION t_csv_to_table_row(SELF IN OUT NOCOPY t_csv_to_table_row) return self as result is
  begin
    return;
  end;
End;
/

create type t_csv_to_table_arr is table of t_csv_to_table_row ;
/
  
create or replace function Sf_Csv_To_Table(p_clob clob) return t_csv_to_table_arr pipelined as
  v_size             number;
  v_start_pos        number := 1;
  v_new_position     number := 0;
  v_string_line      Varchar2(32767);
  v_line             t_csv_to_table_row ;
  v_line_separator   varchar2(10) := chr(10);
  v_clob             clob := p_clob ;
  
  v_column_data_assignment clob;
  v_column_script          Clob := '
declare
  V_String_Line varchar2(32767)    := :StringLine;
  v_line        t_csv_to_table_row := t_csv_to_table_row();
begin
  <<columns>>
  :OutRow := v_line;
end;';
    
  Procedure IP_Init_Column_Script as
  Begin
    For i in 1..80 Loop
      v_column_data_assignment := v_column_data_assignment 
        || '  v_line.C'||to_char(i) ||' := Regexp_Substr(V_String_Line, ''"(.*?)"'',1,'||to_char(i)||',''i'',1);';
    End Loop;
    v_column_script := Replace (v_column_script, '<<columns>>', v_column_data_assignment);
  End IP_Init_Column_Script;
    
    
begin
  IP_Init_Column_Script;
  
  v_size := dbms_lob.getlength(v_clob);
  while v_start_pos <= v_size loop
    v_new_position := nvl(instr(v_clob, v_line_separator, v_start_pos), 4000);
    v_string_line  := substr(v_clob, v_start_pos , v_new_position-v_start_pos );
    
    V_Line := t_csv_to_table_row();
    Execute immediate v_column_script using V_String_Line, out V_Line;
    Pipe Row(v_line);      
    
    v_start_pos    := v_new_position+length(v_line_separator);

  end loop;
  return;
end;
/

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

drop function Sf_Csv_To_Table;

drop type t_csv_to_table_arr ;

drop type t_csv_to_table_row;

create type t_csv_to_table_row is object (

c1 varchar2(1500),

c2 varchar2(1500),

c3 varchar2(1500),

c4 varchar2(1500),

c5 varchar2(1500),

c6 varchar2(1500),

c7 varchar2(1500),

c8 varchar2(1500),

c9 varchar2(1500),

c10 varchar2(1500),

c11 varchar2(1500),

c12 varchar2(1500),

c13 varchar2(1500),

c14 varchar2(1500),

c15 varchar2(1500),

c16 varchar2(1500),

c17 varchar2(1500),

c18 varchar2(1500),

c19 varchar2(1500),

c20 varchar2(1500),

c21 varchar2(1500),

c22 varchar2(1500),

c23 varchar2(1500),

c24 varchar2(1500),

c25 varchar2(1500),

c26 varchar2(1500),

c27 varchar2(1500),

c28 varchar2(1500),

c29 varchar2(1500),

c30 varchar2(1500),

c31 varchar2(1500),

c32 varchar2(1500),

c33 varchar2(1500),

c34 varchar2(1500),

c35 varchar2(1500),

c36 varchar2(1500),

c37 varchar2(1500),

c38 varchar2(1500),

c39 varchar2(1500),

c40 varchar2(1500),

c41 varchar2(1500),

c42 varchar2(1500),

c43 varchar2(1500),

c44 varchar2(1500),

c45 varchar2(1500),

c46 varchar2(1500),

c47 varchar2(1500),

c48 varchar2(1500),

c49 varchar2(1500),

c50 varchar2(1500),

c51 varchar2(1500),

c52 varchar2(1500),

c53 varchar2(1500),

c54 varchar2(1500),

c55 varchar2(1500),

c56 varchar2(1500),

c57 varchar2(1500),

c58 varchar2(1500),

c59 varchar2(1500),

c60 varchar2(1500),

c61 varchar2(1500),

c62 varchar2(1500),

c63 varchar2(1500),

c64 varchar2(1500),

c65 varchar2(1500),

c66 varchar2(1500),

c67 varchar2(1500),

c68 varchar2(1500),

c69 varchar2(1500),

c70 varchar2(1500),

c71 varchar2(1500),

c72 varchar2(1500),

c73 varchar2(1500),

c74 varchar2(1500),

c75 varchar2(1500),

c76 varchar2(1500),

c77 varchar2(1500),

c78 varchar2(1500),

c79 varchar2(1500),

c80 varchar2(1500),

CONSTRUCTOR FUNCTION t_csv_to_table_row(SELF IN OUT NOCOPY t_csv_to_table_row) return self as result

);

Create Type Body t_csv_to_table_row as

CONSTRUCTOR FUNCTION t_csv_to_table_row(SELF IN OUT NOCOPY t_csv_to_table_row) return self as result is

begin

return;

end;

End;

create type t_csv_to_table_arr is table of t_csv_to_table_row ;

create or replace function Sf_Csv_To_Table(p_clob clob) return t_csv_to_table_arr pipelined as

v_size number;

v_start_pos number := 1;

v_new_position number := 0;

v_string_line Varchar2(32767);

v_line t_csv_to_table_row ;

v_line_separator varchar2(10) := chr(10);

v_clob clob := p_clob ;

v_column_data_assignment clob;

v_column_script Clob := '

declare

V_String_Line varchar2(32767) := :StringLine;

v_line t_csv_to_table_row := t_csv_to_table_row();

begin

<<columns>>

:OutRow := v_line;

end;';

Procedure IP_Init_Column_Script as

Begin

For i in 1..80 Loop

v_column_data_assignment := v_column_data_assignment

|| ' v_line.C'||to_char(i) ||' := Regexp_Substr(V_String_Line, ''"(.*?)"'',1,'||to_char(i)||',''i'',1);';

End Loop;

v_column_script := Replace (v_column_script, '<<columns>>', v_column_data_assignment);

End IP_Init_Column_Script;

begin

IP_Init_Column_Script;

v_size := dbms_lob.getlength(v_clob);

while v_start_pos <= v_size loop

v_new_position := nvl(instr(v_clob, v_line_separator, v_start_pos), 4000);

v_string_line := substr(v_clob, v_start_pos , v_new_position-v_start_pos );

V_Line := t_csv_to_table_row();

Execute immediate v_column_script using V_String_Line, out V_Line;

Pipe Row(v_line);

v_start_pos := v_new_position+length(v_line_separator);

end loop;

return;

end;

I am using a pipe lined function in this solution. I created a type to define columns with 80 columns (c1 to c80) then my pipe lined function gets the clob value and parse it to first “lines” then “columns”. On this problem, we have some strict rules like every line separated by new line (chr(10)) and every column separated by a comma and every column surrounded by double quotes. So I made them hard coded (if I find time I will make them dynamic).

I also used a small dynamic sql to assign column values because I am lazy and didn’t want to write 80 rows like “v_line.c1 := regexp_substr….”

In addition, I created a “constructor function” to my type because I want to use this:

” v_line t_csv_to_table_row := t_csv_to_table_row();”

if I didn’t create a constructor function then I have to add 80 NULL at the statement about like this:

” v_line t_csv_to_table_row := t_csv_to_table_row(null,null,null,……..null);”

so result is:

select * 
from   Sf_Csv_To_Table('"row1", "1","test data"' || chr(10)||
                       '"row2", "2","test data2","new column on 2"' || chr(10)||
                       '"row3", "3","test data3","","more new column"' || chr(10)||
                       '"row4", "4","test data4","",""' || chr(10));

select *

from Sf_Csv_To_Table('"row1", "1","test data"' || chr(10)||

'"row2", "2","test data2","new column on 2"' || chr(10)||

'"row3", "3","test data3","","more new column"' || chr(10)||

'"row4", "4","test data4","",""' || chr(10));

result:

as you see my query will always return 80 columns and columns data will be assigned from csv data. if there is no enough column in csv then remaining columns will be null.

using a polymorphic function could be better option on this but since I had limited time I produced a quick solution.

hope it helps.

Parsing CSV Lines and columns in PLSQL

Published on: 2020-06-032020-06-03 by Mustafa Comment

Hi,

Yesterday, one of my customers asked for help to tune a procedure and when I investigated the problem I find out that their method to get a specific “column” value in csv file was the problem. So we discuss the situation and adopt a new version of the code. While doing that I just create a pipe lined function to get same data just for fun and wanted to share it here.

So let’s say have a CSV (comma separated values) file and you need to parse it line by line and maybe even you need to get the “columns” (and by saying columns I meant every comma separated values in a line of csv file) row by row. To achieve that I created a pipe lined function, here it is:

create or replace function sf_parse(p_clob clob, p_separator varchar2) return sys.ODCIVARCHAR2LIST pipelined as
  v_size number;
  v_start_pos number := 1;
  v_new_position number := 0;
  v_line varchar2(4000);
  x_clob clob := p_clob || to_clob(p_separator);
begin
  v_size := dbms_lob.getlength(x_clob);
  
  while v_start_pos <= v_size loop
    
    v_new_position := nvl(instr(x_clob, p_separator, v_start_pos), 4000);
    v_line := substr(x_clob, v_start_pos , v_new_position-v_start_pos );
    v_start_pos := v_new_position+length(p_separator);

    pipe row(v_line);
  end loop;
  
  return;
end;
/

create or replace function sf_parse(p_clob clob, p_separator varchar2) return sys.ODCIVARCHAR2LIST pipelined as

v_size number;

v_start_pos number := 1;

v_new_position number := 0;

v_line varchar2(4000);

x_clob clob := p_clob || to_clob(p_separator);

begin

v_size := dbms_lob.getlength(x_clob);

while v_start_pos <= v_size loop

v_new_position := nvl(instr(x_clob, p_separator, v_start_pos), 4000);

v_line := substr(x_clob, v_start_pos , v_new_position-v_start_pos );

v_start_pos := v_new_position+length(p_separator);

pipe row(v_line);

end loop;

return;

end;

this function gets csv data as clob and parse to lines by a separator. Definition of Line is quite simple but you must be careful the platform. On Windows OS every line is ended with a carriage return (chr(13)) and a new line character (chr(10)) but on Linux OS it is just a new line character so if your CSV file is created on Windows then you must use chr(13) || chr(10) as new line (or enter) character and on Linux just chr(10).

create table tmp_clob (id number, myclob clob);

insert into tmp_clob values 
(1,
  to_clob('1,my first line,some data,123') || to_clob(chr(10)) ||
  to_clob('2,my second line,some more data,456') || to_clob(chr(10)) ||
  to_clob('3,my third line,some much more data,789') 
);
commit;

select *
from sf_parse((select myclob from tmp_clob where id = 1), chr(10));
---------------------------------------------------------------------
1,my first line,some data,123
2,my second line,some more data,456
3,my third line,some much more data,789

create table tmp_clob (id number, myclob clob);

insert into tmp_clob values

(1,

to_clob('1,my first line,some data,123') || to_clob(chr(10)) ||

to_clob('2,my second line,some more data,456') || to_clob(chr(10)) ||

to_clob('3,my third line,some much more data,789')

);

commit;

select *

from sf_parse((select myclob from tmp_clob where id = 1), chr(10));

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

1,my first line,some data,123

2,my second line,some more data,456

3,my third line,some much more data,789

as you see we have all three lines. we can even take this further and use one of the lines as a input parameter of sf_parse:

select *
from sf_parse('1,my first line,some data,123', ',');
-------------------------------------------------------
1
my first line
some data
123

select *

from sf_parse('1,my first line,some data,123', ',');

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

my first line

some data

123

we have all “columns” in csv as rows for the first line. you don’t have copy and paste the line info of course:

select *
from   sf_parse((select myclob from tmp_clob where id = 1), chr(10)) MyLines,
       sf_parse(MyLines.Column_value, ',');
----------------------------------------------------
Column_value                                    Column_value_1
1,my first line,some data,123                   1
1,my first line,some data,123                   my first line
1,my first line,some data,123                   some data
1,my first line,some data,123                   123
2,my second line,some more data,456             2
2,my second line,some more data,456             my second line
2,my second line,some more data,456             some more data
2,my second line,some more data,456             456
3,my third line,some much more data,789         3
3,my third line,some much more data,789         my third line
3,my third line,some much more data,789         some much more data
3,my third line,some much more data,789         789

select *

from sf_parse((select myclob from tmp_clob where id = 1), chr(10)) MyLines,

sf_parse(MyLines.Column_value, ',');

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

Column_value Column_value_1

1,my first line,some data,123 1

1,my first line,some data,123 my first line

1,my first line,some data,123 some data

1,my first line,some data,123 123

2,my second line,some more data,456 2

2,my second line,some more data,456 my second line

2,my second line,some more data,456 some more data

2,my second line,some more data,456 456

3,my third line,some much more data,789 3

3,my third line,some much more data,789 my third line

3,my third line,some much more data,789 some much more data

3,my third line,some much more data,789 789

have a nice day.

PS: I still can not believe that we have to talk/protest about “racism” in 2020 and I am deeply sorry for days. Life matters. We always tend to believe that we are the superior creature but we can not even understand the importance of life. Remember George Floyd. Peace.

How to convert a LONG column to CLOB directly in a query

Published on: 2020-04-062020-04-06 by Mustafa Comment

Hi,

This is a big problem since LONG is a very painful data type. You cannot search in it, use it in CTAS (create table as), pass it to a plsql object as parameter etc. Oracle strongly suggest not to use a LONG column anymore but unfortunately there are many LONG columns in data dictionary views like DBA_TRIGGERS, DBA_TAB_COLS, DBA_MVIEWS, DBA_VIEWS, DBA_TAB_PARTITIONS… I struggled with DBA_TAB_PARTITIONS a lot for example. HIGH_VALUE column is a LONG column and some of my codes to maintain partition operations need that data to identify the necessary partitions. I did so many works to get that data and today, finally I have found a way to convert LONG data to CLOB directly in a query!

I love Oracle community (community.oracle.com). so many Gurus are answering to so many questions and I just learn a lot just by reading. I just saw a thread based on a different topic but a valuable member of community Odie_63 provides a brilliant answer: https://community.oracle.com/message/15591644#15591644

SYS_DBUriGen function is allow you to convert long to clob directly:

Select Owner, Trigger_Name, 
       Sys_Dburigen(Owner, Trigger_Name, Trigger_Body, 'text()').Getclob() As Trigger_Body
From All_Triggers Where Rownum < 10;

Select Owner, Trigger_Name,

Sys_Dburigen(Owner, Trigger_Name, Trigger_Body, 'text()').Getclob() As Trigger_Body

From All_Triggers Where Rownum < 10;

Trigger_Body is a LONG column. first parameters in the function (Owner, trigger_name) are required for uniqueness of the row and they must be columns in the table. you can find information about Sys_DbUriGen at the documentation: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SYS_DBURIGEN.html#GUID-ABA33BEB-F7B7-477B-9FF2-028D62768797

Of course performance is not much good but LONG data type is responsible of this. I hope Oracle will remove all LONG columns as soon as possible. even there is an idea thread for this at the community: https://community.oracle.com/ideas/3319

Thanks to community and Odie_63.

Insert multi line text data using sqlldr

Published on: 2019-07-052020-01-09 by Mustafa Comment

Hello Everyone,

I just answered an OTN question about inserting multi line data using sqlldr and simply wanted to share it here (https://community.oracle.com/thread/4278952)

so if have a data like this in your text file:

1,"this is
two line data", 10
2,"this is
three line
data", 11
3,"this is one line data", 12
4,this, 13

1,"this is

two line data", 10

2,"this is

three line

data", 11

3,"this is one line data", 12

4,this, 13

you need to do some extra work to accomplish this problem. In my example I have 4 rows but 2 of them includes multi line character data. By default sqlldr uses “newline” character (actually maybe saying enter character could be more appropriate) and every new line will be accepted as new row! To solve this problem you need to change your “row delimiter/terminator” but this will bring some extra burden because your current data file (csv, txt etc) is needed to be changed and your “new” row delimiter must be added to end of every row!

this is my table:

create table tmp_x(
  id1 number, 
  my_char_data varchar2(80),
  some_number number);

create table tmp_x(

id1 number,

my_char_data varchar2(80),

some_number number);

This row delimiter could be more than one character and it should be because row delimiter must not be seen in your original data. for example if you have comma in your data you can not use comma as row delimiter because every comma will be count as end of row. In my example I will use $%&/| as row delimiter.

First change your data file and add new delimiter to your data:

1,"this is
two line data",10$%&/|
2,"this is
three line
data",11$%&/|
3,"this is one line data",12$%&/|
4,this,13$%&/|

1,"this is

two line data",10$%&/|

2,"this is

three line

data",11$%&/|

3,"this is one line data",12$%&/|

4,this,13$%&/|

secondly, we need to specify this new delimiter in our control file:

LOAD DATA
infile a.csv "STR X'2425262F7C'"

truncate INTO TABLE tmp_x
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(id1,my_char_data,some_number)

LOAD DATA

infile a.csv "STR X'2425262F7C'"

truncate INTO TABLE tmp_x

FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'

(id1,my_char_data,some_number)

probably you already saw “STR X’2425262F7C'”. this is specifying new row delimiter. X means this is an hex code and 2425262F7C is the hex code of my delimiter $%&/|

how to produce this:

select UTL_RAW.cast_to_raw(‘$%&/|’) from dual;
————————
2425262F7C

so you can use any combination for your delimiter and just simply use utl_raw to generate hex code and use this in controlfile.

rest is same. just run sqlldr command with this control file and it will load the rows as expected.

select * from tmp_x;

       ID1 MY_CHAR_DATA               SOME_NUMBER
---------- -------------------------- -----------
         1 this is                    10
           two line data
         2 this is                    11
           three line
           data
         3 this is one line data      12
         4 this                       13

ID1 MY_CHAR_DATA SOME_NUMBER

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

1 this is 10

two line data

2 this is 11

three line

data

3 this is one line data 12

4 this 13

please consider that your data must be in another file than control file! you can not use “infile *” and BEGINDATA in this structure.

I hope this helps.

Edit 01/09/2020 : I changed sample data formating because of extra enter chars and double quote formating issues.