oracle数据库基本操作.doc

《oracle数据库基本操作.doc》由会员分享，可在线阅读，更多相关《oracle数据库基本操作.doc（30页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、实战经验Oracle数据库基本操作步步详解来源：2007年06月12日 15:34网友评论：0条点击： 274一，约束操作1:更改约束名称：ALTER TABLE TName RENAME CONSTRAINT oldname TO newname;2:删除约束ALTER TABLE TName DROP CONSTRAINT cname3：停止约束ALTER TABLE TName MODIFY CONSTRAINT cname DISABLE;4：起用约束ALTER TABLE TName MODIFY CONSTRAINT cname ENABLE VALIDATE;5：新增约束ALTE

2、R TABLE TName ADD CONSTRAINT cname FOREIGN KEY (ORG_ID) REFERENCES ref_table (ORGID);二，字段操作1：更改字段数据类型：ALTER TABLE TName MODIFY(ORG_ID VARCHAR2(50 BYTE);2：更改字段数据长度：ALTER TABLE TName MODIFY(ORG_ID VARCHAR2(80 BYTE);3：修改表的列名alter table TName rename column xx to yy;三，表操作1：删除表数据：truncate table TName;2:复制

3、空表结构create table new_table as select * from old_table where 1=2;3：复制表(含记录)create table new_table as select * from old_table ;四，存储过程1：命令行编译存储过程ALTER PROCEDURE procedure_name COMPILE;五，数据库链接(DBlink)1：建立数据库连接CREATE DATABASE LINK DBaseLinkName CONNECT TO UserName IDENTIFIED BY Password USING NetServiceN

4、ame;六,Oracle服务器常用命令行（1）数据库管理相关bbed.exe 数据块浏览和编辑工具dbv.exe 数据文件逻辑检测工具exp.exe 数据导出工具imp.exe 数据导入工具mig.exe 数据库移植工具nmumigrate.exe Data Gatherer移植工具ocopy.exe 裸分区数据文件备份工具oradim.exe Oracle服务创建、删除工具orakill.exe Oracle线程删除工具orapwd.exe Oracle密码文件创建工具orastack.exe Oracle进程堆栈修改工具oratclsh.exe TCL脚本解释器otrccol.exe tr

5、ace命令行服务管理工具otrccref.exe trace管理文件生成工具otrcfmt.exe trace数据格式化工具otrcrep.exe trace数据统计报表生成工具rman.exe 备份与恢复工具setlinks.exe 磁盘分区管理工具sqlldr.exe sql Load数据批量导入工具sqlplus.exe 命令行的sqlplussvrmgrl.exe Oracle实例管理工具tkprof.exe SQL语句跟踪文件解析工具vppcntl.exe Data Gatherer服务管理工具（2）Internet Directory相关bulkload.exe 使用SQL*Loa

6、der生成目录对象的工具bulkdelete.exe 子树删除工具bulkmodify.exe 目录对象修改工具ldifwrite.exe 将目录中的信息转换成LDIF格式ldapadd.exe 向目录中增加目录对象、属性、值的工具ldapaddmt.exe ldapadd的多线程版本ldapbind.exe 客户端认证工具ldapcompare.exe 属性值比较工具ldapdelete.exe 将目录对象从目录中删除的工具ldapmoddn.exe 修改DN和RDN的工具ldapmodify.exe 属性修改工具ldapmodifymt.exe ldapmodify的多线程版本ldapse

7、arch.exe 查找和获取目录中目录对象工具oidpasswd.exe Internet Directory密码管理工具oidreconcile.exe 目录对象同步工具（3）NLS相关csscan.exe 数据库字符集转换测试工具lxbcnf.exe NLS配置工具lxegen.exe 自定义日期格式生成工具lxinst.exe 自定义字符集生成工具（4）Net8相关agtctl.exe 异构服务代理控制程序cmctl.exe 连接服务管理工具lsnrctl.exe 监听服务管理工具naegen.exe 哈夫曼参数生成工具namesctl.exe 名字服务管理工具okdstry.exe 删

8、除Kerberos证书工具okinit.exe 创建Kerberos证书工具oklist.exe 检查Kerberos证书工具tnsping.exe 服务名连接测试工具trcasst.exe SQL*Net跟踪文件解析工具（5）interMedia相关ctxhx.exe ConText文本转换工具ctxkbtc.exe ConText词典编译工具ctxload.exe ConText词典导入/导出工具（6）开发相关lmsgen.exe OCI使用的消息文件转换工具loadpsp.exe PL/SQL Server Page装载器ott.exe Oracle对象类型转换工具sqlj.exe sq

9、lj翻译器wrap.exe 存储过程加密工具xml.exe XML解析器xmlcg.exe XML C+ Class 生成器七，Soralis环境下Oracle管理1：启动数据库管理命令：dbca八，Oracle性能优化1：ORACLE的优化器共有3种: a.RULE (基于规则) b. COST (基于成本)c. CHOOSE (选择性)2.ORACLE 采用两种访问表中记录的方式:a.全表扫描 全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描.b.通过ROWID访问表采用基于ROWID的访问方式情况,提高访问表的效率

10、, , ROWID包含了表中记录的物理位置信息.ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高.3：选择最有效率的表名顺序(只在基于规则的优化器中有效) ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子

11、句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并. 如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.5. WHERE子句中的连接顺序 ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.九，Oracle数据库重建1:数据库重建应注意的问题在利用import进行数据库重建过程中,有些视图可能会带

12、来问题,因为结构输入的顺序可能造成视图的输入先于它低层次表的输入,这样建立视图就会失败.要解决这一问题,可采取分两步走的方法:首先输入结构,然后输入数据:imp user/pwddabaseSID file=empdata.dmp rows=Nimp user/pwddabaseSID file=empdata.dmp full=Y buffer=64000commit=Y ignore=Y2:快速编译所有视图- 当在把数据库倒入到新的服务器上后(数据库重建)，需要将视图重新编译一遍，因为该表空间视图到其它表空间的表的连接会出现问题，可以利用PL/SQL的语言特性，快速编译。SQL SPOOL

13、 ON.SQLSQL SELECT ALTER VIEW |TNAME|COMPILE; FROM TAB;SQL SPOOL OFF然后执行ON.SQL即可。SQL ON.SQL授权和创建同义词也可以快速进行：SQL SELECT GRANT SELECT ON |TNAME| TO USERNAME; FROM TAB;SQL SELECT CREATE SYNONYM|TNAME| FOR USERNAME.|TNAME|; FROM TAB;第一条命令输入所有数据库结构,但无记录.第二次输入结构和数据,64000字节提交一次.ignore=Y选项保证第二次输入既使对象存在的情况下也能成

14、功.Oracle数据库操作常见错误及解决方案2009-03-09 17:56ORA-01650:unable to extend rollback segment NAME by NUM intablespace NAME产生原因：上述ORACLE错误为回滚段表空间不足引起的，这也是ORACLE数据管理员最常见的ORACLE错误信息。当用户在做一个非常庞大的数据操作导致现有回滚段的不足，使可分配用的回滚段表空间已满，无法再进行分配，就会出现上述的错误。解决方式：使用“ALTER TABLESPACE tablespace_name ADD DATAFILE filename SIZE size

15、_of_file”命令向指定的数据增加表空间，根据具体的情况可以增加一个或多个表空间。当然这与还与你主机上的裸盘设备有关，如果你主机的裸盘设备已经没有多余的使用空间，建议你不要轻意的增加回滚段表空间的大小，可使用下列的语句先查询一下剩余的tablespace空间有多少：Select user_name,sql_text from V$open_cursor where user_name=;如果多余的空间比较多，就可以适当追加一个大的回滚段给表空间使用，从而避免上述的错误。你也可以用以下语句来检测一下rollback segment的竞争状况：Select class,count from V

16、$waitstat where calss in(system undo header,system undo block,undo header,undo block);和Select sum(value) from V$sysstat where name in (db_block_gets,consistents gets);如果任何一个class in count/sum(value)大于1%，就应该考虑增加rollback segment。ORA-01652:unable to extend temp segment by num in tablespace name产生原因：ORA

17、CLE临时段表空间不足，因为ORACLE总是尽量分配连续空间，一但没有足够的可分配空间或者分配不连续就会出现上述的现象。解决方法：我们知道由于ORACLE将表空间作为逻辑结构-单元，而表空间的物理结构是数据文件，数据文件在磁盘上物理地创建，表空间的所有对象也存在于磁盘上，为了给表空间增加空间，就必须增加数据文件。先查看一下指定表空间的可用空间，使用视图SYS.DBA_FREE_SPACE，视图中每条记录代表可用空间的碎片大小：SQLSelect file_id,block_id,blocks,bytes from sys.dba_free_space where tablespace_name

18、=;返回的信息可初步确定可用空间的最大块，看一下它是否小于错误信息中提到的尺寸，再查看一下缺省的表空间参数：SQLSELECT INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,PCT_INCREASE FROM SYS.DBA_TABLESPACES WHERE TABLESPACE_NAME=name;通过下面的SQL命令修改临时段表空间的缺省存储值：SQLALTER TABLESPACE name DEFAULT STORAGE (INITIAL XXX NEXT YYY);适当增大缺省值的大小有可能解决出现的错误问题，也可以通过修改用户的临时表空间大小来解决

19、这个问题：SQLALTER USER username TEMPORARY TABLESPACE new_tablespace_name;使用ALTER TABLESPACE命令，一但完成，所增加的空间就可使用，无需退出数据库或使表空间脱机，但要注意，一旦添加了数据文件，就不能再删除它，若要删除，就要删除表空间。一个报错例子如下：ORA-1652:unable to extend temp segment by 207381 in tablespace TEMPSPACEORA-01578:Oracle data block corrupted(file # num,block # num)产

20、生原因：当ORACLE访问一个数据块时，由于：1、硬件的I/O错误；2、操作系统的I/O错误或缓冲问题；3、内存或paging问题；4、ORACLE试图访问一个未被格式化的系统块失败；5、数据文件部分溢出等上述几种情况的一种引起了逻辑坏块或者物理坏块，这时就会报ORA-01578的错误。解决方式：由于ORACLE只有在访问到有问题的数据文件时才会报错，所以报错的时间有可能会比实际出错的时间要晚，如果ORA-01578出错信息提示数据坏块指向的是用户自己的数据文件，则用以下方法来解决： 如果通过下面的SQL语句查出的坏块出现有索引上，则只需重建索引即可 SQLSelect owner,segme

21、nt_name,segment_type from dba_extents where file_id= and between block_id and block_id+blocks-1;(和分别是ORA-01578报出的坏块出现的文件号和块号)如果坏块出现在表上，先用以下语句分析是否为永久性坏块（建议多执行一两次，有助于鉴别数据坏块是永久性的（硬盘上的物理坏块）还是随机性的（内存或硬件错误引起）： SQLAnalyze table validate structure cascade;执行该命令后，可能会出现以下的结果：ORA-01578：与原先错误信息有相同的参数，为永久性的物理或逻辑

22、坏块；与原先错误信息有不同的参数，可能与内存，page space和I/O设备有关。如果用户有此表的最新备份，那么最好是用此备份来恢复此表，或者使用event 10231来取出坏块以外的数据：.先关闭数据库.编辑init.ora文件，加入：event=”10231 trace name context forever,level 10”.startup restrict.创建一个临时表：SQLcreate table errortemp as select * from error;(error是坏表的表名).把event从init.ora文件中删掉并重起数据库.rename坏表，把临时表re

23、name成坏表的表名.创建表上的INDEX等如果ORA-01578出错信息提示数据坏块指向的是数据字典或者是回滚段的话，你应该立即与ORACLE公司联系，共同商量一个好的解决办法。这里所讲的解决方法只是比较常见的一种，一些更为具体的解决办法可以查看一下ORACLE的故障解决手册，那里面有浞及使用ROWID方法来取出坏块以外的数据的方法，这里就不介绍了。ORA-01628:max # of extents num reached for rollback segment num产生原因：这种错误通常为一个回滚段和一个表空间已经达到MAXEXTENTS参数设置的极限。要注意的是这个MAXEXTEN

24、TS不是该回滚段或表空间的硬件极限，硬件极限取决于数据库创建时在init.ora文件中指定的DB_BLOCK_SIZE参数的值。解决方法：使用SQL命令ALTER TABLESPACESTORAGE（MAXEXTENTS XXXX）来增加 MAXEXTENTS，其中“XXXX”值必须大于错误信息中所指的数值，但不能大于LARGEST MAXEXTENT的值，如果已经达到了LARGEST MAXEXTENT VALUE，解决的办法就是重新创建较大的范围尺寸，使用带有选项COMPRESS=Y的Export工具导出表，如果表空间有可用空间，先给表做一个备份，用alter tablespace tab

25、lespace_name更改其名字，然后再装载表回数据库。查看其错误出现的地方，如果出现在回滚段或索引上，那么必须将其删除并重建，如果出现在临时表空间，修改临时表空间的存储字段，便可解决这个问题。一个报错例子如下：ORA-1628:max # extents 50 reached for rollback segment RBS_1ORA-00600:internal error code,arguments:num,?,?,?,?产生原因：这种错误通常为ORACLE的内部错误，只对OSS和ORACLE开发有用。ORA-600的错误经常伴随跟踪文件的状态转储（系统状态和进程状态），系统状态存储

26、将包括ORACLE RDBMS持有的当前对象的信息，进程状态转储则将显示特殊进程持有的对象，当进程符合了某错误条件时，经常是由于一些信息取自它持有的一个块，如果我们知道这些错误进程持有的块，就容易跟踪问题的来源。解决方法：一般来说出现这个错误我们本身是无法解决的，只有从提高系统本身各方面来解决这个内部问题，如增加硬件设备，调整系统性能，使用OPS（当然OPS从某种意义上说并不是一种好的解决方式）等。ORA-600错误的第一个变量用于标记代码中错误的位置（代码中的每个部分的第一变量都不一样），从第二个到第五个变量显示附加信息，告诉OSS代码在哪里出现了错误。一个报错例子如下：ORA-00600:

27、 internal error code, arguments: 1237, , , , , , , ORA-03113：end-of-file on communication channel产生原因：通讯不正常结束，从而导致通讯通道终止解决方法：1.检查是否有服进程不正常死机，可从alert.log得知2.检查sql*Net Driver是否连接到ORACLE可执行程序3.检查服务器网络是否正常，如网络不通或不稳定等 4.检查同一个网上是否有两个同样名字的节点5.检查同一个网上是否有重复的IP地址ORA-00942:table or view does not exist产生原因：这是由于

28、装载的表或视图不存在，多半是CATEXP.SQL还没有运行，无法执行Export视图,如果CATEXP.SQL已经运行，则可能是版本错误。解决方法：因为Import和Export共享的一些视图是通过运行CATEXP.SQL来装载的（它们具有相同的视图），并不生成单独的CATEXP.SQL，因而造成视图与Export代码不同步，较难保持彼此之间的兼容，用户就必须建立自己的Export应用，从而避免ORA-00942的错误。上述错误均为我们在使用回滚段时比较常见的问题，ORA-01598指明当前使用的回滚段的状态为“not online”，不能使用，将它改为“online”状态即可使用；ORA-0

29、1636指明当前回滚段已经为“online”状态，可以直接使用，不用再集合它。ORA-1636 signalled during: alter rollback segment rb00 online我们在做统计时还可能遇到下述问题：一个rollback segment的状态为”Needs Recovery”的现象，这是由于ORACLE回退一个事物表中的没有提交的事物时失败所造成的。通常原因为一个datafile或者tablespace是在offline的状态或者一个undo的目标被破坏或者rollback segment被破坏。解决的办法是将所有的tablespace和datafile都置为

30、online状态，如果不能解决则做下面的工作：1.在initsid.ora中加入event=”10015 trace name context forever lever 10”;2.shutdown数据库然后重启；3.在$ORACLE_HOME/rdbms/log下，找到startup时生成的trace file;4.在trace文件中，找到下列信息“error recovery tx(#,#) object #”;5.根据object#(与sys.dba_objects表中的object_id相同)在sys.dba_objects表中查出该object的名字；6.将该object drop

31、掉;7.在init.ora文件中将该rollback segment放回rollback_segments参数中，删除event;8.shutdown数据库然后重启。此时”Needs Recovery”的问题应该是完全解决了，否则就是rollback segment被破坏了。ORA-01688:unable to extend table name.name partition NAME by NUM in tablespace NAME产生原因：指定的tablespace空间已经被占用满，无法扩展。解决方法：使用“ALTER TABLESPACE ADD DATAFILE”命令增加文件系统文

32、件和原始分区，或者增加INITIAL的大小（如：alter tablespace CDRS101 default storage(next 500M pctincrease 1)）应该能够解决，否则就是有人使用你的表空间上创建了一个比较大的数据文件导致你的表空间不够用。一个报错例子如下：ORA-1688: unable to extend table RMMCDR.LOCAL_CDR partition LOCAL_CDR101 by 460800 in tablespace CDRS101Oracle存储过程学习2009-08-26 16:14Oracle存储过程包含三部分：过程声明，执行过

33、程部分，存储过程异常。Oracle存储过程可以有无参数存储过程和带参数存储过程。 一、无参程序过程语法1 create or replace procedure NoParPro2 as ;3 begin4 ;5 exception /存储过程异常6 ;7 end;8 二、带参存储过程实例1 create or replace procedure queryempname(sfindno emp.empno%type) as2 sName emp.ename%type;3 sjob emp.job%type;4 begin5 .7 exception .14 end;15 三、 带参数存储过程

34、含赋值方式 1 create or replace procedure runbyparmeters (isal in emp.sal%type, sname out varchar,sjob in out varchar)2 as icount number;3 begin4 select count(*) into icount from emp where salisal and job=sjob;5 if icount=1 then6 .9 else10 .12 end if;13 exception14 when too_many_rows then15 DBMS_OUTPUT.PU

35、T_LINE(返回值多于1行);16 when others then17 DBMS_OUTPUT.PUT_LINE(在RUNBYPARMETERS过程中出错！);18 end;19 四、在Oracle中对存储过程的调用过程调用方式一1 declare2 realsal emp.sal%type;3 realname varchar(40);4 realjob varchar(40);5 begin /存储过程调用开始6 realsal:=1100;7 realname:=;8 realjob:=CLERK;9 runbyparmeters(realsal,realname,realjob);

36、 必须按顺序10 DBMS_OUTPUT.PUT_LINE(REALNAME| |REALJOB);11 END; /过程调用结束12 过程调用方式二1 declare2 realsal emp.sal%type;3 realname varchar(40);4 realjob varchar(40);5 begin /过程调用开始6 realsal:=1100;7 realname:=;8 realjob:=CLERK;9 runbyparmeters(sname=realname,isal=realsal,sjob=realjob); 指定值对应变量顺序可变10 DBMS_OUTPUT.P

37、UT_LINE(REALNAME| |REALJOB);11 END; /过程调用结束12至此，有关ORACLE的基本存储过程以及对Oracle存储过程的调用方式介绍完毕。30 毕 绍介据的储 本 过存 详详 调/ | _ 变量应定; = , : ; 操 点条论友 . . : 调/ 作 表改;) 0( ：段改 ) 0( ( 型据字：操段 ) 束新 束起 止： 约: ：束更 )( 删 除 据 ) ( % 式用过 _ _ 录含复 * 结空 ;过 束 用/命 ;译 过| . 序必 ; ; = ;= 0=: 据 (始程存 ; 据立服 0( 命 行 ） 相相数 块方调过程.工辑浏存中 在 出过 工导数

38、工检逻文 在(数 导. 工 行多返 _ 工植据 . .工植. 0 区. 工份文 ; 服 、 除 工 ( ; 删 工 % . .( 件 赋工工过储参三 ; . . % ; . 工修栈 本 释 过参二 程存/ 工成理 工管行令 ; ; 工化数 语语参 一 储数数程存计无以过生 常工异存部过明过分包储 -学程储 0 0 0 _ . : 下错个。间表致件数 工工备 比了上表 使是 则解能应 区 理 工 0 如大 者分原统系令 用：解展无用被空 定：生 - 了被 就否 解全应 的 ” 。启库据 导 工 删参 件 . 名 出表 行 _ 在 中 实 与 工工据; ,( “列 到文语 在踪 解 工生 到， /

39、 $重然 工理 . 0 . .相相：的下决果态 为 将办的破 破标的 或状 是 或 因常成所时事没的事一 是这的 状 题述能时统我 0 合用，直可 工象录生 ” 为滚 当明 0；可子状 除为工改用， “态段的当 题见段用使均述。误 0 而用 建必用兼此持，同代 与视因， 的单并图视相（的 . 过视的 和 因决。本版则经 图视 ，行 . 半存图的装由原 0. 地 否录是同.工工节名个是上查.等不不如常络务服 序执 连是 *检 知 可常程 服是 法解 终通将通，常讯的因生转 格 ： - : 中 目 :、 属 下子个 工值。误里哪 诉.信示 变第到 从）都变的 的 码置错中记版版用一错0 等式解种是

40、义某从当 使性统备设增问内解方各高从，决身们个出般：解源题问就的 程错这我， 个持取 息于户常件认误合工进，对进殊将转状，的的持 将状，状进统（状文跟常的0 用开 只，的 常误因生?, , 00-_ 0 -值 ：较如工错题决便字存 空改修 时临如重 除须必 引或在出对方象现错查录库回装然 工的名其 _ 改用备做和给间可 表果工导 的= 项带，围的建是的， 了经果的 于能，指中错须” “ 加） （ 用法方值参 _ 的件 在库于极硬件间或滚不 个意要的数 达空一回为误错因 修 -工了了里法的外坏来法 用浞那手解 一法解具更种见常法解所。的一同 系联 立 应，回或.据数向 据示出 0 如 的. 版线

41、 表 时， 工理码 工工中目获 . 工同录 . 相 名表是 ：临建 库关. 起错硬（性 还.坏的上的 永坏鉴义于两执格议（永成是工句下，在块果号块的现出 0 别 和- _ _ 可索需，上出坏句语面通 法方用据的 工成符定 用向坏相示信 晚时出比可有错所会件文问问有 于方决错 会这 ，物坏了 种的几等服出务件数；块的程程被一图 、； 存内；问错 统操 的件 工理务 ： ，块一.访 原 # 工理听 曼 成0工 .0 服 管 工: ：例报一 。表 要除.删，再 就文加一 注， 机表据 退，可证就增工成一命 使 _ ：问解小空的用通可问的决可大值增适) .( 创 值省缺段 工证时令 .面通 _ 工工 查检 务. 连 测 工_