创建表使用nologging为何我的redo log还是疯狂增长呢？

显示全部楼层 · 2005-10-30 17:05:33

我加了两个日志组，然后执行几个create table ***as select *
from ***nologging
请看日志：

Fri Nov 24 14:49:35 2006
Thread 1 cannot allocate new log, sequence 37
Checkpoint not complete
Current log# 2 seq# 36 mem# 0: /market/oradata/market/redo02.log
Thread 1 advanced to log sequence 37
Current log# 3 seq# 37 mem# 0: /market/oradata/market/redo03.log
Fri Nov 24 14:51:07 2006
Thread 1 advanced to log sequence 38
Current log# 1 seq# 38 mem# 0: /market/oradata/market/redo01.log
Fri Nov 24 14:56:59 2006
/* OracleOEM */ ALTER DATABASE ADD LOGFILE GROUP 4 ('/market/oradata/market/log4
.ora') SIZE 100M
Fri Nov 24 14:57:04 2006
Completed: /* OracleOEM */ ALTER DATABASE ADD LOGFILE GROUP 4 ('/market/oradata/
market/log4.ora') SIZE 100M
Fri Nov 24 14:57:18 2006
/* OracleOEM */ ALTER DATABASE ADD LOGFILE GROUP 5 ('/market/oradata/market/log5
.ora') SIZE 100M
Fri Nov 24 14:57:23 2006
Completed: /* OracleOEM */ ALTER DATABASE ADD LOGFILE GROUP 5 ('/market/oradata/
market/log5.ora') SIZE 100M
Fri Nov 24 15:00:28 2006
Thread 1 advanced to log sequence 39
Current log# 4 seq# 39 mem# 0: /market/oradata/market/log4.ora
Fri Nov 24 15:06:02 2006
Thread 1 advanced to log sequence 40
Current log# 5 seq# 40 mem# 0: /market/oradata/market/log5.ora
Fri Nov 24 15:08:44 2006
Thread 1 advanced to log sequence 41
Current log# 2 seq# 41 mem# 0: /market/oradata/market/redo02.log
Fri Nov 24 15:09:57 2006
Thread 1 advanced to log sequence 42
Current log# 3 seq# 42 mem# 0: /market/oradata/market/redo03.log
Fri Nov 24 15:11:16 2006
Thread 1 advanced to log sequence 43
Current log# 1 seq# 43 mem# 0: /market/oradata/market/redo01.log
Fri Nov 24 15:12:35 2006
Thread 1 cannot allocate new log, sequence 44
Checkpoint not complete
Current log# 1 seq# 43 mem# 0: /market/oradata/market/redo01.log
Thread 1 advanced to log sequence 44
Current log# 4 seq# 44 mem# 0: /market/oradata/market/log4.ora
Fri Nov 24 15:15:16 2006
Thread 1 advanced to log sequence 45
Current log# 5 seq# 45 mem# 0: /market/oradata/market/log5.ora
Fri Nov 24 15:17:53 2006
Thread 1 advanced to log sequence 46
Current log# 2 seq# 46 mem# 0: /market/oradata/market/redo02.log
Fri Nov 24 15:18:03 2006
Expanded controlfile section 11 from 28 to 56 records
Requested to grow by 28 records; added 1 blocks of records

千问 · 2005-10-30 17:05:33

应该是：create table *** nologging as select * from ***

千问 · 2005-10-30 17:05:33

DDL是不会产生太多日志的啊。你看看是不是你系统本身就很忙。不是因为这个语句，还有别的事务啊。

千问 · 2005-10-30 17:05:33

建立的新库，只有我用，从另外的数据库通过dblink创建表。
下次试试yangtingkun版主说的
看来语法没有用对

千问 · 2005-10-30 17:05:33

increase redo log size to 300 M