view counter

Feed items

An ALTER USER to change password updates the timestamp of the password file

Apparently, upto, the password file is opened and verified and then closed when an ALTER USER ... IDENTIFIED BY ... is issued for any database account.

Here's a demo :

[oracle@localhost ~]$ cd $ORACLE_HOME/dbs
[oracle@localhost dbs]$ ls -l orapw*
-rw-r----- 1 oracle oracle 1536 Jun 27 23:53 orapworcl
[oracle@localhost dbs]$ date
Wed Nov 2 23:12:27 SGT 2011
[oracle@localhost dbs]$ sqlplus hemant/hemant

SQL*Plus: Release Production on Wed Nov 2 23:12:47 2011

Handling Exceptions in PLSQL

Tom Kyte has repeatedly, repeatedly, repeatedly found fault with the usage of WHEN OTHERS -- particularly without a RAISE or RAISE_APPLICATION_ERROR.

See his article Why You Really Want to Let Exceptions Propagate (you need to scroll down the page) in the latest issue of Oracle Magazine.


The impact of ASSM on Clustering of data -- 2

Revisiting my earlier test on the same topic, I add a change to "slow" down the operations. The fetching of sequence values with the default cache was so fast in the earlier test, that the three sessions that I manually invoked to execute concurrently did not show a real distribution of data. The first session had retrieved very many sequence values and inserted rows before the second session had even started ...

DBMS_REDEFINITION to redefine a Partition -- and the impact of deferred_segment_creation

During a forums discussion on START_REDEF, I created test cases in 10.2 and 11.2 to test DBMS_REDEFINITION to redefine a Partition (instead of using ALTER TABLE .... EXCHANGE PARTITION).

If there is a Primary Key, normally, I should use options_flag => DBMS_REDEFINITION.CONS_USE_PK However, if I use options_flag => DBMS_REDEFINITION.CONS_USE_ROWID, Oracle attempts to create another Unique Index.

The impact of ASSM on Clustering of data

Automatic Segment Space Management ("ASSM") is designed to reduce block-level contention by multiple sessions concurrently attempting INSERTs by using a bitmap to identify blocks that are on the FreeList. The FreeList is accessed by sessions attempting to insert a row in a table when they need to identify "candidate" blocks. In Manual Segment Space Management ("MSSM"), it is the block-level storage parameter PCTUSED that is a determinant for when a block "enters" and "exits" the freelist.

Controlfiles : Number and Size

A forums thread "Maximum number of Control Files" allowed me to express my opinions about why, for very practical reasons, Oracle does not allow very many multiplexed copies of the controlfile and why the controlfile is "limited" in size.

Here are my two responses :
On the "maximum number of control files" :

You have to consider how Oracle uses the control file.

RMAN Tips -- 3

ALWAYS mention the actual Start and End Times and Contents of all backups.

Whenever you
a. Post a query on forums / email lists
b. Talk to a colleague
c. Report to your manager
*always* mention the Start and End times and contents of your backups.
"Weekly Full Backup and Daily Incremental Backup" is empty of meaning when attempting to resolve a Backup-Restore issue.

Another example of GATHER_TABLE_STATS and a Histogram

In response to a forum thread "Why not selecting index path .", I ran up this example :

SQL> select banner from v$version where rownum=1;

Oracle Database 11g Enterprise Edition Release - Production

SQL> drop table test_index_count5 purge;

Table dropped.

SQL> alter system flush shared_pool;

System altered.
view counter