Tom Kyte

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

Extracting execution plan for PL/SQL block

Tue, 2018-03-27 07:26
<code>Hi Tom, Need help extracting execution plan for any anonymous PL/SQL block . I am able to extract execution plan for SQL using explain plan but it doesn't work for pl/sql block EXPLAIN PLAN FOR select * from dual; select *...
Categories: DBA Blogs

Difference in parallel hints.

Tue, 2018-03-27 07:26
Hi Tom, What is difference in following two scenarios? I am using parallel hints like - 1. select /*+ parallel(e,4) parallel(d,4)*/ e.eid,d.did from emp e, dept d where e.did = d.did; 2. select /*+ parallel(4)*/ e.eid,d.did from e...
Categories: DBA Blogs

customizing the SQLprompt

Tue, 2018-03-27 07:26
Hello Tom, I notice in the Oracle Magazine that you have your SQL prompt displaying "user@instance". Please tell me how you accomplish this. I can get it to display the username OR the instance name but not both. Thanks! -Lisa
Categories: DBA Blogs

Oracle Function returning multiple values

Mon, 2018-03-26 13:06
Can Oracle function return multiple values..? if yes, could you give an example for it?
Categories: DBA Blogs

How to identify database export files

Mon, 2018-03-26 13:06
Any tools/mechanisms to identify database export files taken using exp and expdp, so that i can do a scan on the desktops in my organisation to find who all have kept the export files in their desktops.
Categories: DBA Blogs

inconsistent behavior of DBMS_AQ.REGISTER process

Mon, 2018-03-26 13:06
Hi Chris/Connors, I'm facing one problem with inconsistent behavior of DBMS_AQ.REGISTER process. Followed below steps: 1) Created a queue table (multi consumer) 2) Created & Started the Queue (provided all grants) 3) Crated a call b...
Categories: DBA Blogs

generate file from clob in plsql

Sun, 2018-03-25 18:46
Hi, Tom. Estoy dise?ando un peque?o pkg para encapsular el codigo de generacion de archivos .csv de interfaces de aplicacion, pero cuando se trata de un set de datos muy grande la escritura del archivo falla, dando un error ORA-29285 Mi pregunta ...
Categories: DBA Blogs

Restrict user session for each osuser

Sun, 2018-03-25 18:46
We have a database consists of 14 users and approx 60 people are using it through PLSQL developer. I would like to restrict 5 session each OSuser in PLSQL developer so that weblogic application can have sufficient surplus session. Please guide how ca...
Categories: DBA Blogs

Adding constraint with REGEXP_REPLACE fails

Sun, 2018-03-25 18:46
Hi Tom, Why is this script failing? There is no errors on running this in Oracle 12.01 and previous versions!
Categories: DBA Blogs

Is safe to use row_number() over (order by null) as a rownum alternative?

Sun, 2018-03-25 18:46
Hi, I did some testing and it seems that row_number() over (order by null) could be used as an alternative to oracle's rownum pseudocolumn. Is this behavior by design or is just a coincidence? I'm trying this because some queries are very slow...
Categories: DBA Blogs

Create a unique alpha numeric reference for a record

Sun, 2018-03-25 18:46
Hi, I am developing my first application in Oracle Apex 4.1. I am developing an application that will hold contract information. Each contract has an ID that is a primary key and I am using a sequence and trigger to create this. The end us...
Categories: DBA Blogs

database performance by dblink

Fri, 2018-03-23 11:46
Hi Team , we are facing issue with sql query , which is using dblink in it , most of wait event observed is sql*net message from dblink , from remote database we have figured out the plan , and it seems to good , but we are not able to get that ...
Categories: DBA Blogs

Oracle and MS SQL server data transfers

Fri, 2018-03-23 11:46
Hi Tom, Currently our company is Implementing an ERP system based on Oracle (12c - 12.2.0.1). We already have a product testing system that has data in a SQL Server database (MS SQL Server 2008 R2 64 bit - 10.50.2500.0). We need to Establish a com...
Categories: DBA Blogs

How to Optimize design a 1000+ columns in a Oracle DB

Thu, 2018-03-22 17:26
Hi, We need to store 5 minute interval data, with additional attributes, which will be around 1000+ columns. How to design this optimally for a Fact Table? Doe we have any columnar design in Oracle, such as a vertical split? Please provide your ex...
Categories: DBA Blogs

Export from a Enterprise Edition (Partitioned table) into Oracle Standard edition (non-partitioned table)

Wed, 2018-03-21 23:06
I have an Oracle database Enterprise Edition which has partitioned tables. I need to export the full schema from the Enterprise edition with partitioning and import it into a Standard edition database. Please suggest of this is possible. I am even...
Categories: DBA Blogs

Using pivot table

Wed, 2018-03-21 23:06
Hi Tom, I have a table structure in the database like below, <code><u>Loc | BU | Curr | Product | Stages | Amount</u> 288 |30074| XCD | CCC | 1 | 21 288 |30074| XCD | OTH | 1 | 41 288 |30074| XCD | RMM | 1...
Categories: DBA Blogs

Handle exception without context switch

Wed, 2018-03-21 23:06
Hi TOM, I would like to know if it is possible to handle an exception in some SQL expression without context switch. I.e. I want to have something like select "try" to_date(T.TIMESTR, 'YYYYMMDD') "when exception" null "end" from MYTABLE T; I kn...
Categories: DBA Blogs

FIRST_ROWS syntax

Wed, 2018-03-21 23:06
Recently, I observed a usage of FIRST_ROWS hint written as <code>FIRST_ROWS EXPMA 851.120.01</code> I know the general syntax of FIRST_ROWS hint which should have a <code>(n)</code> value which means the number of rows to be ordered. Can anyone te...
Categories: DBA Blogs

Oracle Sql to simulate skipping titles

Wed, 2018-03-21 23:06
Sir in my Emp table I want to see all the jobs with corresponding employees in that designation simultaneously without repeating the Job name...like below. Example ----------------- JOB ENAME ------- ----...
Categories: DBA Blogs

How to force Oracle to run a subquery only once

Wed, 2018-03-21 04:46
Hi, I have some behaviour that I don't understand. I have built a call to a custom function in to a sql statement. The function returns a string that I want to use in an insert statement. The string is the concatenation of two bits of information e....
Categories: DBA Blogs

Pages