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/start cluster -all

./crsctl start cluster -all

./crsctl stat res -t

14.权限相关

-查询用户拥有的系统权限

SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE ='SCOTT'；

-查询用户拥有的对象权限

SELECT GRANTEE,TABLE_NAME,PRIVILEGE FROM DBA_TAB_PRIVS WHERE GRANTEE ='SCOTT'

-查询用户拥有的角色

SELECT GRANTEE,GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE ='SCOTT'

-合并查询用户所拥有的权限

SELECT GRANTEE,PRIVILEGE,'system privileges' TYPE FROM DBA_SYS_PRIVS WHERE GRANTEE ='SCOTT'UNION ALLSELECT GRANTEE,PRIVILEGE || ' ON '||TABLE_NAME,'object privileges' TYPE FROM DBA_TAB_PRIVS WHERE GRANTEE ='SCOTT'UNION ALLSELECT GRANTEE,GRANTED_ROLE,'user role' TYPE FROM DBA_ROLE_PRIVS WHERE GRANTEE ='SCOTT'

15.编译无效对象

@$ORACLE_HOME/rdbms/admin/utlrp.sql