Tom Kyte

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

Full Table Scan on join query

4 hours 3 min ago
Hello, Ask TOM Team. I have two tables: TABLE1 (parent) and TABLE2 (child). TABLE1_ID (FK) on TABLE2 table has an index. When I run a join query, I got a table access full. There are ~4M rows in the result set. <b>select * from user.table1 t ...
Categories: DBA Blogs

explain plan for index scan

4 hours 3 min ago
Hi Tom, i have updated the plan.i was unaware how to submit the further information on the questions asked, thats why put the questions again. this is with reference to question asked at below link https://asktom.oracle.com/pls/apex/asktom.sear...
Categories: DBA Blogs

Materialized View On Commit Refresh failure

4 hours 3 min ago
Hi Team, Thanks a lot for all the support you have provided so far :) I have created an On Commit Materialized view MVA recently on tableA. Primary Key M View log is used here. Occasionally this refresh fails with ORA-12034 error. Since thi...
Categories: DBA Blogs

CLOB to XMLTYPE out of memory issue

4 hours 3 min ago
I am copying XML data from Operating System file into CLOB and then casting as XMLTYPE for extracting information into relational database. This solution is working for years but I received a business requirement to process big XML files ranging fro...
Categories: DBA Blogs

BLOB Columns In tables

4 hours 3 min ago
Hi Tom, This is a general question about the design of the tables in a DAILY use database. The users will uploading and downloading pdf files from a web app daily. What i want to ask is it a good idea to create my tables with BLOB columns and savi...
Categories: DBA Blogs

Difference between select * and select a.* with alias on a table

4 hours 3 min ago
Hi Tom, What is the difference between using "select * from table1" and "selelct a.* from table1 a". Observed that while doing the select statement "select a.* from table1 a" locks the data for update (like if we use the statement from PLSQL dev...
Categories: DBA Blogs

Listagg Vs Stragg

4 hours 3 min ago
Tom: I was benchmarking stragg with listagg and results below, is that stragg is better than listagg? Please let me know your thoughts on this. <code>drop table t purge; create table t as select * from ( select * from all_objects ...
Categories: DBA Blogs

Explain conversion rules used in NVL2

Tue, 2020-05-26 16:26
Please explain the conversion rules used for this SQL statement (we found some rules https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/NVL2.html#GUID-414D6E81-9627-4163-8AC2-BD24E57742AE but could not apply in this case): <code...
Categories: DBA Blogs

Create MVW takes 15 mins. Refresh of same MVW takes 12 hours (or mostly failes)

Tue, 2020-05-26 16:26
We have a materialized view, which has undergone some tuning. When creating the new MVW, it took 15 mins. The MVW is then refreshed using a shell in which many MVWs are refreshed in order. As part of the refresh, to make it faster, the indexes on ...
Categories: DBA Blogs

Formating SQL commands output from within RMAN

Tue, 2020-05-26 16:26
Is it possible to format SQL interface commands output in RMAN .. at least some basic like >linesize< ? Regards Dejan
Categories: DBA Blogs

ORA-02298: cannot validate (APEX_200100.WWV_FLOW_DATA_SESSION_FK) - parent keys not found

Tue, 2020-05-26 16:26
Hi, Today I upgraded one of our Oracle APEX environment from 19.2 to 20.1. The upgrade was successful except that I found the following error in the installation log: <code># Error: ORA-02298: cannot validate (APEX_200100.WWV_FLOW_DATA_SESSION_F...
Categories: DBA Blogs

ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind

Tue, 2020-05-26 16:26
This is just weird. It works fine on 12.1. On version 19 it requires the "workaround" used in foo3.
Categories: DBA Blogs

Assigning values into table type variable within loop results in error - PLS-00382: expression is of wrong type

Tue, 2020-05-26 16:26
Please refer the script I attached with this question - There are records which are supposed to be stored in table type variable as a result of cursor loop on table - test_mgr_hierar5. The table type is expected to hold values - 177603,157564,15...
Categories: DBA Blogs

automatic partition naming

Tue, 2020-05-26 16:26
hi , is there any way to give a patern name to automatic partitions when using this DBMS_REDEFINITION.start_redef_table package. thank you by advance.
Categories: DBA Blogs

How to Restore ASM Based OCR After Complete Loss of the CRS Diskgroup on Linux/Unix Systems for the 18.c version

Fri, 2020-05-22 02:26
I have seen tho note: "How to Restore ASM Based OCR After Complete Loss of the CRS Diskgroup on Linux/Unix Systems (Doc ID 1062983.1)" This note: "APPLIES TO: Oracle Database - Enterprise Edition - Version 11.2.0.1.0 and later" In the step ...
Categories: DBA Blogs

Making a transparent application database switchover with Data Guard

Fri, 2020-05-22 02:26
Hello, Ask TOM Team. I have a 2-node RAC production database (18.6) with Data Guard configured (using data guard broker). My app's connection strings are pointing to scan-name\db-service. What are the steps to make a database switchover withou...
Categories: DBA Blogs

Strange Behaviour with Oracle_Home and trailing slash

Fri, 2020-05-22 02:26
Hi, I have a server hosting several instances based on the same oracle engine 19.3 (19.0.0.0). As an example, let's consider DB1 and DB2. We connect to the server with a user account member of dba group. In the same user session : <code> e...
Categories: DBA Blogs

Clarification about A-Time column in execution plan

Fri, 2020-05-22 02:26
Hi Experts, I've the following execution plan. As you can see, the "A-Time" of operation 3 is bigger than its parent. Operation 2 behave in the same way with it's parent. Furthermore operation 0 has only 0.03 second. How can this happens? The "A-T...
Categories: DBA Blogs

View pdf file (saved in db server directory)through form or report in oracle ebs

Fri, 2020-05-22 02:26
I have this file named for example" contarct1.pdf" that is saved in the database server directory I can retrieve that directory path an details when i query from "dba_directories" view I need to view this pdf file to user with any of the two op...
Categories: DBA Blogs

Scheduler Jobs not starting at the requested time

Fri, 2020-05-22 02:26
Solaris 11.4 Oracle 12.2 Hi, we have about 70 Schedueler jobs defined, which are starting in intervalls from 1 minute to about 1 month. Sometimes (about 2 to 3 times a weeks) there are time-windows of about 1 to 60 minutes , in which none of th...
Categories: DBA Blogs

Pages