Tom Kyte

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

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

How to avoid multiple scanning of same tables again and again

Mon, 2017-09-04 18:26
Hi Tom This query is scanning the below same table again and again and hence a Table SALES_CASE is referenced 5 times in this query. This table has 3 Million rows. Table MDM_KC_ORGANIZATION is referenced 5 times. This table has 300,000 records....
Categories: DBA Blogs

Deriving/Creating date span's from a set of begin & end dates.

Mon, 2017-09-04 18:26
Hi, I am trying to write a query using analytical functions that derive or create date spans using a set of begin & end date. The eventual spans that gets created should have over-lapping spans. Attached LiveSQL link contains the test case (with samp...
Categories: DBA Blogs

Value of time in a column with type DATE changes to 00:00:00 after more than 96 hours.

Mon, 2017-09-04 18:26
I have a table with a column (CREATED_ON) with type DATE. I insert a row into that today with the value 04-09-2017 12:33:43. However, after 96 hours, if I query for the row, the value of CREATED_ON changes to 04-09-2017 00:00:00. We scanned our co...
Categories: DBA Blogs

SQL*Loader-926

Mon, 2017-09-04 18:26
I ran sqlldr to import the data to table .i got the following error. control file: OPTIONS (SKIP=1) LOAD DATA INFILE '.\..\DATA\APPLICATION_MASTER.sql' BADFILE '.\..\CONTROL\APPLICATION_MASTER\APPLICATION_MASTER' DISCARDFILE '.\..\CONTROL\...
Categories: DBA Blogs

ORA-4030 error caused by pga

Mon, 2017-09-04 00:06
Hi , in our environment , from few days we are receiving ora-4030 for pga. I just wanted to know how much pga maximum can use system memory ? As far as my basic understanding , pga can use , full system memory including swap as well or it c...
Categories: DBA Blogs

Rman Error: dbgc_init_all failed with ORA-48141

Sun, 2017-09-03 05:46
Hi Tom, We getting a weird error, while netbackup is trying to take backup using rman for an oracle instance PMTN5O1, below is the error: dbgc_init_all failed with ORA-48141 RMAN-00571: =========================================================...
Categories: DBA Blogs

Pages