入门基础——Oracle快速入门

1、入门基础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 或SHARED 2.修改/home/network/admin/SQLNET.ORA 文件 增加

2、 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.ora show parameter instance show para

3、meter db alter system set job_queue_processes=50 comment =temporaty change on scope = memory|spfile|both ; alter session set CONCAT(Good, String) GoodString SUBSTR(String,1,3) Str LENGTH(String) 6 INSTR(String, r) 3 LPAD(sal,10,*) *5000 union union all minus 第一个查询减去第二个查询 intersect 交集 SQL select repl

4、ace(itpub oracle,oracle,training) itpub from dual / ITPUB - itpub training Translate SQL select translate(This is second trianing of itpub in Beijing!,abcde,12345) itpub 2 from dual; ITPUB - This is s53on4 tri1ning of itpu2 in B5ijing! Floor SQL select floor(4.55) from dual; FLOOR(4.55) - 4 Ceil SQL

5、 select ceil(4.55) from dual; CEIL(4.55) - 5 ROUND: 4舍5入到指定的小数位 ROUND(45.926, 2) 45.93 TRUNC: 将值截断到指定的小数位 TRUNC(45.926, 2) 45.92 MOD: 返回相除后的余数 MOD(1600, 300) 100 select trunc(sysdate, D) - 3 from dual; trunc(sysdate, D), 截取到本周的第一天(0:00) trunc(sysdate, M), 截取到本月的第一天(0:00) trunc(sysdate, DD), 截取到本日的0:

6、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) - 2463 SQL select ename,sal, 2 case job WHEN MANAGER THEN 1.1*sal 3 WHEN ANALYST THEN 1.2*SAL 4 WHEN CLERK

7、THEN 1.5*SAL 5 ELSE sal END NEW_SAL 6 from emp; ENAME SAL NEW_SAL - - - SMITH 800 1200 ALLEN 1600 1600 作者： sunboy520555 2008-9-27 11:56 回复此发言 - 2 入门基础Oracle快速入门 WARD 1250 1250 JONES 2975 3272.5 MARTIN 1250 1250 BLAKE 2850 3135 CLARK 2450 2695 SCOTT 3000 3600 KING 5000 5000 TURNER 1500 1500 ADAMS 110

8、0 1650 JAMES 950 1425 FORD 3000 3600 MILLER 1300 1950 SQL SELECT job, sal, 2 DECODE(job, ANALYST SAL*1.1, 3 CLERK, SAL*1.15, 4 MANAGER, SAL*1.20, 5 SAL) 6 REVISED_SALARY 7 FROM emp; JOB SAL REVISED_SALARY - - - PRESIDENT 5000 5000 MANAGER 2850 3420 MANAGER 2450 2940 将空值转换为实际的值 数据格式可以是日期,字符,数字 数据类型必须

9、匹配 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, 在关闭数据库(使

10、用shutdown normal或shutdown immediate) 时将导致错误ORA -01116,ORA-01110以及操作系统级错误ORA-07368 解决: 以下有两种解决方案: 1.用户的表空间可以被轻易地重建 即最近导出的对象是可用的或表空间中的对象可以被轻易地重建等.在这种情况下,最简单的方法是offline并删除该数据文件,删除表空间并重建表空间以及所有的对象. svrmgrl startup mount svrmgrl alter database datafile filename offline drop; svrmgrl alter database open;

11、svrmgrl drop tablespace tablespace_name including contents; 重建表空间及所有对象. 2.用户的表空间不能够被轻易地重建 在大多数情况下,重建表空间是不可能及太辛苦的工作.方法是倒备份及做介质恢复.如果您的系统运行在NOARCHIVELOG模式下,则只有丢失的数据在online redo log中方可被恢复。 步骤如下: 1)Restore the lost datafile from a backup 2)svrmgrl startup mount 3)svrmgrl select v1.group#,member,sequence#

12、,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

13、 alter database open resetlogs; 3.只读表空间无需做介质恢复,只要将备份恢复即可.唯一的例外是: 表空间在最后一次备份后被改为read-write 模式 表空间在最后一次备份后被改为read-only 模式 作者： sunboy520555 2008-9-27 11:56 回复此发言 - 3 入门基础Oracle快速入门 在这种情况下,均需进行介质恢复 二、临时表空间 临时表空间并不包含真正的数据,恢复的方法是删除临时表空间并重建即可. 三、系统表空间 如果备份不可用,则只能采用重建数据库的方法 四、回滚表空间 有两种情况: 1、数据库已经完全关闭(使用shut

14、down 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 修改i

15、nit.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) s

16、vrmgrl 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

17、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

18、_id and b.file_name = 文件名全路径 and a.segment_type = TABLE job不能自动运行 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 b where a.object_id=b.object_id error: ora-03212 Tempo

19、rary 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 where segment_name = . db_block_s

20、ize 是参数 查询未提交事务 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 作者： sunboy520555 2008-9-27 11:56 回复此发言 - 4 入门基础Oracle快速入门 ord

21、er by USED_UBLK; 如何将数据库中被锁表解锁 我们在操作数据库的时候，有时候会由于操作不当引起数据库表被锁定，这么我们经常不知所措，不知怎么给这些表解锁，在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 Ex

22、cl., 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

23、 = 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 syste

24、m kill session sid,SERIAL#; 在SQLPLUS里： 1.不显示 行头 set heading off pages 0 2.不显示行数信息 set feedback off 3。不在屏幕上显示结果 set termout off 1.由于该问题由oarcle工程师定位为无法恢复，并在网上进行了求助也每人能解决，所以 我可以方下包袱，去大胆的尝试新办法，按照我对数据库的框架理解去寻求办法。 2.因为无法联机的表空空间为用户表空间，所以不影响数据库系统的启动，所以 通过 ：SQLstartup mount; SQLalter database open;启动数据库 3.我查看了sys.col#,sys.obj#表，想从表中得到关于datafile0016的信息，但没有能找 到我认为需要的信息； 4.后来我查看了sys.file#,发现了所有数据文件的记录，然后我找到对应的file0016的记录 ，想通过删除该记录来屏蔽表空间启动时的数据文件的完整性检测，于是现对sys.fi