oracle日常管理常用查询整理

1、用户信息：

select username,account_status,default_tablespace from dba_users order by 2,1;

2 、版本信息：

select * from v$version@bm11g;

3、数据库状态信息

select status form gv$database;

4、查看所有的表空间使用

SELECT a.tablespace_name,
total,
free,
(total - free) used,
ROUND((total - free) / total,4) * 100 rat
FROM (SELECT tablespace_name,ROUND(SUM(bytes) / (1024 * 1024),4) free
FROM DBA_FREE_SPACE
GROUP BY tablespace_name) a,
(SELECT tablespace_name,4) total
FROM DBA_DATA_FILES
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;

5、查找无效索引

select index_name,index_type,status from dba_indexes where status='INVALID';

6、查找压缩表

select table_name from dba_tables where compression = 'ENABLED' and owner not in('SYSMAN');

7、查看数据泵执行的JOB，用于attach

selectowner_name,job_name,statefromdba_datapump_jobs;

8、查看单张表的大小

SELECT segment_name AS TABLENAME,BYTES FROM user_segments WHERE segment_name='表名'

9、归档删除

RMAN> crosscheck archivelog all;

delete archivelog until time 'sysdate-3' ;

10、查看最近执行的10条SQL语句

select last_load_time,sql_text from v$sql where rownum <=10 order by last_load_time desc;

selectlast_load_time,sql_textFROM v$sql WHERE last_load_time IS NOT NULL and sql_text like 'insert%' and rownum <=10 ORDER BY last_load_time DESC;

11、查看数据库snapshot记录

select snap_id,dbid,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME,SNAP_LEVEL from dba_hist_snapshot where rownum <=10 order by 3;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!