常用数据库状态查询语句

查看表空间使用率

SELECT d.tablespace_name,

        round((1 - nvl(free_space, 0) / space) * 100, 0) "used_rate(%)"

 FROM (SELECT tablespace_name,

              round(SUM(bytes) / (1024 * 1024), 0) space,

              SUM(blocks) blocks

       FROM dba_data_files

       GROUP BY tablespace_name) d,

      (SELECT tablespace_name,

              round(SUM(bytes) / (1024 * 1024), 0) free_space

       FROM dba_free_space

       GROUP BY tablespace_name) f

  WHERE d.tablespace_name = f.tablespace_name(+)

  UNION ALL --if have tempfile

  SELECT D.TABLESPACE_NAME,

         ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)"

  FROM (SELECT TABLESPACE_NAME,

               ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,

               SUM(BLOCKS) BLOCKS

        FROM DBA_TEMP_FILES

        GROUP BY TABLESPACE_NAME) D,

       (SELECT TABLESPACE_NAME,

               ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,

               ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE

        FROM V$TEMP_SPACE_HEADER

        GROUP BY TABLESPACE_NAME) F

  WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

  ORDER BY "used_rate(%)" DESC;

更牛逼的表空间使用率查看

col TABLESPACE_NAME for a20

col SUM_SPACE(M) for a13

col USED_SPACE(M) for a13

col FREE_SPACE(M) for a13

set linesize 120

SELECT D.TABLESPACE_NAME,

       SPACE || 'M' "SUM_SPACE(M)",

       BLOCKS "SUM_BLOCKS",

       SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",

       ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2)

          "USED_RATE(%)",

       FREE_SPACE || 'M' "FREE_SPACE(M)"

  FROM (  SELECT TABLESPACE_NAME,

                 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,

                 SUM (BLOCKS) BLOCKS

            FROM DBA_DATA_FILES

        GROUP BY TABLESPACE_NAME) D,

       (  SELECT TABLESPACE_NAME,

                 ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE

            FROM DBA_FREE_SPACE

        GROUP BY TABLESPACE_NAME) F

 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

UNION ALL

SELECT D.TABLESPACE_NAME,

       SPACE || 'M' "SUM_SPACE(M)",

       BLOCKS SUM_BLOCKS,

       USED_SPACE || 'M' "USED_SPACE(M)",

       ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",

       NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"

  FROM (  SELECT TABLESPACE_NAME,

                 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,

                 SUM (BLOCKS) BLOCKS

            FROM DBA_TEMP_FILES

        GROUP BY TABLESPACE_NAME) D,

       (  SELECT TABLESPACE_NAME,

                 ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,

                 ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE

            FROM V$TEMP_SPACE_HEADER

        GROUP BY TABLESPACE_NAME) F

 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

ORDER BY 5 desc;

查看单个数据文件使用率

set line 132
set wrap off
col file_name for a60
select a.file_name,a.bytes/1024/1024 TOTAL,b.sb/1024/1024 FREE,100*b.sb/a.bytes "FREE%" 
from dba_data_files a,(select file_id,sum(BYTES) sb from dba_free_space group by file_id) b
where a.file_id=b.file_id order by a.file_name;

SGA内存

Show parameter sga

PGA内存

Show parameter pga

会话数

SQL> select count(*) from v$session;

进程数

SQL> select count(*) from v$process;

查看数据库工作状态

select status,instance_role,INSTANCE_NAME from v$instance;

select open_mode from v$database;

查看dg是否工作正常

查询主库最大归档序号和备库最大归档序号

 select max(sequence#) from v$archived_log;

 然后在主库切换日志：alter system switch logfile;

 再次查询备库最大归档序号，一致即归档同步成功。

当发现不一致时，可以 select sequence# from v$archived_log;有时归档序号重置，只要看到最新的一致即可。

看服务名

show parameter servic

看db_name

show parameter db_name

日志文件

select * from v$logfile;

看实例名

show parameter instance

查看数据文件大小

select tablespace_name, file_id,file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;

select * from dba_data_files;可以发现这里查看没有temp表空间

select * from dba_tablespaces;这里却可以发现temp表空间
select * from dba_temp_files;其实这个语句才可以看到temp表空间
  
  

   
   

  
  
  
  

   
   如何看是否自增长：
  
  
  
  

   
   SELECT FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE FROM DBA_DATA_FILES
  
  
  
  

   
   

  
  
  
  

   
   查看所有表
  
  
  
  

   
   
select table_name from dba_all_tables;
   
   

   
   
巡检log文件是否有错
   
   
find /usr1/backup/log/bkarch_20130827* | xargs cat|grep ORA
   
   

   
   


   
   

   
   
查看一个索引是不是文本索引
   
   
 select  ITYP_NAME from dba_indexes where index_name ='STA_BIZ_FEE_CITY0_ADDRX';
   
   
Oracle 文本索引有四种类型：CONTEXT、CTXCAT、CTXRULE 和 CTXXPATH。
   
   

   
   

   
   

   
   

   
   
查看用户对应的默认表空间
   
   
select   username,   DEFAULT_TABLESPACE     from   dba_users;
   
   

   
   
看心跳线日志
   
   
/oracle/app/crs/log/ha-db1/crsd
   
   

   
   
看监听日志
/oracle/app/db/network/log
11g：$ORACLE_BASE/diag/tnslsnr/<instance_name>/listener/trace目录下
   
   

   
   

   
   

   
   

   
   
查看归档空间使用率
   
   
select * from v$flash_recovery_area_usage;
   
   
查看表空间上有什么对象
   
   
col OWNER for a10
col SEGMENT_NAME for a20
col SEGMENT_TYPE for a10
col PARTITION_NAME for a10
col TABLESPACE_NAME for a15

select t.owner,
       t.segment_name,
       t.segment_type,
       partition_name,
       t.tablespace_name,
       t.blocks * 8 / 1024 sizem
  from dba_segments t
  where tablespace_name='XXXXXXXXXXXX'
order by t.blocks desc;
   
   

   
   

   
   

   
   
查看用户数据对象体积
select OWNER,sum(BYTES)/1024/1024 from  dba_segments where owner='DLUSER' group by owner;
   
   
看数据库什么时候启动的
   
   
select STARTUP_TIME from v$instance;
   
   
详细历史信息只能在alert日志找咯
   
   

   
   
怎么看存储过程是不是合法？
   
   
 select OBJECT_NAME,STATUS from dba_objects where  object_type='PROCEDURE' and owner='QHBOSS';