Tom Kyte

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

Implementing Old style SQL outer join along with condition query to the new style

Tue, 2017-10-31 07:06
Hi, I have SQL outer join query which is written in old format with (+) notation and I am working on its migration to the new presentation and without the (+) notation. I am unable to convert the last line of the query with the outer join condition....
Categories: DBA Blogs

Need to print latin ä charater through Dbms out into apex sql developer ?

Tue, 2017-10-31 07:06
Hi gurus, I am trying to print this character a through apex sql developer but it return me ? instead of this a , any one know how can I achieve this. begin dbms_output.put_line ('http://fam.co/') ; end; if my question is not clear please...
Categories: DBA Blogs

two stage RMAN backups

Tue, 2017-10-31 07:06
Hi team. We have an Oracle instance, which I think is using ASM on SAN LUNs. The SAN is multi-path, multi-fabric with the LUNs are provisioned from a high quality H/A storage array. At the moment the DBAs use RMAN to push backups via MML directly ...
Categories: DBA Blogs

Make a simple audit of table connections

Mon, 2017-10-30 12:46
Dear Tom. I want to know which tables in my Oracle schema are impacted by any software, web application or direct queries. An provider said me : " Make an audit on all tables (and views) of your schemas is not a good idea because your DB will w...
Categories: DBA Blogs

Data Model (Design) Question

Mon, 2017-10-30 12:46
I have a around 2 million assets that send 15 min interval data for a given day (i.e) total of 96 intervals for a given day. What is best database modeling approach. My target RDBMS is Oracle. I'm partitioning by week and for each interval I need ...
Categories: DBA Blogs

ASSM and basicfile LOB

Mon, 2017-10-30 12:46
Hello Tom According to https://docs.oracle.com/database/121/ADLOB/adlob_smart.htm#ADLOB46175 basicfile lob must be stored in a tablespace with segment space manually managed. However, it seems oracle does not prevent user from creating it in ...
Categories: DBA Blogs

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

Pages