Oracle入门资料.doc

ORACLE快速入门随笔修改密码方法：1.SQL; grant connect to USRNAME identified by USRPWD ;2.SQL; alter user USRNAME identified by USRPWD ;3.SQL; password USRNAME ;更改 USRNMAE 的口令旧口令：新口令：重新键入新口令：修改成oracle认证方法：1.修改参数文件中remote_login_passwordfile=EXCLUSIVE 或SHARED2.修改/home/network/admin/SQLNET.ORA 文件增加 sqlnet.authentication_services=(NFS)ORACLE_SID是操作系统级的参数INSTANCE_NAME是数据库级的参数SPFILE 是服务器参数文件PFILE 是文本参数文件create pfile=/oracle/test.ora from spfile ;show parameter spfile ; 后如果VALUE值是零，则没有使用spfile。启动数据库是可以指定是用pfile还是用spfile启动 如下：startup pfile=/oracle/test.orashow parameter instanceshow parameter dbalter system set job_queue_processes=50comment =temporaty change onscope = memory|spfile|both ;alter session set CONCAT(Good, String) GoodStringSUBSTR(String,1,3) Str LENGTH(String) 6 INSTR(String, r) 3 LPAD(sal,10,*) *5000union union allminus 第一个查询减去第二个查询intersect 交集 SQL; select replace(itpub oracle,oracle,training)itpub from dual /ITPUB-itpub trainingTranslate SQL; select translate(This is second trianing of itpub in Beijing!,abcde,12345) itpub2from dual;ITPUB-This is s53on4 tri1ning of itpu2 in B5ijing!FloorSQL; select floor(4.55) from dual;FLOOR(4.55)- 4CeilSQL; select ceil(4.55) from dual;CEIL(4.55)- 5ROUND: 4舍5入到指定的小数位ROUND(45.926, 2) 45.93TRUNC: 将值截断到指定的小数位TRUNC(45.926, 2) 45.92MOD: 返回相除后的余数MOD(1600, 300) 100select trunc(sysdate, D) - 3 from dual; trunc(sysdate, D), 截取到本周的第一天(0:00) trunc(sysdate, M), 截取到本月的第一天(0:00) trunc(sysdate, MM), 截取到本月的第一天(0:00)trunc(sysdate, DD), 截取到本日的0:00 trunc(sysdate,yyyy)得到今年的第一天的0:00.十进制与十六进制EYGLEvilen; select to_char(2463,xxxx) from dual;TO_CH-99f已选择 1 行。十六进制与十进制EYGLEvilen; select to_number(99f,xxxx) from dual;TO_NUMBER(99F,XXXX)- 2463SQL; select ename,sal,2case job WHEN MANAGER THEN 1.1*sal3 WHEN ANALYST THEN 1.2*SAL4 WHEN CLERK THEN 1.5*SAL5ELSE sal END NEW_SAL6from emp;ENAME SAL NEW_SAL- - -SMITH 800 1200ALLEN 1600 1600WARD 1250 1250JONES 2975 3272.5MARTIN 1250 1250BLAKE 2850 3135CLARK 2450 2695SCOTT 3000 3600KING 5000 5000TURNER 1500 1500ADAMS 1100 1650JAMES 950 1425FORD 3000 3600MILLER 1300 1950SQL; SELECT job, sal,2 DECODE(job, ANALYSTSAL*1.1,3 CLERK, SAL*1.15,4 MANAGER, SAL*1.20,5 SAL)6 REVISED_SALARY7FROM emp;JOB SAL REVISED_SALARY- - -PRESIDENT 5000 5000MANAGER 2850 3420MANAGER 2450 2940将空值转换为实际的值数据格式可以是日期,字符,数字数据类型必须匹配 NVL(comm,0)NVL(hiredate,01-JAN-97)NVL(job,No Job Yet)NVL2(expr1,expr2,expr3)如果expr1不为Null，返回expr2，如果expr1为Null，返回expr3。expr1可以为任何数据类型NULLIF(expr1,expr2)比较两个表达式，如果相等返回空值，如果不等返回第一个值。COALESCE(expr1,expr2,.,exprn)返回表达式列表里的第一个非空表达式错误:在启动数据库时出现ORA-01157,ORA-01110或操作系统级错误例如ORA-07360,在关闭数据库(使用shutdown normal或shutdown immediate) 时将导致错误ORA-01116,ORA-01110以及操作系统级错误ORA-07368解决:以下有两种解决方案:1.用户的表空间可以被轻易地重建即最近导出的对象是可用的或表空间中的对象可以被轻易地重建等.在这种情况下,最简单的方法是offline并删除该数据文件,删除表空间并重建表空间以及所有的对象. svrmgrl; startup mountsvrmgrl; alter database datafile filename offline drop;svrmgrl; alter database open;svrmgrl; drop tablespace tablespace_name including contents;重建表空间及所有对象.2.用户的表空间不能够被轻易地重建 在大多数情况下,重建表空间是不可能及太辛苦的工作.方法是倒备份及做介质恢复.如果您的系统运行在NOARCHIVELOG模式下,则只有丢失的数据在online redo log中方可被恢复。步骤如下:1)Restore the lost datafile from a backup2)svrmgrl; startup mount3)svrmgrl; select v1.group#,member,sequence#,first_change#; from v$log v1,v$logfile v2 ; where v1.group#=v2.group#;4)如果数据库运行在NOARCHIVELOG模式下则:svrmgrl; select file#,change# from v$recover_file; 如果 CHANGE# 大于最小的FIRST_CHANGE#则数据文件可以被恢复。如果 CHANGE# 小于最小的FIRST_CHANGE#则数据文件不可恢复。 恢复最近一次的全备份或采用方案一. 5)svrmgrl; recover datafile filename; 6)确认恢复成功 7)svrmgrl; alter database open resetlogs; 3.只读表空间无需做介质恢复,只要将备份恢复即可.唯一的例外是: 表空间在最后一次备份后被改为read-write 模式表空间在最后一次备份后被改为read-only 模式在这种情况下,均需进行介质恢复二、临时表空间临时表空间并不包含真正的数据,恢复的方法是删除临时表空间并重建即可. 三、系统表空间如果备份不可用,则只能采用重建数据库的方法 四、回滚表空间有两种情况: 1、数据库已经完全关闭(使用shutdown immediate或shutdown命令) 1) 确认数据库完全关闭 2) 修改init.ora文件,注释rollback-segment 3) svrmgrl; startup restrict mount 4) svrmgrl; alter database datafile filename offline drop; 5) svrmgrl; alter database open; 基于出现的结果: statement processed 转(7) ORA-00604,ORA-00376,ORA-01110转(6) 6) svrmgrl; shutdown immediate 修改init.ora文件,增加如下一行: _corrupted_rollback_segments = (,.) svrmgrl; startup restrict 7) svrmgrl; drop tablespace tablespace_name including contents; 重建表空间及回滚段 9) svrmgrl; alter system disable restricted session; 10) 修改init.ora文件 2、数据库未完全关闭(数据库崩溃或使用shutdown abort命令关闭数据库) 1) 恢复备份 2) svrmgrl; startup mount 3) svrmgrl; select file#,name,status from v$datafile; svrmgrl; alter database datafile filename online; 4) svrmgrl; select v1.group#,member,sequence#,first_change# ; from v$log v1,v$logfile v2 ; where v1.group#=v2.group#; 5) svrmgrl; select file#,change# from v$recover_file; 见一方案2-4 6) svrmgrl; recover datafile filename; 7) svrmgrl; alter database open; 3、数据库处于打开状态 1) 删除回滚段和表空间 2) 重建表空间和回滚段 五、控制文件恢复 1所有的控制文件均被破坏 将备份的控制文件拷贝至原目录下 对于RAW DEVICE,则: dd if=con.bak of=/dev/rdrd/drd1 seek=128 2并非所有的控制文件均被破坏用其他的控制文件启动数据库。3. 数据文件上所存储的表select a.segment_name from dba_extents a,dba_data_files b where a.file_id = b.file_id and b.file_name = 文件名全路径 and a.segment_type = TABLEjob不能自动运行check db parameters job_queue_processes if it set to 0 then set it larger than 0 job_queue_processes=1问题描述执行如下语句select session_id,object_name from v$locked_object a,dba_objects bwhere a.object_id=b.object_iderror: ora-03212 Temporary Segment cannt be cteated in locally-managed tablespace.更改为sys 用户 以dba登录,可以查询.SHUTDOWN IMMEDIATE； STARTUP MOUNT； RECOVER DATABASE； ALTER DATABASE OPEN；查看表大小！select segment_name,(bytes/1024)/1024|M 大小， (BLOCKS*DB_BLOCK_SIZE)/1024)/1024|M占用空间 from dba_segments wheresegment_name = .db_block_size 是参数查询未提交事务select username, SID|,| SERIAL# ses, T.STATUS , START_TIME, RN.NAME, USED_UBLK, USED_UREC from v$transaction T, v$session S, v$rollstat RS, v$rollname RN where T.SES_ADDR = S.SADDR and T.XIDUSN = RS.USN and RN.USN = RS.USN order by USED_UBLK;如何将数据库中被锁表解锁 作者：来自：阅读次数： 18 大 中 小 -我们在操作数据库的时候，有时候会由于操作不当引起数据库表被锁定，这么我们经常不知所措，不知怎么给这些表解锁，在pl/sql Developer工具的的菜单“tools”里面的“sessions”可以查询现在存在的会话，但是我们很难找到那个会话被锁定了，想找到所以被锁的会话就更难了，下面这叫查询语句可以查询出所以被锁的会话。如下：SELECT sn.username, m.SID,sn.SERIAL#, m.TYPE, DECODE (m.lmode, 0, None, 1, Null, 2, Row Share, 3, Row Excl., 4, Share, 5, S/Row Excl., 6, Exclusive, lmode, LTRIM (TO_CHAR (lmode, 990) ) lmode, DECODE (m.request, 0, None, 1, Null, 2, Row Share, 3, Row Excl., 4, Share, 5, S/Row Excl., 6, Exclusive, request, LTRIM (TO_CHAR (m.request, 990) ) request, m.id1, m.id2 FROM v$session sn, v$lock m WHERE (sn.SID = m.SID AND m.request != 0) -存在锁请求，即被阻塞 OR ( sn.SID = m.SID -不存在锁请求，但是锁定的对象被其他会话请求锁定 AND m.request = 0 AND lmode != 4 AND (id1, id2) IN ( SELECT s.id1, s.id2 FROM v$lock s WHERE request != 0 AND s.id1 = m.id1 AND s.id2 = m.id2) )ORDER BY id1, id2, m.request;通过以上查询知道了sid和 SERIAL#就可以开杀了 alter system kill session sid,SERIAL#;在SQLPLUS里： 1.不显示 行头 set heading off pages 0 2.不显示行数信息 set feedback off 3。不在屏幕上显示结果 set termout off 1.由于该问题由oarcle工程师定位为无法恢复，并在网上进行了求助也每人能解决，所以我可以方下包袱，去大胆的尝试新办法，按照我对数据库的框架理解去寻求办法。2.因为无法联机的表空空间为用户表空间，所以不影响数据库系统的启动，所以通过 ：SQL;startup mount; SQL;alter database open;启动数据库3.我查看了sys.col#,sys.obj#表，想从表中得到关于datafile0016的信息，但没有能找到我认为需要的信息；4.后来我查看了sys.file#,发现了所有数据文件的记录，然后我找到对应的file0016的记录，想通过删除该记录来屏蔽表空间启动时的数据文件的完整性检测，于是现对sys.file#进行了备份，然后删除了file0016对应的记录。5.此时执行SQL;alter database tablespace ts_user online;提示数据库已被修改，我惊喜的发现表空间被联机成功了。由于相应的数据文件在这之前已经损坏，而且该数据文件已经被设置成了offline，并且局方在数据库关闭后，在mount状态下直接对该文件所在表空间进行了offline操作，所以现在如果再通过“alter database datafile filename offline drop”已经不起作用，因为此时的表空间默认的状态为offline，该数据文件与其所在表空间（联机或脱机)具有一致性。所以此时如果想启动该表空间，只能通过屏蔽数据文件的完整性检查！ 数据库业务表空间联机失败 数据库业务表空间联机失败1.由于该问题由oarcle工程师定位为无法恢复，并在网上进行了求助也每人能解决，所以我可以方下包袱，去大胆的尝试新办法，按照我对数据库的框架理解去寻求办法。2.因为无法联机的表空空间为用户表空间，所以不影响数据库系统的启动，所以通过 ：SQL;startup mount;SQL;alter database open;启动数据库3.我查看了sys.col#,sys.obj#表，想从表中得到关于datafile0016的信息，但没有能找到我认为需要的信息；4.后来我查看了sys.file#,发现了所有数据文件的记录，然后我找到对应的file0016的记录，想通过删除该记录来屏蔽表空间启动时的数据文件的完整性检测，于是现对sys.file#进行了备份，然后删除了file0016对应的记录。5.此时执行SQL;alter database tablespace ts_user online;提示数据库已被修改，我惊喜的发现表空间被联机成功了。/dev/rlvdata0016文件需要介质恢复，表空间联机失败！ SQLPLUS常用命令假设当前执行命令为：select * from tab;(a)ppend 添加文本到缓冲区当前行尾aorder by tname结果：select * from tab order by tname;（注：a后面跟2个空格）(c)hange/old/new 在当前行用新的文本替换旧的文本c/*/tname结果：select tname from tab;(c)hange/text从当前行删除文本c/tab结果：select tname from ;del删除当前行del n删除第n行(i)nput 文本 在当前行之后添加一行(l)ist 显示缓冲区中所有行(l)ist n 显示缓冲区中第 n 行(l)ist m n 显示缓冲区中 m 到 n 行run执行当前缓冲区的命令/执行当前缓冲区的命令r执行当前缓冲区的命令文件名运行调入内存的sql文件，如：SQL; edit s;如果当前目录下不存在s.sql文件，则系统自动生成s.sql文件，在其中输入“select * from tab;”，存盘退出。SQL; s;系统会自动查询当前用户下的所有表、视图、同义词。文件名 在.sql文件中调用令一个.sql文件时使用save 文件名将缓冲区的命令以文件方式存盘，缺省文件扩展名为.sqlget 文件名 调入存盘的sql文件start 文件名 运行调入内存的sql文件spool 文件名 把这之后的各种操作及执行结果“假脱机”即存盘到磁盘文件上，默认文件扩展名为.lstspool显示当前的“假脱机”状态spool off停止输出例：SQL; spool aSQL; spool正假脱机到 A.LSTSQL; spool offSQL; spool当前无假脱机exit 退出SQL*PLUSdesc 表名显示表的结构show user显示当前连接用户show error 显示错误show all 显示所有68个系统变量值edit 打开默认编辑器，Windows系统中默认是notepad.exe，把缓冲区中最后一条SQL语句调入afiedt.buf文件中进行编辑edit 文件名把当前目录中指定的.sql文件调入编辑器进行编辑clear screen 清空当前屏幕显示ORACLE管理常用命令第一章：日志管理1.forcing log switches sql; alter system switch logfile;2.forcing checkpoints sql; alter system checkpoint;3.adding online redo log groups sql; alter database add logfile group 4 sql; (/disk3/log4a.rdo,/disk4/log4b.rdo) size 1m;4.adding online redo log members sql; alter database add logfile member sql; /disk3/log1b.rdo to group 1, sql; /disk4/log2b.rdo to group 2;5.changes the name of the online redo logfile sql; alter database rename file c:/oracle/oradata/oradb/redo01.log sql; to c:/oracle/oradata/redo01.log;6.drop online redo log groups sql; alter database drop logfile group 3;7.drop online redo log members sql; alter database drop logfile member c:/oracle/oradata/redo01.log;8.clearing online redo log files sql; alter database clear unarchived logfile c:/oracle/log2a.rdo;9.using logminer analyzing redo logfiles a. in the init.ora specify utl_file_dir = b. sql; execute dbms_logmnr_d.build(oradb.ora,c:oracleoradblog); c. sql; execute dbms_logmnr_add_logfile(c:oracleoradataoradbredo01.log, sql; dbms_logmnr.new); d. sql; execute dbms_logmnr.add_logfile(c:oracleoradataoradbredo02.log, sql; dbms_logmnr.addfile); e. sql; execute dbms_logmnr.start_logmnr(dictfilename=;c:oracleoradblogoradb.ora); f. sql; select * from v$logmnr_contents(v$logmnr_dictionary,v$logmnr_parameters sql; v$logmnr_logs); g. sql; execute dbms_logmnr.end_logmnr;第二章：表空间管理1.create tablespaces sql; create tablespace tablespace_name datafile c:oracleoradatafile1.dbf size 100m, sql; c:oracleoradatafile2.dbf size 100m minimum extent 550k logging/nologging sql; default storage (initial 500k next 500k maxextents 500 pctinccease 0) sql; online/offline permanent/temporary extent_management_clause2.locally managed tablespace sql; create tablespace user_data datafile c:oracleoradatauser_data01.dbf sql; size 500m extent management local uniform size 10m;3.temporary tablespace sql; create temporary tablespace temp tempfile c:oracleoradatatemp01.dbf sql; size 500m extent management local uniform size 10m;4.change the storage setting sql; alter tablespace app_data minimum extent 2m; sql; alter tablespace app_data default storage(initial 2m next 2m maxextents 999);5.taking tablespace offline or online sql; alter tablespace app_data offline; sql; alter tablespace app_data online;6.read_only tablespace sql; alter tablespace app_data read only|write;7.droping tablespace sql; drop tablespace app_data including contents;8.enableing automatic extension of data files sql; alter tablespace app_data add datafile c:oracleoradataapp_data01.dbf size 200m sql; autoextend on next 10m maxsize 500m;9.change the size fo data files manually sql; alter database datafile c:oracleoradataapp_data.dbf resize 200m;10.Moving data files: alter tablespace sql; alter tablespace app_data rename datafile c:oracleoradataapp_data.dbf sql; to c:oracleapp_data.dbf;11.moving data files:alter database sql; alter database rename file c:oracleoradataapp_data.dbf sql; to c:oracleapp_data.dbf;第三章：表1.create a table sql; create table table_name (column datatype,column datatype.) sql; tablespace tablespace_name pctfree integer pctused integer sql; initrans integer maxtrans integer sql; storage(initial 200k next 200k pctincrease 0 maxextents 50) sql; logging|nologging cache|nocache2.copy an existing table sql; create table table_name logging|nologging as subquery3.create temporary table sql; create global temporary table xay_temp as select * from xay; on commit preserve rows/on commit delete rows4.pctfree = (average row size - initial row size) *100 /average row size pctused = 100-pctfree- (average row size*100/available data space)5.change storage and block utilization parameter sql; alter table table_name pctfree=30 pctused=50 storage(next 500k sql; minextents 2 maxextents 100);6.manually allocating extents sql; alter table table_name allocate extent(size 500k datafile c:/oracle/data.dbf);7.move tablespace sql; alter table employee move tablespace users;8.deallocate of unused space sql; alter table table_name deallocate unused keep integer9.truncate a table sql; truncate table table_name;10.drop a table sql; drop table table_name cascade constraints;11.drop a column sql; alter table table_name drop column comments cascade constraints checkpoint 1000; alter table table_name drop columns continue;12.mark a column as unused sql; alter table table_name set unused column comments cascade constraints; alter table table_name drop unused columns checkpoint 1000; alter table orders drop columns continue checkpoint 1000 data_dictionary : dba_unused_col_tabs第四章：索引1.creating function-based indexes sql; create index summit.item_quantity on summit.item(quantity-quantity_shipped);2.create a B-tree index sql; create unique index index_name on table_name(column,. asc/desc) tablespace sql; tablespace_name pctfree integer initrans integer maxtrans integer sql; logging | nologging nosort storage(initial 200k next 200k pctincrease 0 sql; maxextents 50);3.pctfree(index)=(maximum number of rows-initial number of rows)*100/maximum number of rows4.creating reverse key indexes sql; create unique index xay_id on xay(a) reverse pctfree 30 storage(initial 200k sql; next 200k pctincrease 0 maxextents 50) tablespace indx;5.create bitmap index sql; create bitmap index xay_id on xay(a) pctfree 30 storage( initial 200k next 200k sql; pctincrease 0 maxextents 50) tablespace indx;6.change storage parameter of index sql; alter index xay_id storage (next 400k maxextents 100);7.allocating index space sql; alter index xay_id allocate extent(size 200k datafile c:/