Tom Kyte

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

Difference between explain and execute plan and actual execute plan

Wed, 2018-02-21 15:46
Hi, I have often got questions around explain plan and execute plan. As per my knowledge, explain plan gives you the execute plan of the query. But I have also read that Execute plan is the plan which Oracle Optimizer intends to use for the query and...
Categories: DBA Blogs

Strange dependency in user_dependency: view depends on unreferenced function

Tue, 2018-02-20 21:26
Dear Team, I will try to simplify the scenario we have, using a simple test case: <code> SQL> create table test_20 ( a number) 2 / Table created. SQL> SQL> create or replace function test_function (p_1 in number) 2 return num...
Categories: DBA Blogs

Report for employee attendance

Tue, 2018-02-20 21:26
I am sorry for asking this seemingly trivial question, but I have been struggling with it for some time, my deadline is approaching and I can't find any answers for it. I have 3 tables: Calendar table: <code>CREATE TABLE "CJ_CAL" ( "CAL_ID...
Categories: DBA Blogs

Using SELECT * combined with WITH-CLAUSE - Bad Practice? View gets compiled with static columns list

Tue, 2018-02-20 21:26
Hey guys, I have a question regarding clean SQL Code / Bad Practice around the use of wildcards in SELECT-Statements. In the provided example I have a base-query with a huge list of columns selected and two (or more) sources I need to have combin...
Categories: DBA Blogs

ORA-01502 with hardcoded index hints

Tue, 2018-02-20 03:06
We have EBS 12.1.3, which has several indexes, that we disable for bulk loading at night and rebuild them once done. While they are disabled some queries get ORA-01502 because of hard coded index hint on these unusable indexes, although we have skip...
Categories: DBA Blogs

SUBPARTITION ELIMINATION

Tue, 2018-02-20 03:06
Table with 10 years worth of data Has two key date columns. Queries are always by one of the dates. <b>But never both</b>. So how to get partition elimination to work for both when really can only partition by 1 column. **Thought was - since ...
Categories: DBA Blogs

Issue with DBMS_CRYPTO Encryption method for larger dataset

Tue, 2018-02-20 03:06
Table Structure <code> CREATE TABLE TEST_PAYLOAD ( CNTNT_PAYLOAD_OID NUMBER(20) NOT NULL, CNTNT_PAYLOAD CLOB, BLOB_CNTNT_PAYLOAD BLOB, ENCRYPT_BLOB BLOB, DECRYPT_BLOB BLOB, CLOB_CNT_PAYLO...
Categories: DBA Blogs

data encryption package

Tue, 2018-02-20 03:06
Tom: Would you please provide some examples for how to use dbms_obfuscation_toolkit ? -Thx -Reji
Categories: DBA Blogs

Primary and foreign key in temporal tables

Tue, 2018-02-20 03:06
I have product and supplier table. Both tables have start and end date columns (temporal validity) Relation between product and supplier table is managed via a link table product_supplier_lnk table. I need to know how to manage primary and foreign...
Categories: DBA Blogs

When case counting statement query issue

Tue, 2018-02-20 03:06
hello Oracle Team I have this test query right now and I have this table. <code>id | cost_center |hardware beni | aaaa |PC123 beni | aaaa |PC234 ari | bbbb |PC456 cari ...
Categories: DBA Blogs

Select query on two calculated fields, one depends on other calculated field

Mon, 2018-02-19 08:46
SELECT A, B, A+B AS C, CASE C (I don't want to use again A+B here its lengthy query) ------ ----- ------ END As D FROM TABLE So here C is a calculated field, I want use result of C to calculate other field D. How can I do this without ha...
Categories: DBA Blogs

Optimization of view using union on 2 tables of different schema

Mon, 2018-02-19 08:46
Hi, I need to get data of table from 2 different schema . So i have created a view which will have union of 2 tables of 2 different schema . E.g. create or replace view acct_details as select column1,column2..column n from Table1 Un...
Categories: DBA Blogs

IF ELSE statements within COLUMN/HAVING elements within SELECT queries

Mon, 2018-02-19 08:46
Hi there, I'm trying to convert quite a few SQL queries from MS Access to PL SQL. Annoyingly a lot of them have VBA in them. Any tips on the best option to replicate this VBA in the select statement? I could use a variable but then I'm not sure...
Categories: DBA Blogs

Can Oracle automatically gather session specific stats for a GTT?

Mon, 2018-02-19 08:46
Hi Team, First of all, Thanks a lot for all the great work you are doing! It would be great if you can help me with a query. While reviewing AWRSQL report of a SQL I noticed that session specific stats were used for that execution. This SQL is ...
Categories: DBA Blogs

Options to load binary file and insert the data in a BLOB column in one pass

Mon, 2018-02-19 08:46
Nowadays when we have a BLOB column to be loaded with the content of a given file, we do : INSERT INTO table(columnPK, column2...columnBLOB) values (PKidtobeinserted, ...., empty_blob()) RETURNING PKidvalue; and later we call a procedure called...
Categories: DBA Blogs

table partition exchange

Mon, 2018-02-19 08:46
Hi Team, we have situation where we have two partition table (range partition ) ,example table A1 ,A1_history . application team want to archive all the partition except last 3 partitions , from Current production table to archival table . ...
Categories: DBA Blogs

Delay in row commit or Uncommited rows by Application

Mon, 2018-02-19 08:46
Hi Tom, Application(.Net) component is calling SP and creates an entry into few tables and sometimes there is delay in committing these transactions by .Net component or there is transaction left uncommitted in Oracle DB. we are aware of this issu...
Categories: DBA Blogs

Rebuilding all the unusable index

Mon, 2018-02-19 08:46
Hi Tom I really need to identify all the failed or unusable indexes in all schema in database and also to rebuild it. Kindly provide a query for both. im using SQL DEVELOPER TOOL in ORACLE 12c.
Categories: DBA Blogs

Oracle Statistics

Mon, 2018-02-19 08:46
I have an upcoming production migration I need help with. Table A has hundreds of million of rows. The migration is supposed to update a column in table A, but instead of doing an UPDATE it creates table B (one-to-one with table A) and then does a ...
Categories: DBA Blogs

variable in FROM clause inside pl/sql

Fri, 2018-02-16 07:46
Hi Tom We have an anonymous pl/sql block which looks like follows but using dbms_sql (the following doesnt work) declare vRows number; begin for i in (select * from user_tables) loop select count(*) into vRows from i....
Categories: DBA Blogs

Pages