Tom Kyte

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

Compare for matching recrds i n two table and return true or false against each row

Thu, 2018-06-28 11:06
Dear Tom I am sorry if this has been dealt before, I am a complete beginner to ORACLE SQL and will appreciate your help. I have two tables table A and Table B. Table A has about 60,000 rows of customer data with Customer Id as main identifie...
Categories: DBA Blogs

How to validate multiple errors in Oracle

Wed, 2018-06-27 16:46
Hi Tom, i have a table like an employee in the three fields 1) ename 2)empno 3)salary <code>create table details(ename varchar2(20),empno number(12),salary number(12));</code> I am receiving data from the file(clob) by the administrator for...
Categories: DBA Blogs

How can I check all the programs in my database?

Wed, 2018-06-27 16:46
Hello The Oracle Masters, First, congratulations for this site and your answers, you are so good that I try my chance today. Is it possible to find in ONE table or ONE view or with a PL/SQL package all the programs that can be executed in my ...
Categories: DBA Blogs

How to UNION ALL xxx_VIEWS and xxx_SOURCE ?

Wed, 2018-06-27 16:46
I often build a view to prototype/develop ETL transformation rules, then "wrap" the view in a PL/SQL package. Occasionally, I need to search the ETL code. However, xxx_VIEW.TEXT is LONG, while xxx_SOURCE is VARCHAR2(4000). I know I can run t...
Categories: DBA Blogs

strange index issue

Wed, 2018-06-27 16:46
Hi Tom, I will explain my case in example. I create simple table just with two columns: <code>create table OC_YKB.TAB_TEST1 ( GUID number, stat varchar2(1) );</code> Then insert 1 million of values where GUID is different values and STAT =...
Categories: DBA Blogs

Retrieving input from a COM port, source being weighing scales

Wed, 2018-06-27 16:46
We are currently migrating a client from a Forms environment towards an APEX application. The client is used to using scales, the result of which is inserted into a Forms application: The user enters the cursor into the target cell in the Forms envir...
Categories: DBA Blogs

Wrong execution plan with domain indexes in or condition

Wed, 2018-06-27 16:46
Hello, I have a very simple query on a table with two columns indexed fulltext. If there are two AND conditions in the where clause, the optimizer uses a correct plan, taking advantage of the two indices. If instead I use the two conditions in OR, ...
Categories: DBA Blogs

why Results cached on one instance cannot be used by another instance

Tue, 2018-06-26 22:26
Want to know why ? Each node in a RAC configuration has a private result cache. Results cached on one instance cannot be used by another instance. However, invalidations work across instances. To handle all synchronization operations between RAC i...
Categories: DBA Blogs

Does PGA have a segment that functions like buffer_cache in SGA

Tue, 2018-06-26 22:26
In direct path read, blocks are read into PGA directly from disk. Where in PGA hold the blocks? Is it work area? How long will PGA hold them? If I have a full table scan and after a while the same session does another full table scan on the same ta...
Categories: DBA Blogs

Listener Log file issue

Tue, 2018-06-26 22:26
hi, please help me to block the particular user entries in the Listener log file.because the size of file was increasing unexceptionally. that user is frequently accessing the db server.i dont want to log the entries for that particular user. ...
Categories: DBA Blogs

How to populate a TABLE type based on an OBJECT TYPE

Tue, 2018-06-26 04:06
Hi All I'm trying to create a table type based on a object type, but when I try to populate it, I get an error. I copied the syntax from several places, and still, it seems i'm doing something wrong. Here's the code <code> CREATE OR REPLACE ...
Categories: DBA Blogs

CONTEXT vs CTXCAT

Tue, 2018-06-26 04:06
Hi Tom, First off all I am newbie to this Oracle Text thing so please bear with me. I am currently evaluating the possibility and interest in changing the use of context indexes to ctxcat indexes in a big database that I use (the text columns ...
Categories: DBA Blogs

Oracle Undocumented Parameter - _PUSH_JOIN_PREDICATE

Tue, 2018-06-26 04:06
Hi, I have a long standing question regarding one of the undocumented parameters of Oracle namely "_push_join_predicate". I personally experienced scenarios where a query (involving multiple big joins) would show up - "Pushed Predicate" in t...
Categories: DBA Blogs

Os Authentication :Eat the cake and have it too

Tue, 2018-06-26 04:06
Hi Tim, Thanx for all your help to the Oracle Community. Is it possible to have a DB user identified externally and also have a DB password so he/she can log both ways Authenticated from os as well as using account name and password FOR...
Categories: DBA Blogs

Version control of pl/sql codes

Tue, 2018-06-26 04:06
Hi, I wanted to know that if there is any way by which we can create versions of the latest copy of pl/sql stored procedures/packages each time we compile. If yes , could you please provide a sample code for the same as currently we do not have...
Categories: DBA Blogs

Backup and Restore records from DB Objects (Table)

Sun, 2018-06-24 15:26
Wanted to check if there is some easy way to create a backup of the tables/schema and use some script that will load back the data from the backup file. I have DBCS access so can run the sh script as well. Please let me know if there is a simpl...
Categories: DBA Blogs

Spot the diffs between two database schemas inside SQL and PL/SQL code

Sun, 2018-06-24 15:26
Hi : I have two Oracle databases (say, PROD and TEST), and in a given schema (present in both DBs) I must assure that the same SQL code (inside views) and/or PL/SQL code (triggers, procs, funcs, packages) exists, disconsidering the non-functional dif...
Categories: DBA Blogs

Making API calls from Oracle database

Sun, 2018-06-24 15:26
In our office, Environment 1: we have an oracle database with Oracle APEX installed. Environment 2: We have a PCI complaint application with some NON-PCI APIs exposed via Kong We want to call the Non-PCI APIs from oracle database and were to...
Categories: DBA Blogs

Cancelling long running queries

Sun, 2018-06-24 15:26
Dear Tom, I'm wondering about how a 'cancel' in Oracle works. For example, I have a long running query and I define a timeout on application level. Once the timeout is reached, the application sends a 'cancel'. What i observed is, that the canc...
Categories: DBA Blogs

materialized view problem while refreshing

Sun, 2018-06-24 15:26
Hi We have have an ORACLE 8.1.7 database on suse linux 7.2 and we have a materialized view with joins and created a primary key constraint on the mview. The refresh mode and refresh type of the created mview is refresh fast on demand. ...
Categories: DBA Blogs

Pages