Tom Kyte

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

Install Apex on Database Vault

Mon, 2017-10-30 12:46
Dear Tom, I am trying to install oracle apex 5.1.3 on oracle database 12c 12.1 release which is vault enabled. I was complete my apex install all are done successfully. but when i call on browser a messages shown is (Connection was reset). ...
Categories: DBA Blogs

Does update query returns affected row(s) value in key holder ?

Mon, 2017-10-30 12:46
Hi TOM, Does Oracle returns affected rows for update SQL statement in key holder? For insert query we supposed to receive the auto generated key. Such a way update query returns the affected rows id ?
Categories: DBA Blogs

Oracle 12c - query behavior change (11g not thrown error while "group by" not mention in sub-query/in-line)

Sun, 2017-10-29 00:06
Hi Team, We are migrating from 11g to 12c. We observed one behavioral change, as "group by" not mention in in-line query but 11g not thrown error. Query characteristics ---------------------------------- 1. Main query contains a inline...
Categories: DBA Blogs

ORA-06565: SET ROLE in LOGON TRIGGER

Sun, 2017-10-29 00:06
We have a trigger CREATE OR REPLACE TRIGGER TRG_AFTER_LOGON AFTER LOGON ON DATABASE BEGIN LogonCheck; END; / with CREATE OR REPLACE PROCEDURE LogonCheck AUTHID current_user AS BEGIN EXECUTE IMMEDIATE 'SET ROLE NONE'; END; / T...
Categories: DBA Blogs

Local unique indexes on partitioned tables

Sun, 2017-10-29 00:06
Tom, I'm wondering why Oracle requires the partitioning key on a local unique index. I realize that internally Oracle doesn't want to have to scan all local index segments to check the constraint. But why should the partitioning key need to be o...
Categories: DBA Blogs

12c release 1 dump questions confusions

Sun, 2017-10-29 00:06
Greetings, Every time i submit a question, i get a sense of confidence that i will surely get a valid and updated answer from the world's most interactive and supportive forum. The group is a ORACLE DICTIONARY for me. I thank you from bottom of...
Categories: DBA Blogs

Counting the number of business days between 2 dates.

Sat, 2017-10-28 05:46
how to get the number of days between two dates excluding saturdays and sundays using pl/sql?
Categories: DBA Blogs

PARALLEL Hint not getting used in SELECT

Sat, 2017-10-28 05:46
Hi, I have a SQL written in stored procedure: SELECT /*+ PARALLEL(TB_TEST, 8) +/ FROM TB_TEST WHERE DT <= TO_DATE('01/01/2015', 'MM/DD/YYYY'); When i execute the procedure and monitor v$session, I could see only one session with having SQL_I...
Categories: DBA Blogs

Colums into rows

Sat, 2017-10-28 05:46
Tom, I see lots of references to turning rows into columns, but how about turning columns into rows? Obviously I can select x union select y union select z, but that is only realistic for a small number of columns. What would you suggest for a lar...
Categories: DBA Blogs

Tuning SQL with MIN and MAX functions

Fri, 2017-10-27 11:26
<code>create table test_dept (dept_id number, dept_name varchar2(50), dept_region number); alter table test_dept add constraint pk primary key (dept_id); create table test_emp (emp_id number, sal number, dept_id number , constraint fk f...
Categories: DBA Blogs

AWR report

Fri, 2017-10-27 11:26
Hi Tom, Can you please explain how to look AWR report in simple term as i am very new to this. Thanks alot.
Categories: DBA Blogs

FGA Auditing - How to avoid false positives when FGA is on a table column and that column is in a view and other view columns are selected?

Fri, 2017-10-27 11:26
I'm new to FGA auditing and have created an FGA policy for SELECT on table T column SSN. This works well. Any user that specifically selects column T.SSN, generates a row in my audit table. (I'm using DB 11.2.0.4) select T.SSN from T; -- generate...
Categories: DBA Blogs

All about the DUAL table

Fri, 2017-10-27 11:26
Hi Tom Feels good to post a question after quite some time. Look at the following : SQL> desc dual Name Null? Type ----------------------------------------- -------- ---------------------------...
Categories: DBA Blogs

AskTom Architecture

Fri, 2017-10-27 11:26
Hi Tom, Thanks for this great resource! I am sure there are many others out there like myself who spend several hours a day reading the questions and your answers. Since so many people seem to be very curious about the architecture of your site...
Categories: DBA Blogs

DBMS PARALLEL EXECUTE : Chunk details

Fri, 2017-10-27 11:26
Hi Chris/Connor, I am executing chunks using procedure p_process_chunk below. Can you help: Is there any way i can get chunk Id(56, 57, etc) task name & Job Name (ex. TASK$_371_1, TASK$_371_2, etc) in p_process_chunk (i need this for logger pe...
Categories: DBA Blogs

Identifying implicit conversions

Fri, 2017-10-27 11:26
Tom, I have recently taken over support of a poorly written application that is suffering performance problems (bet you've heard that before!) - One of the conditions that I have identified is excessive full table scans and or index range scans ...
Categories: DBA Blogs

High Watermark on a table

Fri, 2017-10-27 11:26
Hi Tom, If my delete query doesn't delete any record, will it have any effect on HWM? I mean, everything will remain same as before my delete command, please confirm.
Categories: DBA Blogs

ORA-27103: internal error

Wed, 2017-10-25 22:46
Hello; could you help me with problem of memory, I try describe it. I have application under tomcat on application server, application generates select to database server, after few secconds application logs error ORA-27103: internal error A...
Categories: DBA Blogs

trigger, to get an audit

Wed, 2017-10-25 22:46
I have a table, where there is a column which is updated from multiple sources. I would like to find out the source which is updating this column. for example, it can be a SP or direct application. So is there a possibility of having a trigger, to...
Categories: DBA Blogs

Regarding the Complex Hierarchical Query

Wed, 2017-10-25 22:46
Hi Tom, Could you please help me to develop a Query. My requirement is given below. I have a following table called PRODUCT and the structure is given below. CREATE TABLE PRODUCT(ID NUMBER, NAME VARCHAR2(50), PARENT_ID NUMBER, PRODUCT_TYPE V...
Categories: DBA Blogs

Pages