SYNONYMS
SQL> SELECT * FROM V$VERSION WHERE ROWNUM=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
[oracle@localhost ~]$ cat /etc/issue
Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
Kernel \r on an \m
下面是官方文檔對同義詞的介紹：
A synonym is an alias for a schema object. Synonyms can provide a level of security
by masking the name and owner of an object and by providing location transparency for remote objects of a distributed database. Also, they are convenient to use and reduce the complexity of SQL statements for database users.
Synonyms allow underlying objects to be renamed or moved, where only the synonym must be redefined and applications based on the synonym continue to function without modification.
You can create both public and private synonyms. A public synonym is owned by the special user group named PUBLIC and is accessible to every user in a database. A private synonym
is contained in the schema of a specific user and available only to the user and to grantees for the underlying object.Synonyms themselves are not securable. When you grant object privileges on a synonym,
you are really granting privileges on the underlying object, and the synonym is acting only as an alias for the object in the GRANT statement.
Creating Synonyms
To create a private synonym in your own schema, you must have the CREATE SYNONYM privilege. To create a private synonym in another user's schema,
you must have the CREATE ANY SYNONYM privilege. To create a public synonym, you must have the CREATE PUBLIC SYNONYM system privilege.
Create a synonym using the CREATE SYNONYM statement. The underlying schema object need not exist, nor do you need privileges to access the object for the CREATE SYNONYM statement to succeed. The following statement creates a public synonym named public_emp on
the emp table contained in the schema of jward:
CREATE PUBLIC SYNONYM public_emp FOR jward.emp
When you create a synonym for a remote procedure or function, you must qualify the remote object with its schema name. Alternatively, you can create a local public synonym on the database where the remote object resides, in which case the database link must
be included in all subsequent calls to the procedure or function.
Dropping Synonyms
You can drop any private synonym in your own schema. To drop a private synonym in another user's schema, you must have the DROP ANY SYNONYMsystem
privilege. To drop a public synonym, you must have the DROP PUBLIC SYNONYM system privilege.
Drop a synonym that is no longer required
using DROP SYNONYM statement. To drop a private synonym, omit the PUBLIC keyword. To drop a public synonym, include the PUBLIC keyword.
For example, the following statement drops the private synonym named emp:
DROP SYNONYM emp;
The following statement drops the public synonym named public_emp:
DROP PUBLIC SYNONYM public_emp;
When you drop a synonym, its definition is removed from the data dictionary. All objects that reference a dropped synonym remain. However, they become invalid (not usable). For more information about how dropping synonyms can affect other schema objects, see "Managing
Object Dependencies".
下面我們來動手創建同義詞：
SQL> create Synonym hrt for hr.t;
create public Synonym tt for hr.t
*
第 1 行出現錯誤:
ORA-01031: 權限不足
沒有權限啦，我們來授權：
SQL> grant create synonym to hr;
授權成功。
SQL> create synonym hrt for hr.t;
同義詞已創建。
上面普通用戶創建的是私有同義詞，dba可以創建public synonyms,
當然普通用戶通過被授權也能創建共有同義詞：
SQL> create public Synonym tt for hr.t;
create public Synonym tt for hr.t
*
第 1 行出現錯誤:
ORA-01031: 權限不足
SQL> grant create public synonym to hr;
授權成功。
SQL> create public Synonym tt for hr.t;
同義詞已創建。
共有同義詞可以被所有用戶所使用
直接授予creat any synonym會怎麼樣呢？
SQL> grant create any synonym to hr;
授權成功。
SQL> create synonym hrt for hr.t;
同義詞已創建。
SQL> create synonym hrtt for sys.t;
同義詞已創建。
用戶可以為sys用戶創建私有同義詞了
當然也可以直接:
SQL> grant create synonym to hr with admin option;
授權成功。
SQL> create synonym hrtt for sys.t;
同義詞已創建。
with admin option讓我們想起了權限那部分知識，oracle中很多知識是連貫的。
假如我們對同義詞元對象進行ddl操作時，那又會怎麼樣呢？
SQL> SELECT * FROM T;
ID NAME BI
---------- ------ ----------
5
3 chao 3
SQL> select * from hrt;
ID NAME BI
---------- ------ ----------
5
3 chao 3
SQL> SELECT OBJECT_NAME, STATUS FROM ALL_OBJECTS WHERE OBJECT_NAME='HRT';
OBJECT_NAME STATUS
------------------------------ -------
HRT VALID
SQL> SELECT * FROM DBA_SYNONYMS WHERE SYNONYM_NAME='HRT';
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------------------------------------------------------------------------------------------------------------------------
HR HRT HR T
SQL> select * from t;
ID NAME BI NUM
---------- ------ ---------- ----------
5
3 chao 3
SQL> SELECT * FROM HRT;
ID NAME BI NUM
---------- ------ ---------- ----------
5
3 chao 3
SQL> commit;
提交完成。
SQL> SELECT * FROM HRT;
ID NAME BI NUM
---------- ------ ---------- ----------
5
3 chao 3
SQL> SELECT OBJECT_NAME, STATUS FROM ALL_OBJECTS WHERE OBJECT_NAME='HRT';
OBJECT_NAME STATUS
------------------------------ -------
HRT VALID
此時同義詞自動完成了編譯：
SQL> ALTER SYNONYM HRT COMPILE;
同義詞已變更。
oracle中很多我們查找的視圖都是同義詞，下面我們研究下v$version：
你可以用sql_trace的方法研究：
SQL> alter session set sql_trace=true;
會話已更改。
SQL> select * from v$versioN;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> ALTER SESSION SET SQL_TRACE=FALSE;
會話已更改。
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl3939/orcl3939/trace/orcl3939_ora_5705.trc
下面我們用10046事件來研究：
SQL> alter session set events '10046 trace name context forever ,level 1';
會話已更改。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> alter session set events '10046 trace name context off' ;
會話已更改。
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl3939/orcl3939/trace/orcl3939_ora_8736.trc
由於這種跟蹤文件很難看，用tkprof工具格式下：
[oracle@localhost trace]$ tkprof orcl3939_ora_8736.trc
output = text
TKPROF: Release 11.2.0.1.0 - Development on 星期五 6月 5 19:40:29 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
下面摘自text.prf文件：
TKPROF: Release 11.2.0.1.0 - Development on 星期五 6月 5 19:40:29 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Trace file: orcl3939_ora_8736.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
SQL ID: 9babjv8yq8ru3
Plan Hash: 0
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.04 0 21 0 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.04 0 21 0 2
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91
********************************************************************************
SQL ID: ga9j9xk5cy9s0
Plan Hash: 1697022209
select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece
from
idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piece#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.04 0 0 0 0
Fetch 3 0.00 0.00 0 8 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.05 0 8 0 2
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
2 TABLE ACCESS BY INDEX ROWID IDL_SB4$ (cr=6 pr=0 pw=0 time=0 us cost=3 size=18 card=1)
2 INDEX RANGE SCAN I_IDL_SB41 (cr=4 pr=0 pw=0 time=8 us cost=2 size=0 card=1)(object id 238)
********************************************************************************
SQL ID: cvn54b7yz0s8u
Plan Hash: 3246118364
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece
from
idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piece#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 5 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 5 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID IDL_UB1$ (cr=4 pr=0 pw=0 time=0 us cost=3 size=44 card=2)
1 INDEX RANGE SCAN I_IDL_UB11 (cr=3 pr=0 pw=0 time=0 us cost=2 size=0 card=2)(object id 235)
********************************************************************************
SQL ID: c6awqs517jpj0
Plan Hash: 1319326155
select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece
from
idl_char$ where obj#=:1 and part=:2 and version=:3 order by piece#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 5 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 5 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID IDL_CHAR$ (cr=4 pr=0 pw=0 time=0 us cost=3 size=20 card=1)
1 INDEX RANGE SCAN I_IDL_CHAR1 (cr=3 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 236)
********************************************************************************
SQL ID: 39m4sx9k63ba2
Plan Hash: 2317816222
select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece
from
idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piece#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 7 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.02 0 7 0 2
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
2 TABLE ACCESS BY INDEX ROWID IDL_UB2$ (cr=5 pr=0 pw=0 time=0 us cost=3 size=40 card=2)
2 INDEX RANGE SCAN I_IDL_UB21 (cr=3 pr=0 pw=0 time=2 us cost=2 size=0 card=2)(object id 237)
********************************************************************************
SQL ID: 3nkd3g3ju5ph1
Plan Hash: 2853959010
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1,
spare2
from
obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null
and linkname is null and subname is null
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3 0.00 0.02 0 0 0 0
Fetch 3 0.00 0.00 0 11 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.00 0.02 0 11 0 2
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=0 us cost=4 size=82 card=1)
0 INDEX RANGE SCAN I_OBJ2 (cr=3 pr=0 pw=0 time=0 us cost=3 size=0 card=1)(object id 37)
********************************************************************************
SQL ID: 1mjd9xp80vuqa
Plan Hash: 3023518864
select node,owner,name
from
syn$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID SYN$ (cr=3 pr=0 pw=0 time=0 us cost=2 size=27 card=1)
1 INDEX UNIQUE SCAN I_SYN1 (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 77)
********************************************************************************
SQL ID: 3ktacv9r56b51
Plan Hash: 4184428695
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#,
nvl(property,0),subname,type#,d_attrs
from
dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.05 0 0 0 0
Execute 2 0.00 0.03 0 0 0 0
Fetch 4 0.00 0.03 0 13 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.00 0.13 0 13 0 2
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT ORDER BY (cr=7 pr=0 pw=0 time=0 us cost=10 size=336 card=3)
1 NESTED LOOPS OUTER (cr=7 pr=0 pw=0 time=0 us cost=9 size=336 card=3)
1 TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=4 pr=0 pw=0 time=0 us cost=3 size=90 card=3)
1 INDEX RANGE SCAN I_DEPENDENCY1 (cr=3 pr=0 pw=0 time=0 us cost=2 size=0 card=3)(object id 106)
1 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=0 us cost=2 size=82 card=1)
1 INDEX RANGE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 36)
********************************************************************************
SQL ID: 8swypbbr0m372
Plan Hash: 893970548
select order#,columns,types
from
access$ where d_obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 6 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.00 0.00 0 6 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID ACCESS$ (cr=2 pr=0 pw=0 time=0 us cost=3 size=168 card=4)
0 INDEX RANGE SCAN I_ACCESS1 (cr=2 pr=0 pw=0 time=0 us cost=2 size=0 card=4)(object id 108)
********************************************************************************
SQL ID: g3wrkmxkxzhf2
Plan Hash: 749386351
select cols,audit$,textlength,intcols,property,flags,rowid
from
view$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.01 1 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.01 1 3 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID VIEW$ (cr=3 pr=1 pw=0 time=0 us cost=2 size=57 card=1)
1 INDEX UNIQUE SCAN I_VIEW1 (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 75)
********************************************************************************
SQL ID: 83taa7kaw59c1
Plan Hash: 3765558045
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,
nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,
scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,
rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,
nvl(spare3,0)
from
col$ where obj#=:1 order by intcol#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.01 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.02 0 3 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT ORDER BY (cr=3 pr=0 pw=0 time=0 us cost=3 size=708 card=12)
1 TABLE ACCESS CLUSTER COL$ (cr=3 pr=0 pw=0 time=0 us cost=2 size=708 card=12)
1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 3)
********************************************************************************
SQL ID: 96g93hntrzjtr
Plan Hash: 2239883476
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#,
sample_size, minimum, maximum, distcnt, lowval, hival, density, col#,
spare1, spare2, avgcln
from
hist_head$ where obj#=:1 and intcol#=:2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 6 0.00 0.00 0 0 0 0
Fetch 6 0.00 0.00 0 16 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13 0.00 0.00 0 16 0 4
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=3 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=0 us)(object id 426)
********************************************************************************
SQL ID: grwydz59pu6mc
Plan Hash: 3684871272
select text
from
view$ where rowid=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=0 us cost=1 size=15 card=1)
********************************************************************************
SQL ID: asvzxj61dc5vs
Plan Hash: 3028786551
select timestamp, flags
from
fixed_obj$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 0.00 0.00 0 7 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID FIXED_OBJ$ (cr=2 pr=0 pw=0 time=0 us cost=2 size=17 card=1)
0 INDEX UNIQUE SCAN I_FIXED_OBJ$_OBJ# (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 102)
********************************************************************************
SQL ID: 47u3kz5v39h84
Plan Hash: 0
select inst_id, banner
from
x$version
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 2)
********************************************************************************
SQL ID: 1mqr53r6qg3u6
Plan Hash: 0
select BANNER
from
GV$VERSION where inst_id = USERENV('Instance')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.02 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
SQL ID: 6aq34nj2zb2n7
Plan Hash: 2874733959
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
from
objauth$ where obj#=:1 and col# is not null group by privilege#, col#,
grantee# order by col#, grantee#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.01 1 2 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.01 1 2 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 SORT GROUP BY (cr=2 pr=1 pw=0 time=0 us cost=4 size=15 card=1)
0 TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=2 pr=1 pw=0 time=0 us cost=3 size=15 card=1)
0 INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=1 pw=0 time=0 us cost=2 size=0 card=1)(object id 62)
********************************************************************************
SQL ID: 2q93zsrvbdw48
Plan Hash: 2874733959
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))
from
objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by
grantee#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 5 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 5 0 3
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
3 SORT GROUP BY (cr=5 pr=0 pw=0 time=0 us cost=4 size=15 card=1)
3 TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=5 pr=0 pw=0 time=24 us cost=3 size=15 card=1)
3 INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 62)
********************************************************************************
SQL ID: 7nuw4xwrnuwxq
Plan Hash: 1720483994
select col#,intcol#,toid,version#,packed,intcols,intcol#s,flags, synobj#,
nvl(typidcol#, 0)
from
coltype$ where obj#=:1 order by intcol# desc
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.01 0 3 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY (cr=3 pr=0 pw=0 time=0 us cost=3 size=192 card=4)
0 TABLE ACCESS CLUSTER COLTYPE$ (cr=3 pr=0 pw=0 time=0 us cost=2 size=192 card=4)
1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 3)
********************************************************************************
SQL ID: 9rfqm06xmuwu0
Plan Hash: 832500465
select intcol#, toid, version#, intcols, intcol#s, flags, synobj#
from
subcoltype$ where obj#=:1 order by intcol# asc
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse &nbs
通過格式化後，看的是不是很清楚！
發現v$version的內容來自真實表x$version
SQL> select * from dba_synonyms where synonym_name='V$VERSION';
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------------------------------------------------------------------------------------------------------------------------
PUBLIC V$VERSION SYS V_$VERSION
而v$version是v_$version的公有同義詞
SQL> select text from dba_views where view_name='V_$VERSION';
TEXT
--------------------------------------------------------------------------------
select "BANNER" from v$version
v_$version是視圖
那麼v_$version和x$version是什麼關系呢?下面通過sql_trace來跟蹤：
QL> alter session set sql_trace=true;
會話已更改。
SQL> select * from v_$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> alter session set sql_trace=false;
下面是摘自trace文件:
Trace file /u01/app/oracle/diag/rdbms/orcl3939/orcl3939/trace/orcl3939_ora_10586.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: localhost.localdomain
Release: 2.6.18-194.el5
Version: #1 SMP Mon Mar 29 20:06:41 EDT 2010
Machine: i686
Instance name: orcl3939
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 10586, image:
[email protected] (TNS V1-V3)
*** 2015-06-05 20:43:31.754
*** SESSION ID:(191.5) 2015-06-05 20:43:31.754
*** CLIENT ID:() 2015-06-05 20:43:31.754
*** SERVICE NAME:(SYS$USERS) 2015-06-05 20:43:31.754
*** MODULE NAME:(
[email protected] (TNS V1-V3)) 2015-06-05 20:43:31.754
*** ACTION NAME:() 2015-06-05 20:43:31.754
=====================
PARSING IN CURSOR #3 len=32 dep=0 uid=0 oct=42 lid=0 tim=1433508211754004 hv=1569151342 ad='3ffed0' sqlid='4tk6t8tfsfqbf'
alter session set sql_trace=true
END OF STMT
EXEC #3:c=0,e=122,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1433508211753998
=====================
PARSING IN CURSOR #4 len=52 dep=0 uid=0 oct=47 lid=0 tim=1433508211754638 hv=1029988163 ad='322727f0' sqlid='9babjv8yq8ru3'
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
END OF STMT
PARSE #4:c=0,e=15,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1433508211754637
EXEC #4:c=0,e=132,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1433508211754829
*** 2015-06-05 20:43:42.545
CLOSE #3:c=0,e=6,dep=0,type=0,tim=1433508222545170
CLOSE #4:c=0,e=16,dep=0,type=3,tim=1433508222545254
=====================
PARSING IN CURSOR #4 len=37 dep=1 uid=0 oct=3 lid=0 tim=1433508222546045 hv=1398610540 ad='38d9993c' sqlid='grwydz59pu6mc'
select text from view$ where rowid=:1
END OF STMT
PARSE #4:c=0,e=36,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1433508222546043
EXEC #4:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1433508222546128
FETCH #4:c=0,e=17,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1433508222546162
STAT #4 id=1 cnt=1 pid=0 pos=1 obj=69 op='TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=0 us cost=1 size=15 card=1)'
CLOSE #4:c=999,e=30817,dep=1,type=0,tim=1433508222576993
=====================
PARSING IN CURSOR #3 len=24 dep=0 uid=0 oct=3 lid=0 tim=1433508222582455 hv=539536685 ad='3224b770' sqlid='1q65b3nh2jb9d'
select * from v_$version
END OF STMT
PARSE #3:c=2999,e=37171,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,plh=1078166315,tim=1433508222582454
EXEC #3:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1078166315,tim=1433508222582557
FETCH #3:c=0,e=14,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=1078166315,tim=1433508222582609
FETCH #3:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=4,dep=0,og=1,plh=1078166315,tim=1433508222583297
STAT #3 id=1 cnt=5 pid=0 pos=1 obj=0 op='FIXED TABLE FULL X$VERSION (cr=0
pr=0 pw=0 time=0 us cost=0 size=55 card=1)'
=====================
PARSING IN CURSOR #4 len=52 dep=0 uid=0 oct=47 lid=0 tim=1433508222583864 hv=1029988163 ad='322727f0' sqlid='9babjv8yq8ru3'
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
END OF STMT
PARSE #4:c=0,e=18,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1433508222583863
EXEC #4:c=0,e=126,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1433508222584053
*** 2015-06-05 20:44:08.035
CLOSE #3:c=0,e=42,dep=0,type=0,tim=1433508248035853
CLOSE #4:c=0,e=11,dep=0,type=3,tim=1433508248036049
=====================
PARSING IN CURSOR #3 len=33 dep=0 uid=0 oct=42 lid=0 tim=1433508248036292 hv=525901419 ad='3ffed0' sqlid='aam2chsgpj7mb'
alter session set sql_trace=false
END OF STMT
PARSE #3:c=0,e=218,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1433508248036291
EXEC #3:c=0,e=45,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1433508248036377
從上面可以看出v_$version基於x$version的視圖，但是oracle又不公布x$version的信息
GV$VERSION多用在RAC中
上面鑒於本人水平有限，很多內容沒有做過多解釋，如有疏漏之處，請讀者指正！萬分感謝！
