Oracle 查看资源占用及死锁的处理思路及SQL

 
  • 查看当前在用DB的用户
  • 查看用户正在执行的语句
  • 查看锁住的语句
  • 查看用户在那个机器链接进来
  • 查看哪个Session的CPU占用率最高
  • 查看数据库的最大并发链接
  • 查看历史最大并发数
  • 查看当前并发数
  • 查看某个用户所具备的角色和权限
  • 查看数据库版本
  • 查看patch情况
  • 查看服务器端的parameter
  • 杀Session
  • 看某个Session对应的操作系统PID
  • 。。。


select username,default_tablespace from user_users;
select * from user_role_privs;
select * from user_sys_privs;
select * from user_tab_privs;
select * from user_tables;
select object_name,object_id from user_objects
                where instr(object_name,'LOG')>0;
select object_name,created from user_objects where object_name=upper

('&table_name');
select * from user_sequences;
select view_name from user_views;
select member from v$logfile;
Select version FROM Product_component_version
    Where SUBSTR(PRODUCT,1,6)='Oracle';
用系统管理员,查看当前数据库有几个用户连接:

SQL> select username,sid,serial# from v$session;

如果要停某个连接用

SQL> alter system kill session 'sid,serial#';

如果这命令不行,找它UNIX的进程数

SQL> select pro.spid from v$session ses,v$process pro where ses.sid=21

and ses.paddr=pro.addr;

说明:21是某个连接的sid数

然后用 kill 命令杀此进程号。


select column_name,comments from dict_columns where

table_name='USER_INDEXES';

select column_name,comments from dict_columns where upper(table_name)

='v$session';
如何查看最大会话数?
SELECT * FROM V$PARAMETER WHERE NAME LIKE 'proc%';
Select * from v$license;
SELECT *  FROM  USER_ERRORS;
SELECT * FROM V$INSTANCE;
select a.sid,spid,status,substr(a.program,1,40)

prog,a.terminal,osuser,value/60/100 value
     from v$session a,v$process b,v$sesstat c
     where c.statistic#=11 and c.sid=a.sid and a.paddr=b.addr order by

value desc;

select * from v$sqltext where address = 'C0000001E0B4A160';
select sql_text, piece, hash_value
from v$sqltext
where  address = 'C0000001E0B4A160'
order by hash_value, piece;

select * from v$sqlarea where address = (select sql_address from

v$session where sid = :1);

 


Oracle 连接拒绝:

2011-11-03 14:44:55,546 ERROR [main]com.jolbox.bonecp.ConnectionHandle.obtainInternalConnection(ConnectionHandle.java:229) - Failed to acquire connection. Sleeping for 7000ms. Attempts left: 5
java.sql.SQLException: Io 异常: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=186646784)(ERR=12519)(ERROR_STACK=(ERROR=(CODE=12519)(EMFI=4))))
 at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:189)
 at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:231)
 at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:345)
 at oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:428)
 at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.java:515)
 at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:345)
 at java.sql.DriverManager.getConnection(DriverManager.java:582)
 at java.sql.DriverManager.getConnection(DriverManager.java:185)
 at com.jolbox.bonecp.BoneCP.obtainRawInternalConnection(BoneCP.java:256)
 at com.jolbox.bonecp.ConnectionHandle.obtainInternalConnection(ConnectionHandle.java:211)
 at com.jolbox.bonecp.ConnectionHandle.<init>(ConnectionHandle.java:170)
 at com.jolbox.bonecp.BoneCP.<init>(BoneCP.java:371)
 at com.jolbox.bonecp.BoneCPDataSource.maybeInit(BoneCPDataSource.java:150)
 at com.jolbox.bonecp.BoneCPDataSource.getConnection(BoneCPDataSource.java:112)
 at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)
 at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)
 at org.mybatis.spring.SqlSessionUtils.getSqlSession(SqlSessionUtils.java:116)
 at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:333)
 at $Proxy8.selectList(Unknown Source)
 at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:189)
 at org.apache.ibatis.binding.MapperMethod.executeForList(MapperMethod.java:100)
 at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:70)
 at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:38)
 at $Proxy14.findALLData(Unknown Source)
 at com.conac.framework.service.security.PermissionService.init(PermissionService.java:126)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:597)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeCustomInitMethod(AbstractAutowireCapableBeanFactory.java:1544)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1485)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1417)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:519)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
 at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:291)
 at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
 at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:288)
 at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
 at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:322)
 at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:106)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1325)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1086)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:517)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
 at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:291)
 at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
 at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:288)
 at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
 at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:322)
 at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:106)
 at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveManagedList(BeanDefinitionValueResolver.java:353)
 at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:153)
 at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveManagedMap(BeanDefinitionValueResolver.java:378)
 at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:161)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1325)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1086)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:517)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
 at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:291)
 at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
 at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:288)
 at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
 at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:580)
 at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:895)
 at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:425)
 at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:282)
 at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:204)
 at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:47)
 at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4205)
 at org.apache.catalina.core.StandardContext.start(StandardContext.java:4704)
 at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053)
 at org.apache.catalina.core.StandardHost.start(StandardHost.java:840)
 at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053)
 at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:463)
 at org.apache.catalina.core.StandardService.start(StandardService.java:525)
 at org.apache.catalina.core.StandardServer.start(StandardServer.java:754)
 at org.apache.catalina.startup.Catalina.start(Catalina.java:595)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:597)
 at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:289)
 at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:414)

 

修改数据库连接数以解决上述问题:

c:\sqlplus /nolog
SQL>conn / as sysdba

SQL> show parameter processes;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
job_queue_processes integer 10
log_archive_max_processes integer 1
processes integer 150

SQL> alter system set processes=300 scope = spfile;

系统已更改。

SQL> show parameter processes;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
job_queue_processes integer 10
log_archive_max_processes integer 1
processes integer 150

SQL> create pfile from spfile;

文件已创建。

重启数据库,OK!

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值