Saturday, March 2, 2024

Oracle: Loading lob data using sqlldr

Lab: Loading lob data using sqlldr

reference url: https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/loading-objects-oracle-sql-loader.html#GUID-E02C2828-ABD1-4B8D-9561-124D221B4BE3

LOB loading methods:

LOB Data in Predetermined Size Fields -- Fixed record/field length, each record and field within are limited by the bytes specified in controlfile

LOB Data in Delimited Fields -- Delimited, each record and field within are identified by delimiters

LOB Data in Length-Value Pair Fields -- combo of above, records are delimited and fields width are identified by thje length spec

For our lab we pick: LOB Data in Delimited Fields

Loading method: Inline and lobfile

Steps

Step 1) create the table to load the lob data

create table person_table

(

name varchar2(40)

,resume clob

);

output:

SQL> create table person_table

(

name varchar2(40)

,resume clob

); 2 3 4 5

Table created.

SQL>

Step 2) Check the table existance

Examine the table created:

desc person_Table

alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';

set lines 1200 pages 30000 colsep , time on timing on trim on trims on

col segment_name for a30

col segment_type for a30

col partition_name for a30

col TABLE_NAME for a30

col COLUMN_NAME for a15

col SEGMENT_NAME for a30

col INDEX_NAME for a30

select segment_name

,partition_name

,segment_type

,round(bytes/1024/1024,2) szmb

from user_segments

order by 1,2,3;

select *

from user_lobs

order by TABLE_NAME

,COLUMN_NAME;

Output:

SQL> desc person_Table

Name Null? Type

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

NAME VARCHAR2(40)

RESUME CLOB

00:57:39 SQL> select segment_name

,partition_name

,segment_type

,round(bytes/1024/1024,2) szmb

from user_segments

order by 1,2,3;00:57:44 2 00:57:44 3 00:57:44 4 00:57:44 5 00:57:44 6

SEGMENT_NAME ,PARTITION_NAME ,SEGMENT_TYPE , SZMB

------------------------------,------------------------------,------------------------------,----------

CSVTBL , ,TABLE , 23

OPDB1_GGTEST_TBL , ,TABLE , .06

PRODUCT , ,TABLE , .06

SALES ,SALES_Q1_2023 ,TABLE PARTITION , 8

SALES ,SALES_Q1_2024 ,TABLE PARTITION , 8

SALES ,SALES_Q2_2023 ,TABLE PARTITION , 8

SALES ,SALES_Q3_2023 ,TABLE PARTITION , 8

SALES ,SALES_Q4_2023 ,TABLE PARTITION , 8

8 rows selected.

Elapsed: 00:00:00.00

00:57:45 SQL> select *

from user_lobs

order by TABLE_NAME

,COLUMN_NAME;00:58:19 2 00:58:19 3 00:58:19 4

TABLE_NAME ,COLUMN_NAME ,SEGMENT_NAME ,TABLESPACE_NAME ,INDEX_NAME , CHUNK,PCTVERSION, RETENTION, FREEPOOLS,CACHE ,LOGGING,ENCR,COMPRE,DEDUPLICATION ,IN_,FORMAT ,PAR,SEC,SEG,RETENTI,RETENTION_VALUE

------------------------------,---------------,------------------------------,------------------------------,------------------------------,----------,----------,----------,----------,----------,-------,----,------,---------------,---,---------------,---,---,---,-------,---------------

PERSON_TABLE ,RESUME ,SYS_LOB0000073521C00002$$ ,OPDB1_GGTEST_TBSPC ,SYS_IL0000073521C00002$$ , 8192, , , ,NO ,YES ,NO ,NO ,NO ,YES,ENDIAN NEUTRAL ,NO ,YES,NO ,DEFAULT,

Elapsed: 00:00:00.01

00:58:20 SQL>

Step 3) Load lob using inline content:

-- firstload.ctl

LOAD DATA

INFILE 'firstdata.dat' "str '|'"

INTO TABLE person_table

FIELDS TERMINATED BY ','

(name CHAR(25),

"RESUME" CHAR(507) ENCLOSED BY '<startlob>' AND '<endlob>')

-- firstdata.dat

Julia Nayer,<startlob> Julia Nayer

500 Example Parkway

jnayer@example.com ... <endlob>

sqlldr OPDB1_GGTEST_USER/OPDB1_GGTEST_USER@OPDB1 control=firstload.ctl

Output:

[oracle@vcentos79-oracle-sa1 LobLab2]$ sqlldr OPDB1_GGTEST_USER/OPDB1_GGTEST_USER@OPDB1 control=firstload.ctl

SQL*Loader: Release 19.0.0.0.0 - Production on Sun Mar 3 01:01:20 2024

Version 19.3.0.0.0

Path used: Conventional

Commit point reached - logical record count 1

Table PERSON_TABLE:

1 Row successfully loaded.

Check the log file:

firstload.log

for more information about the load.

[oracle@vcentos79-oracle-sa1 LobLab2]$

Step 4) Verify the table contents

select * from person_Table;

alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';

set lines 1200 pages 30000 colsep , time on timing on trim on trims on

col segment_name for a30

col segment_type for a30

col partition_name for a30

col TABLE_NAME for a30

col COLUMN_NAME for a15

col SEGMENT_NAME for a30

col INDEX_NAME for a30

select segment_name

,partition_name

,segment_type

,round(bytes/1024/1024,2) szmb

from user_segments

order by 1,2,3;

select *

from user_lobs

order by TABLE_NAME

,COLUMN_NAME;

truncate table person_Table;

-- this is done in preperation for step 5

output:

01:03:25 SQL> 1

1* select * from person_Table

01:03:26 SQL> /

NAME ,RESUME

----------------------------------------,--------------------------------------------------------------------------------

Julia Nayer , Julia Nayer

, 500 Example Parkway

, jnayer@example.com ...

Elapsed: 00:00:00.01

01:03:26 SQL> select segment_name

,partition_name

,segment_type

,round(bytes/1024/1024,2) szmb

from user_segments

order by 1,2,3;01:03:59 2 01:03:59 3 01:03:59 4 01:03:59 5 01:03:59 6

SEGMENT_NAME ,PARTITION_NAME ,SEGMENT_TYPE , SZMB

------------------------------,------------------------------,------------------------------,----------

CSVTBL , ,TABLE , 23

OPDB1_GGTEST_TBL , ,TABLE , .06

PERSON_TABLE , ,TABLE , .06

PRODUCT , ,TABLE , .06

SALES ,SALES_Q1_2023 ,TABLE PARTITION , 8

SALES ,SALES_Q1_2024 ,TABLE PARTITION , 8

SALES ,SALES_Q2_2023 ,TABLE PARTITION , 8

SALES ,SALES_Q3_2023 ,TABLE PARTITION , 8

SALES ,SALES_Q4_2023 ,TABLE PARTITION , 8

SYS_IL0000073521C00002$$ , ,LOBINDEX , .06

SYS_LOB0000073521C00002$$ , ,LOBSEGMENT , .13

11 rows selected.

Elapsed: 00:00:00.00

01:04:00 SQL> select *

from user_lobs

order by TABLE_NAME

,COLUMN_NAME;01:04:46 2 01:04:46 3 01:04:46 4

TABLE_NAME ,COLUMN_NAME ,SEGMENT_NAME ,TABLESPACE_NAME ,INDEX_NAME , CHUNK,PCTVERSION, RETENTION, FREEPOOLS,CACHE ,LOGGING,ENCR,COMPRE,DEDUPLICATION ,IN_,FORMAT ,PAR,SEC,SEG,RETENTI,RETENTION_VALUE

PERSON_TABLE ,RESUME ,SYS_LOB0000073521C00002$$ ,OPDB1_GGTEST_TBSPC ,SYS_IL0000073521C00002$$ , 8192, , , ,NO ,YES ,NO ,NO ,NO ,YES,ENDIAN NEUTRAL ,NO ,YES,YES,DEFAULT,

Elapsed: 00:00:00.01

01:04:47 SQL> truncate table person_Table;

Table truncated.

Elapsed: 00:00:00.11

01:05:14 SQL> select * from person_Table;

no rows selected

Elapsed: 00:00:00.00

01:05:20 SQL>

Step 5) Load lob using lobfile content

-- secondload.ctl

LOAD DATA

INFILE 'secondload.dat'

INTO TABLE person_table

FIELDS TERMINATED BY ','

(name CHAR(20),

"RESUME" LOBFILE( CONSTANT 'jqresume.txt') CHAR(2000)

ENCLOSED BY "<startlob>" AND "<endlob>\n")

-- thirdload.dat

Johny Quest,

Speed Racer,

-- jqresume.txt

<startlob> Johny Quest

500 Oracle Parkway

... <endlob>

<startlob> Speed Racer

400 Oracle Parkway

... <endlob>

sqlldr OPDB1_GGTEST_USER/OPDB1_GGTEST_USER@OPDB1 control=secondload.ctl

select * from person_Table;

alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';

set lines 1200 pages 30000 colsep , time on timing on trim on trims on

col segment_name for a30

col segment_type for a30

col partition_name for a30

col TABLE_NAME for a30

col COLUMN_NAME for a15

col SEGMENT_NAME for a30

col INDEX_NAME for a30

select segment_name

,partition_name

,segment_type

,round(bytes/1024/1024,2) szmb

from user_segments

order by 1,2,3;

select *

from user_lobs

order by TABLE_NAME

,COLUMN_NAME;

output:

01:05:20 SQL> select * from person_Table;

NAME ,RESUME

----------------------------------------,--------------------------------------------------------------------------------

Johny Quest , Johny Quest

, 500 Oracle Parkway

, ...

Speed Racer , Speed Racer

, 400 Oracle Parkway

, ...

Elapsed: 00:00:00.01

01:09:41 SQL> select segment_name

,partition_name

,segment_type

,round(bytes/1024/1024,2) szmb

from user_segments

order by 1,2,3;01:09:52 2 01:09:52 3 01:09:52 4 01:09:52 5 01:09:52 6

SEGMENT_NAME ,PARTITION_NAME ,SEGMENT_TYPE , SZMB

------------------------------,------------------------------,------------------------------,----------

CSVTBL , ,TABLE , 23

OPDB1_GGTEST_TBL , ,TABLE , .06

PERSON_TABLE , ,TABLE , .06

PRODUCT , ,TABLE , .06

SALES ,SALES_Q1_2023 ,TABLE PARTITION , 8

SALES ,SALES_Q1_2024 ,TABLE PARTITION , 8

SALES ,SALES_Q2_2023 ,TABLE PARTITION , 8

SALES ,SALES_Q3_2023 ,TABLE PARTITION , 8

SALES ,SALES_Q4_2023 ,TABLE PARTITION , 8

SYS_IL0000073521C00002$$ , ,LOBINDEX , .06

SYS_LOB0000073521C00002$$ , ,LOBSEGMENT , .13

11 rows selected.

Elapsed: 00:00:00.01

01:09:53 SQL> select *

from user_lobs

order by TABLE_NAME

,COLUMN_NAME;01:10:02 2 01:10:02 3 01:10:02 4

TABLE_NAME ,COLUMN_NAME ,SEGMENT_NAME ,TABLESPACE_NAME ,INDEX_NAME , CHUNK,PCTVERSION, RETENTION, FREEPOOLS,CACHE ,LOGGING,ENCR,COMPRE,DEDUPLICATION ,IN_,FORMAT ,PAR,SEC,SEG,RETENTI,RETENTION_VALUE

PERSON_TABLE ,RESUME ,SYS_LOB0000073521C00002$$ ,OPDB1_GGTEST_TBSPC ,SYS_IL0000073521C00002$$ , 8192, , , ,NO ,YES ,NO ,NO ,NO ,YES,ENDIAN NEUTRAL ,NO ,YES,YES,DEFAULT,

Elapsed: 00:00:00.01

01:10:03 SQL>

Demo video:

Thank you!

Let us learn Oracle Database Administration skills

Saturday, March 2, 2024

Oracle: Loading lob data using sqlldr

LOB loading methods:

Steps

Step 1) create the table to load the lob data

Step 2) Check the table existance

Step 3) Load lob using inline content:

Step 4) Verify the table contents

Step 5) Load lob using lobfile content

No comments:

Post a Comment

Troubleshooting the “Cannot Generate SSPI Context” Error After SQL Server Migration