Tom Kyte

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

Order by - varchar column storing numeric values

13 hours 19 min ago
Hi Tom I have a table 'LOCS' with 2 columns of varchar datatype (col1 is varchar yet at the moment we have only numeric values stored): col1 col2 2272 ABC 22722 ABCD 1000 dgdfg 10001 dfm Now, when I query the table: select * from locs...
Categories: DBA Blogs

Record count on basis of insert/delete triggers

13 hours 19 min ago
Hi, We have a pagination requirement across the UI foothold where we need to show the total# of records for significant db facts off the tables in view of numbers. And the query are not utilizing query filter criteria as triggerd by a menu option ...
Categories: DBA Blogs

dbms_job taking a long time

13 hours 19 min ago
Hi, There is a SQL program whose job is to pick up data from multiple tables based on an application ID and populate an MIS table. There are 25-30 queries written to fetch data from multiple tables for an application ID. Data is pulled into col...
Categories: DBA Blogs

How to set the INITTRANS value when Isolation level as Serializable

13 hours 19 min ago
Hi Tom, We current need to set the serializable isolation level, and will enable the ROWDEPENDENCIES when create the table like as below. My question is what is an optimal value for INITRANS? Or what should we consider when set this parameter? Tha...
Categories: DBA Blogs

Executed PL/SQL kept in Stored procedure but while compile Compilation error is coming.

Fri, 2016-09-23 04:46
Hi Tom, Below PL/SQL is working fine. while same code of PL/SQL as below is used in Stored procedure compilation error is coming while compiling stored procedure. Could you please make a suggestion to keep below PL/SQL in below Stored Procedure so...
Categories: DBA Blogs

How to compare two tables of data????

Fri, 2016-09-23 04:46
Hi Tom, I have two tables of values(dept1 and dept2). How do I compare all the data in these two tables??? It will return true if both tables contain the same data & false for otherwise... Another thing is that I do not know how to use CREATE OPE...
Categories: DBA Blogs

Java Connection Pooling with Oracle VPD

Fri, 2016-09-23 04:46
Hi Tom, We have a 3-tier application that is built on Java and Oracle. In our application, we extensively make use of Oracle VPD policies for setting contexts and managing the data. Now, we are building in Java something on top of Oracle. We hit ...
Categories: DBA Blogs

PlSQL- Bulk Collect and Update (Better Approach)

Fri, 2016-09-23 04:46
Hi Tom, I am looking for a better coding approach than what I have in my current system. I have two tables dog_owner(16 Million Records) and dog_owner_stage(8 Million Records). In the current process. I usually insert based on a common owner_accou...
Categories: DBA Blogs

Performance issue in CLOB\BLOB data migration

Fri, 2016-09-23 04:46
(did not get any answer for https://asktom.oracle.com/pls/apex/f?p=100:24:0::NO::P24_ID:9531842300346462307 ) Hello Tom, First of all, i would like you to thank you for your immense support on Database issues.It helps us a lot !! Question : M...
Categories: DBA Blogs

Compile_Error when refreshing a Materialized View from a procedure

Fri, 2016-09-23 04:46
We have Materialized Views which reference tables in other schemas. We can refresh/compile the Materialized Views from the command line however when we refresh/compile the Materialized View from within a procedure the job immediately aborts with...
Categories: DBA Blogs

Practise question

Fri, 2016-09-23 04:46
Hi, I was practicing some question on sql challenge about dml operation using multiple tables and got some doubt. CREATE TABLE plch_departments ( department_id INTEGER PRIMARY KEY, department_name VARCHAR2(30) ) / CREATE TABLE p...
Categories: DBA Blogs

Optimiser Trace

Fri, 2016-09-23 04:46
Hi Tom, A tricky question, recently we upgraded our systems to 11.2.0.4 and started to observe some queries taking much longer (from mins to 10+ hours). On analysing the taces / explain we found the access path had changed from the previous one, so...
Categories: DBA Blogs

Converting an EE DB 12.1.0.2.0 to SE DB 12.1.0.1.0 - Version - empty table

Fri, 2016-09-23 04:46
Hi, Im trying to convert EE 12.1.0.2.0 to SE 12.1.0.1.0 with expdp/impdp. I've found example of converting EE 11.2.0.1 to SE 11.2.0.4 where it is stated: "During import to standard edition we must use keyword VERSION=11.1 to be able to import e...
Categories: DBA Blogs

use of application error

Thu, 2016-09-22 10:26
Hi , I wanted to know is pragma exception init and raise_application_error does the same thing? I wanted to know what is the difference? Also, I wanted to know what is the use , if I place my exception declaration in the package specificat...
Categories: DBA Blogs

Intermittant ORA-08103: object no longer exists

Thu, 2016-09-22 10:26
In a batch job (in java), I am getting the below error details while reading the data (in a batch job). And this error is intermittant with one table on first run and in later runs the table keeps on changing. Before every job run, i will truncate th...
Categories: DBA Blogs

2 level subqueyries and problem select function result

Thu, 2016-09-22 10:26
Hello everybody, I've 2 problems with on 2 queries : 1) The following query <code>with pres as ( select t1.IDPROD AS spe1 , t2.IDPROD AS spe2 ,t1.INDICELIGNEPRESCRIPTION as id_ligne_1,t2.INDICELIGNEPRESCRIPTION as id_ligne_2, ...
Categories: DBA Blogs

SQL Query

Thu, 2016-09-22 10:26
Hi Tom, I have a table like this create table temp1 (f_name varchar2(100), f_date date, f_amount integer ); Records in this table will be like this: Ajay 15-JUL-02 500 Bhavani 15-JUL-02 700 Chakri ...
Categories: DBA Blogs

DBMS_ADVANCED_REWRITE

Thu, 2016-09-22 10:26
Tom, I was trying to tune one of the sql written in Ansi syntax without toughing using dbms_advanced_rewrite API. but ended up with the below error, Can you help me what i am missing here? <code> rajesh@ORA10G> declare 2 l_src long; ...
Categories: DBA Blogs

DB Time

Tue, 2016-09-20 21:46
Hi Tom, I have been reading your questions/answers thread and like it. Want to ask you one simple question. Database Time= CPU Time +Non-Idle Wait; I see in my database, CPU Time is more all the time and Non-Idle Wait is less. say for exampl...
Categories: DBA Blogs

Materialized View

Tue, 2016-09-20 21:46
Hello Tom, What are the best practices with MV - <1> a) generate MV on the DB that owns base tables - and refresh MV and populate over the DB links to other DB's or b) Pull data from the source DB over the DB link and populate the MV from...
Categories: DBA Blogs

Pages