Tom Kyte

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

variable in FROM clause inside pl/sql

Fri, 2018-02-16 07:46
Hi Tom We have an anonymous pl/sql block which looks like follows but using dbms_sql (the following doesnt work) declare vRows number; begin for i in (select * from user_tables) loop select count(*) into vRows from i....
Categories: DBA Blogs

Update current row witrh values from previous row

Fri, 2018-02-16 07:46
Hi, I'm searching for a solution to solve update with a single SQL statement instead of a PL/SQL procedure: <code>create table test (stock_date DATE, stock NUMBER(5), stock_in NUMBER(5), stock_out NUMBER(5), stock_val NUMBER(5)); INSERT INTO tes...
Categories: DBA Blogs

How to find the SQL_ID of the sql statements running in a stored procedure?

Fri, 2018-02-16 07:46
Hi Team, I have scenario in which I need to check which of my procedures(they run in batch) are slowing down the operations. The procedure consist of two main tasks: 1.) Get data from multiple table (has multiple joins and vast data) 2.) insert ...
Categories: DBA Blogs

Based on parameter value need to execute the condition .

Fri, 2018-02-16 07:46
<code>create or replace procedure fetch_ids(ename in varchar2,hiredate in date) as begin select * from emp where empname=ename and join_date=hiredate ; end;</code> Problem statement: 1)if i will not pass the ename, i need to fetch all the e...
Categories: DBA Blogs

how to overcome the job queue limitation of 1000

Fri, 2018-02-16 07:46
Hi Tom, I have a very huge data aggregation which should be ideally done on a OLAP database using cube. Due to some contrains, I am doing it in my transactional database. When I ran the SQL with multiple table joins, the SQL errored out due to...
Categories: DBA Blogs

Materialized View Fast Refresh and the ATOMIC_REFRESH Parameter

Fri, 2018-02-16 07:46
Hi Tom, I have about 25 MV's in my production application. Mostly in two refresh groups. Very few stand alone. One group (lets call it GROUP-A) refreshes every minute as a business requirement and the other (GRIOUP-B) every hour. Few more MV's ev...
Categories: DBA Blogs

what is the difference between shrink ,move and impdp

Fri, 2018-02-16 07:46
Hi: I want to clean some space about some tables ,there are a few ways ,such as move ,shrink and impdp. I want to know which one is better regardless of space consideration and assume this tables can use all those methods. Can you answer my...
Categories: DBA Blogs

Oracle and Active Directory

Fri, 2018-02-16 07:46
My company has select MS Active Directory for the enterprise directory services. We would like to integrate our Oracle networking with AD, in lieu of TNSNAMES or Oracle Names, for database connection resolution. However, we are having a hard time f...
Categories: DBA Blogs

Why do we have specify the authentication clause for shared private fixed database links?

Thu, 2018-02-15 13:26
Hi, A private fixed user database link only requires username + password, e.g. <code>create database link <link_name> connect to <remote_user> identified by <remote_password> using "<tns-string>";</code> A SHARED private fixed user datab...
Categories: DBA Blogs

Do primary keys on a index-organized table have to be incremental?

Thu, 2018-02-15 13:26
Hi Tom, If the primary key on a index-organized table is not incremental, wouldn't this create bottlenecks as data volume grows under OLTP loads? Wouldn't the data being inserted needed to be sorted and inserted in the middle of the leaves? Woul...
Categories: DBA Blogs

generic trigger for auditing column level changes

Thu, 2018-02-15 13:26
I'm trying to create a generic before update trigger which will compare all :old.column_values to all :new.column_values. If the column_values are different, then I would like to log the change to a separate table. When I try to compile :ol...
Categories: DBA Blogs

Non unique clustered index

Thu, 2018-02-15 13:26
Hi Tom, We have a table with a non incremental composite key of (dialaog_id, insertion_date). This creates problems since we have insertions in the middle of the leaves. Data has to be relocated. We cannot use incremental keys since this cre...
Categories: DBA Blogs

prerequisites for expdp/impdp

Thu, 2018-02-15 13:26
Hi, I want to transfer data between test systems, version is Oracle Database 12c Release 12.1.0.1.0 - 64bit Production. I wrote scripts for a full export and import (expdp/impdp) When importing I get many errors because objects do not exist...
Categories: DBA Blogs

Skipping the incorrect email Ids

Thu, 2018-02-15 13:26
Hi Tom, I am not too sure whether this question has been asked earlier or not but here it goes, I have a setup where pl/sql is code is generating reports on a daily , monthly basis, all the reports are in .csv format, the reports generated ar...
Categories: DBA Blogs

Materialized view fast refresh with joins - what work is done where?

Wed, 2018-02-14 19:06
If I have two tables, both with materialized view logs, and I join them together in a fast refreshable materialized view, when that MV is refreshed, where is the work done to eliminate records that have not been deleted, but should no longer be retur...
Categories: DBA Blogs

Print sub-total and total name in SQL

Wed, 2018-02-14 19:06
Hi Team Code sample provided in LiveSQL link. Expected Result: <code> Mgmt1 State1 18900 1786 Mgmt1 State2 28900 2786 Mgmt1 SUB_TOTAL 47800 4572 Mgmt2 State1 38900 3786 Mgmt2 State2 48900 4786 Mgmt2 SUB-TOTAL 87800 8572 ...
Categories: DBA Blogs

ALL_ROWS vs FIRST_ROWS_10

Wed, 2018-02-14 19:06
Hello Team, An SQL(complex one and there are 10+ tables in join) which is called by Siebel application is set with Session parameter (ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10) which took around 55 seconds to show the result as "No record f...
Categories: DBA Blogs

How to Improve the Performance of Group By with Having

Wed, 2018-02-14 19:06
I have a table t containing three fields accountno, tran_date, amount and amount is always > 0. . There are many records for each accountno. I want to select all the accountnos where the sum(amount) > 100. The simple query is like this <code>...
Categories: DBA Blogs

crs processes

Tue, 2018-02-13 06:26
when we start CRS which process will start first and explain its background
Categories: DBA Blogs

Using defined variable (ampersand) for a part of column name

Tue, 2018-02-13 06:26
Good day. I am having problem to define <b><u>only part</u></b> of a column name as a variable (using ampersand). Script example: <b><i>Select &Product._ID, &Product._NAME, &Product._SALES, &Product._DEBT From TABL...
Categories: DBA Blogs

Pages