数据字典SQL查询语句

数据字典sql查询语句
1.数据字典视图(动态性能表)
V$fixed_table / dictionary / dict / dict_columns
2.查詢数据库的名字，创建日期等内容。

Select name, created, log_mode, open_mode
From v$database ;
3.查詢数据库的计算器的主机名，数据库的实例名及系统的版本。

Select host_name, instance_name, version
From v$instance ;
4.查詢数据库管理系统的版本
Select * from v$version ;
5.查詢数据库的用户及创建数据库的时间
Select username, created from dba_users ;
6.查詢数据库的控制文件
Select * from v$controlfile ;
Select value
From v$parameter
Where name = …control_files‟ ;
7.查詢数据库控制文件中全部记录的相关信息
Select type, record_size, records_total, records_used
From v$controlfile_record_section ;
8.查詢数据库的重做日志的配置信息
Select group#, members, bytes, status, archived
From v$log ;
9.查詢重做日志(成员)文件所存放的具体位置
Select * from v$logfile ;
10.查詢ORACLE数据库归档文件的具体位置
Archive log list
11.查詢数据库的表空间以及每个表空间的状态
Select tablespace_name, block_size, status, contents, logging
From dba_tablespces ;
12.查詢表空间的磁盘存储参数
Select tablespace_name, initial_extent, next_extent, max_extents,
Pct_increase, min_extlen
From dba_tablespaces;
13.查詢数据库的表空间存放位置以及文件的名字等信息
Select file_id, file_name, tablespace_name, status, bytes
From dba_data_files ;
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name
order by 1 ;
14.查詢临时表空间的数据文件
Select f.file#, t.ts#, “File”, “Tablespace”,
round(f.bytes/(1024*1024),0) ts_size
From v$tempfile f, v$tablespace t
Where f.ts# = t.ts# ;
15.查詢数据库的当前的默认临时表空间
Select * from database_properties
Where property_name like …%DEFAULT%‟ ;
16.查詢表空间所对应数据文件的名称及状态
Select file#, name, status
From v$datafile ;
17.查詢表空間所對應的數據文件的尺寸
Select file_id, file_name, tablespace_name, bytes/(1024*1024) MB From dba_data_files ;
18.查詢报警文件和后台进程追踪文件及用户进程追踪文件的凖确位置
Select name, value
From v$parameter
Wh ere name like …_dest%‟ ;
19.查找运行很久的SQL
SELECT /*+ ORDERED */
sql_text
FROM v$sqltext a
WHERE (a.hash_value, 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 b
WHERE b.paddr = (SELECT addr
FROM v$process c
WHERE c.spid = '&pid'))
ORDER BY piece ASC
column username format a12
column opname format a16
column progress format a8
select username,sid,opname,
round(sofar*100 / totalwork,0) || '%' as progress,
time_remaining,sql_text
from v$session_longops , v$sql
where time_remaining <> 0
and sql_address = address
and sql_hash_value = hash_value
20.D
21.D
22.D
23.D
24.D
25.D
26.D
27.D
28.D
31.D
32.D
33.D
34.D
35.D
36.D
37.D
38.D
39.D
40.Dd
41.G
42.G
43.G
44.G
45.G
46.G
47.G
48.G
49.G
50.G
51.G
52.G
53.G
54.G
55.G
56.G
57.G
58.G
59.G
60.G
61.G
62.G
63.G
64.G
65.G
66.G
67.G
70.G
71.G
72.G
73.G
74.G
75.G
76.G
77.G
78.G
79.G
80.G
81.G
82.G
83.G
84.G
85.G
86.G
87.G
88.G
89.G
90.G
91.G
92.G
93.G
94.G
95.G
96.G
97.G
98.G
99.G 100.G 101.GG 102.G 103.G 104.G 105.G。