Tom Kyte

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

Goldengate Integrated Replicat Parallel Execution

Thu, 2017-09-14 16:46
We're creating a bi-directional Goldengate replication environment between two Oracle databases... Goldengate 12.2 Oracle EE RDBMS 12.1 Both databases on a 2-node Exadata RAC We're using Integrated Extract and Replicat on both nodes. For p...
Categories: DBA Blogs

On addition of a single column, performance of query drastically impacted

Thu, 2017-09-14 16:46
Hi, On addition of a single column, performance of a query has drastically impacted (40 secs from 0.0002 secs). Oracle has changed earlier plan and picked plan that takes more time. Change : new column added to a query in select clause : dsi...
Categories: DBA Blogs

Oracle SQL background process

Thu, 2017-09-14 16:46
Hi Tom, I have bit knowledge (aware of defination) on PGA and SGA. But I would like to know, when the sql is triggered from client tool(toad). -> background process uses Shared pool memory OR pga memory for performing SYNTAX and SYMANTIC check...
Categories: DBA Blogs

Clustered Index and primary keys

Thu, 2017-09-14 16:46
I have question on clustered index I read from documents that whenever primary key is created, it creates clustered index along with it, and it sorts the rows in the table in the same order as the clustered index(on the actual disk), I didn?t unde...
Categories: DBA Blogs

Unjustified memory consumption of windows server equal to SGA_MAX_SIZE in 12.2.0.1 (with manual MM)

Thu, 2017-09-14 16:46
Hi, I just installed the latest Oracle 12.2.0.1 Enterprise Edition and I noticed something different with 12.1.0.2 regarding the memory consumption of the Windows Oracle RDBMS Kernel Executable. The database is installed on a Windows server 201...
Categories: DBA Blogs

Deterministic functions and Virtual Columns

Wed, 2017-09-13 22:26
It seems that ?DETERMINISTIC? means exactly what I think (or some of the non-oracle sources on the internet) think it does. I expected this script to fail either on the create function, create table or when running the select statements. I would ha...
Categories: DBA Blogs

Join of two tables and want first row of matching records in second table

Wed, 2017-09-13 22:26
I have two tables <code>create table g ( a int, d date); with this data in it: insert into g values ( 1, to_date('01/15/2004','mm/dd/yyyy')); insert into g values ( 2, to_date('01/15/2004','mm/dd/yyyy')); insert into g values ( 3, to_dat...
Categories: DBA Blogs

Selecting column dynamically

Wed, 2017-09-13 22:26
Is there a way to write a SQL which selects the number of columns based on the data available .. Say i am trying to post the number of lines inserted on a table per hour on a day .. So when i run the select between 03:00-04:00 hr it should give...
Categories: DBA Blogs

User defined function in select statement

Wed, 2017-09-13 22:26
Hi, I want fetch total_resale value based on quote_id and group by item_class but its not fetching proper result. Please find DDL, DML and function as below, DDL: <code>CREATE TABLE QUOTE_TEST ( ITEM_CLASS VARCHAR2(20 BYTE) , QUOTE_ID...
Categories: DBA Blogs

Import is taking 4 days to get DB impprted

Wed, 2017-09-13 22:26
Dear sir Database 11g- exported to dmp file sizing 20 GB Then i imported it with imp command as Full=Y and commit=Y, and db got imported successfully with in 5 hours. Next i imported the same dmp file in oracle 12c Initialy import went as...
Categories: DBA Blogs

Create a view to convert rows to columns dynamically

Wed, 2017-09-13 22:26
I have a question regarding view creation. Here is my question: I need to generate a report using a view which takes data from two tables lets say Table_A and Table_B. Sample data in Table_A: <code>empno empname elem_name -------------...
Categories: DBA Blogs

Concurrency wait event

Wed, 2017-09-13 22:26
Greetings, As per my AWR report:: <code>Event Waits Time(s) Avg wait (ms) % DB time Wait Class ----------------------------- SQL*Net more data from client 35,972 9,805 273 65.90 ...
Categories: DBA Blogs

ASH Report blocking issue

Wed, 2017-09-13 22:26
Hi team, There was an issue with db running very slow so i generated the ASH report after to become normal And i get the blocking session over there. I want to find the sql statement for that blocking session but it is showing no records found ...
Categories: DBA Blogs

V$SESSION_LONGOPS

Wed, 2017-09-13 04:06
Tom: when i query v$session_longops, i got the following result: select username ,sid,serial# from v$session_longops; SYS 12 1 John 28 438 HP_DBSPI 42 39 HP_DBSPI 42 ...
Categories: DBA Blogs

dbms_stats.set_global_prefs ('no_invalidate', 'TRUE')

Wed, 2017-09-13 04:06
Good Evening, I came across dbms_stats.set_global_prefs ('no_invalidate', 'TRUE'). By setting this value to TRUE, Oracle "Does not invalidate the dependent cursors if set to TRUE" when gathering statistics. This seems like the no brainer option...
Categories: DBA Blogs

DB_LOST_WRITE_PROTECT

Wed, 2017-09-13 04:06
Tom, Starting at 10g, there is a new initialization parameter: DB_LOST_WRITE_PROTECT (which can be set to NONE, TYPICAL and FULL). Reading a few articles on that, I understand it protects against eventual asyncronous WRITE errors. From th...
Categories: DBA Blogs

Gen Objects DDL Statement Will Change using DDL command using SQLcl tool

Wed, 2017-09-13 04:06
Hi Tom I hit a problem, when gen DDL statement using DDL command in sqlcl tools. Follow with my environment: (1) Redhat (2) Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production (3) SQLcl: Release 4.2.0.17.097.0719 ...
Categories: DBA Blogs

Passing an array to view object as bind variable using IN clause

Wed, 2017-09-13 04:06
Hello Tom, I have an application, designed in Jdeveloper 11.1.2.4.0. In this application I have a view object with many employee ids based upon its query. I then call upon a function, this function uses these ids and returns a yes/no answer based ...
Categories: DBA Blogs

Inaccurate output of an arithmetic operation when executed in procedure or function

Wed, 2017-09-13 04:06
I'm trying to calculate item affected on employee salary but the result of arithmetic operation is inaccurate return 99.99999999999999 instead of 100 as example and this happen only if the operation inside procedure or function but work correctly ot...
Categories: DBA Blogs

Oracle Advanced Queues dequeue throughput

Tue, 2017-09-12 09:46
I am a developer, not a DBA, so know just enough about this to be dangerous. I have a setup a queue using the fairly standard scripts that are on multiple sites. I have used this one: http://www.oracle-developer.net/display.php?id=411 The trouble s...
Categories: DBA Blogs

Pages