DBA Blogs

Histograms

Tom Kyte - Tue, 2017-02-14 10:46
Hello Tom, One question, in 10g and 11g versions, what are the impacts in a busy system of manually gathering the whole database statistics without updating histograms? I mean the impacts from the following points of view: 1- How long it wil...
Categories: DBA Blogs

How to print row value involved in merge statement

Tom Kyte - Mon, 2017-02-13 16:26
Hi, By using dbms_output.put_line( sql%rowcount) we can get the number of rows affected by merge statement. Is it possible to print value of those rows during merge using PL/sql? thanks, Amiya.
Categories: DBA Blogs

Iinconsistent size of tablespace.

Tom Kyte - Mon, 2017-02-13 16:26
Hello, I have two tables: 1st - the empty table t1 (partitioned by date) on the server s1, 2nd - the source table t2 (partitioned by date) on the server s2. Both tables t1 and t2 has the same structure. I want to copy t1 from t2 partition ...
Categories: DBA Blogs

Deduplication of rows with null values

Tom Kyte - Mon, 2017-02-13 16:26
<code>drop table test purge; create table test (ssn int, address varchar2(10), tax_id varchar2(10), identify int); insert into test values(100, '','', null); insert into test values(111, '','', null); insert into test values(111, 'xyz','', nu...
Categories: DBA Blogs

How to get the count of consecutive transactions

Tom Kyte - Mon, 2017-02-13 16:26
Team, Good Monday Morning!!!!!!!! First let me share the sample table and insert scripts for your reference. Table creation script : CREATE TABLE st_lo_trans(customer_name VARCHAR2(10),loan_code NUMBER,loan_tran_code NUMBER,tran_id VARCHA...
Categories: DBA Blogs

Most of the rows are going to one single partition

Tom Kyte - Mon, 2017-02-13 16:26
Hi, We have hash partitioned table with 32 partitions and table has 25 million records.Most of inserted records are going one single partitions in this case 17 million are going to one partition.Is there way this can be controlled so that rows are...
Categories: DBA Blogs

partition's drop

Tom Kyte - Mon, 2017-02-13 16:26
i am creating on partition table with help of the interval (numtoymintervel) then create the 4 partitions. Now i am drop the table without drop the partition's. then i will check in user_tab_partitions. that 4 partitions are creating with systable...
Categories: DBA Blogs

Why we use alter database open resetlogs after flashback database to guaranteed restore point

Tom Kyte - Mon, 2017-02-13 16:26
Hi Team, Below how i created a guaranteed restore point. ========================================================================================= SQL> select name,database_role,open_mode,flashback_on,log_mode from v$database; NAME D...
Categories: DBA Blogs

How to avoid materialized views having staleness="IMPORT" after importing schema by expdp/impdp?

Tom Kyte - Mon, 2017-02-13 16:26
After importing schema by expdp/impdp, I have materialized views with staleness set to "IMPORT". That is, complete refresh is needed to fast refresh is needed later on. However, those materialized views (as star schema commonly seen in datamart) take...
Categories: DBA Blogs

How to extract substrings enclosed within double quotes from a string

Tom Kyte - Mon, 2017-02-13 16:26
I have a requirement to extract column names (there could be multiple instances) from a SQL string. The column names will always be enclosed within double quotes. e.g. "le code" = 802 AND "principal amount" > 1200 OR "branch id" = 'ABC' <b>Req...
Categories: DBA Blogs

ORA-01858

Tom Kyte - Mon, 2017-02-13 16:26
I'm using Apple's macbook pro and I downloaded Virtualbox in order to work with Oracle 11g. I installed Windows 7 in Virtualbox. I wrote my queries in sql*plus. I have a problem with single quotes. Even the query is work in the classroom, it always g...
Categories: DBA Blogs

How to retrieve current value of oracle identity column value for foreign key purpose

Tom Kyte - Sun, 2017-02-12 22:06
Hi, after inserting into parent table w identity column, how to retrieve the identity column value to use for child table foreign key purpose thx Heather
Categories: DBA Blogs

Date math to calculate A and B weeks

Tom Kyte - Sun, 2017-02-12 22:06
I am trying to calculate the Recycle Week. It is either an A or B week. My first attempt was the following with obvious problems as the first day of the week changes every year. <code> SELECT DECODE(MOD(TO_NUMBER(TO_CHAR(SYSDATE, 'WW')), 2), 0,...
Categories: DBA Blogs

"Table override": can I use this feature?

Tom Kyte - Sun, 2017-02-12 22:06
Hi! I created an empty table, lets say, tb_test (col1 varchar2(20 char), col2 varchar2(20 char)); Now I created a view of this table, lets say, vw_test as select * from tb_test; If I do select * from vw_test; I will get nothing, it is empty....
Categories: DBA Blogs

Government Policy regarding Password Minimal age

Tom Kyte - Sun, 2017-02-12 03:46
I'm trying to look for the location on the Oracle database for the parameter that allows you to alter the minimal age for passwords in essence according to the Gov't policy I'm supposed to be able to limit users from changing passwords more than once...
Categories: DBA Blogs

In Oracle Trace, XCTEND indicates the end of a transaction, what indicates its beginning?

Tom Kyte - Sat, 2017-02-11 09:26
We know that a given oracle session we can contain many transactions, each transaction should have a beginning and an end and each wraps many queries within it. While analyzing an Oracle trace file I wanted to make sure that queries are correctly wra...
Categories: DBA Blogs

Need input on Dynamic Update stored procedure with bind variables

Tom Kyte - Sat, 2017-02-11 09:26
Hi AskTom Team, Good day to you! I have some question on some best practice on implementing dynamic update statement using Stored Procedure. I have read many example in this site alone, also other sources, but none seems to answer my specific ques...
Categories: DBA Blogs

Problem running a shell script that contains pipes

Tom Kyte - Sat, 2017-02-11 09:26
Hello, I am using a bash script to generate passwords. This is a bash shell script. When we run this in bash (on Linux) outside of SQL*Plus, it executes fine. We are running Oracle Enterprise Edition 12c on a two-node RAC. When I log into SQ...
Categories: DBA Blogs

EHCC Compression and PCT Free

Tom Kyte - Sat, 2017-02-11 09:26
Hi Tom, We are looking for opportunities to free-up space in our data warehouse. The database is running on Exadata box and the version is Oracle Database 11g Enterprise Edition Release 11.2.0.4.0. Since it is a data warehouse we usually insert da...
Categories: DBA Blogs

Order by Behaviour

Tom Kyte - Sat, 2017-02-11 09:26
<code>Hi, We are migrating data from 9i to 12c by import/export procedure, After import when we are checking product behavior the table display the data in both Oracle 9i application is different from Oracle 12c application. For e.g.: create t...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs