Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 39 min 47 sec ago

Get the first word in a string

Thu, 2018-11-01 09:26
Haai.. i have input like this in below.. <code>Create table Char ( name varchar2(1000) ); Insert all into Char(name) Values('Ask Tom') into Char(name) Values('Become Again Tom') into Char(name) Values('United Sates') select * from du...
Categories: DBA Blogs

Enforce either of column1 or column2 to be not null

Thu, 2018-11-01 09:26
Hi, I have a table where I need either of the column to be not null, meaning at any point of time only one of these column should have a value and the other should be null. I tried below: <code>CREATE TABLE "BCKC"."TEST" ( "ID" NUMBER(9,0),...
Categories: DBA Blogs

SET COMMAND

Thu, 2018-11-01 09:26
Sir I am getting the following error message after executing the command "SET DESCRIBE DEPTH 4 INDENT ON" at SQL prompt SET DESCRIBE DEPTH 4 INDENT ON" is Obsolete Please advise how to describe nested object types Regards DS Rao
Categories: DBA Blogs

advantage of procedure within a procedure

Thu, 2018-11-01 09:26
I've seen package code where there are procedures defined within a procedure, then the "within" procedure is called once in the main procedure - actually I've also seen 3 "within" procedures and the only thing the main procedure does is call these 3 ...
Categories: DBA Blogs

Oracle Fact Table creation and loading strategy

Wed, 2018-10-31 15:06
Thanks in advance for the support.I have a few questions: 1. I will have to load 7 years worth of history data with 128 billion rows into a new Fact Table, which also will have inserts as well as updates for the last 90 days (98% inserts and 2% up...
Categories: DBA Blogs

ORDER BY and CONSISTENT GETS divided by ten

Wed, 2018-10-31 15:06
Hello TOM, When I use an ORDER BY, I see that the consistent gets are divided by ten in my tests, can you explain why? Here are my tests. First I create a table. <code> SQL> create table test_obj01 as select OWNER, OBJECT_NAM...
Categories: DBA Blogs

Ordering serializable transactions

Wed, 2018-10-31 15:06
Hi, I'm trying to work out how to assign an ordering to the results of potentially concurrent serializable transactions so I can definitively say either: - They both had an identical view of the database, or - Identify which one had a later view...
Categories: DBA Blogs

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

Pages