个人简介
偶尔有空上来看看
文章分类
文章存档
最近访客
相关博文
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
分类： 数据库开发技术
2023-11-15 15:20:03
查看dm.ini
-
ps -ef|grep dms|grep -v grep|awk '{print $9}'
-
- ps -ef|grep dms|grep -v grep|awk '{print $9}'|awk -f"=" '{print $2}'
-
--表空间使用
-
set pagesize 100
-
-
select f.tablespace_name,round(t.total_space / 1024,1) total_g,
-
round((t.total_space - f.free_space) / 1024,1) used_g,round(f.free_space / 1024,1) free_g,
-
round(((t.total_space - f.free_space) / t.total_space) * 100) "per_used%"
-
from (select tablespace_name,round(sum(blocks *
-
(select para_value / 1024 from v$dm_ini
-
where para_name = 'global_page_size') / 1024)) free_space
-
from dba_free_space group by tablespace_name) f,
-
(select tablespace_name, round(sum(bytes / 1048576)) total_space
-
from dba_data_files group by tablespace_name) t
-
where f.tablespace_name = t.tablespace_name order by 5 desc;
-
-
--监控运行时错误历史
-
select * from v$runtime_err_history;
-
-
--死锁历史信息记录
-
select * from v$deadlock_history;
-
--查看数据库是否存在阻塞
-
with trx_tab as
-
(select o1.name,l1.trx_id from v$lock l1,sysobjects o1 where l1.table_id=o1.id and o1.id<>0),
-
trx_sess as (
-
select l.trx_id wt_trxid, l.row_idx blk_trxid,l.blocked,(select name table_name from trx_tab a where a.trx_id=l.trx_id) wt_table,
-
s1.sess_id wt_sess,s2.sess_id blk_sess,
-
s1.user_name wt_user_name,s2.user_name blk_user_name,s1.sql_text,s1.clnt_ip,datediff(ss, s1.last_send_time, sysdate) ss
-
from v$lock l,v$sessions s1,v$sessions s2
-
where l.trx_id=s1.trx_id and l.row_idx=s2.trx_id)
-
select sysdate stattime,* from trx_sess where blocked=1;
-
-
-
--按小时统计arch
-
select to_char(create_time,'yy-mm-dd hh24') xiaoshi,round(sum(free)/1024/1024) mb,count(0) cnt from v$arch_file group by to_char(create_time,'yy-mm-dd hh24') order by 1;
-
-
-
-
--最慢的 20 条 sql
-
select top 20 start_time,time_used/1000 time_used,top_sql_text from v$sql_history order by time_used desc;
-
select top 20 * from v$system_long_exec_sqls order by exec_time desc;
-
--高内存的 20 条 sql 信息
-
select top 20 * from v$system_large_mem_sqls order by mem_used_by_k desc;
-
-
--查看所有作业信息
-
select a.id,a.name,a."enable",a.username,a.createtime,a.modifytime,a.describe,
-
b.last_date||' '||b.last_sec last_time,b.next_date||' '||b.next_sec next_time,b.what
-
from sysjob.sysjobs a,sysjob.user_jobs b
-
where a.id=b.job;
-
-
-
--用户权限
-
select * from (
-
select grantee,granted_role privilege,'role_privs' privilege_type,case admin_option when 'y' then 'yes' else 'no' end admin_option from dba_role_privs
-
union select grantee,privilege,'sys_privs' privilege_type,admin_option from dba_sys_privs
-
union select grantee,privilege||' on '||owner||'.'||table_name privilege,'table_privs' privilege_type,grantable from dba_tab_privs
-
)
-
where grantee in (select username from all_users where username not in ('sys','sysdba','syssso','sysauditor') )
- order by grantee,privilege_type,privilege;
给主人留下些什么吧！~~