ORACLE学习笔记(总结版).doc

《ORACLE学习笔记(总结版).doc》由会员分享，可在线阅读，更多相关《ORACLE学习笔记(总结版).doc（61页珍藏版）》请在三一办公上搜索。

1、ORACLE学习笔记一、ORACLE的启动和关闭1、在单机环境下要想启动或关闭ORACLE系统必须首先切换到ORACLE用户，如下su-oraclea、启动ORACLE系统oraclesvrmgrlSVRMGRconnectinternalSVRMGRstartupSVRMGRquitb、关闭ORACLE系统oraclesvrmgrlSVRMGRconnectinternalSVRMGRshutdownSVRMGRquit启动oracle9i数据库命令：$sqlplus/nologSQL*Plus:Release9.2.0.1.0-ProductiononFriOct3113:53:53200

2、3Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.SQLconnect/assysdbaConnectedtoanidleinstance.SQLstartupCSQLstartupORACLEinstancestarted.2、在双机环境下要想启动或关闭ORACLE系统必须首先切换到root用户，如下suroota、启动ORACLE系统haregyoracleb、关闭ORACLE系统haregnoracleOracle数据库有哪几种启动方式说明：有以下几种启动方式：1、startupnomount非安装启动，这种方式启动下可

3、执行：重建控制文件、重建数据库读取init.ora文件，启动instance，即启动SGA和后台进程，这种启动只需要init.ora文件。2、startupmountdbname安装启动，这种方式启动下可执行：数据库日志归档、数据库介质恢复、使数据文件联机或脱机，重新定位数据文件、重做日志文件。执行“nomount”，然后打开控制文件，确认数据文件和联机日志文件的位置，但此时不对数据文件和日志文件进行校验检查。3、startupopendbname先执行“nomount”，然后执行“mount”，再打开包括Redolog文件在内的所有数据库文件，这种方式下可访问数据库中的数据。4、startu

4、p，等于以下三个命令startupnomountalterdatabasemountalterdatabaseopen5、startuprestrict约束方式启动这种方式能够启动数据库，但只允许具有一定特权的用户访问非特权用户访问时，会出现以下提示：ERROR：ORA-01035:ORACLE只允许具有RESTRICTEDSESSION权限的用户使用6、startupforce强制启动方式当不能关闭数据库时，可以用startupforce来完成数据库的关闭先关闭数据库，再执行正常启动数据库命令7、startuppfile=参数文件名带初始化参数文件的启动方式先读取参数文件，再按参数文件中的设

5、置启动数据库例：startuppfile=E:Oracleadminoradbpfileinit.ora8、startupEXCLUSIVE二、用户如何有效地利用数据字典ORACLE的数据字典是数据库的重要组成部分之一，它随着数据库的产生而产生,随着数据库的变化而变化,体现为sys用户下的一些表和视图。数据字典名称是大写的英文字符。数据字典里存有用户信息、用户的权限信息、所有数据对象信息、表的约束条件、统计分析数据库的视图等。我们不能手工修改数据字典里的信息。很多时候，一般的ORACLE用户不知道如何有效地利用它。dictionary全部数据字典表的名称和解释，它有一个同义词dictdict_

6、column全部数据字典表里字段名称和解释如果我们想查询跟索引有关的数据字典时，可以用下面这条SQL语句:SQLselect*fromdictionarywhereinstr(comments,index)0;如果我们想知道user_indexes表各字段名称的详细含义，可以用下面这条SQL语句:SQLselectcolumn_name,commentsfromdict_columnswheretable_name=USER_INDEXES;依此类推，就可以轻松知道数据字典的详细名称和解释，不用查看ORACLE的其它文档资料了。下面按类别列出一些ORACLE用户常用数据字典的查询使用方法。1、

7、用户查看当前用户的缺省表空间SQLselectusername,default_tablespacefromuser_users;查看当前用户的角色SQLselect*fromuser_role_privs;查看当前用户的系统权限和表级权限SQLselect*fromuser_sys_privs;SQLselect*fromuser_tab_privs;2、表查看用户下所有的表SQLselect*fromuser_tables;查看名称包含log字符的表SQLselectobject_name,object_idfromuser_objectswhereinstr(object_name,LO

8、G)0;查看某表的创建时间SQLselectobject_name,createdfromuser_objectswhereobject_name=upper(&table_name);查看某表的大小SQLselectsum(bytes)/(1024*1024)assize(M)fromuser_segmentswheresegment_name=upper(&table_name);查看放在ORACLE的内存区里的表SQLselecttable_name,cachefromuser_tableswhereinstr(cache,Y)0;3、索引查看索引个数和类别SQLselectindex_

9、name,index_type,table_namefromuser_indexesorderbytable_name;查看索引被索引的字段SQLselect*fromuser_ind_columnswhereindex_name=upper(&index_name);查看索引的大小SQLselectsum(bytes)/(1024*1024)assize(M)fromuser_segmentswheresegment_name=upper(&index_name);4、序列号查看序列号，last_number是当前值SQLselect*fromuser_sequences;5、视图查看视图的

10、名称SQLselectview_namefromuser_views;查看创建视图的select语句SQLsetview_name,text_lengthfromuser_views;SQLsetlong2000;说明：可以根据视图的text_length值设定setlong的大小SQLselecttextfromuser_viewswhereview_name=upper(&view_name);6、同义词查看同义词的名称SQLselect*fromuser_synonyms;7、约束条件查看某表的约束条件SQLselectconstraint_name,constraint_type,se

11、arch_condition,r_constraint_namefromuser_constraintswheretable_name=upper(&table_name);SQLselectc.constraint_name,c.constraint_type,cc.column_namefromuser_constraintsc,user_cons_columnsccwherec.owner=upper(&table_owner)andc.table_name=upper(&table_name)andc.owner=cc.ownerandc.constraint_name=cc.cons

12、traint_nameorderbycc.position;8、存储函数和过程查看函数和过程的状态SQLselectobject_name,statusfromuser_objectswhereobject_type=FUNCTION;SQLselectobject_name,statusfromuser_objectswhereobject_type=PROCEDURE;查看函数和过程的源代码SQLselecttextfromall_sourcewhereowner=userandname=upper(&plsql_name);三、查看数据库的SQL1、查看表空间的名称及大小selectt.

13、tablespace_name,round(sum(bytes/(1024*1024),0)ts_sizefromdba_tablespacest,dba_data_filesdwheret.tablespace_name=d.tablespace_namegroupbyt.tablespace_name;2、查看表空间物理文件的名称及大小selecttablespace_name,file_id,file_name,round(bytes/(1024*1024),0)total_spacefromdba_data_filesorderbytablespace_name;3、查看回滚段名称及大

14、小selectsegment_name,tablespace_name,r.status,(initial_extent/1024)InitialExtent,(next_extent/1024)NextExtent,max_extents,v.curextCurExtentFromdba_rollback_segsr,v$rollstatvWherer.segment_id=v.usn(+)orderbysegment_name;4、查看控制文件selectnamefromv$controlfile;5、查看日志文件selectmemberfromv$logfile;6、查看表空间的使用情况

15、selectsum(bytes)/(1024*1024)asfree_space,tablespace_namefromdba_free_spacegroupbytablespace_name;SELECTA.TABLESPACE_NAME,A.BYTESTOTAL,B.BYTESUSED,C.BYTESFREE,(B.BYTES*100)/A.BYTES%USED,(C.BYTES*100)/A.BYTES%FREEFROMSYS.SM$TS_AVAILA,SYS.SM$TS_USEDB,SYS.SM$TS_FREECWHEREA.TABLESPACE_NAME=B.TABLESPACE_N

16、AMEANDA.TABLESPACE_NAME=C.TABLESPACE_NAME;7、查看数据库库对象selectowner,object_type,status,count(*)count#fromall_objectsgroupbyowner,object_type,status;8、查看数据库的版本SelectversionFROMProduct_component_versionWhereSUBSTR(PRODUCT,1,6)=Oracle;9、查看数据库的创建日期和归档方式SelectCreated,Log_Mode,Log_ModeFromV$Database;四、ORACLE用

17、户连接的管理用系统管理员，查看当前数据库有几个用户连接：SQLselectusername,sid,serial#fromv$session;如果要停某个连接用SQLaltersystemkillsessionsid,serial#;如果这命令不行,找它UNIX的进程数SQLselectpro.spidfromv$sessionses,v$processprowhereses.sid=21andses.paddr=pro.addr;说明：21是某个连接的sid数然后用kill命令杀此进程号。五、SQL*PLUS使用a、近入SQL*Plus$sqlplus用户名/密码退出SQL*PlusSQLe

18、xitb、在sqlplus下得到帮助信息列出全部SQL命令和SQL*Plus命令SQLhelp列出某个特定的命令的信息SQLhelp命令名c、显示表结构命令DESCRIBESQLDESC表名d、SQL*Plus中的编辑命令显示SQL缓冲区命令SQLL修改SQL命令首先要将待改正行变为当前行SQLn用CHANGE命令修改内容SQLc/旧/新重新确认是否已正确SQLL使用INPUT命令可以在SQL缓冲区中增加一行或多行SQLiSQL输入内容e、调用外部系统编辑器SQLedit文件名可以使用DEFINE命令设置系统变量EDITOR来改变文本编辑器的类型，在login.sql文件中定义如下一行DEFI

19、NE_EDITOR=vif、运行命令文件SQLSTARTtestSQLtest常用SQL*Plus语句a、表的创建、修改、删除创建表的命令格式如下：createtable表名（列说明列表）；为基表增加新列命令如下：ALTERTABLE表名ADD（列说明列表）例：为test表增加一列Age，用来存放年龄sqlaltertabletestadd（Agenumber(3)）；修改基表列定义命令如下：ALTERTABLE表名MODIFY（列名数据类型）例：将test表中的Count列宽度加长为10个字符sqlalteratbletestmodify（Countychar(10)）；b、将一张表删除语句

20、的格式如下：DORPTABLE表名；例：表删除将同时删除表的数据和表的定义sqldroptabletestc、表空间的创建、删除六、ORACLE逻辑备份的SH文件完全备份的SH文件：exp_comp.shrq=date+%m%dsu-oracle-cexpsystem/managerfull=yinctype=completefile=/oracle/export/db_comp$rq.dmp累计备份的SH文件：exp_cumu.shrq=date+%m%dsu-oracle-cexpsystem/managerfull=yinctype=cumulativefile=/oracle/expo

21、rt/db_cumu$rq.dmp增量备份的SH文件:exp_incr.shrq=date+%m%dsu-oracle-cexpsystem/managerfull=yinctype=incrementalfile=/oracle/export/db_incr$rq.dmproot用户crontab文件/var/spool/cron/crontabs/root增加以下内容021*/oracle/exp_comp.sh302*0-5/oracle/exp_incr.sh452*6/oracle/exp_cumu.sh当然这个时间表可以根据不同的需求来改变的，这只是一个例子。七、ORACLE常用的

22、SQL语法和数据对象一.数据控制语句(DML)部分1.INSERT(往数据表里插入记录的语句)INSERTINTO表名(字段名1,字段名2,)VALUES(值1,值2,);INSERTINTO表名(字段名1,字段名2,)SELECT(字段名1,字段名2,)FROM另外的表名;字符串类型的字段值必须用单引号括起来,例如:GOODDAY如果字段值里包含单引号需要进行字符串转换,我们把它替换成两个单引号.字符串类型的字段值超过定义的长度会出错,最好在插入前进行长度校验.日期字段的字段值可以用当前数据库的系统时间SYSDATE,精确到秒或者用字符串转换成日期型函数TO_DATE(2001-08-01,

23、YYYY-MM-DD)TO_DATE()还有很多种日期格式,可以参看ORACLEDOC.年-月-日小时:分钟:秒的格式YYYY-MM-DDHH24:MI:SSINSERT时最大可操作的字符串长度小于等于4000个单字节,如果要插入更长的字符串,请考虑字段用CLOB类型,方法借用ORACLE里自带的DBMS_LOB程序包.INSERT时如果要用到从1开始自动增长的序列号,应该先建立一个序列号CREATESEQUENCE序列号的名称(最好是表名+序列号标记)INCREMENTBY1STARTWITH1MAXVALUE99999CYCLENOCACHE;其中最大的值按字段的长度来定,如果定义的自动增

24、长的序列号NUMBER(6),最大值为999999INSERT语句插入这个字段值为:序列号的名称.NEXTVAL2.DELETE(删除数据表里记录的语句)DELETEFROM表名WHERE条件;注意：删除记录并不能释放ORACLE里被占用的数据块表空间.它只把那些被删除的数据块标成unused.如果确实要删除一个大表里的全部记录,可以用TRUNCATE命令,它可以释放占用的数据块表空间TRUNCATETABLE表名;此操作不可回退.3.UPDATE(修改数据表里记录的语句)UPDATE表名SET字段名1=值1,字段名2=值2,WHERE条件;如果修改的值N没有赋值或定义时,将把原来的记录内容清

25、为NULL,最好在修改前进行非空校验;值N超过定义的长度会出错,最好在插入前进行长度校验.注意事项:A.以上SQL语句对表都加上了行级锁,确认完成后,必须加上事物处理结束的命令COMMIT才能正式生效,否则改变不一定写入数据库里.如果想撤回这些操作,可以用命令ROLLBACK复原.B.在运行INSERT,DELETE和UPDATE语句前最好估算一下可能操作的记录范围,应该把它限定在较小(一万条记录)范围内,.否则ORACLE处理这个事物用到很大的回退段.程序响应慢甚至失去响应.如果记录数上十万以上这些操作,可以把这些SQL语句分段分次完成,其间加上COMMIT确认事物处理.二.数据定义(DDL

26、)部分1.CREATE(创建表,索引,视图,同义词,过程,函数,数据库链接等)ORACLE常用的字段类型有CHAR固定长度的字符串VARCHAR2可变长度的字符串NUMBER(M,N)数字型M是位数总长度,N是小数的长度DATE日期类型创建表时要把较小的不为空的字段放在前面,可能为空的字段放在后面创建表时可以用中文的字段名,但最好还是用英文的字段名创建表时可以给字段加上默认值,例如DEFAULTSYSDATE这样每次插入和修改时,不用程序操作这个字段都能得到动作的时间创建表时可以给字段加上约束条件例如不允许重复UNIQUE,关键字PRIMARYKEY2.ALTER(改变表,索引,视图等)改变表

27、的名称ALTERTABLE表名1TO表名2;在表的后面增加一个字段ALTERTABLE表名ADD字段名字段名描述;修改表里字段的定义描述ALTERTABLE表名MODIFY字段名字段名描述;给表里的字段加上约束条件ALTERTABLE表名ADDCONSTRAINT约束名PRIMARYKEY(字段名);ALTERTABLE表名ADDCONSTRAINT约束名UNIQUE(字段名);把表放在或取出数据库的内存区ALTERTABLE表名CACHE;ALTERTABLE表名NOCACHE;3.DROP(删除表,索引,视图,同义词,过程,函数,数据库链接等)删除表和它所有的约束条件DROPTABLE表名

28、CASCADECONSTRAINTS;4.TRUNCATE(清空表里的所有记录,保留表的结构)TRUNCATE表名;三.查询语句(SELECT)部分SELECT字段名1,字段名2,FROM表名1,表名2,WHERE条件;字段名可以带入函数例如:COUNT(*),MIN(字段名),MAX(字段名),AVG(字段名),DISTINCT(字段名),TO_CHAR(DATE字段名,YYYY-MM-DDHH24:MI:SS)NVL(EXPR1,EXPR2)函数解释:IFEXPR1=NULLRETURNEXPR2ELSERETURNEXPR1DECODE(AAV1R1V2R2.)函数解释:IFAA=V1T

29、HENRETURNR1IFAA=V2THENRETURNR2.ELSERETURNNULLLPAD(char1,n,char2)函数解释:字符char1按制定的位数n显示，不足的位数用char2字符串替换左边的空位字段名之间可以进行算术运算例如:(字段名1*字段名1)/3查询语句可以嵌套例如:SELECTFROM(SELECTFROM表名1,表名2,WHERE条件)WHERE条件2;两个查询语句的结果可以做集合操作例如:并集UNION(去掉重复记录),并集UNIONALL(不去掉重复记录),差集MINUS,交集INTERSECT分组查询SELECT字段名1,字段名2,FROM表名1,表名2,G

30、ROUPBY字段名1HAVING条件;两个以上表之间的连接查询SELECT字段名1,字段名2,FROM表名1,表名2,WHERE表名1.字段名=表名2.字段名AND;SELECT字段名1,字段名2,FROM表名1,表名2,WHERE表名1.字段名=表名2.字段名(+)AND;有(+)号的字段位置自动补空值查询结果集的排序操作,默认的排序是升序ASC,降序是DESCSELECT字段名1,字段名2,FROM表名1,表名2,ORDERBY字段名1,字段名2DESC;字符串模糊比较的方法INSTR(字段名,字符串)0字段名LIKE字符串%字符串%每个表都有一个隐含的字段ROWID,它标记着记录的唯一性

31、.四.ORACLE里常用的数据对象(SCHEMA)1.索引(INDEX)CREATEINDEX索引名ON表名(字段1,字段2,);ALTERINDEX索引名REBUILD;一个表的索引最好不要超过三个(特殊的大表除外),最好用单字段索引,结合SQL语句的分析执行情况,也可以建立多字段的组合索引和基于函数的索引ORACLE8.1.7字符串可以索引的最大长度为1578单字节ORACLE8.0.6字符串可以索引的最大长度为758单字节2.视图(VIEW)CREATEVIEW视图名ASSELECT.FROM.;ALTERVIEW视图名COMPILE;视图仅是一个SQL查询语句,它可以把表之间复杂的关系

32、简洁化.3.同义词(SYNONMY)CREATESYNONYM同义词名FOR表名;CREATESYNONYM同义词名FOR表名数据库链接名;4.数据库链接(DATABASELINK)CREATEDATABASELINK数据库链接名CONNECTTO用户名IDENTIFIEDBY密码USING数据库连接字符串;数据库连接字符串可以用NET8EASYCONFIG或者直接修改TNSNAMES.ORA里定义.数据库参数global_name=true时要求数据库链接名称跟远端数据库名称一样数据库全局名称可以用以下命令查出SELECT*FROMGLOBAL_NAME;查询远端数据库里的表SELECTFR

33、OM表名数据库链接名;五.权限管理(DCL)语句1.GRANT赋于权限常用的系统权限集合有以下三个:CONNECT(基本的连接),RESOURCE(程序开发),DBA(数据库管理)常用的数据对象权限有以下五个:ALLON数据对象名,SELECTON数据对象名,UPDATEON数据对象名,DELETEON数据对象名,INSERTON数据对象名,ALTERON数据对象名GRANTCONNECT,RESOURCETO用户名;GRANTSELECTON表名TO用户名;GRANTSELECT,INSERT,DELETEON表名TO用户名1,用户名2;2.REVOKE回收权限REVOKECONNECT,R

34、ESOURCEFROM用户名;REVOKESELECTON表名FROM用户名;REVOKESELECT,INSERT,DELETEON表名FROM用户名1,用户名2;查询数据库中第63号错误：selectorgaddr,destaddrfromsm_histable0116whereerror_code=63;查询数据库中开户用户最大提交和最大下发数：selectMSISDN,TCOS,OCOSfromms_usertable；查询数据库中各种错误代码的总和：selecterror_code,count(*)fromsm_histable0513groupbyerror_codeorderby

35、error_code;查询报表数据库中话单统计种类查询。selectsum(Successcount)fromtbl_MiddleMt0411whereServiceType2=111selectsum(successcount),servicetypefromtbl_middlemt0411groupbyservicetype原文地址：oug.org/viewthread.php?tid=60293/创建一个控制文件命令到跟踪文件 alterdatabasebackupcontrolfiletotrace; /增加一个新的日志文件组的语句 connectinternalassysdba al

36、terdatabase addlogfilegroup4 (/db01/oracle/CC1/log_1c.dbf, /db02/oracle/CC1/log_2c.dbf)size5M; alterdatabase addlogfilemember/db03/oracle/CC1/log_3c.dbf togroup4; /在ServerManager上MOUNT并打开一个数据库: connectinternalassysdba startupmountORA1exclusive; alterdatabaseopen; /生成数据字典 catalog catproc /在init.ora中备

37、份数据库的位置 log_archive_dest_1=/db00/arch log_archive_dest_state_1=enable log_archive_dest_2=service=stby.worldmandatoryreopen=60 log_archive_dest_state_2=enable /对用户的表空间的指定和管理相关的语句 createuserUSERNAMEidentifiedbyPASSWORD defaulttablespaceTABLESPACE_NAME; alteruserUSERNAMEdefaulttablespaceTABLESPACE_NAME

38、; alteruserSYSTEMquota0onSYSTEM; alteruserSYSTEMquota50MonTOOLS; createuserUSERNAMEidentifiedbyPASSWORD defaulttablespaceDATA temporarytablespaceTEMP; alteruserUSERNAMEtemporarytablespaceTEMP; /重新指定一个数据文件的大小: alterdatabase datafile/db05/oracle/CC1/data01.dbfresize200M; /创建一个自动扩展的数据文件: createtablespa

39、ceDATA datafile/db05/oracle/CC1/data01.dbfsize200M autoextendON next10M maxsize250M; /在表空间上增加一个自动扩展的数据文件: altertablespaceDATA adddatafile/db05/oracle/CC1/data02.dbf size50M autoextendON maxsize300M; /修改参数: alterdatabase datafile/db05/oracle/CC1/data01.dbf autoextendON maxsize300M; /在数据文件移动期间重新命名: al

40、terdatabaserenamefile /db01/oracle/CC1/data01.dbfto /db02/oracle/CC1/data01.dbf; altertablespaceDATArenamedatafile /db01/oracle/CC1/data01.dbfto /db02/oracle/CC1/data01.dbf; alterdatabaserenamefile /db05/oracle/CC1/redo01CC1.dbfto /db02/oracle/CC1/redo01CC1.dbf; alterdatabasedatafile/db05/oracle/CC1/dat