oracle insert commit 过程探秘

       在网上看到很多人介绍oracle commit的，但是很多都是纯文字一类的，不是很直观。所以，我今天就做一个实验给大家演示下。

    环境如下：

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat May 31 10:50:58 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size                  2253784 bytes
Variable Size            1006636072 bytes
Database Buffers          637534208 bytes
Redo Buffers                7094272 bytes
Database mounted.
Database opened.
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/product/11.1.0/db_1/dbs/arch
Oldest online log sequence     13
Next log sequence to archive   15
Current log sequence           15
SQL> alter system switch logfile;

System altered.

SQL> conn sec/sec
Connected.
SQL> 
SQL> drop table my_insert;

Table dropped.

      什么ORACLE版本啊，都能看到。就不那么具体说了。我这个实验环境最好是新库数据量少点，就你一个人操作最好。测试表最好没有数据。

    时间点一：

SQL> drop table my_insert;

Table dropped.

SQL> create table my_insert (id number,area_code varchar2(40)) tablespace users;

Table created.

SQL> insert into my_insert values(1,'sicheng53');

1 row created.

       我们新建一个表，insert一条新数据，没有commit,这个时候数据应该是在内存里面，磁盘上面应该没有记录的。我用strings来验证

[oracle@localhost ~]$ cd /u01/app/oracle/oradata/PROD/
[oracle@localhost PROD]$ ll
total 1949228
-rw-r----- 1 oracle oinstall   9748480 May 31 10:57 control01.ctl
-rw-r----- 1 oracle oinstall   9748480 May 31 10:57 control02.ctl
-rw-r----- 1 oracle oinstall 362422272 May 31 10:55 example01.dbf
-rw-r----- 1 oracle oinstall  52429312 May 31 10:57 redo01.log
-rw-r----- 1 oracle oinstall  52429312 May 31 10:55 redo02.log
-rw-r----- 1 oracle oinstall  52429312 May 31 10:56 redo03.log
-rw-r----- 1 oracle oinstall 534781952 May 31 10:55 sysaux01.dbf
-rw-r----- 1 oracle oinstall 807411712 May 31 10:55 system01.dbf
-rw-r----- 1 oracle oinstall  20979712 May 31 10:55 temp01.dbf
-rw-r----- 1 oracle oinstall  52436992 May 31 10:55 undotbs01.dbf
-rw-r----- 1 oracle oinstall  57679872 May 31 10:55 users01.dbf
[oracle@localhost PROD]$ strings redo01.log | grep sicheng53
[oracle@localhost PROD]$
[oracle@localhost PROD]$ strings redo02.log | grep sicheng53
[oracle@localhost PROD]$
[oracle@localhost PROD]$ strings redo03.log | grep sicheng53
[oracle@localhost PROD]$
[oracle@localhost PROD]$ strings users01.dbf | grep sicheng53
[oracle@localhost PROD]$

    可以看到,online redo和相应数据文件都没有，为什么只查询了user01.dbf呢。。你自己看见表语句吧。

   

   时间点二：

SQL> commit;

Commit complete.

SQL> select GROUP#,STATUS from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE

SQL> 
SQL> col MEMBER format a50
SQL> select GROUP#,MEMBER from v$logfile;

    GROUP# MEMBER
---------- --------------------------------------------------
         3 /u01/app/oracle/oradata/PROD/redo03.log
         2 /u01/app/oracle/oradata/PROD/redo02.log
         1 /u01/app/oracle/oradata/PROD/redo01.log

SQL> 

     接前面的insert ,之间commit成功，这个时候应该从内存写到online redolog里面去了。查询当前的在线日志

[oracle@localhost PROD]$ strings redo01.log | grep sicheng53
sicheng53

    时间点三：

SQL> alter system checkpoint;

System altered.

  手工运行checkpoint事件，这个时候数据应该写入数据文件了，快去看吧

[oracle@localhost PROD]$ strings users01.dbf | grep sicheng53
        sicheng53<
[oracle@localhost PROD]$ 
[oracle@localhost PROD]$ 

  时间点四：

SQL> alter system switch logfile;

System altered.

SQL> 
SQL>                                         
SQL> select GROUP#,STATUS from v$log;

    GROUP# STATUS
---------- ----------------
         1 ACTIVE
         2 CURRENT
         3 INACTIVE

    大家记得吧，数据库是归档模式的啊。。去看看归档文件里面吧

[oracle@localhost dbs]$ cd /u01/app/oracle/product/11.1.0/db_1/dbs/arch

[oracle@localhost arch]$ ls
1_15_834821443.dbf  1_16_834821443.dbf
[oracle@localhost arch]$ 
[oracle@localhost arch]$ 
[oracle@localhost arch]$ strings 1_15_834821443.dbf | grep sicheng53
[oracle@localhost arch]$ 
[oracle@localhost arch]$ strings 1_16_834821443.dbf| grep sicheng53
sicheng53
[oracle@localhost arch]$ ll

  通过上面的演示，很直观的看到了insert一条数据以后的走向了。有错误的地方，希望大家指正。

  版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!