oracle数据库元数据SQL查询.pdfVIP

-

oracle数据库经典SQL查询

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

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

fromdba_tablespacest,dba_data_filesd

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_e*tent/1024)InitialE*tent,(ne*t_e*tent/1024)Ne*tE*tent,

ma*_e*tents,v.cure*tCurE*tent

Fromdba_rollback_segsr,v$rollstatv

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.BYTESTOTAL,B.BYTESUSED,C.BYTESFREE,SELECTA.TABLESPACE_NAME,A.BYTESTOTAL,B.BYTESUSED,C.BYTESFREE,

FROMSYS.SM$TS_AVAILA,SYS.SM$TS_USEDB,SYS.SM$TS_FREEC

WHEREA.TABLESPACE_NAME=B.TABLESPACE_NAMEAND

A.TABLESPACE_NAME=C.TABLESPACE_NAME;

7、查看数据库库对象

selectowner,object_type,status,count(*)count#fromall_objectsgroup

byowner,object_type,status;

8、查看数据库的版本

SelectversionFROMProduct_ponent_version

WhereSUBSTR(PRODUCT,1,6)=Oracle;

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

SelectCreated,Log_Mode,Log_ModeFromV$Database;

10、捕捉运行很久的SQL

columnusernameformata12

z.

-

columnopnameformata16

columnprogressformata8

selectusername,sid,opname,

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

time_remaining,sql_te*t

fromv$session_longops,v$sql

wheretime_remaining0

andsql_address=address

andsql_hash_value=hash_value

/

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

SELECTpartition_name,high_value,high_value_length,tablespace_name,

pct_free,pct_used,ini_trans,