oracle 常用SQL

查看当前会话占用undo最多的会话

set linesize 200 pagesize 1000
col module for a20 trunc
col username for a12 trunc
col sid for a12 trunc
select *from (
SELECT a.sid||'_'||a.serial# sid,a.username,a.sql_id,round(b.used_ublk*8/1024,1) size_m,b.used_urec,last_call_et, a.module FROM V$SESSION A,V$TRANSACTION B
WHERE A.TADDR=B.ADDR
AND A.TADDR IS NOT NULL
order by b.used_ublk desc) 
where rownum<=50;

oracle 分析进程占用cpu极高的资源

oracle 分析进程占用cpu极高的资源
top
ps -ef | grep pid
根据getsql获取具体SQL
select /*+ ordered */ sql_text,sql_id from v$sqltext a where (a.hash_value, a.address) in
 (select decode(sql_hash_value, 0, prev_hash_value,sql_hash_value),decode(sql_hash_value, 0, prev_sql_addr,sql_address) from v$session b where b.paddr
 = (select addr from v$process c where c.spid = '&pid')) order by piece asc
 /

查看表的大小

SQL>select owner,
       segment_name，
       segment_type,
       tablespace_name,
       round(bytes / 1024 / 1024 / 1024, 0) GB
  from dba_segments
 where segment_name='TEST';

OWNER   SEGMENT_NAME  SEGMENT_TYPE   TABLESPACE_NAME     GB
------- ------------  -------         ----------------- ----
SCOTT    TEST          TABLE             USERS           10

获取表的定义

SQL>select dbms_metadata.get_ddl('TABLE',upper('&i_table_name'),upper('&i_owner')) from dual;

查看表的依赖关系

SQL>select * from user_dependencies t where t.referenced_name = 'TEST';

查看对象的状态：

SQL>select owner, object_name, object_type, status from dba_objects where object_name in ('TEST_PKG','TEST1_PKG');

重新编译对象：

SQL>select 'ALTER ' ||
       decode(object_type, 'PACKAGE BODY', 'PACKAGE', object_type) || ' ' ||
       owner || '.' || OBJECT_NAME ||
       decode(object_type,
              'PACKAGE BODY',
              ' COMPILE BODY ; ',
              'PACKAGE',
              ' COMPILE SPECIFICATION ; ',
              ' COMPILE; ') aa
  from dba_objects
 where status <> 'VALID'
   and dba_objects.owner in ('SCOTT')
   AND object_name in ('TEST_PKG','TEST1_PKG');

查看谁占用了undo表空间

select r.name,rssize/1024/1024/1024 "resize(G)",
       s.sid,s.serial#,s.username,s.status, s.sql_hash_value,s.sql_address,s.machine
	   ,substr(s.program,1,78),r.usn,hwmsize/1024/1024/1024,shrinks,xacts
	   from v$session s, v$transaction t,v$rollname,v$rollstat rs
	   where t.addr=s.taddr
	   and t.xidusn = r.usn
	   and r.usn = rs.usn
	    order by rssize desc;

查看谁占用了temp表空间

select t.blocks*16/1024/1024, s.username, s.schemaname,t.tablespace,t.segtype,t.extents,s.program,
    s.osuser,
	s.terminal,
	s.sid,
	s.serial#,
	sql.sql_text
	from v$sort_usage t, v$session s, v$sql sql
	where t.session_addr = s.saddr
	and t.sqladdr = sql.address
	and t.sqlhash = sql.hash_value;
       
-- 根据pid获取SQL
select addr from v$process t where spid = 39345;
select * from v$session t where paddr=addr;
select * from v$sql where sql_id = ?