-- 创建表空间,表空间不自动扩展,表,插入数据,直到不能插入为止 。
SYS@test> create tablespace tbs_hmw datafile '/u01/oradata/test/tbs_hmw01.dbf' size 380M autoextend off;
Tablespace created.
SYS@test>
create table hmw_1 tablespace tbs_hmw as select * from hmw;
BB@test> create table hmw_1 tablespace tbs_hmw as select * from hmw;
Table created.
Elapsed: 00:01:10.25
BB@test>
BB@test> insert into hmw_1 select * from dba_objects;
73760 rows created.
Elapsed: 00:00:00.69
BB@test> commit;
Commit complete.
Elapsed: 00:00:00.07
BB@test> insert into hmw_1 select * from hmw_1;
221280 rows created.
Elapsed: 00:00:01.58
BB@test> insert into hmw_1 select * from hmw_1;
insert into hmw_1 select * from hmw_1
*
ERROR at line 1:
ORA-01653: unable to extend table BB.HMW_1 by 1024 in tablespace TBS_HMW
Elapsed: 00:00:16.16
BB@test> commit;
Commit complete.
Elapsed: 00:00:00.00
BB@test>
BB@test> select count(*) from hmw_1;
COUNT(*)
----------
1770240
Elapsed: 00:00:07.16
BB@test>
--- 查看表空间情况
TABLESPACE_NAME MEGS_ALLOC MEGS_FREE MEGS_USED PCT_FREE PCT_USED MAX
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
EXAMPLE 400 96 304 24 76 30720
LOB_TBS 4348 252 4096 6 94 30720
SYSAUX 2048 1240 808 61 39 32768
SYSTEM 2048 1209 839 59 41 32768
TBS_HMW 380 2 378 1 99 380
TEMP 248 241 7 97 3 32768
TEST_TMP 10240 10240 0 100 0 0
UNDOTBS1 2048 1304 744 64 36 32768
USERS 10240 5650 4590 55 45 32768
9 rows selected.
Elapsed: 00:00:01.34
--删除掉表hmw_1的数据,再次查看表空间使用情况
BB@test> delete from hmw_1;
1770240 rows deleted.
Elapsed: 00:07:01.04
BB@test> commit;
Commit complete.
Elapsed: 00:00:00.05
BB@test> select count(*) from hmw_1;
COUNT(*)
----------
0
Elapsed: 00:01:09.77
BB@test>
TABLESPACE_NAME MEGS_ALLOC MEGS_FREE MEGS_USED PCT_FREE PCT_USED MAX
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
EXAMPLE 400 96 304 24 76 30720
LOB_TBS 4348 252 4096 6 94 30720
SYSAUX 2048 1238 810 60 40 32768
SYSTEM 2048 1209 839 59 41 32768
TBS_HMW 380 2 378 1 99 380
TEMP 248 241 7 97 3 32768
TEST_TMP 10240 10240 0 100 0 0
UNDOTBS1 2048 786 1262 38 62 32768
USERS 10240 5650 4590 55 45 32768
-- 先计算表空间文件可收缩情况,
其中smqllest 表示当前表空间文件已使用的大小,比如查询到tbs_hmw01.dbf文件是378,该表空间文件已经使用了378M
currsize表示当前表空间文件的大小, tbs_hmw01.dbf文件是380,该表空间总共的文件大小是380M
savings表示未使用的大小, Savings ,说明该表空间文件中,有多少空间没有被使用,这里有2M没有被使用。和上面的表对比下,就明白了。
select file_name,
ceil( (nvl(hwm,1)*8192)/1024/1024 ) smallest,
ceil( blocks*8192/1024/1024) currsize,
ceil( blocks*8192/1024/1024) -
ceil( (nvl(hwm,1)*8192)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+) order by savings desc
Smallest
Size Current Poss.
FILE_NAME Poss. Size Savings
-------------------------------------------------- -------- -------- --------
/u01/oradata/test/users01.dbf 4,591 10,240 5,649
/u01/oradata/test/sysaux01.dbf 811 2,048 1,237
/u01/oradata/test/system01.dbf 839 2,048 1,209
/u01/oradata/test/undotbs01.dbf 1,266 2,048 782
/u01/oradata/test/lob_tbs01.dbf 4,097 4,348 251
/u01/oradata/test/example01.dbf 305 400 95
/u01/oradata/test/tbs_hmw01.dbf 378 380 2
--------
sum 9,225
7 rows selected.
Elapsed: 00:00:01.71
-- 如果要对表空间文件tbs_hmw01.dbf 进行resize操作,则需要先对表hmw_1进行收缩
alter table hmw_1 enable row movement;
alter table hmw_1 shrink space ;
BB@test> alter table hmw_1 enable row movement;
Table altered.
Elapsed: 00:00:00.36
BB@test> alter table hmw_1 shrink space ;
Table altered.
Elapsed: 00:00:57.22
BB@test> alter table hmw_1 disable row movement;
Table altered.
Elapsed: 00:00:00.00
BB@test>
-- 再次查看 ,表空间情况及表空间文件可收缩情况,可以发现文件tbs_hmw01.dbf可以收缩节约378M的空间,表空间TBS_HMW 可用空间为379M
TABLESPACE_NAME MEGS_ALLOC MEGS_FREE MEGS_USED PCT_FREE PCT_USED MAX
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
EXAMPLE 400 96 304 24 76 30720
LOB_TBS 4348 252 4096 6 94 30720
SYSAUX 2048 1237 811 60 40 32768
SYSTEM 2048 1209 839 59 41 32768
TBS_HMW 380 379 1 100 0 380
TEMP 248 241 7 97 3 32768
TEST_TMP 10240 10240 0 100 0 0
UNDOTBS1 2048 785 1263 38 62 32768
USERS 10240 5650 4590 55 45 32768
BB@test> select file_name,
2 ceil( (nvl(hwm,1)*8192)/1024/1024 ) smallest,
3 ceil( blocks*8192/1024/1024) currsize,
4 ceil( blocks*8192/1024/1024) -
5 ceil( (nvl(hwm,1)*8192)/1024/1024 ) savings
6 from dba_data_files a,
7 ( select file_id, max(block_id+blocks-1) hwm
8 from dba_extents
9 group by file_id ) b
w 10 here a.file_id = b.file_id(+) order by savings desc;
Smallest
Size Current Poss.
FILE_NAME Poss. Size Savings
-------------------------------------------------- -------- -------- --------
/u01/oradata/test/users01.dbf 4,591 10,240 5,649
/u01/oradata/test/sysaux01.dbf 811 2,048 1,237
/u01/oradata/test/system01.dbf 839 2,048 1,209
/u01/oradata/test/undotbs01.dbf 1,266 2,048 782
/u01/oradata/test/tbs_hmw01.dbf 2 380 378
/u01/oradata/test/lob_tbs01.dbf 4,097 4,348 251
/u01/oradata/test/example01.dbf 305 400 95
--------
sum 9,601
7 rows selected.
Elapsed: 00:00:04.76
BB@test>
-- OS层面进行收缩文件 ,之前文件大小是381M
BB@test> !du -m /u01/oradata/test/tbs_hmw01.dbf
381 /u01/oradata/test/tbs_hmw01.dbf
BB@test>
BB@test> alter database datafile '/u01/oradata/test/tbs_hmw01.dbf' resize 5M;
Database altered.
Elapsed: 00:00:01.32
BB@test> alter database datafile '/u01/oradata/test/tbs_hmw01.dbf' resize 2M;
Database altered.
Elapsed: 00:00:00.09
BB@test> !du -m /u01/oradata/test/tbs_hmw01.dbf
3 /u01/oradata/test/tbs_hmw01.dbf
BB@test>
-- 如果对该文件收缩到1M,则会提示错误
BB@test> alter database datafile '/u01/oradata/test/tbs_hmw01.dbf' resize 1M;
alter database datafile '/u01/oradata/test/tbs_hmw01.dbf' resize 1M
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required
Elapsed: 00:00:00.71
BB@test>
--查看该表空间文件已经分配的extent的情况,请查看该extent的大小 。大小已经超过了1M ,所以要收缩到1M以下,是不可以的。
select max(block_id+blocks-1) from dba_extents where file_id=8
select 135*8192/1024/1024 from dual
BB@test> select max(block_id+blocks-1) from dba_extents where file_id=8;
MAX(BLOCK_ID+BLOCKS-1)
----------------------
135
Elapsed: 00:00:00.47
BB@test> select 135*8192/1024/1024 from dual;
135*8192/1024/1024
------------------
1.0546875
Elapsed: 00:00:00.01
BB@test>
-- 总结
在OS层对文件进行收缩,只能收缩到高水位线的地方。
或者,要想收缩dbfile到更小,则需要先收缩该dbfile里面表的高水位线,然后再收缩dbfiles。
MOS上是这样说的。
We need to reduce Disk usage at OS level . The first step for that will be to identify the HWM of datafile . The HWM specifies the limit to which you can resize a datafile.
END