DBA Blogs

Connection between Mysql and SQL Developer Datamodeler

Tom Kyte - 8 hours 58 min ago
Hi Tom, Im trying to connect Oracle SQL Developer Datamodeler with Mysql but nothing work. I can connect Oracle SQL Developer with Mysql with an J Connector, but when I do the same steps on SQL Datamodeler (add an extension in the Third Party JDBC) ...
Categories: DBA Blogs

How to copy a DBMS_XMLDOM.domnode between DBMS_XMLDOM.domdocument objects

Tom Kyte - 8 hours 58 min ago
I'm having trouble using the DBMS_XMLDOM package. I can hardly find more online than the API (which isn't that great) and a few "here's how you build an XML document" introductions, with no tutorials on more advanced uses. I am trying to write a file...
Categories: DBA Blogs

Database Queries Slow After DB Re-Import

Tom Kyte - 8 hours 58 min ago
After initial creation of user and import of db, queries were fast. Used following commands: " CREATE USER TESTUSR IDENTIFIED BY TESTUSR default tablespace TESTTAB quota unlimited on TESTTAB ACCOUNT UNLOCK; GRANT CREATE SESSION TO TESTUSR; GRAN...
Categories: DBA Blogs

RMAN using dbms_pipe

Tom Kyte - 8 hours 58 min ago
Hello I wrote a PL/SQL package which allows me to control RMAN out of SQL*Plus using the RMAN syntax. To implement it I used the dbms_pipe/dbms_scheduler functionality which starts the external rman process on OS level and call the rman binary ...
Categories: DBA Blogs

Direct path read temp wait event issue

Tom Kyte - 8 hours 58 min ago
Hi Tom, I have series of job which are configured daily & every day its been completed within expected time 7 mins. But last 4 days back this job is behaving like any thing its taking more than 2 hours. Its going for more sort operations & waiting...
Categories: DBA Blogs

select from table type

Tom Kyte - 8 hours 58 min ago
I have created a type below is the code of the type <code>create or replace type tt_name as table of varchar2(250);</code> Now I want to perform select on this type in a plsql code select listagg(column_name,';') within group(order by colum...
Categories: DBA Blogs

SQLLoader DIRECT option and Unique Index

Hemant K Chitale - Mon, 2016-09-26 09:59
The DIRECT parameter for SQLLoader Command-Line enables Direct Path Load which uses a Direct Path API instead of a regular INSERT statement to load data into the target table.

However, one needs to know how it handles a Unique Index on the target table.
It actually leaves the  UNUSABLE if, duplicate values are loaded. The Index rebuild fails but the duplicate values remain in the table.

Here is a quick demo  (this in 12.1.0.2 MultiTenant). I first setup the target table with a Unique Index.

[oracle@ora12102 Desktop]$ sqlplus hemant/hemant@PDB1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 26 22:36:51 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Mon Sep 26 2016 22:26:16 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> show con_id

CON_ID
------------------------------
3
SQL> create table test_sqlldr_direct
2 (id_column number,
3 data_column varchar2(15))
4 /

Table created.

SQL> create unique index test_sqlldr_direct_u1 on test_sqlldr_direct(id_column);

Index created.

SQL> insert into test_sqlldr_direct values (1, 'First Row');

1 row created.

SQL> commit;

Commit complete.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$

Next, I setup the datafile with a duplicate record and the controlfile.

[oracle@ora12102 Desktop]$ ls -l
total 8
-rw-r--r-- 1 oracle oinstall 40 Sep 26 22:40 load_data.dat
-rw-r--r-- 1 oracle oinstall 165 Sep 26 22:45 load_control.ctl
[oracle@ora12102 Desktop]$ cat load_data.dat
2,'Second Row'
3,'Third Row'
3,'Oops !'
[oracle@ora12102 Desktop]$ cat load_control.ctl
LOAD DATA
INFILE load_data.dat
APPEND INTO TABLE TEST_SQLLDR_DIRECT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
id_column,
data_column)
[oracle@ora12102 Desktop]$

I am now ready to run a Direct Path Load.

[oracle@ora12102 Desktop]$ sqlldr hemant/hemant@pdb1 control=load_control.ctl direct=TRUE

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Sep 26 22:47:09 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Path used: Direct

Load completed - logical record count 3.

Table TEST_SQLLDR_DIRECT:
3 Rows successfully loaded.

Check the log file:
load_control.log
for more information about the load.
[oracle@ora12102 Desktop]$

What is that ? 3 rows loaded successfully ?  So, the duplicate row also did get loaded ?  Let's check the log file.

[oracle@ora12102 Desktop]$ ls -ltr
total 12
-rw-r--r-- 1 oracle oinstall 40 Sep 26 22:40 load_data.dat
-rw-r--r-- 1 oracle oinstall 165 Sep 26 22:45 load_control.ctl
-rw-r--r-- 1 oracle oinstall 1833 Sep 26 22:47 load_control.log
[oracle@ora12102 Desktop]$ cat load_control.log

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Sep 26 22:47:09 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Control File: load_control.ctl
Data File: load_data.dat
Bad File: load_data.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Direct

Table TEST_SQLLDR_DIRECT, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID_COLUMN FIRST * , O(") CHARACTER
DATA_COLUMN NEXT * , O(") CHARACTER

The following index(es) on table TEST_SQLLDR_DIRECT were processed:
index HEMANT.TEST_SQLLDR_DIRECT_U1 was made unusable due to:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Table TEST_SQLLDR_DIRECT:
3 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 3
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 1
Total stream buffers loaded by SQL*Loader load thread: 0

Run began on Mon Sep 26 22:47:09 2016
Run ended on Mon Sep 26 22:47:11 2016

Elapsed time was: 00:00:01.88
CPU time was: 00:00:00.01
[oracle@ora12102 Desktop]$

Did you notice the section in the log file that says :
The following index(es) on table TEST_SQLLDR_DIRECT were processed:
index HEMANT.TEST_SQLLDR_DIRECT_U1 was made unusable due to:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Apparently, the Index is left UNUSABLE.

[oracle@ora12102 Desktop]$ sqlplus hemant/hemant@pdb1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 26 22:50:51 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Mon Sep 26 2016 22:47:09 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select status from user_indexes
2 where index_name = 'TEST_SQLLDR_DIRECT_U1'
3 /

STATUS
--------
UNUSABLE

SQL> select * from test_sqlldr_direct order by 1;

ID_COLUMN DATA_COLUMN
---------- ---------------
1 First Row
2 'Second Row'
3 'Third Row'
3 'Oops !'

SQL> alter index test_sqlldr_direct_u1 rebuild;
alter index test_sqlldr_direct_u1 rebuild
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found


SQL>

We can see the duplicated row for ID_COLUMN=3 and find that the Index cannot be rebuilt.  Oracle has allowed duplicate rows to load and left the Index UNUSABLE.

So, if you are planning to use DIRECT=TRUE and have a Unique Index, make sure you check the status of the Index and/or check the Log file before you proceed with processing the data.


Conversely, here is how the data is handled without DIRECT=TRUE :.

SQL> truncate table test_sqlldr_direct;

Table truncated.

SQL> insert into test_sqlldr_direct values (1,'First Row');

1 row created.

SQL> select status from user_indexes
2 where index_name = 'TEST_SQLLDR_DIRECT_U1'
3 /

STATUS
--------
VALID

SQL>

[oracle@ora12102 Desktop]$ rm load_control.log
[oracle@ora12102 Desktop]$ sqlldr hemant/hemant@PDB1 control=load_control.ctl

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Sep 26 22:59:58 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Path used: Conventional
Commit point reached - logical record count 3

Table TEST_SQLLDR_DIRECT:
2 Rows successfully loaded.

Check the log file:
load_control.log
for more information about the load.
[oracle@ora12102 Desktop]$
[oracle@ora12102 Desktop]$ ls -ltr
total 16
-rw-r--r-- 1 oracle oinstall 40 Sep 26 22:40 load_data.dat
-rw-r--r-- 1 oracle oinstall 165 Sep 26 22:45 load_control.ctl
-rw-r--r-- 1 oracle oinstall 11 Sep 26 22:59 load_data.bad
-rw-r--r-- 1 oracle oinstall 1668 Sep 26 22:59 load_control.log
[oracle@ora12102 Desktop]$ cat load_data.bad
3,'Oops !'
[oracle@ora12102 Desktop]$ cat load_control.log

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Sep 26 22:59:58 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Control File: load_control.ctl
Data File: load_data.dat
Bad File: load_data.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table TEST_SQLLDR_DIRECT, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID_COLUMN FIRST * , O(") CHARACTER
DATA_COLUMN NEXT * , O(") CHARACTER

Record 3: Rejected - Error on table TEST_SQLLDR_DIRECT.
ORA-00001: unique constraint (HEMANT.TEST_SQLLDR_DIRECT_U1) violated


Table TEST_SQLLDR_DIRECT:
2 Rows successfully loaded.
1 Row not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.


Space allocated for bind array: 33024 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 3
Total logical records rejected: 1
Total logical records discarded: 0

Run began on Mon Sep 26 22:59:58 2016
Run ended on Mon Sep 26 22:59:58 2016

Elapsed time was: 00:00:00.07
CPU time was: 00:00:00.00
[oracle@ora12102 Desktop]$

SQL> select * from test_sqlldr_direct
2 order by id_column
3 /

ID_COLUMN DATA_COLUMN
---------- ---------------
1 First Row
2 'Second Row'
3 'Third Row'

SQL>
SQL> select status from user_indexes
2 where index_name = 'TEST_SQLLDR_DIRECT_U1'
3 /

STATUS
--------
VALID

SQL>

The duplicate row was rejected and went to the BAD file and was REJECTED.
.
.
.
So, the next time you chose to use SQLLDR DIRECT=TRUE for its performance benefits, make sure you know how to validate the status of Unique Indexes and check the log file ! Else, you might allow duplicates if the incoming data has not been cleansed before loading into the database.
.
.
.
If you are running 11g, the behaviour is the same in 11g/
.
.
.
.

Categories: DBA Blogs

Order by - varchar column storing numeric values

Tom Kyte - Mon, 2016-09-26 06:06
Hi Tom I have a table 'LOCS' with 2 columns of varchar datatype (col1 is varchar yet at the moment we have only numeric values stored): col1 col2 2272 ABC 22722 ABCD 1000 dgdfg 10001 dfm Now, when I query the table: select * from locs...
Categories: DBA Blogs

Record count on basis of insert/delete triggers

Tom Kyte - Mon, 2016-09-26 06:06
Hi, We have a pagination requirement across the UI foothold where we need to show the total# of records for significant db facts off the tables in view of numbers. And the query are not utilizing query filter criteria as triggerd by a menu option ...
Categories: DBA Blogs

dbms_job taking a long time

Tom Kyte - Mon, 2016-09-26 06:06
Hi, There is a SQL program whose job is to pick up data from multiple tables based on an application ID and populate an MIS table. There are 25-30 queries written to fetch data from multiple tables for an application ID. Data is pulled into col...
Categories: DBA Blogs

How to set the INITTRANS value when Isolation level as Serializable

Tom Kyte - Mon, 2016-09-26 06:06
Hi Tom, We current need to set the serializable isolation level, and will enable the ROWDEPENDENCIES when create the table like as below. My question is what is an optimal value for INITRANS? Or what should we consider when set this parameter? Tha...
Categories: DBA Blogs

Executed PL/SQL kept in Stored procedure but while compile Compilation error is coming.

Tom Kyte - Fri, 2016-09-23 04:46
Hi Tom, Below PL/SQL is working fine. while same code of PL/SQL as below is used in Stored procedure compilation error is coming while compiling stored procedure. Could you please make a suggestion to keep below PL/SQL in below Stored Procedure so...
Categories: DBA Blogs

How to compare two tables of data????

Tom Kyte - Fri, 2016-09-23 04:46
Hi Tom, I have two tables of values(dept1 and dept2). How do I compare all the data in these two tables??? It will return true if both tables contain the same data & false for otherwise... Another thing is that I do not know how to use CREATE OPE...
Categories: DBA Blogs

Java Connection Pooling with Oracle VPD

Tom Kyte - Fri, 2016-09-23 04:46
Hi Tom, We have a 3-tier application that is built on Java and Oracle. In our application, we extensively make use of Oracle VPD policies for setting contexts and managing the data. Now, we are building in Java something on top of Oracle. We hit ...
Categories: DBA Blogs

PlSQL- Bulk Collect and Update (Better Approach)

Tom Kyte - Fri, 2016-09-23 04:46
Hi Tom, I am looking for a better coding approach than what I have in my current system. I have two tables dog_owner(16 Million Records) and dog_owner_stage(8 Million Records). In the current process. I usually insert based on a common owner_accou...
Categories: DBA Blogs

Performance issue in CLOB\BLOB data migration

Tom Kyte - Fri, 2016-09-23 04:46
(did not get any answer for https://asktom.oracle.com/pls/apex/f?p=100:24:0::NO::P24_ID:9531842300346462307 ) Hello Tom, First of all, i would like you to thank you for your immense support on Database issues.It helps us a lot !! Question : M...
Categories: DBA Blogs

Compile_Error when refreshing a Materialized View from a procedure

Tom Kyte - Fri, 2016-09-23 04:46
We have Materialized Views which reference tables in other schemas. We can refresh/compile the Materialized Views from the command line however when we refresh/compile the Materialized View from within a procedure the job immediately aborts with...
Categories: DBA Blogs

Practise question

Tom Kyte - Fri, 2016-09-23 04:46
Hi, I was practicing some question on sql challenge about dml operation using multiple tables and got some doubt. CREATE TABLE plch_departments ( department_id INTEGER PRIMARY KEY, department_name VARCHAR2(30) ) / CREATE TABLE p...
Categories: DBA Blogs

Optimiser Trace

Tom Kyte - Fri, 2016-09-23 04:46
Hi Tom, A tricky question, recently we upgraded our systems to 11.2.0.4 and started to observe some queries taking much longer (from mins to 10+ hours). On analysing the taces / explain we found the access path had changed from the previous one, so...
Categories: DBA Blogs

Converting an EE DB 12.1.0.2.0 to SE DB 12.1.0.1.0 - Version - empty table

Tom Kyte - Fri, 2016-09-23 04:46
Hi, Im trying to convert EE 12.1.0.2.0 to SE 12.1.0.1.0 with expdp/impdp. I've found example of converting EE 11.2.0.1 to SE 11.2.0.4 where it is stated: "During import to standard edition we must use keyword VERSION=11.1 to be able to import e...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs