Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 2 weeks 4 days ago

Select from a table where a key value has matching link for all key values of another table

Tue, 2019-02-19 05:46
I have a set of three tables (T1, T2, T3) that represent dictionary data from external sources. I need to match the data from table 1 with the data in table 3 where <b><i>all</i></b> the rows in table 1 for a given OR_ID are reflected in table 3. T...
Categories: DBA Blogs

Update statement to flag rows

Tue, 2019-02-19 05:46
Hello, Ask Tom team. I'm using the query below to load rows to a destination database based on some conditions. After this is done I want to flag those rows in order to exclude them in the next SSIS ETL run. <code>select t1.invoice_sender,t1.ei...
Categories: DBA Blogs

latch undo global data

Tue, 2019-02-19 05:46
Hi team, I see spikes in oem for wait event latch undo global data . This is on insert statement , having concurrency of 50 Inserts in one second Due to heavily loaded db ash report takes high time It would be helpful if you share some s...
Categories: DBA Blogs

bound variables

Tue, 2019-02-19 05:46
I would like to know more about sql injection. Why is it so hard to tell to the Oracle that a certain string is a parameter and not a part of a Sql command? For example, can a person call himself Delete and his name can not be used in a search? And i...
Categories: DBA Blogs

unable to connect using database link

Tue, 2019-02-19 05:46
DEAR TOM, I CREATED A DATABASE LINK ON MY LOCAL DATABASE USING THE FOLLOWING COMMANDS. SQL> CREATE DATABASE LINK RP 2 CONNECT TO PRINCE 3 IDENTIFIED BY PRINCE 4 USING 'ORB'; Database link created. SQL> SELECT COUNT(*) FROM DUAL@...
Categories: DBA Blogs

Performance issue with data densification process

Mon, 2019-02-18 11:26
Hi Tom, I am facing an issue while making sparse data dense. Here is the problem statement: We are getting price information for securities from upstream in a file and prices will come only when either there will be new security on boarded or t...
Categories: DBA Blogs

The relationship between null and 0

Mon, 2019-02-18 11:26
Coding 1) <code>select comm from emp where comm is not null and comm != 0;</code> Coding 2) <code>select comm from emp where comm != 0;</code> The results of Coding 1 and Coding 2 are the same. I know that the values ??of null and 0 ar...
Categories: DBA Blogs

Table and Index maintenance

Mon, 2019-02-18 11:26
Good Afternoon Tom, I'm going to develop a little SQL Tool for maintenance of compress tables and indexes for our DWH Schema, our clients have Oracle EE (11.2 and 12.2), my "big" doubt is use or not use parallel execution because i see that using ...
Categories: DBA Blogs

writing a stand-alone application to continuously monitor a database queue (AQ)

Mon, 2019-02-18 11:26
Hi Tom, A question regarding oracle AQ... I wish to write a small stand-alone application that would *constantly* monitor a queue (only one queue) for the arrival of a message and as soon as a mesage arrives, take some action. I figured I could use...
Categories: DBA Blogs

Best way to enforce cross-row constraints?

Mon, 2019-02-18 11:26
I use the database to declare (and enforce) as much application logic as I can. What I'd like to do is to enforce application constraints across related rows, if possible. As a contrived example, suppose we have a table of Agreements and a secon...
Categories: DBA Blogs

Efficient way to fetch data using rownumber from a billion record table??

Fri, 2019-02-15 10:06
I'm trying to fetch required data from a billion record table using the row number approch (i.e, select "ingest_ts_utc" from (SELECT to_char(sys_extract_utc(systimestamp), 'YYYY-MM-DD HH24:MI:SS.FF') as "ingest_ts_utc" ,ROWNUM as rno from XYZ.ABC ) ...
Categories: DBA Blogs

using sql loader for a table where you only have insert privileges via a non default role

Fri, 2019-02-15 10:06
TeamTOM, I'm trying to use Sql Loader to load a text file into a normal Oracle table (as opposed to an external one). Unfortunately I only have insert rights to the table via a non default role. If I were just doing a simple insert statement in sq...
Categories: DBA Blogs

Access Table Without Synonym and Without Schema Name

Thu, 2019-02-14 16:06
I have a schema called HCR_SCHEMA, in which I have one table called TEST_EMP, and I have a user say USER_A and granted him select on the TEST_EMP table. I connected as USER_A as tried to execute SELECT * FROM TEST_EMP. Obviosuly it threw me error ...
Categories: DBA Blogs

print an XMLTYPE Record Type variable

Thu, 2019-02-14 16:06
we have a following definition TYPE response_t IS RECORD ( doc XMLTYPE ); A local variable is defined with that record type lv_resp response_t; I want to print the value this local variable has stored.I tried ...
Categories: DBA Blogs

Expdb views_as_tables failes after view recreated, bug or feature?

Thu, 2019-02-14 16:06
This has been giving me fits. I?m running 12.2.0.1 with no maintenance. In the test case below I create a view TEST_VIEW with three columns A,B,C returning one row. Both select from the view and export of the view work as expected. I ...
Categories: DBA Blogs

Update production database from ETL Process

Wed, 2019-02-13 21:46
Hello, Ask Tom Team. I am using a Microsoft SSIS ETL Package to insert data to Oracle (source and destination). I want to do an incremental load every day but I have to flag the rows that are already loaded in the destination. It is not just s...
Categories: DBA Blogs

DB Link between Oracle 12c (Linux) and Oracle 11.2 R2 (Windows)

Wed, 2019-02-13 21:46
Hi everyone, I have a problem with DB Link. I'm trying to create a dblink from and Oracle 12c (installed on Linux Server) and Oracle 11.2 R2 (installed on Windows Server). I've correctly created the dblink, but when i try to query the result is: "<b...
Categories: DBA Blogs

Merge using collections

Wed, 2019-02-13 21:46
Hi Tom, I am just trying to understand if the table function will work with collections. Please find my below code. Script: <code>create table setm_students (id number, name varchar2(10)); create table setm_students_target as select * fro...
Categories: DBA Blogs

getting rows N through M of a result set

Wed, 2019-02-13 21:46
I would like to fetch data after joining 3 tables and sorting based on some field. As this query results into approx 100 records, I would like to cut the result set into 4, each of 25 record. and I would like to give sequence number to each r...
Categories: DBA Blogs

Permission issues when loading text file to external table

Wed, 2019-02-13 21:46
Hey Tom, First of all, I'm sorry if this has been asked already, I simply couldn't find an answer after days of googling. Here it goes: I've created a SP to load a text file into an external table on 10g as per instructed here and many more places...
Categories: DBA Blogs

Pages