Oracle表空间增/删/查

Oracle表空间增/删/查

Oracle创建表空间

 create tablespace vmcm datafile 'vmcm.dbf' size 2000m;

Oracle删除表空间及对应的表空间文件

drop tablespace vmcm including contents and datafiles cascade constraint;

查询Oracle表空间内的表

select table_name,tablespace_name from dba_tables where tablespace_name='orcl_data';

Oracle表空间使用率查询

select b.tablespace_name,b.contents,a.max_mb,a.total_mb,a.free_mb,a.free_pct,a.used_mb,a.max_free_mb,a.max_free_pct,b.status,fsfi_pct
from
(
select total.tablespace_name

,round(total.mb) as total_mb

,round(free.mb) as free_mb

,round(total.mb-free.mb) as used_mb

,round((free.mb / total.mb) * 100) || '%' as free_pct

,round(total.max_mb) as max_mb

,round(total.max_mb-(total.mb-free.mb)) as max_free_mb

,round((total.max_mb-(total.mb-free.mb))/total.max_mb * 100) || '%' as max_free_pct

,round(fsfi_pct,2) fsfi_pct

from (

select tablespace_name

,sum(bytes) / 1024 / 1024 as mb

,sqrt(max(blocks)/sum(blocks))* (100/sqrt(sqrt(count(blocks)))) fsfi_pct

from dba_free_space

group by tablespace_name

) free

,(

select tablespace_name

,sum(case autoextensible

when 'yes'

then maxbytes

else bytes

end) / 1024 / 1024 as max_mb

,sum(bytes) / 1024 / 1024 as mb

from dba_data_files

group by tablespace_name

) total

where free.tablespace_name = total.tablespace_name

union all

select total.tablespace_name

,round(total.mb) as total_mb

,round(free.mb) as free_mb

,round(total.mb-free.mb) as used_mb

,round((free.mb / total.mb) * 100) || '%' as free_pct

,round(total.max_mb) as max_mb

,round(total.max_mb-(total.mb-free.mb)) as max_free_mb

,round((total.max_mb-(total.mb-free.mb))/total.max_mb * 100) || '%' as free_pct_max

,fsfi_pct

from (

select tablespace_name

,sum(free_space) / 1024 / 1024 as mb

,null fsfi_pct from dba_temp_free_space

group by tablespace_name

) free

,(

select tablespace_name

,sum(case autoextensible

when 'yes'

then maxbytes

else bytes

end) / 1024 / 1024 as max_mb

,sum(bytes) / 1024 / 1024 as mb

from dba_temp_files

group by tablespace_name

) total

where free.tablespace_name = total.tablespace_name) a, dba_tablespaces b

where a.tablespace_name(+)=b.tablespace_name order by max_free_pct desc;