Tom Kyte

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

Spatial queries involving lattitude and longitude

Tue, 2018-10-30 02:26
Team, We have a table in an application that has longitude and lattitue as NUMBER datatype. also composite index exists on the columns (LATITUDE, LONGITUDE) in this order. the sql from the application goes like this. select * from the_tabl...
Categories: DBA Blogs

ORA-08103: object no longer exists

Sat, 2018-10-20 23:06
Hi Chris/Connor, Could you please have a look ate below scenario: Whenever we process orders in bulk lets say bunch of 1000 orders, out of which certain orders are failing in the MERGE statement with an error "ORA-12801: error signaled in paralle...
Categories: DBA Blogs

Key-preserved table concept in join view

Sat, 2018-10-20 23:06
I see a lot of examples where primary keys are missing or indexes are not unique. But I didn't see anything matching this example. There are 3 tables joined, and the 3rd table has a compound key that gets one value from the first table and one from...
Categories: DBA Blogs

How to extract XML data using extract function

Thu, 2018-10-18 16:06
inserted row by using below statemet :- <code>insert into xmlt values('<?xml version="1.0"?> <ROWSET> <ROW> <NAME>karthick</NAME> <SALARY>3400</SALARY> </ROW> <ROW> <NAME>c</NAME> <SALARY>1</SALARY> </ROW> <ROW> <NAME>mani</NAME> <SALARY>1</SAL...
Categories: DBA Blogs

Elaborate why 5 & same table used in below query

Thu, 2018-10-18 16:06
<code>select distinct * from t t1 where 5 >= ( select count ( distinct t2.sal ) from t t2 where t2.deptno = t1.deptno and t2.sal >= t1.sal );</code> I'll be grateful if you can explain. how the number work ,5, without var...
Categories: DBA Blogs

Find if a string is Upper, Lower or Mixed Case, numeric, Alpha Numeric etc

Wed, 2018-10-17 21:46
Dear Experts, I populated a table with few rows of strings that are Upper/ Lower/ Mixed case, alpha-numeric, numeric etc. 1. Now I would like to evaluate they type of string using a case statement. I tried using regexp_like, but it fails when ...
Categories: DBA Blogs

Deadlock issue came while using set based sql

Wed, 2018-10-17 21:46
Hi Tom, We are using set based sql in my process, In that we are creating so many GTT tables in a package. And we are executing this package concurrently in more than ten sessions, these sessions will create temporary tables with different name a...
Categories: DBA Blogs

Foreign Keys with default values

Wed, 2018-10-17 21:46
Hello. I'm designing a database in Oracle 12.2 in Toad Data Modeler. It would get lots of inserts. I'm using identity columns as PK (basically I create a sequence and use it as default value in the column, sequence.nextval). When I connect the...
Categories: DBA Blogs

Migration of 6i Forms to APEX

Wed, 2018-10-17 21:46
Hi Team, I am trying to migrate forms 6i to APEX, but problem that i pose here is that i cannot completely migrate all the functionalities of my forms to Apex even after trying to correct Metadata it does not migrate forms completely. So, my q...
Categories: DBA Blogs

Error while opening Database

Wed, 2018-10-17 03:26
HI,THERE I HAVE A SITUATION HERE,DUE TO POWER OUTAGE, DATABASE KEEP GIVING ERROR <code> <code> select name,open_mode from v$database; NAME OPEN_MODE --------- -----------------...
Categories: DBA Blogs

Automatic Memory Management or manual SGA & PGA setup?

Wed, 2018-10-17 03:26
Hi Tom, I need some clarification regarding the pros & cons of Oracle's AMM compare to us manually configuring SGA_TARGET & PGA_Aggregate. I read from quite a number of blogs & posts that doesn't recommend Oracle AMM on mission critical product...
Categories: DBA Blogs

Meaning of object_id and data_object_id in dba_objects.

Wed, 2018-10-17 03:26
Hi Tom, For some objects in dba_objects, I see object_id is not matching with data_object_id even when that object is not part of the cluster. 21:35:49 SQL> select count(*) from dba_objects where data_object_id is not null and object_name not...
Categories: DBA Blogs

Query the table in Oracle 12c In-Memory still have physical reads

Tue, 2018-10-16 09:06
1. I cached a table in Oracle12C inmemory as following. <code>SQL> SELECT A.SEGMENT_NAME, 2 SUM(A.INMEMORY_SIZE) / 1024 / 1024 / 1024, 3 SUM(BYTES) / 1024 / 0124 / 1024, 4 SUM(A.BYTES_NOT_POPULATED) 5 FROM V$...
Categories: DBA Blogs

Salary increment by using cursor

Tue, 2018-10-16 09:06
<code>declare i number; j varchar2(30); cursor raise is select name, salary from emp; begin open raise; loop fetch raise into j,i; i :=i+15; dbms_output.put_line(i||' '||j); exit when raise%notfound; end loop; end; /</code> I am try...
Categories: DBA Blogs

Filter in Interactive Report with morethen 1000 values against one field

Tue, 2018-10-16 09:06
Hello, Is there any way I can apply a filter with more than 1000 values i.e. 5000 in single filed while working on the interactive report. I just want to copy paste my values separated by ','
Categories: DBA Blogs

How to read a value from the user at run time?

Mon, 2018-10-15 14:46
<b></b>Hey! I am a beginner. I was watching YouTube tutorials on Oracle PL/SQL and I came across this feature that uses the placeholder to assign a value to a variable by reading it from the user at the run time. I tried my best to use the given...
Categories: DBA Blogs

Is there way to convert user defined type to JSON?

Mon, 2018-10-15 14:46
Is there way to convert user defined types/collections to JSON (the same way as it's possible to convert them to XML via XMLTYPE)?
Categories: DBA Blogs

How does a global index behave on partitioned table

Mon, 2018-10-15 14:46
Suppose i have below table which has 1 billion record and partitioned based on Budget_Flag <code> CREATE TABLE xx_tab1 (budget_flag varchar(1), period_name VARCHAR2(10), begin_dr number, begin_cr number, creation_date DATE) PARTITI...
Categories: DBA Blogs

regarding exchange table partition

Mon, 2018-10-15 14:46
hi Team, currently, we are doing partition activity , in brief about the activity is we are using exchange partition with other table. While performing exchange we are facing issue of ORA-14097 : column type or size mismatch in ALTER TABLE EXC...
Categories: DBA Blogs

Query or report all runs of a scheduled job

Mon, 2018-10-15 14:46
It's possible someone has already written such a query, but how could I write a select statement that would report on all the expected times a scheduled job is going to run? So, for example, if I have 2 DBMS_SCHEDULED jobs: job_a runs at specifi...
Categories: DBA Blogs

Pages