Oracle学习之路【一】-优快云博客

本文提供了Oracle数据库中表空间管理的实用SQL脚本，包括表、LOB字段及索引的迁移命令，以及如何查看表空间的读写频率和使用率的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

========================================================================

表空间迁移

-- 表迁移
select 'alter table ' || table_name || ' move tablespace tbs_name;' table_name
  from dba_tables
 where owner in ('CPSASST','FACTORY','MANAGE','MANAGEBAK');

-- lob 字段迁移
select 'alter table' ||table_name|| 'move lob('||index_name||') store as (tablespace tbs_name);' from dba_indexes where owner='%***%' and index_name like '%***%'

-- 索引迁移
select 'alter index ' || index_name || ' rebuild tablespace tbs_name;' index_name
  from dba_indexes
 where owner in ('CPSASST', 'FACTORY', 'MANAGE', 'MANAGEBAK');

========================================================================

--查看哪个表空间读写频繁
select name, phyrds, phywrts, readtim, writetim
  from v$filestat a, v$dbfile b
 where a.file# = b.file#
 order by readtim desc;

========================================================================

-- 查看表空间使用率
select a.tablespace_name, round(a.total_size) "total_size(MB)", 
round(a.total_size)-round(b.free_size,3) "used_size(MB)", 
round(b.free_size,3) "free_size(MB)", round(b.free_size/total_size*100,2)||'%' free_rate 
from ( select tablespace_name, sum(bytes)/1024/1024 total_size 
       from dba_data_files 
       group by tablespace_name ) a, 
       ( select tablespace_name, sum(bytes)/1024/1024 free_size 
         from dba_free_space 
         group by tablespace_name ) b 
       where a.tablespace_name = b.tablespace_name(+);

========================================================================