Tom Kyte

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

Undo Table space

Fri, 2016-07-08 03:26
Hi Tom, Following are the scenarios and please let me know what will happen in the listed scenarios : 1. I have a Undo table space with least size, i do a deleting of records in a table which is huge in size(a size which undo tablespace ca...
Categories: DBA Blogs

Website validation using regexp_like.

Thu, 2016-07-07 09:06
Hi, Could you please help to get website validation query using regexp_like. e.g. www.google.com https//:www.google.com both should get validated. Thanks in advance.
Categories: DBA Blogs

Datatype issue.

Thu, 2016-07-07 09:06
Hi all, I have an issue with column datatype.Can you please look at this issue. I have two tables emp1 and emp2 with empno column in both tables in table emp1 column empno is nvarchar2 and in table emp2 column empno is number datatype I am tryin...
Categories: DBA Blogs

how to debug the SQL logic execution ?

Thu, 2016-07-07 09:06
AskTom, I have a question about the 'SQL logic execution': Can Oracle Database log the logic of the SQL execution ? (which condition of the WHERE clause failed ?) example: select 1 from Dual WHERE 1 = 1 AND 1 = 1 + 1 --line 5 ; C...
Categories: DBA Blogs

Fuzzy Name Match Stored Procedure Optimization

Thu, 2016-07-07 09:06
Hello, Have written PL/SQL stored proc 'FuzzyNameMatch' that interrogates first, middle, last names from a single column in two distinct tables, ie source and compare columns. The algo parses shorter strings through longer and increments counter v...
Categories: DBA Blogs

Need to generate numbers between a given range for each record.

Thu, 2016-07-07 09:06
Hi Team, I have below sample table as input : ID MIN MAX 1 5 10 2 3 5 And I want output as follows: ID value 1 5 1 6 1 7 1 8 1 9 2 3 2 4 Id column is a p...
Categories: DBA Blogs

Performance & features of OCCI vs OCI

Thu, 2016-07-07 09:06
I'm going to develop an application that needs max performance. I will need: - batch inserts of BLOB - batch updates of BLOB - batch select of BLOB I consider to use OCCI but I'm not sure if it supports all optimizations that are done in OCI. ...
Categories: DBA Blogs

Materialized view logs and unique indexes

Thu, 2016-07-07 09:06
I have a table with moderate DML activity, a materialized view log on that table, and one materialized view on a remote database. After some activity in the source app, I can see multiple rows in the MV log with the same m_row$$, with a DMLTYPE$ v...
Categories: DBA Blogs

Best Data Type To use

Thu, 2016-07-07 09:06
I've a Table with columns EMPCODE,DAY1,DAY2...DAY31 I've to loop from Day1 to Day31 so what is the best way of doing this,If I should use collections then how should I assign Day1--31 to a Collection and what Collection Should I use. Can anyone p...
Categories: DBA Blogs

Oracle doubts

Wed, 2016-07-06 14:46
c d f ---- ---- ---- 1 a 100 1 b 200 Output: 1 a 100 b 200
Categories: DBA Blogs

cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 0

Wed, 2016-07-06 14:46
I am using PL/SQL in front end and Oracle 11gR2 in back end to developing a db app. In the middle of time, I am going to check hard parsed execution plan of one sql statment. What make me surprised is that the PL/SQL Developer seems has no capability...
Categories: DBA Blogs

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

Pages