Blogroll Report 25/12/2009 – 01/01/2010

<—- Blogroll Report 18/12/2009 – 25/12/2009

1-How do function based indexes using date functions behave for different NLS session settings?
Gary Myers-Happy New Year and fun with dates

2-How does CPU count initialization parameter work?
Lua Donghua-CPU_COUNT initialization parameter

3-Pocket Reference Guide for ASM
Charles Kim-ASM Pocket Reference Guide

4-Pocket Reference Guide for RAC
Charles Kim-Real Application Clusters (RAC) Pocket Reference Guide

5-How many rows are sorted and how much memory used by your query?
Jonathan Lewis-Short Sorts

6-How much memory used per program ?
Ronny Egner-How much memory is being used per program

7-Why does streams appyl process perform well in 11GR2 (oracle internals)?
Alex Fatkulin-Oracle Streams Apply Process changes in 11GR2

8-How to toubleshoot “ORA-00304: requested INSTANCE_NUMBER is busy” in a non-RAC environment?
Arup Nanda-Instance_Number is busy Message during Standby Instance Startup

9-Tom Kyte’s thoughts about Edition Based Redefinition and deferred segment creation
Thomas Kyte-A Closer Look at the New Edition

10-How to work with hybrid columnar compression in Exadata V2?
Arup Nanda-Compressing Columns

11-How to relocate grid infrastructure installation in 11GR2?
Rene Kundersma-Relocating Grid Infrastructure

12-All about grouping
Rob Van Wijk-Paper abput grouping

13-How to use dbms_metadata on objects other than dual?
Jonathan Lewis-DBMS_METADATA

14-How can system stats collection or manually setting mbrc cause performance problems ?
Charles Hooper-High Value for MBRC Causes High BCHR, High CPU Usage, and Slow Performance

15-How to get peeked binds from OTHER_XML column of V$SQL_PLAN?
Dominic Brooks-SQL_PLAN – other_xml

16-How to track down plan changes with AWR?
Dominic Brooks-Plans gone AWRy

17-How to solve CRS is not installed on any of the nodes, reported by cluvf, problem?
Amit Bansal-Cluvfy Reports CRS not Installed on Nodes

18-Data pump tips for parallel and compress
Rajeev Ramdas Thottathil-Datapump export and import – parallel and compress

19-Performance of IGNORE_ROW_ON_DUPKEY_INDEX in 11GR2
Guy Harrison-The 11GR2 IGNORE_ROW_ON_DUPKEY_INDEX hint

20-How to install 11GR2 RAC on Linux Using NFS?
Tim Hall-Oracle Database 11g Release 2 RAC On Linux Using NFS

21-How to configure DNS for SCAN in 11GR2 for personal tests?
Tim Hall-DNS Configuration for the SCAN used with Oracle RAC Database 11g Release 2

22-How to use Analytical Functions for certain complex problems ?
Charles Hooper-SQL – DENSE_RANK, PERCENT_RANK, and COUNT Analytical Functions

Charles Hooper-SQL – COUNT Analytical Function, GROUP BY, HAVING

Charles Hooper-SQL – RANK, MAX Analytical Functions, DECODE, SIGN

23-How does CBO behave for deleting rows with exists or in and which one is better?
Charles Hooper-Which is Most Efficient when Deleting Rows: EXISTS, IN, or a VIEW

24-Possible workaround for “ORA-15077:could not locate ASM instance serving a required diskgroup”
David Marcos-PRKP-1001 : Error starting instance …. followed by CRS-0215

Blogroll Report 01/01/2010 – 08/01/2010 –>

Object without object_id

This is probably not new to most of you who checked the definition of DBA_OBJECTS view, but it was new to me, and I wanted to share.

Normally in documentation DBA_OBJECTS is referenced to ALL_OBJECTS view as all other DBA_XXX views, and OBJECT_ID column in ALL_OBJECTS view is NOT_NULL like below, but DBA_OBJECTS does not have any information about nullability

What I was trying was creating a test table created with CTAS from dba_objects  and tried to create a primary key on OBJECT_ID but I couldn’t because of having nulls. When I checked I found that DB_LINKS don’t have object_id.

 
 
SQL> desc all_objects;
           Name                            Null?    Type
           ------------------------------- -------- ------------------
    1      OWNER                           NOT NULL VARCHAR2(30)
    2      OBJECT_NAME                     NOT NULL VARCHAR2(30)
    3      SUBOBJECT_NAME                           VARCHAR2(30)
    4      OBJECT_ID                       NOT NULL NUMBER
    5      DATA_OBJECT_ID                           NUMBER
    6      OBJECT_TYPE                              VARCHAR2(19)
    7      CREATED                         NOT NULL DATE
    8      LAST_DDL_TIME                   NOT NULL DATE
    9      TIMESTAMP                                VARCHAR2(19)
   10      STATUS                                   VARCHAR2(7)
   11      TEMPORARY                                VARCHAR2(1)
   12      GENERATED                                VARCHAR2(1)
   13      SECONDARY                                VARCHAR2(1)
   14      NAMESPACE                       NOT NULL NUMBER
   15      EDITION_NAME                             VARCHAR2(30)

SQL> desc dba_objects
           Name                            Null?    Type
           ------------------------------- -------- ------------------
    1      OWNER                                    VARCHAR2(30)
    2      OBJECT_NAME                              VARCHAR2(128)
    3      SUBOBJECT_NAME                           VARCHAR2(30)
    4      OBJECT_ID                                NUMBER
    5      DATA_OBJECT_ID                           NUMBER
    6      OBJECT_TYPE                              VARCHAR2(19)
    7      CREATED                                  DATE
    8      LAST_DDL_TIME                            DATE
    9      TIMESTAMP                                VARCHAR2(19)
   10      STATUS                                   VARCHAR2(7)
   11      TEMPORARY                                VARCHAR2(1)
   12      GENERATED                                VARCHAR2(1)
   13      SECONDARY                                VARCHAR2(1)
   14      NAMESPACE                                NUMBER
   15      EDITION_NAME                             VARCHAR2(30)
 

When I check the definition of DBA_OBJECTS, I saw that DB_LINKS are added with a union to the view without an object_id

 
.....
.....
.....
union all
select u.name, l.name, NULL, to_number(null), to_number(null),
       'DATABASE LINK',
       l.ctime, to_date(null), NULL, 'VALID','N','N', 'N', NULL, NULL
from sys.link$ l, sys.user$ u
where l.owner# = u.user#;
 

I check ALL_OBJECTS again and could not find any DB_LINK listed in  ALL_OBJECTS. In my understanding DB_LINKS are not even objects, at least they don’t deserve to have an OBJECT_ID from Oracle’s point.