oracle数据库实践笔记.docx

1、oracle数据库实践笔记1. oracle 结构2. oracle servera) instance(例程)i. 内存结构1. SGA系统全局区域，数据被多会话使用共享a) Shared pooli. Library cache（是代码）1. Sql区02. pl /sql区（后台编程,oracle对sql的扩展） t-sql sql.plii. data dictionary cache（数据字典，像注册表一样重要，是系统数据。Dict是表名和注释的表）1. 数据字典视图（静态）（来自数据文件）a) USER_做前缀：当前用户是所有者如USER_TABLES,UESR_INDEXESb)

2、 ALL_做前缀： 当前用户能访问到的对象c) DBA_做前缀：管理员2. 动态性能视图（来自内存）a) V$做前缀如V$SGAINFOb) Database buffer cache缓存数据c) Redo log buffer cache（log：日志，记录事务）i. 事务：1. A: 原子性2. C:一致性3. I:隔离性4. D:持久性2. PGA专用区域，被某特定会话专用（对堆表进行排序处理等操作的内存）ii. 进程结构1. 用户进程（界面）2. 服务器进程a) 由名称解析找到相应的服务器进程b) 解析的是服务名，找到监听程序，进而找到相应进程c) 分为专用服务器进程（只为一个用户）和

3、共享服务器进程（为很多用户服务，与用户之间存在调度系统）3. 后台进程a) PMON进程监视进程（监视进程异常终止，类似垃圾回收）b) SMON系统监视进程（恢复例程）c) DBWR数据库书写器（将数据缓存写入数据文件即内存到外存）d) LGWR日志书写器（将日志缓存写入日志文件即内存到外存）e) CKPT检查点进程（检查点：1同步所有数据文件2同步所有控制文件3通知DBWR进程将内存写入文件中）f) Othersb) database(文件集合)i. 分为内部文件和外部文件1. 内部文件：数据库用的文件，可能会随时变化2. 外部文件：配置，不主动修改则不会变3. SQLa) QL(SELEC

4、T)b) DML(INSERT;UPDATE;DELETE;MERGE(INSERT+UPDATE)c) DDL数据定义语言(CREATE,ALTER,DROP,TRUNCATE,RENAME,COMMENT)d) DCL数据控制语言，控制数据的访问(GRANT;REVOKE)e) TCL事务控制(COMMIT;ROLLBACK;SAVEPOINT)4. Dual是空表如SELECT 3+4 FROM DUAL;SELECT SYSDATE FROM DUAL;5. ORACLE 双引号表示名称，单引号是字符串如SELECT A“B” FROM DUAL;6. 单引号中加单引号，用两个连续单引

5、号表示7. SELECT 3+4 FROM DUAL;结果还是7，因为加号只表示运算，字符串连接用|8. SQL 大小写不敏感：大小写结果相同，但是解析过程有所不同。只要有一点不一样，hash_value就不一样，就会重新解析。但是plan_hash_value一样9. Oltp: similar olap:exact10. Help index11. c:demo.sql调用硬盘中的命令12. Edit + run 修改缓存中的指令，、也是运行13. Distinct和unique一样14. Between and 包含临界点15. 通配符：a) _:一个b) %:0或多个c) Escape

6、：AB$_% ESCAPE $16. 排序：默认升序(ASC)，DESC是降序。Order by 数字（第几列）a) Order department, salary desc前面的升序，相同的department 按salary降序17. Sql reference参考书18. SUBSTR(DSAF,2,2); SUBSTR(DSAF,-2,2)19. INSTR(DSAFSS, S, 1,2):从第一个开始，第二次出现的20. select lpad( ,(level-1)*2)|ename ename,level from empstart with empno=7839connect

7、 by prior empno=mgr从empno=7839开始，（你的）empno=（别人的）mgr21. Show pagesize; set pagesize 20;22. cname formate a2023. Startup mount24. Alter database open25. Select sysdate from dual;26. Nls:和国家语言有关27. alter session set nls_date_format=yyyy-mm-dd hh:mi:ss;28. alter session set nls_date_format=yyyy-mm-dd hh

8、24:mi:ss;29. alter session set nls_date_format=yyyy-mm-dd hh:mi:ss am;30. select current_date from dual;31. alter session nls_language=English;32. select NEXT_DAY(01-SEP-12, 5) from dual;33. select LAST_NAME,TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12) AS YEAR, TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,HIRE_D

9、ATE),12) AS MONTHfrom employees order by YEAR DESC,MONTH DESC34. trunc:截取 round:舍入35. $:美元 L:当地货币36. 修改地区：NLS_TERRITORY37. select * from nls_session_parameters;38. alter session set NLS_TERRITORY=;39. alter table tp add birth date40. update tp set birth=to_date(94-10-05,yy-mm-dd) where xxx41. RR日期：离

10、现在最相近的那一年42. 处理空值：nvl(comm,0)：comm.若为nill则显示0nvl2(comm,sal,sal+comm)：comm若为nill显示sal，若不为null显示sal+comm.43. Select count(*) total, sum(decode(to_char(hire_date,yyyy),2001,1,0) “2001”From employees;计数44. 设计sql 基于属性检查sql 基于元组45. STDDEV: VARIANCE:46. Select greatest(1,3,5,7,9)47. Select least(1,3,5,7,9)

11、 48. Group by rollup(dep_id,job_id)按dep_id小计49. Group by Cube(dep_id,job_id)按两列小计50. 内部连接select col from table1,table2 where xx=xxx;（SQL2）a) 嵌套循环法小表放右边b) 能筛选掉最多元组的条件放后面51. 内部连接select col from table1 inner join table2 on xx=xxx;（SQL3 INNER可忽略）52. 外部连接（左、右、全）（SQL2）a) 左select col from table1,table2 whe

12、re xx=xxx（+）;显示左边全部b) 右select col from table1,table2 where xx（+）=xxx;显示右边全部c) （+）只能放一边53. 外部连接（左、右、全）（SQL3）a) Sql3 select col from table1,left outer join table2 on xx=xxx(outer可忽略)b) Sql3 select col from table1,right outer join table2 on xx=xxx(outer可忽略)c) Sql3 select col from table1,full outer join

13、 table2 on xx=xxx(outer可忽略)54. 多表查询a) Select cols from buyers b, sales s, product p where xx=xx,and xx=xxb) Select cols from buyers b join sales son xx=xx joinproduct p on xx=xx55. 自连接56. 交叉连接57. PL/SQLa) 块结构i. 匿名块ii. 命名块b) 声明部分c) 可执行部分d) 异常处理e) Declearf) 声明g) Beginh) 可执行i) Exceptionj) 异常处理k) Endl)

14、插入：i. Begin 1. For i in 1.10 loopa) Insert into t1 values(i);2. End loop;ii. Endiii. 运行时还要在最后加/m) 存储过程：i. Creat or replace procedure p_insertii. Asiii. 可加声明iv. Begin 1. For i in 1.10 loopa) Insert into t1 values(i);2. End loop;v. End;vi. /vii. 调用：Execute p_insertviii. 调用：call p_insert();注意括号58. sele

15、ct a.aid,b.aid from t1 a , t1 b where a.aid=b.aid(+)-round(select count(*) from t1)/2) and a.aid=round(select count(*) from t1)/2)/59. select xxx from xxx where empno=&工号60. select * from emp where ename=&xingming;为了不用用户输入引号，编程时加引号61. DEFINE GH=7369select * from emp where ename=&gh;62. undefine gh63

16、. SELECT EMPNO.XX.&C FROM EMP ORDER BY &C会将c定义成值需要undefined来修改64. Show fedineSet define !65. Alter table t10 modify name defaultzhangsan;修改默认66. Grant select on t10 to scott67. DDL DCL自动提交68. Exit正常退出也会提交69. 回滚段作用：a) 读一致性（来自回滚段）b) 回滚c) Select *from t10 as of timestamp(systimestamp-interval5minute)找回

17、闪回恢复70. Desc v$lock71. Select sid from v$lock where block=072. Select sid,serial# from v$ssion where sid=xxx73. Alter system kill session 200,65774. 表明规则75. 表和索引名称空间不同76. select object_name,object_type,namespace from user_objects;77. grant create session to wang;78. grant create table to wang;79. 建表

18、的时候不分配空间使用的时候再分配80. Alter user wang quota 5m on users;81. Grant select scott.emp to wang82. 伪列 pseudocolumn不依赖表的一列83. Oracle1数据字典2包84. Desc dbms_rowid85. Select empno dbms_rowid.ROW_REALTIVE_FNO(rowid) file# 86. Select file#,name from v$datafile where file#=4;87. Varchar省空间88. Char：比较的时候性能优89. Renam

19、e xx to x90. Alter table xxx add xxx char91. Alter table xx rename col xxx to xxx92. Alter table xxx modify xxx number(20);93. Alter table xxx drop column name;94. Conn / as sysdba95. Truncate 清空表中数据(DDL)快 不能回滚 释放空间96. Delete 删除一行（DML）慢 能回滚 空间不释放（High Water Marker）97. 修改列的类型，列必须为空98. comment on tabl

20、e xxx is balabala;99. select comments from user_tab_commentswhere table_name=fs;100. 数据字典列大写101. 约束102. 数据完整性103. DESC USER_CONS_COLUMNS;104. alter table testadd constraint uni_c2 unique(c2);105. 视图：a) 简化查询b) 屏蔽敏感数据c) 简化权限管理106. create view e_infoas select empno,ename,sal from emp;107. create or rep

21、lace view e_infoas select empno,ename,sal ,deptno from emp;108. 若删掉视图之后重建，则原来的用户对视图的权限都丢失109. 可能表还没建好就要建视图，则create force view e_infoas select empno,ename,sal from emp;110. create force view e_infoas select empno,ename,sal from empwhere deptno=30 with check option;111. create force view e_infoas sele

22、ct empno,ename,sal from empwhere deptno=30 with read only;112. 视图中如果没有表中not null列，则不能插入113. Select xx,xxx, from (select * from emp order by sal desc) where rownum=10;115. create sequence s2start with20increament by -1maxvalue 20minvalue 0cyclecache 4116. Gaps in sequence values can occur when: A rol

23、lback occurs The system crashes A sequence is used in another table117. 小表不适合建索引118. 大表视返回元组数占全体比例而定119. A unique index is createdautomatically when you define a PRIMARY KEY orUNIQUE constraint in a table definition.120. Desc user_ind_columns;121. 删除前监视索引V$object_uasge 122. 例程管理a) 例程关闭shutdown immed

24、iateb) 启动历程 conn / as sysdba startup forcei. 例程启动 初始化参数文件startup nomountii. 加载控制文件 Alter datatase mount;iii. 找到数据文件和日志文件，这些在控制文件中描述了 alter DATABASE OPEN123. 初始化配置a) 初始化配置i. 文本文件pfile1. Init(sid).ora如initora11g.oraii. 二进制文件spfile1. Spfile(sid).ora如initora11g.orab) 查询初始化参数i. show parameter;ii. show pa

25、rameter shared_pool_size;c) 修改初始化参数（二进制文件不要用文本编辑器修改）d) 将初始化参数恢复默认值 Alter systrm reset xxxx;e) 修复出错的初始化参数：如果参数修改错了startup pfile=C:a.ora xxx是文件 内容是原来的pfile和要修改的数据f) Create pfile from spfile;g) 启动时现则初始化参数文件顺序i. Spfile(sid).oraii. Spfile.oraiii. Init(sid).oraiv. 指定初始化参数文件Startup pfile=xxxxh) Alert：C:app

26、Administratordiagrdbmsora11gora11gtracei) C:appAdministratorproduct11.2.0dbhome_1databaseSPFILEORA11G.ORAj) alter system set shared_pool_size=100g scope=spfile;124. 创建数据库a) 创建服务（例程）oradim new sid seu 出现oracleserviceseu服务（任务管理器中）cdb) 创建编辑初始化参数文件c) 创建相应的目录记录（看ora文件里面的路径）d) 启动例程Set oracle_sid=seu sqlpl

27、us startupe) 执行创建数据库的语句i. 数据文件：ii. create database sleviniii. datafile C:appAdministratororadataslevinSYSTEM01.DBF size 100miv. sysaux datafile C:appAdministratororadataslevinSYSAUX01.DBF size 100mv. undo tablespace undotbs1 datafile C:appAdministratororadataslevinundotbs01.dbf size 50mvi. default t

28、emporary tablespace temp tempfile C:appAdministratororadataslevintemp01.DBF size 30mvii. logfileviii. group 1 (C:appAdministratororadataslevinredo01.log) size 10240k,ix. group 2 (C:appAdministratororadataslevinredo02.log) size 10240k,x. group 3 (C:appAdministratororadataslevinredo03.log) size 10240k

29、;f) 创建数据字典（调用脚本）i. ?rdbmsadmincatalogg) 创建内部包?rdbmsadmincatproch) 创建spfile: create spfile from pfilei) 创建scott方案i. ?rdbmsadminscottii. Alter user scott identified by tiger;j) 加载产品用户概要信息（配置文件）i. ?sqlplusadminpupbld(先连接system)k) startup upgradel) lsnrctl status125. 维护控制文件a) 控制文件是二进制文件b) 控制文件内容show par

30、ameter controli. Select name from v$controlfilec) 查看 select name from v$contrilfiled) Oradebug setmypide) Oradebug dump control 3f) 控制文件复用g) 创建控制文件 alter database backup controlfile to trace 创建生成控制文件的语句h) ALTER SYSTEM SET control_files =i) C:appAdministratororadataora11gCONTROL01.CTL,j) C:appAdministratorflash_recovery_areaora11gcontrol02.ctl,k) C:CONTBAKCONTROL03.CTL SCOPE=SPFILE;l) 控制文件恢复m) STARTUP NOMOU