几个平时用的数据库语句

上一篇 / 下一篇  2010-11-25 12:18:47 / 个人分类:数据库

1、统计游标:

select * from v$open_cursor where USER_NAME = 'TEST'

select count(sub.state) as fail_num from task ts inner join
select count(sub.state) as suc_num from task ts inner join s

select user_name from v$open_cursor

select * from v$session_connect_info
select count(*) from v$session_connect_info where suser='smp'

2、启动数据库

su - oracle
sqlplus / as sysdba
shutdown immediate
startup force

启动LISTENER 和 sqlplus
执行:
bash-3.00$ $ORACLE_HOME/bin/lsnrctl start(启动监听)

执行:
bash-3.00$ $ORACLE_HOME/bin/sqlplus /NOLOG

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 4 10:22:29 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup;

3、导入导出

导出:
exp username/pwd@hdmdb file=HC.dmp log=HC.log full=y
导入:
imp username/pwd@hdmdb file=HC.dmp log=HC.log full=y

4、==========创建数据库============
  -- Create the user
create user HC
  identified by "HC";
-- Grant/Revoke role privileges
grant connect to HC;
grant resource to HC;
-- Grant/Revoke system privileges
grant create procedure to HC;
grant create sequence to HC;
grant create session to HC;
grant create synonym to HC;
grant create table to HC;
grant create trigger to HC;
grant create view to HC;
grant debug any procedure to HC;
grant debug connect session to HC;
grant create tablespace to HC;

5、抓包:

  snoop -o snoop09301512-od.cap -d ce5 host 192.168.1.123 and port 7001

6、强制释放oracle连接2009-04-01 20:10

sqlplus /nolog
打开sqlplus


connect system/bianqiwei@orcltns as sysdba
使用具有dba权限得用户登陆oracle


show parameter resource_limit
显示资源限定是否开启,value为true是开启,为false是关闭


alter system set resource_limit=true
如果未开启,则使用此命令开启资源限定功能


create profile profileName limit connect_time 60 idle_time 30
创建profile文件,profileName任意起,connect_time设置连接超过多少分钟后强制释放,idle_time设置连续不活动的会话超过多少分钟后强制释放

alter user oracleUser profile profileName
将profile文件作用于指定用户
 
7、删除orabm

$ sqlplus / as sysdba

SQL> drop user orabm cascade;

(通过开源工具orabm计算TPS值来测试服务器CPU性能可参见http://hi.baidu.com/edeed/blog/item/e79b8db1507d1c5d092302d8.html)


TAG:

 

评分:0

我来说两句

Open Toolbar