object_id and data_object_id in dba_objects
對於dba_objects,有幾個值得注意的地方
1, object_id與data_object_id是有區別的,前者指dictionary object number;後者指dictionary object number of the segment
2, 對普通表及索引來說,object_id與data_object_id是一致的.當object不佔用儲存空間時, data_object_id為null,如分割槽表(不是指分割槽),view,sequence,synonym等等
3, 分割槽表中的分割槽有不同的object_id和data_object_id; 分割槽表無data_object_id
4, 除了上面描述的以外,當下述情況發生時,object_id與data_object_id會有區別
[具體見後面引用自yong huang的文章]
5, object_id,data_object_id的分配規則
[具體見後面引用自yong huang的文章]
6, object_id,data_object_id各自的適用場合
[具體見後面引用自yong huang的文章]
7, 注意dbms_rowid包中的object_id是指segment object id即dba_objects.data_object_id
注意dba_objects.object_id與dba_objects.data_object_id的區別
OBJECT_ID :Dictionary object number of the object
DATA_OBJECT_ID :Dictionary object number of the segment that contains
the object
Note: OBJECT_ID and DATA_OBJECT_ID display data
dictionary metadata. Do not confuse these numbers with
the unique 16-byte object identifier (object ID) that the
Oracle Database assigns to row objects in object tables in
the system.
##dba_objects.data_object_id 為null的object_type
#其中''TABLE','INDEX' 是partitioned table,partitioned index,因為segment儲存在單獨的分割槽中
SQL> set head off
SQL> set linesize 1000
SQL> set pagesize 1000
SQL>select distinct object_type from dba_objects where object_name is not null and DATA_OBJECT_ID is null;
CONSUMER GROUP
SEQUENCE
QUEUE
SCHEDULE
RULE
PROCEDURE
OPERATOR
WINDOW
PACKAGE
PACKAGE BODY
LIBRARY
RULE SET
PROGRAM
TYPE BODY
CONTEXT
TRIGGER
JOB CLASS
UNDEFINED
DIRECTORY
SYNONYM
TABLE
VIEW
FUNCTION
INDEX
WINDOW GROUP
TYPE
RESOURCE PLAN
EVALUATION CONTEXT
JOB
29 rows selected.
SQL> col object_name for a30
SQL> col subobject_name for a30
SQL> select object_id,data_object_id,object_name,subobject_name,object_type from dba_objects where object_id in (9820,9819);
9819 24127 BOSSSELLEROLCOMMDETAIL BOSSSELLEROLCOMMDETAIL_P24 TABLE PARTITION
9820 28807 BOSSSELLEROLCOMMDETAIL BOSSSELLEROLCOMMDETAIL_P25 TABLE PARTITION
#可見,分割槽表中的分割槽有單獨的object_id及data_object_id
the following is very important when you use relative knowleges, be sure reference it !
-----------------quote begin---------------------------------------------------
xxx_objects.data_object_id differs from object_id after truncate table (or alter table truncate partition), unless table/partition is empty alter table move (or alter table move partition) alter index rebuild (or alter index rebuild partition) alter table exchange partition Alter table move even without TABLESPACE clause (or specifying the same tablespace) physically moves the table, based on dba_extents.file_id and block_id. But truncate table doesn't move the table. Think of data_object_id as an ID for the segment. If xxx_objects.data_object_id is null, it must be an object not associated with a physical segment, such as view, sequence, domain index, partitioned table/index whose segments are in the individual partitions. * How much does data_object_id increment? Except in case of partition-table exchange, data_object_id is brought up to select max(data_object_id)+1 from dba_objects If you just create a new table, it will be assigned an object_id and data_object_id of select greatest(max(object_id), max(data_object_id)) from dba_objects If the table has an index, truncate will increment data_object_id by 2 because its index takes the number 1 below it. If there's no index, truncate should increment data_object_id by 1 only. Exchange of a partition with a table swaps their data_object_id's. So don't assume data_object_id's always go up. If the table or its partition is already empty, truncating it does not increment data_object_id. (Rebuilding an empty index or its partition still increments data_object_id.) * When to use which ID? You use data_object_id in dbms_rowid.rowid_object, v$bh and x$bh, but object_id in most other cases, such as v$locked_object. v$segstat, v$segment_statistics and v$logmnr_contents have both.
-----------------quote end --------------------------------------------
--tengird 07/01/04 pm
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/94384/viewspace-600300/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- OBJECT_ID和DATA_OBJECT_IDObject
- Differences between DATA_OBJECT_ID and OBJECT_IDObject
- object_id 及 data_object_id (oracle)ObjectOracle
- object_id 與data_object_id 的區別Object
- object_id與data_object_id淺析(一)Object
- object_id與data_object_id淺析(二)Object
- object_id與data_object_id的關係Object
- OBJECT_ID、DATA_OBJECT_ID與truncate的本質Object
- Oracle中object_id和data_object_id的區別OracleObject
- data_object_id 及 object_id 的聯絡與區別Object
- object_id and data_object_id 區別_20091213Object
- Truncate and data_object_idObject
- OBJECT_ID的重用Object
- OBJECT_ID的重用(二)Object
- SQL:OBJECT_ID和OBJECTPROPERTYSQLObject
- dba_objects 中不包含 constraintObjectAI
- 關於DBA_OBJECTS的LAST_DDL_TIMEObjectAST
- truncate操作導致DATA_OBJECT_ID改變Object
- lob索引在不同版本dba_objects存在問題索引Object
- v$session之小測試(三)_與dba_objectsSessionObject
- Oracle11g觸發器重用OBJECT_IDOracle觸發器Object
- Oracle dba_objects和all_objects 最大的區別OracleObject
- procedure儲存過程呼叫dba字首的字典dba_objects儲存過程Object
- Ora-600 [15160] Joining Dba_objects and Dba_segmentsObject
- 11G訪問DBA_OBJECTS和V$LOCK檢視時HANG住Object
- tx鎖之ROW_WAIT_OBJ#和object_id關聯排障AIObject
- 不經意發現的dba_objects和dba_tables中的細節Object
- 關聯v$session,v$locked_object,dba_objects查出鎖死會話及物件SessionObject會話物件
- [20191204]oracle能建立最大object_id是多少.txtOracleObject
- 【檢視】oracle 資料字典檢視之 DBA_OBJECTS / ALL_OBJECTS / USER_OBJECTS(OBJ)OracleObject
- 11g檢視dba_objects中增加了1個有用的欄位namespaceObjectnamespace
- [20191206]為什麼oracle能建立最大object_id=4254950910.txtOracleObject
- itpub論壇提出的問題_為何dba_objects和dba_indexes中同一個user的index數量不一致ObjectIndex