DBA Blogs

Oracle instant client 12c EZConnect Using tcps instead of tcp

Tom Kyte - Thu, 2019-02-28 09:06
I download oracle instant client 12.2.0.1.0, I try to use sql loader to load csv data to the database. I can successfully load using following: <code>sqlLdr.exe userid=user/password@//192.9.200.228:1521/oracle ERRORS=4000 control=D:\temp\csma\xx_2...
Categories: DBA Blogs

export and import taking a lot of time

Tom Kyte - Thu, 2019-02-28 09:06
Hi, I need to export and import a table having 2 million data in it. using exp/imp command . I tried using the below commands but it took a lot of time to export and then import the data. Please help. Commands :exp test/test full=y file=REL...
Categories: DBA Blogs

How to generate DDL for APEX app from code

Tom Kyte - Thu, 2019-02-28 09:06
In Oracle SQL Developer, 'Application Express' section, we can export DDL for an APEX app by context menu, 'Quick DDL' > 'Save to Worksheet' menu. I'd like to do the same by executing some SQL or PL/SQL code. I know we have DBMS_METADATA.GET_...
Categories: DBA Blogs

Reading Header Info from CSV

Tom Kyte - Thu, 2019-02-28 09:06
Hi Team My csv looks like below 123456,20,20,1500 --- this is the header abc,efg,hij,klm,nop,qrs abc,efg,hij,klm,nop,qrs abc,efg,hij,klm,nop,qrs abc,efg,hij,klm,nop,qrs I need to read the header info. Can you pls tell me whats the best ...
Categories: DBA Blogs

Emails sending out of 12c SuperCluster Database

Tom Kyte - Wed, 2019-02-27 14:46
Oracle version: <code>SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 26 15:14:07 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Tue Feb 26 2019 14:05:04 -06:00 Connected to: Oracle Database...
Categories: DBA Blogs

How to group by for a data set

Tom Kyte - Wed, 2019-02-27 14:46
Hi Tom, I have a table with many records for example as below. <code>create table test (a varcahr2(20)); insert into test values ('1'); insert into test values ('2'); insert into test values ('3'); insert into test values ('abc-oo cde')...
Categories: DBA Blogs

Using Access Advisor in Oracle 12

Tom Kyte - Wed, 2019-02-27 14:46
Hello, I am trying to use Access Advisor package in a 12.2 DB. When executing DBMS_ADVISOR.EXECUTE_TASK I get error: begin * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01843: not a valid month ORA-06512: at "SY...
Categories: DBA Blogs

Oracle 12.2: Delete Privilege needs an additional Select Privilege

Tom Kyte - Tue, 2019-02-26 20:26
Hi, Why do we need an extra select privilege additional to the delete privilege? From my point of view, this does'nt make sense: <code>--execute as user a: create table b.t (col varchar2 (10)); insert into b.t values ('a'); commit; gr...
Categories: DBA Blogs

Bind Variables

Tom Kyte - Tue, 2019-02-26 20:26
Hi Tom, I tried to use Bind Variables in my Script. But that script is taking more time than expected. Please have a look at the below script. area@DBWH> truncate table t1; Table truncated. Elapsed: 00:00:00.50 area@DBWH> declare ...
Categories: DBA Blogs

can't we use nologging clause with domain index

Tom Kyte - Tue, 2019-02-26 20:26
Hi Tom, I am trying to create domain index on table, I am getting <b><i>ORA-29850: invalid option for creation of domain indexes</i></b>. Can you please explain why can't we create domain index with nologging option. <code> SQL> begin 2 c...
Categories: DBA Blogs

ORA-30926 on MERGE statement

Bobby Durrett's DBA Blog - Tue, 2019-02-26 15:19

I was on call last week and I got a ticket about a batch job that was failing on a MERGE statement with an ORA-30926 error. Our support team worked around it by deleting some duplicate rows in a table that was feeding into the MERGE. This week I wanted to go back and try to understand what would cause ORA-30926 errors on MERGE statements.

I read through some blog posts and Oracle support documents relating to ORA-30926 and merge. Then I tried building some simple test scripts to see when you get ORA-30926 on a MERGE. At the end of my search I came back to this simple statement from the 18c SQL Language Reference manual’s description of the MERGE statement:

MERGE is a deterministic statement. You cannot update the same row of the target table multiple times in the same MERGE statement.

I was puzzled by the term “deterministic” but “cannot update the same row…multiple times” seems simple enough. I created test scripts to show whether you can update the same row multiple times with a MERGE: zip

Here are the two test tables:

create table target (a number,b number);

create table source (a number,b number);

Here is data and a MERGE statement that causes the error:

SQL> insert into target values (1,1);
SQL> insert into source values (1,2);
SQL> insert into source values (1,3);

SQL> merge into target
  2  using source
  3  on (target.a = source.a)
  4  when matched then
  5  update set target.b=source.b;
using source
      *
ERROR at line 2:
ORA-30926: unable to get a stable set of rows in the source tables 

This seems very simple. We join the source and target tables on column A with value 1. The merge statement tries to update column B on the target table row twice. It tries to set it to 2 and then to 3. I guess this is where the term “deterministic” comes in. If the merge statement updated B to 2 and 3 which would it do first and which second?

The example above with values 2 and 3 for B makes good sense but I saw some odd behavior when I used source rows with 1 and 2 for the B values of the two rows. With B values of 1 and 2 in this case the MERGE does not get an error:

SQL> insert into target values (1,1);
SQL> insert into source values (1,1);
SQL> insert into source values (1,2);

SQL> merge into target
  2  using source
  3  on (target.a = source.a)
  4  when matched then
  5  update set target.b=source.b;

2 rows merged.

SQL> select * from target;

         A          B                                                           
---------- ----------                                                           
         1          2                                                           

This seems to contradict the idea that you cannot update the same row multiple times with a MERGE. In this case it seems like B is updated twice, once to 1 which is what it already was set to and then to 2. I guess this works because B was already set to 1 so the update of B to the same value does not count as an update. It seems like only one update took place and then B ends up set to 2.

This example does not work with a slightly different MERGE statement on Oracle 12.1 or earlier:

SQL> select banner from v$version;

BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production    

SQL> merge into target
  2  using source
  3  on (target.a = source.a)
  4  when matched then
  5  update set target.b=source.b
  6  when not matched then
  7  insert values (source.a,source.b);
using source
      *
ERROR at line 2:
ORA-30926: unable to get a stable set of rows in the source tables 

SQL> select * from source;

         A          B                                                           
---------- ----------                                                           
         1          1                                                           
         1          2                                                           

SQL> 
SQL> select * from target;

         A          B                                                           
---------- ----------                                                           
         1          1                                                           

All that I can say based on these two tests is that sometimes an update of a column to the same value counts as an update and sometimes it does not. The preceding example works on 12.2:

SQL> select banner from v$version;

BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production    

SQL> merge into target
  2  using source
  3  on (target.a = source.a)
  4  when matched then
  5  update set target.b=source.b
  6  when not matched then
  7  insert values (source.a,source.b);

2 rows merged.

SQL> select * from source;

         A          B                                                           
---------- ----------                                                           
         1          1                                                           
         1          2                                                           

SQL> 
SQL> select * from target;

         A          B                                                           
---------- ----------                                                           
         1          2                                                           

It all seemed so simple when I read the documentation. Maybe instead of saying:

You cannot update the same row of the target table multiple times in the same MERGE statement.

Instead it should say:

You may not be able to update the same row of the target table multiple times in the same MERGE statement.

We should not count on being able to update the same row multiple times with a single MERGE statement, but clearly there are some cases in which we can. If you are like me and you get paged on a job that fails with ORA-30926 on a MERGE, it makes sense to get rid of the multiple updates on a single row because you most likely have hit a case where it is not allowed.

Bobby

Categories: DBA Blogs

getting error ( ORA-06553: PLS-306: wrong number or types of arguments in call to) after migrating code from oracle 11.2.0.3.0 to 12 c

Tom Kyte - Mon, 2019-02-25 08:06
we are in the process of upgrading our oracle database from 11.2 to 12.2 ran into an error: ORA-06553: PLS-306: wrong number or types of arguments in call to I have created a simplified script that recreates the issue. the error occurs at com...
Categories: DBA Blogs

AWS Support Knowledge Center

Pakistan's First Oracle Blog - Sun, 2019-02-24 19:29
In addition to AWS documentation and blogs, one of the best resource is AWS Knowledge Center. It contains frequently asked questions from AWS customers so this resource contains real world problems with their solution.


AWS Knowledge Center contains FAQ from almost all of the AWS services. For example, I was researching about if there was a way to restore or recover a terminated EC2 instance in case I didn't have any backups or AMIs, and all the EBS volumes were deleted and the Answer was No.

The great thing was that AWS did mention the best practice in that case about taking backups or making AMIs of instances or better yet enabling the termination protection.

AWS Knowledge Center is quite a rich resource and like everything else with AWS, its continuously evolving.
Categories: DBA Blogs

Fetching all records from a extremely large oracle table

Tom Kyte - Sat, 2019-02-23 19:26
Hi Tom, I am working on a ETL project that involves fetching all records from a Extremely large oracle table (that contains millions and millions of records) and has a very large number of partitions. I need to extract all data from the Table. ...
Categories: DBA Blogs

Measuring usage of schema objects

Tom Kyte - Sat, 2019-02-23 19:26
I have been tasked with identifying which of my group's schema objects (tables and views) are not being used anymore. What system table(s) can I query to find out which users are running SELECT statements against a specific owner's schema objects, p...
Categories: DBA Blogs

New_time function is failed to convert dst changes for date column

Tom Kyte - Sat, 2019-02-23 19:26
Hi , I have 2 table columns with date as datatype. 1 stores in central standard time other in central local time. Everyday a batch runs to populate these values to another oracle db. target db column is in eastern local. when I use new_time functi...
Categories: DBA Blogs

Is there a timeout for a query over database link?

Tom Kyte - Sat, 2019-02-23 19:26
Greetings - I have written PL/sql code that gathers tablespace information among 100's of Databases on a regular basis that would assist in our Capacity Planning Program. This code is configured using dbms scheduler on a central server/db. At...
Categories: DBA Blogs

Chasm Trap problem in Data Warehouses

Kubilay Çilkara - Sat, 2019-02-23 17:44
Chasm trap in data modelling in data warehouses occurs when two fact tables converge into single one dimension table. This is a problem which will cause double-counting and other inconsistencies when these tables are joined. SQL and relational databases surprise me every day!

Star schemata in data warehouses usually have one fact table and many dimension tables where the fact table joins to it's dimensions tables via foreign keys. But what if you wanted to 'share' the dimension across two or more fact tables, use it commonly, slowly starting to create an intertwined galaxy maybe!

For example think of a CUSTOMERS dimension table with the details of the customers and two fact tables SALES and REFUNDS with order and refund transactions as in the data model below.




With data like this in all three tables

Customers



Sales


Refunds




The join of the above three tables in SQL shows

Once the database is created and you have some data you can try and run the following query to find out how many refunds and sales a customer made and see the infamous 'Chasm Trap'






What happened? Do you see the duplicates? We joined by the n-1 principle. That is 3 tables 2 join statements on the keys. It seems is impossible to query these two fact tables via their common dimension. So why can't we know how many SALES and REFUNDS a customer did? Strange, why is the query falling into a Cartesian Join when you want to query from two different tables via a common dimension table.


Correct solution is a pre-aggregated join of the there tables




Conclusion

If you are going to use one dimension on two fact tables then you must first pre-aggregate and then join to get correct results.


Categories: DBA Blogs

I NEED YOU HELP WITH THIS QUERY! URGENT.

Tom Kyte - Sat, 2019-02-23 01:06
Hello I need your help, i don't know how to do this, I'm noob in this field and if you can help I really really gonna be happy. Here is... Assume that the following query is taking a very long time to run. The logins table has 10M records,...
Categories: DBA Blogs

How to handle two diff types in a single column ? is there any work around ?

Tom Kyte - Sat, 2019-02-23 01:06
Hi All, I need your inputs and suggestions for the below ; I have 2 column say A and B . In simple words they want to find percentage .But before that they are performing few validations In excel they had these validation before deriving t...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs