oracl常用sql

[shjp123]

oracl常用性能查询及死锁解决办法汇总。

[shjp123]

select count(1) from dictionary;
select * from dba_data_files;
select count(1) from dba_objects t where t.owner='BESTTONE';
select * from dba_tablespaces t where t.tablespace_name='BESTTONE';
select count(1) from dba_tables t where t.owner='BESTTONE';
select t.table_name,t.comments from dictionary t where t.table_name like 'V$%';
select * from dba_constraints;
select * from dba_cons_columns;
select * from dba_tablespaces t where t.tablespace_name='SIM002';
CREATE TABLESPACE SS DATAFILE 'D:\ORACLE\ORADATA\BSTO\SS.DBF' SIZE 1M;
SELECT T.tablespace_name, T.status FROM DBA_TABLESPACES T;
SELECT T.TABLESPACE_NAME,T.FILE_NAME FROM DBA_DATA_FILES T;
ALTER TABLESPACE SIM002 OFFLINE;
ALTER TABLESPACE SIM002 ONLINE;
SELECT T.os_username,
T.username,
T.terminal,
DECODE(T.returncode,
'0',
'Connected',
'1005',
'failedNull',
'1017',
'failed'),
to_char(t.timestamp, 'dd-mon-yy hh24:mi:ss'),
to_char(t.logoff_time, 'dd-mon-yy hh24:mi:ss')
FROM DBA_AUDIT_SESSION T;--尝试登陆审计
select t.action,t.name from audit_actions t;
select * from dba_audit_object;
select * from v$sesstat;
select a.sid,a.value "total cpu time" from v$sesstat a ,v$statname b
where a.STATISTIC#=b.STATISTIC# and b.NAME='cpu used by this session'
order by a.sid;--会话级服务时间值
select sum(time_waited) "total time waited"
from v$system_event
where event not in
('pmon timer', 'smon timer', 'rdbms ipc message',
'parallel dequeue wait', 'virtual circuit',
'SQL*Net message from client', 'client message', 'null event');
select count(*) from v$process; -- 取得数据库目前的进程数。
select value from v$parameter where name = 'processes'; --取得进程数的上限。
--计算总等待时间的实例级查询
select sid,sum(time_waited) "total time waited" from v$session_event
where event !='SQL*Net message from client'
group by sid;
--查看当前有哪些用户正在使用数据
SELECT osuser,
a.username,
cpu_time / executions / 1000000 || 's',
sql_fulltext,
machine
from v$session a, v$sqlarea b
where a.sql_address = b.address
order by cpu_time / executions desc;
--计算总等待时间的会话级查询
SELECT * FROM V$LOG;
ALTER SYSTEM SWITCH LOGFILE;
SELECT * FROM V$LOG_HISTORY;
SELECT * FROM V$logfile;
select name,value from V$PARAMETER WHERE NAME='db_recover_file_dest';
select * from scott.dept;
select trunc(123.12,-1) from dual;
select round(1234.564,9) from dual;
select concat('','11') from dual;
select initcap('ww') from dual;
select instr('wwwweerr','w',-1,1) from dual;
select translate('d2q1wwr','d2','Dwr') FROM DUAL;
SELECT T.TABLESPACE_NAME,SUM(T.BYTES) FROM DBA_FREE_SPACE T GROUP BY T.TABLESPACE_NAME
--查询表空间的空闲大小
select sum(t.GETS),sum(t.GETMISSES) from v$rowcache t;--查询共享池中数据字典缓存的成功与失败次数
select * from user_extents;--查询用户数据段的存储情况
select * from user_indexes;--查看用户的索引段
SELECT * FROM DICTIONARY;
SELECT * FROM DBA_VARRAYS;
SELECT * FROM SYS.OBJ$;
SELECT * FROM DBA_VIEWS;
SELECT * FROM DBA_SYNONYMS;
SELECT * FROM DBA_TS_QUOTAS;
SELECT * FROM DBA_ROLES;
SELECT * FROM DBA_PROFILES;
select (case t.hyml_dm
when '01' then 'A'
when '02' then 'B'
when '03' then 'C'
when '04' then 'D'
when '05' then 'E'
when '06' then 'F'
when '07' then 'G'
when '08' then 'H'
when '09' then 'I'
when '10' then 'J'
when '11' then 'K'
when '12' then 'L'
when '13' then 'M'
when '14' then 'N'
when '15' then 'O'
when '16' then 'P'
when '17' then 'Q'
when '18' then 'R'
when '19' then 'S'
when '20' then 'T'
when '21' then 'U'
when '22' then 'V'
when '23' then 'W'
when '24' then 'X'
when '25' then 'Y'
when '26' then 'Z'
end) as hydmbm,
t.hyml_dm,
t.hyml_mc,
length(t.hyml_dm) as sublenth,
'00' as zb
from dm_hyml t;
select t.TELPHONE,t.NAME,t.KEY,t.ORD,(case t.parent_id when 0 then '' else (SELECT S.NAME FROM T_PRIMENU S WHERE S.ID=T.PARENT_ID) end), v.vox_name voxname
from t_primenu t, t_voxlab v
where t.product_id = '20071120004553169'
and t.vox = v.vox_num
order by t.key, t.ord asc
select id, key, telphone, vox, product_id, ord, name, vox_type, parent_id, areaid,DECODE(PARENT_ID,'0','',(SELECT S.NAME FROM T_PRIMENU S WHERE S.ID=T.PARENT_ID)) AS TNAME
from t_primenu T WHERE PRODUCT_ID='20071120004553169' ;
select level,sys_connect_by_path(T.NAME,'/') path
from T_PRIMENU T
start with T.PARENT_ID=0
connect by prior T.ID =T.PARENT_ID;
select connect_by_isleaf
from T_PRIMENU T
start with T.PARENT_ID=0
connect by prior T.ID =T.PARENT_ID;
select COUNT(1), area_id
from t_area t
GROUP BY area_id
HAVING COUNT(area_id) > 1;
死锁问题查看
SELECT * FROM V$SYSSTAT WHERE CLASS=4--查看数据库是否发生过死锁
select * from V$sysstat WHERE STATISTIC#=23 AND VALUE=0;--死锁会话是否还在连接中
select l.sid,
serial#,
username,
decode(block, 0, 'NO', 'YES') blocker,
decode(request, 0, 'N0', 'YES') waiter
from v$lock l, v$session s
where s.SID=l.SID and (request>0 or block>0) order by block desc;--查看是否有死锁存在


select * from v$system_event where event='enquece';--查看会话等待enquece的时间
select owner, object_type, substr(OBJECT_NAME, 1, 30) OBJECT_NAME
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME; --查看状态为失效的对象
select username,count(username) from v$session where username is not null group by username --查看死锁进程对应的SQL
SELECT /*+ PUSH_SUBQ */

Command_Type, Sql_Text, Sharable_Mem, Persistent_Mem, Runtime_Mem, Sorts,

Version_Count, Loaded_Versions, Open_Versions, Users_Opening, Executions,

Users_Executing, Loads, First_Load_Time, Invalidations, Parse_Calls,

Disk_Reads, Buffer_Gets, Rows_Processed, SYSDATE Start_Time,

SYSDATE Finish_Time, '>' || Address Sql_Address, 'N' Status

FROM V$sqlarea

WHERE Address = (SELECT Sql_Address

FROM V$session

WHERE Sid =560 )
--查看数据库的连接数
--由上级向下遍历
select *
from lmss2_district d
start with d.id = '0200' --上级id
connect by prior d.id = d.parent_id

--由下级向上回朔
select *
from lmss2_district t
start with t.id='0200'
connect by prior t.parent_id=t.id
--查看正在执行的进程对应的SQL
select sql_text
from v$sqlarea a, v$session s
where a.address = s.sql_address;
--查看系统的library cache命中率
SELECT SUM(PINS) "EXECUTIONS",
SUM(RELOADS) "CACHE MISSES WHILE EXECUTING",
1 - SUM(RELOADS)/SUM(PINS)
FROM V$LIBRARYCACHE;
--查看shared pool的使用情况
select sum(bytes)/(1024*1024) from v$sgastat
where pool='shared pool'
and name != 'free memory';
--专用服务模式下，查看cache在内存中的对象的大小，
select sum(sharable_mem) from v$db_object_cache;
--专用服务模式下，查看SQL占用的内存大小，
select sum(sharable_mem) from v$sqlarea;
--Oracle需要为保存每个打开的游标分配大概250字节的内存，以下语句可以计算这部分内存的占用情况，
select sum(250 * users_opening) from v$sqlarea;
-- ora-00600:内部错误，参数19004的解决办法：
execute dbms_stats.delete_schema_stats('hb2db');--hb2db为表空间名

[shjp123]

自己整理了这么多，就先顶一下，哈哈。

[sql_xeppp]

好东西 收藏


3Q

[zw87804600]

值得学习