Tom Kyte

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

Help with ANSI outer join

Tue, 2016-09-27 18:26
I am not getting same records when converting from oracle outer join (+) to ANSI outer join. Could you please take a look and check what I am missing. How do I write ANSI outer join to return same resultset for below example? <code> -- Cre...
Categories: DBA Blogs

Unknown session

Tue, 2016-09-27 18:26
Hi Tom, One of my oracle procedure is not giving desire performance. From last few days I am monitoring sessions for that particular procedure and I am getting such sessions those are not written inside code. I just wanted to know does the oracle ...
Categories: DBA Blogs

Grant privilege to role with admin option Vs Grant role to user with admin option?

Tue, 2016-09-27 18:26
Hello Tom, I am little confused between following two CASES, though I know the result but I am not very convinced with the reason behind it. (Say) A role ROLE1 is created along with USER1 and USER2 by dba <i><code>connect /as sysdba create...
Categories: DBA Blogs

FETCHING NULL DATE IS DATE IS NOT MATCHING IN WHERE CONDITION

Tue, 2016-09-27 18:26
Sir, There is records month wise like DATE1 ----------- 10/2015 11/2015 03/2016 06/2016 09/2016 I want to get result like this from start date to end date start date := 06/2015 end date := 12/2016 note: when condition would n...
Categories: DBA Blogs

Connection between Mysql and SQL Developer Datamodeler

Tue, 2016-09-27 00:06
Hi Tom, Im trying to connect Oracle SQL Developer Datamodeler with Mysql but nothing work. I can connect Oracle SQL Developer with Mysql with an J Connector, but when I do the same steps on SQL Datamodeler (add an extension in the Third Party JDBC) ...
Categories: DBA Blogs

How to copy a DBMS_XMLDOM.domnode between DBMS_XMLDOM.domdocument objects

Tue, 2016-09-27 00:06
I'm having trouble using the DBMS_XMLDOM package. I can hardly find more online than the API (which isn't that great) and a few "here's how you build an XML document" introductions, with no tutorials on more advanced uses. I am trying to write a file...
Categories: DBA Blogs

Database Queries Slow After DB Re-Import

Tue, 2016-09-27 00:06
After initial creation of user and import of db, queries were fast. Used following commands: " CREATE USER TESTUSR IDENTIFIED BY TESTUSR default tablespace TESTTAB quota unlimited on TESTTAB ACCOUNT UNLOCK; GRANT CREATE SESSION TO TESTUSR; GRAN...
Categories: DBA Blogs

RMAN using dbms_pipe

Tue, 2016-09-27 00:06
Hello I wrote a PL/SQL package which allows me to control RMAN out of SQL*Plus using the RMAN syntax. To implement it I used the dbms_pipe/dbms_scheduler functionality which starts the external rman process on OS level and call the rman binary ...
Categories: DBA Blogs

Direct path read temp wait event issue

Tue, 2016-09-27 00:06
Hi Tom, I have series of job which are configured daily & every day its been completed within expected time 7 mins. But last 4 days back this job is behaving like any thing its taking more than 2 hours. Its going for more sort operations & waiting...
Categories: DBA Blogs

select from table type

Tue, 2016-09-27 00:06
I have created a type below is the code of the type <code>create or replace type tt_name as table of varchar2(250);</code> Now I want to perform select on this type in a plsql code select listagg(column_name,';') within group(order by colum...
Categories: DBA Blogs

Order by - varchar column storing numeric values

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

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

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

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.

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

Pages