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 ('表名');
哪有什么胜利可言,坚持意味着一切。如想使用请备注转载链接~