DBA Blogs

How to set the INITTRANS value when Isolation level as Serializable

Tom Kyte - Mon, 2016-09-26 06:06
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.

Tom Kyte - 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????

Tom Kyte - 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

Tom Kyte - 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)

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Links for 2016-09-22 [del.icio.us]

Categories: DBA Blogs

use of application error

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Links for 2016-09-21 [del.icio.us]

Categories: DBA Blogs

DB Time

Tom Kyte - 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

Tom Kyte - 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

dynamic pl/sql - ora-00922

Tom Kyte - Tue, 2016-09-20 21:46
Hi Tom, I need to use dynammic sql in a stored procedure. I created an anonymous block to test it. The code fails at execute immediate with ORA-00922. But, when I run the code generated by the block, it works. Why doesn't it work in an anonymous bl...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs