1.準備--表空間使用率視圖創建
SQL>
grant select on dba_free_space to test;
grant select on dba_data_files to test;
grant select on v_$temp_extent_pool to test;
grant select on v_$temp_space_header to test;
grant select on dba_temp_files to test;
create or replace view v_xag_tablespace_stat as
select tablespace_type,tablespace_name,autoext
,max_mb,used_mb,round(100 * used_mb / max_mb) pct_used
from
(
select 'USER' as tablespace_type,a.tablespace_name as tablespace_name,autoext
,a.bytes_alloc - nvl(b.bytes_free, 0) as used_mb
,a.maxbytes as max_mb
from
(
select f.tablespace_name,f.autoextensible as autoext
,trunc(sum(f.bytes)/power(2,20)) as bytes_alloc
,trunc(sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes))/power(2,20)) as maxbytes
from dba_data_files f
group by tablespace_name,autoextensible
) a left join
(
select f.tablespace_name,trunc(sum(f.bytes)/power(2,20)) as bytes_free
from dba_free_space f
group by tablespace_name
) b on (a.tablespace_name=b.tablespace_name)
union all
select 'TEMP' as tablespace_type,h.tablespace_name as tablespace_name,f.autoextensible as autoext
,trunc(sum(nvl(p.bytes_used, 0)) / power(2, 20)) used_mb
,trunc(sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes))/power(2, 20)) max_mb
from v$temp_space_header h left join v$temp_extent_pool p on(p.file_id=h.file_id and p.tablespace_name=h.tablespace_name)
join dba_temp_files f on(f.file_id = h.file_id and f.tablespace_name = h.tablespace_name)
group by h.tablespace_name,autoextensible
);
2.創建測試用途的表空間
--drop tablespace sp1 including contents and datafiles;
create tablespace sp1 datafile
'/u02/oradata/MPCDB/MPTEST/sp1.dbf' size 32M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED
extent management local segment space management auto;
create tablespace i_sp1 datafile
'/u02/oradata/MPCDB/MPTEST/i_sp1.dbf' size 16M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
extent management local segment space management auto;
create tablespace sp2 datafile
'/u02/oradata/MPCDB/MPTEST/sp2.dbf' size 32M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED
extent management local segment space management auto;
create tablespace i_sp2 datafile
'/u02/oradata/MPCDB/MPTEST/i_sp2.dbf' size 16M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
extent management local segment space management auto;
create tablespace sp3 datafile
'/u02/oradata/MPCDB/MPTEST/sp3.dbf' size 32M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED
extent management local segment space management auto;
create tablespace i_sp3 datafile
'/u02/oradata/MPCDB/MPTEST/i_sp3.dbf' size 16M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
extent management local segment space management auto;
3.檢查表空間使用率
[oracle@DB01 ~]$ sql TEST/123456@127.0.0.1:1521/MPTEST
SQL> SET SQLFORMAT ansiconsole
SQL> select * from v_xag_tablespace_stat t where t.tablespace_name in('SP1','SP2','SP3','I_SP1','I_SP2','I_SP3') order by 2;
-----------------------------------------------------------------------------------------------------
TABLESPACE_TYPE TABLESPACE_NAME AUTOEXT MAX_MB USED_MB PCT_USED
USER I_SP1 YES 32767 1 0
USER I_SP2 YES 32767 1 0
USER I_SP3 YES 32767 1 0
USER SP1 YES 32767 1 0
USER SP2 YES 32767 1 0
USER SP3 YES 32767 1 0
4.創建測試表
create table tb1 tablespace sp1
as
select rownum row_no,rpad('M',9,'A') || rownum ca,rpad('M',9,'B') cb,rpad('M',9,'C') cc from dual
connect by level<=300000;
alter table tb1 add constraint pk_tb1
primary key(row_no) using index tablespace i_sp1;
create index idx_tb1_01 on tb1(ca) tablespace i_sp1;
#用上面同樣方法創建 tb2、tb3 表
5.檢查空間使用情況
SQL> select * from v_xag_tablespace_stat t where t.tablespace_name in('SP1','SP2','SP3','I_SP1','I_SP2','I_SP3') order by 2;
TABLESPACE_TYPE TABLESPACE_NAME AUTOEXT MAX_MB USED_MB PCT_USED
USER I_SP1 YES 32767 16 0
USER I_SP2 YES 32767 16 0
USER I_SP3 YES 32767 16 0
USER SP1 YES 32767 17 0
USER SP2 YES 32767 17 0
USER SP3 YES 32767 17 0
--查看一个表所占的空间大小
SQL> SELECT bytes/1024/1024 ||'MB' TABLE_SIZE ,u.segment_name,u.segment_type FROM USER_SEGMENTS U WHERE U.SEGMENT_NAME like '%TB%' order by 3,2;
TABLE_SIZE SEGMENT_NAME SEGMENT_TYPE
9MB IDX_TB1_01 INDEX
9MB IDX_TB2_01 INDEX
9MB IDX_TB3_01 INDEX
6MB PK_TB1 INDEX
6MB PK_TB2 INDEX
6MB PK_TB3 INDEX
16MB TB1 TABLE
16MB TB2 TABLE
16MB TB3 TABLE
6.刪除一半數據
delete from tb1 t where mod(row_no,2)=0; --刪除偶數
delete from tb2 t where mod(row_no,2)=0; --刪除偶數
delete from tb3 t where mod(row_no,2)=0; --刪除偶數
commit;
7.再次空間檢查（發現空間未見減少）
SQL> select * from v_xag_tablespace_stat t where t.tablespace_name in('SP1','SP2','SP3','I_SP1','I_SP2','I_SP3') order by 2;
TABLESPACE_TYPE TABLESPACE_NAME AUTOEXT MAX_MB USED_MB PCT_USED
USER I_SP1 YES 32767 16 0
USER I_SP2 YES 32767 16 0
USER I_SP3 YES 32767 16 0
USER SP1 YES 32767 17 0
USER SP2 YES 32767 17 0
USER SP3 YES 32767 17 0
--查看一个表所占的空间大小
SQL> SELECT bytes/1024/1024 ||'MB' TABLE_SIZE ,u.segment_name,u.segment_type FROM USER_SEGMENTS U WHERE U.SEGMENT_NAME like '%TB%' order by 3,2;
TABLE_SIZE SEGMENT_NAME SEGMENT_TYPE
9MB IDX_TB1_01 INDEX
9MB IDX_TB2_01 INDEX
9MB IDX_TB3_01 INDEX
6MB PK_TB1 INDEX
6MB PK_TB2 INDEX
6MB PK_TB3 INDEX
16MB TB1 TABLE
16MB TB2 TABLE
16MB TB3 TABLE
--檢查方法3
SELECT A.TABLESPACE_NAME,
FILENUM,
TOTAL "TOTAL (MB)",
F.FREE "FREE (MB)",
TO_CHAR(ROUND(FREE * 100 / TOTAL, 2), '990.00') "FREE%",
TO_CHAR(ROUND((TOTAL - FREE) * 100 / TOTAL, 2), '990.00') "USED%",
ROUND(MAXSIZES, 2) "MAX (MB)"
FROM (SELECT TABLESPACE_NAME,
COUNT(FILE_ID) FILENUM,
SUM(BYTES / (1024 * 1024)) TOTAL,
SUM(MAXBYTES) / 1024 / 1024 MAXSIZES
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / (1024 * 1024))) FREE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE A.TABLESPACE_NAME = F.TABLESPACE_NAME
and A.TABLESPACE_NAME like '%SP%'
order by 1;
------------------------------------------
TABLESPACE_NAME FILENUM TOTAL (MB) FREE (MB) FREE% USED% MAX (MB)
SP1 1 32 15 46.88 53.13 32767.98
SP2 1 32 15 46.88 53.13 32767.98
SP3 1 32 15 46.88 53.13 32767.98
8.分析表（空間未見減少）
analyze table tb1 compute statistics;
or
analyze table tb1 compute statistics for table for all indexes for all indexed columns;
說明：
analyze table my_table compute statistics;
等价于：
analyze table my_table compute statistics for table for all indexes for all columns;
SQL>
SELECT bytes/1024/1024 ||'MB' TABLE_SIZE ,u.segment_name,u.segment_type FROM USER_SEGMENTS U WHERE U.SEGMENT_NAME like '%TB%' order by 3,2;
TABLE_SIZE SEGMENT_NAME SEGMENT_TYPE
9MB IDX_TB1_01 INDEX
9MB IDX_TB2_01 INDEX
9MB IDX_TB3_01 INDEX
6MB PK_TB1 INDEX
6MB PK_TB2 INDEX
6MB PK_TB3 INDEX
16MB TB1 TABLE
16MB TB2 TABLE
16MB TB3 TABLE
9.move （降低高水位） --案例1
优点：可以移动表到其他表空间，
在执行命令时 不需要执行alter table table_name enable row movement
缺点：表move 会导致表中的索引失效，要rebuild；同时表会产生行级锁；
在此如果表中有LOB字段 时要用一下命令来实现表空间移动：
alter table owner.table_name move tablespace tablespace_name lob (lob_column) store as lob segment tablespace tablespace_name;
也可以单独move lob，index要rebuild
--不move到新空間
SQL> alter table tb1 move;
SQL> select index_name,table_name,tablespace_name,index_type,status from dba_indexes where table_owner='TEST' and status!='VALID';
INDEX_NAME TABLE_NAME TABLESPACE_NAME INDEX_TYPE STATUS
PK_TB1 TB1 I_SP1 NORMAL UNUSABLE
IDX_TB1_01 TB1 I_SP1 NORMAL UNUSABLE
SQL> alter index PK_TB1 rebuild;
SQL> alter index IDX_TB1_01 rebuild;
SQL> select index_name,table_name,tablespace_name,index_type,status from dba_indexes where table_owner='TEST' and status!='VALID';
10.空間檢查（發現空間減少）
SQL> select * from v_xag_tablespace_stat t where t.tablespace_name in('SP1','SP2','SP3','I_SP1','I_SP2','I_SP3') order by 2;
TABLESPACE_TYPE TABLESPACE_NAME AUTOEXT MAX_MB USED_MB PCT_USED
USER I_SP1 YES 32767 9 0
USER I_SP2 YES 32767 16 0
USER I_SP3 YES 32767 16 0
USER SP1 YES 32767 9 0
USER SP2 YES 32767 17 0
USER SP3 YES 32767 17 0
--查看一个表所占的空间大小
SQL> SELECT bytes/1024/1024 ||'MB' TABLE_SIZE ,u.segment_name,u.segment_type FROM USER_SEGMENTS U WHERE U.SEGMENT_NAME like '%TB%' order by 3,2;
TABLE_SIZE SEGMENT_NAME SEGMENT_TYPE
5MB IDX_TB1_01 INDEX
9MB IDX_TB2_01 INDEX
9MB IDX_TB3_01 INDEX
3MB PK_TB1 INDEX
6MB PK_TB2 INDEX
6MB PK_TB3 INDEX
8MB TB1 TABLE
16MB TB2 TABLE
16MB TB3 TABLE
--檢查方法3
SELECT A.TABLESPACE_NAME,
FILENUM,
TOTAL "TOTAL (MB)",
F.FREE "FREE (MB)",
TO_CHAR(ROUND(FREE * 100 / TOTAL, 2), '990.00') "FREE%",
TO_CHAR(ROUND((TOTAL - FREE) * 100 / TOTAL, 2), '990.00') "USED%",
ROUND(MAXSIZES, 2) "MAX (MB)"
FROM (SELECT TABLESPACE_NAME,
COUNT(FILE_ID) FILENUM,
SUM(BYTES / (1024 * 1024)) TOTAL,
SUM(MAXBYTES) / 1024 / 1024 MAXSIZES
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / (1024 * 1024))) FREE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE A.TABLESPACE_NAME = F.TABLESPACE_NAME
and A.TABLESPACE_NAME like '%SP%'
order by 1;
------------------------------------------
TABLESPACE_NAME FILENUM TOTAL (MB) FREE (MB) FREE% USED% MAX (MB)
I_SP1 1 16 7 43.75 56.25 32767.98
SP1 1 32 23 71.88 28.13 32767.98
SP2 1 32 15 46.88 53.13 32767.98
SP3 1 32 15 46.88 53.13 32767.98
11.move （降低高水位） --案例2--移動到新空間中
create tablespace sp2_new datafile
'/u02/oradata/MPCDB/MPTEST/sp2_new.dbf' size 32M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED
extent management local segment space management auto;
create tablespace i_sp2_new datafile
'/u02/oradata/MPCDB/MPTEST/i_sp2_new.dbf' size 16M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
extent management local segment space management auto;
alter index PK_ACC_MERCHANTACCOUNTDET_1 rebuild partition PD1812 TABLESPACE I_PD1812 logging;
SQL> alter table tb2 move tablespace sp2_new; ---移动表到其它表空间
SQL> select index_name,table_name,tablespace_name,index_type,status from dba_indexes where table_owner='TEST' and status!='VALID';
-----------------------------------------------------------------------------------------------------
INDEX_NAME TABLE_NAME TABLESPACE_NAME INDEX_TYPE STATUS
PK_TB2 TB2 I_SP2 NORMAL UNUSABLE
IDX_TB2_01 TB2 I_SP2 NORMAL UNUSABLE
SQL> alter index PK_TB2 rebuild tablespace i_sp2_new;
SQL> alter index IDX_TB2_01 rebuild tablespace i_sp2_new;
SQL> select index_name,table_name,tablespace_name,index_type,status from dba_indexes where table_owner='TEST' and status!='VALID';
12.空間檢查（發現空間減少）
SQL> select * from v_xag_tablespace_stat t where t.tablespace_name in('SP1','SP2','SP3','SP2_NEW','I_SP1','I_SP2','I_SP3','I_SP2_NEW') order by 2;
TABLESPACE_TYPE TABLESPACE_NAME AUTOEXT MAX_MB USED_MB PCT_USED
USER I_SP1 YES 32767 9 0
USER I_SP2 YES 32767 1 0
USER I_SP2_NEW YES 32767 9 0
USER I_SP3 YES 32767 16 0
USER SP1 YES 32767 9 0
USER SP2 YES 32767 1 0
USER SP2_NEW YES 32767 9 0
USER SP3 YES 32767 17 0
--查看一个表所占的空间大小
SQL> SELECT bytes/1024/1024 ||'MB' TABLE_SIZE ,u.segment_name,u.segment_type FROM USER_SEGMENTS U WHERE U.SEGMENT_NAME like '%TB%' order by 3,2;
TABLE_SIZE SEGMENT_NAME SEGMENT_TYPE
5MB IDX_TB1_01 INDEX
5MB IDX_TB2_01 INDEX
9MB IDX_TB3_01 INDEX
3MB PK_TB1 INDEX
3MB PK_TB2 INDEX
6MB PK_TB3 INDEX
8MB TB1 TABLE
8MB TB2 TABLE
16MB TB3 TABLE
13.收縮空間 shrink space （回收相应的索引）
优点：降低高水位时索引不会失效
缺点：不能将表移动到其他表空间；高水位降低效果没有move明显；
同时在执行命令前要先执行(alter table table_name enable row movement允许行移动)也会表会产生行级锁，
shrink比move更耗费cpu,产生很多current block这样生成巨大的redo与undo 如果表中索引很少可以建议使用move降低高水位
alter table tb3 enable row movement; --开启行迁移功能。
alter table tb3 shrink space cascade; -- 调整HWM时将阻塞DML操作 ,收缩表并降低hwm，并且回收相应的索引。
alter table tb3 disable row movement; --關閉行迁移功能。
14.空間檢查
SQL> SELECT bytes/1024/1024 ||'MB' TABLE_SIZE ,u.segment_name,u.segment_type FROM USER_SEGMENTS U WHERE U.SEGMENT_NAME like '%TB%' order by 3,2;
TABLE_SIZE SEGMENT_NAME SEGMENT_TYPE
5MB IDX_TB1_01 INDEX
5MB IDX_TB2_01 INDEX
4.5625MB IDX_TB3_01 INDEX
3MB PK_TB1 INDEX
3MB PK_TB2 INDEX
2.625MB PK_TB3 INDEX
8MB TB1 TABLE
8MB TB2 TABLE
7.625MB TB3 TABLE
SQL> select * from v_xag_tablespace_stat t where t.tablespace_name in('SP1','SP2','SP3','SP2_NEW','I_SP1','I_SP2','I_SP3','I_SP2_NEW') order by 2;
TABLESPACE_TYPE TABLESPACE_NAME AUTOEXT MAX_MB USED_MB PCT_USED
USER I_SP1 YES 32767 9 0
USER I_SP2 YES 32767 1 0
USER I_SP2_NEW YES 32767 9 0
USER I_SP3 YES 32767 9 0
USER SP1 YES 32767 9 0
USER SP2 YES 32767 1 0
USER SP2_NEW YES 32767 9 0
USER SP3 YES 32767 9 0
SQL> select index_name,table_name,tablespace_name,index_type,status from dba_indexes where table_owner='TEST' and status!='VALID';
no rows selected
15.改变数据文件所占空间大小(OS 上空間大小）
如果想改变数据文件所占空间大小可执行如下语句：
alter database datafile 'filename' resize 8g重定义数据文件的大小（不能小于该数据文件已用空间的大小）。
alter database datafile '/u02/oradata/MPCDB/MPTEST/sp3.dbf' resize 24M;
alter database datafile '/u02/oradata/MPCDB/MPTEST/i_sp3.dbf' resize 12M;
16.收縮空間 shrink space（不回收相应的索引）
create table tb22 tablespace sp2
as
select rownum row_no,rpad('M',9,'A') || rownum ca,rpad('M',9,'B') cb,rpad('M',9,'C') cc from dual
connect by level<=300000;
alter table tb22 add constraint pk_tb22
primary key(row_no) using index tablespace i_sp2;
create index idx_tb22_01 on tb22(ca) tablespace i_sp2;
delete from tb22 t where mod(row_no,2)=0; --刪除偶數
commit;
SQL> select * from v_xag_tablespace_stat t where t.tablespace_name in('SP1','SP2','SP3','SP2_NEW','I_SP1','I_SP2','I_SP3','I_SP2_NEW') order by 2;
TABLESPACE_TYPE TABLESPACE_NAME AUTOEXT MAX_MB USED_MB PCT_USED
USER I_SP1 YES 32767 9 0
USER I_SP2 YES 32767 16 0
USER I_SP2_NEW YES 32767 9 0
USER I_SP3 YES 32767 9 0
USER SP1 YES 32767 9 0
USER SP2 YES 32767 17 0
USER SP2_NEW YES 32767 9 0
USER SP3 YES 32767 9 0
alter table tb22 enable row movement; --开启行迁移功能。
alter table tb22 shrink space; --( 调整HWM时将阻塞DML操作),收缩表，并且降低hwm
alter table tb22 disable row movement; --關閉行迁移功能。
--索引未收縮
SQL> select * from v_xag_tablespace_stat t where t.tablespace_name in('SP1','SP2','SP3','SP2_NEW','I_SP1','I_SP2','I_SP3','I_SP2_NEW') order by 2;
TABLESPACE_TYPE TABLESPACE_NAME AUTOEXT MAX_MB USED_MB PCT_USED
USER I_SP1 YES 32767 9 0
USER I_SP2 YES 32767 16 0
USER I_SP2_NEW YES 32767 9 0
USER I_SP3 YES 32767 9 0
USER SP1 YES 32767 9 0
USER SP2 YES 32767 9 0
USER SP2_NEW YES 32767 9 0
USER SP3 YES 32767 9 0
--收縮索引shrink space （不回收相应的索引）
alter index pk_tb22 shrink space;
alter index idx_tb22_01 shrink space;
SQL> select * from v_xag_tablespace_stat t where t.tablespace_name in('SP1','SP2','SP3','SP2_NEW','I_SP1','I_SP2','I_SP3','I_SP2_NEW') order by 2;
TABLESPACE_TYPE TABLESPACE_NAME AUTOEXT MAX_MB USED_MB PCT_USED
USER I_SP1 YES 32767 9 0
USER I_SP2 YES 32767 9 0
USER I_SP2_NEW YES 32767 9 0
USER I_SP3 YES 32767 9 0
USER SP1 YES 32767 9 0
USER SP2 YES 32767 9 0
USER SP2_NEW YES 32767 9 0
USER SP3 YES 32767 9 0
网友评论