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

Non unique clustered index

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

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

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

Oracle Partner Hub is Now in Kenya

Oracle Partner Hub Now, in Kenya! Dear Valued Partner, We are pleased to invite you to the Launch of the Oracle Partner HUB, Oracle’s Cloud Centre of Excellence for Kenya and East...

We share our skills to maximize your revenue!
Categories: DBA Blogs

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

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

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

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

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

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

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

In- Memory option versus buffer cache

Tom Kyte - Tue, 2018-02-13 06:26
Hi Team, can you provide some information regarding in-memory database ? As per my understanding i see that it works in same way of keeping object in buffer cache like keep pool etc. in-memory is also provide same functionality .So , why i s...
Categories: DBA Blogs

substr operation

Tom Kyte - Tue, 2018-02-13 06:26
hi there, select substr('hellooracle',1,2)from dual;--> this will return 'he' select substr('hellooracle',3,4)from dual;-->this will return 'll' my question is i want to cut the entire string in a given order(for the above example it will like 1,...
Categories: DBA Blogs

Row count of tables

Tom Kyte - Tue, 2018-02-13 06:26
Hi Tom, Recently I have experienced something weird about table row count after gather stats the table. At first I have checked count(*) and num_rows from one table. Then count(*) shows 651 rows and num_rows shows 2211 rows. So I have do...
Categories: DBA Blogs

Reg: Masking Sensitive column data

Tom Kyte - Tue, 2018-02-13 06:26
Hi TOM I have a requirement of masking sensitive data as we have to refresh production data to development environment. I have gone through a couple of options ,Data masking through enterprise manager and through vpd policy. The actual requir...
Categories: DBA Blogs

3rd party tool fails with: ORA-00600: internal error code, arguments: [kdu_array_buf:depth], [17], [16], [], [], [], [], [], [], [], [], []

Tom Kyte - Tue, 2018-02-13 06:26
Hi TOM, that is the error that we get: SQL: UPDATE /*+ PARALLEL */ POM_BACKPOINTER SET to_class = 1546 WHERE to_uid IN ( SELECT puid FROM ao_temp ); ERROR: ORA-00600: internal error code, arguments: [kdu_array_buf:depth], [17], [16], [],...
Categories: DBA Blogs

Sequence no mismatch between Primary and Standby

Tom Kyte - Tue, 2018-02-13 06:26
Hi, I have taken backup from primary database that time the max sequence number was 285 which was applied on standby. after few minutes had an issue in standby database so that i dropped standby database permanently. so here what i done..restored ...
Categories: DBA Blogs

SP2-0606 when use dynamic spool path

Tom Kyte - Tue, 2018-02-13 06:26
SQL*Plus client : Release 11.2.0.1.0 on Windows 8 client writing output in Spool file with hardcoded path is working in any directory, but with spool &filename with and without doublequotes around that string I get the message <b>SP2-0606: Kann ...
Categories: DBA Blogs

DBMS_PARALLEL_EXECUTE for create table as select

Tom Kyte - Tue, 2018-02-13 06:26
Hi Tom, I am trying to make use of this package at some SE 12.2 and 11.2 databases we have our customers' data. What I have already read about, always shows how to make chunks of a table object. I would like to usae it with the result of a c...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs