数据字典SQL查询语句

1、数据字典sql查询语句1.数据字典视图(动态性能表V$fixed_table / dictionary / dict / dict_columns2.查詢数据库的名字,创建日期等内容。Select name, created, log_mode, open_modeFrom v$database ;3.查詢数据库的计算器的主机名,数据库的实例名及系统的版本。Select host_name, instance_name, versionFrom v$instance ;4.查詢数据库管理系统的版本Select * from v$version ;5.查詢数据库的用户及创建数据库的时间Selec

2、t username, created from dba_users ;6.查詢数据库的控制文件Select * from v$controlfile ;Select valueFrom v$parameterWhere name = control_files ;7.查詢数据库控制文件中全部记录的相关信息Select type, record_size, records_total, records_usedFrom v$controlfile_record_section ;8.查詢数据库的重做日志的配置信息Select group#, members, bytes, status, ar

3、chivedFrom v$log ;9.查詢重做日志(成员文件所存放的具体位置Select * from v$logfile ;10.查詢ORACLE数据库归档文件的具体位置Archive log list11.查詢数据库的表空间以及每个表空间的状态Select tablespace_name, block_size, status, contents, loggingFrom dba_tablespces ;12.查詢表空间的磁盘存储参数Select tablespace_name, initial_extent, next_extent, max_extents,Pct_increase,

4、 min_extlenFrom dba_tablespaces;13.查詢数据库的表空间存放位置以及文件的名字等信息Select file_id, file_name, tablespace_name, status, bytesFrom dba_data_files ;select t.tablespace_name, round(sum(bytes/(1024*1024,0 ts_sizefrom dba_tablespaces t, dba_data_files dwhere t.tablespace_name = d.tablespace_namegroup by t.tablespa

5、ce_nameorder by 1 ;14.查詢临时表空间的数据文件Select f.file#, t.ts#, “File”, “Tablespace”,round(f.bytes/(1024*1024,0 ts_sizeFrom v$tempfile f, v$tablespace tWhere f.ts# = t.ts# ;15.查詢数据库的当前的默认临时表空间Select * from database_propertiesWhere property_name like %DEFAULT% ;16.查詢表空间所对应数据文件的名称及状态Select file

6、#, name, statusFrom v$datafile ;17.查詢表空間所對應的數據文件的尺寸Select file_id, file_name, tablespace_name, bytes/(1024*1024 MB From dba_data_files ;18.查詢报警文件和后台进程追踪文件及用户进程追踪文件的凖确位置Select name, valueFrom v$parameterWh ere name like _dest% ;19.查找运行很久的SQLSELECT /*+ ORDERED */sql_textFROM v$sqltext aWHERE (a.hash_v

7、alue, a.address IN (SELECT DECODE (sql_hash_value,0, prev_hash_value,sql_hash_value,DECODE (sql_hash_value, 0, prev_sql_addr, sql_address FROM v$session bWHERE b.paddr = (SELECT addrFROM v$process cWHERE c.spid = &pidORDER BY piece ASCcolumn username format a12column opname format a16column progress

8、 format a8select username,sid,opname,round(sofar*100 / totalwork,0 | % as progress,time_remaining,sql_textfrom v$session_longops , v$sqlwhere time_remaining 0and sql_address = addressand sql_hash_value = hash_value20.D21.D22.D23.D24.D25.D26.D27.D28.D31.D32.D33.D34.D35.D36.D37.D38.D39.D40.Dd41.G42.G43.G44.G45.G46.G47.G48.G49.G50.G51.G52.G53.G54.G55.G56.G57.G58.G59.G60.G61.G62.G63.G64.G65.G66.G67.G70.G71.G72.G73.G74.G75.G7