em上查询数据库基本信息

admin · 发表于 2020-5-30 07:31:15

em上管理得数据库多了以后，有时候需要查询所有数据库得特定信息，这是可以对em管理库进行自定义得查询来满足不同得需求

--查询platform平台数量分布
select PLATFORM_NAME, count(*)
from db_database
group by PLATFORM_NAME
order by PLATFORM_NAME
--查询os平台的数量
select os,count(*) from dblist group by os order by 2 desc
--查询version的数量
select os,count(*) from dblist group by os order by 2 desc
--查询没有开启归档的数据库
select d.dbname, a.app, a.mip, d.log_mode
from db_database d, dblist a
where d.dbname = a.dblink
and d.log_mode = 'NOARCHIVELOG'
--查询只有1个控制文件的数据库
select dbname, count(*)
from db_controlfile
group by dbname
having count(*) < 2;
--查询按年份创建的数据库
select substr(to_char(created, 'yyyymmdd'), 1, 4) c_year, count(*)
from db_database
group by substr(to_char(created, 'yyyymmdd'), 1, 4)
order by substr(to_char(created, 'yyyymmdd'), 1, 4)
--查询具有dba权限的用户
break on dbname on app on mip
select r.dbname, d.app, d.mip, r.grantee user_with_dba
from db_role r, dblist d
where d.dblink(+) = r.dbname
and r.granted_role = 'DBA'
and r.grantee not in ('SYS',
'SYSTEM',
'SYSMAN',
'GOLDENGATE',
'OGG',
'OGGUSER',
'DIRECTOR')
order by 2
--查询具有dba权限的用户的历史情况，按数据库，按月份排序
select dbname, to_char(time, 'yyyy-mm-dd') time, count(*)
from hist_role
where granted_role = 'DBA'
group by dbname, to_char(time, 'yyyy-mm-dd')
order by 1, 2;
--查询运行超过1年的数据库
select l.dbname, d.app, d.mip, l.instance_name, l.uptime "DAYS"
from dblist d, db_instance l
where d.dblink = l.dbname
and l.uptime > 365
order by l.instance_name, l.uptime desc
--查询asm_diskgroup可用空间
select a.dbname,
d.app,
a.group_number,
a.name,
a.total_mb,
a.free_mb,
round(NVL(a.free_mb / a.total_mb * 100, 0), 0) pct_free
from dblist d, db_asmdg a
where d.dblink = a.dbname
and round(NVL(a.free_mb / a.total_mb * 100, 0), 0) < '&pct_value'
order by pct_free
--查询不可用对象
select t.dbname, d.app, t.owner, t.object_type, count(object_name) count
from dblist d, db_object t
where d.dblink = t.dbname
group by t.dbname, d.app, t.owner, t.object_type
order by t.dbname, t.owner
--查询特定值的parameter
select p.dbname, d.app, d.mip, p.pname, p.value
from dblist d, db_parameter p
where d.dblink = p.dbname
and p.pname = '&pname'
--查询实例会话数量
select s.dbname, d.app, d.mip, s.inst_id, count(*) session_count
from dblist d, db_session s
where d.dblink = s.dbname
and d.jiagou = 'RAC'
group by s.dbname, d.app, d.mip, s.inst_id
order by 1, 2, 3
--查询数据文件位置
select dbname,
count(distinct(substr(file_name, 0, 1))) file_type
from db_datafile
group by dbname
having count(distinct(substr(file_name, 0, 1))) > 1
order by file_type desc
--查询数据库的数据量
select dbname,
round(sum(total_mb) / 1024, 0) total_gb,
round(sum(used_mb) / 1024, 0) used_gb
from db_datafile
group by dbname
order by total_gb desc
--查询需要扩容的表空间
select dbname, name, ts_size, free, pct_free
from db_tablespace
where type = 'PERMANENT'
and pct_free < 15
and free < 10000
order by 1, 2, 5
--查询特定表空间的使用量
select dbname, name, ts_size, free, pct_free
from db_tablespace
where type = 'PERMANENT'
and name = '&TS_name'
order by 3 desc
--查询1月内创建的用户
select u.dbname, d.app, u.username, u.created, u.default_tablespace, u.profile
from db_user u, dblist d
where u.dbname = d.dblink
and u.account_status = 'OPEN'
and u.created > sysdate - 30
--查询test账户
select u.dbname, d.app, d.mip, u.username, u.account_status
from db_user u, dblist d
where u.dbname = d.dblink
and u.username like '%TEST%'
order by 5

复制代码

		自动登录	找回密码
密码			立即注册

[日常管理] em上查询数据库基本信息

浏览过的版块