Tom Kyte

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...

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 ...

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...

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...

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...

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...

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 ...

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...

(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...

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...

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...

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...

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...

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...

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...

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, ...

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 ...

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; ...

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...

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...