Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 15 hours 59 min ago

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

Best practices for keeping passwords hidden

Wed, 2019-02-13 03:26
Can you recommend best practices for keeping passwords hidden in command lines for impdp/expdp, shell scripts etc.?
Categories: DBA Blogs

Object View To Manipulate Data In Tables

Wed, 2019-02-13 03:26
Hi, I have been using Object View, User Defined Types in Oracle for sometime. I tried to follow the approach here. <code>https://docs.oracle.com/cd/A64702_01/doc/server.805/a58241/ch_ov.htm</code> I am wondering if we put all the data manipul...
Categories: DBA Blogs

Lob: Basic - Secure

Wed, 2019-02-13 03:26
We observed frequent wait event 'enq HW - contention' in performance reports with SQL includes LOB Objects. Lob object Details ? Object - WF_WORKFLOW Owner - IBMUCD What changes we can face if we convert LOB from basic to secure?...
Categories: DBA Blogs

Distinct count across multiple tables

Wed, 2019-02-13 03:26
I have three possible places where accounts data can be requested. On the MONTHLY USAGE, HISTORY USAGE or ENROLLMENT tables. The same account(s) could be on all three tables, or on the table(S) multiple times, but some accounts could be on one or ...
Categories: DBA Blogs

lost update

Wed, 2019-02-13 03:26
Tom, I was reading your book expert one-on-one and in Chapter 3: Locking and Concurrency, you have mentioned a scenario of lost update. I was trying to simulate that and I did not succeed in doing the test. I tried to update emp table in session...
Categories: DBA Blogs

Update a column after multiple criteria

Wed, 2019-02-13 03:26
Hello, I have the following data: Id AMAOUNT NO_PRATITION ID_STRATEGY 1 100 99 XXX 2 200 99 XXX 3 0 99 YYY 4 100 99 YYY 5 200 99 YYY 6 0 99 ZZZ 7 100 99 ZZZ 8 200 99 ...
Categories: DBA Blogs

How Oracle Database gets the data from multiple left joins

Wed, 2019-02-13 03:26
Hi Tom, I came across a scenario today. table1 id integer name varchar table2 id integer name varchar designation varchar table3 id integer name varchar relation_status varchar I have the query as below which is working fine ...
Categories: DBA Blogs

Please help understand expiration_secs in sys.dbms_lock.allocate_unique

Tue, 2019-02-12 09:06
Please help understand the meaning of the param <b>expiration_secs</b> in <code>sys.dbms_lock.allocate_unique</code> Does it represent the time till which the named DB lock stays on, if not unlocked/commit/rollback by the same session? https:/...
Categories: DBA Blogs

Cautions using GMT/UTC in AWS RDS instance when located in EST region

Tue, 2019-02-12 09:06
Background: 1. I have just created a Database out on an AWS RDS instance on the East Coast. 2. I noted that the TimeZone was set to GMT/UTC (Greenwich Mean Time) when I setup DB in AWS. 3. I note that when I query <select sysdate from dual;> from ...
Categories: DBA Blogs

Log Stored Procedure calls

Tue, 2019-02-12 09:06
Our solution exposes a Stored Procedure to a DB user having the right grants to execute it. We need to know the date and time for each execution for that stored procedure. We are in production, so we can not instrument the running code of the store...
Categories: DBA Blogs

Pages