oracle DBA常用的sql语句

 

--查看是否启用MTS(如果返回是none或是shared表示启用)

SQL> select distinct server from v$session;

 

--查看表空间的名称及大小

SQL> select t.tablespace_name,round(sum(bytes/(1024*1024)),0)||'M' tx_size from dba_tablespaces t, dba_data_files d where t.tablespace_name=d.tablespace_name group by t.tablespace_name;

 

--查看表空间物理文件的名称及大小

SQL> select tablespace_name,file_id,file_name,round(bytes/(1024*1024),0)||'M' total_space from dba_data_files order by tablespace_name;

 

--检查各表空间可用空间的百分比

SQL> select f.tablespace_name,round((f.bytes/t.bytes*100),2)||'%' keyong from sys.sm$ts_free f,(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) t where t.tablespace_name=f.tablespace_name;

--查看表空间的使用情况

SQL> select tablespace_name,sum(bytes)/(1024*1024) as free_space from dba_free_space group by tablespace_name;

 

SQL> select a.tablespace_name,a.bytes total,b.bytes userd,c.bytes free,(b.bytes*100)/a.bytes||'%' userdpersent,(c.bytes*100)/a.bytes||'%' freepresent from sys.sm$ts_avail a,sys.sm$ts_used b,sys.sm$ts_free c where a.tablespace_name=b.tablespace_name and a.tablespace_name=c.tablespace_name;

 

--查看数据文件大小及头大小

SQL> select v1.file_name,v1.file_id,num1 totle_space,num3 free_space,

  2  num1-num3 used_space,nvl(num2,0) data_space,

  3  num1-num3-nvl(num2,0) file_head from

  4  (select file_name,file_id,sum(bytes) num1 from dba_data_files group by file_name,file_id) v1,(select file_id,sum(bytes) num2 from dba_extents group by file_id) v2,

  5  (select file_id,sum(bytes) num3 from dba_free_space group by file_id) v3 where v1.file_id=v2.file_id(+) and v1.file_id = v3.file_id(+);

 

--动态的查看表的索引信息

SQL> select a.index_name,a.column_name,b.status,b.index_type from user_ind_columns a,user_indexes b where a.index_name = b.index_name and a.table_name='&table_name';

Enter value for table_name: aaa

old   1: select a.index_name,a.column_name,b.status,b.index_type from user_ind_columns a,user_indexes b where a.index_name = b.index_name and a.table_name='&table_name'

new   1: select a.index_name,a.column_name,b.status,b.index_type from user_ind_columns a,user_indexes b where a.index_name = b.index_name and a.table_name='aaa'

 

 

--查看数据库对象信息

SQL> select owner,object_type,status,count(*)  total from all_objects group by owner,object_type,status;

 

--动态查看表空间表、索引的存储情况检查

SQL> select segment_name,sum(bytes),count(*) ext_quan from dba_extents where tablespace_name='&tablespace_name' and segment_type='table/index' group by tablespace_name,segment_name order by count(*) desc;

Enter value for tablespace_name: rman_ts

old   1: select segment_name,sum(bytes),count(*) ext_quan from dba_extents where tablespace_name='&tablespace_name' and segment_type='table/index' group by tablespace_name,segment_name order by count(*) desc

new   1: select segment_name,sum(bytes),count(*) ext_quan from dba_extents where tablespace_name='rman_ts' and segment_type='table/index' group by tablespace_name,segment_name order by count(*) desc

 

 

--动态查看用户表、索引的存储情况检查

SQL> select table_name from user_tables where table_name not in (select table_name from user_ind_columns);

--查看用户，角色的基本信息。

SQL> select username,account_status,default_tablespace,temporary_tablespace,created from dba_users;

 

--查找所有的用户

SQL> select * from all_users;

 

--查看当前用户下的用户和角色的基本信息

SQL> select username,account_status,default_tablespace,temporary_tablespace,created from user_users;

 

--动态查看用户的系统权限

SQL> select * from dba_sys_privs where grantee='&grantee';

Enter value for grantee: connect;

old   1: select * from dba_sys_privs where grantee='&grantee'

new   1: select * from dba_sys_privs where grantee='connect;'

 

 

SQL> select * from user_sys_privs;

 

--查看用户的角色

SQL> select * from dba_roles;

 

--查看当前连接所具有的权限

SQL> select * from session_privs;

 

--查看readdisk最高的前十条记录

SQL> select sql_text from (select * from v$sqlarea order by disk_reads) where rownum<=10;

 

--查看当前等待事件的会话

SQL> select se.sid,s.username,se.event,se.total_waits,se.time_waited,se.average_wait from v$session s,v$session_event se where se.sid=s.sid and s.status='active' and se.event not like '%sql*net%';

 

SQL> select sid,event,p1,p2,p3,wait_time,seconds_in_wait,state from v$session_wait

  2  where event not like '%message%' and event not like 'sql*net%' and event not like '%timer%' and event!='wake up time manager';

--查看数据库软件版本信息

SQL> select * from v$version;

 

BANNER                                                                          

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production          

PL/SQL Release 11.2.0.1.0 - Production                                          

CORE11.2.0.1.0Production                                                      

TNS for Linux: Version 11.2.0.1.0 - Production                                  

NLSRTL Version 11.2.0.1.0 - Production                                          

--查看实例相关信息

SQL> select instance_name,host_name,version oracle_version,startup_time from v$instance;

 

INSTANCE_NAME                                                                   

----------------                                                                

HOST_NAME                                                                       

----------------------------------------------------------------                

ORACLE_VERSION    STARTUP_TIME                                                  

----------------- -------------------                                           

orcl                                                                            

abc.com                                                                         

11.2.0.1.0        2011-01-25 16:38:15                                           

 

--查看数据库名称、归档与否，运行平台

SQL> select name,created,log_mode,platform_name,db_unique_name from v$database;

 

NAME      CREATED             LOG_MODE                                          

--------- ------------------- ------------                                      

PLATFORM_NAME                                                                   

--------------------------------------------------------------------------------

DB_UNIQUE_NAME                                                                  

------------------------------                                                  

ORCL      2011-01-10 06:04:18 ARCHIVELOG                                        

Linux IA (32-bit)                                                               

orcl                                                                            

 

--查看当前数据库的字符集

SQL> select userenv('lang') from dual;

 

USERENV('LANG')                                                                 

----------------------------------------------------                            

US                                                                              

 

SQL> select userenv('language') from dual;

 

USERENV('LANGUAGE')                                                             

----------------------------------------------------                            

AMERICAN_AMERICA.ZHS16GBK                                                       

--查看回滚段名称及大小

SQL> select segment_name,tablespace_name,r.status,(initial_extent/1024)||'K' initialextent,(next_extent/1024)||'K' nextextent,

  2  (max_extents/1024)||'K' max_extents,v.curext curextent from dba_rollback_segs r,v$rollstat v where r.segment_id=v.usn(+) order by segment_name;

 

 

--查看内存使用情况

SQL> select least(max(b.value)/(1024*1024),sum(a.bytes)/(1024*1024)) shared_pool_used,

  2  max(b.value)/(1024*1024) shared_pool_size,

  3  greatest(max(b.value)/(1024*1024),sum(a.bytes)/(1024*1024))-(sum(a.bytes)/(1024*1024)) shared_pool_avail,

  4  ((sum(a.bytes)/(1024*1024))/(max(b.value)/(1024*1024)))*100 avail_pool_pct from v$sgastat a,v$parameter b

  5  where (a.pool='shared pool' and a.name not in('free memory'))

  6  and b.name='shared_pool_size';

((sum(a.bytes)/(1024*1024))/(max(b.value)/(1024*1024)))*100 avail_pool_pct from v$sgastat a,v$parameter b

                           *

 

--查看用户内存使用情况

SQL> select username,sum(sharable_mem),sum(persistent_mem),sum(runtime_mem) from sys.v_$sqlarea a,dba_users b where a.parsing_user_id=b.user_id group by username;

 

--查看客户端登陆的ip

SQL> select username,machine,sysdate,sys_context('userenv','ip_address') clientaddress from v$session where audsid=userenv('sessionid');

 

--查看oracle服务器操作系统及相应的系统信息

SQL> select * from v$osstat;

 

SQL> select * from v$sysstat;

 

SQL> spool off