Tom Kyte

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

Filling nulls in hierarchical query

Wed, 2018-03-28 20:06
Recently in the Oracle magazine of nov-06 you explained how to fill up null values with the previous non-null value of a column. I got a similar problem, but on hierachical query. I need to fill up null columns from its parent non-null value instea...
Categories: DBA Blogs

Log Switches and log file sync

Wed, 2018-03-28 01:46
Hello Tom, I currently have 6 log groups of 1 2GB member. These six groups are on the same mount point in a virtual environment. There is a log switch every hour. During peak times, however, the log file sync wait seems to be an issue for us. The ...
Categories: DBA Blogs

Shrink SYSAUX

Wed, 2018-03-28 01:46
I shrinked a tablespace SYSAUX but I have got segments towards the end of the datafile, the resize command will failed. I checked type segment names and it type this query: select file_id, block_id, block_id + blocks - 1 end_block,...
Categories: DBA Blogs

system statistics

Wed, 2018-03-28 01:46
Hi Tom, Recently with the help of your valuable answers to my questions regarding system statistics, I have put "system statsitics" into production system after testing and results are wonderful. It saved my more than 50% of execution time. My que...
Categories: DBA Blogs

Materialized view refresh in case of DDL on base table, like truncate, drop and recreate

Wed, 2018-03-28 01:46
Hi Tom, I was trying to prepare materialized view against some table which are periodically dropped and recreated. I did not find an option to automatically refresh the materialized view in this case. Refresh FAST ON COMMIT is not working, and REF...
Categories: DBA Blogs

Query transformation - Remote database

Wed, 2018-03-28 01:46
Hi, I have a query that fetches data from a remote database and this data is not what i was expecting (less data or no data). I did try to understand what is causing the issue and noticed the query was being transformed while run on the remote db....
Categories: DBA Blogs

Automating procedure testing which returns cursors

Tue, 2018-03-27 07:26
Hi Team, In my project we have testing suite built for PL/SQL and it tests basic test cases like input/output comparision. So in the tool if I configure procedure P1 (p_in IN number, P_out OUT number) with input and and expected output, the too...
Categories: DBA Blogs

Extracting 500 Million Rows from Oracle DB -- Pro*C OR PL/SQL UTL_FILE? Deep Dive into WHY?

Tue, 2018-03-27 07:26
Hi Tom, The question is simple. I need 500 Mil rows to offload from oracle, what should I choose to to develop an application with ? PRO*C or PL/SQL UTL_FILE? and why ? I have gone through the debated topics on which approach between PRO*C o...
Categories: DBA Blogs

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

Pages