sql oracle dba,oracle DBA常用的sql语句

最新推荐文章于 2021-04-04 16:16:19 发布

灯灯藕粉

最新推荐文章于 2021-04-04 16:16:19 发布

阅读量83

点赞数

文章标签： sql oracle dba

--查看是否启用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')

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

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