关于dual 表的小技巧

一. DUAL表的来历：

dual 表属于sys

-- Create table
create table DUAL
(
DUMMY VARCHAR2(1)
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 16K
minextents 1
maxextents unlimited
);
-- Grant/Revoke object privileges
grant select on DUAL to PUBLIC with grant option;


SQL> desc dual

名称 是否为空? 类型
-------------------------------
DUMMY       VARCHAR2(1)

SQL> select * from dual;
dummy
--------
X
SQL> INSERT INTO DUAL VALUES('1');

已创建 1 行。

SQL> SELECT COUNT(*) FROM DUAL;

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

这就是dual 的精妙之处.


二.如果drop 掉 dual 会怎样呢？

SQL> drop table dual;

表已删除。

SQL>
SQL> shutdown immediate;

数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 75498876 bytes
Database Buffers 83886080 bytes
Redo Buffers 7139328 bytes
数据库装载完毕。
ORA-01092: ORACLE 实例终止。强制断开连接

我们察看alert_sid.log :

Mon Oct 02 14:00:56 2006
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default values:
processes = 150
__shared_pool_size = 67108864
__large_pool_size = 4194304
__java_pool_size = 4194304
__streams_pool_size = 0
spfile = F:ORACLEPRODUCT10.2.0DB_1DBSSPFILESONG.ORA
sga_target = 167772160
control_files = F:ORACLEPRODUCT10.2.0ORADATASONGCONTROL01.CTL, F:ORACLEPRODUCT10.2.0ORADATASONGCONTROL02.CTL, F:ORACLEPRODUCT10.2.0ORADATASONGCONTROL03.CTL
db_block_size = 8192
__db_cache_size = 83886080
compatible = 10.2.0.1.0
db_file_multiblock_read_count= 16
db_recovery_file_dest = F:oracleproduct10.2.0/flash_recovery_area
db_recovery_file_dest_size= 536870912
undo_management = AUTO
undo_tablespace = UNDOTBS1
remote_login_passwordfile= EXCLUSIVE
db_domain =
dispatchers = (PROTOCOL=TCP) (SERVICE=songXDB)
job_queue_processes = 10
audit_file_dest = F:ORACLEPRODUCT10.2.0ADMINSONGADUMP
background_dump_dest = F:ORACLEPRODUCT10.2.0ADMINSONGBDUMP
user_dump_dest = F:ORACLEPRODUCT10.2.0ADMINSONGUDUMP
core_dump_dest = F:ORACLEPRODUCT10.2.0ADMINSONGCDUMP
db_name = song
open_cursors = 300
pga_aggregate_target = 16777216
PMON started with pid=2, OS id=3020
PSP0 started with pid=3, OS id=716
MMAN started with pid=4, OS id=1640
DBW0 started with pid=5, OS id=3540
LGWR started with pid=6, OS id=2780
CKPT started with pid=7, OS id=2364
SMON started with pid=8, OS id=3256
RECO started with pid=9, OS id=3456
CJQ0 started with pid=10, OS id=3800
MMON started with pid=11, OS id=244
Mon Oct 02 14:00:57 2006
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=12, OS id=4004
Mon Oct 02 14:00:57 2006
starting up 1 shared server(s) ...
Mon Oct 02 14:00:57 2006
ALTER DATABASE MOUNT
Mon Oct 02 14:01:02 2006
Setting recovery target incarnation to 2
Mon Oct 02 14:01:02 2006
Successful mount of redo thread 1, with mount id 533560409
Mon Oct 02 14:01:02 2006
Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Mon Oct 02 14:01:03 2006
ALTER DATABASE OPEN
Mon Oct 02 14:01:04 2006
Beginning crash recovery of 1 threads
parallel recovery started with 2 processes
Mon Oct 02 14:01:05 2006
Started redo scan
Mon Oct 02 14:01:08 2006
Completed redo scan
48 redo blocks read, 25 data blocks need recovery
Mon Oct 02 14:01:08 2006
Started redo application at
Thread 1: logseq 15, block 27787
Mon Oct 02 14:01:09 2006
Recovery of Online Redo Log: Thread 1 Group 2 Seq 15 Reading mem 0
Mem# 0 errs 0: F:ORACLEPRODUCT10.2.0ORADATASONGREDO02.LOG
Mon Oct 02 14:01:09 2006
Completed redo application
Mon Oct 02 14:01:09 2006
Completed crash recovery at
Thread 1: logseq 15, block 27835, scn 1000842
25 data blocks read, 25 data blocks written, 48 redo blocks read
Mon Oct 02 14:01:14 2006
Thread 1 advanced to log sequence 16
Thread 1 opened at log sequence 16
Current log# 3 seq# 16 mem# 0: F:ORACLEPRODUCT10.2.0ORADATASONGREDO03.LOG
Successful open of redo thread 1
Mon Oct 02 14:01:15 2006
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Oct 02 14:01:15 2006
SMON: enabling cache recovery
Mon Oct 02 14:01:18 2006
Successfully onlined Undo Tablespace 1.
Mon Oct 02 14:01:18 2006
SMON: enabling tx recovery
Mon Oct 02 14:01:20 2006
Database Characterset is ZHS16GBK
Mon Oct 02 14:01:23 2006
Errors in file f:oracleproduct10.2.0adminsongudumpsong_ora_2468.trc:
ORA-01775: 同义词的循环链

Error 1775 happened during db open, shutting down database
USER: terminating instance due to error 1775
Mon Oct 02 14:01:24 2006
Errors in file f:oracleproduct10.2.0adminsongbdumpsong_lgwr_2780.trc:
ORA-01775: looping chain of synonyms

Mon Oct 02 14:01:24 2006
Errors in file f:oracleproduct10.2.0adminsongbdumpsong_psp0_716.trc:
ORA-01775: looping chain of synonyms

Mon Oct 02 14:01:24 2006
Errors in file f:oracleproduct10.2.0adminsongbdumpsong_mman_1640.trc:
ORA-01775: looping chain of synonyms

Mon Oct 02 14:01:25 2006
Errors in file f:oracleproduct10.2.0adminsongbdumpsong_dbw0_3540.trc:
ORA-01775: looping chain of synonyms

Mon Oct 02 14:01:25 2006
Errors in file f:oracleproduct10.2.0adminsongbdumpsong_pmon_3020.trc:
ORA-01775: looping chain of synonyms

Mon Oct 02 14:01:25 2006
Errors in file f:oracleproduct10.2.0adminsongbdumpsong_reco_3456.trc:
ORA-01775: looping chain of synonyms

Mon Oct 02 14:01:25 2006
Errors in file f:oracleproduct10.2.0adminsongbdumpsong_ckpt_2364.trc:
ORA-01775: looping chain of synonyms

Instance terminated by USER, pid = 2468
ORA-1092 signalled during: ALTER DATABASE OPEN...


ORA-01775 looping chain of synonyms

Cause: Through a series of CREATE synonym statements, a synonym was defined that referred to itself. For example, the following definitions are circular:

CREATE SYNONYM s1 for s2 CREATE SYNONYM s2 for s3 CREATE SYNONYM s3 for s1
Action: Change one synonym definition so that it applies to a base table or view and retry the operation.

处理方法:

SQL> shutdown immediate;
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 75498876 bytes
Database Buffers 83886080 bytes
Redo Buffers 7139328 bytes
数据库装载完毕。
SQL> create pfile='f:pfilesong.ora' from spfile;

文件已创建。

在pfile 中加入参数:
replication_dependency_tracking= false
这个参数如果为true ,将会对读/写相关性进行跟踪.

SQL> startup pfile=f:pfilesong.ora;
ORACLE 例程已经启动。

Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 75498876 bytes
Database Buffers 83886080 bytes
Redo Buffers 7139328 bytes
数据库装载完毕。
数据库已经打开。
SQL>

SQL> create table DUAL
2 (
3 DUMMY VARCHAR2(1)
4 )
5 tablespace SYSTEM
6 pctfree 10
7 pctused 40
8 initrans 1
9 maxtrans 255
10 storage
11 (
12 initial 16K
13 minextents 1
14 maxextents unlimited
15 );

表已创建。


SQL> grant select on DUAL to PUBLIC with grant option;

授权成功。

SQL> insert into dual values('X');

已创建 1 行。

SQL> commit ;

提交完成。

SQL> startup force

ORACLE 例程已经启动。

Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 75498876 bytes
Database Buffers 83886080 bytes
Redo Buffers 7139328 bytes
数据库装载完毕。
数据库已经打开。



三.假如程序出现了大量的dual 的sql ,导致dual 成为hot block ,可以进行置换。

create view dual_view as select dummy from x$dual;
grant select on dual_view to public;
rename dual to dual_table;
rename dual_view to dual;

Trackback URL: http://blog.itpub.net//trackback.php?id=216328