Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 1 month 1 day ago

To compare two same tables from different schema without primary key and not same number of columns

Fri, 2019-12-20 08:55
We have table 'CUSTOMER' in two different schema's. Both are not having any primary key and the column numbers in both table do not match(i.e schema1 table can have 97 column other schema table has 101).the column names are same which are present in ...
Categories: DBA Blogs

PL/SQL code in packages on DB vs on APEX sites - how it affect performance?

Fri, 2019-12-20 08:55
Dear Tom, As you develop in APEX you can move all your PL/SQL code into packages or you can put all on APEX. How this affect performance? I know that it is better to move code to packages to make quick changes and have more control over code. ...
Categories: DBA Blogs

PLSQL

Fri, 2019-12-20 08:55
Requ:- If table do not have records then need to be inserted if have already the END DATE column only update with an no.of years based on Terms( For example If Term year is 10, then 10*12=120 Months, means..10 years needs to added to the END DATE col...
Categories: DBA Blogs

Merge Delete

Fri, 2019-12-20 08:55
How do I delete with a merge statement? I want to update MERGE_DELETE_TEST2 to match MERGE_DELETE_TEST1 (think ETL). I cannot get merge delete to remove the row that exists in MERGE_DELETE_TEST2 that does not exist in MERGE_DELETE_TEST1.
Categories: DBA Blogs

Import Production Dump to new schema

Fri, 2019-12-20 08:55
Hi, We have a live project which has only 1 schema. We are upgrading some features and so we are going to release it as version 2.0 . My question is that the production dump which is only one schema has to be to imported to 4 different schema o...
Categories: DBA Blogs

Check Constraints and Explain Plan Filter Predicates

Fri, 2019-12-20 08:55
Why does the Oracle SQL Optimizer include a filter predicate for a Check Constraint when generating an execution plan for a SELECT statement? If the constraint is valid (according to DBA_CONSTRAINTS), then the table rows are all compliant with the co...
Categories: DBA Blogs

Dynamic filters and arriving bind variables for them.

Fri, 2019-12-20 08:55
Team, we have an application, that used to search using any kind of filters on any colums - something like below. the procedure is used to return the resultset to the application, based on the WHERE clause being passed as input. when running...
Categories: DBA Blogs

Error while relocating database service

Fri, 2019-12-20 08:55
Hello, Ask Tom Team. <b>My environment: </b> I have a database running on 2-node RAC. I created a database service with TAF and transaction guard srvctl add service -db dbprod -service dbprod1_xa -preferred dbprod1 -available dbprod2 -fail...
Categories: DBA Blogs

Clob vs Binary XML storage

Sun, 2019-12-15 17:54
Hello Team, While doing poc for storing XML in ClOB storage and Binary XML storage ,I could see storing XML in Binary XML takes less table space as compared to CLOB .As far as I know both store XML in LOB storage.so why there is difference betwee...
Categories: DBA Blogs

ora-24247 when making an https call

Sun, 2019-12-15 17:54
Hi, I have a problem when making an https call inside a package. It doesn't appear to recognise the privileges granted to access the acl. When I call utl_http.begin_request in an anonymous plsql block or in a procedure with authid defined as cu...
Categories: DBA Blogs

HA and Failover in Oracle RAC

Sun, 2019-12-15 17:54
Hello, Ask Tom Team. I have some many questions about Oracle RAC HA and Failover. I was reading the info in below link and it help me a lot. But I still have some questions. https://asktom.oracle.com/pls/apex/asktom.search?tag=failover-in-rac...
Categories: DBA Blogs

Complex Query

Sun, 2019-12-15 17:54
I have a large number of orders (200) involving around 2000 diferent products and need to group the in batches of 6 orders. The task is to identify the best possible groups of orders so performance (human performance) can be maximized. As a start...
Categories: DBA Blogs

Can we call a procedure in select statement with any restriction?

Sun, 2019-12-15 17:54
hi tom plz tell me in simple example explanation Can we restrict the function invoke in select statement. Can we call a procedure in select statement with any restriction?
Categories: DBA Blogs

Gather Stats while doing a CTAS

Fri, 2019-12-06 17:53
Can you please provide your opinion on the below point. This is what I have noticed. When we create a table using a CTAS, and then check the user_Tables, the last_analyzed and num_rows column is already populated with accurate data. If it is so, ...
Categories: DBA Blogs

How can application control to explicitly call OCIStmtPrepare2 rather than OCIStmtPrepare when using pro*C

Fri, 2019-12-06 17:53
Our application got an ORA-25412: transaction replay disabled by call to OCIStmtPrepare. Oracle Version: 12.2. The Oracle runs in RAC mode. After searched on the internet, we found below explanation: <i>This call(OCIStmtPrepare) does no...
Categories: DBA Blogs

'BEFORE CREATE ON SCHEMA' trigger apparently not firing before Create Table

Fri, 2019-12-06 17:53
In Oracle 8.1.7 instance set up with characterset US7ASCII <code> Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production With the Partitioning option JServer Release 8.1.7.4.0 - Production SQL> create table t1 (c1 varchar2...
Categories: DBA Blogs

Left Joining Four Tables without duplicates from right tables or Cartesian product!

Fri, 2019-12-06 17:53
I am running the query below to get data from 4 tables, but the problem that data is fetched as Cartesian product, in other words, items from tblEdu is being duplicated with items from tblTrain <code> SELECT tblpersonal.*, tbltrain.*, tbledu.*,...
Categories: DBA Blogs

Observation regarding Interval partitioning

Mon, 2019-12-02 17:52
<b>Hi, <b>I had some observation regarding Interval partitioning when I was looking into one issue,</b> Below is the use case </b> For ex : We have two tables <code>CREATE TABLE TEST_GURU_1 ( ENAME VARCHAR2(500), EMPDATE DATE ) ...
Categories: DBA Blogs

Parallel execution of procedure like multithreading in java

Mon, 2019-12-02 17:52
Hi All, Consider below sample Data model: Application is maintaining information of different countries - States -Cities (each of this is individual tables). At the end of quarter we are doing assessment and calculating different metrics at countr...
Categories: DBA Blogs

A stored procedure taking 98% CPU time as a percentage of Elapsed Time

Mon, 2019-12-02 17:52
A stored procedure taking 98% CPU time as a percentage of Elapsed Time. Please provide us the fix and recommendations to check
Categories: DBA Blogs

Pages