oracle数据库基本操作.docx

oracle数据库基本操作.docx

《oracle数据库基本操作.docx》由会员分享，可在线阅读，更多相关《oracle数据库基本操作.docx（9页珍藏版）》请在冰豆网上搜索。

oracle数据库基本操作

实战经验Oracle数据库基本操作步步详解

来源：

　2007年06月12日15:

34　网友评论：

0条　点击：

274

一，约束操作1:

更改约束名称：

ALTERTABLETNameRENAMECONSTRAINToldnameTOnewname;2:

删除约束ALTERTABLETNameDROPCONSTRAINTcname3：

停止约束ALTERTABLETNameMODIFYCONSTRAINTcnameDISABLE;4：

起用约束ALTERTABLETNameMODIFYCONSTRAINTcnameENABLEVALIDATE;5：

新增约束ALTERTABLETNameADDCONSTRAINTcnameFOREIGNKEY（ORG_ID）REFERENCESref_table（ORGID）;二，字段操作1：

更改字段数据类型：

ALTERTABLETNameMODIFY（ORG_IDVARCHAR2（50BYTE））;2：

更改字段数据长度：

ALTERTABLETNameMODIFY（ORG_IDVARCHAR2（80BYTE））;3：

修改表的列名altertableTNamerenamecolumnxxtoyy;

三，表操作1：

删除表数据：

truncatetableTName;2:

复制空表结构createtablenew_tableasselect*fromold_tablewhere1=2;3：

复制表（含记录）createtablenew_tableasselect*fromold_table;

四，存储过程1：

命令行编译存储过程ALTERPROCEDUREprocedure_nameCOMPILE;

五，数据库链接（DBlink）1：

建立数据库连接CREATEDATABASELINKDBaseLinkNameCONNECTTOUserNameIDENTIFIEDBYPasswordUSING'NetServiceName';

六,Oracle服务器常用命令行

（1）数据库管理相关

数据块浏览和编辑工具

数据文件逻辑检测工具

数据导出工具

数据导入工具

数据库移植工具

DataGatherer移植工具

裸分区数据文件备份工具

Oracle服务创建、删除工具

Oracle线程删除工具

Oracle密码文件创建工具

Oracle进程堆栈修改工具

TCL脚本解释器

trace命令行服务管理工具

trace管理文件生成工具

trace数据格式化工具

trace数据统计报表生成工具

备份与恢复工具

磁盘分区管理工具

sqlLoad数据批量导入工具

命令行的sqlplus

Oracle实例管理工具

SQL语句跟踪文件解析工具

DataGatherer服务管理工具

（2）InternetDirectory相关

使用SQL*Loader生成目录对象的工具

子树删除工具

目录对象修改工具

将目录中的信息转换成LDIF格式

向目录中增加目录对象、属性、值的工具

ldapadd的多线程版本

客户端认证工具

属性值比较工具

将目录对象从目录中删除的工具

修改DN和RDN的工具

属性修改工具

ldapmodify的多线程版本

查找和获取目录中目录对象工具

InternetDirectory密码管理工具

目录对象同步工具

（3）NLS相关

数据库字符集转换测试工具

NLS配置工具

自定义日期格式生成工具

自定义字符集生成工具

（4）Net8相关

异构服务代理控制程序

连接服务管理工具

监听服务管理工具

哈夫曼参数生成工具

名字服务管理工具

删除Kerberos证书工具

创建Kerberos证书工具

检查Kerberos证书工具

服务名连接测试工具

SQL*Net跟踪文件解析工具

（5）interMedia相关

ConText文本转换工具

ConText词典编译工具

ConText词典导入/导出工具

（6）开发相关

OCI使用的消息文件转换工具

PL/SQLServerPage装载器

Oracle对象类型转换工具

sqlj翻译器

存储过程加密工具

XML解析器

XMLC++Class生成器

七，Soralis环境下Oracle管理1：

启动数据库管理命令：

dbca

八，Oracle性能优化1：

ORACLE的优化器共有3种:

　a.　RULE（基于规则）　b.COST（基于成本）　c.CHOOSE（选择性）　采用两种访问表中记录的方式:

a.全表扫描全表扫描就是顺序地访问表中每条记录.ORACLE采用一次读入多个数据块（databaseblock）的方式优化全表扫描.b.通过ROWID访问表采用基于ROWID的访问方式情况,提高访问表的效率,,ROWID包含了表中记录的物理位置信息..ORACLE采用索引（INDEX）实现了数据和存放数据的物理位置（ROWID）之间的联系.通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高.3：

选择最有效率的表名顺序（只在基于规则的优化器中有效）   ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表（基础表drivingtable）将被最先处理.在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时,会运用排序及合并的方式连接它们.首先,扫描第一个表（FROM子句中最后的那个表）并对记录进行派序,然后扫描第二个表（FROM子句中最后第二个表）,最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.   如果有3个以上的表连接查询,那就需要选择交叉表（intersectiontable）作为基础表,交叉表是指那个被其他表所引用的表.5.WHERE子句中的连接顺序．   ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.

九，Oracle数据库重建1:

数据库重建应注意的问题　　在利用import进行数据库重建过程中,有些视图可能会带来问题,因为结构输入的顺序可能造成视图的输入先于它低层次表的输入,这样建立视图就会失败.要解决这一问题,可采取分两步走的方法:

首先输入结构,然后输入数据:

　　　　impuser/pwd@dabaseSIDfile=rows=N　　　　impuser/pwd@dabaseSIDfile=full=Ybuffer=64000　　　　commit=Yignore=Y2:

快速编译所有视图　　----当在把数据库倒入到新的服务器上后（数据库重建），需要将视图重新编译一遍，因为该表空间视图到其它表空间的表的连接会出现问题，可以利用PL/SQL的语言特性，快速编译。

　　SQL>SPOOL　　SQL>SELECT‘ALTERVIEW‘||TNAME||’　　COMPILE;’FROMTAB;　　SQL>SPOOLOFF　　然后执行即可。

　　SQL　　授权和创建同义词也可以快速进行：

　　SQL>SELECT‘GRANTSELECTON’　　||TNAME||’TOUSERNAME;’FROMTAB;　　SQL>SELECT‘CREATESYNONYM　　‘||TNAME||’FORUSERNAME.’||TNAME||’;’FROMTAB;　　　　第一条命令输入所有数据库结构,但无记录.第二次输入结构和数据,64000字节提交一次.ignore=Y选项保证第二次输入既使对象存在的情况下也能成功.

Oracle数据库操作常见错误及解决方案

2009-03-0917:

56

ORA-01650:

unabletoextendrollbacksegmentNAMEbyNUMintablespaceNAME　　产生原因：

上述ORACLE错误为回滚段表空间不足引起的，这也是ORACLE数据管理员最常见的ORACLE错误信息。

当用户在做一个非常庞大的数据操作导致现有回滚段的不足，使可分配用的回滚段表空间已满，无法再进行分配，就会出现上述的错误。

　　解决方式：

使用“ALTERTABLESPACEtablespace_nameADDDATAFILEfilenameSIZEsize_of_file”命令向指定的数据增加表空间，根据具体的情况可以增加一个或多个表空间。

当然这与还与你主机上的裸盘设备有关，如果你主机的裸盘设备已经没有多余的使用空间，建议你不要轻意的增加回滚段表空间的大小，可使用下列的语句先查询一下剩余的tablespace空间有多少：

Selectuser_name,sql_textfromV$open_cursorwhereuser_name=’’;　　如果多余的空间比较多，就可以适当追加一个大的回滚段给表空间使用，从而避免上述的错误。

你也可以用以下语句来检测一下rollbacksegment的竞争状况：

Selectclass,countfromV$waitstatwherecalssin（‘systemundoheader’,’systemundoblock’,’undoheader’,’undoblock’）;　　和Selectsum（value）fromV$sysstatwherenamein（‘db_block_gets’,’consistentsgets’）;　　如果任何一个classincount/sum（value）大于1%，就应该考虑增加rollbacksegment。

　　ORA-01652:

unabletoextendtempsegmentbynumintablespacename　　产生原因：

ORACLE临时段表空间不足，因为ORACLE总是尽量分配连续空间，一但没有足够的可分配空间或者分配不连续就会出现上述的现象。

　　解决方法：

我们知道由于ORACLE将表空间作为逻辑结构-单元，而表空间的物理结构是数据文件，数据文件在磁盘上物理地创建，表空间的所有对象也存在于磁盘上，为了给表空间增加空间，就必须增加数据文件。

先查看一下指定表空间的可用空间，使用视图，视图中每条记录代表可用空间的碎片大小：

SQL>Selectfile_id,block_id,blocks,bytesfromwheretablespace_name=’’;　　返回的信息可初步确定可用空间的最大块，看一下它是否小于错误信息中提到的尺寸，再查看一下缺省的表空间参数：

SQL>SELECTINITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,PCT_INCREASEFROMWHERETABLESPACE_NAME=name;　　通过下面的SQL命令修改临时段表空间的缺省存储值：

SQL>ALTERTABLESPACEnameDEFAULTSTORAGE（INITIALXXXNEXTYYY）;　　适当增大缺省值的大小有可能解决出现的错误问题，也可以通过修改用户的临时表空间大小来解决这个问题：

SQL>ALTERUSERusernameTEMPORARYTABLESPACEnew_tablespace_name;　　使用ALTERTABLESPACE命令，一但完成，所增加的空间就可使用，无需退出数据库或使表空间脱机，但要注意，一旦添加了数据文件，就不能再删除它，若要删除，就要删除表空间。

　　一个报错例子如下：

ORA-1652:

unabletoextendtempsegmentby207381intablespaceTEMPSPACE　　ORA-01578:

Oracledatablockcorrupted（file#num,block#num）　　产生原因：

当ORACLE访问一个数据块时，由于：

　　1、硬件的I/O错误；　　2、操作系统的I/O错误或缓冲问题；　　3、内存或paging问题；　　4、ORACLE试图访问一个未被格式化的系统块失败；5、数据文件部分溢出等上述几种情况的一种引起了逻辑坏块或者物理坏块，这时就会报ORA-01578的错误。

　　解决方式：

由于ORACLE只有在访问到有问题的数据文件时才会报错，所以报错的时间有可能会比实际出错的时间要晚，如果ORA-01578出错信息提示数据坏块指向的是用户自己的数据文件，则用以下方法来解决：

　　如果通过下面的SQL语句查出的坏块出现有索引上，则只需重建索引即可SQL>Selectowner,segment_name,segment_typefromdba_extentswherefile_id=andbetweenblock_idandblock_id+blocks-1;（和分别是ORA-01578报出的坏块出现的文件号和块号）　　如果坏块出现在表上，先用以下语句分析是否为永久性坏块（建议多执行一两次，有助于鉴别数据坏块是永久性的（硬盘上的物理坏块）还是随机性的（内存或硬件错误引起））：

SQL>Analyzetablevalidatestructurecascade;　　执行该命令后，可能会出现以下的结果：

　　ORA-01578：

与原先错误信息有相同的参数，为永久性的物理或逻辑坏块；与原先错误信息有不同的参数，可能与内存，pagespace和I/O设备有关。

　　如果用户有此表的最新备份，那么最好是用此备份来恢复此表，或者使用event10231来取出坏块以外的数据：

　　<1>.先关闭数据库　　<2>.编辑init.ora文件，加入：

event=”10231tracenamecontextforever,level10”　　<3>.startuprestrict　　<4>.创建一个临时表：

SQL>createtableerrortempasselect*fromerror;（error是坏表的表名）　　<5>.把event从init.ora文件中删掉并重起数据库　　<6>.rename坏表，把临时表rename成坏表的表名　　<7>.创建表上的INDEX等　　如果ORA-01578出错信息提示数据坏块指向的是数据字典或者是回滚段的话，你应该立即与ORACLE公司联系，共同商量一个好的解决办法。

　　这里所讲的解决方法只是比较常见的一种，一些更为具体的解决办法可以查看一下ORACLE的故障解决手册，那里面有浞及使用ROWID方法来取出坏块以外的数据的方法，这里就不介绍了。

ORA-01628:

max#ofextentsnumreachedforrollbacksegmentnum　　产生原因：

这种错误通常为一个回滚段和一个表空间已经达到MAXEXTENTS参数设置的极限。

要注意的是这个MAXEXTENTS不是该回滚段或表空间的硬件极限，硬件极限取决于数据库创建时在文件中指定的DB_BLOCK_SIZE参数的值。

　　解决方法：

使用SQL命令ALTERTABLESPACE…STORAGE（MAXEXTENTSXXXX）来增加MAXEXTENTS，其中“XXXX”值必须大于错误信息中所指的数值，但不能大于LARGESTMAXEXTENT的值，如果已经达到了LARGESTMAXEXTENTVALUE，解决的办法就是重新创建较大的范围尺寸，使用带有选项COMPRESS=Y的Export工具导出表，如果表空间有可用空间，先给表做一个备份，用altertablespacetablespace_name更改其名字，然后再装载表回数据库。

　　查看其错误出现的地方，如果出现在回滚段或索引上，那么必须将其删除并重建，如果出现在临时表空间，修改临时表空间的存储字段，便可解决这个问题。

　　一个报错例子如下：

ORA-1628:

max#extents50reachedforrollbacksegmentRBS_1　　ORA-00600:

internalerrorcode,arguments:

[num],[],[],[],[]　　产生原因：

这种错误通常为ORACLE的内部错误，只对OSS和ORACLE开发有用。

ORA-600的错误经常伴随跟踪文件的状态转储（系统状态和进程状态），系统状态存储将包括ORACLERDBMS持有的当前对象的信息，进程状态转储则将显示特殊进程持有的对象，当进程符合了某错误条件时，经常是由于一些信息取自它持有的一个块，如果我们知道这些错误进程持有的块，就容易跟踪问题的来源。

　　解决方法：

一般来说出现这个错误我们本身是无法解决的，只有从提高系统本身各方面来解决这个内部问题，如增加硬件设备，调整系统性能，使用OPS（当然OPS从某种意义上说并不是一种好的解决方式）等。

ORA-600错误的第一个变量用于标记代码中错误的位置（代码中的每个部分的第一变量都不一样），从第二个到第五个变量显示附加信息，告诉OSS代码在哪里出现了错误。

　　一个报错例子如下：

ORA-00600:

internalerrorcode,arguments:

[1237],[],[],[],[],[],[],[]　　ORA-03113：

end-of-fileoncommunicationchannel　　产生原因：

通讯不正常结束，从而导致通讯通道终止　　解决方法：

1>.检查是否有服进程不正常死机，可从得知　　2>.检查sql*NetDriver是否连接到ORACLE可执行程序　　3>.检查服务器网络是否正常，如网络不通或不稳定等4>.检查同一个网上是否有两个同样名字的节点　　5>.检查同一个网上是否有重复的IP地址　　ORA-00942:

tableorviewdoesnotexist　　产生原因：

这是由于装载的表或视图不存在，多半是还没有运行，无法执行Export视图,如果已经运行，则可能是版本错误。

　　解决方法：

因为Import和Export共享的一些视图是通过运行来装载的（它们具有相同的视图），并不生成单独的，因而造成视图与Export代码不同步，较难保持彼此之间的兼容，用户就必须建立自己的Export应用，从而避免ORA-00942的错误。

　　上述错误均为我们在使用回滚段时比较常见的问题，ORA-01598指明当前使用的回滚段的状态为“notonline”，不能使用，将它改为“online”状态即可使用；ORA-01636指明当前回滚段已经为“online”状态，可以直接使用，不用再集合它。

　　ORA-1636signalledduring:

alterrollbacksegmentrb00online　　我们在做统计时还可能遇到下述问题：

一个rollbacksegment的状态为”NeedsRecovery”的现象，这是由于ORACLE回退一个事物表中的没有提交的事物时失败所造成的。

通常原因为一个datafile或者tablespace是在offline的状态或者一个undo的目标被破坏或者rollbacksegment被破坏。

解决的办法是将所有的tablespace和datafile都置为online状态，如果不能解决则做下面的工作：

　　1>.在中加入event=”10015tracenamecontextforeverlever10”;　　2>.shutdown数据库然后重启；　　3>.在$ORACLE_HOME/rdbms/log下，找到startup时生成的tracefile;　　4>.在trace文件中，找到下列信息“errorrecoverytx（#,#）object#”;　　5>.根据object#（与表中的object_id相同）在表中查出该object的名字；　　6>.将该objectdrop掉;　　7>.在文件中将该rollbacksegment放回rollback_segments参数中，删除event;8>.shutdown数据库然后重启。

此时”NeedsRecovery”的问题应该是完全解决了，否则就是rollbacksegment被破坏了。

　　ORA-01688:

unabletoextendtablepartitionNAMEbyNUMintablespaceNAME　　产生原因：

指定的tablespace空间已经被占用满，无法扩展。

　　解决方法：

使用“ALTERTABLESPACEADDDATAFILE”命令增加文件系统文件和原始分区，或者增加INITIAL的大小（如：

altertablespaceCDRS101defaultstorage（next500Mpctincrease1））应该能够解决，否则就是有人使用你的表空间上创建了一个比较大的数据文件导致你的表空间不够用。

　　一个报错例子如下：

ORA-1688:

unabletoextendtablepartitionLOCAL_CDR101by460800intablespaceCDRS101

Oracle存储过程学习

2009-08-2616:

14

Oracle存储过程包含三部分：

过程声明，执行过程部分，存储过程异常。

Oracle存储过程可以有无参数存储过程和带参数存储过程。

一、无参程序过程语法

1createorreplaceprocedureNoParPro2as;3begin4;5exception    ..7exception         ....14end;15   三、带参数存储过程含赋值方式1createorreplaceprocedurerunbyparmeters（isalin%type,                           snameoutvarchar,sjobinoutvarchar）2asicountnumber;3begin4      selectcount（*）intoicountfromempwheresal>isalandjob=sjob;5      ificount=1then6        ....9      else10        ....12      endif;13exception14      whentoo_many_rowsthen15      （'返回值多于1行'）;16      whenothersthen17      （'在RUNBYPARMETERS过程中出错！

'）;18end;19四、在Oracle中对存储过程的调用过程调用方式一1declare2       realsal%type;3       realnamevarchar（40）;4       realjobvarchar（40）;5begin  //存储过程调用