-- 查看表空间和对应物理文件名
select
b.file_id 物理文件号,
b.file_name 物理文件名,
b.tablespace_name 表空间,
b.bytes / 1024 / 1024 大小M,
(b.bytes -
sum
(nvl(a.bytes, 0))) / 1024 / 1024 已使用M,
substr((b.bytes -
sum
(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) 利用率
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.file_name, b.bytes
order
by
b.tablespace_name;
-- 152,153,154,155,156,157,158,159,160,161,162,163 物理文件名/datadb/ZHFCDB/default_tbs**,物理I文件号 152,153,154,155,156,157,158,159,160,161,162,163 几乎未达使用
-- 确认下表空间对应的数据文件是否有在
SELECT
*
FROM
dba_extents
WHERE
TABLESPACE_NAME =
'DEFAULT_TBS'
and
extend_id
in
(152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163);
-- 为空集,说明数据文件数据块还未写入数据,可以直接删除
-- 数据清理与清空回收站
-- 清除用户回收站
purge recyclebin;
-- 清除全库回收站
purge dba_recyclebin;
-- 整理表空间
alter
tablespace tablespace_name
coalesce
;
-- 清空表数据
truncate
table
table_name;
-- 释放表空间
alter
table
table_name
deallocate
UNUSED KEEP 0;
-- 注:(1)先清空表数据,此时表依旧存在,Truncate不支持回滚,并且不能truncate一个带有外键的表,如果要删除首先要取消外键,然后再删除;(2)注意如果不加KEEP 0的话,表空间是不会释放的。
-- 迁移表数据。在出现以下错误时,我们不仅可以通过整理表空间或者清楚回收站进行解决,还可以通过将待释放表空间的表数据全部迁移到其他表空间,将空间释放后再迁移回原表空间。(此次操作无须涉及)
-- 需移动的表数据
select
DISTINCT
'alter table '
|| owner||
'.'
||segment_name ||
' move tablespace user_test;'
from
dba_extents
where
segment_type=
'TABLE'
and
file_id=4;
-- 需移动的索引数据
select
DISTINCT
'alter index '
|| owner||
'.'
|| segment_name ||
' rebuild tablespace user_test;'
from
dba_extents
where
segment_type=
'INDEX'
and
file_id=4;
-- 需移动的分区表数据
select
DISTINCT
'alter table '
|| owner||
'.'
|| segment_name ||
' move partition '
|| partition_name ||
' tablespace user_test;'
from
dba_extents
where
segment_type=
'TABLE PARTITION'
and
file_id=4;
-- 需移动的分区表索引数据
select
DISTINCT
'alter index '
|| owner||
'.'
|| segment_name ||
' rebuild partition '
|| partition_name ||
' tablespace user_test;'
from
dba_extents
where
segment_type=
'INDEX PARTITION'
and
file_id=4;
-- 上述代码中file_id可以通过查看表空间进行比对更改,上述代码执行后可以获得对应的自动生成的SQL代码,此时运行对应的SQL代码即可迁移表数据
-- 生成RESIZE代码 (无须操作)
select
a.file#,
a.
name
,
a.bytes / 1024 / 1024 CurrentMB,
ceil(HWM * a.block_size) / 1024 / 1024 ResizeTo,
(a.bytes - HWM * a.block_size) / 1024 / 1024 ReleaseMB,
'alter database datafile '
''
|| a.
name
||
''
' resize '
||
ceil(HWM * a.block_size / 1024 / 1024) ||
'M;'
ResizeCMD
from
v$datafile a,
(
select
file_id,
max
(block_id + blocks - 1) HWM
from
dba_extents
where
file_id
in
(
select
b.file#
From
v$tablespace a, v$datafile b
where
a.ts# = b.ts#
and
a.
name
=
'tablespace_name'
)
group
by
file_id) b
where
a.file# = b.file_id(+)
and
(a.bytes - HWM * block_size) > 0
order
by
5;
-- 删除空的表空间,但是不包含物理文件
drop
tablespace tablespace_name;
-- 删除非空表空间,但是不包含物理文件
drop
tablespace tablespace_name including contents;
-- 删除空表空间,包含物理文件
drop
tablespace tablespace_name including datafiles;
-- 删除非空表空间,包含物理文件
drop
tablespace tablespace_name including contents
and
datafiles;
-- 如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段,就要加上CASCADE CONSTRAINTS
drop
tablespace tablespace_name including contents
and
datafiles
CASCADE
CONSTRAINTS;
-- 查看表空间数据表,数据块,数据段的使用信息
SELECT
*
FROM
dba_tables a
WHERE
a.TABLESPACE_NAME =
'DEFAULT_TBS'
;
SELECT
*
FROM
dba_extents
WHERE
TABLESPACE_NAME =
'DEFAULT_TBS'
;
SELECT
*
FROM
dba_segments
WHERE
TABLESPACE_NAME =
'DEFAULT_TBS'
;