view counter

Feed items

Capturing SQL PLAN Baselines

An introductory :

SQL> select count(*) from dba_sql_plan_baselines ;


SQL> declare
2 ret_value pls_integer;
3 sql_id_in varchar2(30);
4 cursor c1 is
5 select distinct sql_id from v$sql,dba_users
6 where parsing_user_id = user_id
7 and username = 'HEMANT'
8 order by 1;
9 begin
10 open c1 ;
11 loop
12 fetch c1 into sql_id_in;
13 exit when c1%NOTFOUND;
14 dbms_output.put_line('For SQL_ID : ' || sql_id_in);

11g OCP

I passed the 1Z0-055 "Oracle Database 11g: New Features for 9i OCPs" examination today.


Getting all the instance parameters

A simple method (in 11g -- here tested in to get all the database instance parameters.

Currently, I am using an SPFILE :

[oracle@localhost ~]$ cd $ORACLE_HOME/dbs
[oracle@localhost dbs]$ ls -ltr
total 32
-rw-r--r-- 1 oracle oracle 2851 May 16 2009 init.ora
drwx------ 2 oracle oracle 4096 Oct 30 2009 peshm_DBUA0_0
-rw-rw---- 1 oracle oracle 1544 Oct 30 2009 hc_DBUA0.dat
drwx------ 2 oracle oracle 4096 Oct 30 2009 peshm_orcl_0
-rw-rw---- 1 oracle oracle 24 Apr 7 2010 lkORCL

RMAN's COPY command

Following some thread on forums, I had logged a documentation query against the 11gR2 RMAN documentation on 31-Jan.

My question to Oracle's 11gR2 documentation team had been

"Why is the COPY command not documented in the Reference ?" (referring to the 11gR2 Backup and Recovery Reference).

I have today received a response from the documentation team :

Collection of my Oracle Blog posts on Backup and Recovery

I have uploaded a collection of my Oracle Blog posts on Backup and Recovery, Control files, ArchiveLogs and such. This is now available as a PDF file. I hope that it will be useful as it is downloadable and viewable "off the web".

The entries go as far back as December 2006 and are current as of upto 03-May-2011. I have also included comments as of upto 03-May-2011.

SELECT FOR UPDATE with SubQuery (and "Write Consistency")

Just a quick post to provide a reference to a "bug" aka "expected behaviour if it is in the specification" that Jonathan Lewis has written about.


ArchiveLogs in the controlfile

RMAN's listing will only show those that RMAN has not obsoleted or deleted.

RMAN> crosscheck archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
validation failed for archived log
archived log file name=/addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_04_09/o1_mf_1_722_6t0vvymw_.arc RECID=549 STAMP=748046335
validation failed for archived log


Returning to my thread on Deterministic Functions ......

Suppose that I have a (partitioned) SALES table with data like this :

SQL> desc sales;
Name Null? Type
----------------------------------------- -------- ----------------------------

Standby Databases (aka "DataGuard") -1

(before I return to my series on Deterministic functions ........... )

This week I have had two sets of questions about standby databases -- what a coincidence ! The last time I really worked with a Standby was in 8.1.7 -- without the DataGuard "backport" from 9i. (and, before that, in 7.0). All of a sudden, I have had questions about 10g and 11 DG.

Anyways, since I had started "exploring" 11.2 DG (skipping over 9i and 10g) a few days ago, and had setup a DG environment in my VM, here are some entries from the Standby's alert.log :

DETERMINISTIC Functions -- 2

Following up on my previous post, I now take up a Deterministic Function defined on a date column.

First, I setup the test data :

SQL> create or replace function ten_days_ago_date(date_in date) return date     DETERMINISTIC
2 as
3 deducted_date date;
4 begin
5 select date_in - 10 into deducted_date from dual;
6 return deducted_date;
7 end;
8 /

Function created.

view counter