dba常用sql-1(轉)

表空間空餘,迅速收縮臨時段,檢視索引定義,檢視裸裝置IO,檢視錶的物理大小等DBA常用sql。

[@more@]（）

//表空間空餘察看
select segment_name,bytes from dba_segments where bytes>100000000 and tablespace_name='TS_LIST07';
select bytes from dba_free_space where tablespace_name='TS_LIST07' order by bytes;
SELECT TABLESPACE_NAME,MAX(BYTES) FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ORDER BY MAX(BYTES);

select tablespace_name,sum(bytes),sum(free),sum(free)*100/sum(bytes) from (select
b.file_id file_ID,
b.tablespace_name tablespace_name,
b.bytes Bytes,
(b.bytes-sum(nvl(a.bytes,0))) used,
sum(nvl(a.bytes,0)) free,
sum(nvl(a.bytes,0))/(b.bytes)*100 Percent
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id) group by tablespace_name order by sum(free)*100/sum(bytes);

//迅速收縮臨時段
alter tablespace temp default storage(pctincrease 1)；
alter tablespace temp default storage(pctincrease 0)；

//檢視索引定義
select INDEX_NAME,TABLE_NAME,COLUMN_NAME from dba_ind_columns where TABLE_OWNER='IUTBS' order by TABLE_NAME;
//刪除重單
delete from device_state_710 a
where rowidwhere ACCESS_NUMBER=a.ACCESS_NUMBER and SERVICE_TYPE=a.SERVICE_TYPE and STATENO=a.STATENO
and BEFORE_STATENO=a.BEFORE_STATENO and FROM_NO=a.FROM_NO and BEFORM_NO=a.BEFORM_NO
and SEND_DATE=a.SEND_DATE and RUN_DATE=a.RUN_DATE);

//檢視裸裝置IO
select
substr(DF.NAME,1,5) Drive,
DF.NAME file_name,
(fs.phyblkrd+fs.phyblkwrt)
from v$filestat fs,v$datafile df
where df.file#=fs.file#;

//檢視錶的物理大小
select * from DBA_SEGMENTS WHERE OWNER='iutbs';
select SEGMENT_NAME,BYTES from dba_segments where segment_type='TABLE' and owner='IUTBS' ;
select * from dba_segments where segment_type='INDEX' and owner='IUTBS' ;