Skip navigation.

Hemant K Chitale

Syndicate content
I am an Oracle Database Specialist in Singapore.
get an rss feed of this blog at http://hemantoracledba.blogspot.com/feeds/posts/default?alt=rss

Updated: 4 hours 2 min ago

Storing Trailing NULLs in a table

Thu, 2014-03-27 09:49
Oracle has an optimization in that if, for a row, all trailing columns are NULL, it does not have to store the NULL in each column, but saves space by reducing the physical size of the row in the block (in effect "compressing the NULLs").  If, however, the NULLs appear in intermediate columns between the first column and the last column (with one or more non-NULL values intervening or at the end), it has to write one byte for each NULL, without any "compression".

Here is a simple demo with two tables holding 9 data columns each.  In 90% of the rows, 6 columns have NULL values.

In the first table, an intermediate column and the last column have non-NULL value, thus resulting in the columns having NULLs  interspersed with non-NULLs.

SQL> drop table intermediate_nulls;

Table dropped.

SQL>
SQL> create table intermediate_nulls
2 (
3 id_column number,
4 data_col_1 varchar2(25),
5 data_col_2 varchar2(25),
6 data_col_3 varchar2(25),
7 data_col_4 varchar2(25),
8 data_col_5 varchar2(25),
9 data_col_6 varchar2(25),
10 data_col_7 varchar2(25),
11 data_col_8 varchar2(25),
12 data_col_9 varchar2(25)
13 )
14 /

Table created.

SQL>
SQL> REM Insert 1 million rows
SQL> REM 9 in 10 rows have NULLs for cols 2 to 5, 7 to 8 but all have values in cols 1,6,9
SQL> insert into intermediate_nulls
2 select rownum,
3 'Col 1 Data',
4 decode(mod(rownum,10),0,'Col 2 Data', null),
5 decode(mod(rownum,10),0,'Col 3 Data', null),
6 decode(mod(rownum,10),0,'Col 4 Data', null),
7 decode(mod(rownum,10),0,'Col 5 Data', null),
8 'Col 6 Data',
9 decode(mod(rownum,10),0,'Col 7 Data', null),
10 decode(mod(rownum,10),0,'Col 8 Data', null),
11 'Col 9 data'
12 from dual
13 connect by level < 1000001
14 /

1000000 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> exec dbms_stats.gather_table_stats('','INTERMEDIATE_NULLS');

PL/SQL procedure successfully completed.

SQL>
SQL> select num_rows, avg_row_len, blocks
2 from user_tables
3 where table_name = 'INTERMEDIATE_NULLS'
4 /

NUM_ROWS AVG_ROW_LEN BLOCKS
---------- ----------- ----------
1000000 50 7678

SQL>

In the second table, all 6 trailing columns have NULLs.

SQL> drop table trailing_nulls;

Table dropped.

SQL>
SQL> create table trailing_nulls
2 (
3 id_column number,
4 data_col_1 varchar2(25),
5 data_col_2 varchar2(25),
6 data_col_3 varchar2(25),
7 data_col_4 varchar2(25),
8 data_col_5 varchar2(25),
9 data_col_6 varchar2(25),
10 data_col_7 varchar2(25),
11 data_col_8 varchar2(25),
12 data_col_9 varchar2(25)
13 )
14 /

Table created.

SQL>
SQL>
SQL> REM Insert 1 million rows
SQL> REM 9 in 10 rows have NULLs for cols 4 to 9 -- i.e. all trailing cols
SQL> insert into trailing_nulls
2 select rownum,
3 'Col 1 Data',
4 'Col 2 Data',
5 'Col 3 Data',
6 decode(mod(rownum,10),0,'Col 4 Data', null),
7 decode(mod(rownum,10),0,'Col 5 Data', null),
8 decode(mod(rownum,10),0,'Col 6 Data', null),
9 decode(mod(rownum,10),0,'Col 7 Data', null),
10 decode(mod(rownum,10),0,'Col 8 Data', null),
11 decode(mod(rownum,10),0,'Col 9 Data', null)
12 from dual
13 connect by level < 1000001
14 /

1000000 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> exec dbms_stats.gather_table_stats('','TRAILING_NULLS');

PL/SQL procedure successfully completed.

SQL>
SQL> select num_rows, avg_row_len, blocks
2 from user_tables
3 where table_name = 'TRAILING_NULLS'
4 /

NUM_ROWS AVG_ROW_LEN BLOCKS
---------- ----------- ----------
1000000 50 6922

SQL>

We can see that the TRAILING_NULLS table consumes approximately 10% less disk space.
Note : The AVG_ROW_LEN is the small.  When the data is queried, NULLs (one byte each) are returned to the client.  It is in the data block where the "compression" is done.
.
.
.
Categories: DBA Blogs

Plan HASH_VALUE remains the same for the same Execution Plan, even if ROWS and COST change

Sun, 2014-03-23 02:41
Here is a simple demo that shows that the Plan Hash_Value does not consider the ROWS and COST but only the Execution Plan.  Thus, even with more rows added into a table, if the Execution Plan remains the same for a query, it is independent of the number of ROWS and the COST changing.

SQL> -- create the table
SQL> create table branch_list
2 (country_code varchar2(3), branch_code number, branch_city varchar2(50));

Table created.

SQL>
SQL> -- create an index
SQL> create index branch_list_cntry_ndx
2 on branch_list(country_code);

Index created.

SQL>
SQL>
SQL>
SQL> -- populate it with 100 rows, one third being 'IN'
SQL> insert into branch_list
2 select decode(mod(rownum,3),0,'IN',1,'US',2,'US'), rownum, dbms_random.string('X',32)
3 from dual
4 connect by level < 101
5 /

100 rows created.

SQL>
SQL> -- gather statistics
SQL> exec dbms_stats.gather_table_stats('','BRANCH_LIST');

PL/SQL procedure successfully completed.

SQL>
SQL> -- get an execution plan
SQL> explain plan for
2 select branch_code, branch_city
3 from branch_list
4 where country_code = 'IN'
5 /

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 513528032

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 1950 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| BRANCH_LIST | 50 | 1950 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | BRANCH_LIST_CNTRY_NDX | 50 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("COUNTRY_CODE"='IN')

14 rows selected.

SQL>
SQL>
SQL> -- add another 400 rows, none of them being 'IN'
SQL> insert into branch_list
2 select decode(mod(rownum,6),0,'SG',1,'US',2,'US',3,'US',4,'AU',5,'UK'), rownum+100, dbms_random.string('X',32)
3 from dual
4 connect by level < 401
5 /

400 rows created.

SQL>
SQL> -- update statistics
SQL> exec dbms_stats.gather_table_stats('','BRANCH_LIST');

PL/SQL procedure successfully completed.

SQL>
SQL> -- get the execution plan again
SQL> explain plan for
2 select branch_code, branch_city
3 from branch_list
4 where country_code = 'IN'
5 /

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 513528032

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 33 | 1320 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| BRANCH_LIST | 33 | 1320 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | BRANCH_LIST_CNTRY_NDX | 33 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("COUNTRY_CODE"='IN')

14 rows selected.

SQL>
SQL> select column_name, histogram
2 from user_tab_columns
3 where table_name = 'BRANCH_LIST';

COLUMN_NAME HISTOGRAM
------------------------------ ---------------
COUNTRY_CODE FREQUENCY
BRANCH_CODE NONE
BRANCH_CITY NONE

SQL> select count(*) from user_tab_histograms
2 where table_name = 'BRANCH_LIST'
3 and column_name = 'COUNTRY_CODE'
4 /

COUNT(*)
----------
5

SQL>


After the addition of 400 rows to a 100 row table, the distribution of rows has changed. At the second Gather_Table_Stats call, Oracle has properly omputed a Frequency Histogram on the COUNTRY_CODE column for the 5 countries ('IN','US','SG','AU','UK').  The estimate for the COUNTRY_CODE='IN' is now more accurate.

However, what I want to demonstrate here is that although "ROWS" (and "BYTES for that many ROWS) and "COST" have changed in the new Execution Plan, the PLAN HASH VALUE ("513528032") remains the same.  Thus, the PLAN HASH VALUE is independent of changes to the ROWS/BYTES and COST.  The Execution Plan, per se, hasn't changed.
.
.
.



Categories: DBA Blogs

BOGOF from Packt

Sat, 2014-03-22 07:14
Packt Publishing is celebrating the publishing of their 2000th book with a Buy One Get One Free offer.
.
.
.
Categories: DBA Blogs

My slideshare site has had 1000 views

Mon, 2014-03-17 08:17
I have put up some presentations and articles on slideshare.  My site there has had 1,000 views.
.
.
.

Categories: DBA Blogs

"Dropping" an Index Partition

Sat, 2014-03-01 09:06
Here is a simple way to "drop" an Index Partition  using the 11.2 behaviour of DEFERRED_SEGMENT_CREATION  by dropping the segment for the Index Partition.

CORRECTION : Although I posted this as DEFERRED_SEGMENT_CREATION=TRUE behaviour, Marko has pointed out that it applies even when D_S_C is set to FALSE.  So I am posting two test cases, one with D_S_C set to TRUE, and one with it set to FALSE.

This is the first case with DEFERRED_SEGMENT_CREATION=TRUE

HEMANT>show parameter deferre

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
HEMANT>
HHEMANT>l
1 create table sales_history
2 (sale_date date, product_id number, customer_id number, quantity number, price number, remarks varchar2(125))
3 partition by range (sale_date)
4 (partition P_2010 values less than (to_date('01-JAN-2011','DD-MON-YYYY')),
5 partition P_2011 values less than (to_date('01-JAN-2012','DD-MON-YYYY')),
6 partition P_2012 values less than (to_date('01-JAN-2013','DD-MON-YYYY')),
7 partition P_2013 values less than (to_date('01-JAN-2014','DD-MON-YYYY')),
8 partition P_2014 values less than (to_date('01-JAN-2015','DD-MON-YYYY')),
9 partition P_2015 values less than (to_date('01-JAN-2016','DD-MON-YYYY')),
10 partition P_MAX values less than (MAXVALUE)
11* )
HEMANT>/

Table created.

HEMANT>
HEMANT>l
1 create bitmap index sales_history_prdct_ndx
2 on sales_history(product_id)
3* local
HEMANT>/

Index created.

HEMANT>
HEMANT>select segment_name, partition_name
2 from user_segments
3 where segment_type = 'INDEX PARTITION'
4 and segment_name = 'SALES_HISTORY_PRDCT_NDX'
5 /

no rows selected

HEMANT>

Since deferred_segment_creation is set to TRUE, no index partition segments exist till data is loaded.

HEMANT>l
1 insert into sales_history
2 select to_date('01-JUL-2010','DD-MON-RR')+ (rownum*365), mod(rownum,5)+1,mod(rownum,1000)+1, 100, 12,'Sale Done'
3 from dual
4* connect by level < 5
HEMANT>/

4 rows created.

HEMANT>
HEMANT>l
1 select segment_name, partition_name
2 from user_segments
3 where segment_type = 'INDEX PARTITION'
4* and segment_name = 'SALES_HISTORY_PRDCT_NDX'
HEMANT>/

SEGMENT_NAME PARTITION_NAME
------------------------------ ------------------------------
SALES_HISTORY_PRDCT_NDX P_2011
SALES_HISTORY_PRDCT_NDX P_2012
SALES_HISTORY_PRDCT_NDX P_2013
SALES_HISTORY_PRDCT_NDX P_2014

HEMANT>

So, now 4 index partitions are populated for the 4 rows in the 4 table partitions.

What happens when I make an Index Partition UNUSABLE ?

HEMANT>commit;

Commit complete.

HEMANT>alter index sales_history_prdct_ndx modify partition P_2011 unusable;

Index altered.

HEMANT>
HEMANT>l
1 select segment_name, partition_name
2 from user_segments
3 where segment_type = 'INDEX PARTITION'
4* and segment_name = 'SALES_HISTORY_PRDCT_NDX'
HEMANT>/

SEGMENT_NAME PARTITION_NAME
------------------------------ ------------------------------
SALES_HISTORY_PRDCT_NDX P_2012
SALES_HISTORY_PRDCT_NDX P_2013
SALES_HISTORY_PRDCT_NDX P_2014

HEMANT>

The corresponding Index Partition Segment has also "disappeared".  I have released the space that was used by the Index Partition without actually deleting rows from the table.
This is possible with deferred_segment_creation set to TRUE.

Next is the test case with DEFERRED_SEGMENT_CREATION set to FALSE.
Here you will notice that Partitions with zero rows (i.e. those that are empty) still have Segments -- thus all the Partitions are created upfront even in the absence of rows in the table (and index).

HEMANT> show parameter deferred

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean FALSE
HEMANT>
HEMANT> create table transactions_history
2 (txn_id number, txn_date date, txn_product_id number, txn_value number, remarks varchar2(50))
3 partition by range (txn_date)
4 (partition P_2010 values less than (to_date('01-JAN-2011','DD-MON-YYYY')),
5 partition P_2011 values less than (to_date('01-JAN-2012','DD-MON-YYYY')),
6 partition P_2012 values less than (to_date('01-JAN-2013','DD-MON-YYYY')),
7 partition P_2013 values less than (to_date('01-JAN-2014','DD-MON-YYYY')),
8 partition P_2014 values less than (to_date('01-JAN-2015','DD-MON-YYYY')),
9 partition P_2015 values less than (to_date('01-JAN-2016','DD-MON-YYYY')),
10 partition P_MAX values less than (MAXVALUE)
11 )
12 /

Table created.

HEMANT>
HEMANT> create bitmap index txn_hist_prdct_id on
2 transactions_history(txn_product_id)
3 local
4 /

Index created.

HEMANT>
HEMANT> select segment_name, partition_name
2 from user_segments
3 where segment_name = 'TXN_HIST_PRDCT_ID'
4 and segment_type = 'INDEX PARTITION'
5 order by 2
6 /

SEGMENT_NAME PARTITION_NAME
------------------------------ ------------------------------
TXN_HIST_PRDCT_ID P_2010
TXN_HIST_PRDCT_ID P_2011
TXN_HIST_PRDCT_ID P_2012
TXN_HIST_PRDCT_ID P_2013
TXN_HIST_PRDCT_ID P_2014
TXN_HIST_PRDCT_ID P_2015
TXN_HIST_PRDCT_ID P_MAX

7 rows selected.

HEMANT>
HEMANT> insert into transactions_history
2 select rownum, to_date('01-JUL-2010','DD-MON-RR')+ (rownum*365), mod(rownum,5)+1,rownum*100,'Txn Done'
3 from dual
4 connect by level < 5
5 /

4 rows created.

HEMANT>
HEMANT> select segment_name, partition_name
2 from user_segments
3 where segment_name = 'TXN_HIST_PRDCT_ID'
4 and segment_type = 'INDEX PARTITION'
5 order by 2
6 /

SEGMENT_NAME PARTITION_NAME
------------------------------ ------------------------------
TXN_HIST_PRDCT_ID P_2010
TXN_HIST_PRDCT_ID P_2011
TXN_HIST_PRDCT_ID P_2012
TXN_HIST_PRDCT_ID P_2013
TXN_HIST_PRDCT_ID P_2014
TXN_HIST_PRDCT_ID P_2015
TXN_HIST_PRDCT_ID P_MAX

7 rows selected.

HEMANT>
HEMANT> alter index txn_hist_prdct_id modify partition P_2011 unusable;

Index altered.

HEMANT>
HEMANT> select segment_name, partition_name
2 from user_segments
3 where segment_name = 'TXN_HIST_PRDCT_ID'
4 and segment_type = 'INDEX PARTITION'
5 order by 2
6 /

SEGMENT_NAME PARTITION_NAME
------------------------------ ------------------------------
TXN_HIST_PRDCT_ID P_2010
TXN_HIST_PRDCT_ID P_2012
TXN_HIST_PRDCT_ID P_2013
TXN_HIST_PRDCT_ID P_2014
TXN_HIST_PRDCT_ID P_2015
TXN_HIST_PRDCT_ID P_MAX

6 rows selected.

HEMANT>
HEMANT> select *
2 from transactions_history
3 order by txn_date
4 /

TXN_ID TXN_DATE TXN_PRODUCT_ID TXN_VALUE
---------- --------- -------------- ----------
REMARKS
--------------------------------------------------
1 01-JUL-11 2 100
Txn Done

2 30-JUN-12 3 200
Txn Done

3 30-JUN-13 4 300
Txn Done

4 30-JUN-14 5 400
Txn Done


HEMANT>


Here we see that setting the P_2011 Partition UNUSABLE resulting it in being dropped. The segments for Partitions P_2010, P_2015 and P_MAX persist even though they are empty. .
.
.
.

Categories: DBA Blogs

RMAN Image Copy File Names

Sun, 2014-02-23 09:38
RMAN provides a convenient way to name Image Copies (backups) of datafiles to retain the same file name without the path.  This is possible with the "%b" FORMAT modifier.

Here's a demo :

[oracle@localhost ~]$ cd /IMA*/ORCL*
[oracle@localhost ORCL_DB]$ pwd
/IMAGE_BACKUP/ORCL_DB
[oracle@localhost ORCL_DB]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Sun Feb 23 23:24:55 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1229390655)

RMAN> backup as copy database format '/IMAGE_BACKUP/ORCL_DB/%b';


Starting backup at 23-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
output file name=/IMAGE_BACKUP/ORCL_DB/sysaux01.dbf tag=TAG20140223T232527 RECID=3 STAMP=840324368
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
output file name=/IMAGE_BACKUP/ORCL_DB/system01.dbf tag=TAG20140223T232527 RECID=4 STAMP=840324400
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf
output file name=/IMAGE_BACKUP/ORCL_DB/users01.dbf tag=TAG20140223T232527 RECID=5 STAMP=840324414
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_hemant_8pnowslc_.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/23/2014 23:26:55
ORA-01276: Cannot add file /IMAGE_BACKUP/ORCL_DB/o1_mf_hemant_8pnowslc_.dbf. File has an Oracle Managed Files file name.
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
output file name=/IMAGE_BACKUP/ORCL_DB/undotbs01.dbf tag=TAG20140223T232527 RECID=6 STAMP=840324420
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/example01.dbf
output file name=/IMAGE_BACKUP/ORCL_DB/example01.dbf tag=TAG20140223T232527 RECID=7 STAMP=840324428
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
copying current control file
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/23/2014 23:27:12
ORA-19715: invalid format b for generated name
ORA-27302: failure occurred at: slgpn
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 23-FEB-14
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/23/2014 23:27:12
ORA-19715: invalid format b for generated name
ORA-27302: failure occurred at: slgpn

RMAN>
RMAN> quit


Recovery Manager complete.
[oracle@localhost ORCL_DB]$ pwd
/IMAGE_BACKUP/ORCL_DB
[oracle@localhost ORCL_DB]$ pwd
/IMAGE_BACKUP/ORCL_DB
[oracle@localhost ORCL_DB]$ ls -l
total 2641496
-rw-rw---- 1 oracle oracle 85991424 Feb 23 23:27 example01.dbf
-rw-rw---- 1 oracle oracle 1320165376 Feb 23 23:26 sysaux01.dbf
-rw-rw---- 1 oracle oracle 881860608 Feb 23 23:26 system01.dbf
-rw-rw---- 1 oracle oracle 178266112 Feb 23 23:27 undotbs01.dbf
-rw-rw---- 1 oracle oracle 235937792 Feb 23 23:26 users01.dbf
[oracle@localhost ORCL_DB]$


However, this fails with OMF files and controlfile auto backups.

Also, you have to be careful to check that you do not have two datafiles with the same name in two different folders. RMAN recognises the presence of the first datafile copied and raises an error on the second datafile

For example :

SQL> create tablespace test_tbs datafile '/home/oracle/app/oracle/oradata/orcl/test_tbs_01.dbf' size 100M;

Tablespace created.

SQL> alter tablespace test_tbs add datafile '/tmp/test_tbs_01.dbf' size 50M;

Tablespace altered.

SQL>
SQL> !ls -l /home/oracle/app/oracle/oradata/orcl/test_tbs_01.dbf
-rw-rw---- 1 oracle oracle 104865792 Feb 23 23:34 /home/oracle/app/oracle/oradata/orcl/test_tbs_01.dbf

SQL> !ls -l /tmp/test_tbs_01.dbf
-rw-rw---- 1 oracle oracle 52436992 Feb 23 23:34 /tmp/test_tbs_01.dbf

SQL> exit

RMAN> backup as copy tablespace test_tbs format '/IMAGE_BACKUP/ORCL_DB/%b';

Starting backup at 23-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=47 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/orcl/test_tbs_01.dbf
output file name=/IMAGE_BACKUP/ORCL_DB/test_tbs_01.dbf tag=TAG20140223T233608 RECID=8 STAMP=840324970
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/tmp/test_tbs_01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/23/2014 23:36:13
ORA-19504: failed to create file "/IMAGE_BACKUP/ORCL_DB/test_tbs_01.dbf"
ORA-27038: created file already exists
Additional information: 1

RMAN>

The %b format for datafiles is also supposed to be usable with the SET NEWNAME clause in a RESTORE run.

.
.
.
Categories: DBA Blogs

SQL Analytics

Fri, 2014-02-21 22:56
Oracle Learning Library video series on SQL Analytics
SQL Analytics Overview Video Series.
.
.
Categories: DBA Blogs

An SQL Performance Quiz

Thu, 2014-02-20 09:19
Markus Winand has written a very small SQL Performance Quiz.  (I scored 5 out of 5 on Oracle).
He has published a review of the results.  A very large number of visitors have failed.

.
.
.


Categories: DBA Blogs

login.sql does not require a login

Wed, 2014-02-12 07:55
Oracle's sqlplus can use a login.sql file to execute commands -- e.g. setup options.
This file is read and executed when you start sqlplus, even without having logged in to a database.
Here's a quick demo :
I start an sqlplus session without a login.sql
[oracle@localhost ~]$ pwd
/home/oracle
[oracle@localhost ~]$ ls -l login.sql
ls: login.sql: No such file or directory
[oracle@localhost ~]$ sqlplus hemant/hemant

SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 12 08:01:43 2014

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show pagesize
pagesize 14
SQL> show linesize
linesize 80
SQL> show sqlprompt
sqlprompt "SQL> "
SQL>

Now, I create a login.sql and invoke sqlplus without logging in to the database.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ vi login.sql
[oracle@localhost ~]$ cat login.sql
set pagesize 60
set linesize 132
set sqlprompt 'HemantSQL>'
[oracle@localhost ~]$
[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 12 08:05:24 2014

Copyright (c) 1982, 2010, Oracle. All rights reserved.

HemantSQL>show pagesize
pagesize 60
HemantSQL>show linesize
linesize 132
HemantSQL>show user
USER is ""
HemantSQL>

Without having connected to a database (and created a database session), the login.sql was executed.

I can also have it dynamically use a variable --- e.g. the sqlprompt changing based on my login username.

HemantSQL>exit
[oracle@localhost ~]$ vi login.sql
[oracle@localhost ~]$ cat login.sql
set pagesize 60
set linesize 132
set sqlprompt '_USER>'
[oracle@localhost ~]$
[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 12 08:08:12 2014

Copyright (c) 1982, 2010, Oracle. All rights reserved.

>
>show user
USER is ""
>connect hemant/hemant
Connected.
HEMANT>show user
USER is "HEMANT"
HEMANT>connect hr/oracle
Connected.
HR>show user
USER is "HR"
HR>
HR>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$

Notice how the sqlprompt was simply ">" when no user was logged in ? On the "HEMANT" and "HR" logins, the prompt did change.

.
.
.
Categories: DBA Blogs

Database Technology Index

Sat, 2014-02-08 08:41
A very useful page / link.Oracle Database Technology Index      (not strictly 12c only).
.
.
Categories: DBA Blogs

The difference between SELECT ANY DICTIONARY and SELECT_CATALOG_ROLE

Thu, 2014-02-06 09:59
I've seen some DBAs confused about these two "privileges" or "roles".

SELECT ANY DICTIONARY is a System Privilege.

SELECT_CATALOG_ROLE is a Role you would see in DBA_ROLES.  However, querying DBA_SYS_PRIVS does NOT show what privileges are granted to this role.

SELECT_CATALOG_ROLE predates the SELECT ANY DICTIONARY privilege.

The SELECT ANY DICTIONARY privilege grants Read access on Data Dictionary tables owned by SYS.  The SELECT_CATALOG_ROLE role grants Read access to Data Dictionary (DBA_%) and Performance (V$%) views.

Here is a short demo :


SQL*Plus: Release 11.2.0.2.0 Production on Thu Feb 6 07:48:15 2014

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user sad identified by sad;

User created.

SQL> grant create session, select any dictionary to sad;

Grant succeeded.

SQL> create user scr identified by scr;

User created.

SQL> grant create session, select_catalog_role to scr;

Grant succeeded.

SQL>
SQL> connect sad/sad
Connected.
SQL> select count(*) from sys.user$;

COUNT(*)
----------
115

SQL> select count(*) from dba_users;

COUNT(*)
----------
53

SQL> connect scr/scr
Connected.
SQL> select count(*) from sys.user$;
select count(*) from sys.user$
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select count(*) from dba_users;

COUNT(*)
----------
53

SQL>

If you needed to grant a new / junior DBA or a Consultant the privilege to query the Data Dictionary and Performance views, which would you grant ?

.
.
.

Categories: DBA Blogs

My first Backup and Recovery Quiz

Tue, 2014-01-28 09:45
I have created a short 5 question quiz on Oracle Backup and Recovery at QuizBean.  Please test it.  You can submit your comments against this blog post.  (I want to see if QuizBean is reliable).
.
.
.
Categories: DBA Blogs

LAST_CALL_ET in V$SESSION

Sun, 2014-01-26 09:21
The LAST_CALL_ET column in V$SESSION represents, for an ACTIVE, session "represents the elapsed time (in seconds) since the session has become active."

So, if you are running an DML operation, it represents how long the DML has been running.

What if you are running a SELECT that is sending a large number of rows to a client ?  If the query runs for 10minutes and you repeatedly query it, does LAST_CALL_ET show the time since the query began ?

What if you are running a PL/SQL Procedure or an Anonymous PL/SQL block which calls one or more SQL statements ?  At any instant in time the session may be running one of the SQLs.  Does LAST_CALL_ET queried at that instant  show the time that SQL has been running ?


Think about it.

UPDATE 01-Feb-14 :  Here are two discussions around LAST_CALL_ET :

https://community.oracle.com/thread/1115312?start=0&tstart=0
https://community.oracle.com/thread/2584291?start=0&tstart=0

Three earlier posts by me :

16-Aug-2010

17-Aug-2010

22-Aug-2010

.
.
.

Categories: DBA Blogs

OTNYathra 2014

Mon, 2014-01-20 10:18
The February 2014 OTNYathra in India.

.
.
.

Categories: DBA Blogs