Oracle培训(BEST)文档手册

数据库

ORACLE张志华（凌志）计算机专业技术培训课程ORACLE10g/11gORACLE数据库的基本知识

学习目标1

关键内容2准备工作3结构设计41数据处理51理论知识6ORACLE10g/11g学习目标1ORACLE10g/11gORACLE主要学习目标掌握表空间及用户的创建、修改和删除等操作了解ORACLE的基本知识熟练掌握记录的插入、删除、修改和查询操作了解PK/CK/UK/FK等知识掌握三种约束培训目标掌握权限管理ORACLE10g/11g

关键内容2ORACLE10g/11g关键内容：oracle流程231创建用户创建表空间分配表空间45数据处理授权ORACLE10g/11g关键内容：创建表空间及表创建主键约束创建唯一性约束创建外键约束创建检查约束

创建表创建表空间ORACLE10g/11g

理论知识3ORACLE10g/11gORACLE中相关名词（一）一、PL/SQLPL/SQL也是一种程序语言，叫做过程化SQL语言（ProceduralLanguage/SQL）。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL就是把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。二、NLSRTL:全球化支持OracleNLSRuntimeLibrary(NLSRTL)：与oracle的字符集文件以及字符支持有关。Oracle的全球支持是通过OracleNLSRuntimeLibrary(NLSRTL)来实施的，NLS运行库通过独立的函数来完成运行时与语言相关的转换及控制。ORACLE10g/11gORACLE中相关名词（二）

三、VSS(VolumeShadowcopyService,卷映射拷贝服务)，它可以在多卷、或者单个卷上创建映射拷贝，同时不会影响到系统性能。A、快速数据备份和恢复；B、针对制定文件的恢复；C、磁带备份、测试，以及数据挖掘方面；VSS整体框架包含了VSS核心模块、请求者(Requestor)、写入者(Writer)，以及提供者(Provider)。import---imp(导入)export---exp(导出)ORACLE10g/11gORACLE中相关名词（三）四、RAC OracleRAC是OracleRealApplicationCluster的简写，官方中文文档一般翻译为“真正应用集群”，它一般有两台或者两台以上同构计算机及共享存储设备构成，可提供强大的数据库处理能力，现在是Oracle10g/11gGrid应用的重要组成部分。五、SGAPGA SGA:系统全局区，用于存储数据库信息的内存区，该信息为数据库所有进程所共享，包括oracle服务器的数据和控制信息。 PGA:程序全局区，包括单个服务器进程或单个后台进程的数据和控制信息，与几个进程共享的SGA刚好相反，PGA只能被一个进程所使用，在进程创建时分配，进程结束是收回。ORACLE10g/11gTNS透明网络底层（一）Oracle中TNS的完整定义：TransparenceNetworkSubstrate，透明网络底层。监听服务是它重要的一部分，不是全部，不要把TNS当作只是监听器。ORACLE当中，如果想访问某个服务器，必须要设置TNS，它不像SQLSERVER那样在客户端自动列举出在局域网内所有的在线服务器，只需在客户端选择需要的服务器，然后使用帐号与密码登录即可。而ORCAL不能自动列举出网内的服务器，需要通过读取TNS配置文件才能列出经过配置的服务器名。配置文件名一般为：tnsnames.ora存取路径：$ORACLE_HOME\network\admin\tnsnames.oraORACLE10g/11gTNS透明网络底层（二）配置示例#TNSNAMES.ORANetworkConfigurationFile:C:\oracle\ora81\network\admin\tnsnames.ora#GeneratedbyOracleconfigurationtools.#sampledatabase_name=orcl(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=servicename)))斜体红色处是需要修改的地方，但是一般我们只修改三处位置，连接别名database_name、连接地址、连接的数据库名servicename。配置好了就可以使用客户端软件（如PL/SQLDeveloper、TOAD）来连接数据库了。ORACLE10g/11gTNSPING命令OracleNet工具（命令）tnsping，是一个OSI会话层的工具，它用来：1）可以检查tcp协议是否可用，即：我们平时说的网络通不通；能验证一台服务器的名字解析（nameresolution，当然是oracle自己的网络服务名）;2）tnsping命令监听服务器上的listener监听是否已开启;3）tnsping命令不检测服务器上的监听的服务名，所以tnsping通，不代表ORACLEDB就能连通。tnsping命令：如果能够ping通，则说明客户端能解析listener的机器名，而且lister也已经启动，但是并不能说明数据库已经打开，而且tnsping的过程与真正客户端连接的过程也不一致。但是如果不能用tnsping通，则肯定连接不到数据库。查看服务配置的ora文件路径：tnsnames.ora格式：tnsping主机名或IP地址(如:tnspinglocalhost)(再如：tnsping00)ORACLE10g/11gLISTENER监听命令监听命令：LSNRCTLlsnrctlstart开启监听lsnrctlstop关闭监听lsnrctlstatus监听状态通过在lsnrctl中输入setdisplayverbose，然后再通过命令service查看，服务状态为READY表示PMON自动注册的服务名，而UNKNOWN则表示该服务是手工在LISTENER.ORA（监听服务配置）中配置的数据库服务！ORACLE10g/11gORACLE的主要管理工具TOAD小青蛙PL/SQLDEVELOPER增强版SQL开发工具OEM(OracleEnterpriseManager)甲骨文企业管理器ORACLE10g/11gTOADforOracle的使用TOAD英文含义是：青蛙蟾蜍。ToolsofOracleApplicationDevelopers(甲骨文应用开发工具)四个版本：标准版、专业版、专家版（开发套装版）、管理套装版。官方网站：TOAD比PL/SQLDEVELOPER贵100-200倍，价格一般在2-4万人民币。在Oracle应用程序的开发过程中，访问数据库对象和编写SQL程序是一件乏味且耗费时间的工作，对数据库进行日常管理也是需要很多SQL脚本才能完成的。QuestSoftware为此提供了高效的Oracle应用开发工具-Toad（ToolsofOracleApplicationDevelopers）。在Toad的新版本中，还加入了DBA模块，可以帮助DBA完成许多日常管理工作。它最大的特点就是简单易用，访问速度快。使用Toad，我们可以通过一个图形化的用户界面快速访问数据库，完成复杂的SQL和PL/SQL代码编辑和测试工作。Toad由Oracle开发专家专门为开发人员而设计，是一个功能强大、结构紧凑的专业化PL/SQL开发环境。ORACLE10g/11gTOAD监听程序配置（服务器端）通过开始---程序---OracleDB11g（服务器端）---配置和移植工具---NetConfigurationAssistant(NCA网络配置助手)，建立监听程序配置。（如果存在监听程序，可以更新配置，没有监听程序，就添加一个）注意：要不断下一步，可以根据需要取一个个性化的名称，默认监听程序为：LISTENER（可保持不变）;网络协议选择:TCP;端口配置时，使用甲骨文默认的标准端口：1521；ORACLE10g/11gTOAD监听程序配置（客户端）通过开始---程序---OracleClient11g（客户端）---配置和移植工具---NetConfigurationAssistant(NCA网络配置助手)，建立与服务器端相同的监听程序（例如：均配置为LISTENER)。注意：如果存在监听程序，可以更新配置，如果没有监听程序，就添加一个新配置;要不断下一步，可以根据实际需要取一个个性化的名称，默认监听程序为：LISTENER（可保持不变）;网络协议选择:TCP;端口配置时，使用甲骨文默认的标准端口：1521；view---ToadOpptions---Oracle---Transactios--勾选Commitaftereverystatement.就可以写完sql语句后自动提交。在Toad中想同时选中多条Insert语句插入数据时会报无效字符,可以Excuteasscript.ORACLE10g/11g

准备工作4ORACLE10g/11g启动：startupopenstartupmountalterdatabasemountalterdatabaseopenORACLE10g/11gORACLE的启动与关闭启动：startupopen关闭：shutdownimmediate(请勿随意关闭)ORACLE主要服务有：（1）OracleService<SID>：数据库服务（*）（2）Oracle<HOME_NAME>TNSListener：监听器服务（*）（3）Oracle<Home_Name>Agent：代理服务（4）OracleDBConsole<SID>企业管理器（浏览器版）(*)（5）Oracle<Home_Name>HTTPServer（6）Oracle<Home_Name>ManagementServerOracle两个主要的组成部分是：数据库和实例Oracle数据库用于存储和检索信息，是数据的集合。包括逻辑结构和物理结构。Oracle实例是指数据库服务器的内存及相关处理程序。甲骨文的使用者身份问题两种身份SYSDBA管理员SystemDatabaseAdministratorSYSOPER操作员SystemOperatorORACLE10g/11gORACLE10g/11g正确使用SQL*PLUS开始---运行---CMD---SQLPLUS开始---运行---SQLPLUS退出：EXIT/QUIT清屏：CLEARSCREEN------相当于DOS中的CLS操作流程：实例---表空间---表---用户---授权Instance---TableSpace---Table---User---Grant每个实例是庞大数据库中的一个具体的分站点，DatabaseConfigurationAssistant（数据库配置助手）可建立新实例。ORACLE10g/11g登录请登录数据库再进行相关操作！管理员登录：sysassysdba或systemassysdba操作员登录：scottassysoper或systemassysoper查看登录者身份：showuser;ORACLE10g/11g查看用户是否锁定查看数据库管理员DBA表的结构情况：descdba_users;查看用户(SYSMAN)的账号是否锁定：selectusername,account_statusfromdba_userswhereusername='SYSMAN';给用户加锁：alterusersysmanaccountlock;给用户解锁：alterusersysmanaccountunlock;[以HR/PM/BI/SCOTT/SYSMAN为例，如果用户账号状态显示加密过期EXPIRED，修改此账号的密码即可]ORACLE10g/11g甲骨文常用配置助手通过开始---程序找到ORACLE安装菜单中的：配置和移植工具使用NCA配置网络NCA---NetConfigurationAssitant(网络配置助手)使用DCA配置新的数据库实例DCA---DatabaseConfigurationAssistant(数据库配置助手)ORACLE10g/11g常用排错命令lsnrctlstatus查看服务器端监听状态tnsping地址或服务器名查看客户端网络连接与服务器监听状态sqlplus/nolog不登录到服务器进行操作，不需要输入用户名和密码ORACLE10g/11g

结构设计（形式）5ORACLE10g/11g如何使用SQLPLUS进行库、表、列操作?(一)进入SQLPLUS,确定你现在是以管理员sysdba身份登录的：1.首先，创建（新）用户：（*）

createuserusernameidentifiedbypassword;

username：新用户名的用户名password:新用户的密码也可以不创建新用户，而仍然用以前的用户，如：继续利用scott用户用户默认是权限锁定的，即accountlock,解锁请用在上面的命令后面加上：accountunlock.只有解锁后的用户才可进行各种操作。以SYSDBA身份登陆时可以修改其他用户的密码，比如：SQL>alteruseruser01identifiedbyuser10;用户已更改。这个是把USER01用户密码修改为USER10ORACLE10g/11g2.创建表空间：

（*）

createtablespacetablespacenamedatafile‘d:\space\data.dbf’

sizexxxm;tablespacename：表空间的名字‘d:\data.dbf’：表空间的存储位置xxx表空间的大小，m单位为兆(M)autoExtendonnext10M--空间的自动增长的值是10Mpermanentonline;--永久使用EXTENTMANAGEMENTLOCALUNIFORMSIZE256K;--扩展本地管理表空间扩展区块的统一大小为256K如何使用SQLPLUS进行库、表、列操作?(二)ORACLE10g/11g如何使用SQLPLUS进行库、表、列操作?(三)3.将表空间分配给用户：（*）

alertuserusernamedefaulttablespacetablespacename;

将名字为tablespacename的表空间分配给username4.给用户授权：

（*）connectresourcedbagrantcreatesession,createtable,unlimitedtablespacetousername;在TOAD中查看用户SQL命令：descusername（用户名）,然后执行，即可弹出窗口;ORACLE10g/11g如何使用SQLPLUS进行库、表、列操作?(四)5.然后再以自己创建的用户登录，登录之后创建表即可。connusername/password@###;###手工指定表空间，将用户与空间建立联系6、创建角色与角色授权（*）

createrole角色名identifiedby密码;--创建新角色grantcreatesessionto角色名；--给角色授予创建会话的权限grant角色名to用户名；

--把角色授予用户ORACLE10g/11g如何使用SQLPLUS进行库、表、列操作?(五)7、删除用户（*）DROPUSERtest[CASCADE];8、删除表空间（*）DROPTABLESPACEtest01INCLUDINGCONTENTSANDDATAFILES;1)删除表空间内容和数据文件；2)一般无效表空间占用磁盘空间,所以这个应该很常用。ORACLE10g/11g如何使用SQLPLUS进行库、表、列操作?(六)9、查看用户角色权限（*）select*fromuser_role_privs;PRIVILEGE-----privilege权限（allrights）10、查看用户表权限select*fromuser_tab_privs;查看已有用户状态

※查看用户与表空间的对应情况：selectusername,account_statusfromdba_users;通过dba_users，查看Oracle账户的锁定状态，状态解释如下：OPEN表示账户为解锁状态；EXPIRED表示账户为过期状态(需要设置口令才能解除此状态)；LOCKED表示账户为锁定状态。※查看用户与表空间的对应情况：selectusername,default_tablespacefromdba_users;ORACLE10g/11g对用户进行锁定和解锁oracle数据库安装好之后，scott之类的用户默认情况下是被锁住的，无法使用scott用户登录数据库。SQL>ALTERUSERusernameACCOUNTUNLOCK;如果要解锁scott，就用scott代替上面的username部分。同样，还可以将一个用户锁住：SQL>ALTERUSERusernameACCOUNTLOCK;替换方式同上。最后补充下，修改用户需要登录sqlplus的这个用户有alteruser的数据库权限。使用dba执行：SQL>grantalterusertousername;这样，对应的需要登录sqlplus的用户就可以去解锁其它用户了。ORACLE10g/11gORACLE10g/11g如何使用SQLPLUS进行库、表、列操作?(七)11、创建表（*）createtabledemo(id属性,name属性,pass属性...);常用的数据类型有：numbervarchar2datecharlong等创建表

createtable表名(idintnotnull,namevarchar2(20)notnull)tablespace表空间名--所属的表空间storage(initial64K--表的初始值minextents1--最小扩展值maxextentsunlimited--最大扩展值);ORACLE10g/11g如何使用SQLPLUS进行库、表、列操作?(八)12、描述表的结构desc表名；（*）13、删除表：drop表名；（*）14、为usrs表添加主键和索引（*）altertableusersaddconstraintpkprimarykey(ID);15、为已经创建users表添加外键（*）altertableusersaddconstraintfk_roleidforeignkey(roleid)referencesrole(role_id)ondeletecascad;--下边写主表的列ondeletecascad是创建级联（cascad层叠、级联、小瀑布）ORACLE10g/11g如何使用SQLPLUS进行库、表、列操作?(九)查看ORACLE中已安装的各种产品组件版本SQL>

select

*

from

v$version;从系统表中提取系统日期：select

sysdate

from

dual;显示所有系统参数的状态：showall;

显示提示信息：prompt要显示的信息（词或句）;

ORACLE10g/11g如何使用SQLPLUS进行库、表、列操作?(十)查询所有的表空间：select*fromv$tablespace;select*fromdba_tablespaces；查询所有用户：（*）select*fromdba_users;查询符合某些条件的用户：（*）

select*fromdba_userswhereusernamelike'ABC%';查看所有表：selecttable_namefromall_tables;查看所有表对象：selectobject_namefromall_objectswhereobject_type='TABLE'；ORACLE10g/11g强调：表结构1、查看表结构DESC表名（*）2、更改表的结构：（*）ALTERTABLE表名ADD字段名字段属性（添加）ALTERTABLE表名MODIFY字段名字段属性（修改）ALTERTABLE表名DROPCOLUMN字段名（删除）altertable表名renamecolumn旧的字段名to新的字段名;（实现字段名的修改）showlinesize（显示行宽）setlinesize数值（设置行宽）ORACLE10g/11g强调：授权与撤消----------------------------授权权限----------------GRANT权限TO用户；（*）一般授权权限有：dba、connect、resource语法结构：授权GRANT角色|权限TO用户（角色）SQL>GRANTCONNECTTOlingzhi;授权成功。SQL>GRANTRESOURCETOlingzhi;授权成功。-------------------------回收权限-------------------------REVOKE角色|权限FROM用户（角色）（*）//修改用户的密码ALTERUSER用户名IDENTIFIEDBY新密码//修改用户处于锁定（非锁定）状态ALTERUSER用户名ACCOUNTLOCK|UNLOCKORACLE10g/11g查看数据库的字符集，解决乱码默认字符集为：ZHS16GBKSELECTPROPERTY_VALUEFROMDATABASE_PROPERTIESWHEREPROPERTY_NAME='NLS_CHARACTERSET';客户端与数据库服务器的字符集一致，方可避免乱码。右键＂我的电脑＂，在＂属性＂－－＂设置＂－－＂环境变量＂－－＂系统变量＂中增加一项，其中变量名为NLS_LANG，变量值为AMERICAN_AMERICA.ZHS16GBK重启ＴＯＡＤ后，即可看到原为乱码的内容，显示为正常中文。ORACLE10g/11gORACLE备份与还原（了解）备份数据就用导入导出imp/exp，导出dmp数据文件，也可导出为sql文件。在sqlplus中，导入用hostimp命令；在sqlplus中，导出用hostexp命令；然后根据每一步的提示自动完成相关导入和导出工作。

ORACLE10g/11g

数据处理（内容）6ORACLE10g/11g强调：增删改查insertinto表名values(值1,值2,值3,值4...);如：insertintomytbvalues(1,'zhang','123456','shanghai');deletefrom表名where条件；如：deletefrommytbwhereid=2;update表名set字段=‘新值’where条件；如：updatemytbsetname='liuhong'whereid=3;select*from表名；如：select*frommytb;ORACLE10g/11g补充：常见登录方法直接登录：sqlplus---用户名---密码以管理员身份登录：sqlplus---用户名assysdb