oracle dba常用语句3

 

 
24．查询表空间使用情况
select a.tablespace_name,
       round(a.abytes / 1024 / 1024) totalmb,
       round((a.abytes - f.fbytes) / 1024 / 1024) usedmb,
       round(f.fbytes / 1024 / 1024) freemb,
       (1 - round(f.fbytes / a.abytes, 2)) * 100 "%USED",
       round(f.fbytes / a.abytes, 2) * 100 "%FREE"
  from (select tablespace_name, sum(bytes) abytes from dba_data_files group by tablespace_name) a,
       (select tablespace_name, sum(bytes) fbytes from dba_free_space group by tablespace_name) f
 where a.tablespace_name = f.tablespace_name

也可以这样（这个查询较快）：

select a.tablespace_name,
       round(a.abytes / 1024 / 1024) totalmb,
       round((a.abytes - f.fbytes) / 1024 / 1024) usedmb,
       round(f.fbytes / 1024 / 1024) freemb,
       (1 - round(f.fbytes / a.abytes, 2)) * 100 "%USED",
       round(f.fbytes / a.abytes, 2) * 100 "%FREE"
  from (select tablespace_name, sum(bytes) abytes from dba_data_files group by tablespace_name) a,
       (select tablespace_name, sum(bytes) fbytes from dba_free_space group by tablespace_name) f
 where a.tablespace_name = f.tablespace_name
 
25. 查询表空间的碎片程度
 
select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name
having count(tablespace_name)>10;
 
alter tablespace name coalesce;
alter table name deallocate unused;
 
create or replace view ts_blocks_v as
select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space
union all
select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;
 
select * from ts_blocks_v;
 
select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space
group by tablespace_name;
 
26。查询有哪些数据库实例在运行
select inst_name from v$active_instances;

27。查询表占用空间大小

select DISTINCT owner,segment_name,bytes/1024/1024 Mb from dba_extents where 
owner='SCOTT' and segment_type='TABLE';