大慧博客

大慧的技术专栏

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, ROUND(SUM(bytes) / (1024 * 1024), 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

select owner_name,job_name ,state from dba_datapump_jobs;

8、查看单张表的大小

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

9、归档删除

list archivelog all;

RMAN> crosscheck archivelog all;

delete archivelog until time 'sysdate-3' ;

delete archivelog until time 'sysdate' ;##全部干掉!

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

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

select last_load_time,sql_text FROM 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;

12、查看ASM磁盘使用

select group_number,name,total_mb,free_mb from v$asm_diskgroup;

13、停止RAC

cd $GRID_HOME/bin
./crsctl stop cluster -all

./crsctl start cluster -all

阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/nirvana52/article/details/52873488
个人分类: oracle
上一篇weblogic 启动缓慢问题的解决
下一篇oracle 升级10.2.0.1到10.2.0.5
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭