1、探求Oracle架构
使用Sql*plus，用sys用户登录
[oracle@rhlinux5 ~]$ sqlplus / as sysdba
观察Oracle实例的后台进程和服务器进程：
SQL> select program from v$process;
查看控制文件文件分布：
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
+DATA/mydb/controlfile/current.261.766011275
+DATA/mydb/controlfile/current.260.766011275
查看数据文件分布：
SQL> col name format a50
SQL> select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------
1 +DATA/mydb/datafile/system.256.766011189
2 +DATA/mydb/datafile/sysaux.257.766011189
3 +DATA/mydb/datafile/undotbs1.258.766011189
4 +DATA/mydb/datafile/users.259.766011189
5 +DATA/mydb/datafile/example.269.766011309
6 +DATA/mydb/datafile/test.271.766051993
7 +DATA/mydb/datafile/tbs1.272.768757719
8 +DATA/mydb/datafile/tbs2.dbf
查看表空间及对应的数据库文件
SQL>col tablespace_name format a30
SQl>col file_name format a50
SQL> set line 300
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------
USERS +DATA/mydb/datafile/users.259.766011189
UNDOTBS1 +DATA/mydb/datafile/undotbs1.258.766011189
SYSAUX +DATA/mydb/datafile/sysaux.257.766011189
SYSTEM +DATA/mydb/datafile/system.256.766011189
EXAMPLE +DATA/mydb/datafile/example.269.766011309
TEST +DATA/mydb/datafile/test.271.766051993
TBS1 +DATA/mydb/datafile/tbs1.272.768757719
TBS2 +DATA/mydb/datafile/tbs2.dbf
查看日志组信息：
SQL> select group#,members,bytes/1024/1024,status from v$log;
GROUP# MEMBERS BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------------
1 2 50 INACTIVE
2 2 50 CURRENT
3 2 50 INACTIVE
查看日志成员分布：
SQL>col member format a50;
SQL> select group#,member from v$logfile order by group#;
GROUP# MEMBER
---------- --------------------------------------------------
1 +DATA/mydb/onlinelog/group_1.262.766011279
1 +DATA/mydb/onlinelog/group_1.263.766011281
2 +DATA/mydb/onlinelog/group_2.264.766011283
2 +DATA/mydb/onlinelog/group_2.265.766011283
3 +DATA/mydb/onlinelog/group_3.266.766011285
3 +DATA/mydb/onlinelog/group_3.267.766011287
查看SGA信息
SQL> select * from v$sgainfo;
NAME BYTES RES
-------------------------------------------------- ---------- ---
Fixed SGA Size 1339824 No
Redo Buffers 5132288 No
Buffer Cache Size 285212672 Yes
Shared Pool Size 318767104 Yes
Large Pool Size 4194304 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 8388608 Yes
Shared IO Pool Size 0 Yes
Granule Size 4194304 No
Maximum SGA Size 849530880 No
Startup overhead in Shared Pool 62914560 No
NAME BYTES RES
-------------------------------------------------- ---------- ---
Free SGA Memory Available 222298112
查看动态内存分配信息
SQL>col component format a30
SQL> select component,current_size,min_size,max_size from v$memory_dynamic_components;
COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE
------------------------------ ------------ ---------- ----------
shared pool 318767104 318767104 318767104
large pool 4194304 4194304 4194304
java pool 4194304 4194304 4194304
streams pool 8388608 8388608 8388608
SGA Target 629145600 629145600 629145600
DEFAULT buffer cache 285212672 285212672 285212672
KEEP buffer cache 0 0 0
RECYCLE buffer cache 0 0 0
DEFAULT 2K buffer cache 0 0 0
DEFAULT 4K buffer cache 0 0 0
DEFAULT 8K buffer cache 0 0 0
COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE
------------------------------ ------------ ---------- ----------
DEFAULT 16K buffer cache 0 0 0
DEFAULT 32K buffer cache 0 0 0
Shared IO Pool 0 0 0
PGA Target 222298112 222298112 222298112
ASM Buffer Cache 0 0 0
2、创建一个表段，然后计算它的物理位置，理解段、区、块的概念
1、以SYSTEM 用户登录数据库
sqlplus system/password
2、创建表
SQL>create table test1 (id number);
3、确定所在的表空间，区间大小，区间所在文件号，以及作为区间开始位置的文件块
SQL>select tablespace_name, extent_id,bytes,file_id,block_id
from dba_extents where owner='SYSTEM' and segment_name='TEST1';
TABLESPACE_NAME EXTENT_ID BYTES FILE_ID BLOCK_ID
------------------------------ ---------- ---------- ---------- ----------
SYSTEM 0 65536 1 111985
4、根据文件ID确定文件名称，看到提示，用前面查询的file_id替换
SQL> select file_name from dba_data_files where file_id=&file_id;
输入 file_id 的值: 1
原值 1: select file_name from dba_data_files where file_id=&file_id
新值 1: select file_name from dba_data_files where file_id=1
FILE_NAME
--------------------------------------------------------------------------------
E:APPADMINISTRATORORADATATESTSYSTEM01.DBF
表段存在一个64k的区间中
5、计算区间在文件中的位置
SQL>select block_size*&block_id/1024/1024 from dba_tablespaces
where tablespace_name='&tablespace';
替换上面查询的block_id和tablespace_name
主要tablespace_name 要大写。
输入 block_id 的值: 111985
原值 1: select block_size*&block_id/1024/1024 from dba_tablespaces
新值 1: select block_size*111985/1024/1024 from dba_tablespaces
输入 tablespace 的值: SYSTEM
原值 2: where tablespace_name='&tablespace'
新值 2: where tablespace_name='SYSTEM'
BLOCK_SIZE*111985/1024/1024
---------------------------
874.882813
这个区间开始于文件874M的位置。
通过这个练习理解表空间，数据文件，段、区间、数据块的概念