1-架构

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:\APP\ADMINISTRATOR\ORADATA\TEST\SYSTEM01.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的位置。

通过这个练习理解表空间,数据文件,段、区间、数据块的概念

posted @ 2013-06-17 14:43  老猫-DB  阅读(266)  评论(0编辑  收藏  举报