ORACLE数据库设计说明.docx
《ORACLE数据库设计说明.docx》由会员分享，可在线阅读，更多相关《ORACLE数据库设计说明.docx（92页珍藏版）》请在冰豆网上搜索。
ORACLE数据库设计说明
第1章Oracle数据库安装配置
当安装OracleDatabase时，如果没有建立数据库，在安装完成之后可以使用DBCA工具建立数据库。
数据库配置助手（DatabaseConfigurationAssistant）用于建立数据库、配置数据库选项、删除数据库和管理模板。
当建立了Oracle数据库之后，为了使得服务器端可以监听该Oracle数据库，必须配置监听程序。
为了使得客户端可以访问该数据库，必须在客户端配置网络服务名。
只有合理地配置了监听程序和网络服务名之后，客户应用才能访问该数据库。
配置监听程序和网络服务名可以使用网络管理工具NetManager完成。
（源码网整理：
.codepub.）
1.1配置监听程序
监听程序用于接收客户端的连接请求。
当客户应用访问OracleServer时，监听程序会接收并检查连接请求，以确定是否可以为该客户应用提供数据服务。
在建立了Oracle数据库之后，为了使得客户应用可以访问Oracle数据库，必须在监听程序中追加该数据库。
一个监听程序可以监听多个Oracle数据库，多个监听程序也可以监听同一个Oracle数据库。
当安装数据库产品时，会自动建立默认监听程序LISTENER。
配置监听程序的具体步骤如下：
●展开监听程序，并选中LISTENER节点，此时在NETMANAGER窗口右端会显示默认监听位置，其中“协议”用于指定监听程序要使用的网络协议（默认为TCP/IP）；“主机”用于指定服务器所在机器的主机名或IP地址；“端口”用于指定监听程序要使用的TCP/IP端口号（默认1521）。
●在NetManager窗口上端的下拉列表中选择数据库服务，此时会显示默认的数据库配置，“全局数据库名”用于指定数据库的全局数据库名；“Oracle主目录”用于指定Oracle数据库软件的安装路径；“SID”用于指数据库例程名。
为了监听新建的数据库（如DEMO），必须追加该数据库。
●单击“添加数据库”按钮，然后进行相应配置，在“全局数据库名”处输入DEMO数据库的初始化参数SERVICE—HOME所对应的值，在SID处输入DEMO数据库的例程名。
配置了监听程序之后，保存网络配置信息。
●保存了监听程序配置之后，为了使得其网络配置生效，必须重新启动监听程序。
（通过服务器管理器重新启动监听程序）
1.2配置网络服务名
●选中“服务命名”，然后单击+按钮，此时会显示“Net服务名”界面，建议使用数据库名作网络服务名。
●选取与监听程序一致的网络协议“TCP/IP”。
●指定数据库所在主机名及其监听端口号。
●指定监听程序所配置的全局数据库名或者SID。
●测试网络服务名配置是否成功，如果成功则表示网络服务名配置正确。
●完成网络服务名配置之后，保存网络配置信息。
第2章管理用户访问
本章主要容：
●表空间、用户及方案概述
●用户访问
●创建用户
●修改用户
●授予用户访问权
●撤消用户
●废除用户访问
●创建角色
2.1表空间、用户及方案概述
表空间是数据库的逻辑组成部分。
从物理上说，数据库数据存放在数据文件中；从逻辑上说，数据库数据存放在表空间（tablespace）中，并且表空间是由一个或多个数据文件组成的。
一个表空间是由一个或多个数据文件组成的。
用户（也称为）是定义在数据库中的一个名称，它是Oracle数据库的基本访问控制机制。
当连接到Oracle数据库时，默认情况下必须要提供用户名和口令。
只有在输入了正确的用户名和口令之后，才能够连接到数据库，并执行各种管理操作和数据访问操作。
方案（Schema）是用户所拥有数据库对象的集合。
在Oracle数据库中对象是以用户来组织的，用户与方案是一一对应的关系，并且二者名称相同。
例SCOTT用户所拥有的所有对象都属于SCOTT方案，而SYSTEM用户所拥有的所有对象都属于SYSTEM方案。
当访问数据库对象时，有一些注意事项：
●在同一个方案中不能存在同名对象，但不同方案可以具有同名对象。
●用户可以直接访问其方案对象，但如果要访问其他方案对象，则必须具有对象权限。
如用户SCOTT可以直接查询其方案表EMP，但如果用户SMITH要检索SCOTT方案的表EMP，则必须在EMP表上具有SELECT对象权限。
●当用户访问其他方案对象时，必须加方案名为前缀。
例，如用户SMITH要访问SCOTT方案的EMP表，则必须使用SCOTT.EMP。
2.2用户访问
在多用户环境里，一个数据库可能有多个用户同时在访问。
当有不同的用户同时访问数据库时，保护数据库安全，防非授权访问非常重要。
因此，必须在数据库里创建用户，并为用户指定用户名和密码，这样可以保证只有经过授权的，即有正确用户名和密码的用户才能访问数据库。
数据库管理员（DBA）是最高级别的用户，他可以创建其他用户。
在创建了用户后，DBA需要按用户的需求为用户分配权限。
权限指用户执行特定语句的许可，这意味并非所有用户都被允许修改重要数据。
例如，某个用户可能只需要有连接数据库和查询某些表的记录的权限。
类似地，另一个用户可能要求有创建和修改表的权限。
DBA有访问数据库的一切权限，并有权为其他用户分配权限。
下表列出了部分DBA权限：
DBA权限
有权执行
CREATEUSER
创建新用户
DROPUSER
撤消用户
DROPANYTABLE
撤消表
BACKUPANYTABLE
为表制作备份
SELECTANYTABLE
查询数据库对象，如表和视图
CREATEANYTABLE
创建表
为了维护存储在数据库中数据的安全，Oracle提供了以下数据库安全措施：
●管理和控制数据库访问
●用Oracle数据字典验证权限
●为指定用户提供对数据库特定对象（表、视图和序列等）的访问
●为数据库对象提供同义词
可被数据库操作采用的数据库的安全策略包括：
●系统安全：
系统安全涉及系统级的访问，如允许用户通过指出用户名和密码连接Oracle，为用户分配磁盘空间，限定用户所能执行的操作。
用户能执行的操作包括：
查询数据库对象的容、创建数据库对象和更改数据库对象。
●数据安全：
数据安全涉及对数据库对象的访问和使用，以及用户在数据库对象上所拥有的权限的程度。
2.3创建用户
CREATEUSER语句用于创建新用户。
在创建新用户时，必须为新用户指定用户名和密码。
为了使新用户能登录服务器和访问数据库，DBA必须显式地为用户分配权限。
只有拥有CREATEUSER权限的用户才能创建新用户。
例如，DBA创建了新用户Susan，但Susan无权创建其他新用户。
只有当Susan有了CREATEUSER这个权限后，她才能创建别的新用户。
创建用户的语法示例：
CREATEUSERuserIDENTIFIEDBYpassword
DEFAULTTABLESPACEdata01
TEMPORARYTABLESPACEtemp
QUOTA3MONdata01
PASSWORDEXPIRE;
其中IDENTIFIEDBY用于指定用户口令；DEFAULTTABLESPACE用于指定用户的默认表空间，当建立表或者索引时，如果不指定TABLESPACE子句，那么Oracle会自动在默认表空间上为这些对象分配空间；TEMPORARYTABLESPACE用于指定用户的临时表空间，当用户执行排序操作时，或临时数据超过PGA工作区，则会在该表空间上建立临时段；QUOTA用于指定表空间配额，即用户对象在表空间上可占用的最大空间；PASSWORDEXPIRE用于指定终止口令，最终强制用户在登录时改变口令。
当建立了新用户之后，需要注意以下问题：
●初始创建的数据库用户没有任何权限，不能执行任何数据库操作。
●如果在建立用户时不指定DEFAULTTABLESPACE子句，那么Oracle会将数据库默认表空间作为用户的默认表空间。
在OracleDatabase10g之前，如果不指定DEFAULTTABLESPACE子句，那么Oracle会将SYSTEM表空间作为用户的默认表空间。
●如果在建立用户时不指定TEMPORARYTABLESPACE子句，那么Oracle会将数据库默认临时表空间作为用户的临时表空间。
●如果在建立用户时没有为特定表空间指定QUOTA子句，那么用户在特定表空间上的配额为0，这样用户将不能在相应表空间上建立数据对象。
2.4修改用户
修改用户信息是使用ALTERUSER命令完成的。
一般情况下，该命令是由DBA来执行的，如果以其他用户身份修改用户信息，必须要具有ALTERUSER系统权限。
1．修改口令
创建用户时为每个用户指定一个初始密码。
之后可修改密码，修改密码的方法有两种：
●管理员修改：
ALTERUSERuserIDENTIFIEDBYpassword;（password为用户的新密码）
●用户自己修改：
用户登录后输入命令：
PASSWORD；
修改表空间配额
表空间配额用于限制用户对象在表空间上可占用的最大空间。
如果用户对象己经占满了表空间配额所允许的最大空间，那么该用户将不能在该表空间上分配新的空间。
此时如果执行了涉及到空间分配的SQL操作（如INSERT、UPDATE、CREATETABLE等），则会显示错误，修改表空间语法：
ALTERUSERuserQUOTA10MONdata01;
2.5锁定用户账户
为了禁止特定数据库用户用户访问数据库，DBA可以锁定用户账户，
ALTERUSERuserACCOUNTLOCK;
2.6解锁用户账户
为了使得数据库用户可以访问数据库，DBA可以解锁用户账户。
ALTERUSERuserACCOUNTUNLOCK；
2.7默认角色
当将多个角色授予数据库用户之后，通过使用ALTERUSER命令可以设置用户的默认角色。
（当为用户指定了默认角色后，以该用户身份登录时会自动激活其默认角色，并不激活非默认角色）
ALTERUSERuserDEFAULTROLEselect_role;
[补充：
]
默认角色和非默认角色的区别是什么?
这个理解有多种，第一种：
默认角色可以是我们创建数据库就可以见的connect/dba等，非默认角色需要我们自己创建；第二种：
一个用户可以有多个角色，默认的角色登录即生效，非默认的需要激活才能使用。
默认角色和权限集是Oracle安装过程中预先定义的。
每个版本的默认角色都有所变化。
CREATEUSERSMISIDENTIFIEDBYSMIS；
grantdba,connecttoSMIS;
createroler_px;
grantr_pxtoSMIS;
select*fromdba_role_privswheregrantee='SMIS';
alteruserSMISdefaultroleallexceptr_px;
select*fromdba_role_privswheregrantee='SMIS';
2.8授予用户访问权
用户创建后，数据库管理员需要为他分配权限。
权限关系到数据库的安全，它决定了用户在数据库上所能执行的操作。
GRANT语句用于为用户分配权限。
语法如下：
GRANTprivilegeTOuser;
[注释：
授予所有系统权限GRANTALLPRIVILEGEStotest_2;]
[注释：
授予所有对象权限GRANTALL[PRIVILEGES]ONDEMO.DEPTTOTEST_1;]
可分配给用户的权限有：
●系统权限：
允许用户访问数据库的权限称为系统权限。
●对象权限：
允许用户在数据库对象上执行查询、更新、删除或添加数据等操作的权限，此称为对象权限。
常用的系统权限有：
系统权限
有权执行
CREATESESSION
连接数据库
CREATETABLE
创建表
CREATEVIEW
创建视图
CREATEPUBLICSYNONYM
建立同义词
CREATESEQUENCE
创建序列
CREATEPROCEDURE
建立过程、函数和包
CREATETRIGGER
建立触发器
CREATECLUSTER
建立簇
CREATETYPE
建立对象类型
CREATEDATABASELINK
建立数据库链
另外，Oracle还提供了一类ANY系统权限，当用户具有该类系统权限时，可以在
任何方案中执行相应操作。
例如，如果用户具有SELECTANYTABLE系统权限，那么用户可以查询任何方案的表（除数据字典基表和数据字典视图DBA_XXX----DBA和特权用户专访）。
如：
GRANTCREATESESSION,CREATESEQUENCE,CREATEVIEWTOjohn;
上述命令执行后，用户john将拥有创建会话、视图和序列的系统权限。
*Oracle提供了100多种系统权限（ALTERTABLE，ALTERVIEW，ALTERPROCEDURE，DROPTABLE，DROPVIEW，DROPPROCEDURE等）。
一般情况下，授予系统权限是由DBA来完成的；如果要以其他用户身份授予系统权限，则要求该用户必须具有GRANTANYPRIVILEGE系统权限，或者具有相应系统权限及其转授系统权限选项（WITHADMINOPTION），授予系统权限是使用GRANT命令来完成的，其语法如下：
GRANTsystem_priv[,system_priv,…]
TO{user|role|public},[,{user|role|public}]...
[WITHADMINOPTION];
注：
user也可以是用户组PUBLIC；UNLIMITEDTABLESPACE权限不能被授予角色
下表列出了所有对象权限。
对象权限
适用于
ALTER
表、序列
DELETE
表、视图
EXECUTE
过程
INDEX
表
INSERT
表、视图
REFERENCES
表（基于表建立从表）
SELECT
表、视图、序列
UPDATE
表、视图
如果用户在某个同义词上拥有权限，而这个同义词又需要引用其他基表，那么用户
在同义词上拥有的权限会转换为在基表上的权限。
缺省地，用户对他所创建的对象拥有完全的权限。
如用户在用户模式上创建了一个表，那么缺省地，他在用户模式上拥有所创建的这个表的所有权限。
授予对象权限一般情况下是由对象所有者或者DBA用户来完成的；如果以其他用户身份授予对象权限，则要求用户必须具有该对象权限及转授对象权限选项（WITHGRANTOPTION），语法如下：
GRANT{object_priv[（columns）][,object_priv[（columns）]]…
|ALL[PRIVILEGES]}ON[schema.]object
TO{user|role|PUBLIC}[,{user|role|PUBLIC}]…
[WITHGRANTOPTION];
例：
grantupdateonscott..emptoblake;
Grantupdate（sal）onemptoblake;（只能在insert、update和references上授予列权限
2.9撤消用户
DROPUSER语句用于删除用户。
语法：
DROPUSERusername;
撤消用户时，用户创建的对象并没有被撤消。
为了撤消创建对象的用户
语法：
DROPUSERusernameCASCADE；
（不指定CASCADE不能撤消创建对象的用户）
2.10废除用户访问
一般情况下，收回系统权限是由DBA来完成的；如果以其他用户身份收回系统权限，则要求该用户必须具有相应系统权限及其转授系统权限选项（WITHADMINOPTION）。
收回系统权限是使用REVOKE命令来完成的。
语法如下：
REVOKEsystem_priv[,system_priv]…
FROM{user|role|PUBLIC}[,{user|role|PUBLIC}]…
用户的权限可使用REVOKE语句废除。
一旦某个用户的权限被撤消，由他创建的所有用户和从他那获得权限的所有用户也都被撤消。
如，Jim把CREATETABLE权限授给John。
当Jim的权限被撤消时，同时自动撤消John的权限。
如：
GRANTCREATETABLETOJohn;
REVOKECREATETABLEFROMJohn;（不能撤消自己的权限）
一般情况下，收回对象权限是由对象所有者或者DBA用户来完成的；如果以其他用户身份收回对象权限，则要求用户必须具有该对象权限及转授对象权限选项（WITHGRANTOPTION）。
语法如下：
REVOKE{object_priv[,object_priv]…|ALL[PRIVILEGES]}
ON[schema.]object
FROM{user|role|PUBLIC}[,{user|role|PUBLIC}]…
[CASCADECONSTRAINTS];
CASCADECONSTRAINTS用于删除任何与该对象相关的约束和对象，例如索引、触发器、权限、完整性约束等。
2.11创建角色
角色是相关权限的命名集合，使用角色的主要目的是为了简化权限管理。
角色可以是权限的组合，也可以是角色的组合。
角色包括预定义角色和自定义角色两类。
常用的预定义角色有：
（oracle10g）
●CONNECTRole：
分配给临时用户的角色。
通常，为只需要查询材料而无须创建表的用户分配这个角色。
●RESOURCERole：
这个角色分配给常规用户
●DBARole：
这个角色拥有一切系统权限，包括不加限制的表空间配额以及WITHADMINOPTION选项。
默认的DBA用户为SYS和SYSTEM，他们可以将任何系统权限授予其他用户。
读者需要注意，DBA角色不具备SYSDBA和SYSOPER特权。
创建角色的语法：
CREATEROLErole[NOTIDENTIFIED];不验证—用于公用角色或用户默认角色
或
CREATEROLErole
IDENTIFIEDBYpassword;数据库验证
为角色分配密码或修改角色密码：
ALTERROLErole
IDENTIFIEDBYpassword;（password为要设置的新密码）
如：
CREATEROLEAcadre;
ALTERROLEAcadreIDENTIFIEDBYsuccess;
为角色授予权限
GRANTprivilegeTOrole;
例：
只授予CONNECT权限给角色Acadre，这样他们不可能操作数据库。
GRANTconnectTOAcadre;
将角色指派给用户
一个角色可指派给多个用户。
类似的，一个用户也可具有多个角色。
当把角色指派给用户时，赋予该角色的权限也自动分配给用户。
为用户分配角色的语法：
GRANTroleTOuser;
也可以使用ALTERUSER语句授权用户。
ALTERUSERJohnDefaultROLEStudent;
授予用户John以缺省角色student。
2.12新建的用户怎样才能成功创建一个表
当一个用户刚被创建时是不具备任何权限的，因此要在该用户模式下创建表，需授予CREATESESSION、CREATETABLE、以及UNLIMITEDTABLESPACE（或分配配额）权限，因为：
当用户要连接到数据库时必须拥有CREATESESSION权限
当用户要创建表时必须拥有CREATETABLE权限，同时用户还需要在表空间中拥有配额或者被授予UNLIMITEDTABLESPACE。
现在我们来做一个测试：
1）、创建用户TEST，密码为passwd_1:
SQL>CREATEUSERtest
IDENTIFIEDBYpasswd_I1
用户已创建
2）当用TEST连接数据库时：
SQL>conntest/passwd_1
ERROR:
ORA-01045:
userTESTlacksCREATESESSIONprivilege;logondenied
警告:
您不再连接到ORACLE。
//因为缺少CREATESESSION的权限，登陆失败。
3）利用SYS给TEST授予CREATESESSION权限：
SQL>grantcreatesessiontotest;
授权成功。
4）SQL>conntest/passwd_1
已连接。
5）在test的方案中创建表exam1:
SQL>createtableexam1
（student_idint,
paper_idint）;
createtableexam1
ERROR位于第1行:
ORA-01031:
权限不足
//因为未给TEST用户授予createtable权限，因此不能够创建表exam1.
6）给TEST用户授予CRETETABLE权限
SQL>grantcreatetabletotest;
授权成功。
7）SQL>createtableexam1
（student_idint,
paper_idint）;
createtableexam1
ERROR位于第1行:
ORA-01950:
表空间'SYSTEM'中无权限
//因为在创建用户时没有指定表空间，因此默认的表空间是SYSTEM表空间，而TEST用户还需要在表空间SYSTEM中既没有拥有配额又没有被授予UNLIMITEDTABLESPACE权限，因此对于这种情况有两种解决办法：
第一种方法：
SQL>alterusertest
quota15monsystem;
用户已更改。
//在SYSTEM表空间中，给用户TEST分配15M的使用空间
SQL>createtableexam1
（student_idint,
paper_idint）;
表已创建
第二种方法：
SQL>grantunlimitedtablespacetotest
授权成功。
SQL>createtableexam2
（student_idint,
paper_idint）;
第3章建立和管理表
本章主要容：
●表简介
●创建表
●修改表
●截断和删除表
●显示表的信息
3.1简介
表是Oracle数据库最基本的对象，它用于存储用户数据。
●设计表
当设计表时，需要考虑以下因素：
✓当规划表和列时，应该使用有意义的名称。
当定义表名和列名时，只能使用字符（A－Z，a-z）、数字（0－9）、_、$和#，名称必须以字符开始，并且长度不能超过30个字符。
✓当规划表名和列名时，要使用一致的缩写格式、单数或复数格式。
✓为了给用户和其他人员提供有意义的帮助信息
升级会员 