Tony’s Oracle Tips

February 21, 2010

What is the difference between OBJECT_ID and DATA_OBJECT_ID?

Filed under: Uncategorized — tonyhasler @ 1:12 pm

You may have noticed that the views DBA_OBJECTS, ALL_OBJECTS and USER_OBJECTS contain two identification columns: OBJECT_ID and DATA_OBJECT_ID. They are described in the documentation as the “Dictionary object number of the object” and the “Dictionary object number of the segment that contains the object”. I found this confusing. Particularly as neither the view DBA_SEGMENTS nor the underlying dictionary table SEG$ contain any identifier. This blog is the results of my research to understand this better. 

To make this blog more readable I will use the term segment id to refer to the DATA_OBJECT_ID. This is unofficial terminology but is easier to read than the dictionary object number of the segment that contains the object

I am sure that anybody keen enough to read my blog knows the difference between a segment and a dictionary object. A segment refers to one or more extents that represent actual storage. Some dictionary objects require storage and some do not. SEQUENCE, PACKAGE, and TYPE BODY are examples of dictionary objects that do not require storage and so have no associated segment. For these cases the column DATA_OBJECT_ID will be either NULL or zero. There is some inconsistency here but otherwise this is straightforward. 

Let us begin our investigation of objects that require storage with a simple example using 11gR1 (in 11gR2 the segment is not allocated until the first row is inserted)

COLUMN object_name format a5
COLUMN subobject_name format a5

CREATE TABLE t1 (a INTEGER);

SELECT object_id, data_object_id
  FROM user_objects
WHERE object_name = 'T1';

The result of these operations is as follows:

OBJECT_ID DATA_OBJECT_ID
---------- --------------
85495 85495

We can see that the two identifiers returned are the same in this case. This may fool you into believing that there is some meaningful correlation between the two numbers but as I have discovered there is not. It is the case that numbers are allocated to objects and segments using the same “number fountain” but as we shall see they need not be the same. 

Let us take another case: 


CREATE TABLE t2 (a INTEGER) PARTITION BY LIST(a)
(PARTITION p1 VALUES (1),PARTITION p2 VALUES (2));

SELECT object_name, subobject_name, object_id,
       data_object_id
  FROM user_objects
 WHERE object_name = 'T2';

The result is of the query is: 


OBJEC SUBOB  OBJECT_ID DATA_OBJECT_ID
----- ----- ---------- --------------
T2               85496
T2    P1         85497          85497
T2    P2         85498          85498

We can see that the table itself has no segment and that the object id and segment id of the partitions are the same as before. 


ALTER TABLE t1 MOVE;
SELECT object_name, subobject_name, object_id,
       data_object_id
  FROM user_objects
 WHERE object_name = 'T1';

After moving the table the results of the query are: 


OBJEC SUBOB  OBJECT_ID DATA_OBJECT_ID
----- ----- ---------- --------------
T1               85495          85499

We can see that although the object id has remained unchanged the newly allocated segment has been allocated a new segment id. 


CREATE TABLE t3 (a INTEGER);

SELECT object_name, subobject_name, object_id,
       data_object_id
  FROM dba_objects
 WHERE object_name = 'T3' AND owner = USER;

ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t3;

SELECT object_name, subobject_name, object_id,
       data_object_id
  FROM user_objects
 WHERE object_name IN ('T2', 'T3');

The test above creates a new table T3 and then exchanges it with partition P1 of T2. This is what happens: 


OBJEC SUBOB  OBJECT_ID DATA_OBJECT_ID
----- ----- ---------- --------------
T3               85500          85500

1 row selected.
Table altered.

OBJEC SUBOB  OBJECT_ID DATA_OBJECT_ID
----- ----- ---------- --------------
T2               85496
T2    P1         85497          85500
T2    P2         85498          85498
T3               85500          85497

You see what has happened? The exchange partition simply swapped the segments around. Now we have one object with a dictionary id of 85500 and another object that is associated with segment id 85500. Any idea that there is some correlation between the numbers now seems lost. 

So what is the point of the segment id? Why has Oracle exposed this column to us when we cannot correlate it with DBA_SEGMENTS? Well, there are two purposes that I have discovered. 


CREATE CLUSTER c1 (a INTEGER);

CREATE TABLE t4 (a  INTEGER)CLUSTER c1(a);
CREATE TABLE t5 (a  INTEGER)CLUSTER c1(a);

SELECT object_name, object_id, data_object_id
  FROM user_objects
 WHERE object_name IN ('C1', 'T4', 'T5');

Here we have created a cluster and allocated two tables to the cluster. This is the output of the query :


OBJEC  OBJECT_ID DATA_OBJECT_ID
----- ---------- --------------
C1         85501          85501
T4         85502          85501
T5         85503          85501

We can see that in the case of clusters more than one object can be associated with a single segment and we can use the segment id to identify all the objects that share a segment. 

Another reason we may need the segment id is when we are examining the buffer cache. 


SELECT COUNT (*)
  FROM v$bh b, user_objects o
 WHERE b.objd = o.data_object_id AND object_name = 'T1';

Buffers in the cache are associated with segments and not objects. This distinction is required for two reasons. The first is that in the case of clusters a single buffer is associated with multiple objects. The second is that if we correlated buffers with objects then an EXCHANGE PARTITION operation would require all of the buffers associated with the objects being exchanged to be renumbered.

In fact, it was a query similar to the above that prompted this analysis. I had incorrectly joined the OBJD column of V$BH with OBJECT_ID from DBA_OBJECTS. Because there were so many objects that had identical values for the segment id and the object id my query looked like it was working fine!

February 1, 2010

Session Time Zone Versus Database Time Zone

Filed under: Uncategorized — tonyhasler @ 1:19 pm

It is funny how you start to learn lots of new things about Oracle when you start working in an environment that is different from ones that you have worked with before. I am currently working with a Windows laptop with UK regional settings and am connecting to databases in New York via a standard Oracle client. I have a colleague in Singapore accessing the same databases and that is how I discovered the weird NLS_TERRITORY issue in this earlier post.

Now here is another time zone related issue that can cause different results with clients in different time zones. What do you think the result of this query is?


SELECT SYSTIMESTAMP-(SYSTIMESTAMP-1) FROM DUAL;

First let us work out the type of the result. SYSTIMESTAMP is of type TIMESTAMP WITH TIME ZONE which I will abbreviate to TTZ for the rest of this post. The documentation tells us that when we subtract a numeric value from a TIMESTAMP (no distinction is made with TTZ) the resultant type is a TIMESTAMP. This appears to be either a documentation error or a bug as the actual type of the expression SYSTIMESTAMP-1 is DATE. If it is a bug then it can never be fixed (for compatibility reasons) so it becomes a documentation error anyway! The same table also tells us that the difference between a TIMESTAMP and a DATE is an INTERVAL [DAY TO SECOND]. And that is indeed the type of the result our query.

Now let us turn our attention to the value of this interval. The numeric ‘1’ is treated as meaning ‘1 day’ so you would imagine that SYSTIMESTAMP-1 would yield the same result as SYSDATE-1 wouldn’t you? Well if that is what you thought you would be right. You get a DATE that specifies the date and time 24 hours ago specified in the time zone of the database (but see update below) as opposed to your session: the time zone information has just been dropped. Now if we consult that same table again we can see that when we subtract a DATE from a TTZ we get an interval. But this note says that “[When] one operand is a DATE value[…] and the other operand has a time zone value the Oracle uses the session time zone in the returned value.” The wording is a little ambiguous but the upshot is that the DATE is now interpreted as being associated with the session time zone. This means that the result of the query is “one day plus (the session time zone minus the database time zone)”. So if the database and the session time zone are the same then you get a fairly predictable result: an INTERVAL DAY TO SECOND of one day (ignoring the fraction of a second error that gets introduced in the conversions) but otherwise you get something else. Try this script:


ALTER SESSION SET TIME_ZONE='-11:00'  -- Something other than the time zone of your database server
                                      -- But see update note below
/
SELECT SYSTIMESTAMP-(SYSTIMESTAMP-1) FROM DUAL;

Note that there is no such problem if you use CURRENT_TIMESTAMP (a TTZ based on the session timestamp) or LOCALTIMESTAMP (a TIMESTAMP with no time zone also based on the session time zone).

UPDATE:  I mention the “time zone of the database” above but this should not be confused with the “database time zone” that you see by using the built-in DBTIMEZONE function.  The latter is only used for TIMESTAMP WITH LOCAL TIME ZONE data types.  The time zone used by the function SYSTIMESTAMP is the time zone taken from the operating system of the server on which the database resides.

Create a free website or blog at WordPress.com.