Oracle数据库的管理与应用

Oracle数据库的管理与应用
（基本理论、实际应用）
1ORACLE数据库及实例的概念
1.1 ORACLE 实例的结构
1.1.1ORACLE数据库实例的概念
数据库实例（Instance，也成为服务器server）就是用来访问一个数据库文件集的一个存储结构及后台进程的集合。

在具有并行服务器（Oracle Parallel Server）选项时，一个单独的数据库可以被多个实例访问。

1.1.2ORACLE数据库实例的组成
每一次在数据库服务器上启动一数据库时，称为系统全局区（System Global Area）的一内存区（简称SGA区）被分配，有多个ORACLE系统进程被启动。

SGA区和这些ORACLE系统进程的结合被称为一个ORACLE实例。

SGA区包含最通用的有关数据库结构信息的查询，由数据块缓冲存储区、字典缓冲区、日志缓冲区和SQL共享池组成；主要的ORACLE后台进程包括系统监控（SMON）、进程监控（PMON）、数据库写入进程（DBWR）、日志写入进程（LGWR）、检查点进程等等。

决定实例的组成（启动哪些后台进程及各启动几个）和大小（数据块的大小及SGA区中各部分所包含的数据块数）的参数存储在文件init.ora中，此文件在实例启动时被读取，也可以在运行中被DBA修改，但在运行中的任何修改都只有在下一次启动时才起作用。

实例的init.ora文件名中通常包含实例的名字，如果一个实例叫“home”，其init.ora文件将被起名为inithome.ora。

1.2 ORACLE数据库的结构和空间管理
1.2.1物理结构
数据库是一个数据的集合。

一个ORACLE数据库在物理上就是一组文件的集合，有以下三类物理文件：
数据文件（data file）：一个数据库有一个或多个物理的数据文件，存储着数据库的所有数据，逻辑数据库结构的数据物理地存储在数据文件中。

日志文件（redo log file）：一个数据库有两个或更多的联机日志文件，用于保存所有数据库事务的日志，在数据库遭到破坏时用来恢复数据库事务。

ORACLE以循环方式向日志文件写入，当运行于ARCHIVELOG模式时，数据库在重写日志文件前将先对其进行备份（不过这将消耗很大的存储空间）。

控制文件（control file）：控制文件用来记录数据库的物理结构，它记录这数据库中所有文件的控制信息，包含下列信息类型：
数据库名；
数据库数据文件和日志文件的名字和位置；
数据库建立日期。

1.2.2逻辑结构
1.2.2.1 表空间、段、范围和数据块
1.2.2.1.1表空间(tablespace)
表空间是数据库的逻辑划分，可利用表空间完成下列工作：
控制数据库数据的磁盘分配，为提高性能，可跨越设备分配存储结构；
将确定的空间份额分配给数据库用户；
通过使单个表空间在线或离线，控制数据的可用性。

数据库建立时会自动建立一个名为SYSTEM的表空间，它总包含整个数据库的数据字典表。

1.2.2.1.2段(segment)、范围(extent)和数据块(data block)
ORACLE通过段、范围和数据块等逻辑结构来更细地控制磁盘空间的使用。

ORACLE中有四种类型的段：数据段、索引段、回滚段和临时段，分别用来存储数据、索引和一些未提交的事务或临时数据。

一个段由一组范围组成，对段的分配以范围为单位。

范围是数据库存储空间分配的逻辑单位，由连续的数据块组成。

数据块是ORACLE管理数据文件中存储空间的单位，是数据库使用的I/O的最小单位。

1.2.2.2 数据库用户（User）、模式(Schema)和模式对象
数据库用户是可访问数据库内容的一个帐号，每一个数据库用户对应一个模式，模式是用户拥有的模式对象的集合。

模式对象为直接引用数据库数据的逻辑结构，模式对象包含如表、视图、索引、聚集、序列、同义词、数据库链、存储过程和包等结构。

模式对象是逻辑的数据存储结构，每一种模式对象在磁盘上没有一个相应文件存储其信息，一个模式对象逻辑地存储在数据库的一个表空间中，同时可以物理地包含在表空间的一个或多个数据文件中。

用图表示模式对象、表空间、段、范围、块与数据文件之间的关系。

对模式对象的简单介绍：序列（Sequence）、同义词（synonym）、存储过程和包（Package）。

2ORACLE数据库的基本管理
2.1 启动和关闭数据库
svrmgrl
svrmgr>connect internal
svrmgr>startup (nomount/mount/open/restrict/force)
svrmgr>shutdown(normal/immediate/abort)
svrmgr>exit
2.2 管理对象的存储参数
Storage存储子句用于指定对象的存储特征，该子句可以出现在建立或更改下列对象的命令中：表空间、表、索引、聚集、回滚段、快照、快照日志等。

Storage参数会影响两个方面：存取数据库中数据所花费的时间长短和数据库中空间如何有效使用。

如果在建立表空间时指定了存储参数，那么该值就会作为在该表空间分配段时的缺省值。

当然，在建立表、索引等对象时还可以单独指定各自的存储参数。

用ALTER命令更改上述对象的存储参数，其新值仅对未来的范围分配有影响。

Storage存储子句中的主要参数如下：
INITIAL：指定对象的第一个范围的大小，以K、M或字节为单位。

NEXT：指定分配给对象的下一范围的大小。

PCTINCREASE：指定第二个范围之后的每一个范围为前一个范围的增长的百分数。

缺省值为50，其含义为后一个范围比前一个大50%。

最小值为0，其含义为以后的范围大小均相同。

MINEXTENTS：指定在建立段时可分配的范围的总数。

MAXEXTENTS：指定可分配给一个对象的范围总数，最大值依赖于操作系统。

其它：OPTIMAL，FREELIST GROUP，FREELISTS
存储参数使用中的常见问题：
在存储参数使用中最常见的问题就是不经过估算，直接使用系统缺省的存储参数设置。

而缺省的设置通常是首片很小，PCTINCREASE值为50，MAXEXTENTS为121，这样可能造成下列问题：首先是随着数据的增长，系
统需不断增加新的范围，而新范围的大小呈指数型增长，很可能造成需要系统分配很大的一个范围，此时系统虽有总量上大于此范围的空闲空间，但并不连续，因此系统无法满足空间分配要求，造成无法继续插入数据；也有可能系统还能提供足够空间，但段已扩展到了121个范围，同样造成无法继续插入数据；同时由于同一段的不同范围间在物理上并不连续，造成I/O性能低下；还有可能造成空间的巨大浪费。

比较正确的做法是很好地根据具体应用估算出每个表（尤其是数据量较大的重点表）的数据量，将每个段的数据尽量分布在较少的范围里，且PCTINCREASE的值不能太大，最好为0。

2.3 管理表空间及数据文件
创建表空间
create tablespace …
通过增加数据文件修改表空间大小
alter tablespace XXX add datafile ‘全路径文件名’ size XXM …
通过修改数据文件大小修改表空间大小
alter database datafie ‘全路径文件名’ resize XXM;
2.4 管理表和索引
create table，create index，alter table，alter index，drop table，drop index
值得注意的是，将一个表及其索引分别建在不同的表空间中有助于提高访问该表数据的性能。

2.5 管理用户
ORACLE系统缺省创建的用户：在一个数据库被创建的同时，缺省建立了两个重要的用户：SYS与SYSTEM。

SYS拥有数据字典表和数据字典表的视图，这些视图供数据库中其它用户使用。

2.6 管理权限及角色
2.6.1系统权限(System Privileges)
ORACLE数据库定义了许多系统权限用来管理对数据库对象的访问，包括Alter any table、Create table、Alter User等几十种。

2.6.2对象权限(Object Privileges)
若没有相应的系统权限，又要访问一个其他用户拥有的对象，必须首先被授予访问这个对象的权限。

典型的用户权限有：对表或视图的插入、查询、修
改、删除，对存储过程的执行权限等。

通过对存储过程执行权限的管理可以在业务逻辑级实现权限的管理。

2.6.3角色（Role）
角色是权限的集合（包括系统权限和对象权限），利用它可以大大简化对权限的管理。

可用GRANT（with admin option）命令授予，REVOKE命令回收。

这种角色管理机制也很值得我们所做的应用系统借鉴。

系统缺省建立了以下角色并分配了相应的系统权限：CONNECT、RESOURCE、DBA、EXP_FULL_DATABASE、IMP_FULL_DATABASE。

但需注意的是，在存储过程中需要的权限必须是直接授予的，而不能是间接从角色继承的，这是一个常犯的错误。

3ORACLE数据库的基本应用
3.1 客户端的连接配置
通过Sql*Net easy config或Net8 easy config工具或手工配置\ORACLE_HOME \NETWORK\ADMIN\tnsnames.ora文件。

例：
SERVER3 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.91.105)(PORT = 1521))
)
(CONNECT_DA TA =
(SID = ora8)
)
)
3.2 部分注册表信息(HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE)
（1）NLS_LANG
客户端语言设置，典型值SIMPLIFIED CHINESE_CHINA.ZHS16GBK （2）NLS_DA TE_FORMA T
客户端日期格式设置。

3.3 SQL*PLUS的使用技巧
3.3.1SQL*PLUS的常用命令
（1）DESC
（2）EDIT
（3）/
（4）SET
（5）SPOOL
（6）@（执行文件）
3.3.2利用SQL*PLUS对数据库进行简单监控
（1）当前SESSION状况（V$SESSION）
select sid,serial#,rpad(username,10) usern,status,osuser,terminal,program
from v$session;
（2）杀会话
alter system kill session ‘sid,serial#’;
（3）查当前锁的情况
select rpad(oracle_username,10) o_name,session_id
sid,os_user_name,decode(locked_mode,0,'None',1,'Null',2,'Row
share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive')
lock_type, process,object_name from V$locked_object,all_objects
where v$locked_object.object_id=all_objects.object_id;
select sql_text from v$sql where address=(select sql_address from v$session where sid=215)
3.3.3与数据字典结合完成一些复杂任务
3.3.3.1 数据字典
数据字典是由一组表和视图组成，它提供有关该数据库的信息，由SYS用户所拥有，驻留在SYSTEM表空间中。

数据字典包含的视图集一般分为三类，彼此以前缀相区别，前缀为USER、ALL、DBA。

前缀为USER的视图，为用户视图，是在用户的模式内；
前缀为ALL的视图，为扩展的用户视图（为用户可存取的视图）；前缀为DBA
的视图为DBA的视图（为全部用户可存取的视图）。

如ALL_TABLES、DBA_TABLES、USER_TABLES。

3.3.3.2 一个例子
例：为一个模式下的所有表创建公共同义词：
SQL>set head off;
SQL> spool c:\temp\test1.sql
SQL> select 'create public synonym '||table_name||' for '||table_name||';' from tabs;
SQL> spool off;
编辑c:\temp\test1.sql文件
SQL>@ c:\temp\test1.sql
3.4 数据库的逻辑备份和恢复
3.4.1导出（export）
用Export工具对数据库进行逻辑备份可以备份整个数据库、指定用户或指定表，它所输出的文件包括完全重建所有备份对象所需的命令，同时也包含了数据。

3.4.2导入（import）
用来将Export工具输出的文件再导入数据库中。

4推荐工具及参考书
4.1 推荐工具
4.1.1Quest Software公司针对ORACLE数据库的开发及管理软件
4.1.1.1 SQL Navigator
推荐原因：
（1）有助于ORACLE的初学者掌握ORACLE数据库的概念和结构；
（2）所提供的各种方便的工具能极大地加快程序（尤其是存储过程）的开发速度，提高程序的开发质量。

4.2 推荐参考书
（1）ORACLE数据库系统基础俞盘翔编注清华大学出版社
（2）ORACLE 8系列丛书机械工业出版社
（3）ORACLE 8i for NT 的帮助文档
●CREA TE GLOBAL TEMPORARY TABLE vcrm.temp_query_charge_sum
● (bill_cycle_id NUMBER,
● acct_item_name V ARCHAR2(20) NOT NULL,
● charge_on_due NUMBER(12,2) NOT NULL,
● charge_payed NUMBER(12,2))
ON COMMIT DELETE ROWS
动态sql的例子
PROCEDURE SYS_CREATE_NEW_USER ( in_v_user_name IN VARCHAR2, in_v_user_pwd IN VARCHAR2, out_i_err_code OUT INTEGER, out_v_err_text OUT VARCHAR2) IS
/
Ä£¿ãªàºÅ£º ϵͳ¹ÜÀíÄ£¿ãÃû³Æ£º
¹¦ÄÜÃâÊö£º
Ä¿ªäªí:
ÒýÓêí:
ÊäÈë²ÎÊý£º
Êä³ö²ÎÊý£º -1 ¸ÃÓû§ÒÑ´æÔÚ -2 ´´½¨Óû§Ê§©Ü -3 ÏòÓû§ÊÚȨcreate sessionʪ³ö´í -100 δ֪´íÎó
ªà д ÈË£ºÀî«áÅæ||2001Ää8ÔÂ28ÈÕÐÞ ¸Ä ÈË£º
´¦ÀíÂß¼­£º
*/
source_cursor INTEGER;
processed INTEGER;
l_count NUMBER(1);
-- ÔÚÉÏÃæÉõÃô³ÌÐòªäÁ¿
BEGIN
select count(*) into l_count from ALL_USERS where username=in_v_user_name;
IF(l_count!=0)THEN out_i_err_code:=-1;
out_v_err_text:='¸ÃÓû§ÒѾ­´æÔÚ'||in_v_user_name;
RETURN;
END IF;
BEGIN--´´½¨ÐµÄÓû§ source_cursor :=DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(source_cursor,
' create user '||in_v_user_name ||
' identified by '||in_v_user_pwd, DBMS_SQL.V7);
processed :=DBMS_SQL.EXECUTE(source_cursor);
EXCEPTION
WHEN OTHERS THEN IF DBMS_SQL.IS_OPEN(source_cursor)THEN DBMS_SQL.CLOSE_CURSOR(source_cursor);
END IF; out_i_err_code:=-2;
out_v_err_text:='´´½¨Óû§'||in_v_user_name||'ʪ³ö´í£¿£¿'||sqlerrm;
RETURN;
END;
out_i_err_code:=0;
out_v_err_text:='´´½¨Óû§'||in_v_user_name||'³É¹¦';
BEGIN--ÊÚÓâÓû§CREATE SESSIONϵͳȨÏÞ DBMS_SQL.PARSE(source_cursor,
' grant create session to '||in_v_user_name, DBMS_SQL.V7);
processed :=DBMS_SQL.EXECUTE(source_cursor);
EXCEPTION
WHEN OTHERS THEN IF DBMS_SQL.IS_OPEN(source_cursor)THEN DBMS_SQL.CLOSE_CURSOR(source_cursor);
END IF; out_i_err_code:=-3;
out_v_err_text:='Ïò'||in_v_user_name||'ÊÚÓâCREATE
SESSIONȨÏÞʪ³ö´í£¿£¿'||sqlerrm;
RETURN;
END;--ÊÚÓâÓû§CREATE SESSIONϵͳȨÏÞ
BEGIN--ÊÚÓâÓû§VCRMROLE½ÇÉ« DBMS_SQL.PARSE(source_cursor,
' grant vcrmrole to '||in_v_user_name, DBMS_SQL.V7);
processed :=DBMS_SQL.EXECUTE(source_cursor);
EXCEPTION
WHEN OTHERS THEN IF DBMS_SQL.IS_OPEN(source_cursor)THEN DBMS_SQL.CLOSE_CURSOR(source_cursor);
END IF; out_i_err_code:=-4;
out_v_err_text:='Ïò'||in_v_user_name||'ÊÚÓâÓû§VCRMROLE½Çɫʪ³ö´í£¿£¿'|| sqlerrm;
RETURN;
END;--ÊÚÓâÓû§VCRMROLE½ÇÉ«
EXCEPTION
WHEN OTHERS THEN IF DBMS_SQL.IS_OPEN(source_cursor)THEN DBMS_SQL.CLOSE_CURSOR(source_cursor);
END IF;
out_i_err_code:=-100;
out_v_err_text:='«¢Éöδ֪´íÎ󣿣¿'||sqlerrm;
RETURN;
END;
-- ¹ý³Ì½áÊø
一个dba用户的权限只是针对自身用户所创建的各种结构有dba权限,对其他用户所建的结构必须再付权限,如 grant any role to …
在动态视图中使用GROUP BY
使用(select .. from .. group by ..) g 当成一个动态的视图或表
例:
select d.dept_name,
e.emp_last_name,
g.avg_salary
from dept d,
emp e,
(select dept_no,
avg(salary)
from emp
group by dept_no ) g
where e.dept_no = d.dept_no
and e.dept_no = g.dept_no
and e.salary <= 0.80 * g.salary;
NOT EXISTS的使用
最好不要使用NOT IN 的语句,因为他被优化器转换后不能使用索引.
因此子查询最好使用NOT EXISTS
SELECT d.dept_name,
FROM dept d
WHERE not exists ( select ‘ｘ’
From emp e
Where e.dept_no = d.dept_no); 从一个表向另一个不存在的表拷贝数据
CREA TE TABLE LJH
AS
SELECT AD,
BC
FROM GOOD
WHERE ….
DESC 表名 ---可以列出表结构
数据库联接
CREA TE DA TABASE LINK ljh
CONNECT TO scott IDENTIFIED BY tiger
USING ‘DFDFDF’
SELECT * FROM EMP@ljh。