Tom Kyte

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

Checkpoint processing

Thu, 2017-09-07 01:26
My Question: 1. When there is check point, data is written on Disk even if the transaction is not committed yet, when roll back issued, how does it happen..? I Mean end up with physical delete insert and update or logical operation like if ? Ins...
Categories: DBA Blogs

Error while sending the attachment using UTL_SMTP mail

Thu, 2017-09-07 01:26
Dear Tom, We are getting error Sqlcode : -29279 - Sqlerrm :ORA-29279: SMTP permanent error: 554 Transaction failed: Missing start boundary while sending the attachment using UTL_SMTP. please help us on this issue. Thanks in advance. ...
Categories: DBA Blogs

Exploring connect by

Wed, 2017-09-06 07:06
Hi Tom, I am new here, so if i ask wrong ques or not following the guidelines, apologizes. Suppose i have a table like this: <code>COL1 ---- A B C D E F G</code> I have to transform this columns in a single cell like this A,B,C,D,E,...
Categories: DBA Blogs

Performance issue.

Wed, 2017-09-06 07:06
we are in the process of performance tuning , the below program is taking time, Please guide us to tune further or can we write any other way to improve the performance, <code>CREATE OR REPLACE TYPE "RECO_FE" FORCE AS OBJECT ( o_o_id ...
Categories: DBA Blogs

Upper bound length constraint for list partitioned tables (ORA-14116)

Wed, 2017-09-06 07:06
Dear Tom, We do have some table where partitions will be created frequently: <code>CREATE TABLE partitioned_table ( someUsefulPartitionIndicator NUMBER (*,0), someData varchar2(4000) ) partition by list (someUsefulPartitionIndicato...
Categories: DBA Blogs

How to optimize query for multiple joined tables

Wed, 2017-09-06 07:06
Hi TOM, I have this simplified version of a query. I would just like to check if there are other ways to further optimize the query especially on the subqueries. I'm limited to SQL only. Reason for me thinking that this can be further optimized...
Categories: DBA Blogs

mutating table

Wed, 2017-09-06 07:06
hello, i've got a table MRC and a trigger on it (AFTER INSERT) thus, after an insert in the table MRC, this trigger has to determine if a new line must be inserted into an other table PLAN : for that, it does compare the :new values with the MO...
Categories: DBA Blogs

Tuning Question

Wed, 2017-09-06 07:06
Hi I have the following query: <code> SELECT cust_id, cosmos_cust_id, prot_id, customer_name, protocol_name, dist_order_no, cust_ivr_ref_no, ship...
Categories: DBA Blogs

Update 5 Million records in Database in least time

Wed, 2017-09-06 07:06
I have approx to 5 million records in a table and I need to update one column of this table from another Table. <b>I have used Bulk Collect with FORALL option ( limit = 500 ) but it is taking 6 to 7 hours</b>. Do we have any option available with ...
Categories: DBA Blogs

Retrieving just the 1st record from each group of accounts

Wed, 2017-09-06 07:06
I have records in a marketer table and an account table that I want just the 1st account for each marketer that was notified on a specific date. How can I do that with a query? <b>MARKETER 10033 COMPANY 1 10036 COMPANY 2 10044 COMPANY...
Categories: DBA Blogs

V$session contains KILLED sessions

Wed, 2017-09-06 07:06
Hi Oracle Masters, Is there any way to remove killed session rows from v$session Manually ? Fahd
Categories: DBA Blogs

Migrate an Oracle Query with LEAD function to equivalent MySql Query

Tue, 2017-09-05 12:46
Please help me with an equivalent query in mySQL for the below: There is a table called "proj_table" Based on cycle_id, i need to find the current and previous value for each column for a project <code>SELECT project_id , cycle_id ,final_value ...
Categories: DBA Blogs

Materialized View Refresh and Dropping Partitions

Tue, 2017-09-05 12:46
Hi Tom, Based on your answer to the thread "https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9534336000346923525", two questions popped up in my mind. Can you please help me. 1. What if I want to create a non-partitioned m...
Categories: DBA Blogs

Difference between constraints and Indexes

Tue, 2017-09-05 12:46
Hi Tom, First of all I personally thank you (and of course I believe that others do) for all the support you have given to us in giving us this opportunity to post questions thru this site. Now with my concern: 1. I understand t...
Categories: DBA Blogs

Error Logging

Tue, 2017-09-05 12:46
Hi Team, I have written a package which has lot of procedures and within each procedure ,I am logging each step in a log table (which i created )and if some exception is coming then logging it in a (errorlog table) .Since logging steps created each ...
Categories: DBA Blogs

User-defined aggregate function in Java

Tue, 2017-09-05 12:46
I'd like to implement a SQL user-defined aggregation function in language Java. I started with a 'Hello World' function, which is trivial to code in PL/SQL - see https://livesql.oracle.com/apex/livesql/s/finvtl1sfarg4x2ywvvdiwrjh In the next step...
Categories: DBA Blogs

What is difference between UGA and temp files in db

Tue, 2017-09-05 12:46
Hi, I was reading about oracle DB architecture, I came across data files(I read about temp file). Temp file --->> when user do a sorting operation -->> the data will go to this file space -->> sorting is done and data is displayed to the end ...
Categories: DBA Blogs

Increasing the number of hash sub-partitions

Mon, 2017-09-04 18:26
Hi, I'm using Oracle 11g Enterprise edition database. I've an existing table with both Range partitions and hash sub-partitions. Currently the number of sub-partitions are set as 64 when the table was first created and I may have a requirement in...
Categories: DBA Blogs

Uncommitted transactions in Data Files and Redo Log files

Mon, 2017-09-04 18:26
Hi Tom First of all I want to inform you that, I love you very much and I have a big respect to you I have a question related uncommitted transactions. I know that while we issue COMMIT, LGWR writes committed and uncommitted transactions from re...
Categories: DBA Blogs

Daily transactions to a table

Mon, 2017-09-04 18:26
Hi, TOM. Is there a sql query that returns the daily transactions to a specific table? For example, I want to know how many insert and update were executed a day for table "People". Also, it's important to consider the commit statement for t...
Categories: DBA Blogs

Pages