11 July 2007


I didn't want to be the only Oracle blogger who didn't observe the holy day of HOLY CRAP ORACLE 11G IS COMING!!!!!11

Sarcastic excitement aside, I'm looking forward to Arup Nanda's 11g features series (with handy rss feed).

Gathering stats for ONLY a partition

I'm sure you all know to use DBMS_STATS to gather statistics for your tables and indexes. The DBMS_STATS.GATHER_TABLE_STATS() method has as its first three parameters OWNNAME, TABNAME, and PARTNAME, specifying the schema owner name, table name, and table partition name, respectively.

I had been assuming that specifying a partition name via the PARTNAME parameter would mean that I'd be gathering stats for just that partition. Wrong! You also need to specify GRANULARITY => PARTITION if that is your goal. Otherwise the PARTNAME is simply ignored and global (table-wide) statistics will be gathered instead.

Thanks to Wolfgang Breitling for the cluestick.

CONNECT role, we hardly knew ye.

I knew that in Oracle 10gR2, the CONNECT role had been stripped down to only provide the CREATE SESSION privilege. I did not, however, know that Oracle had officially deprecated the CONNECT role.

Count me among the lazy DBAs that Andrew Clarke is referring to. :p

06 July 2007

TWO_TASK is actually still used.

You'll have to forgive my obtuseness. I only ever log into my Oracle servers as "oracle" and am able to login locally with

$ sqlplus / as sysdba
$ sqlplus dts

So today I was a little scared when I couldn't do this from an OS user other than oracle. The $ORACLE_SID variable was indeed set correctly. After quite a bit of hot Google action it turns out that sqlplus still uses the TWO_TASK environment variable. For some reason I had assumed it was deprecated and/or obsolete since I had never had to use it.

Sometimes it helps to step down from the mountaintop for a clearer view.

05 July 2007

The Story of RMAN Duplication and the Pre-Existing Tempfiles

Just a brief note to share with you that if you are performing an RMAN duplication over an existing instance, you'll want to delete your temporary tablespace tempfiles first. Metalink Note 374934.1 can tell you the whys and wherefores. The note also seems to suggest that the problem exists solely in Oracle

The note suggests getting the list of datafiles with "select * from dba_temp_files;", doing a "startup force nomount" and then deleting the files via OS commands, before finally kicking off the RMAN duplicate command. I had been toying with a different method (creating a new temp2 tablespace and dropping the original with contents and datafiles), but this method seems to be cleaner. Plus it is the WORD OF METALINK.