DBA Blogs

Select query on two calculated fields, one depends on other calculated field

Tom Kyte - 3 hours 58 min ago
SELECT A, B, A+B AS C, CASE C (I don't want to use again A+B here its lengthy query) ------ ----- ------ END As D FROM TABLE So here C is a calculated field, I want use result of C to calculate other field D. How can I do this without ha...
Categories: DBA Blogs

Optimization of view using union on 2 tables of different schema

Tom Kyte - 3 hours 58 min ago
Hi, I need to get data of table from 2 different schema . So i have created a view which will have union of 2 tables of 2 different schema . E.g. create or replace view acct_details as select column1,column2..column n from Table1 Un...
Categories: DBA Blogs

IF ELSE statements within COLUMN/HAVING elements within SELECT queries

Tom Kyte - 3 hours 58 min ago
Hi there, I'm trying to convert quite a few SQL queries from MS Access to PL SQL. Annoyingly a lot of them have VBA in them. Any tips on the best option to replicate this VBA in the select statement? I could use a variable but then I'm not sure...
Categories: DBA Blogs

Can Oracle automatically gather session specific stats for a GTT?

Tom Kyte - 3 hours 58 min ago
Hi Team, First of all, Thanks a lot for all the great work you are doing! It would be great if you can help me with a query. While reviewing AWRSQL report of a SQL I noticed that session specific stats were used for that execution. This SQL is ...
Categories: DBA Blogs

Options to load binary file and insert the data in a BLOB column in one pass

Tom Kyte - 3 hours 58 min ago
Nowadays when we have a BLOB column to be loaded with the content of a given file, we do : INSERT INTO table(columnPK, column2...columnBLOB) values (PKidtobeinserted, ...., empty_blob()) RETURNING PKidvalue; and later we call a procedure called...
Categories: DBA Blogs

table partition exchange

Tom Kyte - 3 hours 58 min ago
Hi Team, we have situation where we have two partition table (range partition ) ,example table A1 ,A1_history . application team want to archive all the partition except last 3 partitions , from Current production table to archival table . ...
Categories: DBA Blogs

Delay in row commit or Uncommited rows by Application

Tom Kyte - 3 hours 58 min ago
Hi Tom, Application(.Net) component is calling SP and creates an entry into few tables and sometimes there is delay in committing these transactions by .Net component or there is transaction left uncommitted in Oracle DB. we are aware of this issu...
Categories: DBA Blogs

Rebuilding all the unusable index

Tom Kyte - 3 hours 58 min ago
Hi Tom I really need to identify all the failed or unusable indexes in all schema in database and also to rebuild it. Kindly provide a query for both. im using SQL DEVELOPER TOOL in ORACLE 12c.
Categories: DBA Blogs

Oracle Statistics

Tom Kyte - 3 hours 58 min ago
I have an upcoming production migration I need help with. Table A has hundreds of million of rows. The migration is supposed to update a column in table A, but instead of doing an UPDATE it creates table B (one-to-one with table A) and then does a ...
Categories: DBA Blogs

variable in FROM clause inside pl/sql

Tom Kyte - 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

Tom Kyte - 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?

Tom Kyte - 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 .

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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?

Tom Kyte - 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?

Tom Kyte - 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

Tom Kyte - 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

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs