一：Oracle11g 体系结构
二：逻辑储存结构
二、1：数据块（data blocks）
----通过 v$parameter数据字典来查询oracle标准数据块的大小。
SYS@orcl> startup
ORACLE instance started. Total System Global Area 1221992448 bytes
Fixed Size 1344596 bytes
Variable Size 771754924 bytes
Database Buffers 436207616 bytes
Redo Buffers 12685312 bytes
Database mounted.
Database opened.
SYS@orcl> col name format a30;
SYS@orcl> col value format a20;
SYS@orcl> select name,value from v$parameter where name='db_block_size'; NAME VALUE
------------------------------ --------------------
db_block_size 8192 SYS@orcl> show parameter db_block NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers integer 0
db_block_checking string FALSE
db_block_checksum string TYPICAL
db_block_size integer 8192
二、2：数据区 extent
二、3：段 segment
二、4：表空间 tablespace
二、5： SYSTEM 表空间
SYS@orcl> col table_name for a30;
SYS@orcl> col comments for a30;
SYS@orcl> select * from dict; TABLE_NAME COMMENTS
------------------------------ ------------------------------
DBA_CONS_COLUMNS Information about accessible c
olumns in constraint definitio
ns DBA_LOG_GROUP_COLUMNS Information about columns in l
og group definitions DBA_LOBS Description of LOBs contained
in all tables DBA_CATALOG All database Tables, Views, Sy
二、6：SYSAUX 表空间
二、7：UODO 表空间
二、8：USERS 表空间
三：物理存储结构
三、1： 数据文件
[oracle@localhost ~]$ sqlplus / as sysdba; SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 8 23:27:12 2016 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@orcl> col file_name format a50;
SYS@orcl> set linesize3000;
SYS@orcl> select file_name,tablespace_name from dba_data_files where rownum<=10; FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf USERS
/u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
/u01/app/oracle/oradata/orcl/example01.dbf EXAMPLE SYS@orcl>
三、2： 控制文件
SYS@orcl> col name fromat a60;
SYS@orcl> col name format a60;
SYS@orcl> col status format a20;
SYS@orcl> col block_size format a60;
SYS@orcl> col is_recoveris_recover format a60 ;
SYS@orcl> col block_size format a150 ;
SYS@orcl> select * from v$controlfile; STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
-------------------- ------------------------------------------------------------ --- ---------- --------------
/u01/app/oracle/oradata/orcl/control01.ctl NO ########## 594
/u01/app/oracle/fast_recovery_area/orcl/control02.ctl NO ########## 594
三、3：日志文件
SYS@orcl> col member for a50;
SYS@orcl> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_
---------- -------------------- ------- -------------------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO SYS@orcl>
SYS@orcl> set pagesize 30;
SYS@orcl> show parameter log_archive_dest; NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
log_archive_dest_3 string
log_archive_dest_30 string
log_archive_dest_31 string
log_archive_dest_4 string NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_11 string enable
log_archive_dest_state_12 string enable
log_archive_dest_state_13 string enable
log_archive_dest_state_14 string enable
log_archive_dest_state_15 string enable
log_archive_dest_state_16 string enable
log_archive_dest_state_17 string enable
log_archive_dest_state_18 string enable
log_archive_dest_state_19 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_20 string enable
log_archive_dest_state_21 string enable
log_archive_dest_state_22 string enable
log_archive_dest_state_23 string enable
log_archive_dest_state_24 string enable
log_archive_dest_state_25 string enable
log_archive_dest_state_26 string enable
log_archive_dest_state_27 string enable
log_archive_dest_state_28 string enable
log_archive_dest_state_29 string enable NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_3 string enable
log_archive_dest_state_30 string enable
log_archive_dest_state_31 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
SYS@orcl>
三、4：服务器参数文件
SYS@orcl> col name for a30;
SYS@orcl> col value for a30;
SYS@orcl> select name ,value,ismodified from v$parameter where rownum <=20; NAME VALUE ISMODIFIED
------------------------------ ------------------------------ ----------
lock_name_space FALSE
processes 150 FALSE
sessions 248 FALSE
timed_statistics TRUE FALSE
timed_os_statistics 0 FALSE
resource_limit FALSE FALSE
license_max_sessions 0 FALSE
license_sessions_warning 0 FALSE
cpu_count 4 FALSE
instance_groups FALSE
event FALSE
sga_max_size 1224736768 FALSE
use_large_pages TRUE FALSE
pre_page_sga FALSE FALSE
shared_memory_address 0 FALSE
hi_shared_memory_address 0 FALSE
use_indirect_data_buffers FALSE FALSE
lock_sga FALSE FALSE
processor_group_name FALSE
shared_pool_size 0 FALSE 20 rows selected. SYS@orcl>
--查看Cursor相关参数
SYS@orcl> show parameter cursor; NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_bind_capture_destination string memory+disk
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 50 ---修改相关参数
SYS@orcl> alter system set open_cursors=350 scope=both; System altered. SYS@orcl> alter system set session_cached_cursors=100 scope=spfile; System altered. SYS@orcl> alter system set cursor_space_for_time=true scope=spfile; System altered.
/*
scope 参数值有三个选项。
scope=memory: 只改变当前实例运行，亦即初始化参数改变了只对当前实例有效，当实例重启之后，初始化参数值还原。
scope=spfile：只改变spfile的设置。亦即改变初始化参数文件内容。实例重启后参数生效。
scope=both：既改变实例也改变spfile。需要注意的是，如果修改静态参数，必须指定SPFILE参数（scope=spfile），否则会报错。
*/
三、5：密码文件
三、6：警告文件
三、7：跟踪文件
SYS@orcl> show parameter user_dump NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /u01/app/oracle/diag/rdbms/orc
l/orcl/trace
SYS@orcl>
四：Oracle11g 服务器结构
四、1：系统全局区 SGA
四、 1-1：高速数据缓冲区 Database buffer cache
四、 1-2：重做日志缓冲区 Red log buffer cache
SYS@orcl> show parameter log_buffer NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_buffer integer 12386304
SYS@orcl>
四、 1-3：共享池 Shared Pool
------------------------------------ ----------- -------------
四、 1-4：大型池 Large Pool
修改 large_pool_size 值的大小：
SYS@orcl> show parameter large_pool NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
large_pool_size big integer 0
SYS@orcl> alter system set large_pool_size=16m; System altered. SYS@orcl> show parameter large_pool NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
large_pool_size big integer 16M
SYS@orcl>
四、 1-5：Java 池
SYS@orcl> show parameter java_pool NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_pool_size big integer 0
SYS@orcl>
四、 1-6： 流池
SYS@orcl> show parameter streams NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size big integer 0
SYS@orcl>
四、2：程序全局区 PGA
四、 2-1：私有SQL区
四、 2-2： 会话区
显示当前用户进程PGA 信息：
SYS@orcl> show parameter pga; NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 0
SYS@orcl>
四、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
查看当前实例进程信息
SYS@orcl> set pagesize 50;
SYS@orcl> select name,description from v$bgprocess; NAME DESCRIPTION
----- ----------------------------------------------------------------
PMON process cleanup
VKTM Virtual Keeper of TiMe process
GEN0 generic0
DIAG diagnosibility process
DBRM DataBase Resource Manager
VKRM Virtual sKeduler for Resource Manager
RSMN Remote Slave Monitor
PING interconnect latency measurement
FMON File Mapping Monitor Process
PSP0 process spawner 0
ACMS Atomic Controlfile to Memory Server
DSKM slave DiSKMon process
DIA0 diagnosibility process 0
DIA1 diagnosibility process 1
DIA2 diagnosibility process 2
DIA3 diagnosibility process 3
DIA4 diagnosibility process 4
DIA5 diagnosibility process 5
DIA6 diagnosibility process 6
DIA7 diagnosibility process 7
DIA8 diagnosibility process 8
DIA9 diagnosibility process 9
LMON global enqueue service monitor
LMD0 global enqueue service daemon 0
LMS0 global cache service process 0
LMS1 global cache service process 1
LMS2 global cache service process 2
LMS3 global cache service process 3
LMS4 global cache service process 4
LMS5 global cache service process 5
LMS6 global cache service process 6
LMS7 global cache service process 7
LMS8 global cache service process 8
LMS9 global cache service process 9
LMSa global cache service process 10
LMSb global cache service process 11
LMSc global cache service process 12
LMSd global cache service process 13
LMSe global cache service process 14
LMSf global cache service process 15
LMSg global cache service process 16
LMSh global cache service process 17
LMSi global cache service process 18
LMSj global cache service process 19
LMSk global cache service process 20
LMSl global cache service process 21
LMSm global cache service process 22 NAME DESCRIPTION
----- ----------------------------------------------------------------
LMSn global cache service process 23
LMSo global cache service process 24
LMSp global cache service process 25
LMSq global cache service process 26
LMSr global cache service process 27
LMSs global cache service process 28
LMSt global cache service process 29
LMSu global cache service process 30
LMSv global cache service process 31
LMSw global cache service process 32
LMSx global cache service process 33
LMSy global cache service process 34
RMS0 rac management server
LMHB lm heartbeat monitor
MMAN Memory Manager
DBW0 db writer process 0
DBW1 db writer process 1
DBW2 db writer process 2
DBW3 db writer process 3
DBW4 db writer process 4
DBW5 db writer process 5
DBW6 db writer process 6
DBW7 db writer process 7
DBW8 db writer process 8
DBW9 db writer process 9
DBWa db writer process 10 (a)
DBWb db writer process 11 (b)
DBWc db writer process 12 (c)
DBWd db writer process 13 (d)
DBWe db writer process 14 (e)
DBWf db writer process 15 (f)
DBWg db writer process 16 (g)
DBWh db writer process 17 (h)
DBWi db writer process 18 (i)
DBWj db writer process 19 (j)
DBWk db writer process 20 (k)
DBWl db writer process 21 (l)
DBWm db writer process 22 (m)
DBWn db writer process 23 (n)
DBWo db writer process 24 (o)
DBWp db writer process 25 (p)
DBWq db writer process 26 (q)
DBWr db writer process 27 (r)
DBWs db writer process 28 (s)
DBWt db writer process 29 (t)
DBWu db writer process 30 (u)
DBWv db writer process 31 (v) NAME DESCRIPTION
----- ----------------------------------------------------------------
DBWw db writer process 32 (w)
DBWx db writer process 33 (x)
DBWy db writer process 34 (y)
DBWz db writer process 35 (z)
MRP0 Managed Standby Recovery
ARC0 Archival Process 0
ARC1 Archival Process 1
ARC2 Archival Process 2
ARC3 Archival Process 3
ARC4 Archival Process 4
ARC5 Archival Process 5
ARC6 Archival Process 6
ARC7 Archival Process 7
ARC8 Archival Process 8
ARC9 Archival Process 9
ARCa Archival Process 10
ARCb Archival Process 11
ARCc Archival Process 12
ARCd Archival Process 13
ARCe Archival Process 14
ARCf Archival Process 15
ARCg Archival Process 16
ARCh Archival Process 17
ARCi Archival Process 18
ARCj Archival Process 19
ARCk Archival Process 20
ARCl Archival Process 21
ARCm Archival Process 22
ARCn Archival Process 23
ARCo Archival Process 24
ARCp Archival Process 25
ARCq Archival Process 26
ARCr Archival Process 27
ARCs Archival Process 28
ARCt Archival Process 29
NSA1 Redo transport NSA1
NSS1 Redo transport NSS1
NSA2 Redo transport NSA2
NSS2 Redo transport NSS2
NSA3 Redo transport NSA3
NSS3 Redo transport NSS3
NSA4 Redo transport NSA4
NSS4 Redo transport NSS4
NSA5 Redo transport NSA5
NSS5 Redo transport NSS5
NSA6 Redo transport NSA6
NSS6 Redo transport NSS6 NAME DESCRIPTION
----- ----------------------------------------------------------------
NSA7 Redo transport NSA7
NSS7 Redo transport NSS7
NSA8 Redo transport NSA8
NSS8 Redo transport NSS8
NSA9 Redo transport NSA9
NSS9 Redo transport NSS9
NSAA Redo transport NSAA
NSSA Redo transport NSSA
NSAB Redo transport NSAB
NSSB Redo transport NSSB
NSAC Redo transport NSAC
NSSC Redo transport NSSC
NSAD Redo transport NSAD
NSSD Redo transport NSSD
NSAE Redo transport NSAE
NSSE Redo transport NSSE
NSAF Redo transport NSAF
NSSF Redo transport NSSF
NSAG Redo transport NSAG
NSSG Redo transport NSSG
NSAH Redo transport NSAH
NSSH Redo transport NSSH
NSAI Redo transport NSAI
NSSI Redo transport NSSI
NSAJ Redo transport NSAJ
NSSJ Redo transport NSSJ
NSAK Redo transport NSAK
NSSK Redo transport NSSK
NSAL Redo transport NSAL
NSSL Redo transport NSSL
NSAM Redo transport NSAM
NSSM Redo transport NSSM
NSAN Redo transport NSAN
NSSN Redo transport NSSN
NSAO Redo transport NSAO
NSSO Redo transport NSSO
NSAP Redo transport NSAP
NSSP Redo transport NSSP
NSAQ Redo transport NSAQ
NSSQ Redo transport NSSQ
NSAR Redo transport NSAR
NSSR Redo transport NSSR
NSAS Redo transport NSAS
NSSS Redo transport NSSS
NSAT Redo transport NSAT
NSST Redo transport NSST
NSAU Redo transport NSAU NAME DESCRIPTION
----- ----------------------------------------------------------------
NSSU Redo transport NSSU
NSAV Redo transport NSAV
NSSV Redo transport NSSV
LGWR Redo etc.
LCK0 Lock Process 0
CKPT checkpoint
ABMR Auto BMR Background Process
LSP1 Dictionary build process for Logical Standby
LSP0 Logical Standby
LSP2 Set Guard Standby Information for Logical Standby
CTWR Change Tracking Writer
RVWR Recovery Writer
FBDA Flashback Data Archiver Process
SMON System Monitor Process
SMCO Space Manager Process
RECO distributed recovery
GTX0 Global Txn process 0
GTX1 Global Txn process 1
GTX2 Global Txn process 2
GTX3 Global Txn process 3
GTX4 Global Txn process 4
GTX5 Global Txn process 5
GTX6 Global Txn process 6
GTX7 Global Txn process 7
GTX8 Global Txn process 8
GTX9 Global Txn process 9
GTXa Global Txn process 10
GTXb Global Txn process 11
GTXc Global Txn process 12
GTXd Global Txn process 13
GTXe Global Txn process 14
GTXf Global Txn process 15
GTXg Global Txn process 16
GTXh Global Txn process 17
GTXi Global Txn process 18
GTXj Global Txn process 19
CJQ0 Job Queue Coordinator
RCBG Result Cache: Background
EMNC EMON Coordinator
QMNC AQ Coordinator
DMON DG Broker Monitor Process
RSM0 Data Guard Broker Resource Guard Process 0
NSV0 Data Guard Broker NetSlave Process 0
NSV1 Data Guard Broker NetSlave Process 1
NSV2 Data Guard Broker NetSlave Process 2
NSV3 Data Guard Broker NetSlave Process 3
NSV4 Data Guard Broker NetSlave Process 4 NAME DESCRIPTION
----- ----------------------------------------------------------------
NSV5 Data Guard Broker NetSlave Process 5
NSV6 Data Guard Broker NetSlave Process 6
NSV7 Data Guard Broker NetSlave Process 7
NSV8 Data Guard Broker NetSlave Process 8
NSV9 Data Guard Broker NetSlave Process 9
NSVA Data Guard Broker NetSlave Process A
NSVB Data Guard Broker NetSlave Process B
NSVC Data Guard Broker NetSlave Process C
NSVD Data Guard Broker NetSlave Process D
NSVE Data Guard Broker NetSlave Process E
NSVF Data Guard Broker NetSlave Process F
NSVG Data Guard Broker NetSlave Process G
NSVH Data Guard Broker NetSlave Process H
NSVI Data Guard Broker NetSlave Process I
NSVJ Data Guard Broker NetSlave Process J
NSVK Data Guard Broker NetSlave Process K
NSVL Data Guard Broker NetSlave Process L
NSVM Data Guard Broker NetSlave Process M
NSVN Data Guard Broker NetSlave Process N
NSVO Data Guard Broker NetSlave Process O
NSVP Data Guard Broker NetSlave Process P
NSVQ Data Guard Broker NetSlave Process Q
NSVR Data Guard Broker NetSlave Process R
NSVS Data Guard Broker NetSlave Process S
NSVT Data Guard Broker NetSlave Process T
NSVU Data Guard Broker NetSlave Process U
INSV Data Guard Broker INstance SlaVe Process
FSFP Data Guard Broker FSFO Pinger
RBAL ASM Rebalance master
ARB0 ASM Rebalance 0
ARB1 ASM Rebalance 1
ARB2 ASM Rebalance 2
ARB3 ASM Rebalance 3
ARB4 ASM Rebalance 4
ARB5 ASM Rebalance 5
ARB6 ASM Rebalance 6
ARB7 ASM Rebalance 7
ARB8 ASM Rebalance 8
ARB9 ASM Rebalance 9
ARBA ASM Rebalance 10
ASMB ASM Background
MARK mark AU for resync koordinator
GMON diskgroup monitor
VBG0 Volume BG 0
VBG1 Volume BG 1
VBG2 Volume BG 2
VBG3 Volume BG 3 NAME DESCRIPTION
----- ----------------------------------------------------------------
VBG4 Volume BG 4
VBG5 Volume BG 5
VBG6 Volume BG 6
VBG7 Volume BG 7
VBG8 Volume BG 8
VBG9 Volume BG 9
VDBG Volume Driver BG
VMB0 Volume Membership 0
ACFS ACFS CSS
MMON Manageability Monitor Process
MMNL Manageability Monitor Process 2
XDMG cell automation manager
XDWK cell automation worker actions 295 rows selected. SYS@orcl>
四、5： 数据字典
四、 5-1：Oracle数据字典概述
四、 5-2：Oracle 常用数据字典
.
四、 5-2_1：基本数据字典
-----基本数据字典及其说明
select * from dba_tablespaces;---关于表空间的信息
select * from dba_ts_quotas ;---所有用户表空间的限额
select * from dba_free_space;--所有表空间中*分区
select * from dba_segments;--描述数据库中所有段的储存空间；
select * from dba_extents;--数据库中所有分区的信息
select * from dba_tables;---数据库中所有数据表的描述
select * from dba_tab_columns;---所有表、视图 及簇的列
select * from dba_views ;--数据库中所有的视图信息；
select * from synonyms;--关于同义词的信息查询；
select * from dba_sequences;--所有用户序列号信息
select * from dba_constraints;--所有用户表的约束信息；
select * from dba_indexes;--关于数据库索引的描述；
select * from dba_ind_columns;--在所有表及簇上压缩索引的列
select * from dba_triggers;--所有用户的触发器信息
select * from dba_source;--所有用户储存过程的信息；
select * from dba_data_files;--查询关于数据库文件的信息；
select * from dba_tab_privs;--查询关于对象授权的信息
select * from dba_objects;--数据库中所有的对象；
select * from dba_users;--关于数据库中所有用户的信息；
四、 5-2_2：常用动态性能视图
------------常用动态性能视图
select * from v$database ;---描述关于数据库的相关信息
select * from v$datafile;--数据库使用的数据文件信息
select * from v$log;--从控制文件中提取有关重做日志组的信息
select * from v$logfile;--有关实例重置日志组文件名及其位置的信息
select * from v$archived_log;--记录归档日志文件的基本信息
select * from v$archive_dest;--记录归档日志文件的路径信息
select * from v$controlfile ;--描述控制文件的相关信息
select * from v$instance ; ---记录实例的基本信息
select * from v$system_parameter;--显示实例当前有效的参数信息
select * from v$sga;--显示实例的SGA区的大小
select * from v$sgastat;--统计SGA使用情况的信息
select * from v$parameter ;-- 记录初始化参数文件中所有项的值
select * from v$lock ;--通过访问数据库会话，设置对象锁的所有信息
select * from v$session;--有关会话的信息
select * from v$sqltext;--记录SQL语句的信息
select * from v$sql;--记录SQL语句的详细信息
select * from v$bgprocess;--显示后台进程信息；
select * from v$process;--当前进程的信息---------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------