oracle select from global_name;,GLOBAL_NAME和props$对象介绍

作者：Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

我们知道，全局数据库名是在分布式数据库系统中用于标识数据库的唯一名称，默认为DB_NAME.DB_DOMAIN。

该默认值在数据库创建的时候被标记，如果数据库创建后手工修改了DB_NAME或者DB_DOMAIN，全局数据库名称仍然保持为数据库创建时候的DB_NAME.DB_DOMAIN。

SYS@lunarp>select * from GLOBAL_NAME;

GLOBAL_NAME

------------------------------------------------------------------------------------------------------------------------------------------------------

lunarbb

Elapsed: 00:00:00.01

SYS@lunarp>desc GLOBAL_NAME

Name Null? Type

----------------------------------------------------------------------------------- -------- --------------------------------------------------------

GLOBAL_NAME VARCHAR2(4000)

SYS@lunarp>

我们查看一下GLOBAL_NAME是什么类型的对象，其定义是怎样的：

SYS@lunarp>col object_name for a50

SYS@lunarp>select owner,object_name,object_type from dba_objects where object_name=upper('global_name');

OWNER OBJECT_NAME OBJECT_TYPE

------------------------------ -------------------------------------------------- -------------------

SYS GLOBAL_NAME VIEW

PUBLIC GLOBAL_NAME SYNONYM

Elapsed: 00:00:00.00

SYS@lunarp>select dbms_metadata.get_ddl('VIEW','GLOBAL_NAME','SYS') from dual;

CREATE OR REPLACE FORCE VIEW "SYS"."GLOBAL_NAME" ("GLOBAL_NAME") AS

select value$ from sys.props$ where name = 'GLOBAL_DB_NAME'

Elapsed: 00:00:00.02

SYS@lunarp>select dbms_metadata.get_ddl('SYNONYM','GLOBAL_NAME','PUBLIC') from dual;

CREATE OR REPLACE PUBLIC SYNONYM "GLOBAL_NAME" FOR "SYS"."GLOBAL_NAME"

Elapsed: 00:00:00.10

SYS@lunarp>

可以看到，GLOBAL_NAME实际上是已于sys.props$ where name = ‘GLOBAL_DB_NAME’的一张视图和同义词。

props$中总共多少行数据，在相同版本是固定的，我这里是11.2，因此共36行：

SYS@lunarp>select count(*) from props$;

COUNT(*)

----------

36

Elapsed: 00:00:00.01

SYS@lunarp>

props$的定义如下：

SYS@lunarp>select dbms_metadata.get_ddl('TABLE','PROPS$','SYS') from dual;

CREATE TABLE "SYS"."PROPS$"

( "NAME" VARCHAR2(30) NOT NULL ENABLE,

"VALUE$" VARCHAR2(4000),

"COMMENT$" VARCHAR2(4000)

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "SYSTEM"

Elapsed: 00:00:03.89

SYS@lunarp>

看一下props$对象存储了哪些内容：

SYS@lunarp>select * from props$;

NAME VALUE$ COMMENT$

------------------------------ -------------------------------------------------- --------------------------------------------------

DICT.BASE 2 dictionary base tables version #

DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace

DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace

DEFAULT_EDITION ORA$BASE Name of the database default edition

Flashback Timestamp TimeZone GMT Flashback timestamp created in GMT

TDE_MASTER_KEY_ID

DBTIMEZONE -08:00 DB time zone

DST_UPGRADE_STATE NONE State of Day Light Saving Time Upgrade

DST_PRIMARY_TT_VERSION 14 Version of primary timezone data file

DST_SECONDARY_TT_VERSION 0 Version of secondary timezone data file

DEFAULT_TBS_TYPE SMALLFILE Default tablespace type

NLS_LANGUAGE AMERICAN Language

NLS_TERRITORY AMERICA Territory

NLS_CURRENCY $ Local currency

NLS_ISO_CURRENCY AMERICA ISO currency

NLS_NUMERIC_CHARACTERS ., Numeric characters

NLS_CHARACTERSET AL32UTF8 Character set

NLS_CALENDAR GREGORIAN Calendar system

NLS_DATE_FORMAT DD-MON-RR Date format

NLS_DATE_LANGUAGE AMERICAN Date language

NLS_SORT BINARY Linguistic definition

NLS_TIME_FORMAT HH.MI.SSXFF AM Time format

NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format

NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format

NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone format

NLS_DUAL_CURRENCY $ Dual currency symbol

NLS_COMP BINARY NLS comparison

NLS_LENGTH_SEMANTICS BYTE NLS length semantics

NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception

NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set

NLS_RDBMS_VERSION 11.2.0.3.0 RDBMS version for NLS parameters

GLOBAL_DB_NAME Global database name

EXPORT_VIEWS_VERSION 8 Export views revision #

WORKLOAD_CAPTURE_MODE CAPTURE implies workload capture is in progress

WORKLOAD_REPLAY_MODE PREPARE implies external replay clients can connec

t; REPLAY implies workload replay is in progress

NO_USERID_VERIFIER_SALT 6A58202A068B678B09CF571DC967F3EC

36 rows selected.

Elapsed: 00:00:00.00

SYS@lunarp>

当数据库正常启动时，会对PROPS$表进行全表扫描来获取PROPS$中是否含有错误信息。

一般升级的时候系统会将数据库引导对象的错误信息记录到该表的BOOTSTRAP_UPGRADE_ERROR中：

SYS@lunarp>select * from props$ where name = 'GLOBAL_DB_NAME';

NAME VALUE$ COMMENT$

------------------------------ ------------------------------ --------------------------------------------------

GLOBAL_DB_NAME lunarbb Global database name

Elapsed: 00:00:00.01

SYS@lunarp>

启动时10046跟踪的trace中：

PARSING IN CURSOR #140086496404128 len=60 dep=1 uid=0 oct=3 lid=0 tim=1407167733432924 hv=3306824342 ad='8503c550' sqlid='g64r07v2jn8nq'

SELECT NULL FROM PROPS$ WHERE NAME='BOOTSTRAP_UPGRADE_ERROR'

END OF STMT

PARSE #140086496404128:c=17997,e=76437,p=3,cr=12,cu=0,mis=1,r=0,dep=1,og=4,plh=415205717,tim=1407167733432919

EXEC #140086496404128:c=0,e=44,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=415205717,tim=1407167733433101

WAIT #140086496404128: nam='db file sequential read' ela= 60288 file#=1 block#=800 blocks=1 obj#=98 tim=1407167733493464

WAIT #140086496404128: nam='db file sequential read' ela= 14273 file#=1 block#=801 blocks=1 obj#=98 tim=1407167733507890

FETCH #140086496404128:c=999,e=74855,p=2,cr=3,cu=0,mis=0,r=0,dep=1,og=4,plh=415205717,tim=1407167733507990

STAT #140086496404128 id=1 cnt=0 pid=0 pos=1 obj=98 op='TABLE ACCESS FULL PROPS$ (cr=3 pr=2 pw=0 time=74849 us)'

从这里我们看到，读取了file#=1 block#=800和file#=1 block#=801，他们分别为props$的段头和第一个数据块的位置。

他们的对象号是obj#=98，也就是props$。

从数据库中，我们也可以证实这一点：

SYS@lunarp>select owner,segment_name,HEADER_FILE,HEADER_BLOCK from dba_segments where SEGMENT_NAME='PROPS$';

OWNER SEGMENT_NAME HEADER_FILE HEADER_BLOCK

------------------------------ -------------------------------------------------- ----------- ------------

SYS PROPS$ 1 800

Elapsed: 00:00:00.01

SYS@lunarp>

SYS@lunarp>select dbms_rowid.rowid_relative_fno(rowid) file#,

2 dbms_rowid.rowid_block_number(rowid) blk#

3 from sys.props$

4 where name = 'GLOBAL_DB_NAME';

FILE# BLK#

---------- ----------

1 801

Elapsed: 00:00:00.10

SYS@lunarp>select obj#,dataobj#,owner#,name from obj$ where obj#=98;

OBJ# DATAOBJ# OWNER# NAME

---------- ---------- ---------- ------------------------------

98 98 0 PROPS$

Elapsed: 00:00:00.01

SYS@lunarp>