oracle笔记(个人日常应用记录).docVIP

查看最占资源的进程执行的sql #!/bin/bash ps -e -o pcpu -o pid -o user -o args | grep ora_ | sort -k 1| tail -5r spid=`ps -e -o pcpu -o pid -o user -o args | grep ora_ | sort -k 1| tail -5r | awk {print $2}` for i in $spid do sqlplus -S /nolog EOF conn / as sysdba set feedback off set linesize 200 set pagesize 70 column spid format 99999 column sid format 99999 column module format a20 column username format a8 column sql_text format a60 select distinct c.spid,b.sid,b.username,a.module,a.hash_value,sql_text from v\$sql a,v\$session b,v\$process c where a.hash_value=b.sql_hash_value and a.address=b.sql_address and b.paddr=c.addr and c.spid =$i; exit EOF done 连接数据库 sqlplus /nolog conn /as sysdba 查看被锁的表 select p.spid,a.serial#, c.object_name,b.session_id,b.oracle_username,b.os_user_name from v$process p,v$session a, v$locked_object b,all_objects c where p.addr=a.paddr and a.process=b.process and c.object_id=b.object_id or select all_objects.object_name,S.SID,s.serial#,s.osuser,s.PROGRAM,s.machine,s.CLIENT_INFO from v$lock k, v$session s, ALL_OBJECTS where k.SID = s.SID and k.TYPE IN (TX, TM) and k.id1 = all_objects.object_id; 查看连接的进程 SELECT sid, serial#, username, osuser FROM v$session where osuser = IBM; 杀掉进程 alter system kill session sid,serial# 创建用户 建用户的语法： 创建用户： CREATE USER username PROFILE DEFAULT IDENTIFIED BY password DEFAULT TABLESPACE UserTableSpaceName TEMPORARY TABLESPACE TempTableSpaceName ACCOUNT UNLOCK; 删除用户： DROP USER username; 修改表空间： ALTER USER username DEFAULT TABLESPACE OtherUserTableSpaceName; ALTER USER username TEMPORARY TABLESPACE OtherTempTableSpaceName; 修改口令： ALTER USER username INDENTIFIED BY NewPassword; 查看表结构ddl sqlplus 登陆 Set pages 999; set long 90000; select dbms_metadata.get_ddl(TABLE,SZT_PQSO2,SHQSYS) from dual; 第一个参数为对象类型，如TABLE、VIEW等，