Oracle学习之路【一】

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

 表空间迁移

 

-- 表迁移
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(+);

 

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