Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 2 months 1 week ago

Create table replicate along with dependents in different schema

Tue, 2019-03-12 15:26
Hi Chris/Connor, I have one requirement where we need to create a replica of table in different schema and different table space (schema and table space will be inputs to the scripts)in same database server.. This we need to do using plsql only so ...
Categories: DBA Blogs

How to distinguish "Semantic error" and "Syntactic error"?

Tue, 2019-03-12 15:26
Hello Tom, Is there a way, in a PL/SQL programm, to manage differently "Semantic error" and "Syntactic error"? In the bloc "EXCEPTION", I wish to count these kind of errors, how can I do? I found nothing in the documentation and I don't t...
Categories: DBA Blogs

Need rank based on person_no column

Tue, 2019-03-12 15:26
Hi All, I have column ord which is common for all the records , then person_no. This can be repeated. I have a column called flag which is a sequence. I was trying populate a new column(NEW_FLAG) which will basically rank the person_no according...
Categories: DBA Blogs

Using connect by level to generate dates and times

Tue, 2019-03-12 15:26
I have two SQL Queries: SQL Query 1: <code>select to_date(:sDate,'dd-mm-rrrr')+(level-1)DateRange from dual connect by level <= ((to_date(:endDate,'dd-mm-rrrr')-to_date(:sDate,'dd-mm-rrrr')) + 1);</code> SQL Query 2: <code>select level,to...
Categories: DBA Blogs

Number Data Type Precision

Tue, 2019-03-12 15:26
Hi, From Oracle Docs "The absence of precision and scale designators specifies the maximum range and precision for an Oracle number." https://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#SQLRF30020 When i declared number with ou...
Categories: DBA Blogs

Updating a table when a match is found in another table

Mon, 2019-03-11 21:06
Hi Tom, I have a table (T1) and a view is created upon T1 by masking few PI information say (V1) T1 area_code integer (100 USA, 200 UK) id integer Name string age integer DOB date type string (Individual/Company) V1 (Masked versi...
Categories: DBA Blogs

Not getting required date after using TO_CHAR function with DATE column

Mon, 2019-03-11 21:06
<code> create table demo_tab(id number(2),name varchar2(50),from_date date); Table created. insert into demo_tab values(2,'AUDI','04-MAR-2019'); 1 row created. insert into demo_tab values(3,'BMW','06-MAR-2019'); 1 row created. i...
Categories: DBA Blogs

Date format

Mon, 2019-03-11 21:06
Hi, I have data in the format 2019-02-21T13:58:18.000+00:00 in a varchar2 column(loaded into staging table from a CSV file). When merging this staging table I need to put this value into a DATE data type column. How to do this ? Format of the dat...
Categories: DBA Blogs

TO_DATE() on SYSDATE failed in insert all query.

Mon, 2019-03-11 21:06
Hello, Yesterday I used insert all query to enter 8 rows in two tables directly from toad. At that time I used TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI') for inserting sysdate in my table (I know sysdate was enough but 'coz of since last four m...
Categories: DBA Blogs

Database operations monitor 12c

Mon, 2019-03-11 02:46
Team, Was reading about Real time operations monitor in Oracle 12c @ <u>https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/monitoring-database-operations.html#GUID-DC573FB7-40C5-4D6D-BE81-EF356900B444 </u> and here is a quick...
Categories: DBA Blogs

SQL fetch phase need library cache lock/pin

Mon, 2019-03-11 02:46
Hi , thks for all your works here,i've learnd much a lot! now, i have some question on <b>SQL fetch phase</b> and <b>library cache lock/pin </b>which stuck me for days. here is something i find from some website, <i> SELECT statement follow...
Categories: DBA Blogs

Need to find the Oracle EBS Version 12 Data Model

Mon, 2019-03-11 02:46
Need to find the Oracle EBS Version 12 Data Model
Categories: DBA Blogs

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

Pages