Oracle维护常用SQL语句整理一

1、查看表空间的名称及大小

selectt.tablespace_name,round(sum(bytes/(1024*1024)),0)ts_size

fromdba_tablespaces t,dba_data_files d

wheret.tablespace_name=d.tablespace_name

groupbyt.tablespace_name;

2、查看表空间物理文件的名称及大

selecttablespace_name,file_id,file_name,

round(bytes/(1024*1024),0)total_space

fromdba_data_files

orderbytablespace_name;

3、查看回滚段名称及大小

selectsegment_name,tablespace_name,r.status,

(initial_extent/1024)InitialExtent,(next_extent/1024)NextExtent,

max_extents,v.curext CurExtent

Fromdba_rollback_segs r,v$rollstat v

Wherer.segment_id=v.usn(+)

orderbysegment_name;

4、查看控制文件

selectnamefromv$controlfile;

5、查看日志文件

selectmemberfromv$logfile;

6、查看表空间的使用情况

selectsum(bytes)/(1024*1024)asfree_space,tablespace_name

fromdba_free_space

groupbytablespace_name;

SELECTA.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED,C.BYTES FREE,

(B.BYTES*100)/A.BYTES"% USED",(C.BYTES*100)/A.BYTES"% FREE"

FROMSYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C

WHEREA.TABLESPACE_NAME=B.TABLESPACE_NAMEANDA.TABLESPACE_NAME=C.TABLESPACE_NAME;

7、查看数据库库对象

selectowner,object_type,status,count(*)count#fromall_objectsgroupbyowner,object_type,status;

8、查看数据库的版本

SelectversionFROMProduct_component_versionWhereSUBSTR(PRODUCT,1,6)='Oracle';

9、查看数据库的创建日期和归档方式

SelectCreated,Log_Mode,Log_ModeFromV$Database;

10、捕捉运行很久的SQL

columnusername format a12

columnopname format a16

columnprogress format a8

selectusername,sid,opname,

round(sofar*100/totalwork,0)'%'asprogress,

time_remaining,sql_text

fromv$session_longops,v$sql

wheretime_remaining<>0

andsql_address=address

andsql_hash_value=hash_value；

11、查看数据表的参数信息

SELECTpartition_name,high_value,high_value_length,tablespace_name,

pct_free,pct_used,ini_trans,max_trans,initial_extent,

next_extent,min_extent,max_extent,pct_increase,FREELISTS,

freelist_groups,LOGGING,BUFFER_POOL,num_rows,blocks,

empty_blocks,avg_space,chain_cnt,avg_row_len,sample_size,

last_analyzed

FROMdba_tab_partitions

--WHERE table_name = :tname AND table_owner = :towner

ORDER