Oracle11g 体系结构

一：Oracle11g 体系结构

二：逻辑储存结构

二、1：数据块（data blocks）

  1 ----通过 v$parameter数据字典来查询oracle标准数据块的大小。
  2 SYS@orcl> startup
  3 ORACLE instance started.
  4 
  5 Total System Global Area 1221992448 bytes
  6 Fixed Size                  1344596 bytes
  7 Variable Size             771754924 bytes
  8 Database Buffers          436207616 bytes
  9 Redo Buffers               12685312 bytes
 10 Database mounted.
 11 Database opened.
 12 SYS@orcl> col name format a30;
 13 SYS@orcl> col value format a20;
 14 SYS@orcl> select name,value from v$parameter where name='db_block_size';
 15 
 16 NAME                           VALUE
 17 ------------------------------ --------------------
 18 db_block_size                  8192
 19 
 20 SYS@orcl> show parameter db_block
 21 
 22 NAME                                 TYPE        VALUE
 23 ------------------------------------ ----------- ------------------------------
 24 db_block_buffers                     integer     0
 25 db_block_checking                    string      FALSE
 26 db_block_checksum                    string      TYPICAL
 27 db_block_size                        integer     8192

二、2：数据区 extent

二、3：段 segment

二、4：表空间 tablespace

二、5： SYSTEM 表空间

  1 SYS@orcl> col table_name for a30;
  2 SYS@orcl> col comments for a30;
  3 SYS@orcl> select * from dict;
  4 
  5 TABLE_NAME                     COMMENTS
  6 ------------------------------ ------------------------------
  7 DBA_CONS_COLUMNS               Information about accessible c
  8                                olumns in constraint definitio
  9                                ns
 10 
 11 DBA_LOG_GROUP_COLUMNS          Information about columns in l
 12                                og group definitions
 13 
 14 DBA_LOBS                       Description of LOBs contained
 15                                in all tables
 16 
 17 DBA_CATALOG                    All database Tables, Views, Sy

二、6：SYSAUX 表空间

二、7：UODO 表空间

二、8：USERS 表空间

三：物理存储结构

三、1：数据文件

  1 [oracle@localhost ~]$ sqlplus / as sysdba;
  2 
  3 SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 8 23:27:12 2016
  4 
  5 Copyright (c) 1982, 2011, Oracle.  All rights reserved.
  6 
  7 
  8 Connected to:
  9 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
 10 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 11 
 12 SYS@orcl> col file_name format a50;
 13 SYS@orcl> set linesize3000;
 14 SYS@orcl> select file_name,tablespace_name from dba_data_files where rownum<=10;
 15 
 16 FILE_NAME                                          TABLESPACE_NAME
 17 -------------------------------------------------- ------------------------------
 18 /u01/app/oracle/oradata/orcl/users01.dbf           USERS
 19 /u01/app/oracle/oradata/orcl/undotbs01.dbf         UNDOTBS1
 20 /u01/app/oracle/oradata/orcl/sysaux01.dbf          SYSAUX
 21 /u01/app/oracle/oradata/orcl/system01.dbf          SYSTEM
 22 /u01/app/oracle/oradata/orcl/example01.dbf         EXAMPLE
 23 
 24 SYS@orcl>

View Code

  1 SYS@orcl> col file_name format a50;
  2 SYS@orcl> col tablespace_name format a20;
  3 SYS@orcl> select file_name,tablespace_name from dba_temp_files;
  4 
  5 FILE_NAME                                          TABLESPACE_NAME
  6 -------------------------------------------------- --------------------
  7 /u01/app/oracle/oradata/orcl/temp01.dbf            TEMP
  8 
  9 SYS@orcl>

View Code

三、2：控制文件

  1 SYS@orcl> col name fromat a60;
  2 SYS@orcl> col name format a60;
  3 SYS@orcl> col status format a20;
  4 SYS@orcl> col block_size format a60;
  5 SYS@orcl> col is_recoveris_recover format a60 ;
  6 SYS@orcl> col block_size format a150 ;
  7 SYS@orcl> select * from v$controlfile;
  8 
  9 STATUS               NAME                                                         IS_ BLOCK_SIZE FILE_SIZE_BLKS
 10 -------------------- ------------------------------------------------------------ --- ---------- --------------
 11                      /u01/app/oracle/oradata/orcl/control01.ctl                   NO  ##########            594
 12                      /u01/app/oracle/fast_recovery_area/orcl/control02.ctl        NO  ##########            594

View Code

三、3：日志文件

  1 SYS@orcl> col member for a50;
  2 SYS@orcl> select * from v$logfile;
  3 
  4     GROUP# STATUS               TYPE    MEMBER                                             IS_
  5 ---------- -------------------- ------- -------------------------------------------------- ---
  6          3                      ONLINE  /u01/app/oracle/oradata/orcl/redo03.log            NO
  7          2                      ONLINE  /u01/app/oracle/oradata/orcl/redo02.log            NO
  8          1                      ONLINE  /u01/app/oracle/oradata/orcl/redo01.log            NO
  9 
 10 SYS@orcl>
 11

View Code

  1 
  2 SYS@orcl> col name format a30;
  3 SYS@orcl> select dbid,name,log_mode from v$database;
  4 
  5       DBID NAME                           LOG_MODE
  6 ---------- ------------------------------ ------------
  7 1457025880 ORCL                           ARCHIVELOG

View Code

  1 
  2 SYS@orcl> set pagesize 30;
  3 SYS@orcl> show parameter log_archive_dest;
  4 
  5 NAME                                 TYPE        VALUE
  6 ------------------------------------ ----------- ------------------------------
  7 log_archive_dest                     string
  8 log_archive_dest_1                   string
  9 log_archive_dest_10                  string
 10 log_archive_dest_11                  string
 11 log_archive_dest_12                  string
 12 log_archive_dest_13                  string
 13 log_archive_dest_14                  string
 14 log_archive_dest_15                  string
 15 log_archive_dest_16                  string
 16 log_archive_dest_17                  string
 17 log_archive_dest_18                  string
 18 log_archive_dest_19                  string
 19 log_archive_dest_2                   string
 20 log_archive_dest_20                  string
 21 log_archive_dest_21                  string
 22 log_archive_dest_22                  string
 23 log_archive_dest_23                  string
 24 log_archive_dest_24                  string
 25 log_archive_dest_25                  string
 26 log_archive_dest_26                  string
 27 log_archive_dest_27                  string
 28 log_archive_dest_28                  string
 29 log_archive_dest_29                  string
 30 log_archive_dest_3                   string
 31 log_archive_dest_30                  string
 32 log_archive_dest_31                  string
 33 log_archive_dest_4                   string
 34 
 35 NAME                                 TYPE        VALUE
 36 ------------------------------------ ----------- ------------------------------
 37 log_archive_dest_5                   string
 38 log_archive_dest_6                   string
 39 log_archive_dest_7                   string
 40 log_archive_dest_8                   string
 41 log_archive_dest_9                   string
 42 log_archive_dest_state_1             string      enable
 43 log_archive_dest_state_10            string      enable
 44 log_archive_dest_state_11            string      enable
 45 log_archive_dest_state_12            string      enable
 46 log_archive_dest_state_13            string      enable
 47 log_archive_dest_state_14            string      enable
 48 log_archive_dest_state_15            string      enable
 49 log_archive_dest_state_16            string      enable
 50 log_archive_dest_state_17            string      enable
 51 log_archive_dest_state_18            string      enable
 52 log_archive_dest_state_19            string      enable
 53 log_archive_dest_state_2             string      enable
 54 log_archive_dest_state_20            string      enable
 55 log_archive_dest_state_21            string      enable
 56 log_archive_dest_state_22            string      enable
 57 log_archive_dest_state_23            string      enable
 58 log_archive_dest_state_24            string      enable
 59 log_archive_dest_state_25            string      enable
 60 log_archive_dest_state_26            string      enable
 61 log_archive_dest_state_27            string      enable
 62 log_archive_dest_state_28            string      enable
 63 log_archive_dest_state_29            string      enable
 64 
 65 NAME                                 TYPE        VALUE
 66 ------------------------------------ ----------- ------------------------------
 67 log_archive_dest_state_3             string      enable
 68 log_archive_dest_state_30            string      enable
 69 log_archive_dest_state_31            string      enable
 70 log_archive_dest_state_4             string      enable
 71 log_archive_dest_state_5             string      enable
 72 log_archive_dest_state_6             string      enable
 73 log_archive_dest_state_7             string      enable
 74 log_archive_dest_state_8             string      enable
 75 log_archive_dest_state_9             string      enable
 76 SYS@orcl>

View Code

三、4：服务器参数文件

  1 SYS@orcl> col name for a30;
  2 SYS@orcl> col value for a30;
  3 SYS@orcl> select name ,value,ismodified from v$parameter where rownum <=20;
  4 
  5 NAME                           VALUE                          ISMODIFIED
  6 ------------------------------ ------------------------------ ----------
  7 lock_name_space                                               FALSE
  8 processes                      150                            FALSE
  9 sessions                       248                            FALSE
 10 timed_statistics               TRUE                           FALSE
 11 timed_os_statistics            0                              FALSE
 12 resource_limit                 FALSE                          FALSE
 13 license_max_sessions           0                              FALSE
 14 license_sessions_warning       0                              FALSE
 15 cpu_count                      4                              FALSE
 16 instance_groups                                               FALSE
 17 event                                                         FALSE
 18 sga_max_size                   1224736768                     FALSE
 19 use_large_pages                TRUE                           FALSE
 20 pre_page_sga                   FALSE                          FALSE
 21 shared_memory_address          0                              FALSE
 22 hi_shared_memory_address       0                              FALSE
 23 use_indirect_data_buffers      FALSE                          FALSE
 24 lock_sga                       FALSE                          FALSE
 25 processor_group_name                                          FALSE
 26 shared_pool_size               0                              FALSE
 27 
 28 20 rows selected.
 29 
 30 SYS@orcl>

View Code

  1 --查看Cursor相关参数
  2 SYS@orcl> show parameter cursor;
  3 
  4 NAME                                 TYPE        VALUE
  5 ------------------------------------ ----------- ------------------------------
  6 cursor_bind_capture_destination      string      memory+disk
  7 cursor_sharing                       string      EXACT
  8 cursor_space_for_time                boolean     FALSE
  9 open_cursors                         integer     300
 10 session_cached_cursors               integer     50
 11 
 12 ---修改相关参数 
 13 SYS@orcl> alter system set open_cursors=350 scope=both;
 14 
 15 System altered.
 16 
 17 SYS@orcl> alter system set session_cached_cursors=100 scope=spfile;
 18 
 19 System altered.
 20 
 21 SYS@orcl> alter system set cursor_space_for_time=true scope=spfile;
 22 
 23 System altered.
 24 /*
 25 scope 参数值有三个选项。
 26 scope=memory: 只改变当前实例运行，亦即初始化参数改变了只对当前实例有效，当实例重启之后，初始化参数值还原。
 27 scope=spfile：只改变spfile的设置。亦即改变初始化参数文件内容。实例重启后参数生效。
 28 scope=both：既改变实例也改变spfile。需要注意的是，如果修改静态参数，必须指定SPFILE参数（scope=spfile），否则会报错。
 29 */

View Code

三、5：密码文件

  1 [oracle@localhost dbs]$ ls
  2 hc_orcl.dat  init.ora  initPROD1.ora  lkORCL  orapworcl  spfileorcl.ora
  3 [oracle@localhost dbs]$ pwd
  4 /u01/app/oracle/product/11.2.0/db_1/dbs
  5 [oracle@localhost dbs]$ orapwd file=dbsorapwPROD1 entries=30
  6 
  7 Enter password for SYS:  ---oracle
  8 [oracle@localhost dbs]$
  9

View Code

三、6：警告文件

三、7：跟踪文件

  1 SYS@orcl> show parameter user_dump
  2 
  3 NAME                                 TYPE        VALUE
  4 ------------------------------------ ----------- ------------------------------
  5 user_dump_dest                       string      /u01/app/oracle/diag/rdbms/orc
  6                                                  l/orcl/trace
  7 SYS@orcl>

View Code

四：Oracle11g 服务器结构

四、1：系统全局区 SGA

四、 1-1：高速数据缓冲区 Database buffer cache

四、 1-2：重做日志缓冲区 Red log buffer cache

  1 SYS@orcl> show parameter log_buffer
  2 
  3 NAME                                 TYPE        VALUE
  4 ------------------------------------ ----------- ------------------------------
  5 log_buffer                           integer     12386304
  6 SYS@orcl>

View Code

四、 1-3：共享池 Shared Pool

  1 
  2 ------------------------------------ ----------- -------------

View Code

四、 1-4：大型池 Large Pool

修改 large_pool_size 值的大小：

  1 SYS@orcl> show parameter large_pool
  2 
  3 NAME                                 TYPE        VALUE
  4 ------------------------------------ ----------- ------------------------------
  5 large_pool_size                      big integer 0
  6 SYS@orcl> alter system set large_pool_size=16m;
  7 
  8 System altered.
  9 
 10 SYS@orcl> show parameter large_pool
 11 
 12 NAME                                 TYPE        VALUE
 13 ------------------------------------ ----------- ------------------------------
 14 large_pool_size                      big integer 16M
 15 SYS@orcl>

View Code

四、 1-5：Java 池

  1 SYS@orcl> show parameter java_pool
  2 
  3 NAME                                 TYPE        VALUE
  4 ------------------------------------ ----------- ------------------------------
  5 java_pool_size                       big integer 0
  6 SYS@orcl>

View Code

四、 1-6：流池

  1 SYS@orcl> show parameter streams
  2 
  3 NAME                                 TYPE        VALUE
  4 ------------------------------------ ----------- ------------------------------
  5 streams_pool_size                    big integer 0
  6 SYS@orcl>

View Code

四、2：程序全局区 PGA

四、 2-1：私有SQL区

四、 2-2：会话区

显示当前用户进程PGA 信息：

  1 SYS@orcl> show parameter pga;
  2 
  3 NAME                                 TYPE        VALUE
  4 ------------------------------------ ----------- ------------------------------
  5 pga_aggregate_target                 big integer 0
  6 SYS@orcl>

View Code

四、3：前台进程

四、 3-1：用户进程

四、 3-2：服务器进程

四、4：后台进程

四、 4-1：数据写入进程DBWR

四、 4-2：检查点进程CKPT

四、 4-3：日志写入进程 LGWR

四、 4-4：归档进程 ARCH

  1 SYS@orcl> show parameter log_archive_max_processes
  2 
  3 NAME                                 TYPE        VALUE
  4 ------------------------------------ ----------- ------------------------------
  5 log_archive_max_processes            integer     4
  6 SYS@orcl>

View Code

四、 4-5：系统金控进程 SMOM

四、 4-6：进程监控进程 PMOM

四、 4-7：锁定进程 LCKN

四、 4-8：恢复进程 RECO

四、 4-9：调度进程 DNNN

四、 4-10：快照进程 SNPN

查看当前实例进程信息

  1 SYS@orcl> set pagesize 50;
  2 SYS@orcl> select name,description from v$bgprocess;
  3 
  4 NAME  DESCRIPTION
  5 ----- ----------------------------------------------------------------
  6 PMON  process cleanup
  7 VKTM  Virtual Keeper of TiMe process
  8 GEN0  generic0
  9 DIAG  diagnosibility process
 10 DBRM  DataBase Resource Manager
 11 VKRM  Virtual sKeduler for Resource Manager
 12 RSMN  Remote Slave Monitor
 13 PING  interconnect latency measurement
 14 FMON  File Mapping Monitor Process
 15 PSP0  process spawner 0
 16 ACMS  Atomic Controlfile to Memory Server
 17 DSKM  slave DiSKMon process
 18 DIA0  diagnosibility process 0
 19 DIA1  diagnosibility process 1
 20 DIA2  diagnosibility process 2
 21 DIA3  diagnosibility process 3
 22 DIA4  diagnosibility process 4
 23 DIA5  diagnosibility process 5
 24 DIA6  diagnosibility process 6
 25 DIA7  diagnosibility process 7
 26 DIA8  diagnosibility process 8
 27 DIA9  diagnosibility process 9
 28 LMON  global enqueue service monitor
 29 LMD0  global enqueue service daemon 0
 30 LMS0  global cache service process 0
 31 LMS1  global cache service process 1
 32 LMS2  global cache service process 2
 33 LMS3  global cache service process 3
 34 LMS4  global cache service process 4
 35 LMS5  global cache service process 5
 36 LMS6  global cache service process 6
 37 LMS7  global cache service process 7
 38 LMS8  global cache service process 8
 39 LMS9  global cache service process 9
 40 LMSa  global cache service process 10
 41 LMSb  global cache service process 11
 42 LMSc  global cache service process 12
 43 LMSd  global cache service process 13
 44 LMSe  global cache service process 14
 45 LMSf  global cache service process 15
 46 LMSg  global cache service process 16
 47 LMSh  global cache service process 17
 48 LMSi  global cache service process 18
 49 LMSj  global cache service process 19
 50 LMSk  global cache service process 20
 51 LMSl  global cache service process 21
 52 LMSm  global cache service process 22
 53 
 54 NAME  DESCRIPTION
 55 ----- ----------------------------------------------------------------
 56 LMSn  global cache service process 23
 57 LMSo  global cache service process 24
 58 LMSp  global cache service process 25
 59 LMSq  global cache service process 26
 60 LMSr  global cache service process 27
 61 LMSs  global cache service process 28
 62 LMSt  global cache service process 29
 63 LMSu  global cache service process 30
 64 LMSv  global cache service process 31
 65 LMSw  global cache service process 32
 66 LMSx  global cache service process 33
 67 LMSy  global cache service process 34
 68 RMS0  rac management server
 69 LMHB  lm heartbeat monitor
 70 MMAN  Memory Manager
 71 DBW0  db writer process 0
 72 DBW1  db writer process 1
 73 DBW2  db writer process 2
 74 DBW3  db writer process 3
 75 DBW4  db writer process 4
 76 DBW5  db writer process 5
 77 DBW6  db writer process 6
 78 DBW7  db writer process 7
 79 DBW8  db writer process 8
 80 DBW9  db writer process 9
 81 DBWa  db writer process 10 (a)
 82 DBWb  db writer process 11 (b)
 83 DBWc  db writer process 12 (c)
 84 DBWd  db writer process 13 (d)
 85 DBWe  db writer process 14 (e)
 86 DBWf  db writer process 15 (f)
 87 DBWg  db writer process 16 (g)
 88 DBWh  db writer process 17 (h)
 89 DBWi  db writer process 18 (i)
 90 DBWj  db writer process 19 (j)
 91 DBWk  db writer process 20 (k)
 92 DBWl  db writer process 21 (l)
 93 DBWm  db writer process 22 (m)
 94 DBWn  db writer process 23 (n)
 95 DBWo  db writer process 24 (o)
 96 DBWp  db writer process 25 (p)
 97 DBWq  db writer process 26 (q)
 98 DBWr  db writer process 27 (r)
 99 DBWs  db writer process 28 (s)
100 DBWt  db writer process 29 (t)
101 DBWu  db writer process 30 (u)
102 DBWv  db writer process 31 (v)
103 
104 NAME  DESCRIPTION
105 ----- ----------------------------------------------------------------
106 DBWw  db writer process 32 (w)
107 DBWx  db writer process 33 (x)
108 DBWy  db writer process 34 (y)
109 DBWz  db writer process 35 (z)
110 MRP0  Managed Standby Recovery
111 ARC0  Archival Process 0
112 ARC1  Archival Process 1
113 ARC2  Archival Process 2
114 ARC3  Archival Process 3
115 ARC4  Archival Process 4
116 ARC5  Archival Process 5
117 ARC6  Archival Process 6
118 ARC7  Archival Process 7
119 ARC8  Archival Process 8
120 ARC9  Archival Process 9
121 ARCa  Archival Process 10
122 ARCb  Archival Process 11
123 ARCc  Archival Process 12
124 ARCd  Archival Process 13
125 ARCe  Archival Process 14
126 ARCf  Archival Process 15
127 ARCg  Archival Process 16
128 ARCh  Archival Process 17
129 ARCi  Archival Process 18
130 ARCj  Archival Process 19
131 ARCk  Archival Process 20
132 ARCl  Archival Process 21
133 ARCm  Archival Process 22
134 ARCn  Archival Process 23
135 ARCo  Archival Process 24
136 ARCp  Archival Process 25
137 ARCq  Archival Process 26
138 ARCr  Archival Process 27
139 ARCs  Archival Process 28
140 ARCt  Archival Process 29
141 NSA1  Redo transport NSA1
142 NSS1  Redo transport NSS1
143 NSA2  Redo transport NSA2
144 NSS2  Redo transport NSS2
145 NSA3  Redo transport NSA3
146 NSS3  Redo transport NSS3
147 NSA4  Redo transport NSA4
148 NSS4  Redo transport NSS4
149 NSA5  Redo transport NSA5
150 NSS5  Redo transport NSS5
151 NSA6  Redo transport NSA6
152 NSS6  Redo transport NSS6
153 
154 NAME  DESCRIPTION
155 ----- ----------------------------------------------------------------
156 NSA7  Redo transport NSA7
157 NSS7  Redo transport NSS7
158 NSA8  Redo transport NSA8
159 NSS8  Redo transport NSS8
160 NSA9  Redo transport NSA9
161 NSS9  Redo transport NSS9
162 NSAA  Redo transport NSAA
163 NSSA  Redo transport NSSA
164 NSAB  Redo transport NSAB
165 NSSB  Redo transport NSSB
166 NSAC  Redo transport NSAC
167 NSSC  Redo transport NSSC
168 NSAD  Redo transport NSAD
169 NSSD  Redo transport NSSD
170 NSAE  Redo transport NSAE
171 NSSE  Redo transport NSSE
172 NSAF  Redo transport NSAF
173 NSSF  Redo transport NSSF
174 NSAG  Redo transport NSAG
175 NSSG  Redo transport NSSG
176 NSAH  Redo transport NSAH
177 NSSH  Redo transport NSSH
178 NSAI  Redo transport NSAI
179 NSSI  Redo transport NSSI
180 NSAJ  Redo transport NSAJ
181 NSSJ  Redo transport NSSJ
182 NSAK  Redo transport NSAK
183 NSSK  Redo transport NSSK
184 NSAL  Redo transport NSAL
185 NSSL  Redo transport NSSL
186 NSAM  Redo transport NSAM
187 NSSM  Redo transport NSSM
188 NSAN  Redo transport NSAN
189 NSSN  Redo transport NSSN
190 NSAO  Redo transport NSAO
191 NSSO  Redo transport NSSO
192 NSAP  Redo transport NSAP
193 NSSP  Redo transport NSSP
194 NSAQ  Redo transport NSAQ
195 NSSQ  Redo transport NSSQ
196 NSAR  Redo transport NSAR
197 NSSR  Redo transport NSSR
198 NSAS  Redo transport NSAS
199 NSSS  Redo transport NSSS
200 NSAT  Redo transport NSAT
201 NSST  Redo transport NSST
202 NSAU  Redo transport NSAU
203 
204 NAME  DESCRIPTION
205 ----- ----------------------------------------------------------------
206 NSSU  Redo transport NSSU
207 NSAV  Redo transport NSAV
208 NSSV  Redo transport NSSV
209 LGWR  Redo etc.
210 LCK0  Lock Process 0
211 CKPT  checkpoint
212 ABMR  Auto BMR Background Process
213 LSP1  Dictionary build process for Logical Standby
214 LSP0  Logical Standby
215 LSP2  Set Guard Standby Information for Logical Standby
216 CTWR  Change Tracking Writer
217 RVWR  Recovery Writer
218 FBDA  Flashback Data Archiver Process
219 SMON  System Monitor Process
220 SMCO  Space Manager Process
221 RECO  distributed recovery
222 GTX0  Global Txn process 0
223 GTX1  Global Txn process 1
224 GTX2  Global Txn process 2
225 GTX3  Global Txn process 3
226 GTX4  Global Txn process 4
227 GTX5  Global Txn process 5
228 GTX6  Global Txn process 6
229 GTX7  Global Txn process 7
230 GTX8  Global Txn process 8
231 GTX9  Global Txn process 9
232 GTXa  Global Txn process 10
233 GTXb  Global Txn process 11
234 GTXc  Global Txn process 12
235 GTXd  Global Txn process 13
236 GTXe  Global Txn process 14
237 GTXf  Global Txn process 15
238 GTXg  Global Txn process 16
239 GTXh  Global Txn process 17
240 GTXi  Global Txn process 18
241 GTXj  Global Txn process 19
242 CJQ0  Job Queue Coordinator
243 RCBG  Result Cache: Background
244 EMNC  EMON Coordinator
245 QMNC  AQ Coordinator
246 DMON  DG Broker Monitor Process
247 RSM0  Data Guard Broker Resource Guard Process 0
248 NSV0  Data Guard Broker NetSlave Process 0
249 NSV1  Data Guard Broker NetSlave Process 1
250 NSV2  Data Guard Broker NetSlave Process 2
251 NSV3  Data Guard Broker NetSlave Process 3
252 NSV4  Data Guard Broker NetSlave Process 4
253 
254 NAME  DESCRIPTION
255 ----- ----------------------------------------------------------------
256 NSV5  Data Guard Broker NetSlave Process 5
257 NSV6  Data Guard Broker NetSlave Process 6
258 NSV7  Data Guard Broker NetSlave Process 7
259 NSV8  Data Guard Broker NetSlave Process 8
260 NSV9  Data Guard Broker NetSlave Process 9
261 NSVA  Data Guard Broker NetSlave Process A
262 NSVB  Data Guard Broker NetSlave Process B
263 NSVC  Data Guard Broker NetSlave Process C
264 NSVD  Data Guard Broker NetSlave Process D
265 NSVE  Data Guard Broker NetSlave Process E
266 NSVF  Data Guard Broker NetSlave Process F
267 NSVG  Data Guard Broker NetSlave Process G
268 NSVH  Data Guard Broker NetSlave Process H
269 NSVI  Data Guard Broker NetSlave Process I
270 NSVJ  Data Guard Broker NetSlave Process J
271 NSVK  Data Guard Broker NetSlave Process K
272 NSVL  Data Guard Broker NetSlave Process L
273 NSVM  Data Guard Broker NetSlave Process M
274 NSVN  Data Guard Broker NetSlave Process N
275 NSVO  Data Guard Broker NetSlave Process O
276 NSVP  Data Guard Broker NetSlave Process P
277 NSVQ  Data Guard Broker NetSlave Process Q
278 NSVR  Data Guard Broker NetSlave Process R
279 NSVS  Data Guard Broker NetSlave Process S
280 NSVT  Data Guard Broker NetSlave Process T
281 NSVU  Data Guard Broker NetSlave Process U
282 INSV  Data Guard Broker INstance SlaVe Process
283 FSFP  Data Guard Broker FSFO Pinger
284 RBAL  ASM Rebalance master
285 ARB0  ASM Rebalance 0
286 ARB1  ASM Rebalance 1
287 ARB2  ASM Rebalance 2
288 ARB3  ASM Rebalance 3
289 ARB4  ASM Rebalance 4
290 ARB5  ASM Rebalance 5
291 ARB6  ASM Rebalance 6
292 ARB7  ASM Rebalance 7
293 ARB8  ASM Rebalance 8
294 ARB9  ASM Rebalance 9
295 ARBA  ASM Rebalance 10
296 ASMB  ASM Background
297 MARK  mark AU for resync koordinator
298 GMON  diskgroup monitor
299 VBG0  Volume BG 0
300 VBG1  Volume BG 1
301 VBG2  Volume BG 2
302 VBG3  Volume BG 3
303 
304 NAME  DESCRIPTION
305 ----- ----------------------------------------------------------------
306 VBG4  Volume BG 4
307 VBG5  Volume BG 5
308 VBG6  Volume BG 6
309 VBG7  Volume BG 7
310 VBG8  Volume BG 8
311 VBG9  Volume BG 9
312 VDBG  Volume Driver BG
313 VMB0  Volume Membership 0
314 ACFS  ACFS CSS
315 MMON  Manageability Monitor Process
316 MMNL  Manageability Monitor Process 2
317 XDMG  cell automation manager
318 XDWK  cell automation worker actions
319 
320 295 rows selected.
321 
322 SYS@orcl>

View Code

四、5：数据字典

四、 5-1：Oracle数据字典概述

四、 5-2：Oracle 常用数据字典

.

四、 5-2_1：基本数据字典

  1 -----基本数据字典及其说明
  2 select  * from dba_tablespaces;---关于表空间的信息
  3 select  * from dba_ts_quotas ;---所有用户表空间的限额
  4 select  * from dba_free_space;--所有表空间中自由分区
  5 select * from  dba_segments;--描述数据库中所有段的储存空间；
  6 select * from  dba_extents;--数据库中所有分区的信息
  7 select  * from  dba_tables;---数据库中所有数据表的描述
  8 select * from  dba_tab_columns;---所有表、视图 及簇的列
  9 select * from dba_views ;--数据库中所有的视图信息；
 10 select * from synonyms;--关于同义词的信息查询；
 11 select * from dba_sequences;--所有用户序列号信息
 12 select * from dba_constraints;--所有用户表的约束信息；
 13 select *  from dba_indexes;--关于数据库索引的描述；
 14 select * from dba_ind_columns;--在所有表及簇上压缩索引的列
 15 select * from dba_triggers;--所有用户的触发器信息 
 16 select  * from dba_source;--所有用户储存过程的信息；
 17 select * from dba_data_files;--查询关于数据库文件的信息；
 18 select  * from dba_tab_privs;--查询关于对象授权的信息
 19 select  * from  dba_objects;--数据库中所有的对象；
 20 select * from  dba_users;--关于数据库中所有用户的信息；
 21

四、 5-2_2：常用动态性能视图

  1 
  2 ------------常用动态性能视图
  3 select * from v$database ;---描述关于数据库的相关信息
  4 select  * from v$datafile;--数据库使用的数据文件信息
  5 select * from v$log;--从控制文件中提取有关重做日志组的信息
  6 select * from v$logfile;--有关实例重置日志组文件名及其位置的信息
  7 select * from v$archived_log;--记录归档日志文件的基本信息
  8 select * from v$archive_dest;--记录归档日志文件的路径信息
  9 select * from v$controlfile ;--描述控制文件的相关信息
 10 select * from v$instance ; ---记录实例的基本信息
 11 select * from v$system_parameter;--显示实例当前有效的参数信息
 12 select * from v$sga;--显示实例的SGA区的大小
 13 select * from v$sgastat;--统计SGA使用情况的信息
 14 select * from v$parameter ;-- 记录初始化参数文件中所有项的值
 15 select * from v$lock ;--通过访问数据库会话，设置对象锁的所有信息
 16 select * from v$session;--有关会话的信息
 17 select * from v$sqltext;--记录SQL语句的信息
 18 select * from v$sql;--记录SQL语句的详细信息
 19 select * from  v$bgprocess;--显示后台进程信息；
 20 select * from v$process;--当前进程的信息
 21

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

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

posted @ 2017-12-10 21:16 一品堂.技术学习笔记阅读(3814) 评论(0) 编辑收藏举报

刷新页面返回顶部

✧*꧁一品堂.技术学习笔记꧂*✧.

Oracle11g 体系结构

一：Oracle11g 体系结构

二：逻辑储存结构

二、1：数据块（data blocks）

二、2：数据区 extent

二、3：段 segment

二、4：表空间 tablespace

二、5： SYSTEM 表空间

二、6：SYSAUX 表空间

二、7：UODO 表空间

二、8：USERS 表空间

三：物理存储结构

三、1： 数据文件

三、2： 控制文件

三、3：日志文件

三、4：服务器参数文件

三、5：密码文件

三、6：警告文件

三、7：跟踪文件

四：Oracle11g 服务器结构

四、1：系统全局区 SGA

四、 1-1：高速数据缓冲区 Database buffer cache

四、 1-2：重做日志缓冲区 Red log buffer cache

四、 1-3：共享池 Shared Pool

四、 1-4：大型池 Large Pool

四、 1-5：Java 池

四、 1-6： 流池

四、2：程序全局区 PGA

四、 2-1：私有SQL区

四、 2-2： 会话区

四、3：前台进程

四、 3-1： 用户进程

四、 3-2：服务器进程

四、4：后台进程

四、 4-1： 数据写入进程DBWR

四、 4-2： 检查点进程CKPT

四、 4-3：日志写入进程 LGWR

四、 4-4：归档进程 ARCH

四、 4-5：系统金控进程 SMOM

四、 4-6：进程监控进程 PMOM

四、 4-7：锁定进程 LCKN

四、 4-8：恢复进程 RECO

四、 4-9：调度进程 DNNN

四、 4-10：快照进程 SNPN

查看当前实例进程信息

四、5： 数据字典

四、 5-1：Oracle数据字典概述

四、 5-2：Oracle 常用数据字典

四、 5-2_1：基本数据字典

四、 5-2_2：常用动态性能视图

公告

✧꧁一品堂.技术学习笔记꧂✧.

三、1：数据文件

三、2：控制文件

四、 1-6：流池

四、 2-2：会话区

四、 3-1：用户进程

四、 4-1：数据写入进程DBWR

四、 4-2：检查点进程CKPT

四、5：数据字典