oracle tablespace info

/*表空间的作用
1.决定数据库实体的空间分配;
2.设置数据库用户的空间份额;
3.控制数据库部分数据的可用性;
4.分布数据于不同的设备之间以改善性能;
5.备份和恢复数据。*/

--建立表空间--语法格式:
create [undo] tablespace tablespace_name
[datafile ['filename'] [size integer [ k | m ]] [reuse] [autoextend { off | on [ next integer [ k | m ] ] [maxsize { unlimited | integer [ k | m ] }] }]
[ { mininum extent integer [k|m]
| blocksize integer [k]
|logging|nologing] 
|force logging
|default {data_segment_compression} storage_clause
|[online|offline]
|[permanent|temporary]
|extent_manager_clause
|segment_manager_clause}]

uniform size 128k;--指定区尺寸为128k,如不指定，区尺寸默认为64k

undo --说明系统将创建一个回滚表空间。
--在9i中数据库管理员可以不必管理回滚段，只有建立了undo表空间，系统就会自动管理回滚段的分配，回收的工作。当然,也可以创建一般的表空间,在上面创建回滚段.不过对于用户来说,系统管理比自己管理要好很多.如果需要自己管理,请参见回滚段管理的命令详解.
--当没有为系统指定回滚表空间时,系统将使用system系统回滚段来进行事务管理。

2、tablespace_name --指出表空间的名称。

3、datafile datefile_spec1--指出表空间包含什么空间文件。
datefile_spec1 是形如 ['filename'] [size integer [ k | m ]] [reuse] [autoextend_clause]
[autoextend_clause]是形如： autoextend { off | on [ next integer [ k | m ] ] [maxsize_clause] }
其中filename是数据文件的全路径名，
size是文件的大小,
reuse表示文件是否被重用.
autoextend表明是否自动扩展. off | on 表示自动扩展是否被关闭.
next 表示数据文件满了以后,扩展的大小.
maxsize_clause表示数据文件的最大大小.形如maxsize { unlimited | integer [ k | m ] }.
unlimited 表示无限的表空间.
integer是数据文件的最大大小.

datafile 'd:\oracle\oradata\imagedata01.dbf' size 2000m,
'd:\oracle\oradata\imagedata02.dbf' size 2000m

4、mininum extent integer [k|m]
指出在表空间中范围的最小值。这个参数可以减小空间碎片，保证在表空间的范围是这个数值的整数倍。

5、blocksize integer [k]--这个参数可以设定一个不标准的块的大小。
如果要设置这个参数，必须设置db_block_size,至少一个db_nk_block_size,并且声明的integer的值必须等于db_nk_block_size.
注意：在临时表空间不能设置这个参数。

6、logging有nologging和logging两个选项,
这个子句声明这个表空间上所有的用户对象的日志属性（缺省是logging），包括表，索引，分区，物化视图，物化视图上的索引，分区。

nologging:创建表空间时,不创建重做日志.
logging和nologging正好相反,就是在创建表空间时生成重做日志.
用nologging时,好处在于创建时不用生成日志,这样表空间的创建较快,但是没能日志,数据丢失后,不能恢复;
但是一般我们在创建表空间时,是没有数据的,按通常的做法,是建完表空间,并导入数据后,是要对数据做备份的;
所以通常不需要表空间的创建日志,因此,在创建表空间时,选择nologging,以加快表空间的创建速度.

force logging
使用这个子句指出表空间进入强制日志模式。此时，系统将记录表空间上对象的所有改变，除了临时段的改变。这个参数高于对象的nologging选项。
注意：设置这个参数数据库不行open并且出于读写模式。而且，在临时表空间和回滚表空间中不能使用这个选项。

default storage_clause
声明缺省的存储子句。

online|offline
改变表空间的状态。online使表空间创建后立即有效.这是缺省值.offline使表空间创建后无效.这个值，可以从dba_tablespace中得到。

permanent|temporary
指出表空间的属性，是永久表空间还是临时表空间。永久表空间存放的是永久对象，临时表空间存放的是session生命期中存在的临时对象。这个参数 生成的临时表空间创建后一直都是字典管理，不能使用extent management local选项。如果要创建本地管理表空间，必须使用create temporary tablespace
注意，声明了这个参数后，不能声明block size

extent_management_clause
这是最重要的子句，说明了表空间如何管理范围。一旦你声明了这个子句，只能通过移植的方式改变这些参数。
如果希望表空间本地管理的话，声明local选项。本地管理表空间是通过位图管理的。autoallocate说明表空间自动分配范围，用户不能指定范围的大小。只有9.0以上的版本具有这个功能。uniform说明表空间的范围的固定大小，缺省是1m。
不能将本地管理的数据库的system表空间设置成字典管理。
oracle公司推荐使用本地管理表空间。
如果没有设置extent_management_clause，oracle会给他设置一个默认值。如果初始化参数compatible小于9.0.0,那么系统创建字典管理表空间。如果大于9.0.0,那么按照如下设置：
如果没有指定default storage_clause,oracle创建一个自动分配的本地管理表空间。
否则，如果指定了mininum extent,那么oracle判断mininum extent 、initial、next是否相等,以及pctincrease是否=0.如果满足以上的条件，oracle创建一个本地管理表空间，extent size是initial.如果不满足以上条件，那么oracle将创建一个自动分配的本地管理表空间。
如果没有指定mininum extent。initial、那么oracle判断next是否相等,以及pctincrease是否=0。如果满足oracle创建一个本地管理表空间并指定uniform。否则oracle将创建一个自动分配的本地管理表空间。
注意：本地管理表空间只能存储永久对象。如果你声明了local,将不能声明default storage_clause,mininum extent、temporary.
extent management local

第四:extent management local存储区管理方法
在字典中管理(dictionary):
将数据文件中的每一个存储单元做为一条记录,所以在做dm操作时,就会产生大量的对这个管理表的delete和update操作.
做大量数据管理时,将会产生很多的dm操作,严得的影响性能,同时,长时间对表数据的操作,会产生很多的磁盘碎片.
本地管理(local):
用二进制的方式管理磁盘,有很高的效率,同进能最大限度的使用磁盘.同时能够自动跟踪记录临近空闲空间的情况，避免进行空闲区的合并操作。

12、segment_management_clause
segment space management auto 
第五:segment space management
磁盘扩展管理方法:
segment space management:使用该选项时区大小由系统自动确定。由于oracle可确定各区的最佳大小，所以区大小是可变的。
uniform segment space management:指定区大小，也可使用默认值(1mb)。

第六:段空间的管理方式:
auto:只能使用在本地管理的表空间中.
使用local管理表空间时,数据块中的空闲空间增加或减少后，其新状态都会在位图中反映出来。
位图使oracle管理空闲空间的行为更加自动化，并为管理空闲空间提供了更好的性,但对含有lob字段的表不能自动管理.
manual:目前已不用,主要是为向后兼容.
*/

--创建三种类型的表空间
--1.temporary:临时表空间,用于临时数据的存放;
create temporary tablespace temp1 
tempfile '/u01/app/oracle/oradata/orcl/temp11.dbf' size 10m;
--2.undo:还原表空间.用于存入重做日志文件.
create undo tablespace "sample"......
--3.用户表空间:最重要,也是用于存放用户数据表空间
create tablespace "sample"......

--创建大文件表空间
create bigfile tablespace 表名
datafile 'd:\ndo\ddo\表名.dbf‘
size 500m autoextend on;

/*发现表空间文件容量与db_block_size有关，在初始建库时，db_block_size要根据实际需要，设置为4k,8k、16k、32k、64k等几种大小，oracle的物理文件最大只允4194304个块（由操作系统决定），smallfile tablespace表空间文件的最大值为4194304×db_block_size/1024m。
即：
4k最大表空间为：16384m=16g
8k最大表空间为：32768m=32g
16k最大表空间为：65536m=64g
32k最大表空间为：131072m=128g
64k最大表空间为：262144m=256g*/

//---------------------------------------------------------------------
oracle bigfile tablespace 大文件表空间 ----------------------------
/*oracle 10g 新增的表空间类型：大文件 (bigfile) 表空间。 　　 　　
大文件表空间从某种角度来说提高了 oracle 在 vldb 上的管理能力。 
只有自动段空间管理的 lmt (locally managed tablespaces ) 支持 bigfile 表空间。 
大文件表空间只能包含一个文件，但是文件可以达到 4g 个数据块大小。
(以下用 bft 指代 bigfile tablespace。bft 可以和以下存储技术结合使用：
自动存储管理(asm) 　　lvm 　　omf 　　
理论上的 bft 可以达到下面所列的值：　　 　　
数据块大小(单位：k) bft 最大值(单位：t) 　　
2k 8t 　　
4k 16t 　　
8k 32t 　　
16k 64t 　　
32k 128t　　 　　
在实际环境中，这还受到操作系统的文件系统的限制。 　　 　　

bft基本操作 　　 　　
10g 数据库在创建的时候，会指定默认的表空间类型。
如果不特殊指定的话，默认为 smallfile 类型的表空间。 　　 　　
select * from database_properties where property_name = 'default_tbs_type'; 
这种情况下，如果我们创建表空间的时候不指定类型，那么默认创建的都是 smallfile 类型的表空间。*/

--修改数据库默认的表空间类型
--可以通过 alter database 命令来修改数据库默认的表空间类型： 　　 　　
alter database set default bigfile tablespace; 　

select * from database_properties where property_name = 'default_tbs_type'; 　　 　　
alter database set default smallfile tablespace; 　　 　　
--创建 bigfile 类型的表空间，只需指定额外的一个参数 bigfile 即可，其他和原有创建表空间语法类似： 　　 　　
create bigfile tablespace bftbs 　datafile '/u01/app/oracle/oradata/demo/bftbs01.dbf' size 5m; 　
--dba_tablespaces (user_tablespaces)与 v$tablespace 这两个视图可以查看 bigfile --表空间的相关信息。
--先看看 dba_tablespaces 在 10g 中有了什么变化： 　　 　　
desc dba_tablespaces 　　　

/*和 9i 相比，dba_tablespaces 视图多了两列：retention 和 bigfile。
其中 bigfile 列说明该表空间是否为 bft：*/
select tablespace_name, bigfile from dba_tablespaces;

--v$tablespace 视图相对 9i 也增加了新的列： 　
desc v$tablespace name
--其中 flashback_on 和 bigfile 列都是新增的。 　　 　　

/*bft 属性 　　 　　
bft有一些特有的属性。 　　 　　
1.每个表空间只能包含一个数据文件。如果试图添加新的文件，则会报告 ora-32771 错误：*/ 　　 　　
alter tablespace bftbs 　
add datafile '/u01/app/oracle/oradata/demo/bftbs02.dbf' size 5m; 　　
alter tablespace bftbs 　　

--* error at line 1: 　　
--ora-32771: cannot add file to bigfile tablespace 　　 　　

--只有自动段空间管理的 lmt (locally managed tablespaces ) 支持 bft 　　 　　
create bigfile tablespace bftbs02 　　
datafile '/u01/app/oracle/oradata/demo/bftbs02.dbf' size 5m 　　
extent management dictionary; 　　

create bigfile tablespace bftbs02 　　
--* error at line 1: 　　
--ora-12913: cannot create dictionary managed tablespace 　　 　　

create bigfile tablespace bftbs02 　　
datafile '/u01/app/oracle/oradata/demo/bftbs02.dbf' size 5m 　
segment space management manual; 　　

create bigfile tablespace bftbs02 
--* error at line 1: 　　
--ora-32772: bigfile is invalid option for this type of tablespace 　　 　　

--相对文件号(relative_fno)为1024 ( 4096 on os/390)因为bft只有一个数据文件，所以其相对文件号也是固定的:1024 　　 　　
select tablespace_name, file_id, relative_fno from dba_data_files; 　　 　　

--rowid的变化 　　 　　
/*在 bft 上存储的表的 rowid 与 smallfile 表空间上的 rowid 结构有些不同的。
要正确得到 rowid 信息，dbms_rowid 包增加了一个新的参数　ts_type_in　来解决这个问题。*/
--参考这个范例： 　　 　　
select dbms_rowid.rowid_block_number (rowid, 'bigfile') from foo; 
　
--你可以创建多大的表空间? 　　 　　
--我们在前面提及，bft 还受到操作系统的文件系统的限制。
--下面我们以 linux 操作系统为例： 　　 　　
show parameters db_block_size 　 　　

db_block_size　　integer　　 8192 　　
--也就是说，理论上我们可以创建最大 32t

--修改表空间配置--语法格式:
alter	tablespce	表空间名
(add	datafile 文件标识符[,文件标识符]...
|rename datafile ’文件名’ [,’文件名’]...
to ’文件名’ [,’文件名’]...
|default storage(存储配置参数)
|online|offline[normal|immediate]
|(begin|end)backup);

/*注：temporary和undo表空间是oracle管理的特殊的表空间.只用于存放系统相关数据.
oracle创建表空间应该授予的权限
1.被授予关于一个或多个表空间中的resource特权;
2.被指定缺省表空间;
3.被分配指定表空间的存储空间使用份额;
4.被指定缺省临时段表空间。*/

select tablespace_name "表空间名称",status "状态",extent_management "区管理方式",
allocation_type "磁盘扩展管理方式", segment_space_management"段管理方式"
from dba_tablespaces;

--使表空间脱机
alter table space game offline;

--如果是意外删除了数据文件，则必须带有recover选项
alter table space game offline for recover;

--使表空间联机
alter table space game online;

--使数据文件脱机
alter database datafile 3 offline;

--使数据文件联机
alter database datafile 3 online;

--使表空间只读
alter table space game read only;

--使表空间可读写
alter table space game read write;

--缩小临时表空间大小
alter database tempfile 'd:\oracle\product\10.2.0\oradata\telemt\temp01.dbf' resize 100m;

--首先查看表空间的名字和所属文件
select tablespace_name,file_id,file_name,round(bytes/(1024*1024),0) total_space 
from dba_data_files
order by tablespace_name;

--增加数据文件
alter tablespace game
add datafile '/oracle/oradata/db/game02.dbf' size 1000m;

--手动增加数据文件尺寸
alter database datafile '/oracle/oradata/db/game.dbf' resize 4000m;

--设定数据文件自动扩展
alter database 
datafile '/oracle/oradata/db/game.dbf' 
autoextend on 
next 100m 
max size 10000m;

--增大临时文件大小：
alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ resize 100m;

--将临时数据文件设为自动扩展：
alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ autoextend on next 5m maxsize unlimited;

--向临时表空间中添加数据文件：
alter tablespace temp add tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ size 100m;

--删除表空间语法格式:
drop tablespace 表空间名 [including contents[and datafile]];

--删除临时表空间
--删除临时表空间的一个数据文件：
alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ drop;
--删除临时表空间(彻底删除)：
drop tablespace temp1 including contents and datafiles cascade constraints;

--临时表空间组介绍
--创建临时表空间组：
create temporary tablespace tempts1 tempfile '/home/oracle/temp1_02.dbf' size 2m tablespace group group1;
create temporary tablespace tempts2 tempfile '/home/oracle/temp2_02.dbf' size 2m tablespace group group2;

--查询临时表空间组：dba_tablespace_groups视图
select * from dba_tablespace_groups;
group_name tablespace_name
------------------------------ ------------------------------
group1 tempts1
group2 tempts2

--将表空间从一个临时表空间组移动到另外一个临时表空间组：
alter tablespace tempts1 tablespace group group2 ;
select * from dba_tablespace_groups;

group_name tablespace_name
------------------------------ ------------------------------
group2 tempts1
group2 tempts2

--把临时表空间组指定给用户
alter user scott temporary tablespace group2;

--在数据库级设置临时表空间
alter database <db_name> default temporary tablespace group2;

--删除临时表空间组 (删除组成临时表空间组的所有临时表空间)
drop tablespace tempts1 including contents and datafiles;
select * from dba_tablespace_groups;

group_name tablespace_name
------------------------------ ------------------------------
group2 tempts2

drop tablespace tempts2 including contents and datafiles;
select * from dba_tablespace_groups;
group_name tablespace_name

--对临时表空间进行shrink（11g新增的功能）
--将temp表空间收缩为20m
alter tablespace temp shrink space keep 20m; 
--自动将表空间的临时文件缩小到最小可能的大小
alter tablespace temp shrink tempfile ’/u02/oracle/data/lmtemp02.dbf’;

--临时表空间作用
/*oracle临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。
重启数据库可以释放临时表空间，如果不能重启实例，而一直保持问题sql语句的执行，temp表空间会一直增长。直到耗尽硬盘空间。
网上有人猜测在磁盘空间的分配上，oracle使用的是贪心算法，如果上次磁盘空间消耗达到1gb，那么临时表空间就是1gb。
也就是说当前临时表空间文件的大小是历史上使用临时表空间最大的大小。

临时表空间的主要作用：
索引create或rebuild；
order by 或 group by；
distinct 操作；
union 或 intersect 或 minus；
sort-merge joins；
analyze。*/

--检查表空间使用情况
--检查当前用户空间分配情况
select tablespace_name,sum(extents),sum(blocks),sum(bytes) 
from user_segments group by tablespace_name

--检查各用户空间分配情况
select owner,tablespace_name,sum(extents),sum(blocks),sum(bytes) 
from dba_segments
group by owner,tablespace_name;

--检查当前用户数据库实体空间使用情况
select tablespace_name,segment_name,segment_type,count(extent_id),sum(blocks),sum(bytes) 
from user_extents
group by tablespace_name,segment_name,segment_type;

--检查各用户空间使用情况
select owner,tablespace_name,count(extent_id),sum(blocks),sum(bytes) 
from user_extents group by owner,tablespace_name;

--检查数据库空间使用情况
select tablespace_name,count(extent_id),sum(blocks),sum(bytes)
from user_extents
group by tablespace_name;

--检查当前用户自由空间情况
select tablespace_name,count(block_id),sum(blocks),sum(bytes) 
from user_free_space group by tablespace_name;

--检查数据库自由空间情况
select tablespace_name,count(block_id),sum(blocks),sum(bytes) from dba_free_space group by tablespace_name;
select segment_name,bytes/1024/1024 m from user_segments where segment_name like 'test_tb%';

--查询表空间位置及大小 
select tablespace_name,file_id,file_name,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;

--查看剩余表空间大小
select sum(bytes)/(1024*1024) as free_space,tablespace_name from dba_free_space group by tablespace_name;

--查看表空间大小
select t.tablespace_name,round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t,dba_data_files d
where t.tablespace_name=d.tablespace_name
group by t.tablespace_name;

--表空间使用情况 
--(1) 
select f.tablespace_name "表空间名",filenum,d.total "表空间大小(m)",d.total-f.free_total "已使用空间(m)",
to_char(round(f.free_total*100/d.total,2), '990.00') "空闲比%", 
to_char(round((d.total-f.free_total)/d.total*100,2),'990.99') "使用比%",
f.free_total "空闲空间(m)",f.max_bytes "最大块(m)",round(maxsizes, 2) "max (mb)",largest "最大扩展段(m)", 
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "采样时间"
from (select tablespace_name,round(sum(bytes)/(1024*1024),2) free_total,round(max(bytes)/(1024*1024),2) max_bytes
from sys.dba_free_space 
group by tablespace_name) f,
(select dd.tablespace_name,count(file_id) filenum,sum(maxbytes)/1024/1024 maxsizes,round(sum(dd.bytes)/(1024*1024),2) total
from sys.dba_data_files dd
group by dd.tablespace_name) d,
(select round(max(ff.length)*16/1024,2) largest,ts.name tablespace_name 
from sys.fet$ ff,sys.file$ tf,sys.ts$ ts 
where ts.ts#=ff.ts# 
and ff.file#=tf.relfile# 
and ts.ts#=tf.ts# 
group by ts.name, tf.blocks) c
where d.tablespace_name=f.tablespace_name 
and d.tablespace_name = c.tablespace_name(+)
order by 4 desc;

--(2)
col tablespace_name format a20;
select b.file_id file_id,b.tablespace_name tablespace_name,b.bytes bytes,(b.bytes-sum(nvl(a.bytes,0))) used,
sum(nvl(a.bytes,0)) free,sum(nvl(a.bytes,0))/(b.bytes)*100 percent 
from dba_free_space a,dba_data_files b 
where a.file_id=b.file_id 
group by b.tablespace_name,b.file_id,b.bytes 
order by b.file_id;

--(3)
select a.tablespace_name,a.bytes total,b.bytes used,c.bytes free,
(b.bytes*100)/a.bytes "% used",(c.bytes*100)/a.bytes "% free"
from sys.sm$ts_avail a,sys.sm$ts_used b,sys.sm$ts_free c
where a.tablespace_name=b.tablespace_name 
and a.tablespace_name=c.tablespace_name;

--(4)
column tablespace_name format a18;
column sum_m format a12;
column used_m format a12;
column free_m format a12;
column pto_m format 9.99;
select s.tablespace_name,ceil(sum(s.bytes/1024/1024))||'m' sum_m,ceil(sum(s.usedspace/1024/1024))||'m' used_m,
ceil(sum(s.freespace/1024/1024))||'m' free_m,sum(s.usedspace)/sum(s.bytes) ptused
from (select b.file_id,b.tablespace_name,b.bytes,(b.bytes-sum(nvl(a.bytes,0))) usedspace,
sum(nvl(a.bytes,0)) freespace,(sum(nvl(a.bytes,0))/(b.bytes))*100 freepercentratio 
from sys.dba_free_space a,sys.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) s 
group by s.tablespace_name 
order by sum(s.freespace)/sum(s.bytes) desc;

--查看临时表空间大小（dba_temp_files视图）（v_$tempfile视图）
select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
select status,enabled, name, bytes/1024/1024 file_size from v_$tempfile;--sys用户查看

--查看临时表空间的使用情况（gv_$temp_space_header视图必须在sys用户下才能查询）
--gv_$temp_space_header视图记录了临时表空间的使用大小与未使用的大小
--dba_temp_files视图的bytes字段记录的是临时表空间的总大小
select temp_used.tablespace_name,total - used as "free",total as "total",round(nvl(total - used, 0) * 100 / total, 3) "free percent"
from (select tablespace_name, sum(bytes_used) / 1024 / 1024 used from gv_$temp_space_header group by tablespace_name) temp_used,
(select tablespace_name, sum(bytes) / 1024 / 1024 total from dba_temp_files group by tablespace_name) temp_total
where temp_used.tablespace_name = temp_total.tablespace_name

--查看当前临时表空间使用大小与正在占用临时表空间的sql语句
select sess.sid,segtype,blocks*8/1000 "mb",sql_text
from v$sort_usage sort,v$session sess,v$sql sql
where sort.session_addr=sess.saddr
and sql.address=sess.sql_address
order by blocks desc;

--数据库对象下一扩展与表空间的free扩展值的检查：
select a.table_name,a.next_extent,a.tablespace_name
from all_tables a,(
select tablespace_name,max(bytes) as big_chunk from dba_free_space group by tablespace_name) f 
where f.tablespace_name=a.tablespace_name 
and a.next_extent>f.big_chunk
union 
select a.index_name,a.next_extent,a.tablespace_name
from all_indexes a,(
select tablespace_name,max(bytes) as big_chunk from dba_free_space group by tablespace_name) f 
where f.tablespace_name=a.tablespace_name 
and a.next_extent>f.big_chunk;

--查看无法扩展的段
/*oracle对一个段比如表段或索引无法扩展时，取决的并不是表空间中剩余的空间是多少，
而是取于这些剩余空间中最大的块是否够表比索引的“next”值大，所以有时一个表空间剩余几个g的空闲空间，
在你使用时oracle还是提示某个表或索引无法扩展，就是由于这一点，这时说明空间的碎片太多了。
这个脚本是找出无法扩展的段的一些信息。*/
select segment_name,segment_type,owner,a.tablespace_name "tablespacename",initial_extent/1024 "inital_extent(k)",
next_extent/1024 "next_extent(k)",pct_increase,b.bytes/1024 "tablespace max free space(k)",
b.sum_bytes/1024 "tablespace total free space(k)"
from dba_segments a,
(select tablespace_name,max(bytes) bytes,sum(bytes) sum_bytes from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
and next_extent>b.bytes
order by 4,3,1;

--查看wacos表空间内的索引的扩展情况：
select substr(segment_name,1,20) "segment name",bytes,count(bytes)
from dba_extents where segment_name 
in (select index_name from dba_indexes where tablespace_name ='wacos')
group by segment_name,bytes 
order by segment_name;

--查看wacos表空间下所有的索引:
select 'analyze index'||segment_name||'validate strstructure;'
from dba_segments 
where tablespace_name='wacos'
and segment_type='index';

--监控表空间的 i/o 比例 
select df.tablespace_name name,df.file_name "file",f.phyrds pyr,f.phywrts pyw,f.phyblkrd pbr,f.phyblkwrt pbw 
from v$filestat f,dba_data_files df 
where f.file#=df.file_id 
order by df.tablespace_name;

--查看表空间数据文件的读写性能:
select name,phyrds,phywrts,avgiotim,miniotim,maxiowtm,maxiortm 
from v$filestat,v$datafile 
where v$filestat.file#=v$datafile.file#;

select fs.name name,f.phyrds,f.phywrts,f.phyblkrd,f.phyblkwrt,f.readtim,f.writetim
from v$filestat f,v$datafile fs 
where f.file#=fs.file# 
order by fs.name;
--(注意：如果phyblkrd与phyrds很接近的话，则表明这个表空间中存在全表扫描的表，这些表需要调整索引或优化sql语句)
--尽量不要在system表空间做与系统无关的操作，应给各个用户建立单独的表空间。

--转换表空间为local方式管理:
exec sys.dbms_space_admin.tablespace_migrate_to_local('tbs_test');

--判断表空间碎片：（如果最大空闲空间占总空间很大比例则可能不存在碎片，如果比例较小，且有许多空闲空间，则可能碎片很多）
select t.tablespace_name,sum(t.bytes),max(t.bytes),count(*),max(t.bytes)/sum(t.bytes) radio
from dba_free_space t
group by t.tablespace_name
order by t.tablespace_name

--统计各个类别的表的个数
select tablespace_name,count(tablespace_name)
from dba_free_space 
group by tablespace_name 
having count(tablespace_name)>10;

alter tablespace name coalesce;
alter table table_name deallocate unused;

create or replace view ts_blocks_v 
as
select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space 
union all
select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;

select * from ts_blocks_v;
select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space group by tablespace_name;

select 'alter tablespace'||tablespace_name||'coalesce;'
from dba_free_space_coalesced 
where percent_extents_coalesced<100
or percent_blocks_coalesced<100;

--由于自由空间碎片是由几部分组成，如范围数量、最大范围尺寸等，我们可用fsfi--free space fragmentation index（自由空间碎片索引）值来直观体现:
--fsfi=100*sqrt(max(extent)/sum(extents))*1/sqrt(sqrt(count(extents)))

rem fsfi value compute 
rem fsfi.sql 
column fsfi format 999,99 
select tablespace_name,sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))) fsfi 
from dba_free_space 
group by tablespace_name 
order by 1;
spool fsfi.rep;
/ 
spool off;

--可以看出，fsfi的最大可能值为100（一个理想的单文件表空间）。随着范围的增加，fsfi值缓慢下降，而随着最大范围尺寸的减少，fsfi值会迅速下降。
--比如，在某数据库运行脚本fsfi.sql,得到以下fsfi值： 
tablespace_name fsfi 
------------------------------ ------- 
rbs 74.06 
system 100.00 
temp 22.82 
tools 75.79 
users 100.00 
user_tools 100.00 
ydcx_data 47.34 
ydcx_idx 57.19 
ydjf_data 33.80 
ydjf_idx 75.55

--统计出了数据库的fsfi值，就可以把它作为一个可比参数。在一个有着足够有效自由空间，且fsfi值超过30的表空间中，很少会遇见有效自由空间的问题。当一个空间将要接近可比参数时，就需要做碎片整理了。

--查看碎片程度高的表 
select segment_name table_name,count(*) extents 
from dba_segments 
where owner not in ('sys','system')
group by segment_name 
having count(*)=(select max(count(*)) from dba_segments group by segment_name);

--表碎片太多，对表进行重建的处理
/*如果必须要重建表，alter table ...move tablespace...绝对是第一选择，理由如下： 
1 对于大表move时，对此表的查询不受影响，只有在move操作完成的瞬间受影响。dml操作受影响。 
2 index结构不受影响，只需move完成后rebuild。 
3 与其它对象依赖关系不受影响，操作前不必为对象间的依赖关系操心。 
4 move操作可以parallel。 
5 nologging选项对move操作有作用，可大大加快重建速度。如果要move的表是nologging的，则不需指定。 
基于以上理由，move是rebuild table的最佳选择，应该优先考虑，大家可以对照以上优点考虑一下用exp/imp的优缺点。*/

　　

转载于:https://www.cnblogs.com/xueyzhou/p/4528853.html