Tom Kyte

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

sum() at each level in hierarchial query

Fri, 2018-11-09 18:06
consider the following query select level,sys_connect_by_path(last_name,'\') name,salary from employees start with last_name='King' connect by prior employee_id=manager_id is it possible to get the result like name sum_of_all_l...
Categories: DBA Blogs

Date in comparison with string

Fri, 2018-11-09 18:06
Hi Tom, I have a table named RTBS_SUBS_EVENT and there is a date column named expiry_date. expiry_date is a partitioned column too. a column named ID and it's indexed. NLS_DATE_FORMAT of the database is 'DD-MON-RR'. I run this query and th...
Categories: DBA Blogs

Join elimination

Fri, 2018-11-09 18:06
Hello All, While making some tests using LiveSQL, which is Oracle 18c, I found a strange behavior regarding join elimination. That is, join elimination DOES happen even if the constraints are DEFERRED ! My LiveSQL test script can be found he...
Categories: DBA Blogs

Is there any point backing up flashback 'flb' files?

Fri, 2018-11-09 18:06
Hi, Is there any point/benefit in backing up flb files to tape using an OS utility? Or, to put it another way: would backing up flb files just be a waste of tape? Thanks
Categories: DBA Blogs

Sorting -- sort a string like a number.

Thu, 2018-11-08 23:46
I have a alphanumeric column in the database. It contains only numeric values.Is it possible to do a numeric sort directly by a sql command like "order by <column>"
Categories: DBA Blogs

Blocks read using index vs. full table scan

Thu, 2018-11-08 23:46
Hi Tom, I have a 3 part question about performance... 1) The first question is sort of a introductory summary question. 2) Then I have an example ending with a question. 3) Finaly I have a very specific 'important' question. Why is it some...
Categories: DBA Blogs

Do we know for sure from the trace file, that the application uses transactions on the database?

Thu, 2018-11-08 05:26
We got deadlock. In the trace file there is the deadlock graph: <code> Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds wai...
Categories: DBA Blogs

Using REGEXP to find ten consecutive digits with possible separators

Thu, 2018-11-08 05:26
I HAVE THIS FREE FLOWING DATA FIELD IN A VARCHAR2 COLUMN THAT I AM CLEANING UP. I WOULD LIKE TO GET THOSE VALUES THAT HAVE A TOTAL OF 10 DIGITS IN ALL AND MIGHT CONTAIN THESE CHARACTERS : . OR - OR / OR ( ) CREATE TABLE E ( PHONE VARCHAR2(5...
Categories: DBA Blogs

Oracle Sequence vs GUID

Thu, 2018-11-08 05:26
We're developing a new system in for the company with .NET and Oracle Database 12cR2 using RAC. It basically does the following: receives invoices in XML format, then validates it according to business rules. A requirement is to give to the clien...
Categories: DBA Blogs

Row visibility set in a package procedure EXECUTE IMMEDIATE 'ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE'

Wed, 2018-11-07 11:06
Hi Tom, I've got a table DAVE_ELOF set to row archival (alter table dave_elof row archival;). I want that a user can set row visibility via a package procedure setStudyVisibleYN(aStudyId number, aVisible_YN varchar2). Code snipp...
Categories: DBA Blogs

Parallelism in IOT's

Wed, 2018-11-07 11:06
I am trying to join (outer join) a number of IOT's all with the same key in Parallel Mode; and am expecting the optimizer to use a Merge Join in order to avoid sorts (as the documentation clearly says it should); but it does not. - I'm tracing the pr...
Categories: DBA Blogs

history of session for particular sql id

Wed, 2018-11-07 11:06
I have a query which takes times to execute. When a user connects to the system and I monitor database I also get the SQL statement which takes times. but when user complete his task and after days he talks to me report takes too much time. I analyz...
Categories: DBA Blogs

How to determine the faster of two queries based on the explain plan

Wed, 2018-11-07 11:06
Hello I have two SQL scripts that are producing the exact same result (returning approx. 30000 rows) but the first one does it for 30min. and the second one for 2min. The slower query has (by my understatnding) the better explain plan (because it ...
Categories: DBA Blogs

Hierarchy Validation - Incoming hierarchy vs Expected hierarchy

Wed, 2018-11-07 11:06
Hi Experts, I have a requirement to validate the hierarchical structure of flat files which are loaded into the database against an expected structure that is known. I have created some sample data to demonstrate what I would like to do: <code>...
Categories: DBA Blogs

Flashback data archive for auditing

Wed, 2018-11-07 11:06
Hi Tom, Greetings !! we would like to leverage the 18c Flashback Data archive feature for auditing in our environment. Currently its trigger based with some business logic implemented in it and only 30% of tables being audited and capturing all...
Categories: DBA Blogs

Which is better to use Direct SQL or Bulk Collect

Wed, 2018-11-07 11:06
I have a source table of almost 20 million records. I have to load another table from source table after some checks and validation. What would be the better way to transfer, Insert through Direct SQL or Use Bulk Collect. what is difference between...
Categories: DBA Blogs

How to prevent some firewall from terminating SQL*Plus sessions with long-running statements?

Wed, 2018-11-07 11:06
Hi Tom, In know that I should talk to the firewall admins to get things fixed but this is - unfortunately - out of scope. So here we go: We have a database running on some Linux server and some client application running on some W2k16 server. T...
Categories: DBA Blogs

Who last updated an Oracle object

Wed, 2018-11-07 11:06
Hello, My goal is to produce a list of all objects (procedures, packages, functions, triggers, and tables) that have changed in the database since they were first created. I have a query that compares "created" and last_ddl_time" in the vi...
Categories: DBA Blogs

Counter to indicate rate changes

Tue, 2018-11-06 16:46
Hi Tom! Glad to have you back. Oracle: 12.1.0.2 My challenge is as follows: The t_res table contains hotel room reservations that consist of a reservation id (res_id), date of stay (res_date), and a rate card (rate_id) which typically dic...
Categories: DBA Blogs

How to split big union all query

Tue, 2018-11-06 16:46
Hi Tom In my application uses SSRS tool and PLSQL .According the requirement I have category as a in parameter like 'AD','SA','SSV' and 'SOA' , so I have written single query with union all operator for all category. Now problem is my query has b...
Categories: DBA Blogs

Pages