ORACLE数据库全技术

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 t

43、o 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 user user_sourcesource of stored objects accessible to the user user_synonymsthe

44、users private synonyms user_sys_privssystem privileges granted to current user user_tablesdescription of the users own tables user_tablespacesdescription of accessible tablespaces user_tab_audit_optsauditing options for users own tables and views user_tab_columnscolumns of users tables, views and cl

45、usters user_tab_commentscomments on the tables and views owned by the user user_tab_grantsgrants on objects for which the user is the owner, grantor or grantee user_tab_grants_madeall grants on objects owned by the user user_tab_grants_recdgrants on objects for which the user is the grantee user_tab

46、_privsgrants on objects for which the user is the owner, grantor or grantee user_tab_privs_madeall grants on objects owned by the user user_tab_privs_recdgrants on objects for which the user is the grantee user_triggerstriggers owned by the user user_trigger_colscolumn usage in users triggers user_t

47、s_quotastablespace quotas for the user user_usersinformation about the current user user_viewstext of views owned by the user accessible_columnssynonym for all_tab_columns all_col_grantssynonym for column_privileges all_jobssynonym for user_jobs all_tab_grantssynonym for table_privileges catsynonym

48、for user_catalog clu synonym for user_clusters colssynonym for user_tab_columns dba_audit_connect synonym for user_audit_connect dba_audit_resourcesynonym for user_audit_resource dba_refresh_childrensynonym for user_refresh_children dictsynonym for dictionary indsynonym for user_indexes objsynonym f

49、or user_objects seqsynonym for user_sequences sm$versionsynonym for sm_$version synsynonym for user_synonyms tabssynonym for user_tables v$accesssynonym for v_$access v$archivesynonym for v_$archive v$backupsynonym for v_$backup v$bgprocesssynonym for v_$bgprocess v$circuitsynonym for v_$circuit v$c

50、ompatibilitysynonym for v_$compatibility v$compatsegsynonym for v_$compatseg v$controlfilesynonym for v_$controlfile v$databasesynonym for v_$database v$datafilesynonym for v_$datafile v$dbfilesynonym for v_$dbfile v$dblinksynonym for v_$dblink v$db_object_cachesynonym for v_$db_object_cache v$dispa

51、tchersynonym for v_$dispatcher v$enabledprivssynonym for v_$enabledprivs v$filestatsynonym for v_$filestat v$fixed_tablesynonym for v_$fixed_table v$latchsynonym for v_$latch v$latchholdersynonym for v_$latchholder v$latchnamesynonym for v_$latchname v$librarycachesynonym for v_$librarycache v$licensesynonym for v_$license v$loadcstatsynonym for v_$loadcstat v$loadtstatsynonym for v_$loadtstat v$locksynonym for v_$lock v$logsynonym for