Chinaunix首页 | 论坛 | 博客
  • 博客访问: 283472
  • 博文数量: 27
  • 博客积分: 368
  • 博客等级: 一等列兵
  • 技术积分: 491
  • 用 户 组: 普通用户
  • 注册时间: 2012-05-09 21:35
个人简介

再出发..

文章分类

全部博文(27)

文章存档

2018年(1)

2014年(6)

2013年(5)

2012年(15)

我的朋友

分类: Oracle

2012-06-03 19:20:58

永久表空间
    大文件:表空间下只能一个数据文件     最大:             4G*db_block_size
    小文件:表空间下最多1022个数据文件     单个文件最大:    4M*db_block_size
    
================================创建大文件表空间=============================================

SYS@iniesta>> create bigfile tablespace test datafile '/u01/oradata/iniesta/test01.dbf' size 100m;

Tablespace created.

SYS@iniesta>> alter tablespace test add datafile '/u01/oradata/iniesta/test02.dbf' size 100m;
alter tablespace test add datafile '/u01/oradata/iniesta/test02.dbf' size 100m
*
ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace

=======================================================================================================    
    
    read only/read write
    
只读的表空间下面的所有对象都不能DML,TRUNCATE..但是可以DROP(表定义存在数据字典(SYSTEM TBS)中)    
SYSTEM,UNDO,TEMP 表空间不能READ ONLY    
    
    
    
    online/offline
    
表空间的离线上线主要用于数据文件的迁移
        alter database rename file 'xx' to 'xx'
        alter tablespace rename datafile 'xx' to 'xx'

MISSING:tablespace offline 或者 read only 后做control file backup,生成的控制文件中并不包含该数据文件,但是在数据字典
        中是存在该数据文件信息的,所以在v$datafile中就会增加一条该数据文件的信息:missing...
        alter database rename ‘missing...’ to ‘xx’ 解决

===============================表空间的离线和数据文件的离线=================================

表空间的离线会触发针对该表空间的检查点,并把当前的SCN写入v$datafile.CHECKPOINT_CHANGE# 和 v$datafile.LAST_CHANGE#
重新上线时会再次触发一个针对该表空间的检查点,并把当前SCN写到v$datafile.CHECKPOINT_CHANGE# 和  v$datafile_header.CHECKPOINT_CHANGE#
步骤:
1,offline     发生检查点,将当前SCN写入v$datafile.LAST_CHANGE#,v$datafile.CHECKPOINT_CHANGE#
2,online      发生检查点,再次将当前SCN写入v$datafile.CHECKPOINT_CHANGE#, 以及v$datafile_header.CHECKPOINT_CHANGE#


SYS@iniesta>> alter tablespace test offline;

Tablespace altered.

SYS@iniesta>> select name,checkpoint_change#,last_change# from v$datafile;

NAME                                     CHECKPOINT_CHANGE# LAST_CHANGE#
---------------------------------------- ------------------ ------------
/u01/oradata/iniesta/system01.dbf                   2194767
/u01/oradata/iniesta/undotbs01.dbf                  2194767
/u01/oradata/iniesta/sysaux01.dbf                   2194767
/u01/oradata/iniesta/users01.dbf                    2194767
/u01/oradata/iniesta/users99.dbf                    2194767
/u01/oradata/iniesta/test01.dbf                     2194784      2194784


数据文件的离线不会产生检查点,只是把当前的SCN写入 v$datafile.LAST_CHANGE#
在数据文件重新上线时需要使用日志文件或者归档日志进行介质恢复,如果当前数据库不处于归档模式,
那么在数据文件离线时会报错,因为日志文件可能被覆盖导致重现上线时介质恢复失败,此时需要
在sql语句中指定 for drop强行离线,至于上线时介质恢复能否成功则取决于日志是否被覆盖。
步骤:
1,offline           不发生检查点 只把当前SCN写入 v$datafile.LAST_CHANGE#
2,media recovery    重做v$datafile.CHECKPOINT_CHANGE# 与 v$datafile.LAST_CHANGE# 之间的redo并发生检查点,完成后再把当前SCN写入 v$datafile.LAST_CHANGE#,v$datafile.CHECKPOINT_CHANGE#
3,online          发生检查点,再次将当前SCN写入 v$datafile.CHECKPOINT_CHANGE#

alter database 和 alter tablespace 的区别
alter tablespace 只能使表空间下的所有数据文件离线,如果需要表空间中多个数据文件中的一个离线,
只能使用 alter database,非归档模式下强制离线必须使用alter database datafile .. offline for drop

SYS@iniesta>> alter database datafile 6 offline;

Database altered.

SYS@iniesta>> select name,checkpoint_change#,last_change# from v$datafile;

NAME                                     CHECKPOINT_CHANGE# LAST_CHANGE#
---------------------------------------- ------------------ ------------
/u01/oradata/iniesta/system01.dbf                   2195741
/u01/oradata/iniesta/undotbs01.dbf                  2195741
/u01/oradata/iniesta/sysaux01.dbf                   2195741
/u01/oradata/iniesta/users01.dbf                    2195741
/u01/oradata/iniesta/users99.dbf                    2195741
/u01/oradata/iniesta/test01.dbf                     2195741      2195753

数据文件离线后的恢复:
        
SYS@iniesta>> alter database datafile '/u01/oradata/iniesta/test01.dbf' offline;

Database altered.

SYS@iniesta>> alter database datafile '/u01/oradata/iniesta/test01.dbf' online;
alter database datafile '/u01/oradata/iniesta/test01.dbf' online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/u01/oradata/iniesta/test01.dbf'


SYS@iniesta>> recover datafile '/u01/oradata/iniesta/test01.dbf'
Media recovery complete.
SYS@iniesta>> alter database datafile '/u01/oradata/iniesta/test01.dbf' online;

Database altered.

=======================================================================================================

数据文件自动扩容:


SYS@iniesta>> alter database datafile '/u01/oradata/iniesta/test02.dbf' autoextend on next 10M maxsize 150M;

Database altered.

SYS@iniesta>> alter database datafile '/u01/oradata/iniesta/test02.dbf' resize 110M;

Database altered.


=========================================删除表空间==============================

删除表空间需要先删除表空间内的对象,注意与其他表空间对象的外键约束关系

SYS@iniesta>> select c.table_name,constraint_name,tablespace_name
from dba_constraints c,dba_tables t where c.table_name in ('T11','T12') and c.table_name=t.table_name;

TABLE_NAME           CONSTRAINT_NAME TABLESPACE_NAME
-------------------- --------------- ---------------
T12                  T12_FK          SYSTEM
T11                  T11_PK          TEST


SYS@iniesta>> drop tablespace test;
drop tablespace test
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option


SYS@iniesta>> drop tablespace test including contents and datafiles;
drop tablespace test including contents and datafiles
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys


SYS@iniesta>> drop tablespace test including contents and datafiles cascade constraints;

Tablespace dropped.


SYS@iniesta>> select table_name,constraint_name from dba_constraints where table_name in ('T11','T12');

no rows selected

======================================================================================================


查看系统默认的永久表空间,临时表空间,及表空间的默认类型(大文件/小文件)
SYS@iniesta>> desc database_properties
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 PROPERTY_NAME                                         NOT NULL VARCHAR2(30)
 PROPERTY_VALUE                                                 VARCHAR2(4000)
 DESCRIPTION                                                    VARCHAR2(4000)

SYS@iniesta>>


表空间的第一个数据文件不能被删除
临时表空间可以没有数据文件
===========================================temporary tablespace=============================
表空间组:
dba_tablespace_groups

临时表空间的使用:

V$TEMPSEG_USAGE



SYS@uplookin>> create temporary tablespace tempa tempfile '/u01/oradata/uplookin/tempa01.dbf' size 50M;    

Tablespace created.

SYS@uplookin>> create temporary tablespace tempb tempfile '/u01/oradata/uplookin/tempb01.dbf' size 50M;    

Tablespace created.

SYS@uplookin>> alter  tablespace tempa tablespace group tempgroup;

Tablespace altered.

SYS@uplookin>> alter  tablespace tempb tablespace group tempgroup;

Tablespace altered.

SYS@uplookin>> select count(*) from dba_tablespace_groups;                                             

  COUNT(*)
----------
         2

SYS@uplookin>> select * from dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ ---------------
TEMPGROUP                      TEMPA
TEMPGROUP                      TEMPB

SYS@uplookin>> create user u1 identified by u1 default tablespace mytbs temporary tablespace tempgroup quota 10M on mytbs;

User created.

SYS@uplookin>> create user u2 identified by u2 default tablespace mytbs temporary tablespace tempa quota 10M on mytbs;

User created.


tempa tempb 都增加到临时表空间组tempgroup,而u1的默认临时表空间是tempgroup,所以如果u1在两个会话中
进行磁盘排序时会使用不同的临时表空间,即分别在tempa和tempb中排序,可以试验证明

=============实验===============

SCOTT@chen>> create temporary tablespace tmpa tempfile '/u01/oradata/chen/tmpa01.dbf' size 10M;

SCOTT@chen>> create temporary tablespace tmpb tempfile '/u01/oradata/chen/tmpb01.dbf' size 10M;

SCOTT@chen>> alter tablespace tmpa tablespace group tempgroup;

SCOTT@chen>> alter tablespace tmpb tablespace group tempgroup;

SCOTT@chen>> desc dba_tablespace_groups


SCOTT@chen>> alter user scott temporary tablespace tempgroup;

SCOTT@chen>>

SYS@chen>> select sid,serial#,username from v$session where username is not null;

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
       149        255 SYS
       154        321 SCOTT
       160         11 SCOTT


SCOTT@chen>> select * from t1 order by 4;
...
199584 rows selected.

SCOTT@chen>> select * from t1 order by 3;  
...
199584 rows selected.


SYS@chen>> select username,TABLESPACE ,BLOCKS from v$sort_usage;

USERNAME                       TABLESPACE                          BLOCKS
------------------------------ ------------------------------- ----------
SCOTT                          TMPB                                  2816
SCOTT                          TMPA                                  2816

===============================

===================================undo tablespace========================================
SYS@iniesta>> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SYS@iniesta>>

自动管理时只能使用undo_tablespace指定的单个表空间
手动管理时,undo_tablespace参数失效

undo_retention 改前影像的保留时间,并不是强制保留时间
可以改成强制保持,需要将UNDO设置足够大

保持时间和UNDO表空间的大小取决于:
    1,最长时间的select语句
    2,事务产生的日志量


手动管理时应该最少使用两个表空间,故障时用以切换

create rollback segment
alter rollback segment

系统表空间的对象才能使用系统回滚段
手动管理时将需要开机ONLINE的 rollback segment 放在该参数中
SYS@iniesta>> show parameter  rollback_segment

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
rollback_segments                    string
transactions_per_rollback_segment    integer     5



v$undostat             估算单位时间内产生的undo blocks
dba_rollback_segs    查看回滚段的相关信息
v$transaction        查看回滚段使用情况

=============================================实验=====================================

SYS@uplookin>> alter system set undo_management=manual scope=spfile;

System altered.

SYS@uplookin>> startup force
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              75499088 bytes
Database Buffers          205520896 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SYS@uplookin>> select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME    TABLESPACE_NAME STATUS
--------------- --------------- ----------
SYSTEM          SYSTEM          ONLINE
_SYSSMU1$       UNDOTBS1        OFFLINE
_SYSSMU2$       UNDOTBS1        OFFLINE
_SYSSMU3$       UNDOTBS1        OFFLINE
_SYSSMU4$       UNDOTBS1        OFFLINE
_SYSSMU5$       UNDOTBS1        OFFLINE
_SYSSMU6$       UNDOTBS1        OFFLINE
_SYSSMU7$       UNDOTBS1        OFFLINE
_SYSSMU8$       UNDOTBS1        OFFLINE
_SYSSMU9$       UNDOTBS1        OFFLINE
_SYSSMU10$      UNDOTBS1        OFFLINE
_SYSSMU11$      MYUNDO          OFFLINE
_SYSSMU12$      MYUNDO          OFFLINE
_SYSSMU13$      MYUNDO          OFFLINE
_SYSSMU14$      MYUNDO          OFFLINE
_SYSSMU15$      MYUNDO          OFFLINE
_SYSSMU16$      MYUNDO          OFFLINE
_SYSSMU17$      MYUNDO          OFFLINE
_SYSSMU18$      MYUNDO          OFFLINE
_SYSSMU19$      MYUNDO          OFFLINE
_SYSSMU20$      MYUNDO          OFFLINE

21 rows selected.


SYS@uplookin>> delete from u1.t11 where rownum<=10;
delete from u1.t11 where rownum<=10
               *
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'MYTBS'

SYS@uplookin>> create rollback segment r1 tablespace myundo;

Rollback segment created.


SYS@uplookin>> alter rollback segment r1 online;

Rollback segment altered.

SYS@uplookin>> select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME    TABLESPACE_NAME STATUS
--------------- --------------- ----------
SYSTEM          SYSTEM          ONLINE
_SYSSMU1$       UNDOTBS1        OFFLINE
_SYSSMU2$       UNDOTBS1        OFFLINE
_SYSSMU3$       UNDOTBS1        OFFLINE
_SYSSMU4$       UNDOTBS1        OFFLINE
_SYSSMU5$       UNDOTBS1        OFFLINE
_SYSSMU6$       UNDOTBS1        OFFLINE
_SYSSMU7$       UNDOTBS1        OFFLINE
_SYSSMU8$       UNDOTBS1        OFFLINE
_SYSSMU9$       UNDOTBS1        OFFLINE
_SYSSMU10$      UNDOTBS1        OFFLINE
_SYSSMU11$      MYUNDO          OFFLINE
_SYSSMU12$      MYUNDO          OFFLINE
_SYSSMU13$      MYUNDO          OFFLINE
_SYSSMU14$      MYUNDO          OFFLINE
_SYSSMU15$      MYUNDO          OFFLINE
_SYSSMU16$      MYUNDO          OFFLINE
_SYSSMU17$      MYUNDO          OFFLINE
_SYSSMU18$      MYUNDO          OFFLINE
_SYSSMU19$      MYUNDO          OFFLINE
_SYSSMU20$      MYUNDO          OFFLINE
R1              MYUNDO          ONLINE



SYS@uplookin>> create rollback segment r2 tablespace myundo ;

Rollback segment created.


SYS@uplookin>> select segment_name,tablespace_name,status from dba_rollback_segs where segment_name like 'R%';

SEGMENT_NAME    TABLESPACE_NAME STATUS
--------------- --------------- ----------
R1              MYUNDO          ONLINE
R2              MYUNDO          OFFLINE


SYS@uplookin>> alter system set rollback_segments= r1,r2 scope=spfile;

System altered.


监控回滚段的使用:
1,监控用户使用的undo segment blocks
    v$transaction
        SES_ADDR     RAW(4 | 8)     User session object address
        USED_UBLK     NUMBER         Number of undo blocks used
        USED_UREC     NUMBER         Number of undo records used
    v$session
        saddr
        username
        
2,监控用户使用的回滚段名
    v$transaction
        ADDR         RAW(4 | 8)     Address of the transaction state object
        XIDUSN         NUMBER          Undo segment number
    v$session
        taddr
        username
    v$rollstat
        USN         NUMBER             Rollback (undo) segment number
        NAME         VARCHAR2(30)     Rollback segment name

3,估算回滚段的大小
    v$undostat    每十分钟刷新一次回滚段的使用情况,可以统计出每秒钟产生的 undo segment blocks,
                再乘以 undo_retention 基本可得到 undo tablespace 的大小

4,事务指定回滚段
    SQL> set transaction use rollback segment R1;

===========================================闪回-=============================================

闪表:
    从回收站闪表
    回复后索引会改名,但是正常使用
闪查询:
    从 undo segments中找 old image        
    所以依赖于 undo_retention,及UNDO表空间的大小,
    如果表结构被更改
    只有old image 没被覆盖才能闪成功
    只能闪DML,因为DROP ,ALTER,TRUNCATE 不会记录表内数据的UNDO

函数 timestamp_to_scn
     scn_to_timestamp
     to_timestamp


SYS@iniesta>> desc smon_scn_time  

====================================实验===================================================

SYS@iniesta>> SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
    2177389

SYS@iniesta>> SELECT sysdate FROM dual;          

SYSDATE
-------------------
2012-05-31 10:56:09

SYS@iniesta>> SELECT * FROM scott.t1;            

        ID
----------
         1
         2
         3

SYS@iniesta>> DELETE FROM scott.t1;

3 rows deleted.

SYS@iniesta>> COMMIT;

Commit complete.

SYS@iniesta>> SELECT * FROM scott.t1;

no rows selected

SYS@iniesta>> SELECT timestamp_to_scn(to_timestamp('2012-05-31 10:56:09','YYYY-MM-DD HH24:MI:SS')) FROM dual;

TIMESTAMP_TO_SCN(TO_TIMESTAMP('2012-05-3110:56:09','YYYY-MM-DDHH24:MI:SS'))
---------------------------------------------------------------------------
                                                                    2177390

SYS@iniesta>> SELECT * FROM scott.t1 AS OF SCN 2177390;

        ID
----------
         1
         2
         3

SYS@iniesta>> ALTER TABLE scott.t1 ENABLE ROW MOVEMENT;

Table altered.

SYS@iniesta>> FLASHBACK TABLE scott.t1 TO SCN 2177390;

Flashback complete.

SYS@iniesta>> SELECT * FROM scott.t1;

        ID
----------
         1
         2
         3

SYS@iniesta>>

=======================================================================================================


阅读(1958) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~