永久表空间
大文件：表空间下只能一个数据文件 最大: 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>>
=======================================================================================================
阅读(1968) | 评论(0) | 转发(0) |
