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_namehaving count(tablespace_name)>10; alter tablespace name coalesce;alter table name deallocate unused; create or replace view ts_blocks_v asselect tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_spaceunion allselect 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_spacegroup 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';