ORACLE数据库全技术.doc

《ORACLE数据库全技术.doc》由会员分享，可在线阅读，更多相关《ORACLE数据库全技术.doc（40页珍藏版）》请在三一办公上搜索。

1、Oracle数据库全技术（V1.0）宏智科技股份有限公司2001年 11月日 期：2001年 11月密 级：公开资料 内部资料 保密资料 机密资料状 态：初稿 讨论稿 发布项 目： 作 者：版权声明宏智科技股份有限公司版权所有，保留一切权利。未经本公司书面许可，任何单位和个人不得擅自摘抄、复制本文档的部分或全部，并以任何形式传播。文档记录页版本日期责任人记录说明备注V1.02001.12.14王强文档初稿建立审批页日期负责人职务签字确认目 录1数据库71.1参数71.2启动71.3关闭81.4日志文件91.5SQL语句分类92系统功能92.1包92.1.1DBMS_Job包的用法102.2系统

2、数据字典132.2.1v$bgprocess(后台进程)192.2.2表相关192.2.3各类文件相关202.2.4SGA区212.3公用子句212.3.1存储子句(storage)212.3.2约束子句（constraint）243表空间243.1数据文件243.2建立253.2.1在裸设备上建表空间253.3系统表空间253.4使用检查253.4.1空间占用情况254安全管理264.1角色（role）264.1.1建立/修改/删除264.1.2系统提供的角色264.2用户（user）274.2.1建立/修改/删除274.2.2系统提供的用户274.3权限284.3.1赋权284.3.2回收

3、284.3.3说明284.4检查284.4.1SYSDBA/SYSOPER权限285表285.1表操作285.1.1语法285.1.2说明295.2数据操作305.2.1语法305.2.2说明305.2.3性能估算315.3约束(CONSTRAINT)315.3.1主键(primary key)315.4数据处理315.4.1删除一个表中重复的记录315.4.2两个表中数据比较326索引327回滚段管理327.1回滚段概述327.1.1回滚段的作用327.1.2回滚段的种类337.1.3回滚段的使用337.1.4回滚段的扩张（EXTEND）337.1.5回滚段的回收和OPTIMAL参数337.

4、2回滚段操作347.2.1创建回滚段347.2.2使回滚段在线347.2.3修改回滚段的存储参数347.2.4回收回滚段的空间357.2.5使回滚段离线357.2.6删除回滚段357.3查询回滚段的信息367.3.1回滚段的统计信息367.3.2回滚段的当前活动事务367.3.3回滚段的数量规划367.4回滚段的问题及解决方法368备份及恢复378.1exp与imp378.1.1帮助语法378.1.2参数说明398.1.3应用举例40ORACLE数据库全技术1 数据库ORACLE在初始安装时建立一个缺省数据库，实例名为ORCL。每个数据库最少有一个控制文件（建议3个，分别放在不同的磁盘上）。o

5、racle server可以同时启动多个数据库。一套操作系统上只能安装一个版本的ORACLE数据库系统。一套ORACLE数据库系统中可以有多个ORACLE数据库。每个ORACLE数据库拥有一个数据库实例（INSTANCE）。所以，一套操作系统上同时可以有多个oracle数据库实例启动。1.1 参数在Oracle中，我们可以通过调整一些重要参数来提高数据库性能。这些配置参数都可以在init.ora文件中找到，或者我们也可以用Oracle Enterprise Manager来设置这些参数。我们可以结合运用Oracle Performance Manager、查询命令以及AUTOTRACE功能，辅

6、助优化这些配置参数。AUTOTRACE功能能够显示出系统如何解析SQL语句，并给出它的执行情况统计报告。执行下面这个命令将启动AUTOTRACE功能：SET AUTOTRACE ON如果要查看执行情况统计报告，但不想查看执行计划，我们执行如下命令：SET AUTOTRACE TRACEONLY参数作用备注DB_BLOCK_BUFFERS设置系统全局区（SGA，System Global Area）的大小。Oracle使用这个缓冲区保存和处理数据。SGA参数DB_BLOCK_SIZE设置内存块的大小。在Oracle 8或者更高版本中，它可以设置为32K；在早期版本中，最大限制是16K。警告：如果

7、改变这个数值，你必须重新构造数据库。SGA参数SHARED_POOL_SIZEOracle用这个内存处理过程、包、触发器。SGA参数SORT_AREA_SIZE这是用于排序的内存空间大小。1.2 启动有以下几种启动方式：1、startup nomount 非安装启动，这种方式启动下可执行：重建控制文件、重建数据库 读取init.ora文件，启动instance，即启动SGA和后台进程，这种启动只需要init.ora文件。2、startup mount dbname 安装启动，这种方式启动下可执行：数据库日志归档、数据库介质恢复、使数据文件联机或脱机、重新定位数据文件、重做日志文件。 执行“no

8、mount”，然后打开控制文件，确认数据文件和联机日志文件的位置，但此时不对数据文件和日志文件进行校验检查。3、startup open dbname 先执行“nomount”，然后执行“mount”，再打开包括Redo log文件在内的所有数据库文件，这种方式下可访问数据库中的数据。4、startup，等于以下三个命令 startup nomount alter database mount alter database open5、startup restrict 约束方式启动 这种方式能够启动数据库，但只允许具有一定特权的用户访问 非特权用户访问时，会出现以下提示： ERROR： ORA

9、-01035: ORACLE 只允许具有 RESTRICTED SESSION 权限的用户使用6、startup force 强制启动方式 当不能关闭数据库时，可以用startup force来完成数据库的关闭 先关闭数据库，再执行正常启动数据库命令7、startup pfile=参数文件名 带初始化参数文件的启动方式 先读取参数文件，再按参数文件中的设置启动数据库 例：startup pfile=E:Oracleadminoradbpfileinit.ora8、startup EXCLUSIVE1.3 关闭有三种关闭方式：1、shutdown normal 正常方式关闭数据库。2、shutd

10、own immediate 立即方式关闭数据库。 在SVRMGRL中执行shutdown immediate，数据库并不立即关闭，而是在Oracle执行某些清除工作后才关闭（终止会话、释放会话资源），当使用shutdown不能关闭数据库时，shutdown immediate可以完成数据库关闭的操作。3、shutdown abort 直接关闭数据库，正在访问数据库的会话会被突然终止， 如果数据库中有大量操作正在执行，这时执行shutdown abort后，重新启动数据库需要很长时间。1.4 日志文件1.5 SQL语句分类SQL：Structured Query LanguageDDL数据定义语

11、言：create、alter、drop（创建、修改结构、删除）（其他：rename）DML数据操纵语言：insert、delete、select、update（增、删、查、改）（其他：truncate）DCL数据控制语言：grant、revoke（授权、回收）、set role事务控制：commit、rollback、savepoint（其他：lock table、set constraint(s)、set transaction）审计控制：audit、noaudit系统控制：alter system会话控制：alter session其他语句：comment（添加注释）、explain pl

12、an、analyze、validate、call2 系统功能2.1 包Oracle提供了几个包，它们可以用来完成很多任务，从内部进程通信到文件I/O，到在PL/SQL块中动态创建和执行SQL语句。所有这些包由SYS用户所拥有当Oracle最初安装时两个用户中的一个，这些包中最重要的包括：DBMS_Alert不用轮询就允许应用命名并发出警告条件信号的过程与函数DBMS_DDL允许获取PL/SQL程序内部一定数量的DDL语句的过程DBMS_Describe为存储过程与函数描述API的过程DBMS_Job管理BLOBs、CLOBs、NCLOBs与BFILEs的过程与函数DBMS_Output允许PL

13、/SQL程序生成终端输出的过程与函数DBMS_Pipe允许数据库会话使用管道通信(通信频道)的过程与函数DBMS_SQL在PL/SQL程序内部执行动态SQL的过程与函数DBMS_UtilityDBMS_UtilityUTL_File允许PL/SQL程序读写服务器文件系统上的文本文件的过程与函数2.1.1 DBMS_Job包的用法2.1.1.1 包含以下子过程 Broken()过程。change()过程。Interval()过程。Isubmit()过程。Next_Date()过程。Remove()过程。Run()过程。Submit()过程。User_Export()过程。What()过程。2.1

14、.1.1.1 Broken()过程Broken()过程更新一个已提交的工作的状态，典型地是用来把一个已破工作标记为未破工作。这个过程有三个参数：job 、broken与next_date。PROCEDURE Broken (job IN binary_integer, Broken IN boolean, next_date IN date :=SYSDATE)job参数是工作号，它在问题中唯一标识工作。broken参数指示此工作是否将标记为破TRUE说明此工作将标记为破，而FLASE说明此工作将标记为未破。next_date参数指示在什么时候此工作将再次运行。此参数缺省值为当前日期和时间。2

15、.1.1.1.2 Change()过程Change()过程用来改变指定工作的设置。这个过程有四个参数：job、what 、next_date与interval。PROCEDURE Change (job IN binary_integer, What IN varchar2, next_date IN date, interval IN varchar2)此job参数是一个整数值，它唯一标识此工作。What参数是由此工作运行的一块PL/SQL代码块。next_date参数指示何时此工作将被执行。interval参数指示一个工作重执行的频度。2.1.1.1.3 Interval()过程Inter

16、val()过程用来显式地设置重执行一个工作之间的时间间隔数。这个过程有两个参数：job与interval。PROCEDURE Interval (job IN binary_integer, Interval IN varchar2)job参数标识一个特定的工作。interval参数指示一个工作重执行的频度。2.1.1.1.4 ISubmit()过程ISubmit()过程用来用特定的工作号提交一个工作。这个过程有五个参数：job、what、next_date、interval与no_parse。PROCEDURE ISubmit (job IN binary_ineger, What IN v

17、archar2, next_date IN date, interval IN varchar2, no_parse IN booean:=FALSE)这个过程与Submit()过程的唯一区别在于此job参数作为IN型参数传递且包括一个由开发者提供的工作号。如果提供的工作号已被使用，将产生一个错误。2.1.1.1.5 Next_Date()过程Next_Date()过程用来显式地设定一个工作的执行时间。这个过程接收两个参数：job与next_date。PROCEDURE Next_Date(job IN binary_ineger, next_date IN date)job标识一个已存在的工

18、作。next_date参数指示了此工作应被执行的日期与时间。2.1.1.1.6 Remove()过程Remove()过程来删除一个已计划运行的工作。这个过程接收一个参数：PROCEDURE Remove(job IN binary_ineger);job参数唯一地标识一个工作。这个参数的值是由为此工作调用Submit()过程返回的job参数的值。已正在运行的工作不能由调用过程序删除。2.1.1.1.7 Run()过程Run()过程用来立即执行一个指定的工作。这个过程只接收一个参数：PROCEDURE Run(job IN binary_ineger) job参数标识将被立即执行的工作。2.1.

19、1.1.8 Submit()过程使用Submit()过程，工作被正常地计划好。这个过程有五个参数：job、what、next_date、interval与no_parse。PROCEDURE Submit ( job OUT binary_ineger, What IN varchar2, next_date IN date, interval IN varchar2, no_parse IN booean:=FALSE)job参数是由Submit()过程返回的binary_ineger。这个值用来唯一标识一个工作。what参数是将被执行的PL/SQL代码块。next_date参数指识何时将运

20、行这个工作。interval参数何时这个工作将被重执行。no_parse参数指示此工作在提交时或执行时是否应进行语法分析TRUE指示此PL/SQL代码在它第一次执行时应进行语法分析，而FALSE指示本PL/SQL代码应立即进行语法分析。2.1.1.1.9 User_Export()过程User_Export()过程返回一个命令，此命令用来安排一个存在的工作以便此工作能重新提交。此程序有两个参数：job与my_call。PROCEDURE User_Export(job IN binary_ineger, my_call IN OUT varchar2)job参数标识一个安排了的工作。my_ca

21、ll参数包含在它的当前状态重新提交此工作所需要的正文。2.1.1.1.10 What()过程What()过程应许在工作执行时重新设置此正在运行的命令。这个过程接收两个参数：job与what。PROCEDURE What (job IN binary_ineger, What IN OUT varchar2)job参数标识一个存在的工作。what参数指示将被执行的新的PL/SQL代码。2.1.1.2 一个简单例子创建测试表SQL create table a(a date);表已创建。创建一个自定义过程SQL create or replace procedure test as 2 begin

22、 3 insert into a values(sysdate); 4 end; 5 /过程已创建。创建JOBSQL variable job1 number;SQL SQL begin 2 dbms_job.submit(:job1,test;,sysdate,sysdate+1/1440);-每天1440分钟，即一分钟运行test过程一次 3 end; 4 /PL/SQL 过程已成功完成。运行JOBSQL begin 2 dbms_job.run(:job1); 3 end; 4 /PL/SQL 过程已成功完成。SQL select to_char(a,yyyy/mm/dd hh24:mi

23、:ss) 时间 from a;时间-2001/01/07 23:51:212001/01/07 23:52:222001/01/07 23:53:24删除JOBSQL begin 2 dbms_job.remove(:job1); 3 end; 4 /PL/SQL 过程已成功完成2.2 系统数据字典以下表格中收集了Oracle数据字典中几乎所有的视图或别名，至于每个视图或别名的字段请用Describe语句来查询。命名说明：Oracle数据字典中，对象名称多数以USER.，ALL.，DBA.为前缀USER.视图中记录通常记录执行查询的帐户所拥有的对象的信息，ALL.视图中记录包括USER记录和授

24、权至PUBLIC或用户的对象的信息，DBA.视图包含所有数据库对象，而不管其所有者。使用举例：select * from dba_data_files查询表空间的信息(当前用户必须拥有DBA角色)。select owner , object_name ,object_type from all_objects查询某一用户下的所有表、过程、函数等信息。视图名 版本描述 ALL_CATALOGAll tables, views, synonyms, sequences accessible to the user ALL_COL_COMMENTSComments on columns of acc

25、essible tables and views ALL_COL_GRANTS_MADEGrants on columns for which the user is owner or grantorALL_COL_GRANTS_RECDGrants on columns for which the user or PUBLIC is the grantee ALL_COL_PRIVSGrants on columns for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the g

26、rantee ALL_COL_PRIVS_MADEGrants on columns for which the user is owner or grantorALL_COL_PRIVS_RECDGrants on columns for which the user, PUBLIC or enabled role is the grantee ALL_CONSTRAINTSConstraint definitions on accessible tables ALL_CONS_COLUMNSInformation about accessible columns in constraint

27、 definitions ALL_DB_LINKSDatabase links accessible to the user ALL_DEF_AUDIT_OPTSAuditing options for newly created objects ALL_DEPENDENCIESDependencies to and from objects accessible to the user ALL_ERRORSCurrent errors on stored objects that user is allowed to create ALL_INDEXESDescriptions of ind

28、exes on tables accessible to the user ALL_IND_COLUMNSCOLUMNs comprising INDEXes on accessible TABLES ALL_OBJECTSObjects accessible to the user ALL_REFRESHAll the refresh groups that the user can touch ALL_REFRESH_CHILDRENAll the objects in refresh groups, where the user can touch the group ALL_SEQUE

29、NCESDescription of SEQUENCEs accessible to the user ALL_SNAPSHOTSSnapshots the user can look at ALL_SOURCECurrent source on stored objects that user is allowed to create ALL_SYNONYMSAll synonyms accessible to the user ALL_TABLESDescription of tables accessible to the user ALL_TAB_COLUMNSColumns of a

30、ll tables, views and clusters ALL_TAB_COMMENTSComments on tables and views accessible to the user ALL_TAB_GRANTS_MADEUsers grants and grants on users objects ALL_TAB_GRANTS_RECDGrants on objects for which the user or PUBLIC is the grantee ALL_TAB_PRIVSGrants on objects for which the user is the gran

31、tor, grantee, owner, or an enabled role or PUBLIC is the grantee ALL_TAB_PRIVS_MADEUsers grants and grants on users objects ALL_TAB_PRIVS_RECDGrants on objects for which the user, PUBLIC or enabled role is the grantee ALL_TRIGGERSTriggers accessible to the current user ALL_TRIGGER_COLSColumn usage i

32、n users triggers or in triggers on users tables ALL_USERSInformation about all users of the database ALL_VIEWSText of views accessible to the user AUDIT_ACTIONSDescription table for audit trail action type codes. Maps action type numbers to action type names COLUMN_PRIVILEGESGrants on columns for wh

33、ich the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee DICTIONARYDescription of data dictionary tables and views DICT_COLUMNSDescription of columns in data dictionary tables and views DUAL全部GLOBAL_NAMEglobal database name INDEX_HISTOGRAMstatistics on keys with repea

34、t count INDEX_STATSstatistics on the b-tree RESOURCE_COSTCost for each resource ROLE_ROLE_PRIVSRoles which are granted to roles ROLE_SYS_PRIVSSystem privileges granted to roles ROLE_TAB_PRIVSTable privileges granted to roles SESSION_PRIVSPrivileges which the user currently has set SESSION_ROLESRoles

35、 which the user currently has enabled. SYSTEM_PRIVILEGE_MAPPrivileges which can be granted to user.TABLE_PRIVILEGESGrants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee USER_AUDIT_CONNECTAudit trail entries for user logons/logoffs USER_AUDIT

36、_OBJECTAudit trail records for statements concerning objects, specifically: table, cluster, view, index, sequence, public database link, public synonym, procedure, trigger, rollback segment, tablespace, role, user USER_AUDIT_SESSIONUSER_AUDIT_STATEMENTAudit trail records concerning grant, revoke, au

37、dit, noaudit and alter system USER_AUDIT_TRAILAudit trail entries relevant to the user USER_CATALOGTables, Views, Synonyms and Sequences owned by the user USER_CLUSTERSDescriptions of users own clusters USER_CLU_COLUMNSMapping of table columns to cluster columns USER_COL_COMMENTSComments on columns

38、of users tables and views USER_COL_GRANTSGrants on columns for which the user is the owner, grantor or grantee USER_COL_GRANTS_MADEAll grants on columns of objects owned by the user USER_COL_GRANTS_RECDGrants on columns for which the user is the grantee USER_COL_PRIVSGrants on columns for which the

39、user is the owner, grantor or grantee USER_COL_PRIVS_MADEAll grants on columns of objects owned by the user USER_COL_PRIVS_RECDGrants on columns for which the user is the grantee USER_CONSTRAINTSConstraint definitions on users own tables USER_CONS_COLUMNSInformation about accessible columns in const

40、raint definitions USER_CROSS_REFSCross references for users views and synonyms USER_DB_LINKSDatabase links owned by the user USER_DEPENDENCIESDependencies to and from a users objects USER_ERRORSCurrent errors on stored objects owned by the user USER_EXTENTSExtents comprising segments owned by the us

41、er USER_FREE_SPACEFree extents in tablespaces accessible to the user USER_INDEXESDescription of the users own indexes USER_IND_COLUMNSCOLUMNs comprising users INDEXes or on users TABLESUSER_JOBSAll jobs owned by this user USER_OBJECTSObjects owned by the user USER_OBJECT_SIZESizes, in bytes, of vari

42、ous pl/sql objects USER_OBJ_AUDIT_OPTSAuditing options for users own tables and views USER_REFRESHAll the refresh groups USER_REFRESH_CHILDRENAll the objects in refresh groups, where the user owns the refresh group USER_RESOURCE_LIMITSDisplay resource limit of the user USER_ROLE_PRIVSRoles granted to current user USER_SEGMENTSStorage allocated for all database segments USER_SEQUENCESDescription of the users own SEQUENCEs USER_SNAPSHOTSSnapshots the user can look at USER_SNAPSHOT_LOGSAll snapshot logs owned by the u