oracle 的常用查询

查询堵塞会话

select b.sid,
       b.serial#,
       b.username,
       a.event,
       c.sql_text,
            c.sql_id,
       b.machine,
       b.program
  from v$session_wait a, v$session b, v$sqlarea c
 where a.sid = b.sid
   and b.sql_id = c.sql_id
   and a.wait_class <> 'Idle';

查看数据库各个时间负载

    SELECT *
  FROM ( SELECT A.INSTANCE_NUMBER,
               A.SNAP_ID,
               B.BEGIN_INTERVAL_TIME + 0 BEGIN_TIME,
               B.END_INTERVAL_TIME + 0 END_TIME,
               ROUND(VALUE - LAG( VALUE, 1 , '0')
                     OVER(ORDER BY A.INSTANCE_NUMBER, A.SNAP_ID)) "DB TIME"
          FROM (SELECT B.SNAP_ID,
                       INSTANCE_NUMBER,
                       SUM(VALUE ) / 1000000 / 60 VALUE
                  FROM DBA_HIST_SYS_TIME_MODEL B
                 WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
                   AND UPPER (B.STAT_NAME) IN UPPER(('DB TIME' ))
                 GROUP BY B.SNAP_ID, INSTANCE_NUMBER) A,
               DBA_HIST_SNAPSHOT B
         WHERE A.SNAP_ID = B.SNAP_ID
           AND B.DBID = (SELECT DBID FROM V$DATABASE)
           AND B.INSTANCE_NUMBER = A.INSTANCE_NUMBER)
 WHERE TO_CHAR(BEGIN_TIME, 'YYYY-MM-DD') = TO_CHAR(SYSDATE , 'YYYY-MM-DD')
 ORDER BY BEGIN_TIME;

查看表各空间大小和占用率

select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",
round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
    where     a.tablespace_name=b.tablespace_name
      order by ((a.bytes-b.bytes)/a.bytes) desc;

查询表空间及大小

select file_name,tablespace_name,bytes/1024/1024 from dba_data_files where tablespace_name LIKE ('USERS');

查看表的所属表空间

select tablespace_name,table_name,owner from dba_tables where table_name='表名'; #如果tablespace_name为空说明该表为默认表空间

select username,default_tablespace from dba_users where username='用户名';

查看主键

select a.constraint_name, a.column_name from dba_cons_columns a,dba_constraints b where a.constraint_name=b.constraint_name and b.constraint_type='P' and a.table_name='大写表名';

查看字段注释

SELECT 'comment on column '||a.owner||'.'||a.table_name||'.'||a.column_name||' is '||''''||a.comments||''''||' ;' FROM Dba_Col_Comments a
WHERE a.owner IN ('用户名') and a.table_name='表名' and a.COMMENTS is not null;

查看指定的表空间是否为自动扩展

SQL>   select file_name,autoextensible,increment_by from dba_data_files where tablespace_name = '表空间名'; 

数据文件改为自动扩展

SQL> alter database datafile '/u01/app/oracle/oradata/XXX/XXXX01.dbf' autoextend on;
或者
SQL>  alter database datafile '/u01/app/oracle/oradata/XXX/XXXX01.dbf' autoextend on next xxx maxsize xxxx;

关闭自动扩展

alter database datafile '/u01/app/oracle/oradata/XXX/XXXX01.dbf' autoextend off;

查看表的大小

select sum(bytes)/1024/1024/1024 G from dba_segments where owner='NJYB' and segment_name in ('表名');