OFFLINE不同选项的测试（NORMAL,TEMPORARY,IMMEDIATE）（一）

最新推荐文章于 2021-08-05 11:43:16 发布

太阳上有风

最新推荐文章于 2021-08-05 11:43:16 发布

阅读量701

点赞数

分类专栏： Oracle剖析 Oracle理论文章标签：测试 system insert archive sql table

本文链接：https://blog.csdn.net/songxixi/article/details/7010978

版权

Oracle剖析同时被 2 个专栏收录

24 篇文章 1 订阅

订阅专栏

Oracle理论

12 篇文章 0 订阅

订阅专栏

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     2
Next log sequence to archive   4
Current log sequence           4
SQL> alter system switch logfile;

System altered.

SQL> create table t(scn int) tablespace users;

Table created.

SQL> insert into t select dbms_flashback.get_system_change_Number from dba_objects where rownum<=1000;

1000 rows created.

SQL> commit;

Commit complete.

SQL> select max(scn),min(scn) from t;

MAX(SCN) MIN(SCN)
---------- ----------
658591 658591

SQL> select change# from v$instance;
select change# from v$instance
*
ERROR at line 1:
ORA-00904: "CHANGE#": invalid identifier

SQL> insert into t select dbms_flashback.get_system_change_number from dba_objects where rownum<1000;

999 rows created.

SQL> insert into t select dbms_flashback.get_system_change_number from dba_objects where rownum<2;

1 row created.

SQL> select distinct scn,count(*) from t group by scn;

       SCN   COUNT(*)
---------- ----------
    659277          1
    658591       1000
    659275        999

SQL> insert into t select dbms_flashback.get_system_change_number from t;

2000 rows created.

SQL> select distinct scn,count(*) from t group by scn;

       SCN   COUNT(*)
---------- ----------
    659277          1
    658591       1000
    659275        999
    659295        469
    659293       1531

SQL> commit;

Commit complete.

SQL> select group#,bytes,archived,first_change#,sequence#,status from v$log;

    GROUP#      BYTES ARC FIRST_CHANGE# SEQUENCE# STATUS
---------- ---------- --- ------------- ---------- ----------------
         1   52428800 YES        637009          4 INACTIVE
         2   52428800 NO         658540          5 CURRENT
         3   52428800 YES        614226          3 INACTIVE

SQL>
SQL>
SQL> alter system checkpoint;

System altered.

SQL> select substr(name,1,40) dname,checkpoint_change#,last_change#,offline_change#,status from v$datafile;

DNAME
--------------------------------------------------------------------------------
CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# STATUS
------------------ ------------ --------------- -------
/u01/oradata/orac10g21/system01.dbf
659333 602446 SYSTEM

/u01/oradata/orac10g21/undotbs01.dbf
659333 602446 ONLINE

/u01/oradata/orac10g21/sysaux01.dbf
659333 602446 ONLINE

/u01/oradata/orac10g21/example01.dbf
659333 602446 ONLINE

/u01/oradata/orac10g21/testing_lmt.dbf
659333 0 ONLINE

/u01/oradata/orac10g21/tbs_test_02.dbf
659333 0 ONLINE

/u01/oradata/orac10g21/tbs_test_03.dbf
659333 0 ONLINE

9 rows selected.

SQL> set linesize 2000
SQL> select substr(name,1,40) dname,checkpoint_change#,last_change#,offline_change#,status from v$datafile;

DNAME                                                                            CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# STATUS
-------------------------------------------------------------------------------- ------------------ ------------ --------------- -------
/u01/oradata/orac10g21/system01.dbf                                                          659333                       602446 SYSTEM
/u01/oradata/orac10g21/undotbs01.dbf                                                         659333                       602446 ONLINE
/u01/oradata/orac10g21/sysaux01.dbf                                                          659333                       602446 ONLINE
/u01/oradata/orac10g21/users01.dbf                                                           659333                       602446 ONLINE
/u01/oradata/orac10g21/example01.dbf                                                         659333                       602446 ONLINE
/u01/oradata/orac10g21/testing_lmt.dbf                                                       659333                            0 ONLINE
/u01/oradata/orac10g21/tbs_test_01.dbf                                                       659333                            0 ONLINE
/u01/oradata/orac10g21/tbs_test_02.dbf                                                       659333                            0 ONLINE
/u01/oradata/orac10g21/tbs_test_03.dbf                                                       659333                            0 ONLINE

9 rows selected.

SQL> select name from v$datafile;

NAME
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/oradata/orac10g21/system01.dbf
/u01/oradata/orac10g21/undotbs01.dbf
/u01/oradata/orac10g21/sysaux01.dbf
/u01/oradata/orac10g21/users01.dbf
/u01/oradata/orac10g21/example01.dbf
/u01/oradata/orac10g21/testing_lmt.dbf
/u01/oradata/orac10g21/tbs_test_01.dbf
/u01/oradata/orac10g21/tbs_test_02.dbf
/u01/oradata/orac10g21/tbs_test_03.dbf

9 rows selected.

SQL> alter tablespace users add datafile '/u01/oradata/orac10g21/users02.dbf' size 10m;

Tablespace altered.

SQL> select substr(name,1,40) dname,checkpoint_change#,last_change#,offline_change#,status from v$datafile;

10 rows selected.

SQL>