www.eygle.com

---

March 5, 2009

Oracle 11g 的INTERVAL分区不支持IOT表

作者:eygle

出处:http://blog.eygle.com

今天测试发现Oracle Database 11g中的INTERVAL分区不支持IOT表：

SQL> CREATE TABLE iship (
  2  aindex  NUMBER,
  3  smmsih  VARCHAR2(20),
  4  idatet  DATE,
  5  odatet  DATE,
  6  CONSTRAINT pk_iship PRIMARY KEY(aindex,smmsih,idatet,odatet))
  7  ORGANIZATION INDEX
  8  COMPRESS 2
  9  PARTITION BY RANGE (idatet)
10  INTERVAL (NUMTODSINTERVAL(1,'day'))
11  (
12  PARTITION day090301 VALUES LESS THAN (TO_DATE('2009-03-02', 'YYYY-MM-DD'))
13  )
14  ;
(
*
ERROR at line 11:
ORA-25198: only range, list, and hash partitioning are supported for index-organized table

提示表明，IOT表仅支持Range、List、Hash分区，而且IOT表不支持复合分区。
INTERVAL分区的限制还包括：
Index Organized tables (IOTs) are not supported by interval partitioning.
Domain index cannot be created on interval partitioned tables.
Only one partitioning key column can be set for the interval partition and it must be either a DATE or NUMBER data type.

INTERVAL的实现对与维护有很大的帮助，在IOT的RANGE分区上应当没有多大的难度，期望在将来版本中，Oracle能够增加这一支持。

Posted by eygle at 4:55 PM | Comments (0)

---

SQL*Plus中 Set timing on的时间显示单位

作者:eygle

出处:http://blog.eygle.com

在SQL*PLUS里，可以通过set timing on显示执行SQL操作的时间。如：

SQL> set timing on
SQL> select count(*) from dba_objects;

COUNT(*)
----------
15923

Elapsed: 00:00:01.02

有人问到这个时间的单位：
以上数字输出分别是：Hours:Minutes:Seconds.Milliseconds

Milliseconds 即 1 / 1000 秒。

注意有的版本，Seconds 秒位进位到分钟未减掉60，是Bug。

Posted by eygle at 4:05 PM | Comments (0)

---

Oracle DataGuard 常用维护命令参考手册

作者:eygle

出处:http://blog.eygle.com

Start Standby Database

startup nomount
alter database mount standby database;
alter database recover managed standby database disconnect;

Disable/Enable archive log destinations

alter system set log_archive_dest_state_2 = 'defer';
alter system set log_archive_dest_state_2 = 'enable';

To remove a delay from a standby

alter database recover managed standby database cancel;

alter database recover managed standby database nodelay disconnect;

Stop and Start of Logical standby apply

alter database stop logical standby apply;
alter database start logical standby apply;

Physical Standby switchover:
In Primary Database：

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;

In standby Database:

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
SHUTDOWN IMMEDIATE;
STARTUP;

In Primary Database:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

If the primary Database is down,we can use fllowing step to active standby database:

Alter DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
Alter DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
SHUTDOWN IMMEDIATE;
STARTUP;

Register missing archive log file
Find archive log gap by query:

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

register using:

ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

Posted by eygle at 10:52 AM | Comments (0)

---