oracle10G 深入dual伪表(原)

今天群里谈DUAL,网上找了点说明，DUAL表就像ORACLE的宝贝女儿，一直隐藏而众多人士又想了解靠近，我决定试一下，发现10G的dual表比9i有变动，也更加合理，话不多说，老风格动手吧

— by anbob.com 转载请声明

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 7月 13 10:50:08 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

sys@ORCL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

sys@ORCL> select * from dual;

D
-
X

sys@ORCL> insert into dual values ('1');

1 row created.

sys@ORCL> insert into dual values ('2');

1 row created.

sys@ORCL> insert into dual values ('3');

1 row created.

sys@ORCL> commit;

Commit complete.

sys@ORCL> select (*) from dual;
select (*) from dual
*
ERROR at line 1:
ORA-00936: missing expression

sys@ORCL> select count(*) from dual;

COUNT(*)
----------
1

sys@ORCL> delete from dual;

1 row deleted.

sys@ORCL> select * from dual;

D
-
1

sys@ORCL> delete from dual;

1 row deleted.

sys@ORCL> select * from dual;

D
-
2

sys@ORCL> delete from dual;

1 row deleted.

sys@ORCL> select * from dual;

D
-
3

sys@ORCL> delete from dual;

1 row deleted.

sys@ORCL> select * from dual;

no rows selected

sys@ORCL> insert into dual values ('1');

1 row created.

sys@ORCL> insert into dual values ('2');

1 row created.

sys@ORCL> insert into dual values ('3');

1 row created.

sys@ORCL> insert into dual values ('4');

1 row created.

sys@ORCL> commit;

Commit complete.

sys@ORCL> select file_id,block_id from dba_extents where segment_name='DUAL';

FILE_ID   BLOCK_ID
---------- ----------
1       2081

sys@ORCL> oradebug dump datafile 1 block 2082
ORA-00073: command DUMP takes between 2 and 3 argument(s)
sys@ORCL> oradebug setmyid
ORA-00070: command setmyid is not valid
sys@ORCL> oradebug setmypid
Statement processed.
sys@ORCL> alter system dump datafile 1 block 2082
2  ;

System altered.

sys@ORCL> oradebug tracefile_name
/u01/app/oracle/admin/orcl/udump/orcl_ora_3730.trc
sys@ORCL> ! vi /u01/app/oracle/admin/orcl/udump/orcl_ora_3730.trc

/u01/app/oracle/admin/orcl/udump/orcl_ora_3730.trc内容

/u01/app/oracle/admin/orcl/udump/orcl_ora_3730.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      orazhang
Release:        2.6.18-53.el5xen
Version:        #1 SMP Wed Oct 10 17:06:12 EDT 2007
Machine:        i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 3730, image: oracle@orazhang (TNS V1-V3)
 
*** 2011-07-13 10:59:58.142
*** SERVICE NAME:(SYS$USERS) 2011-07-13 10:59:58.125
*** SESSION ID:(144.5) 2011-07-13 10:59:58.125
Start dump data blocks tsn: 0 file#: 1 minblk 2082 maxblk 2082
buffer tsn: 0 rdba: 0x00400822 (1/2082)
scn: 0x0000.4fd5cbd4 seq: 0x09 flg: 0x06 tail: 0xcbd40609
frmt: 0x02 chkval: 0x4d2b type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0D400C00 to 0x0D402C00
D400C00 0000A206 00400822 4FD5CBD4 06090000  [....".@....O....]
D400C10 00004D2B 00000001 00000102 4FD5CBB8  [+M.............O]
D400C20 00000000 00030002 00000000 0016000F  [................]
D400C30 00000547 03000137 002D0397 00002008  [G...7.....-.. ..]
D400C40 4FD5CBD4 00170011 000003FA 030002CF  [...O............]
D400C50 001B02C4 00008000 4FD5CBAF 00080100  [...........O....]
D400C60 0022FFFF 1F521F78 00001F5A 1F9B0008  [..".x.R.Z.......]
D400C70 1F911F96 1F871F8C 1F7D1F82 00001F78  [..........}.x...]
D400C80 00000000 00000000 00000000 00000000  [................]
        Repeat 500 times
D402BD0 00000000 0101012C 01012C34 012C3301  [....,...4,...3,.]
D402BE0 2C320101 31010101 0101013C 01013C33  [..2,...1<...3<..]
D402BF0 013C3201 3C310101 58010101 CBD40609  [.2<...1<...X....]
Block header dump:  0x00400822
 Object id on Block? Y
 seg/obj: 0x102  csc: 0x00.4fd5cbb8  itc: 2  flg: O  typ: 1 - DATA
D400C50 001B02C4 00008000 4FD5CBAF 00080100  [...........O....]
D400C60 0022FFFF 1F521F78 00001F5A 1F9B0008  [..".x.R.Z.......]
D400C70 1F911F96 1F871F8C 1F7D1F82 00001F78  [..........}.x...]
D400C80 00000000 00000000 00000000 00000000  [................]
        Repeat 500 times
D402BD0 00000000 0101012C 01012C34 012C3301  [....,...4,...3,.]
D402BE0 2C320101 31010101 0101013C 01013C33  [..2,...1<...3<..]
D402BF0 013C3201 3C310101 58010101 CBD40609  [.2<...1<...X....]
Block header dump:  0x00400822
 Object id on Block? Y
 seg/obj: 0x102  csc: 0x00.4fd5cbb8  itc: 2  flg: O  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000f.016.00000547  0x03000137.0397.2d  --U-    8  fsc 0x0000.4fd5cbd4
0x02   0x0011.017.000003fa  0x030002cf.02c4.1b  C---    0  scn 0x0000.4fd5cbaf
 
data_block_dump,data header at 0xd400c5c
===============
tsiz: 0x1fa0
hsiz: 0x22
pbl: 0x0d400c5c
bdba: 0x00400822
     76543210
flag=--------
ntab=1
nrow=8
frre=-1
fsbo=0x22
fseo=0x1f78
avsp=0x1f52
tosp=0x1f5a
0xe:pti[0]      nrow=8  offs=0
0x12:pri[0]     offs=0x1f9b
0x14:pri[1]     offs=0x1f96
0x16:pri[2]     offs=0x1f91
0x18:pri[3]     offs=0x1f8c
0x1a:pri[4]     offs=0x1f87
0x1c:pri[5]     offs=0x1f82
0x1e:pri[6]     offs=0x1f7d
0x20:pri[7]     offs=0x1f78
block_row_dump:
tab 0, row 0, @0x1f9b
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 1, @0x1f96
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 2, @0x1f91
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 3, @0x1f8c
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 4, @0x1f87
tl: 5 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 1]  31
tab 0, row 5, @0x1f82
tl: 5 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 1]  32
tab 0, row 6, @0x1f7d
tl: 5 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 1]  33
tab 0, row 7, @0x1f78
tl: 5 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 1]  34
end_of_block_dump
End dump data blocks tsn: 0 file#: 1 minblk 2082 maxblk 2082

sys@ORCL> select chr(to_number('31','xx')) from dual;

CH
--
1

sys@ORCL> select chr(to_number('32','xx')) from dual;

CH
--
2

sys@ORCL> select chr(to_number('33','xx')) from dual;

CH
--
3

sys@ORCL> select chr(to_number('34','xx')) from dual;

CH
--
4

sys@ORCL> select * from dual;

D
-
1

sys@ORCL> truncate table dual;

Table truncated.

sys@ORCL> select * from dual;

no rows selected

sys@ORCL> select sysdate from dual;

SYSDATE
-------------------
2011-07-13 11:10:39

sys@ORCL> set autot trace
sys@ORCL> select sysdate from dual;

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
0  consistent gets
0  physical reads
0  redo size
415  bytes sent via SQL*Net to client
385  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

sys@ORCL> insert into dual values ('4');

1 row created.

Execution Plan
----------------------------------------------------------

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | INSERT STATEMENT |      |     1 |     2 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

Statistics
----------------------------------------------------------
1  recursive calls
6  db block gets
4  consistent gets
0  physical reads
548  redo size
922  bytes sent via SQL*Net to client
944  bytes received via SQL*Net from client
6  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
1  rows processed

sys@ORCL> commit;

Commit complete.

sys@ORCL> select * from dual;

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
3  consistent gets
0  physical reads
0  redo size
407  bytes sent via SQL*Net to client
385  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

sys@ORCL> select sysdate from dual;

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
0  consistent gets
0  physical reads
0  redo size
415  bytes sent via SQL*Net to client
385  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

note:
dual是sys所有的一个表，然后创建的同义词给其它用户使用，所含一列，就像一个索引组织表，是按插入的顺序存方，无论是select还是delete，隐含加入了rownum=1,truncate可能截断整个表，无数据但sysdate from dual还可以用区别于老版本，sysdate from dual 的执行计划consistent gets也有别于老版本，只是查询表内的数据还是总会有3个逻辑读