Tom Kyte

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

Synchronous refresh in mview ORA-31922: Foreign key must contain partition key in table

Tue, 2020-02-18 06:11
Team, Here is my testcase which got failed during Synchronous refresh in mview. <code>create table products as select rownum as prod_id, object_name as prod_name, object_type as prod_category, object_id as prod_category_id, data_object...
Categories: DBA Blogs

Oracle 19C database issue with table types and pipelining

Tue, 2020-02-18 06:11
I have a package working fine in 11g version. But when I deploy the same package in 19c version, the behavior is different. PFB the description. Package specification has an cursor and created a table type with cursor%rowtype. Having a pipel...
Categories: DBA Blogs

Sqoop ojdbc8.jar throws error ORA-06502:PL/SQL:: numeric or value error

Tue, 2020-02-18 06:11
Hi Tom, We are trying to do a sqoop import to hive from Oracle and struck with a weird error below: WARN[main] org.apache.hadoop.mapred.YarnChild: Exception running child : java.io.IOException: java.sql.SQLExcepion: ORA-00606:error occurred ...
Categories: DBA Blogs

Scheduling Compilation or Execution of Stored Procedures

Tue, 2020-02-18 06:11
Hello We have a Oracle 11g Release 2 database with five identical working Schemas being accessed by a VB Client Server business application The db server, with 32GB RAM just hosts this one database Copies of Client applications are installed a...
Categories: DBA Blogs

Finding when someone dropped an object

Tue, 2020-02-18 06:11
Hi Team, I have a DB, where a table is dropped from the schema accidentally. We are trying to find whether it got dropped due to manual execution of DROP query and by whom? Is there any way that we can find that the DROP query which is executed ...
Categories: DBA Blogs

How to fetch part of a string for LONG datatype

Tue, 2020-02-18 06:11
HI, I am writing a query to find missing table partitions for next year using all_tab_partitions table, I am able to fetch the records with the help of column partition positions, but I have to extract the last partition date (YYYY-MM-DD) from HIG...
Categories: DBA Blogs

Average of 0 and Value - gives incorrect output. Is there a way to ignore the 0 during the average function.

Fri, 2020-02-14 21:11
Hi Tom, I am having a SQL output as follows. <code>A B C D E ---------------- ---------- ---------- ------------ ----------- 2020-02-12 221 68677 99.6...
Categories: DBA Blogs

Indexing strategy for dates in a query

Fri, 2020-02-14 21:11
Hello, Ask Tom team. I have the following query: <code>SELECT guid, sender_id, doc, status, arrived_date, register_date, last_updated_date FROM user1.table1 WHERE (sender_id=:SENDER OR :SENDER IS NULL ) AND (status=:STATUS OR :status IS NU...
Categories: DBA Blogs

Left padded String based on sub-string length

Fri, 2020-02-14 21:11
Hi Chris, I want to restrict the length of the input string to 8 characters by adjusting all the digits (after 'MFT' in below example) from Input string. Means, want to accommodate all the digits in string. Ex.1. Input String is 'MFT123456' ...
Categories: DBA Blogs

Why commit/rollback or any DDL command not allowed in trigger or function?

Thu, 2020-02-13 21:10
Hi Tom, Theoretically I know that commit/rollback/DDL or anything that causes transaction to end are not allowed in a trigger and function if calling function in SQL statement. To use any of those in trigger/function we can use PRAGMA AUTONOMOUS T...
Categories: DBA Blogs

Want to replace a particular string with a null value

Thu, 2020-02-13 21:10
We have 2 types of record_data format in table speedwing table 1st type --> <code>..NTP ID MT20190125 - NTP PRODUCT META BASIC FIX 2019 - TRAVEL START DATE 24/01/2019 - TRAVEL END DATE 31/12/20' ..NTP ID MT20190125 - NTP PRODUCT META BASIC FIX 2...
Categories: DBA Blogs

Roles granted to other roles

Thu, 2020-02-13 21:10
Is it true that roles can not be granted to other roles anymore? I am unable to find documentation of this, but was informed that this was taken away in 12c. If this is true, will you please post the document?
Categories: DBA Blogs

Unable to load jar using dbms_java.loadjava in Oracle

Thu, 2020-02-13 03:10
I need to upload a jar file in Oracle RDBMS using dbms_java.loadjava method. I have granted all the required permission and able to run below function successfully. <code>create or replace function get_java_property(prop in varchar2) return varch...
Categories: DBA Blogs

LINESIZE and displaying data on a screen : the biggest part of execution time?

Thu, 2020-02-13 03:10
Hello Masters, I have one big question about the SQL*Plus parameter LINESIZE and the display of datas. I read in documentation Oracle 19 SQL*Plus : https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/sqlplus-users-guide-and-refe...
Categories: DBA Blogs

Transform IF ELSE END IF TO CASE WHEN

Thu, 2020-02-13 03:10
I need to use PL-SQL or SQL to do my query. First: I want to transform all the IF..END IF used in the body of function F_CALCUL_TAUX to CASE..WHEN..END, and use the result inside my query. Secundo: It's possible to transform that and use it inside ...
Categories: DBA Blogs

Function for alphabetical sequence like a spreadsheet

Thu, 2020-02-13 03:10
I need function which convert numeric to alphabet like when I input 1 then it will return 'A', when i input 2 then it will return 'B' please help me on this.
Categories: DBA Blogs

Index rebuild is taking long time before partition exchange

Wed, 2020-02-12 06:04
Hi Tom, I have a work table where I will do all the calculations and do partition exchange to main table. This work table is truncate & load. This process has couple of steps. 1. I will copy few records from main table to work table and ca...
Categories: DBA Blogs

Autotrace traceonly

Wed, 2020-02-12 06:04
Hi, Could you please help to understand: How can I get the execution plan with all details like A rows E rows, etc when trace only enabled. I always get basic plan details like rows bytes and cpu% only. It would be helpful if you could share an...
Categories: DBA Blogs

UPDATE scenario - facing ORA-01427

Wed, 2020-02-12 06:04
Hello Pls see the UPDATE statement below ... I am faced with ORA-01427 when running this Have also tried "... WHERE EXISTS (SELECT 1 ..." but have not got it right I need to update the TAXINV and TAXINVDATE columns in TBLTARGET where TAXINV i...
Categories: DBA Blogs

Oracle Entity Framework Core 3.x

Tue, 2020-02-11 18:04
When will the https://www.nuget.org/packages/Oracle.EntityFrameworkCore/ support dotnet core sdk 3.1 ?
Categories: DBA Blogs

Pages