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

Object info w.r.t redo

Tue, 2017-11-14 03:06
Dear Sir, How can I found which object generate more redo in database. Thanks Pradeep
Categories: DBA Blogs

DBMS facilities

Tue, 2017-11-14 03:06
I am not able to understand this question. What are they asking in the question? Thank You!: "Starting at the client invocation describe the mechanics of the DBMS through a SQL SELECT all the way through the response to the client. Name all faci...
Categories: DBA Blogs

difference between sql tuning advisor and sql access advisor

Tue, 2017-11-14 03:06
After reading both "Sql Tuning advisor" and "sql access advisor" , getting little confused on -- what is exact difference behind these advisors -- which situation one can use one advisor over another Please provide your guidance. thx A...
Categories: DBA Blogs

Table with date column datatype (Storing datetime) causing issue in fetching result and when use with to_date and to_char causing performance issue.

Tue, 2017-11-14 03:06
Hello Tom, Good Morning. This is my first time posting question, I always got helped for my most of problems from your post. Need your help in understanding what will be good way with respect of performance. I have one table with date dat...
Categories: DBA Blogs

is alter table drop constraint drop index syntactically valid?

Mon, 2017-11-13 08:46
Hi Tom, I'm dropping unique constraint from a table and need to drop the unique index too. Yes there's lot written about this topic and what is not clear to me seems to be just documentation issue: I found advices to use syntax <code>alter tabl...
Categories: DBA Blogs

oracle external table issue

Mon, 2017-11-13 08:46
Hi team, I have a txt file which has data delimited by "TAB SPACE" and i am trying to load the data using the oet table.Here is the script below which i used to load the data . Unfortunately the data is not loading . Can any one say how to load th...
Categories: DBA Blogs

Persuade customer to use SQLT

Mon, 2017-11-13 08:46
Hi Tom, While doing sql query tuning I came across SQLT Tool and I found it very useful. But there's one problem. Our clients (esp. client's DBAs) are not ready to allow me to use it on their production environment. I told them that it won't have ...
Categories: DBA Blogs

Last login

Mon, 2017-11-13 08:46
We are running versions 11.2.0.4 12.1 12.2. I am looking for a generic solution to capture last login date. I think the best solution would be an initial load and then maintain the information via an after logon trigger, which will contain a merg...
Categories: DBA Blogs

INACTIVE session is blocking active session

Mon, 2017-11-13 08:46
DBA is throwing information as follows 06112017:11:00:09 WELOPP@n1pv97/46581 (Session=('300,19867')Status=INACTIVE sqlid=>) blocking WELOPP@n1pv97/45876 (Session=('1803,10683') Status=ACTIVE sqlid=fp5x2quh0zpqk) f...
Categories: DBA Blogs

For Joins in Query Performance optimization

Sun, 2017-11-12 14:26
I have a query with 4 For loops puting data into temp table an then that temp table TEMPTBL_NUMBER_SEARCH is called to execute the operations in a select clause. So the problem with the 4 for loop is making it slow to 15-20 mins. Its all indside a pr...
Categories: DBA Blogs

export issue

Sun, 2017-11-12 14:26
Hi team, We are taking daily export of schema with expdp But for a few days we are continuously getting error saying - snapshot too Old. Table is a partitioned table weekly base. And the script which we are using for expdp is - expdp us...
Categories: DBA Blogs

Does the context switch account for the recursive calls

Sun, 2017-11-12 14:26
Hi Tom, Here is what i did trying to understand the enhancements of 12c. Here i was trying to understand the enhancements of WITH clause. I have created the table and compiled the below function. <code> CREATE TABLE lnd_numbers AS SELECT ...
Categories: DBA Blogs

In sql how can I update a value , and then reuse the updated value and re-update it

Sun, 2017-11-12 14:26
Hi Gurus I need to write in SQL something which previously was done in PL/SQL if possible. I have a Invoice Line Description e.g. 'ABC Mon Tue' for which I need to translate certain words. I also have a lookup(fnd_lookups) which stores the...
Categories: DBA Blogs

Versioning Data Model

Sat, 2017-11-11 01:46
Hi AskTom team, I'd like your ideas about the data model design and/or Oracle features that I could take advantage of to achieve the design goals described below. <u>Background:</u> I'm in the early stages of designing a data model for a bra...
Categories: DBA Blogs

Union all query missing lines

Sat, 2017-11-11 01:46
Hello Tom and Tom, Linked live sql shows a condensed and "moved-to-dual" query we are using with a far resemblance on our database. It's a couple of nested "union all" statements, where we would expect the outermost union (UNION2) to deliver the u...
Categories: DBA Blogs

Grant select on a View with grant option does not work

Sat, 2017-11-11 01:46
Hi, I have Schema_1 that owns table_1, table_2, table_3. Schema_1 creates View_1 using table_1, Schema_1 Creates View_2 using table_2, Schema_1 Creates View_3 using table_3. Schema_2 Creates View_4 using View_1, View_2 and View_3. Then ...
Categories: DBA Blogs

Identify patterns and create groups

Fri, 2017-11-10 07:35
I have data that looks like this: <code>create table t (a varchar2(30), b date); insert into t values (NULL,TO_DATE('2003/05/03 16:02:44', 'yyyy/mm/dd hh24:mi:ss')); insert into t values (NULL,TO_DATE('2003/05/03 17:02:44', 'yyyy/mm/dd hh24:mi...
Categories: DBA Blogs

optimistic search for most recent records

Fri, 2017-11-10 07:35
Hi, I have very large table which constantly grows. The search is executed by ID column, which is part of PK. <code> create table TEST ( ID varchar2(20) primary key, VALUE varchar2(20), CREATED_TS timestamp default := systimes...
Categories: DBA Blogs

selecting table column based on lookup table

Fri, 2017-11-10 07:35
Hi I am trying to get columns from a table only if that column value is set as "YES" in another lookup table. Please help me to get the query for the same. I have a lookup table like this: create table cust_bug_lookup(Title varchar2(100), ...
Categories: DBA Blogs

Partitioned table performance

Fri, 2017-11-10 07:35
We have a partitioned table with more than 200 columns and 60 indexes. It has 10 foreign keys with related indexes and the remaining indexes are global style. It partitioned in a yearly basis and sub-partitioned in company. Now, we're have perfor...
Categories: DBA Blogs

Pages