Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 2 months 2 weeks ago

Top N order by query is slow

Fri, 2016-06-24 09:46
Hi Tom, I'm trying to retrieve the last 20 rows by time for users with more than 100K rows in a table with more than 1 million records. The query performance is fine (in ms) when the user has a small number of records. But takes more than 2 minute...
Categories: DBA Blogs

Unable to Retrieve sys_refcursor values from remote function

Thu, 2016-06-23 15:09
Hi, i have created a function in DB1 that returns a sys_refcursor as output which is giving the result as desired in DB1. But when other database DB2 is trying to execute the function using dblink, that cursor is not returning any values. It is no...
Categories: DBA Blogs

Want to skip record if it's length not matching with required length while loading data in oracle external table

Thu, 2016-06-23 15:09
Hi Tom, I want to load data from fixed length file to oracle external table. I have specified length for each column while creating external table so data for most records getting loaded correctly. But if record length dosent match then data gets...
Categories: DBA Blogs

how to use Connection String in VB.NET using Oracle Wallet ?

Thu, 2016-06-23 15:09
In vb.net we could use following connection string but i recently do practical on oracle wallet done successfully in SQL PLUS Tools but main question is i want to use this connection string (username and password and tnsping) using oracle wallet sto...
Categories: DBA Blogs

Tree and "Youngest Common Ancestor"

Thu, 2016-06-23 15:09
<code>Hello Tom, I could finally ask you a question... I have a table like this: create table tree(name varchar2(30), id number, pid number, primary key(id), foreign key(pid) references tree(id)); with sample data: insert into tree va...
Categories: DBA Blogs

How to split comma seperated column of clob datatype and insert distinct rows into another table?

Thu, 2016-06-23 15:09
Hi, I need to split the comma separated values of clob datatype column in one table and insert only the distinct rows in another table. The details of the table are given below. The toaddress column in Table A is of datatype CLOB. Table B has ...
Categories: DBA Blogs

Maintaining Partitioned Tables

Thu, 2016-06-23 15:09
<code>Hi Tom, I need to build a table that will hold read-only data for up to 2 months. The table will have a load (via a perl script run half hourly) of 3 million new rows a day. Queries will be using the date col in the table for data eliminati...
Categories: DBA Blogs

Handling ORA-12170: TNS:Connect timeout occurred & ORA-03114: not connected to ORACLE failures

Thu, 2016-06-23 15:09
Hi Tom, We had a scenario where the sqlplus connection failed with "ORA-12170: TNS:Connect timeout occurred" in one instance & "ORA-03114: not connected to ORACLE" in another instance while executing from a shell script, but in both the cases retu...
Categories: DBA Blogs

Single Sign on ( SSO ) in Oracle Apex

Thu, 2016-06-23 15:09
Hi Tom , I am suffering in implementing Single Sign on ( SSO ) in Oracle apex using Custom authentication scheme. i have two applications like <code> <b> App id</b> <b> Application Name</b> 101 - ...
Categories: DBA Blogs

Version Control for PLSQL

Thu, 2016-06-23 15:09
Hi Tom, A couple years ago, I saw demo of how to version your PLSQL code. I have been searching for the code, all morning and I cannot find it anywhere. Can you point me to where/how I can version my PLSQL package? Thanks, Greg
Categories: DBA Blogs

Create second Ora 12c environment on same AWS server, now CDB/PDB type

Thu, 2016-06-23 15:09
Hi, I have an Oracle 12c running on AWS on CentOS Linux rel 7.1. The database has been installed as a standalone non-CDB database. This DEV database is used for development. I have to install a second environment, for QA, on the same server. This ...
Categories: DBA Blogs

Pages