查看ORACLE数据库信息的一些SQL

最新推荐文章于 2024-05-30 07:33:52 发布

Paulmin

最新推荐文章于 2024-05-30 07:33:52 发布

阅读量1.1k

点赞数

分类专栏： Oracle

本文链接：https://blog.csdn.net/Paulmin/article/details/16857785

版权

Oracle 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

--转自 http://blog.csdn.net/great_domino/article/details/323359 --

-3 查看数据库sql执行时间较长的

select sql_id, sql_text, sql_exec_start, elapsed_time/1000000 as 执行时间, sql_exec_id, session_serial#, 
      cpu_time, physical_read_requests, physical_write_requests, user_io_wait_time, plsql_exec_time
from v$sql_monitor 
where elapsed_time/1000000 > 10--sql_text like '%p_handle_supplier_demand%'
order by sql_exec_start;

select sql_id, sql_fulltext/*clob*/, plan_hash_value, elapsed_time, cpu_time, executions from v$sql;

-2、查看数据库的最大连接数以及目前连接数（或可以为你设计数据库连接池时提供依据）

SELECT name,value  From V$parameter Where Name = 'sessions'; 
NAME        VALUE
sessions    555
注：最大连接数 555

  Select * From V$resource_Limit Where Resource_Name='sessions';
RESOURCE_NAME    CURRENT_UTILIZATION    MAX_UTILIZATION    INITIAL_ALLOCATION    LIMIT_VALUE
sessions             155                     295                  555                 555
注：
RESOURCE_NAME            资源名
CURRENT_UTILIZATION        资源的当前使用量
MAX_UTILIZATION            实例启动以来，资源使用达到的最大值
INITIAL_ALLOCATION        初始化值，一般等于limit_value
LIMIT_VALUE                系统设置的资源值

  Select Count(*)  FROM v$session;
在本视图 v$session 中，每一个连接到数据库实例中的session都拥有一条记录。包括用户session及后台进程如DBWR，LGWR，arcchiver等等。
SADDR - session address
SID - session identifier 常用于链接其他列
SERIAL# - SID有可能会重复，当两个session的SID重复时，SERIAL#用来区别session（说白了某个session是由sid和serial#这两个值确定的）
AUDSID - audit session id。可以通过audsid查询当前session的sid。select sid from v$session where audsid=userenv('sessionid');
PADDR - process address,关联v$process的addr字段，通过这个可以查询到进程对应的session
USER# - 同于dba_users中的user_id，Oracle内部进程user#为0.
USERNAME - session's username。等于dba_users中的username。Oracle内部进程的username为空。
COMMAND - session正在执行的sql id，1代表create table,3代表select。
TADDR - 当前的transaction address。可以用来关联v$transaction中的addr字段。
LOCKWAIT - 可以通过这个字段查询出当前正在等待的锁的相关信息。sid + lockwait与v$loc中的sid + kaddr相对应。
STATUS - 用来判断session状态。Active:正执行SQL语句。inactive:等待操作。killed:被标注为杀死。
SERVER - 服务类型。
SCHEMA# - schema user id。Oracle内部进程的schema#为0。
SCHEMANAME - schema username。Oracle内部进程的为sys。
OSUSER - 客户端操作系统用户名。
PROCESS - 客户端process id。
MACHINE - 客户端machine name。
TERMINAL - 客户端执行的terminal name。
PROGRAM - 客户端应用程序。比如ORACLE.EXE或sqlplus.exe
TYPE - session类型。
SQL_ADDRESS,SQL_HASH_VALUE,SQL_ID,SQL_CHILD_NUMBER - session正在执行的sql状态，和v$sql中的address,hash_value,sql_id,child_number对应。
PREV_SQL_ADDR,PREV_HASH_VALUE,PREV_SQL_ID,PREV_CHILD_NUMBER - 上一次执行的sql状态。
MODULE,MODULE_HASH,ACTION,ACTION_HASH,CLIENT_INFO - 应用通过DBMS_APPLICATION_INFO设置的一些信息。
FIXED_TABLE_SEQUENCE - 当session完成一个user call后就会增加的一个数值，也就是说，如果session挂起，它就不会增加。因此可以根据这个字段来监控某个时间点以来的session性能情况。例如，一个小时前某个session的此字段数值为10000，而现在是20000，则表明一个小时内其user call较频繁，可以重点关注此session的performance statistics。
ROW_WAIT_OBJ# - 被锁定行所在table的object_id。和dba_object中的object_id关联可以得到被锁定的table name。
ROW_WAIT_FILE# - 被锁定行所在的datafile id。和v$datafile中的file#关联可以得到datafile name。
ROW_WAIT_BLOCK# - 同上，对应块。
ROW_WAIT_ROW# - session当前正在等待的被锁定的行。
LOGON_TIME - session logon time.

-1、查看数据表的某个字段

SELECT * FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME='ABNMAL_REON' AND TABLE_NAME = 'SALE_ORER' AND OWNER='KOPRD' ORDER BY COLUMN_ID;

0、查看数据库字符集

select * from nls_database_parameters

1、查看表空间的名称及大小

set linesize 140;
set pages 200;
column tablespace_name format a30;

select tablespace_name,min_extents,max_extents,pct_increase,status from dba_tablespaces;

select tablespace_name,initial_extent,next_extent,contents,logging,extent_management,allocation_type
from dba_tablespaces
order by tablespace_name;

2、查看表空间物理文件的名称及大小

column db_block_size new_value blksz noprint
select value db_block_size from v$parameter where name='db_block_size';
column tablespace_name format a16;
column file_name format a60;
set linesize 160;
select file_name,round(bytes/(1024*1024),0) total_space,autoextensible,increment_by*&blksz/(1024*1024) as incement,maxbytes/(1024*1024) as maxsize from dba_data_files order by tablespace_name;

3、查看回滚段名称及大小

COLUMN roll_name   FORMAT a13          HEADING 'Rollback Name'
COLUMN tablespace  FORMAT a11          HEADING 'Tablspace'
COLUMN in_extents  FORMAT a20          HEADING 'Init/Next Extents'
COLUMN m_extents   FORMAT a10          HEADING 'Min/Max Extents'
COLUMN status      FORMAT a8           HEADING 'Status'
COLUMN wraps       FORMAT 999          HEADING 'Wraps'
COLUMN shrinks     FORMAT 999          HEADING 'Shrinks'
COLUMN opt         FORMAT 999,999,999  HEADING 'Opt. Size'
COLUMN bytes       FORMAT 999,999,999  HEADING 'Bytes'
COLUMN extents     FORMAT 999          HEADING 'Extents'

SELECT
    a.owner || '.' || a.segment_name          roll_name
  , a.tablespace_name                         tablespace
  , TO_CHAR(a.initial_extent) || ' / ' ||
    TO_CHAR(a.next_extent)                    in_extents
  , TO_CHAR(a.min_extents)    || ' / ' ||
    TO_CHAR(a.max_extents)                    m_extents
  , a.status                                  status
  , b.bytes                                   bytes
  , b.extents                                 extents
  , d.shrinks                                 shrinks
  , d.wraps                                   wraps
  , d.optsize                                 opt
FROM
    dba_rollback_segs a
  , dba_segments b
  , v$rollname c
  , v$rollstat d
WHERE
       a.segment_name = b.segment_name
  AND  a.segment_name = c.name (+)
  AND  c.usn          = d.usn (+)
ORDER BY a.segment_name;

4、查看控制文件

    select name from v$controlfile;

5、查看日志文件

    select member from v$logfile;

6、查看表空间的使用情况

select * from( select sum(bytes)/(1024*1024)  as "free_space(M)",tablespace_name
from dba_free_space
group by tablespace_name) order by "free_space(M)";

7、查看数据库库对象

    select owner, object_type, status, count(*) count# from all_objects
    group by owner,object_type,status;

8、查看数据库的版本　

    select * from v$version;

9、查看数据库的创建日期和归档方式

    select created,log_mode,log_mode from v$database;

10、查看临时数据库文件

    select STATUS, ENABLED, NAME from v$tempfile;

11. 查看oracle所有进程

select addr, pid, program, background from v$process;

12. You can check v$sess_io to find the SID with the maximum block_changes and browse through their open cursors for more details:

select opcur.sid, opcur.sql_text, opcur.user_name, sess.schemaname
from v$open_cursor opcur, v$session sess
where opcur.saddr = sess.saddr
and opcur.sid = sess.sid
and opcur.SID = <SID>
/

-- Check any locked objects in DB --

 SELECT A.sid,
       b.serial#,
       Decode(A.TYPE, 'MR', 'Media Recovery',
                      'RT', 'Redo Thread',
                      'UN', 'User Name',
                      'TX', 'Transaction',
                      'TM', 'DML',
                      'UL', 'PL/SQL User Lock',
                      'DX', 'Distributed Xaction',
                      'CF', 'Control File',
                      'IS', 'Instance State',
                      'FS', 'File Set',
                      'IR', 'Instance Recovery',
                      'ST', 'Disk Space Transaction',
                      'TS', 'Temp Segment',
                      'IV', 'Library Cache Invalida-tion',
                      'LS', 'Log Start or Switch',
                      'RW', 'Row Wait',
                      'SQ', 'Sequence Number',
                      'TE', 'Extend Table',
                      'TT', 'Temp Table',
                      'Unknown')  LockType,
       c.object_name,
       b.username,
       b.osuser,
       Decode(a.lmode, 0, 'None',
                       1, 'Null',
                       2, 'Row-S',
                       3, 'Row-X',
                       4, 'Share',
                       5, 'S/Row-X',
                       6, 'Exclusive',
                       'Unknown') LockMode,
       B.machine,
       D.spid
FROM   v$lock a,
       v$session b,
       all_objects c,
       v$process D
WHERE  a.sid = b.sid
       AND a.TYPE IN ( 'TM', 'TX' )
       AND c.object_id = a.id1
       AND B.paddr = D.addr  ;

//用于解锁
alter system kill session'SID,SERIAL#'

http://www.ixora.com.au/scripts/sql/suspicious_sql.sql

-------------------------------------------------------------------------------
--
-- Script:	suspicious_sql.sql
-- Purpose:	to find suspicious sql that may need tuning
-- For:		8.1.6 and above
--
-- Copyright:	(c) Ixora Pty Ltd
-- Author:	Steve Adams
--
-------------------------------------------------------------------------------
@save_sqlplus_settings

column load format a6 justify right
column executes format 9999999
break on load on executes

select
  substr(to_char(s.pct, '99.00'), 2) || '%'  load,
  s.executions  executes,
  p.sql_text
from
  ( 
    select
      address,
      buffer_gets,
      executions,
      pct,
      rank() over (order by buffer_gets desc)  ranking
    from
      ( 
	select
	  address,
	  buffer_gets,
	  executions,
	  100 * ratio_to_report(buffer_gets) over ()  pct
	from
	  sys.v_$sql
	where
	  command_type != 47
      )
    where
      buffer_gets > 50 * executions
  )  s,
  sys.v_$sqltext  p
where
  s.ranking <= 5 and
  p.address = s.address
order by
  1, s.address, p.piece
/

undefine Percent
@restore_sqlplus_settings