管理Oracle时常用的数据字典和动态性能视图
管理Oracle时常用的数据字典和动态性能视图
1.v$version
查询数据库版本信息
[sql] select * from v$version; [html] BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Solaris: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production
2.v$sgainfo
查询sga中的相关信息
[sql] select name, bytes/1024/1024"Size(M)"from v$sgainfo; [html] NAME Size(M) -------------------------------- ---------- Fixed SGA Size 2.12744903 Redo Buffers 2.19921875 Buffer Cache Size 300 Shared Pool Size 216 Large Pool Size 4 Java Pool Size 4 Streams Pool Size 4 Shared IO Pool Size 0 Granule Size 4 Maximum SGA Size 816.328125 Startup overhead in Shared Pool 68.5404663 Free SGA Memory Available 284
3.v$parameter 查询初始化参数相关信息
[sql] select name,valuefrom v$parameterwhere name like'%pool%'; [html] NAME VALUE -------------------------------------------------------------------------------- ---------------------------------------------------- shared_pool_size 0 large_pool_size 0 java_pool_size 0 streams_pool_size 0 shared_pool_reserved_size 10905190 buffer_pool_keep buffer_pool_recycle global_context_pool_size olap_page_pool_size 0 9 rows selected 4.v$sysstat和v$statname v$sysstat是统计信息,v$statname用于显示从v$sesstat和v$sysstat解析过的统计名。比如,确定系统总的事务量,可以: [sql] select a.value as "TransactionCount" from v$sysstat a, v$statname b where a.statistic# = b.statistic# and b.name = 'user commits'; [html] TransactionCount ---------------- 6954 5.v$instance 这个v$视图显示当前实例的状态。比如,要确定系统总的运行时间: [sql] select (sysdate - startup_time)*24*60*60 as seconds from v$instance; [html] SECONDS ------- 34183 6.v$session 查询会话信息。比如: [sql] select username, sid, serial# from v$session where username is not null; [html] USERNAME SID SERIAL# ------------------------------ ---------- ---------- SYS 1 7 SYSMAN 32 165 SYSMAN 33 25 SYSMAN 35 55 SYS 37 1065 SYS 38 3205 SYSMAN 39 3 SYS 42 5039 SYSMAN 43 49 SYSMAN 44 97 SYSMAN 45 29 DBSNMP 46 95 SYSMAN 47 359 DBSNMP 48 893 SYSMAN 51 13 SYS 57 2345 7.v$sql 查询sql相关信息。如下: [sql] select sql_text, parse_calls, loads, executions from v$sql where upper(sql_text)like'。。。'; 8.v$process和v$bgprocess 进程和后台进程相关信息,比如: [sql] select p.pid, p.spid, b.name, b.description from v$process p, v$bgprocess b where p.addr = b.paddr and b.paddr <>'00'; [html] PID SPID NAME DESCRIPTION ---------- ------------------------ ----- ---------------------------------------------------------------- 2 808 PMON process cleanup 3 810 PSP0 process spawner 0 4 812 VKTM Virtual Keeper of TiMe process 5 816 GEN0 generic0 6 818 DIAG diagnosibility process 7 820 DBRM DataBase Resource Manager 8 822 DIA0 diagnosibility process 0 9 824 MMAN Memory Manager 10 826 DBW0 db writer process 0 11 828 LGWR Redo etc. 12 830 CKPT checkpoint 13 832 SMON System Monitor Process 14 834 RECO distributed recovery 15 836 MMON Manageability Monitor Process 16 838 MMNL Manageability Monitor Process 2 20 852 ARC0 Archival Process 0 21 854 ARC1 Archival Process 1 22 856 ARC2 Archival Process 2 23 858 ARC3 Archival Process 3 24 860 QMNC AQ Coordinator PID SPID NAME DESCRIPTION ---------- ------------------------ ----- ---------------------------------------------------------------- 25 874 CJQ0 Job Queue Coordinator 28 908 SMCO Space Manager Process 22 rows selected 9.dba_tablespaces [sql] select tablespace_name, status, contents from dba_tablespaces; [html] TABLESPACE_NAME STATUS CONTENTS ------------------------------ --------- --------- SYSTEM ONLINE PERMANENT SYSAUX ONLINE PERMANENT UNDOTBS1 ONLINE UNDO TEMP ONLINE TEMPORARY USERS ONLINE PERMANENT EXAMPLE ONLINE PERMANENT STU ONLINE PERMANENT 7 rows selected 10.user_segments 查看段信息。比如: [sql] select segment_name, segment_type, tablespace_name, bytes/1024"Size(KB)", extents, blocks from user_segments where segment_name ='DEPT' [html] SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME Size(KB) EXTENTS BLOCKS ------------------------------ ------------------ ------------------------------ ---------- ---------- ---------- DEPT TABLE USERS 64 1 8 11.user_extents 查看区段信息。比如: [sql] select segment_name, tablespace_name, extent_id, bytes/1024"Size(KB)", blocks from user_extents where segment_name ='DEPT'; [html] SEGMENT_NAME TABLESPACE_NAME EXTENT_ID Size(KB) BLOCKS ------------------------------ ------------------------------ ---------- ---------- ---------- DEPT USERS 0 64 8 12.dba_dat
- 更多Oracle疑问解答:
- 运行exp备份oracle数据库提示oracle-12154错误
- 有没有,生产Oracle Rman 备份脚本的工具啊!
- 初学orcle,希望有大大帮忙解说一下详细步骤,从登录oracle到创建表的过程
- oracle语句问题:一张user表,三个字段,id,name,time,插入记录比如:张三2007,李四2008,张三2011
- 如何写一个ORACLE触发器同步两个表中的数据?
- oracle 如何查看一个服务器上有多少个数据库.
- oracle 创建包的时候错误 求解
- oracle 重复列的问题
- oracle 中如何查处2星期前的数据
- 请教oracle数据库安装中的问题
- 请问谁能提供给我标准的oracle ERP的数据库表结构并详细说明各表主要的作用?
- 安装oracle遇到的问题 invalid entry CRC (expected 0x3e12e795 but got 0x9db0e9fd)
- 我的是ORACLE 10G,在RMAN中如何按指定的时间恢复数据文件啊?
- oracle为什么没有自动增长列
- oracle快捷键都有哪些啊?