Oracle的存储管理
一、表空间的概念和使用
[oracle@oracle3 ORCL]$ ll *.dbf
-rw-r----- 1 oracle oinstall 104865792 06-24 15:41 example01.dbf
-rw-r----- 1 oracle oinstall 1073750016 06-24 15:41 plsql01.dbf
-rw-r----- 1 oracle oinstall 356524032 06-24 15:41 sysaux01.dbf
-rw-r----- 1 oracle oinstall 545267712 06-24 15:41 system01.dbf
-rw-r----- 1 oracle oinstall 31465472 06-17 00:00 temp01.dbf
-rw-r----- 1 oracle oinstall 560996352 06-24 15:41 undotbs01.dbf
-rw-r----- 1 oracle oinstall 6561792 06-24 15:41 users01.dbf --初始情况下数据库所有的数据文件
SQL> select ts#,name from v$tablespace; --表空间
TS# NAME
---------- ------------------------------
0 SYSTEM
1 UNDOTBS1
2 SYSAUX
4 USERS
3 TEMP
6 EXAMPLE
7 PLSQL
7 rows selected.
select * from dba_data_files --数据表空间由数据文件组成
select * from dba_temp_files --临时表空间和临时文件对应关系
操作1：给表空间USERS添加一个数据文件，给表空间扩容。
SQL> alter tablespace users add datafile '/u01/oracle/oradata/ORCL/users02.dbf' size 1G;
Tablespace altered.
select * from dba_data_files --一个表空间有两个数据文件
结论：一个表空间可以对多个数据文件，但是一个数据文件不能跨两个表空间
SQL> conn /as sysdba
Connected.
SQL> create table xu(x int) tablespace users;
Table created. --创建表的时候指定表空间，表中增加数据到底进入文件4还是文件7，Oralce自己决定的。
如果创建表的时候没有指定表空间，用默认的表空间（不是系统表空间）。不能把数据表创建在SYSTEM表空间上。
操作2：创建一个表空间
SQL> create tablespace mytbs1 datafile '/u01/oracle/oradata/ORCL/mytbs101.dbf' size 500M;
Tablespace created.
如果表空间满了，说明数据文件用光了，此时，需要扩容表空间，即增加数据文件。
如果磁盘满了，新加一个磁盘，创建一个目录，将这个目录挂载到这个磁盘，然后新的数据文件创建在新的目录下。
操作3：删除表空间
SQL> alter tablespace mytbs1 offline; --离线的目的是让当前所有连接到这个表空间的进程断开。防止下一步被挂死。
Tablespace altered.
SQL> drop tablespace mytbs1 including contents and datafiles; --删除的时候包括物理文件也会删除
Tablespace dropped.
练习：创建一个表空间MYTBSTEST 大小500M（要求两个数据文件），将其扩容到1G（总共3个数据文件），再删除这个表空间.
###########################################################################################
二、段（segment）
一个表空间可以分若干个段。
select * from dba_segments where owner='SCOTT'
一个表、索引就是一个段，创建一个段其实就是创建了一个表或者索引。
三、区（extent）
select * from dba_extents where owner='SCOTT' and segment_name='TT'
段由区组成
往表中插入数据就是往段中插入数据，随之，区的数量会增加。
一个区内的空间在物理上是连续的，区与区之间不一定是连续的，段的空间也不是连续的。
表空间 > 段> 区 > 数据块
四、oracle分配extent大小的方法
select * from user_extents where segment_name='TT' --TT是一个段
A 先分配16个大小是64K的区
B 再分配63个大小是1M的区
C 再分配120个大小是8M的区
D 后面的区全部是64M大小的
oracle分配算法好处在哪里？
1、区越大，数据块在物理上越连续，越连续就越利于查询这个表
2、如果一开始就做成64M的区，可能这个表只有几条记录，大量空间会被浪费。
3、如果开始的时候全是小区，虽然空间不是很连续，但是由于数据量小，性能损耗几乎是可以忽略的。
4、随着数据量的加大，扫表的成本会越来越高，此时后面的数据需要大的区尽量连续。
如果知道这个表将来的数据量或者规模。可以将extent的大小设置为等长，固定的。
SQL> create tablespace mytbs2 datafile '/u01/oracle/oradata/ORCL/mytbs201.dbf' size 500M extent management local uniform size 32M;
Tablespace created. --创建等长大小的区，本例是32M。
SQL> create tablespace mytbs3 datafile '/u01/oracle/oradata/ORCL/mytbs301.dbf' size 500M extent management local uniform;
Tablespace created. --省略后面的size 表示区等长大小每个1M
###########################################################################################
五、大文件表空间
前面的表空间都是小文件表空间，8K大小数据块情况下，数据文件的上限是32G。从oralce10g版本开始，引入大文件表空间，数据文件上限是32T
SQL> create bigfile tablespace mybigtbs datafile '/u01/oracle/oradata/ORCL/mybigtbs01.dbf' size 2G;
Tablespace created. --oracle公司的限制，一旦创建大文件表空间，就只能创建一个数据文件
SQL> alter tablespace mybigtbs add datafile '/u01/oracle/oradata/ORCL/mybigtbs201.dbf' size 2G;
alter tablespace mybigtbs add datafile '/u01/oracle/oradata/ORCL/mybigtbs201.dbf' size 2G
*
ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace --不能添加文件到大文件表空间
SQL> select tablespace_name,bigfile from dba_tablespaces;
TABLESPACE_NAME BIG
------------------------------ ---
SYSTEM NO
UNDOTBS1 NO
SYSAUX NO
TEMP NO
USERS NO
EXAMPLE NO
PLSQL NO
MYTBS2 NO
MYTBS3 NO
MYBIGTBS YES --大文件表空间的标志
10 rows selected.
SQL> desc dba_tablespaces
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30) --表空间名字
BLOCK_SIZE NOT NULL NUMBER --块大小
INITIAL_EXTENT NUMBER --表空间创建段的时候初始情况下区的大小
NEXT_EXTENT NUMBER --段的下一个区的大小
MIN_EXTENTS NOT NULL NUMBER --段中最少一个区
MAX_EXTENTS NUMBER --段中最大的区
PCT_INCREASE NUMBER --oracle10g中是废弃的参数
MIN_EXTLEN NUMBER --区的最小值
STATUS VARCHAR2(9) --表空间状态
CONTENTS VARCHAR2(9) --PERMANENT数据表空间 UNDO表空间 TEMPORARY临时表空间
LOGGING VARCHAR2(9) --表空间的事务是否写日志
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10) --管理方式是本地的
ALLOCATION_TYPE VARCHAR2(9) --区的分配方法：SYSTEM 自动分配区的算法 UNIFORM 创建者自己指定的区大小
PLUGGED_IN VARCHAR2(3) --
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6) --段管理方式：SYSTEM SYSAUX TEMP都是手动管理方式，其他的都是自动管理方式
DEF_TAB_COMPRESSION VARCHAR2(8) --是否是数据压缩方式存储
RETENTION VARCHAR2(11)
BIGFILE VARCHAR2(3) --大文件标志
###########################################################################################
六、本地管理表空间的方法
SQL> desc dba_tablespaces
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30) --表空间名字
BLOCK_SIZE NOT NULL NUMBER --块大小
INITIAL_EXTENT NUMBER --表空间创建段的时候初始情况下区的大小
NEXT_EXTENT NUMBER --段的下一个区的大小
MIN_EXTENTS NOT NULL NUMBER --段中最少一个区
MAX_EXTENTS NUMBER --段中最大的区
PCT_INCREASE NUMBER --oracle10g中是废弃的参数
MIN_EXTLEN NUMBER --区的最小值
STATUS VARCHAR2(9) --表空间状态
CONTENTS VARCHAR2(9) --PERMANENT数据表空间 UNDO表空间 TEMPORARY临时表空间
LOGGING VARCHAR2(9) --表空间的事务是否写日志
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10) --管理方式是本地的
ALLOCATION_TYPE VARCHAR2(9) --区的分配方法：SYSTEM 自动分配区的算法 UNIFORM 创建者自己指定的区大小
PLUGGED_IN VARCHAR2(3) --
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6) --段管理方式：SYSTEM SYSAUX TEMP都是手动管理方式，其他的都是自动管理方式
DEF_TAB_COMPRESSION VARCHAR2(8) --是否是数据压缩方式存储
RETENTION VARCHAR2(11)
BIGFILE VARCHAR2(3) --大文件标志
1、创建表空间的时候指定关于extent的扩展方式
create tablespace mytbs1 datafile '/u01/oracle/oradata/ORCL/mytbs101.dbf' size 500M (extent management local autoallocate); --本地管理方式，增长方式为自动增长
区的使用是靠数据文件头的位图来控制的，位图在数据文件中，理解为本地。
create tablespace mytbs1 datafile '/u01/oracle/oradata/ORCL/mytbs101.dbf' size 500M (extent management local uniform size xxxxx); --区的扩展方式为等量增长
2、创建表空间的时候可以设置数据文件的扩展方式
SQL> create tablespace mytbs4 datafile '/u01/oracle/oradata/ORCL/mytbs401.dbf' size 10M autoextend on next 1M maxsize 3G;
Tablespace created. --设置为数据文件自动扩展，每次扩展1M，到3G为止
可以既指定区的扩展方式，又指定数据文件的扩展方式
SQL> create tablespace mytbs5 datafile '/u01/oracle/oradata/ORCL/mytbs501.dbf' size 10M autoextend on next 1M maxsize 3G extent management local uniform size 2M;
Tablespace created.
设置数据文件扩展无上限（上限是32G） --oracle表空间文件硬限制
SQL> create tablespace mytbs4 datafile '/u01/oracle/oradata/ORCL/mytbs401.dbf' size 10M autoextend on next 1M maxsize unlimited;
Tablespace created. --不建议，因为磁盘空间处在不安全状态。
SQL> select file_id,autoextensible from dba_data_files; --文件扩展方式
FILE_ID AUT
---------- ---
4 YES
3 YES
2 YES
1 YES
5 YES
6 NO
7 NO
8 NO
9 NO
10 NO
11 YES
12 YES
12 rows selected.
select * from dba_data_files --文件自动扩展的参数
文件的扩展方式可以随意去调整
SQL> alter database datafile 12 autoextend off; --文件大小关闭的时候是多大就是多大，用完为止
Database altered.
SQL> alter database datafile 12 autoextend on;
Database altered.
SQL> alter database datafile 12 autoextend off;
Database altered.
SQL> alter database datafile 12 autoextend on next 5M maxsize 10G;
Database altered.
###########################################################################################
七、创建非标准块大小的表空间
1、先创建非标准块大小的内存区域，要有这个内存空间才能创建这个非标准块，否则报错
ORA-29339: tablespace block size 4096 does not match configured block sizes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_4k_cache_size big integer 0
改spfile参数后：
SQL> show parameter 4k
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_4k_cache_size big integer 32M
2、创建4K大小数据块的表空间
SQL> create tablespace my4ktbs blocksize 4k datafile '/u01/oracle/oradata/ORCL/my4ktbs.dbf' size 300M;
Tablespace created.
SQL> select tablespace_name,block_size from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE
------------------------------ ----------
SYSTEM 8192
UNDOTBS1 8192
SYSAUX 8192
TEMP 8192
USERS 8192
EXAMPLE 8192
PLSQL 8192
MYTBS2 8192
MYTBS3 8192
MYBIGTBS 8192
MYTBS4 8192
MYTBS5 8192
MY4KTBS 4096
13 rows selected.
###########################################################################################
八、用户默认的表空间
创建表的时候不指定表空间的话，默认用用户缺省的表空间
select username,default_tablespace from dba_users where username='SCOTT' --USERS
创建表时候指定了表空间，就用指定的表空间
SQL> create table xyt (x int) tablespace mytbs4;
Table created.
select * from dba_tables where owner='SCOTT' --查询表对应的表空间
将用户默认的表空间修改
SQL> conn /as sysdba
Connected.
SQL> alter user scott default tablespace mytbs2; --仅仅是政策的修改
User altered.
scott下所有的表对应表空间是不会变化的。因为数据已经进入数据文件成了既定的事实，无法修改。
SQL> conn scott/scott
Connected.
SQL> create table xyz(x number);
Table created. --此时是MYTBS2
用户默认的表空间从哪里来的。
SQL> conn /as sysdba
Connected.
SQL> create user xp identified by xp default tablespace mytbs4;
User created.
如果不指定，发现默认的表空间是USERS
SQL> drop user xp;
User dropped.
SQL> create user xp identified by xp; 继承数据库默认的数据表空间给用户作为默认的数据表空间
User created.
select * from database_properties where property_name='DEFAULT_PERMANENT_TABLESPACE' --USERS
系统默认的数据表空间是不能删除的
SQL> drop tablespace users including contents and datafiles;
drop tablespace users including contents and datafiles
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace
修改系统默认的数据表空间
SQL> alter database default tablespace mytbs5;
Database altered. --发现其他用户从数据库默认表空间继承过来的默认表空间都会变化。不建议操作。
SQL> alter database default tablespace users;
Database altered.
###########################################################################################
九、用户的临时表空间
作用
A PGA--支撑运算（hash 、sort、 bitmap merge），如果PGA区域不够用，计算空间会借用临时表空间--临时文件
B 可以在临时表空间上创建临时表，但是临时表的数据是不会永久保存的，当事务结束的时候，临时表的数据会自动清空
select username,temporary_tablespace from dba_users --当前数据库只有一个临时表空间TEMP，所有用户都用TEMP
SQL> select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE
-----------------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP --系统默认的临时表空间
SQL> create user xp identified by xp default tablespace mytbs2 temporary tablespace temp;
User created. --如果不指定就用系统默认的
创建临时表空间
SQL> create temporary tablespace temp01 tempfile '/u01/oracle/oradata/ORCL/temp101.dbf' size 300M;
Tablespace created.
select * from dba_temp_files --两个临时表空间
用户默认的临时表空间可以修改
SQL> alter user xp temporary tablespace temp01;
User altered.
数据库默认的临时表空间也可以修改
SQL> alter database default temporary tablespace temp01;
Database altered.
SQL> alter database default temporary tablespace temp;
Database altered.
数据库上所有的临时表空间都可以删除，暂时不报错，但是要用到的时候发现没有，就会报错了。
SQL> alter user scott temporary tablespace temp01;
User altered.
SQL> conn /as sysdba
Connected.
SQL> drop tablespace temp01 including contents and datafiles;
Tablespace dropped.
SQL> conn scott/scott
Connected.
SQL> create table testa as select * from all_objects;
Table created.
SQL> create table testb as select * from all_objects;
Table created.
SQL> select * from testa a,testb b order by 1,2,3,4,5,6,7,8,9,10;
select * from testa a,testb b order by 1,2,3,4,5,6,7,8,9,10
--大量的排序算法，PGA空间不够，会用临时表空间
*
ERROR at line 1:
ORA-00959: tablespace 'TEMP01' does not exist
SQL> conn /as sysdba
Connected.
SQL> alter user scott temporary tablespace temp; --改回去
User altered.
SQL> conn scott/scott
Connected.
SQL> select * from testa a,testb b order by 1,2,3,4,5,6,7,8,9,10; --此时不报错
###########################################################################################
十、UNDO 表空间--为了保护数据的一致性而存储数据旧的镜像的表空间
感受一些数据一致性的控制
当修改数据未提交的时候，修改的进程直接修改数据块，这个数据库块的旧的镜像被转移到UNDO中存储，此时没有提交的时候为了防止其他用户修改发生错乱，这个行会被加锁，如果查询这个数据的话，发现有锁，就直接到UNDO中去查询数据的旧的镜像。
一个数据库只能有一个UNDO是有效的。
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO --UNDO的管理方式：AUTO
undo_retention integer 900 --undo数据在undo段中保留的最大时间为900s
undo_tablespace string UNDOTBS1 --当前数据库默认的undo表空间
SQL> select * from v$rollname;
USN NAME
---------- ------------------------------
0 SYSTEM --第一个undo段在系统表空间上SYSTEM，系统用户使用的undo空间，系统事务用 的，例如操作数据字典的系统层面的回滚段
-----------------------------------------
1 _SYSSMU1$
2 _SYSSMU2$
3 _SYSSMU3$
4 _SYSSMU4$
5 _SYSSMU5$
6 _SYSSMU6$
7 _SYSSMU7$
8 _SYSSMU8$
9 _SYSSMU9$
10 _SYSSMU10$ --是在UNDO表空间上的UNDOTBS1
11 rows selected.
undo_retention--从事务结束之后（commit或者rollback之后），旧的数据在undo保存的最大时间是900s，但是这个不是硬性限制，当UNDO空间不够用的时候，这个空间是有可能在900s内被覆盖的。
select * from v$rollstat --UNDO段使用的统计情况
UNDO段空间不足的时候需要加数据文件扩容
比如：修改了20G数据，但是此时UNDO段只有10G，旧的镜像空间是不足的，此时会发生ORA-01555的错误，此时需要扩容UNDO 表空间
案例：修改（切换）当前数据库的UNDO表空间
1、创建一个新的UNDO 表空间
SQL> create undo tablespace undotbs2 datafile '/u01/oracle/oradata/ORCL/undo02.dbf' size 2G;
Tablespace created.
2、切换
alter tablespace undotbs1 offline; --回滚没有提交的数据
SQL> alter system set undo_tablespace=undotbs2 scope=both;
System altered.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
3、删除旧的
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
4、扩容
SQL> alter tablespace undotbs2 add datafile '/u01/oracle/oradata/ORCL/undotbs201.dbf' size 500M;
Tablespace altered.
SQL> select * from v$rollname;
USN NAME
---------- ------------------------------
0 SYSTEM --系统回滚段在SYSTEM表空间中是不会变化的
11 _SYSSMU11$ --跟之前相比变了11-20
12 _SYSSMU12$
13 _SYSSMU13$
14 _SYSSMU14$
15 _SYSSMU15$
16 _SYSSMU16$
17 _SYSSMU17$
18 _SYSSMU18$
19 _SYSSMU19$
20 _SYSSMU20$
11 rows selected.
练习：
1、创建一个表空间（区大小是4M，数据文件自动增长，步长1M，上限是500M）和临时表空间（大小300M）。
2、创建一个用户ZCL，指定上面的表空间和临时表空间为这个用户的默认的表空间和临时表空间
3、切换这个用户ZCL的临时表空间和数据表空间到TEMP和USERS
4、切换数据库的UNDO表空间到UNDOTBS上（2GB）
5、再给这个UNDO表空间扩容到2.5GB
----------------------------------------------------------------------------------------------------------------
###########################################################################################
临时表空间组（10g）
需求：创建两个临时表空间组
组名 表空间 数据文件
------------------------------------------------------------------------------------
grp1 tempa tempb tempa01.dbf tempa02.dbf tempb01.dbf tempb02.dbf
grp2 tempc tempd tempc01.dbf tempc02.dbf tempd01.dbf tempd02.dbf
--------------------------------------------------------------------------------------
1、创建临时表空间tempa tempb tempc tempd 并添加到组
SQL> create temporary tablespace tempa tempfile '/u01/oracle/oradata/ORCL/tempa01.dbf' size 200M,'/u01/oracle/oradata/ORCL/tempa02.dbf' size 200M tablespace group grp1;
Tablespace created.
SQL> create temporary tablespace tempb tempfile '/u01/oracle/oradata/ORCL/tempb01.dbf' size 200M,'/u01/oracle/oradata/ORCL/tempb02.dbf' size 200M tablespace group grp1;
Tablespace created.
SQL> create temporary tablespace tempc tempfile '/u01/oracle/oradata/ORCL/tempc01.dbf' size 200M,'/u01/oracle/oradata/ORCL/tempc02.dbf' size 200M tablespace group grp2;
Tablespace created.
SQL> create temporary tablespace tempd tempfile '/u01/oracle/oradata/ORCL/tempd01.dbf' size 200M,'/u01/oracle/oradata/ORCL/tempd02.dbf' size 200M tablespace group grp2;
Tablespace created.
确认：
select a.group_name,a.tablespace_name,b.file_name from dba_tablespace_groups a,dba_temp_files b
where a.tablespace_name = b.tablespace_name
2、创建用户的时候可以指定临时表空间组
SQL> create user zcl identified by zcl temporary tablespace grp1;
User created.
3、切换现有的用户临时表空间到临时表空间组
SQL> alter user zcl temporary tablespace grp2;
User altered.
select * from dba_users --确认
4、将数据库默认的临时表空间切换到临时表空间组上
SQL> alter database default temporary tablespace grp1;
Database altered.
5、将已经有的临时表空间从一个组移动到另外一个组
SQL> alter tablespace tempb tablespace group grp2;
Tablespace altered.
6、将已经建好的临时表空间添加到组
SQL> alter tablespace temp tablespace group grp1;
Tablespace altered.
7、扩容临时表空间组：直接给表空间加临时文件即可
8、删除临时表空间组：直接删除掉对应的临时表空间即可
总结：临时表空间组就是一个逻辑分组的概念，不是一个表空间对象。目的是为了让临时表空间资源在用户之间分开不争用。
###########################################################################################
十一、oracle的三级位图技术--深入剖析表空间本地管理方式
1、创建一个测试表空间
SQL> conn /as sysdba
Connected.
SQL> create tablespace henry datafile '/u01/oracle/oradata/ORCL/henry01.dbf' size 100M extent management local uniform;
Tablespace created.
2、授权给用户
SQL> grant dba to scott;
Grant succeeded.
SQL> conn scott/scott
Connected.
3、创建一个测试段
SQL> create table test(a varchar2(10)) tablespace henry;
Table created.
4、查询视图
select * from dba_extents where owner='SCOTT' and segment_name='TEST' order by block_id --现在有一个extent
区的数据从文件的块9开始
1~8块数据是什么？ 一个8K数据块大小的数据文件预留前8个块作为文件头
5、将数据块1~3 dump出来
SQL> alter system dump datafile 15 block min 1 block max 3;
System altered.
[oracle@oracle3 udump]$ pwd
/u01/oracle/admin/ORCL/udump --这目录中存储的是server processes进程的跟踪文件，一个进程结束，这个文件的写入就结束。重新连接的话，会产生新的跟踪文件
找到最近的跟踪文件
Start dump data blocks tsn: 22 file#: 15 minblk 1 maxblk 3
Block 1 (file header) not dumped: use dump file header command --数据块1没有dump出来
------------
buffer tsn: 22 rdba: 0x03c00002 (15/2) --文件15的块2
scn: 0x0000.002839ee seq: 0x02 flg: 0x00 tail: 0x39ee1d02
frmt: 0x02 chkval: 0x0000 type: 0x1d=KTFB Bitmapped File Space Header --位图文件空间头
Hex dump of block: st=0, typ_found=1
---------------------------------------------------------------
File Space Header Block:
Header Control:
RelFno: 15, Unit: 128, Size: 12800, Flag: 1
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 7, Tail: 12680, First: 1, Free: 98
Deallocation scn: 0.0
Header Opcode:
Save: No Pending Op
--------------------------------------------------------------------
buffer tsn: 22 rdba: 0x03c00003 (15/3)
scn: 0x0000.002839ee seq: 0x01 flg: 0x00 tail: 0x39ee1e01
frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap --位图文件的空间位图
Hex dump of block: st=0, typ_found=1
File Space Bitmap Block:
BitMap Control: --位图控制句柄
RelFno: 15, BeginBlock: 9, Flag: 0, First: 1, Free: 63487
0100000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
........
End dump data blocks tsn: 22 file#: 15 minblk 2 maxblk 3
01 一组（16进制） = 0000 0001
每8个bytes为一组，组和组之间是从左往右读，组内是从右往左读
读取：1000 0000 表示当前extent使用是：userd free free free free free free free
6、手工分配一个区
SQL> alter table test allocate extent;
Table altered.
SQL> alter system dump datafile 15 block 3;
System altered.
File Space Bitmap Block:
BitMap Control:
RelFno: 15, BeginBlock: 9, Flag: 0, First: 2, Free: 63486
0300000000000000 0000000000000000 0000000000000000 0000000000000000
03=0000 0011 反过来：1100 0000 used used free free free free free free
手动分配4个区
SQL> alter table test allocate extent;
Table altered.
SQL> alter table test allocate extent;
Table altered.
SQL> alter table test allocate extent;
Table altered.
SQL> alter table test allocate extent;
Table altered.
SQL> alter system dump datafile 15 block 3;
System altered.
File Space Bitmap Block:
BitMap Control:
RelFno: 15, BeginBlock: 9, Flag: 0, First: 6, Free: 63482
3F00000000000000 0000000000000000 0000000000000000 0000000000000000
3F= 0011 1111 反过来：1111 1100 used used used used used used free free
块3~8全部是位图，分解后按照区的大小加乘就是文件的上限32G。这个位图仅仅是记录已经格式化过的区。
---------------------------------------------------------------------------------------------
研究下文件的第9个块
SQL> alter system dump datafile 15 block 9;
System altered.
*** 2014-06-25 15:10:28.120
Start dump data blocks tsn: 22 file#: 15 minblk 9 maxblk 9
buffer tsn: 22 rdba: 0x03c00009 (15/9)
scn: 0x0000.00283ce2 seq: 0x01 flg: 0x04 tail: 0x3ce22001
frmt: 0x02 chkval: 0x75c5 type: 0x20=FIRST LEVEL BITMAP BLOCK --一级位图块
Hex dump of block: st=0, typ_found=1
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 1 parent dba: 0x03c0000b -父块的物理地址parent data block address
poffset: 0
unformatted: 60 total: 64 first useful block: 4
owning instance : 1
instance ownership changed at
Last successful Search
Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0
Extent Map Block Offset: 4294967295
First free datablock : 4
Bitmap block lock opcode 0
Locker xid: : 0x0000.000.00000000
Inc #: 0 Objd: 57927
HWM Flag: Not Set
Highwater:: 0x03c0000d ext#: 0 blk#: 4 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x03c00009 Length: 64 Offset: 0
0:Metadata 1:Metadata 2:Metadata 3:Metadata --元数据：块9、10、11、12
4:unformatted 5:unformatted 6:unformatted 7:unformatted 没有格式化
8:unformatted 9:unformatted 10:unformatted 11:unformatted
12:unformatted 13:unformatted 14:unformatted 15:unformatted
16:unformatted 17:unformatted 18:unformatted 19:unformatted
20:unformatted 21:unformatted 22:unformatted 23:unformatted
24:unformatted 25:unformatted 26:unformatted 27:unformatted
28:unformatted 29:unformatted 30:unformatted 31:unformatted
32:unformatted 33:unformatted 34:unformatted 35:unformatted
36:unformatted 37:unformatted 38:unformatted 39:unformatted
40:unformatted 41:unformatted 42:unformatted 43:unformatted
44:unformatted 45:unformatted 46:unformatted 47:unformatted
48:unformatted 49:unformatted 50:unformatted 51:unformatted
52:unformatted 53:unformatted 54:unformatted 55:unformatted
56:unformatted 57:unformatted 58:unformatted 59:unformatted
60:unformatted 61:unformatted 62:unformatted 63:unformatted
--------------------------------------------------------
End dump data blocks tsn: 22 file#: 15 minblk 9 maxblk 9
--------------------------------------------------------
DBA Ranges : --块10的64个位置标示的是块73~136
--------------------------------------------------------
0x03c00049 Length: 64 Offset: 0
0:unformatted 1:unformatted 2:unformatted 3:unformatted
4:unformatted 5:unformatted 6:unformatted 7:unformatted
8:unformatted 9:unformatted 10:unformatted 11:unformatted
12:unformatted 13:unformatted 14:unformatted 15:unformatted
16:unformatted 17:unformatted 18:unformatted 19:unformatted
20:unformatted 21:unformatted 22:unformatted 23:unformatted
24:unformatted 25:unformatted 26:unformatted 27:unformatted
28:unformatted 29:unformatted 30:unformatted 31:unformatted
32:unformatted 33:unformatted 34:unformatted 35:unformatted
36:unformatted 37:unformatted 38:unformatted 39:unformatted
40:unformatted 41:unformatted 42:unformatted 43:unformatted
44:unformatted 45:unformatted 46:unformatted 47:unformatted
48:unformatted 49:unformatted 50:unformatted 51:unformatted
52:unformatted 53:unformatted 54:unformatted 55:unformatted
56:unformatted 57:unformatted 58:unformatted 59:unformatted
60:unformatted 61:unformatted 62:unformatted 63:unformatted
--------------------------------------------------------
End dump data blocks tsn: 22 file#: 15 minblk 10 maxblk 10
create or replace function getfbin(p_dba in varchar2) return varchar2 is
l_str varchar2(255) default null;
begin
l_str:='datafile is '||dbms_utility.data_block_address_file(to_number(ltrim(p_dba,'0x'),'xxxxxxxx'))||chr(10)||
' datablock is '||dbms_utility.data_block_address_block(to_number(ltrim(p_dba,'0x'),'xxxxxxxx'));
return l_str;
exception when others then
return '';
end;
select getfbin('0x03c0000b') from dual; --文件15块11
SQL> alter system dump datafile 15 block 11;
System altered.
Start dump data blocks tsn: 22 file#: 15 minblk 11 maxblk 11
buffer tsn: 22 rdba: 0x03c0000b (15/11)
scn: 0x0000.00283ce5 seq: 0x04 flg: 0x04 tail: 0x3ce52104
frmt: 0x02 chkval: 0x655d type: 0x21=SECOND LEVEL BITMAP BLOCK ==二级位图块
Hex dump of block: st=0, typ_found=1
Dump of Second Level Bitmap Block
number: 2 nfree: 2 ffree: 0 pdba: 0x03c0000c --父块地址
Inc #: 0 Objd: 57927
opcode:2
xid:
L1 Ranges : level 1的范围
--------------------------------------------------------
0x03c00009 Free: 5 Inst: 1 块9
0x03c0000a Free: 5 Inst: 1 块10
--------------------------------------------------------
End dump data blocks tsn: 22 file#: 15 minblk 11 maxblk 11
12--11--9
10
137
138
二级位图块中存一级位图块范围。
三级位图块存储二级位图块和一级位图块的混合视图
SQL> alter system dump datafile 15 block 12;
System altered.
Start dump data blocks tsn: 22 file#: 15 minblk 12 maxblk 12
buffer tsn: 22 rdba: 0x03c0000c (15/12)
scn: 0x0000.00283cea seq: 0x01 flg: 0x04 tail: 0x3cea2301
frmt: 0x02 chkval: 0x49e4 type: 0x23=PAGETABLE SEGMENT HEADER --三级位图块--页表段头
Hex dump of block: st=0, typ_found=1----------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 128
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x03c0000d ext#: 0 blk#: 4 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x03c0000d ext#: 0 blk#: 4 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0x03c00009
Level 1 BMB for Low HWM block: 0x03c00009
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x03c0000b
Last Level 1 BMB: 0x03c0000a
Last Level II BMB: 0x03c0000b
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 1 obj#: 57927 flag: 0x10000000
Inc # 0
Extent Map 区的映射
-----------------------------------------------------------------
0x03c00009 length: 128 块9
Auxillary Map --辅助映射图
--------------------------------------------------------
Extent 0 : L1 dba: 0x03c00009 块9是一级位图块 Data dba: 0x03c0000d 数据从13开始
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x03c0000b
End dump data blocks tsn: 22 file#: 15 minblk 12 maxblk 12
块137和138已经是第二个区的数据：
--------------------------------------------------------
DBA Ranges : 块137
--------------------------------------------------------
0x03c00089 Length: 64 Offset: 0
0:Metadata 1:Metadata 2:unformatted 3:unformatted --metadata是自己137和138
4:unformatted 5:unformatted 6:unformatted 7:unformatted
8:unformatted 9:unformatted 10:unformatted 11:unformatted
12:unformatted 13:unformatted 14:unformatted 15:unformatted
16:unformatted 17:unformatted 18:unformatted 19:unformatted
20:unformatted 21:unformatted 22:unformatted 23:unformatted
24:unformatted 25:unformatted 26:unformatted 27:unformatted
28:unformatted 29:unformatted 30:unformatted 31:unformatted
32:unformatted 33:unformatted 34:unformatted 35:unformatted
36:unformatted 37:unformatted 38:unformatted 39:unformatted
40:unformatted 41:unformatted 42:unformatted 43:unformatted
44:unformatted 45:unformatted 46:unformatted 47:unformatted
48:unformatted 49:unformatted 50:unformatted 51:unformatted
52:unformatted 53:unformatted 54:unformatted 55:unformatted
56:unformatted 57:unformatted 58:unformatted 59:unformatted
60:unformatted 61:unformatted 62:unformatted 63:unformatted
--------------------------------------------------------
--------------------------------------------------------
DBA Ranges : --块138
--------------------------------------------------------
0x03c000c9 Length: 64 Offset: 0
0:unformatted 1:unformatted 2:unformatted 3:unformatted
4:unformatted 5:unformatted 6:unformatted 7:unformatted
8:unformatted 9:unformatted 10:unformatted 11:unformatted
12:unformatted 13:unformatted 14:unformatted 15:unformatted
16:unformatted 17:unformatted 18:unformatted 19:unformatted
20:unformatted 21:unformatted 22:unformatted 23:unformatted
24:unformatted 25:unformatted 26:unformatted 27:unformatted
28:unformatted 29:unformatted 30:unformatted 31:unformatted
32:unformatted 33:unformatted 34:unformatted 35:unformatted
36:unformatted 37:unformatted 38:unformatted 39:unformatted
40:unformatted 41:unformatted 42:unformatted 43:unformatted
44:unformatted 45:unformatted 46:unformatted 47:unformatted
48:unformatted 49:unformatted 50:unformatted 51:unformatted
52:unformatted 53:unformatted 54:unformatted 55:unformatted
56:unformatted 57:unformatted 58:unformatted 59:unformatted
60:unformatted 61:unformatted 62:unformatted 63:unformatted
--------------------------------------------------------
创建测试数据
SQL> insert into test select object_id from dba_objects;
50821 rows created.
SQL> commit;
Commit complete.
SQL> alter system dump datafile 15 block 9;
System altered.
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x03c00009 Length: 64 Offset: 0
0:Metadata 1:Metadata 2:Metadata 3:Metadata
4:FULL 5:FULL 6:FULL 7:FULL
8:FULL 9:FULL 10:FULL 11:FULL
12:FULL 13:FULL 14:FULL 15:FULL
16:FULL 17:FULL 18:FULL 19:FULL
20:FULL 21:FULL 22:FULL 23:FULL
24:FULL 25:FULL 26:FULL 27:FULL
28:FULL 29:FULL 30:FULL 31:FULL
32:FULL 33:FULL 34:FULL 35:FULL
36:FULL 37:FULL 38:FULL 39:FULL
40:FULL 41:FULL 42:FULL 43:FULL
44:FULL 45:FULL 46:FULL 47:FULL
48:FULL 49:FULL 50:FULL 51:FULL
52:FULL 53:FULL 54:FULL 55:FULL
56:FULL 57:FULL 58:FULL 59:FULL
60:FULL 61:FULL 62:FULL 63:FULL
--------------------------------------------------------
块10：
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x03c00049 Length: 64 Offset: 0
0:FULL 1:75-100% free 2:75-100% free 3:75-100% free
4:FULL 5:75-100% free 6:75-100% free 7:75-100% free
8:0-25% free 9:75-100% free 10:75-100% free 11:75-100% free
12:75-100% free 13:75-100% free 14:75-100% free 15:75-100% free
16:75-100% free 17:75-100% free 18:75-100% free 19:75-100% free
20:75-100% free 21:75-100% free 22:75-100% free 23:75-100% free
24:75-100% free 25:75-100% free 26:75-100% free 27:FULL
28:FULL 29:75-100% free 30:75-100% free 31:FULL
32:FULL 33:75-100% free 34:75-100% free 35:FULL
36:FULL 37:75-100% free 38:75-100% free 39:FULL
40:FULL 41:75-100% free 42:75-100% free 43:FULL
44:75-100% free 45:75-100% free 46:75-100% free 47:FULL
48:75-100% free 49:75-100% free 50:75-100% free 51:FULL
52:75-100% free 53:75-100% free 54:75-100% free 55:FULL
56:75-100% free 57:75-100% free 58:75-100% free 59:FULL
60:75-100% free 61:75-100% free 62:75-100% free 63:FULL
--------------------------------------------------------
结论：数据块的状态 unformated
0~25% free
25~50% free
50~75% free
75~100% free
FULL
当插入数据时候从三级位图中找空闲块插入数据的。
三级位图技术--段的自动管理方式。
SQL> select tablespace_name,segment_space_management from dba_tablespaces;
TABLESPACE_NAME SEGMEN
------------------------------ ------
SYSTEM MANUAL
SYSAUX AUTO
TEMP MANUAL
USERS AUTO
UNDOTBS2 MANUAL
EXAMPLE AUTO
PLSQL AUTO
MYTBS2 AUTO --自动管理方式就是ASSM（auto segment space management）
MYTBS3 AUTO
MYBIGTBS AUTO
MYTBS4 AUTO
MYTBS5 AUTO
MY4KTBS AUTO
TEMPA MANUAL
TEMPB MANUAL
TEMPC MANUAL
TEMPD MANUAL
HENRY AUTO
18 rows selected.
MANUAL--手工管理方式。
找空闲数据块时候，根据freelist（段的属性）中的块来找，内存中的一个链表
--------------------------FREE list
数据块有两个参数：PCTFREE PCTUSED
10 40
A 当数据块的使用量超过90的时候，认为块满了，从freelist上拿掉
B 当删除数据删除的数据少于40%的时候，认为是空闲块。
C server processes会将空块和少于40%空间的块按需放到freelist上
D 当对空间有需求的时候，进程从freelist上拿数据来操作的。
FREElist GROUP -治标不治本的
PCTFREE--1、判断是否是满块 ，A 行不能跨块的 B 当update某个字段时候，可能行会变长，一块存不下一行了，发生行迁移，这个是影响性能的
2、判断是否从freelist上拿下来 。不用
SQL> select dbms_metadata.get_ddl('TABLE','EMP') from dual;
DBMS_METADATA.GET_DDL('TABLE','EMP')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDEX
PCTFREE 10
INITRANS 2 --索引数据块初始化打开的事务槽数
MAXTRANS 255 --索引数据块最大支撑255个进程访问
COMPUTE STATISTICS --采集收集性能数据
存储参数
STORAGE(INITIAL 65536 --第一个extent的大小
NEXT 1048576 --下一类区的大小
MINEXTENTS 1 --最少一个extent
MAXEXTENTS 2147483645 --最多的区数
PCTINCREASE 0 --区的增长百分比，ASSM下是废弃的参数
FREELISTS 1 --ASSM下是废弃的参数
FREELIST GROUPS 1 --ASSM下是废弃的参数
BUFFER_POOL DEFAULT) --指定主键索引使用的缓冲区是缺省的缓冲区
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE --外键
) PCTFREE 10 --表段的PCTFREE参数
PCTUSED 40 --ASSM下是废弃的参数
INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT) --指定表使用的缓冲区是缺省的缓冲区
TABLESPACE "USERS"
###########################################################################################
十二：表空间扩展
方法1：直接给表空间添加数据文件
方法2：将数据文件设置为自动扩展
A 创建表空间的时候指定数据文件是自动扩展的
B 改变数据文件为自动扩展
方法3：resize方法
SQL> alter database datafile 15 resize 200M;
Database altered.
SQL> alter database datafile 15 resize 100M;
Database altered.
SQL> alter database datafile 15 resize 5M;
alter database datafile 15 resize 5M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value --缩小的时候不能影响已经有的数据的存储
SQL> alter database datafile 15 resize 20M;
Database altered.
###########################################################################################
十三、限制用户对表空间访问
需求：显限制用户HR只能使用表空间MYTBS2空间中10M的空间
SQL> conn scott/scott
Connected.
SQL> alter user hr quota 10M on henry;
User altered.
SQL> conn hr/hr
Connected.
SQL> create table test tablespace henry as select * from all_objects;
Table created.
SQL> insert into test select * from all_objects;
40914 rows created.
SQL> commit;
Commit complete.
SQL> insert into test select * from all_objects;
40914 rows created.
SQL> commit;
Commit complete.
SQL> insert into test select * from all_objects;
40914 rows created.
SQL> commit;
Commit complete.
select sum(bytes)/1024/1024 from dba_extents where segment_name='TEST' and owner='HR' --19M
SQL> conn scott/scott
Connected.
SQL> select * from dba_sys_privs where grantee='HR';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
HR CREATE VIEW NO
HR UNLIMITED TABLESPACE（限制表空间） NO --这个权限会覆盖quota
HR CREATE DATABASE LINK NO
HR CREATE SEQUENCE NO
HR CREATE SESSION NO
HR ALTER SESSION NO
HR CREATE SYNONYM NO
7 rows selected.
SQL> revoke unlimited tablespace from hr; --回收权限
Revoke succeeded.
SQL> conn hr/hr
Connected.
SQL> truncate table test;
Table truncated.
SQL> insert into test select * from all_objects;
40914 rows created.
SQL> commit;
Commit complete.
SQL> insert into test select * from all_objects;
40914 rows created.
SQL> commit;
Commit complete.
SQL> insert into test select * from all_objects;
insert into test select * from all_objects
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'HENRY' --配额不够了
select sum(bytes)/1024/1024 from dba_extents where segment_name='TEST' and owner='HR' --10M
SQL> conn /as sysdba
Connected.
SQL> alter user hr quota 5M on henry; --再改为5M
User altered.
此时再插入数据是可以的，但是仅仅是将已经开辟的extents的空间填满而已，大小不会超过10M
监控表空间配额的方法：
select username,
tablespace_name,
(case max_bytes
when -1 then
'NULL'
else
to_char(max_bytes / 1024 / 1024 || 'MB')
end) as qutoas
from dba_ts_quotas
##########################################################################################
实操案例：重命名数据文件或者移动数据文件
需求：将数据文件henry01.dbf改名为henry201.dbf
1、将数据文件对应的表空间离线
SQL> conn /as sysdba
Connected.
SQL> alter tablespace henry offline;
Tablespace altered.
2、修改名字
[oracle@oracle3 ORCL]$ mv henry01.dbf henry201.dbf
3、修改数据字典
SQL> alter tablespace henry rename datafile '/u01/oracle/oradata/ORCL/henry01.dbf' to '/u01/oracle/oradata/ORCL/henry201.dbf';
Tablespace altered.
4、将表空间online
SQL> alter tablespace henry online;
Tablespace altered.
练习：在/u01/oracle/oradata/ORCL/下创建一个目录data，将USERS表空间的数据文件移动到data目录下。
###########################################################################################
案例：监控表空间的使用
SQL> desc dba_free_space --表空间剩余空间大小，一行是一个连续的空间，显示的是数据文件空间的剩余大小
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME VARCHAR2(30)
FILE_ID NUMBER --文件号
BLOCK_ID NUMBER --连续空间的起始数据块号
BYTES NUMBER --剩余空间
BLOCKS NUMBER
RELATIVE_FNO NUMBER
监控语句：发现表空间不足的要及时扩容
select tbs,
sum(totalM),
sum(usedM),
sum(remaindM),
round(sum(usedM) / sum(totalM), 2) as usedPer,
round(sum(remaindM) / sum(totalM), 2) as remaindPer
from (select b.FILE_ID id,
b.TABLESPACE_NAME tbs,
b.BYTES / 1024 / 1024 totalM,
(b.BYTES - sum(a.BYTES)) / 1024 / 1024 usedM,
sum(a.BYTES) / 1024 / 1024 remaindM
from dba_free_space a, dba_data_files b
where a.FILE_ID = b.FILE_ID
group by b.FILE_ID, b.TABLESPACE_NAME, b.BYTES
order by b.TABLESPACE_NAME)
group by tbs
监控即将用完的表空间是否可以扩容了，如果数据文件是可以自动扩展的，表空间使用率基本上接近于100%的。
建议：将SYSTEM表空间和SYSAUX表空间的文件自动扩展关闭的，防止滥用。
SQL> alter database datafile 1 autoextend off;
Database altered.
SQL> alter database datafile 1 resize 1G;
Database altered.
SQL> alter database datafile 3 autoextend off;
Database altered.
SQL> alter database datafile 3 resize 500M;
Database altered.
做成自动作业：SP 每天查询存储的使用，将结果插入一个表进行监控。
SQL> set long 10000
SQL> select dbms_metadata.get_ddl('TABLESPACE','HENRY') from dual;
DBMS_METADATA.GET_DDL('TABLESPACE','HENRY')
--------------------------------------------------------------------------------
CREATE TABLESPACE "HENRY" DATAFILE
'/u01/oracle/oradata/ORCL/henry201.dbf' SIZE 104857600
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576 SEGMENT SPACE MANAGEMENT AUTO --创建语句
ALTER DATABASE DATAFILE '/u01/oracle/oradata/ORCL/henry201.dbf' RESIZE 524288000 --resize语句
###########################################################################################
案例：将表空间设置为只读
SQL> alter tablespace henry read only;
Tablespace altered.
SELECT * FROM dba_tablespaces --status是read only
SQL> alter tablespace henry read write;
Tablespace altered. --设置为可读写
将表空间设置为只读，执行时候，是否有发生完全检查点事件？
SQL> select checkpoint_change#,file# from v$datafile_header where tablespace_name='HENRY';
CHECKPOINT_CHANGE# FILE#
------------------ ----------
2809745 15
SQL> alter tablespace henry read only;
Tablespace altered.
SQL> select checkpoint_change#,file# from v$datafile_header where tablespace_name='HENRY';
CHECKPOINT_CHANGE# FILE#
------------------ ----------
2810026 15 --设置为只读会强制生成检查点，并将这个表空间的脏块写入到数据文件更新数据文件头部
SQL> select checkpoint_change#,file# from v$datafile_header;
CHECKPOINT_CHANGE# FILE#
------------------ ----------
2809745 1
2809745 2
2809745 3
2809745 4
2809745 5
2809745 6
2809745 7
2809745 8
2809745 9
2809745 10
2809745 11
2809745 12
2809745 13
2809745 14 --其他的数据文件是不受影响的
2810026 15
15 rows selected.
SQL> select checkpoint_change#,file# from v$datafile_header where tablespace_name='HENRY';
CHECKPOINT_CHANGE# FILE#
------------------ ----------
2810026 15
SQL> alter tablespace henry read write; --要生成一个完全检查点的，但是没有业务数据更新的，仅仅是完全检查点的更新
Tablespace altered.
SQL> select checkpoint_change#,file# from v$datafile_header where tablespace_name='HENRY';
CHECKPOINT_CHANGE# FILE#
------------------ ----------
2810098 15 --
SQL> select checkpoint_change#,file# from v$datafile_header;
CHECKPOINT_CHANGE# FILE#
------------------ ----------
2809745 1
2809745 2
2809745 3
2809745 4
2809745 5
2809745 6
2809745 7
2809745 8
2809745 9
2809745 10
2809745 11
2809745 12
2809745 13
2809745 14 --其他的文件无影响
2810098 15
15 rows selected.
###########################################################################################
对一个表空间下的一个数据文件进行离线和在线是检查点的变化情况
移动一个表空间下面的个别数据文件
场景：假设这个表空间由10000个数据文件，只是移动其中的1个文件，整个表空间离线的话，导致其他的数据文件上正在进行事务中断，这个操作会导致客户端程序中的数据丢失，这个在生产是严重的事故。
问题1：是否可以对个别数据文件离线 --可以
问题2：假如离线的话，导致一个表空间下数据文件的头部的SCN不一致，怎么处理？--oracle允许一个表空间下的数据文件头部的SCN是不一致的
问题3：离线的数据文件中的数据如果有用户要修改，数据库会怎么处理。--无法修改，但是其他的数据文件是可以操作的
1、给henry表空间添加一个数据文件
SQL> alter tablespace henry add datafile '/u01/oracle/oradata/ORCL/henry202.dbf' size 300M;
Tablespace altered.
select * from dba_data_files where tablespace_name='HENRY' --文件15和16
select * from user_tables --看到表XXXX和TEST在henry表空间上，这两个表一定在文件15上
2、将文件移动
[oracle@oracle3 ORCL]$ mkdir data
[oracle@oracle3 ORCL]$ pwd
/u01/oracle/oradata/ORCL
将文件15 /u01/oracle/oradata/ORCL/henry201.dbf 移动到data下
3、将数据文件离线
SQL> alter database datafile 15 offline;
Database altered.
SQL> select checkpoint_change#,file# from v$datafile_header where file# in(15,16);
CHECKPOINT_CHANGE# FILE#
------------------ ----------
2814157 15 --检查点是没有变化的，文件15上有脏块没有写入磁盘的话这个操作也是不会写入的。
2814751 16
SQL> select online_status,file_id from dba_data_files where file_id in(15,16);
ONLINE_STATUS FILE_ID
-------------------- ----------
RECOVER 15 --当前文件状态是恢复状态。如果这个文件在离线过程中，刚才离线之前没有写入文件脏块可能会被覆盖掉
ONLINE 16
尝试在henry表空间上做事务
SQL> insert into test select * from test;
insert into test select * from test
*
ERROR at line 1:
ORA-00376: file 15 cannot be read at this time
ORA-01110: data file 15: '/u01/oracle/oradata/ORCL/henry201.dbf' --离线的文件是不能操作的
SQL> create table tu(x int) tablespace henry;
Table created. --这个表一定在文件16上
4、移动这个文件
[oracle@oracle3 ORCL]$ mv henry201.dbf data/
5、更新数据字典
SQL> alter tablespace henry rename datafile '/u01/oracle/oradata/ORCL/henry201.dbf' to '/u01/oracle/oradata/ORCL/data/henry201.dbf';
Tablespace altered.
6、让这个文件15在线
SQL> alter database datafile 15 online;
alter database datafile 15 online
*
ERROR at line 1:
ORA-01113: file 15 needs media recovery --需要恢复，因为离线时脏块信息没有写进文件，文件头也没变，所以离线后的脏块信息有可能改变了，这是就需要通过redo或归档日志来恢复。
ORA-01110: data file 15: '/u01/oracle/oradata/ORCL/data/henry201.dbf'
SQL> select checkpoint_change#,file# from v$datafile_header where file# in(15,16);
CHECKPOINT_CHANGE# FILE#
------------------ ----------
2814157 15
2814751 16
SQL> alter system checkpoint;
System altered.
SQL> select checkpoint_change#,file# from v$datafile_header where file# in(15,16);
CHECKPOINT_CHANGE# FILE#
------------------ ----------
2814157 15 --不管外部检查点怎么变化，文件15的检查点是不会变的。
2815197 16
SQL> recover datafile 15; --用归档文件恢复这个文件
Media recovery complete.
SQL> select checkpoint_change#,file# from v$datafile_header where file# in(15,16);
CHECKPOINT_CHANGE# FILE#
------------------ ----------
2814861 15 --检查点变了。恢复到丢失的事务检查点为止
2815197 16
SQL> alter database datafile 15 online;
Database altered. --强制生成检查点并写入数据文件
SQL> select checkpoint_change#,file# from v$datafile_header where file# in(15,16);
CHECKPOINT_CHANGE# FILE#
------------------ ----------
2815277 15 --有变化，检查点再变，因为online操作会把检查点再更新一次
2815197 16 --无变化，因为没有操作16
###########################################################################################
如果让整个表空间离线，检查点怎样变化？
SQL> alter tablespace henry offline;
Tablespace altered.
SQL> select checkpoint_change#,file# from v$datafile_header where file# in(15,16);
CHECKPOINT_CHANGE# FILE#
------------------ ----------
0 15
0 16 --文件离线，header就看不到了
SQL> select checkpoint_change#,file# from v$datafile where file# in(15,16);
CHECKPOINT_CHANGE# FILE#
------------------ ----------
2815339 15
2815339 16 --这个值是从控制文件中看到的
整个表空间离线的话，是会强制脏块写入的。
SQL> alter tablespace henry online;
Tablespace altered.
SQL> select checkpoint_change#,file# from v$datafile where file# in(15,16);
CHECKPOINT_CHANGE# FILE#
------------------ ----------
2815396 15
2815396 16
检查点会再一次改变，因为online会引发系统事件，生成新的检查点
表空间的离线和在线是会强制写数据文件的，但是单个数据文件离线是不会写的。将来在线的时候用日志来恢复的。
SQL> select checkpoint_change#,file# from v$datafile_header;
CHECKPOINT_CHANGE# FILE#
------------------ ----------
2815224 1
2815224 2
2815224 3
2815224 4
2815224 5
2815224 6
2815224 7
2815224 8
2815224 9
2815224 10
2815224 11
2815224 12
2815224 13
2815224 14
2815396 15
2815396 16 --这个过程是不影响其他的数据文件的
16 rows selected.
尝试将归档关闭，再做这个操作：
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size 1267260 bytes
Variable Size 301992388 bytes
Database Buffers 922746880 bytes
Redo Buffers 32284672 bytes
Database mounted.
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database datafile 15 offline;
alter database datafile 15 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled --不开归档这个操作是没办法做的
因为离线时没有将脏块信息写进数据文件，要在在线是通过redo或者归档日志将已经发生变化的脏块信息写进数据文件，但redo文件只能恢复最近三个redo文件的内容，如果离线时间太长，redo文件的信息将会被覆盖掉，所以必须开启归档模式，来保证当数据文件离线时间过长也能恢复数据。
###########################################################################################
UNDO表空间设置大小的依据
undotablespace = UR*UPS*block_size + 冗余
UR=undo_retention=900s
UPS=整个数据库每秒钟新增的数据块的数量
UPS*block_size=整个数据库每秒钟新增的数据块字节数
UPS=每天数据库的数据增量/24/3600/8192
select tbs,
sum(totalM),
sum(usedM),
sum(remaindM),
round(sum(usedM) / sum(totalM), 2) as usedPer,
round(sum(remaindM) / sum(totalM), 2) as remaindPer
from (select b.FILE_ID id,
b.TABLESPACE_NAME tbs,
b.BYTES / 1024 / 1024 totalM,
(b.BYTES - sum(a.BYTES)) / 1024 / 1024 usedM,
sum(a.BYTES) / 1024 / 1024 remaindM
from dba_free_space a, dba_data_files b
where a.FILE_ID = b.FILE_ID
group by b.FILE_ID, b.TABLESPACE_NAME, b.BYTES
order by b.TABLESPACE_NAME)
group by tbs
计算数据库每天的数据增量求平均值。
undotablespace = 900*（每天数据库的数据增量/24/3600） *1.2
注意：这种计算方式计算的数据增量，数据库以插入操作为主的，如果数据库delete和update操作也很多，这个公式没意义了。
###########################################################################################
ASSM下的高水位线HWM（high water mark）
MSSM方式下，HWM标记段上已经使用和没有使用的数据块的分界线，已经使用的表示已经被格式化过，没有使用过表示没有被格式化。
ASSM下，也是这个定义的，但是稍微有差别。 --ASSM表空间中段空间的自动管理方式
测试：
SQL> drop tablespace henry including contents and datafiles;
Tablespace dropped.
SQL> create tablespace henry datafile '/u01/oracle/oradata/ORCL/henry01.dbf' size 100M extent management local uniform size 128K segment space management auto;
Tablespace created.
SQL> conn scott/scott
Connected.
SQL> create table test tablespace henry as select * from dba_objects;
Table created.
SQL> conn scott/scott
Connected.
SQL> set autotrace trace stat --打开oracle自动跟踪工具
SQL> set linesize 1000
SQL> select count(1) from test;
自动跟踪出来的信息：
Statistics
----------------------------------------------------------
1089 recursive calls
0 db block gets --由于没有修改数据，当前读块次是0
969 consistent gets --一致性读块次
722 physical reads --物理读的块次
0 redo size
413 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
29 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off
SQL> delete from test where rownum<=20000;
20000 rows deleted.
SQL> commit;
Commit complete.
SQL> alter system flush shared_pool; --清掉缓存
System altered.
SQL> alter system flush buffer_cache; --清掉缓存
System altered.
SQL> set autotrace trace stat
SQL> select count(1) from test;
Statistics
----------------------------------------------------------
274 recursive calls
0 db block gets
809 consistent gets
705 physical reads
0 redo size
413 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
数据少了40%，但是IO没有减少？
SQL> set autotrace off
SQL> delete from test;
30822 rows deleted.
SQL> commit;
Commit complete.
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> set autotrace trace stat
SQL> select count(1) from test;
Statistics
----------------------------------------------------------
274 recursive calls
0 db block gets
809 consistent gets
704 physical reads
0 redo size
410 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
数据全删，但是IO还是这么大？
原因在于--delete操作不会使得段的HWM下降，而扫描表是从HWM开始往下扫的。
select * from user_extents where segment_name='TEST' --区是45个
将三级位图块dump出来
SQL> alter system dump datafile 15 block 11;
System altered.
*** 2014-06-27 16:45:06.737
*** ACTION NAME:() 2014-06-27 16:45:06.736
*** MODULE NAME:(SQL*Plus) 2014-06-27 16:45:06.736
*** SERVICE NAME:(SYS$USERS) 2014-06-27 16:45:06.736
*** SESSION ID:(431.14) 2014-06-27 16:45:06.736
Start dump data blocks tsn: 22 file#: 15 minblk 11 maxblk 11
buffer tsn: 22 rdba: 0x03c0000b (15/11)
scn: 0x0000.002ba585 seq: 0x01 flg: 0x04 tail: 0xa5852301
frmt: 0x02 chkval: 0x4915 type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 45 #blocks: 720
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x03c002d6 ext#: 44 blk#: 13 ext size: 16 --HWM在文件15的块726上
select * from dba_extents where owner='SCOTT' and segment_name='TEST' --HWM在最后的区上
#blocks in seg. hdr's freelists: 0
#blocks below: 717
mapblk 0x00000000 offset: 44
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x03c002d6 ext#: 44 blk#: 13 ext size: 16
#blocks in seg. hdr's freelists: 0
#blocks below: 717
mapblk 0x00000000 offset: 44
Level 1 BMB for High HWM block: 0x03c002b9
Level 1 BMB for Low HWM block: 0x03c002b9
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x03c0000a
Last Level 1 BMB: 0x03c002b9
Last Level II BMB: 0x03c0000a
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 45 obj#: 58033 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x03c00009 length: 16
0x03c00019 length: 16
0x03c00029 length: 16
0x03c00039 length: 16
0x03c00049 length: 16
0x03c00059 length: 16
0x03c00069 length: 16
0x03c00079 length: 16
0x03c00089 length: 16
0x03c00099 length: 16
0x03c000a9 length: 16
0x03c000b9 length: 16
0x03c000c9 length: 16
0x03c000d9 length: 16
0x03c000e9 length: 16
0x03c000f9 length: 16
0x03c00109 length: 16
0x03c00119 length: 16
0x03c00129 length: 16
0x03c00139 length: 16
0x03c00149 length: 16
0x03c00159 length: 16
0x03c00169 length: 16
0x03c00179 length: 16
0x03c00189 length: 16
0x03c00199 length: 16
0x03c001a9 length: 16
0x03c001b9 length: 16
0x03c001c9 length: 16
0x03c001d9 length: 16
0x03c001e9 length: 16
0x03c001f9 length: 16
0x03c00209 length: 16
0x03c00219 length: 16
0x03c00229 length: 16
0x03c00239 length: 16
0x03c00249 length: 16
0x03c00259 length: 16
0x03c00269 length: 16
0x03c00279 length: 16
0x03c00289 length: 16
0x03c00299 length: 16
0x03c002a9 length: 16
0x03c002b9 length: 16
0x03c002c9 length: 16
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x03c00009 Data dba: 0x03c0000c
Extent 1 : L1 dba: 0x03c00019 Data dba: 0x03c0001a
Extent 2 : L1 dba: 0x03c00029 Data dba: 0x03c0002a
Extent 3 : L1 dba: 0x03c00039 Data dba: 0x03c0003a
Extent 4 : L1 dba: 0x03c00049 Data dba: 0x03c0004a
Extent 5 : L1 dba: 0x03c00059 Data dba: 0x03c0005a
Extent 6 : L1 dba: 0x03c00069 Data dba: 0x03c0006a
Extent 7 : L1 dba: 0x03c00079 Data dba: 0x03c0007a
Extent 8 : L1 dba: 0x03c00079 Data dba: 0x03c00089
Extent 9 : L1 dba: 0x03c00079 Data dba: 0x03c00099
Extent 10 : L1 dba: 0x03c00079 Data dba: 0x03c000a9
Extent 11 : L1 dba: 0x03c000b9 Data dba: 0x03c000ba
Extent 12 : L1 dba: 0x03c000b9 Data dba: 0x03c000c9
Extent 13 : L1 dba: 0x03c000b9 Data dba: 0x03c000d9
Extent 14 : L1 dba: 0x03c000b9 Data dba: 0x03c000e9
Extent 15 : L1 dba: 0x03c000f9 Data dba: 0x03c000fa
Extent 16 : L1 dba: 0x03c000f9 Data dba: 0x03c00109
Extent 17 : L1 dba: 0x03c000f9 Data dba: 0x03c00119
Extent 18 : L1 dba: 0x03c000f9 Data dba: 0x03c00129
Extent 19 : L1 dba: 0x03c00139 Data dba: 0x03c0013a
Extent 20 : L1 dba: 0x03c00139 Data dba: 0x03c00149
Extent 21 : L1 dba: 0x03c00139 Data dba: 0x03c00159
Extent 22 : L1 dba: 0x03c00139 Data dba: 0x03c00169
Extent 23 : L1 dba: 0x03c00179 Data dba: 0x03c0017a
Extent 24 : L1 dba: 0x03c00179 Data dba: 0x03c00189
Extent 25 : L1 dba: 0x03c00179 Data dba: 0x03c00199
Extent 26 : L1 dba: 0x03c00179 Data dba: 0x03c001a9
Extent 27 : L1 dba: 0x03c001b9 Data dba: 0x03c001ba
Extent 28 : L1 dba: 0x03c001b9 Data dba: 0x03c001c9
Extent 29 : L1 dba: 0x03c001b9 Data dba: 0x03c001d9
Extent 30 : L1 dba: 0x03c001b9 Data dba: 0x03c001e9
Extent 31 : L1 dba: 0x03c001f9 Data dba: 0x03c001fa
Extent 32 : L1 dba: 0x03c001f9 Data dba: 0x03c00209
Extent 33 : L1 dba: 0x03c001f9 Data dba: 0x03c00219
Extent 34 : L1 dba: 0x03c001f9 Data dba: 0x03c00229
Extent 35 : L1 dba: 0x03c00239 Data dba: 0x03c0023a
Extent 36 : L1 dba: 0x03c00239 Data dba: 0x03c00249
Extent 37 : L1 dba: 0x03c00239 Data dba: 0x03c00259
Extent 38 : L1 dba: 0x03c00239 Data dba: 0x03c00269
Extent 39 : L1 dba: 0x03c00279 Data dba: 0x03c0027a
Extent 40 : L1 dba: 0x03c00279 Data dba: 0x03c00289
Extent 41 : L1 dba: 0x03c00279 Data dba: 0x03c00299
Extent 42 : L1 dba: 0x03c00279 Data dba: 0x03c002a9
Extent 43 : L1 dba: 0x03c002b9 Data dba: 0x03c002ba
Extent 44 : L1 dba: 0x03c002b9 Data dba: 0x03c002c9
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x03c0000a
End dump data blocks tsn: 22 file#: 15 minblk 11 maxblk 11
SQL> truncate table test; --truncate有使HWM下降的功能
Table truncated.
select * from dba_extents where owner='SCOTT' and segment_name='TEST' --区变为1个，截断操作可以使HWM下降
SQL> set autotrace trace stat
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> select count(1) from test;
Statistics
----------------------------------------------------------
628 recursive calls
0 db block gets
97 consistent gets --IO明显降低很多
19 physical reads
0 redo size
410 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> conn /as sysdba
Connected.
SQL> alter system dump datafile 15 block 11;
System altered.
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 16
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x03c0000c ext#: 0 blk#: 3 ext size: 16
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x03c0000c ext#: 0 blk#: 3 ext size: 16 --HWM在文件15的块12
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0x03c00009
Level 1 BMB for Low HWM block: 0x03c00009
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x03c0000a
Last Level 1 BMB: 0x03c00009
Last Level II BMB: 0x03c0000a
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 1 obj#: 58034 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x03c00009 length: 16
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x03c00009 Data dba: 0x03c0000c
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x03c0000a
End dump data blocks tsn: 22 file#: 15 minblk 11 maxblk 11
高水位线是Oracle扫描表的起始点，如果删除操作太多导致段空间空间增多，碎片增加，块的状态出现的 75%~100% free 状态太多。考虑回收碎片了。
ASSM下HHWM和LHWM的特点：
1、所有在HHWM之上的数据块都是没有被格式化的。
2、所有在LHWM之下的数据块都是被格式化过的。
3、在HHWM和LHWM之间的数据块可能是格式化过的也可能是没有格式化过的。
4、Oracle扫描表的时候会扫到HHWM为止。
5、CAST方式创建的表，HHWM=LHWM
CAST方式 = create table xxxx as select 。。。。。。。。。
如果LHWM之下出现了未被格式化过的块，oralce访问这个表的时候会报错，ORA-08103错误，查询这个这个表或者给这个表加索引都会报错。
SQL> insert into test select * from all_objects;
50293 rows inserted
SQL> commit;
Commit complete
alter system dump datafile 15 block 11;
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 45 #blocks: 720
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x03c002d9 ext#: 44 blk#: 16 ext size: 16 --
#blocks in seg. hdr's freelists: 0
#blocks below: 701
mapblk 0x00000000 offset: 44
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x03c002d9 ext#: 44 blk#: 16 ext size: 16
#blocks in seg. hdr's freelists: 0
#blocks below: 701
mapblk 0x00000000 offset: 44
Level 1 BMB for High HWM block: 0x03c002b9
Level 1 BMB for Low HWM block: 0x03c002b9
--------------------------------------------------------
SQL> select getfbin('0x03c002d9') from dual;
GETFBIN('0X03C002D9')
--------------------------------------------------------------------------------
datafile is 15
datablock is 729 --HHWM=LHWM=块729上
破坏HHWM之下的数据块，将其设置为未被格式化过的。
[root@oracle3 ~]# dd if=/dev/zero of=/u01/oracle/oradata/ORCL/henry01.dbf bs=8192 seek=714 count=1 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 2.9692e-05 seconds, 276 MB/s
SQL> select count(1) from test; --此时由于缓存的作用，查询是不报错的
COUNT(1)
----------
50293
SQL> alter system flush buffer_cache; --清理缓存后。
System altered.
SQL> select count(1) from test;
select count(1) from test
*
ERROR at line 1:
ORA-08103: object no longer exists
唯一修复的办法是恢复这个数据块。备份文件+日志文件。 一定要备份文件存在，否则这个数据是恢复不了的。
SQL> truncate table test;
Table truncated. --此时HWM再块714之下
SQL> select count(1) from test;
COUNT(1)
----------
0
###########################################################################################
表空间收缩--shrink方法
原理--对表做一系列的dml操作（产生大量的redo），删除表末端的稀疏行，在表的底端重新插入，填满漏洞空间，逐步将剩余的空间留在表的末端，oracle重置表的HWM，释放自由空间。在操作的过程中，表的行上会暂时出现行级排它锁，某些行的操作会受到影响，大部分行是不受影响的，所以从全局上看，shrink操作不影响用户访问。
注意：数据量很大的表，年久失修，长时间没有做碎片回收，shrink操作时间就会很长，归档很多，操作前需要评估。
SQL> insert into test select * from dba_objects;
50933 rows inserted
SQL> commit;
Commit complete
SQL> alter system dump datafile 15 block 11;
System altered.
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 45 #blocks: 720
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x03c002d9 ext#: 44 blk#: 16 ext size: 16
#blocks in seg. hdr's freelists: 0
#blocks below: 701
mapblk 0x00000000 offset: 44
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x03c002d9 ext#: 44 blk#: 16 ext size: 16
#blocks in seg. hdr's freelists: 0
#blocks below: 701
mapblk 0x00000000 offset: 44
Level 1 BMB for High HWM block: 0x03c002b9
Level 1 BMB for Low HWM block: 0x03c002b9
--------------------------------------------------------
SQL> select getfbin('0x03c002d9') from dual;
GETFBIN('0X03C002D9')
--------------------------------------------------------------------------------
datafile is 15
datablock is 729 --HWM
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> set autotrace trace stat
SQL> select count(1) from test;
Statistics
----------------------------------------------------------
217 recursive calls
0 db block gets
731 consistent gets
705 physical reads
0 redo size
413 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> delete from test where rownum<=10000;
10000 rows deleted
SQL> commit;
Commit complete
SQL> delete from test where rownum<=10000; --段上出现“漏洞”
10000 rows deleted
SQL> commit;
Commit complete
SQL> alter system flush shared_pool;
System altered.
SQL> select count(1) from test;
Statistics
----------------------------------------------------------
1035 recursive calls
0 db block gets
891 consistent gets
723 physical reads
0 redo size
413 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
29 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter table test enable row movement;
Table altered. --行要在物理上发生移动的
SQL> alter table test shrink space; --企业环境中这个操作要花费很长的时间。怎么处理这种环境。
Table altered.
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> select count(1) from test;
Statistics
----------------------------------------------------------
225 recursive calls
0 db block gets
461 consistent gets
437 physical reads
0 redo size
413 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
select * from dba_extents where segment_name='TEST' and owner='SCOTT' --shrink之后有28个区
SQL> alter system dump datafile 15 block 11;
System altered.
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 28 #blocks: 448
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x03c001c7 ext#: 27 blk#: 14 ext size: 16
#blocks in seg. hdr's freelists: 0
#blocks below: 431
mapblk 0x00000000 offset: 27
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x03c001c7 ext#: 27 blk#: 14 ext size: 16
#blocks in seg. hdr's freelists: 0
#blocks below: 431
mapblk 0x00000000 offset: 27
Level 1 BMB for High HWM block: 0x03c001b9
Level 1 BMB for Low HWM block: 0x03c001b9
SQL> select getfbin('0x03c001c7') from dual;
GETFBIN('0X03C001C7')
--------------------------------------------------------------------------------
datafile is 15
datablock is 455 --证明HWM确实是下降了。
---------------------------------------------------------------------------------------------------------------------------
企业环境中--规模很大的表--用shrink操作是不现实
规模小的表--用shrink
思路--1、要达到什么样的状况才考虑碎片的回收？ --二八原则：20%碎片率
2、在有限的时间窗口内怎么设计这个回收方案？ --分工
3、怎样匹配现有技术团队参与这个方案？每个团队对于这个数据库的操作权限是不一样的。
团队分工：A 开发组（项目组）--对数据库权限是相对比较低，仅仅要求对项目所创建的用户SCOTT，下的对象的操作权限，包括表、SP开发，数据的抽取。
B DBA组--不关注业务细节，对数据库上所有的用户进行监控和维护，对数据库操作权限是比较大的。
C 优化组--较为关注业务细节，对业务上的SQL语句进行全面优化：执行计划的调试，统计数据的收集策略。
D 审计组--对ABC三组人员的行为进行监控和审计。
对于用户小表碎片回收--A组做。--直接开发SP，用用表过滤出小数据量、业务相关性弱的表，直接shrink
大型的业务表，流水账，设计到财务的数据--DBA组--此时需要用数据泵导入导出，需要相对大的权限操作表空间，所以才去导出，重建表，导入的方式。
D组监控
dbms_space.space_usage --这个是判断是否要重建的依据
###########################################################################################
每个表每一行都有一个rowid
select t.*,t.rowid from test t --每个表的每行都有一个rowid，可以理解为行的物理地址或者主键，行的物理地址发生了移动，rowid要变化。
行移动功能打开目的是为了让rowid变化。rowid是不存储在表空间中的，仅仅查询的时候根据对象号、行号、块号、文件号、行类型用一个加密的算法的函数得出rowid结果。
根据rowid反解出对象号、行号、块号、文件号、行类型：
create or replace function get_rowid(l_rowid in varchar2) return varchar2 is
ls_my_rowid varchar2(150);
row_type number;
obj_number number;
file_number number;
blk_number number;
row_number number;
begin
dbms_rowid.rowid_info(l_rowid,
row_type,
obj_number,
file_number,
blk_number,
row_number);
ls_my_rowid := 'row type is ' || to_char(row_type) || chr(10) ||
'object# is ' || to_char(obj_number) || chr(10) ||
'file# is ' || to_char(file_number) || chr(10) ||
'blk# is ' || to_char(blk_number) || chr(10) ||
'row# is ' || to_char(row_number);
return ls_my_rowid;
exception when others then
return 'error!';
end;
调用
SQL> select get_rowid('AAAOOhAAPAAAAAMAAD') from dual;
GET_ROWID('AAAOOHAAPAAAAAMAAD'
--------------------------------------------------------------------------------
row type is 1
object# is 58273
file# is 15
blk# is 12
row# is 3
select * from dba_objects where data_object_id=58273 --查询对象号
由于5个值联合起来不会重复，所以rowid是不会有重复值，可以认为是一个物理地址。
###########################################################################################
回收表空间碎片的move方法
回收表空间碎片的move方法，相对比较快，但是引发的影响比较大,会导致索引失效。
9i中用的比较多，但是随着数据量加大，这个方法用的场合越来越少。
原理--表表所有行从一个物理位置移动到另外一个物理位置，移动的过程中回收碎片。在同一个表空间内完成。
要求当前表的表空间必须有一倍于当前表存储大小的free空间。行所有的rowid都会变化。
SQL> insert into test select * from dba_objects;
50934 rows inserted
SQL> commit;
Commit complete
SQL> delete from test where rownum<=10000;
10000 rows deleted
SQL> delete from test where rownum<=10000;
10000 rows deleted
SQL> commit;
Commit complete
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select count(1) from test;
Statistics
----------------------------------------------------------
1035 recursive calls
0 db block gets
1335 consistent gets
1163 physical reads
0 redo size
413 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
29 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> delete from test where rownum<=15000;
15000 rows deleted
SQL> delete from test where rownum<=20000;
20000 rows deleted
SQL> commit;
Commit complete
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> select count(1) from test;
Statistics
----------------------------------------------------------
225 recursive calls
0 db block gets
1177 consistent gets
1147 physical reads
0 redo size
413 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter table test disable row movement;
Table altered.
SQL> alter table test move; --不打开行移动功能也是可以的
Table altered.
select * from dba_extents where segment_name='TEST' and owner='SCOTT' --可以看到extent的块号都变了
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> select count(1) from test;
Statistics
----------------------------------------------------------
1035 recursive calls
0 db block gets
563 consistent gets
399 physical reads
116 redo size
413 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
29 sorts (memory)
0 sorts (disk)
1 rows processed
但是这里还有一个问题：move操作由于会改变表的rowid，而表中如果有索引的话（索引中是要存储rowid），所有的索引会失效，此时我们需要重建索引，重建索引的代价在数据库中是很高的，重建索引的时间（对于大表来说）很长，这种方法要慎用
总结：1、空间损耗 2、rowid的失效而引发的其他问题。
#########################################################################################
SYSAXU表空间
SYSAXU表空间--辅助表空间，对应的管理用户是SYSMAN，也属于系统用户。
管理范畴--oralce其他组件：logminer等。oracle组件用SYSMAN用户管理，耗用的表空间是SYSAUX
select * from v$sysaux_occupants --oracle初始化的组件有26个，
SQL> desc v$sysaux_occupants
Name Null? Type
----------------------------------------- -------- ----------------------------
OCCUPANT_NAME VARCHAR2(64) --组件名
OCCUPANT_DESC VARCHAR2(64) --组件的功能描述
SCHEMA_NAME VARCHAR2(64) --所属的用户
MOVE_PROCEDURE VARCHAR2(64) --移动使用表空间使用的SP：SYSAUX如果不足的话，可以将组件使用的表空间移动到其他表空间上，移动的方法，oracle给我们封装了对应的存储过程。
MOVE_PROCEDURE_DESC VARCHAR2(64) --移动方法的描述
SPACE_USAGE_KBYTES NUMBER --当前该组件使用这个表空间SYSAUX的大小
SQL> exec SYS.DBMS_LOGMNR_D.SET_TABLESPACE('MYTBS4');
PL/SQL procedure successfully completed.
select * from v$sysaux_occupants --发现 SPACE_USAGE_KBYTES变为0，表示在SYSAUX上不用空间了
转载于:https://blog.51cto.com/fengsonglin/1615429
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
