Oracle 数据库表空间运维的记录
-- 查看表空间和对应物理文件名
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'
;
