Tom Kyte

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

Do bind variables in APEX trigger row by row processing?

Wed, 2016-07-06 14:46
To start this off, I kind of have a solution for my problem, I'm just trying to understand the mechanics behind it. Ok, here's the set-up: - I have a very simple APEX page: a report on a view + 2 date pickers to filter the data; - The query is a...
Categories: DBA Blogs

why does "select user from dba_users" work?

Wed, 2016-07-06 14:46
Hi all, I accidently typed today "select <b>user</b> from dba_users" in SQLPlus instead of "select <b>username</b> from dba_users" and it worked. Not like the correct SQL would have, but i got 21 rows (which is the correct number of users in the dat...
Categories: DBA Blogs

Slow query because the cardinality estimate is wrong for joins on foreign keys

Wed, 2016-07-06 14:46
While investigating a very slow query in our OLTP db, I noticed that Oracle severly under estimates the cardinality for joins that are on foreign key. The following script replicates the issue. create table A (part number not null, rec number not...
Categories: DBA Blogs

Tune order by clause in query.

Wed, 2016-07-06 14:46
Hi Tom, In the below query order by is taking a lot of time. so i thought of creating a composite index on columns that are present in the order by clause and force that index using hint. <b>But my problem is, here i have to fetch data of lo...
Categories: DBA Blogs

I need to delete 18000 rows from a table but where clause condition varies. How to complete this deletion in simple way

Wed, 2016-07-06 14:46
I need to delete 18000 rows from a table but where clause condtion varies for each set of records. A particular where clause condition can delete 3 records. Another particular where clause condition can delete 1 record. I combined both delete stat...
Categories: DBA Blogs

where are the executed statments stored?

Wed, 2016-07-06 14:46
suppose i execute a plsql block , all the statments are not executed. Only high load statments are executed. How to see which of the statements are executed and where are they stored, Like which view/table?
Categories: DBA Blogs

Cloud Raining - Where is Oracle with the Cloud - Is the DB Giant sleeping ?

Wed, 2016-07-06 14:46
Hello AskTom Team, I have been working with Oracle Database for over a decade and half. With the recent shift of Companies wanting to put their systems in Cloud rather then on prem What is the future of Oracle. I just attended a...
Categories: DBA Blogs

Inner join vs Where

Wed, 2016-07-06 14:46
What is the best practice use "Inner Join" o "Where" Example Example A select DISTINCT(ET.DESCRIPTION) FROM EVENTTYPE ET INNER JOIN EVENTDCO E ON E.EVENTTYPEID = ET.EVENTTYPEID INNER JOIN CONTEXTOPERATION CTX ON E.OPERATIONPK ...
Categories: DBA Blogs

ora-01008, what is the bind variable's name?

Tue, 2016-07-05 20:26
Good time of day, Tom! I run several SQL via DBMS_sql package. Each of that SQL has a set of bind variables. Is there any feature to get a list of variables' names for given SQL? For instance. I wonder to get a list of ':v_name',':p_result' ...
Categories: DBA Blogs

Generate tree paths for hierarchy

Tue, 2016-07-05 20:26
Hello , I have one question which are asked into interview ,To make a tree when user insert a node into table its path get automatically reflected into table Table: Tree ---------------------- node(int) parentNode(int) path(...
Categories: DBA Blogs

LEAST AND GREATEST functions

Tue, 2016-07-05 20:26
Hello, I am trying to use the below SQL : SELECT least ( DECODE (:VAR1, 9999, NULL, :VAR1), DECODE (:VAR2,9999, NULL,:VAR2) ) FROM DUAL; VAR1 & VAR2 need to be NUMBERs (not varchar) the above SQL seems to work for all numbers exce...
Categories: DBA Blogs

trigger

Tue, 2016-07-05 20:26
Hi, my table is with fist name , last name , status. Now the thing is I want to change the status to "APPROVED" as soon as I made the entry in last name, if last name column is empty status should be default lets say "PENDING". I tried it u...
Categories: DBA Blogs

High Soft Parsing

Tue, 2016-07-05 02:06
Hi Tom, We are experiencing high Soft parsing in our databases , though we have enabled session cached cursors and all our SQL/PL SQL blocks using bind variables. We are using Pro C as a host language interact with the back end database. Load...
Categories: DBA Blogs

Can we do a CTAS (Create table as) without the NOT NULL constraints?

Tue, 2016-07-05 02:06
Can we do a CTAS (Create table as) and create the new table without the NOT NULL constraints? select * from v$version; Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE ...
Categories: DBA Blogs

Format the Number for display

Tue, 2016-07-05 02:06
<code>Hello Guru, Q1) I need to display numbers from a database in a specific format. Table Tn (n number(6,3)); The format is 999.999 SQL> insert into tn values( 123.123) ; 1 row created. SQL> insert into tn values(0) ; 1 row created. SQL...
Categories: DBA Blogs

NOLOGGING

Tue, 2016-07-05 02:06
Hi, We have a problem, each day our DR setup is getting about 300 GB of data. We have found this is due to the huge amount of archive logs being written. We have certain bulk operations that are taking place, most of which are from bulk deletes or...
Categories: DBA Blogs

Retreive userid who has taken training more than once

Tue, 2016-07-05 02:06
Hi Tom, I have 2 tables user and training User Userid Username Trainingid 1 A 1 2 B 2 3 C 2 4 D 3 5 E 2 Training Trainingid trainername userid countoftrainings Date 1 X 1 2 ...
Categories: DBA Blogs

oracle text with order by clause

Mon, 2016-07-04 07:46
Dir Sir: I am developing an anti-plagiarism system, I am using Oracle text to search for my text. my corpus contains more than 30 million of records. I want to check my document against this corpus. I want to fetch the highest score only, not all...
Categories: DBA Blogs

Can be differentiate cascade delete or statement(delete from query) inside the table trigger

Mon, 2016-07-04 07:46
Hi Tom, i have question about cascade delete, How its work internally and how to differentiate that row by delete from delete statement on child table or row deleted by a cascade delete. because i am facing problem and try to solve Mutating error...
Categories: DBA Blogs

Partitioning Questions

Mon, 2016-07-04 07:46
Hello I would like to post a question related to which partitioning I can go for based on the below scenario. I have 2 tables named table1 and table2. Both the tables are having customer I'd and user id column. The hierarchy is multiple users...
Categories: DBA Blogs

Pages