dba常用sql-1(转) Rounders | 06 八月, 2007 16:31 表空间空余,迅速收缩临时段,查看索引定义,查看裸设备IO,查看表的物理大小等DBA常用sql。 （） //表空间空余察看 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 rowid<(select max(rowid) from device_state_710 where 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' ;[@more@]