Oracle 常用语句
创建用户
create user dev identified by iampaawd;
grant connect, resource to dev;
查看表
SELECT * FROM USER_TABLES;
select * from user_tab_comments;
select * from user_col_comments;
查看执行计划
'%SELECT DISTINCT%' 为你要找的SQL,'3039j4pnpw0sf'是你找到的SQL_ID
SELECT SQL_ID, SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%SELECT DISTINCT%';
SELECT * FROM v$sql_plan WHERE SQL_ID='3039j4pnpw0sf';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('3039j4pnpw0sf',0));
select * from table( dbms_xplan.display_cursor('3039j4pnpw0sf'));
查看序列
select sequence_name, min_value, max_value, increment_by, last_number from user_sequences;
select SEQUENCE_OWNER,SEQUENCE_NAME from dba_sequences where sequence_owner='用户名';
查看最近执行的SQL
SELECT sql_text, last_load_time,SQL_ID FROM v$sql WHERE last_load_time IS NOT NULL and sql_text like 'select%' ORDER BY last_load_time DESC;
查看SQL绑定的参数
select name,datatype_string,VALUE_STRING from v$sql_bind_capture where sql_id='0bgqvw16fpts0';
查看执行SQL的机器名称
select * from dba_hist_active_sess_history t where t.sql_id='3wr2mysfyh36n'
当前的连接数
select count(*) from v$process;
查看最大连接数
SELECT * FROM V$parameter WHERE NAME = 'processes';
修改最大连接数
ALTER SYSTEM SET processes=1500 SCOPE=spfile;
shutdown immediate;
startup;
服务端字符集
select userenv('language') from dual;
select * from nls_database_parameters where parameter like '%CHARACTERSET%';
客户端字符集
select * from nls_instance_parameters;
修改字符集 和 时区
su oracle
sqlplus /nolog
conn /as SYSDBA
alter database set time_zone='+8:00';
shutdown immediate;
startup MOUNT
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
alter database open;
ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16;
select * from v$nls_parameters;
shutdown immediate;
startup
EXIT
EXIT
创建表空间
chmod 700 /home
su oracle
sqlplus /nolog
conn /as SYSDBA
CREATE TABLESPACE TBS_HISUSER DATAFILE '/home/TABLESPACE01' SIZE 100M autoextend on;
EXIT
EXIT
导出表结构
SELECT t1.Table_Name AS "表名称",
t3.comments AS "表说明",
t1.Column_Name AS "字段名称",
t1.Data_Type AS "数据类型",
t1.Data_Length AS "长度",
t1.NullAble AS "是否为空",
t2.Comments AS "字段说明",
t1.Data_Default "默认值"
FROM cols t1 left join user_col_comments t2
on t1.Table_name=t2.Table_name and t1.Column_Name=t2.Column_Name
left join user_tab_comments t3
on t1.Table_name=t3.Table_name
WHERE NOT EXISTS ( SELECT t4.Object_Name FROM User_objects t4
WHERE t4.Object_Type='TABLE'
AND t4.Temporary='Y'
AND t4.Object_Name=t1.Table_Name )
ORDER BY t1.Table_Name, t1.Column_ID;
创建同义词
create public synonym 表名 for 用户名.表名;
表授权
GRANT SELECT , DELETE , UPDATE , INSERT ON TWBAS_ROOM_EXTEND TO 用户名
查看会话数排序 GV$Session 集群所有连接数数据
SELECT *
FROM (SELECT PROGRAM,MACHINE, COUNT(1) AS LINKTOTAL
FROM GV$SESSION
WHERE PROGRAM='w3wp.exe' --连接进程
GROUP BY PROGRAM,MACHINE) A
ORDER BY LINKTOTAL DESC;
查询超过30分钟未活动的会话 如果查集群所有则使用GV$SESSION表
--LAST_CALL_ET：上次执行sql后到当前时间间隔的时间（单位为秒）
SELECT SID, SERIAL#, MODULE, STATUS, S.LAST_CALL_ET, MACHINE
FROM V$SESSION S
WHERE 1 = 1
AND S.USERNAME IS NOT NULL
AND S.LAST_CALL_ET <= 30 * 60
--AND PROGRAM = 'w3wp.exe'
AND S.STATUS = 'INACTIVE'
ORDER BY S.LAST_CALL_ET DESC;
事务锁表查询
--TM表示表锁或DML锁，TX表示行锁或事务锁
SELECT S.SID,
S.SERIAL#,
S.USERNAME,
S.SCHEMANAME,
S.OSUSER,
S.PROCESS,
S.MACHINE,
S.TERMINAL,
S.LOGON_TIME,
L.TYPE
FROM V$SESSION S, V$LOCK L
WHERE S.SID = L.SID
AND S.USERNAME IS NOT NULL
AND L.TYPE IN ('TX', 'TM')
ORDER BY SID;
alter system kill session 'SID,SERIAL#' immediate;
查看当前所有用户的会话(SESSION)：
SELECT * FROM V$SESSION
WHERE USERNAME IS NOT NULL
ORDER BY LOGON_TIME , SID;
其中Oracle内部进程的USERNAME为空
查看当前用户的所有SESSION：
SELECT * FROM V$SESSION
WHERE USERNAME = USER
ORDER BY LOGON_TIME, SID;
查看当前窗口/当前用户的会话信息
SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE AUDSID=USERENV('SESSIONID');
查看所有ACTIVE会话(活动会话)
SELECT * FROM V$SESSION
WHERE USERNAME IS NOT NULL AND STATUS='ACTIVE'
ORDER BY LOGON_TIME, SID;
查看当前会话的ID可以通过如下脚本:
SELECT * FROM V$MYSTAT WHERE ROWNUM =1
查看当前用户的SPID
SELECT P.SPID, S.SID, S.SERIAL#
FROM V$PROCESS P
INNER JOIN V$SESSION S ON P.ADDR = S.PADDR
WHERE S.AUDSID=USERENV('SESSIONID');
查看数据库允许最大会话数
SHOW PARAMETER SESSIONS;
SELECT NAME, TYPE, VALUE FROM V$PARAMETER WHERE NAME LIKE 'session%'
查看曾经的最大会话数：
SELECT SESSIONS_MAX,SESSIONS_WARNING,SESSIONS_CURRENT,SESSIONS_HIGHWATER
FROM v$license
SESSIONS_HIGHWATER表示曾经的最大会话数512
查询那些应用的连接数此时是多少
SELECT B.PROGRAM , COUNT(1)
FROM V$PROCESS A, V$SESSION B
WHERE A.ADDR = B.PADDR
AND B.USERNAME IS NOT NULL
GROUP BY B.PROGRAM
关于会话信息
通过如下SQL你可以查询你的每个应用程序到底在等待什么，从而针对这些信息对数据库的性能进行调整。
COL USERNAME FOR A12;
COL PROGRAM FOR A32;
COL EVENT FOR A26;
SELECT S.USERNAME
,S.PROGRAM
,S.STATUS
,SE.EVENT
,SE.TOTAL_WAITS
,SE.TOTAL_TIMEOUTS
,SE.TIME_WAITED
,SE.AVERAGE_WAIT
FROM V$SESSION S, V$SESSION_EVENT SE
WHERE S.SID=SE.SID AND SE.EVENT NOT LIKE 'SQL*Net%'
AND S.STATUS ='ACTIVE' AND S.USERNAME IS NOT NULL;
ORACLE中查询被锁的表并释放session
SELECT A.OWNER
,A.OBJECT_NAME
,B.XIDUSN
,B.XIDSLOT
,B.XIDSQN
,B.SESSION_ID
,B.ORACLE_USERNAME
,B.OS_USER_NAME
,B.PROCESS
,B.LOCKED_MODE
,C.MACHINE
,C.STATUS
,C.SERVER
,C.SID
,C.SERIAL#
,C.PROGRAM
FROM ALL_OBJECTS A,V$LOCKED_OBJECT B,SYS.GV_$SESSION C
WHERE A.OBJECT_ID = B.OBJECT_ID AND B.PROCESS = C.PROCESS ORDER BY 1,2;
查看占用系统IO较大的session
SELECT se.sid
,se.serial#
,pr.spid
,se.username
,se.status
,se.terminal
,se.program
,se.module
,se.sql_address
,st.event
,st.p1text
,si.physical_reads
,si.block_changes
FROM v$session se,v$session_wait st,v$sess_io si,v$process pr
WHERE st.sid=se.sid AND st.sid=si.sid
AND se.paddr=pr.ADDR AND se.sid>6
AND st.wait_time=0 AND st.event NOT LIKE '%SQL%'
ORDER BY physical_reads DESC;
找出耗cpu较多的session
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#=12 and c.sid=a.sid and a.paddr=b.addr
order by value
参考
https://www.cnblogs.com/kerrycode/p/4100865.html
本文所有SQL，均为网络搜集。
