Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 7 hours 56 min ago

Difference between Two SQL's Count

Thu, 2017-03-23 21:26
Hi Chris/Connor, I need to find the count difference of below two queries like -- Get the count of first SQL -- Get the count of second SQL -- Then COUNT(of 1st SQL) - COUNT(of 2nd SQL) -- IF count if greater than 0 then I need to return...
Categories: DBA Blogs

MAX and ORDER working different

Thu, 2017-03-23 21:26
Hello, <code>select rn, code, max(code) over () as mcode from ( select 1 as rn, '1C538454-D89A-E211-88D1-1CC1DEE781E4' as code from dual union all select 2 as rn, 'C5FAEF8F-D1B0-E211-A69F-1CC1DEE781E4' as code from dual ) order by code a...
Categories: DBA Blogs

12c Table Clustering feature and storage indexes, bloom filters and where filters

Thu, 2017-03-23 21:26
I would be grateful if you can shed some light on the question of using the 12c clustering (sorting) table data to improve the effectiveness of storage indexes. The question is: If there is (A) a widely used predicate on columns SUBMISSION_UNIT_C...
Categories: DBA Blogs

same explain plan but once in a while query is taking unusual time

Thu, 2017-03-23 21:26
Hi, In my Application, one query with fixed filter columns and dynamic values, performs in msecs most of the time but once in a while the query is taking up to 1 min. By using SQL ID I was able to get explain plan, which is same for long runni...
Categories: DBA Blogs

Error on table PEGANBAM_EXTERNAL.self_service_id, ELISA_ID. Field in data file exceeds maximum length

Thu, 2017-03-23 21:26
Error is : Record 4: Rejected - Error on table SELF_SERVICE_ID_B, column "SSN". ORA-01400: cannot insert NULL into ("PEGANBAM_EXTERNAL"."SELF_SERVICE_ID_B"."SSN") Here is my control file below: OPTIONS ( DIRECT = FALSE , PARALLEL ...
Categories: DBA Blogs

Large Number of buffer Gets

Thu, 2017-03-23 21:26
Hi Team, Not able to understand few thing while running SQL. I am running SQL,in which there is full scan table with around 7 million records. and if I check gv$sql,it has high number if IO. almost around 100 millions. My question is: 1. ...
Categories: DBA Blogs

Exchange range-hash partition to another range-hash partition table.

Thu, 2017-03-23 21:26
Hi, I want to do partition exchange from Table A to Table B. Following is one test case, create table RANGE_HASH_TEST ( TRN_DT DATE, SEQ_NO NUMBER, REF_NO VARCHAR2(26) ) PARTITION BY RANGE (TR...
Categories: DBA Blogs

Need an SQL script to insert multiple bind variables all at a time into the "Select statement at where clause".

Thu, 2017-03-23 21:26
Hi Tom, Good Morning, This is Venkatesh and I am from India,I need an SQL script to insert multiple bind variables all at a time into the "Select statement at where clause". For eg: Select * from Tab where col in ('val1','val2','val3'.......
Categories: DBA Blogs

Times Ten

Thu, 2017-03-23 21:26
Hi, Please answer my below questions 1.What is Time ten SQL? 2.whats the difference between Time Ten SQL and Pl/SQL?
Categories: DBA Blogs

sqlnet parameter

Thu, 2017-03-23 21:26
what is different between SQLNET.EXPIRE_TIME and SQLNET.INBOUND_CONNECT_TIMEOUT in easy meaning? if How can I simulate above both parameter?
Categories: DBA Blogs

Index on Column having special characters

Thu, 2017-03-23 03:06
HI Team, Consider below table structure - tb_contact_details ------------------ sequenceno mobileno request_date ---------- -------- ------------ 100001 +9198989 11/12/2001 100002 +9198989 11/12/2002 100003 +9191258 11/12/200...
Categories: DBA Blogs

Archival strategy from huge data tables

Thu, 2017-03-23 03:06
Hi Tom, We have a requirement to archive data from huge data into some backup tables based upon a date range or number of rows. As per the stats that i extracted these tables contains data around 275 MM records. Can you please let me know the b...
Categories: DBA Blogs

Loading two files with one CTL having different column

Thu, 2017-03-23 03:06
Hello Tom, Good Morning. I am in need to load two csv files with same CTL through SQLLoader. File f1 has 3 columns and ctl loads them in corresponding table columns. But file f2 has 2 columns only(last column of f1 is not present). ...
Categories: DBA Blogs

How to reduce the size of a tablespace?

Thu, 2017-03-23 03:06
today I could purge a "table space" that apparently occupied 70 gb, but actually used 3 gb, therefore now that "table space" measures 70 gb with only 3 gb occupied, then I want to minimize the "table space" at least 5 gb, but not let me make the oper...
Categories: DBA Blogs

Partitioning in 11g/12c

Wed, 2017-03-22 08:46
Hi Chirs/Connor, We have 10 tables (Size around 510 GigaBytes). We need to convert these non-partitioned table to Partition based on certain columns we identifed. Currently we are running on Oracle 11g and there is no direct option available t...
Categories: DBA Blogs

Using a view with table/cast/multiset within a cte utilizing a union statement returns null

Wed, 2017-03-22 08:46
Trying to utilize TABLE/CAST/MULTISET in Ora11 to duplicate some functionality from converted SQL using outer apply. Since Oracle doesn't have that in version 11 I defined an object and table of said object, cross join it and basically get the same t...
Categories: DBA Blogs

Explain plan cardinality and cost

Wed, 2017-03-22 08:46
Hi, I checked EXPLAIN PLAN of a Select Query(In SQL Devloper Tool by pressing F10 Short cut Key), there i noticed one column Cardinality with COST. As Cardinality Increases i noticed increase in COST, i read Optimizer chooses less cost plan to ...
Categories: DBA Blogs

Full table scan with sorting.

Wed, 2017-03-22 08:46
Dear Team, I have one analytic question, please help me to understand this. Suppose I have SGA_TARGET=5 GB DB_CACHE_SIZE= 2 GB PGA_SIZE= 2 GB TABLE SIZE= 15 GB (e.g. table_name employee) If I fire command like "select * from employee or...
Categories: DBA Blogs

rac undo tablespace

Wed, 2017-03-22 08:46
Hi , Why we need separate undo and temp table space in rac and please justify
Categories: DBA Blogs

Merging Partitions Of Two Different Tables

Wed, 2017-03-22 08:46
Hi, Suppose i have a table A in which there is a varchar2(200) and a DATE column. I have created a range partition on the date column.I have another table B in same table space and in same database with similar structure. Is there any way by which...
Categories: DBA Blogs

Pages