Tom Kyte

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

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

How to save and show real executions Plans without EE resources

Tue, 2018-03-20 10:26
Hi - I would like to know if it is possible to save (in a plan table, say) the real execution plan of a given SQL present in the SQL cache and later show it via DBMS_XPLAN - first please note, I'm talking about the real execution plan (and including ...
Categories: DBA Blogs

SQL problem [ORA-01722: invalid number]

Tue, 2018-03-20 10:26
Hi Tom, Could you please help me with the below SQL. I have 2 users "dev" and "admin", both are in same database. DEV>DESC TB_CMA086_US_CITY Name Null? Type --------------------------------...
Categories: DBA Blogs

database sql performance with histograms

Tue, 2018-03-20 10:26
Hi Team, is their any way to find old histogram on column . in my situation , i see table having column status having histogram height based , but earlier it was freq. based , in this situation i know this because we have collected data , but in ...
Categories: DBA Blogs

Different ways of writing SQL for no particular reason

Tue, 2018-03-20 10:26
Find highest salary in each department without using MAX function Use a single SELECT statement only. For an added complexity (optional): try not using ANY functions at all (neither group, nor analytic, not even scalar) ...
Categories: DBA Blogs

Creating dates with date ranges

Tue, 2018-03-20 10:26
Hi Tom, i have a table MY_DATES (START_DATE DATE, END_DATE DATE) with date like : <code> START_DATE END_DATE --------------------------- 18-DEC-17 07-JAN-18 27-JAN-18 06-FEB-18 08-MAR-18 18-MAR-18 </code> ...
Categories: DBA Blogs

Pages