螃哈哈一只

数据库由一个或者多个表空间组成。

表空间由一个或者多个数据文件组成，里面存放着就是段。

段由一个或多个区段组成，一个区段肯定只在一个数据文件中，并且逻辑地址是连续的，然而段可以存在与多个数据文件中。

区段是由最小单位块组成的，是Oracle最小的I/O单位。

所谓的数据库，其实就是Oracle中的实例，本机有2个实例，一个是orcl一个是crab，因此会有2个文件夹，分别代表了2个数据库，如下图所示：

这2个数据库都是由表空间组成的，进去看看

里面的DBF后缀就是我们的数据表空间，SYSAUX，SYSTEM，TEMP，USERS，UNDOTBS
这些表空间随着时间的推移，会越来越大，因此会存在多个数据表空间文件，以01 02 03结尾

增大到一定程度，虽然表空间被多个数据文件隔开了，里面的段可能也因为数据表文件的隔开而分家，比如：有一张表格EMP，由于它太大了，可能一半在USERS01.DBF上，而另一半在USERS02.DBF上，这里的表格就是指表格段，段的其中一种。但是，区段不同，一个段由多个区段组成，这是由Oracle来管理的，不是我们看得见的，它不会存在于2个数据表文件中。

大家口中常说的Oracle参数文件，其实是指Oracle的初始化参数文件。在早期的版本中，初始化参数文件是init.ora，而且一般都是放在客户端，因为管理员经常会在客户端去维护服务器，当从客户端发出启动远程数据库服务器指令的时候，Oracle客户端就会去读取init.ora，把初始化参数文件中的参数传入服务器中，这样就会导致存在大量的init.ora副本，管理员每用一个不同的客户端登陆，就会有一份init.ora。为什么不干脆放在服务器上呢？可能是Oracle开发初期，设计师们为了让管理员维护更方便写吧，所以用了这种方式，毕竟init.ora是一个普通的字符文本，修改维护都非常方便，如果放在服务器端，还得借用ftp等传输，就麻烦了。

不过现在的init.ora已经不常用了，取而代之的是SPFILE，这是最近版本新引入的一种保存参数的文件，它只有一份，并且是存放在服务器端，只有一份。我们可以用select * from V$parameter where name like ‘%spfile%’;这个命令去找存放它的位置，并且V$parameter就是从这个文件读取的。要修改参数也不像以前那样直接修改文件了，而是用命令行ALTER SYSTEM 命令了。

实验中可以发现SPFILE在客户端是没有的

当我用sys登陆时，查询’sessionid’，得到的是4294967295
select userenv(’sessionid’) from dual;
USERENV(’SESSIONID’)
——————–
4294967295

接着我在同一台机子上用另外一个终端继续登陆，得到的还是4294967295
select userenv(’sessionid’) from dual;
USERENV(’SESSIONID’)
——————–
4294967295

我换一台机子，继续用此用户名登陆，得到的还是4294967295

我将OracleOraDb10g_home1TNSListener服务重启，SESSIONID仍旧是4294967295

最后干脆将Oracle数据库服务重启，SESSIONID仍旧是4294967295

从这个实验可以看出，SESSIONID和登录者是关联的，是唯一的。

接下来，我用sys登陆2次，用scott登陆2次
SQL> select SID, AUDSID, USER#, USERNAME from V$Session;

SID AUDSID USER# USERNAME
———- ———- ———- ——————————
150 244 57 SCOTT
151 242 57 SCOTT
152 4294967295 0 SYS
153 4294967295 0 SYS
155 0 0
157 0 0

可以看到，V$Session中的AUDSID其实就是SESSIONID，虽然我创建了4个会话，而AUDSID唯一的就只有2个，这2个恰恰标示了有多少个用户登陆，而不管登陆的次数；而SID则是唯一的标识，只要终端登陆一次，就分配给它唯一的SID，这个SID才是真正的会话标识。有一点要注意，SID是递减的，难道只有150来个可以登陆吗？有待考证（翻阅相关资料，的确是这样的,Oracle默认就是允许150个进程访问）

以前用数据库几乎没有数据字典这个概念，但是在Oracle中，这是一个非常重要的概念，它包含了

• 数据库中所有方案对象（schema object）的定义(包括表，视图，索引，簇，同义词，序列，过程，函数，包，触发器等等)
• 数据库为一个方案对象分配了多少空间，以及该对象当前使用了多少空间
• 列的默认值
• 完整性约束（integrity constraint）信息
• 数据库用户名
• 每个用户被授予（grant）的权限（privilege）与角色（role）
• 监控（audit）信息，例如哪个用户对某个方案对象进行了访问或更新操作
• 数据库中的其他概要信息

可以这么说，Oracle的数据字典，相当与XML的Schema，Schema定义了XML的具体结构，数据字典定义了数据库里面的各种结构，比如有哪些表，表的约束有哪些，等等。

现在，如果我想查看我用了哪些约束，思路应该是，约束–constrai…

于是我查 select * from dictionary where table_name like ‘%CONSTR%’; 注意，这里的dictionary就是数据字典，里面包含了大量定义数据库的表格，结果为：

TABLE_NAME                       COMMENTS ——————————————————————————–
USER_CONSTRAINTS               Constraint definitions on user’s own tables
ALL_CONSTRAINTS                Constraint definitions on accessible tables

于是我知道，USER_CONSTRAINTS里面含有我所登陆的用户的约束信息

继续查： select * from USER_CONSTRAINTS;

出来的结果就是一些主键、是否为空、外键等等信息了。

值得注意的是USER_CONSTRAINTS其实是SYS内建的视图，权限是大家都可以访问，想看USER_CONSTRAINTS是如何定义的吗？呵呵，还是用刚才的思路，查：select * from dictionary where table_name like ‘%VIEWS%’; 可以找到

USER_VIEWS                     Description of the user’s own views
ALL_VIEWS                      Description of views accessible to the user
ALL_MVIEWS                     All materialized views in the database
USER_MVIEWS                    All materialized views in the database
ALL_BASE_TABLE_MVIEWS          All materialized views with log(s) in the database that the user can see
USER_BASE_TABLE_MVIEWS         All materialized views with log(s) owned by the user in the database
ALL_REGISTERED_MVIEWS          Remote materialized views of local tables that the user can see
USER_REGISTERED_MVIEWS         Remote materialized views of local tables currently using logs owned by the user
ALL_XML_VIEWS                  Description of the all XMLType views that the user has privileges on
USER_XML_VIEWS                 Description of the user’s own XMLType views

这里当然是用ALL_VIEWS ，继续看看ALL_VIEWS 的结构，desc ALL_VIEWS ;

Name             Type           Nullable Default Comments
—————- ————– ——– ——- ———————————————————–
OWNER            VARCHAR2(30)                    Owner of the view
VIEW_NAME        VARCHAR2(30)                    Name of the view
TEXT_LENGTH      NUMBER         Y                Length of the view text
TEXT             LONG           Y                View text
TYPE_TEXT_LENGTH NUMBER         Y                Length of the type clause of the object view
TYPE_TEXT        VARCHAR2(4000) Y                Type clause of the object view
OID_TEXT_LENGTH  NUMBER         Y                Length of the WITH OBJECT OID clause of the object view
OID_TEXT         VARCHAR2(4000) Y                WITH OBJECT OID clause of the object view
VIEW_TYPE_OWNER  VARCHAR2(30)   Y                Owner of the type of the view if the view is an object view
VIEW_TYPE        VARCHAR2(30)   Y                Type of the view if the view is an object view
SUPERVIEW_NAME   VARCHAR2(30)   Y                Name of the superview, if view is a subview

好了，接着输出TEXT就可以看到了

Oracle对数据库数据文件（datafile）中的存储空间进行管理的单位是数据块（data block）。数据块是数据库中最小的（逻辑）数据单位。与数据块对应的，所有数据在操作系统级的最小物理存储单位是字节（byte）。

在Oracle中，不论数据块中存储的是表（table）、索引（index）或簇表（clustered data），其内部结构都是类似的。如下图：

本图显示了数据块的各个组成部分，包括：数据块头（包括标准内容和可变内容）（common and variable header），表目录区（table directory），行目录区（row directory），可用空间区（free space），行数据区（row data）。以下各节将分别讲解各个组成部分。图中两个箭头表示一个数据块中的可用空间区的容量是可变的。

数据块头（包括标准内容和可变内容）

数据块头（header）中包含了此数据块的概要信息，例如块地址（block address）及此数据块所属的段（segment）的类型（例如，表或索引）。

表目录区

如果一个数据表在此数据块中储存了数据行，那么数据表的信息将被记录在数据块的表目录区（table directory）中。

行目录区

此区域包含数据块中存储的数据行的信息（每个数据行片断（row piece） 在行数据区（row data area）中的地址）。[一个数据块中可能保存一个完整的数据行，也可能只保存数据行的一部分 ，所以文中使用row piece]

行数据

数据块（data block）中行数据区（row data）包含了表或索引的实际数据。一个数据行可以跨多个数据块。

可用空间区

在插入新数据行，或在更新数据行需要更多空间时（例如，原来某行最后一个字段为空（trailing null），现在要更新为非空值），将 使用可用空间区（free space）中的空间。

如果一个数据块（data block）属于表或簇表的数据段（data segment），或属于索引的索引段（index segment），那么在其可用空间区中还可能会存储事务条目（transaction entry）。如果一个数据块中的数据行（row）正在由 INSERT，UPDATE，DELETE，及 SELECT…FOR UPDATE 语句访问，此数据块中就需要保存事务条目。事务条目所需的存储空间依据操作系统而定。在常见的操作系统中事务条目大约需要占用23字节（byte）。

数据块可用空间的有效性及优化

有两种SQL语句可以增加数据块中的可用空间：分别是 DELETE 语句，和将现有数据值更新为占用容量更小值的 UPDATE 语句。在以下两种条件下，上述两种操作释放的空间可以被后续的 INSERT 语句使用：
如果 INSERT 语句与上述两种操作在同一事务（transaction）中，且位于释放空间的语句之后，那么 INSERT 语句可以使用被释放的空间。
如果 INSERT 语句与释放空间的语句在不同的事务中（比如两者是由不同的用户提交的），那么只有在释放空间的语句提交后，且插入数据必需使用此数据块时，INSERT 语句才会使用被释放的空间。

行链接（Row Chaining）及行迁移（Row Migrating）

有两种情况会导致表中某行数据过大，一个数据块（data block）无法容纳。第一种情况，当一行数据被插入时一个数据块就无法容纳。在这种情况下Oracle将这行数据存储在段内的一个数据块链（chain）中。在插入数据量大的行时常会发生行链接（row chaining），例如一个包含数据类型为 LONG 或 LONG RAW 列的数据行。此时行链接不可避免。

第二种情况，原本存储在一个数据块（data block）内的数据行，因为更新操作导致长度增长，而所在数据块的可用空间也不能容纳增长后的数据行。在这种情况下，Oracle将此行数据迁移（migrate）到新的数据块中。Oracle在被迁移数据行原来所在位置保存一个指向新数据块的指针。被迁移数据行的 rowid 保持不变。

当数据行发生链接（chain）或迁移（migrate）时，对其访问将会造成 I/O 性能降低，因为Oracle为获取这些数据行的数据时，必须访问更多的数据块（data block）。

PCTFREE 和 PCTUSED 如何协同发挥作用

在第一步中，数据块占用空间比例小于80%时才能插入新数据，因为 PCTFREE 参数限定必须保留20%的可用空间用于块内已有数据的更新。

在第二步中，对数据块中已有数据的更新操作可以使用数据块中的保留空间。但删除了一些数据行，或者更新后的数据行变小了，这时已用空间又开始缩小，但是这时候小于80%仍旧不能插入数据的，只有当数据块内的占用空间比例低于40%时才能向其中插入新数据（由PCTUSED控制着）。

在第三步中，当数据块内的占用空间比例低于40%时，此数据块再次可以被用于插入新数据。

在第四步中，数据块占用空间比例小于80%时才能插入新数据，因为 PCTFREE 参数限定必须保留20%的可用空间用于块内已有数据的更新。此过程如此往复循环。

Oracle有关的两个基本内存结构是系统全局区（system global area，SGA）和程序全局区（program global area，PGA）

System Global Area (SGA)

The System Global Area (SGA) is a shared memory region that contains data and control information for one Oracle instance. Oracle allocates the SGA when an instance starts and deallocates it when the instance shuts down. Each instance has its own SGA.

Users currently connected to an Oracle database share the data in the SGA. For optimal performance, the entire SGA should be as large as possible (while still fitting in real memory) to store as much data in memory as possible and to minimize disk I/O.

The information stored in the SGA is divided into several types of memory structures, including the database buffers, redo log buffer, and the shared pool.

Database buffers store the most recently used blocks of data. The set of database buffers in an instance is the database buffer cache. The buffer cache contains modified as well as unmodified blocks. Because the most recently (and often, the most frequently) used data is kept in memory, less disk I/O is necessary, and performance is improved.

The redo log buffer stores redo entries—a log of changes made to the database. The redo entries stored in the redo log buffers are written to an online redo log, which is used if database recovery is necessary. The size of the redo log is static. So if the data was too old, maybe it would be unrecoveried.

The shared pool contains shared memory constructs, such as shared SQL areas. A shared SQL area is required to process every unique SQL statement submitted to a database. A shared SQL area contains information such as the parse tree and execution plan for the corresponding statement. A single shared SQL area is used by multiple applications that issue the same statement, leaving more shared memory for other uses.

Program Global Area (PGA)

The Program Global Area (PGA) is a memory buffer that contains data and control information for a server process. A PGA is created by Oracle when a server process is started. The information in a PGA depends on the Oracle configuration.

——————————————————————————————-
对于经常用到where和order by的字段，最好对其建立索引，但是下面一些情况，索引将不起作用

对于 LIKE ‘er%’ 这种形式，索引是能起作用的，但是对于LIKE %er%这种形式，索引将不起作用，这时最好使用where加等号的形式对其进行全文检索

where name!=’crab’这种形式，索引也起不到作用，仍要一个一个去判断
——————————————————————————————–
主键和外键的数据类型最好一致，否则用where语句性能不是很好

最后来道题目
S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄
C (C#,CN ) C#,CN 分别代表课程编号、课程名称
SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩

查询选修全部课程的学员姓名和所属单位

　　Select SN,SD FROM S
　　Where [S#] IN(
　　Select [S#] FROM C
　　LEFT JOIN
　　SC ON SC.[C#]=C.[C#] GROUP BY [S#]
　　HAVING COUNT([S#])=(select count(*) from C))

以前碰到这个问题都是让着oracle，去改tomcat，可是随着问题越来越多，忍无可忍了，决心把这个给改了。
网上大都说删除 SPFILEORCL.ORA 里面的 dispatchers=’(PROTOCOL=TCP) (SERVICE=XDB)’ 可是我试过不行，重起服务+重新配置还是不行，说明这个方法不是很保障，而且删除后其实是禁止了这个服务，这样不好。我们还是老老实实通过修改端口来做比较好。 阅读全文 »