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