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(+);
?
========================================================================?