Tom Kyte

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

Information related fro AWR/ASH

Mon, 2019-03-11 02:46
Dear Sir's, Please help me with good link or book for AWR/ASH report analyzer and various wait events including RAC. Also want to seen behind rollback segment header contention in oracle. Please help me to sort this. Oracle version: 12.2....
Categories: DBA Blogs

Index skip scan with high NDV leading column

Fri, 2019-03-08 00:26
Hello Tom, I have the situation: A table tbl (220 Mio recs) the following query <code>select col_a, col_b, col_c from tbl t where Mod(Dbms_Rowid.Rowid_Row_Number(t.rowid, 2) = 0 --pred 1 and col_d =<lit>; --pred 2</code> col_d is in...
Categories: DBA Blogs

ORA-29279: SMTP permanent error: 502 Command not implemented while executing a mail proc

Fri, 2019-03-08 00:26
Hi Tom, Very Good Morning ! <b>First let me make sure, here no sample table data is needed . </b> Usually for all of the scheduled procedures, we come with some kind of mail procedures, where in we get either success/Failure mail once the j...
Categories: DBA Blogs

Estimate database size from archive

Thu, 2019-03-07 06:06
I am new to Oracle...and am being asked by a friend. He has a Remedy (Oracle dB) system and an archive that is 37 MB. He wants to move the archive to a new system... How large will the database be from this archive instance? What are your recom...
Categories: DBA Blogs

How can I show special characters in ascii values

Thu, 2019-03-07 06:06
Hi Tom, I would like to show many characters ascii values and I use this sql below <code>set serveroutput on declare c varchar2 (100); a number (3); begin for i in ( select 'abcde' from dual ) loop ...
Categories: DBA Blogs

JSON_SERIALIZE versus JSON_QUERY

Thu, 2019-03-07 06:06
Hi Beda, What is the added value of JSON_SERIALIZE(expr) compared to JSON_QUERY(expr, '$')? Are there significant differences in the actual code executed underneath? Thanks in advance, Stew Ashton
Categories: DBA Blogs

Migration to 18c from version 10g

Wed, 2019-03-06 11:46
Hey folks, We have been stuck on 10g for a while (couldn't migrate off forms and reports apps) and now find we are positioned to drop our forms & reports. We're looking to migrate the db from 10g to 18. Has anyone done this kind of a jump before...
Categories: DBA Blogs

Unwanted results in SQL query

Wed, 2019-03-06 11:46
Hello, Ask Tom Team. I have a SQL query but I'm getting unwanted results. <code>SELECT t1.invoice_sender,t1.einvoice,t3.modified_einvoice,SUBSTR(t3.modified_einvoice,2,2),t2.LOADED_606,t4.APPROVALS FROM table1 t1 INNER JOIN table2 t2 on ...
Categories: DBA Blogs

Unable to install 18c Expression Edition

Wed, 2019-03-06 11:46
We are unable to install 18c Expression Edition in Windows 10 it is extracting and in middle, it is start rollback. Finally below the below message. Below is the message. The wizard was interrupted before Oracle Database 18c Express Edition c...
Categories: DBA Blogs

Different Plans in SQL and PL/SQL

Wed, 2019-03-06 11:46
Hi, in a PLSQL package I generate and execute dynamic SQL Statements. I wrote a litte procedure, which returns the output of dbms_xplan.display into a log table: <code> FUNCTION logPlan(p_statament VARCHAR2) RETURN CLOB IS PRAGMA autonomous_t...
Categories: DBA Blogs

Caching for PLSQL packages over ORDS

Tue, 2019-03-05 17:26
I need to cache few values when the plsql procedure is called through a rest service multiple times i.e. when it is executed from a same user multiple times for optimization. I am calling below package procedure through ORDS rest service. Belo...
Categories: DBA Blogs

Replacing card number with *

Tue, 2019-03-05 17:26
I have a 16 digit card number where I need to replace the 3rd digit of the card number to 9th digit card number with *.Tried regular expressions,replace and translate nothing worked.Need guidance in this tom!!
Categories: DBA Blogs

Declare a dynamic table type

Tue, 2019-03-05 17:26
Hello, I have a stored procedure which takes 2 input paramaters - owner and table_name. I would like to create a TYPE variable based on what is passed. This is the code: <code> create or replace PROCEDURE proc ( in_owner IN VARCHA...
Categories: DBA Blogs

Why are Index Organized tables (IOTs) not supported by interval partitioning

Mon, 2019-03-04 23:06
Hi tom, Could you please let me know why we can not use interval partitioning on index organized tables. Thanks in advance
Categories: DBA Blogs

How can I export the tablespace that has been dropped one of the datafile?

Fri, 2019-03-01 03:26
Hi,Tom During my work,i have met such question,and hope yr answer. 1. I wrongly create a datafile under the tablespace DRSYS,then I alter database 'datafile ' offline, and drop this datafile under OS command.Now the troubles arise. W...
Categories: DBA Blogs

Oracle instant client 12c EZConnect Using tcps instead of tcp

Thu, 2019-02-28 09:06
I download oracle instant client 12.2.0.1.0, I try to use sql loader to load csv data to the database. I can successfully load using following: <code>sqlLdr.exe userid=user/password@//192.9.200.228:1521/oracle ERRORS=4000 control=D:\temp\csma\xx_2...
Categories: DBA Blogs

export and import taking a lot of time

Thu, 2019-02-28 09:06
Hi, I need to export and import a table having 2 million data in it. using exp/imp command . I tried using the below commands but it took a lot of time to export and then import the data. Please help. Commands :exp test/test full=y file=REL...
Categories: DBA Blogs

How to generate DDL for APEX app from code

Thu, 2019-02-28 09:06
In Oracle SQL Developer, 'Application Express' section, we can export DDL for an APEX app by context menu, 'Quick DDL' > 'Save to Worksheet' menu. I'd like to do the same by executing some SQL or PL/SQL code. I know we have DBMS_METADATA.GET_...
Categories: DBA Blogs

Reading Header Info from CSV

Thu, 2019-02-28 09:06
Hi Team My csv looks like below 123456,20,20,1500 --- this is the header abc,efg,hij,klm,nop,qrs abc,efg,hij,klm,nop,qrs abc,efg,hij,klm,nop,qrs abc,efg,hij,klm,nop,qrs I need to read the header info. Can you pls tell me whats the best ...
Categories: DBA Blogs

Emails sending out of 12c SuperCluster Database

Wed, 2019-02-27 14:46
Oracle version: <code>SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 26 15:14:07 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Tue Feb 26 2019 14:05:04 -06:00 Connected to: Oracle Database...
Categories: DBA Blogs

Pages