oracle数据库实践笔记.docx
《oracle数据库实践笔记.docx》由会员分享，可在线阅读，更多相关《oracle数据库实践笔记.docx（18页珍藏版）》请在冰豆网上搜索。
oracle数据库实践笔记
1.oracle结构
2.oracleserver
a）instance（例程）
i.内存结构
1.SGA系统全局区域，数据被多会话使用共享
a）Sharedpool
i.Librarycache（是代码）
1.Sql区0
2.pl/sql区（后台编程,oracle对sql的扩展）t-sqlsql.pl
ii.datadictionarycache（数据字典，像注册表一样重要，是系统数据。
Dict是表名和注释的表）
1.数据字典视图（静态）（来自数据文件）
a）USER_做前缀：
当前用户是所有者如USER_TABLES,UESR_INDEXES
b）ALL_做前缀：
当前用户能访问到的对象
c）DBA_做前缀：
管理员
2.动态性能视图（来自内存）
a）V$做前缀如V$SGAINFO
b）Databasebuffercache缓存数据
c）Redologbuffercache（log：
日志，记录事务）
i.事务：
1.A:
原子性
2.C:
一致性
3.I:
隔离性
4.D:
持久性
2.PGA专用区域，被某特定会话专用（对堆表进行排序处理等操作的内存）
ii.进程结构
1.用户进程（界面）
2.服务器进程
a）由名称解析找到相应的服务器进程
b）解析的是服务名，找到监听程序，进而找到相应进程
c）分为专用服务器进程（只为一个用户）和共享服务器进程（为很多用户服务，与用户之间存在调度系统）
3.后台进程
a）PMON进程监视进程（监视进程异常终止，类似垃圾回收）
b）SMON系统监视进程（恢复例程）
c）DBWR数据库书写器（将数据缓存写入数据文件即内存到外存）
d）LGWR日志书写器（将日志缓存写入日志文件即内存到外存）
e）CKPT检查点进程（检查点：
1同步所有数据文件2同步所有控制文件3通知DBWR进程将内存写入文件中）
f）Others
b）database（文件集合）
i.分为内部文件和外部文件
1.内部文件：
数据库用的文件，可能会随时变化
2.外部文件：
配置，不主动修改则不会变
3.SQL
a）QL（SELECT）
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是空表如SELECT3+4FROMDUAL;SELECTSYSDATEFROMDUAL;
5.ORACLE双引号表示名称，单引号是字符串如SELECT‘A’“B”FROMDUAL;
6.单引号中加单引号，用两个连续单引号表示
7.SELECT3+’4’FROMDUAL;结果还是7，因为加号只表示运算，字符串连接用||
8.SQL大小写不敏感：
大小写结果相同，但是解析过程有所不同。
只要有一点不一样，hash_value就不一样，就会重新解析。
但是plan_hash_value一样
9.Oltp:
similarolap:
exact
10.Helpindex
11.@c:
\demo.sql调用硬盘中的命令
12.Edit+run修改缓存中的指令，、也是运行
13.Distinct和unique一样
14.Betweenand包含临界点
15.通配符：
a）_:
一个
b）%:
0或多个
c）Escape：
’AB$_%’ESCAPE‘$’
16.排序：
默认升序（ASC），DESC是降序。
Orderby数字（第几列）
a）Orderdepartment,salarydesc前面的升序，相同的department按salary降序
17.Sqlreference参考书
18.SUBSTR（‘DSAF’,2,2）;SUBSTR（‘DSAF’,-2,2）
19.INSTR（‘DSAFSS’,’S’,1,2）:
从第一个开始，第二次出现的
20.selectlpad（'',（level-1）*2）||enameename,levelfromemp
startwithempno=7839
connectbypriorempno=mgr
从empno=7839开始，（你的）empno=（别人的）mgr
21.Showpagesize;setpagesize20;
22.cnameformatea20
23.Startupmount
24.Alterdatabaseopen
25.Selectsysdatefromdual;
26.Nls:
和国家语言有关
27.altersessionsetnls_date_format='yyyy-mm-ddhh:
mi:
ss';
28.altersessionsetnls_date_format='yyyy-mm-ddhh24:
mi:
ss';
29.altersessionsetnls_date_format='yyyy-mm-ddhh:
mi:
ssam';
30.selectcurrent_datefromdual;
31.altersessionnls_language=English;
32.selectNEXT_DAY（’01-SEP-12’,5）fromdual;
33.selectLAST_NAME,
TRUNC（MONTHS_BETWEEN（SYSDATE,HIRE_DATE）/12）ASYEAR,
TRUNC（MOD（MONTHS_BETWEEN（SYSDATE,HIRE_DATE）,12））ASMONTH
fromemployees
orderbyYEARDESC,MONTHDESC
34.trunc:
截取round:
舍入
35.$:
美元L:
当地货币
36.修改地区：
NLS_TERRITORY
37.select*fromnls_session_parameters;
38.altersessionsetNLS_TERRITORY=…;
39.altertabletpaddbirthdate
40.updatetpsetbirth=to_date（’94-10-05’,’yy-mm-dd’）wherexxx
41.RR日期：
离现在最相近的那一年
42.处理空值：
nvl（comm,0）：
comm.若为nill则显示0
nvl2（comm,sal,sal+comm）：
comm若为nill显示sal，若不为null显示sal+comm.
43.Selectcount（*）total,sum（decode（to_char（hire_date,’yyyy’）,’2001’,1,0））“2001”
Fromemployees;计数
44.设计sql基于属性检查sql基于元组
45.STDDEV:
VARIANCE:
46.Selectgreatest（1,3,5,7,9）
47.Selectleast（1,3,5,7,9）
48.Groupbyrollup（dep_id,job_id）按dep_id小计
49.GroupbyCube（dep_id,job_id）按两列小计
50.内部连接selectcolfromtable1,table2wherexx=xxx;（SQL2）
a）嵌套循环法小表放右边
b）能筛选掉最多元组的条件放后面
51.内部连接selectcolfromtable1innerjointable2onxx=xxx;（SQL3INNER可忽略）
52.外部连接（左、右、全）（SQL2）
a）左selectcolfromtable1,table2wherexx=xxx（+）;显示左边全部
b）右selectcolfromtable1,table2wherexx（+）=xxx;显示右边全部
c）（+）只能放一边
53.外部连接（左、右、全）（SQL3）
a）Sql3selectcolfromtable1,leftouterjointable2onxx=xxx（outer可忽略）
b）Sql3selectcolfromtable1,rightouterjointable2onxx=xxx（outer可忽略）
c）Sql3selectcolfromtable1,fullouterjointable2onxx=xxx（outer可忽略）
54.多表查询
a）Selectcolsfrombuyersb,saless,productpwherexx=xx,andxx=xx
b）Selectcolsfrombuyersbjoinsalessonxx=xxjoinproductponxx=xx
55.自连接
56.交叉连接
57.PL/SQL
a）块结构
i.匿名块
ii.命名块
b）声明部分
c）可执行部分
d）异常处理
e）Declear
f）声明
g）Begin
h）可执行
i）Exception
j）异常处理
k）End
l）插入：
i.Begin
1.Foriin1..10loop
a）Insertintot1values（i）;
2.Endloop;
ii.End
iii.运行时还要在最后加/
m）存储过程：
i.Creatorreplaceprocedurep_insert
ii.As
iii.可加声明
iv.Begin
1.Foriin1..10loop
a）Insertintot1values（i）;
2.Endloop;
v.End;
vi./
vii.调用：
Executep_insert
viii.调用：
callp_insert（）;注意括号
58.selecta.aid,b.aid
fromt1a,t1b
wherea.aid=b.aid（+）-round（（selectcount（*）fromt1）/2）
anda.aid<=round（（selectcount（*）fromt1）/2）
/
59.selectxxxfromxxxwhereempno=&工号
60.select*fromempwhereename='&xingming';
为了不用用户输入引号，编程时加引号
61.DEFINEGH=7369
select*fromempwhereename=&gh;
62.undefinegh
63.SELECTEMPNO.XX.&&CFROMEMPORDERBY&C会将c定义成值需要undefined来修改
64.Showfedine
Setdefine!
65.Altertablet10modifynamedefault’zhangsan’;修改默认
66.Grantselectont10toscott
67.DDLDCL自动提交
68.Exit正常退出也会提交
69.回滚段作用：
a）读一致性（来自回滚段）
b）回滚
c）Select*fromt10asoftimestamp（systimestamp-interval’5’minute）找回闪回恢复
70.Descv$lock
71.Selectsidfromv$lockwhereblock=0
72.Selectsid,serial#fromv$ssionwheresid=xxx
73.Altersystemkillsession‘200,657’
74.表明规则
75.表和索引名称空间不同
76.selectobject_name,object_type,namespacefromuser_objects;
77.grantcreatesessiontowang;
78.grantcreatetabletowang;
79.建表的时候不分配空间使用的时候再分配
80.Alteruserwangquota5monusers;
81.Grantselectscott.emptowang
82.伪列pseudocolumn不依赖表的一列
83.Oracle1数据字典2包
84.Descdbms_rowid
85.Selectempnodbms_rowid.ROW_REALTIVE_FNO（rowid）file#
86.Selectfile#,namefromv$datafilewherefile#=4;
87.Varchar省空间
88.Char：
比较的时候性能优
89.Renamexxtox
90.Altertablexxxaddxxxchar
91.Altertablexxrenamecolxxxtoxxx
92.Altertablexxxmodifyxxxnumber（20）;
93.Altertablexxxdropcolumnname;
94.Conn/assysdba
95.Truncate清空表中数据（DDL）快不能回滚释放空间
96.Delete删除一行（DML）慢能回滚空间不释放（HighWaterMarker）
97.修改列的类型，列必须为空
98.commentontablexxxis'balabala';
99.selectcommentsfromuser_tab_comments
wheretable_name='fs';
100.数据字典列大写
101.约束
102.数据完整性
103.DESCUSER_CONS_COLUMNS;
104.altertabletest
addconstraintuni_c2unique（c2）;
105.视图：
a）简化查询
b）屏蔽敏感数据
c）简化权限管理
106.createviewe_info
asselectempno,ename,salfromemp;
107.createorreplaceviewe_info
asselectempno,ename,sal,deptnofromemp;
108.若删掉视图之后重建，则原来的用户对视图的权限都丢失
109.可能表还没建好就要建视图，则
createforceviewe_info
asselectempno,ename,salfromemp;
110.createforceviewe_info
asselectempno,ename,salfromemp
wheredeptno=30withcheckoption;
111.createforceviewe_info
asselectempno,ename,salfromemp
wheredeptno=30withreadonly;
112.视图中如果没有表中notnull列，则不能插入
113.Selectxx,xxx,from（select*fromemporderbysaldesc）whererownum<=3;
114.Selectrownum,empnoename,from（selectrownuma,emp.*fromemp）wherea>=10;
115.createsequences2
startwith20
increamentby-1
maxvalue20
minvalue0
cycle
cache4
116.Gapsinsequencevaluescanoccurwhen:
–Arollbackoccurs
–Thesystemcrashes
–Asequenceisusedinanothertable
117.小表不适合建索引
118.大表视返回元组数占全体比例而定
119.AuniqueindexiscreatedautomaticallywhenyoudefineaPRIMARYKEYorUNIQUEconstraintinatabledefinition.
120.Descuser_ind_columns;
121.删除前监视索引V$object_uasge
122.例程管理
a）例程关闭shutdownimmediate
b）启动历程conn/assysdbastartupforce
i.例程启动初始化参数文件startupnomount
ii.加载控制文件Alterdatatasemount;
iii.找到数据文件和日志文件，这些在控制文件中描述了alterDATABASEOPEN
123.初始化配置
a）初始化配置
i.文本文件pfile
1.Init（sid）.ora如initora11g.ora
ii.二进制文件spfile
1.Spfile（sid）.ora如initora11g.ora
b）查询初始化参数
i.showparameter;
ii.showparametershared_pool_size;
c）修改初始化参数（二进制文件不要用文本编辑器修改）
d）将初始化参数恢复默认值Altersystrmresetxxxx;
e）修复出错的初始化参数：
如果参数修改错了startuppfile='C:
\a.ora'xxx是文件内容是原来的pfile和要修改的数据
f）Createpfilefromspfile;
g）启动时现则初始化参数文件顺序
i.Spfile（sid）.ora
ii.Spfile.ora
iii.Init（sid）.ora
iv.指定初始化参数文件Startuppfile=xxxx
h）Alert：
C:
\app\Administrator\diag\rdbms\ora11g\ora11g\trace
i）C:
\app\Administrator\product\11.2.0\dbhome_1\database\SPFILEORA11G.ORA
j）altersystemsetshared_pool_size=100gscope=spfile;
124.创建数据库
a）创建服务（例程）oradim–new–sidseu出现oracleserviceseu服务（任务管理器中）cd\
b）创建编辑初始化参数文件
c）创建相应的目录记录（看ora文件里面的路径）
d）启动例程Setoracle_sid=seusqlplusstartup
e）执行创建数据库的语句
i.数据文件：
ii.createdatabaseslevin
iii.datafile'C:
\app\Administrator\oradata\slevin\SYSTEM01.DBF'size100m
iv.sysauxdatafile'C:
\app\Administrator\oradata\slevin\SYSAUX01.DBF'size100m
v.undotablespaceundotbs1datafile'C:
\app\Administrator\oradata\slevin\undotbs01.dbf'size50m
vi.defaulttemporarytablespacetemptempfile'C:
\app\Administrator\oradata\slevin\temp01.DBF'size30m
vii.logfile
viii.group1（'C:
\app\Administrator\oradata\slevin\redo01.log'）size10240k,
ix.group2（'C:
\app\Administrator\oradata\slevin\redo02.log'）size10240k,
x.group3（'C:
\app\Administrator\oradata\slevin\redo03.log'）size10240k;
f）创建数据字典（调用脚本）
i.@?
\rdbms\admin\catalog
g）创建内部包@?
\rdbms\admin\catproc
h）创建spfile:
createspfilefrompfile
i）创建scott方案
i.@?
\rdbms\admin\scott
ii.Alteruserscottidentifiedbytiger;
j）加载产品用户概要信息（配置文件）
i.@?
\sqlplus\admin\pupbld（先连接system）
k）startupupgrade
l）lsnrctlstatus
125.维护控制文件
a）控制文件是二进制文件
b）控制文件内容showparametercontrol
i.Selectnamefromv$controlfile
c）查看selectnamefromv$contrilfile
d）Oradebugsetmypid
e）Oradebugdumpcontrol3
f）控制文件复用
g）创建控制文件alterdatabasebackupcontrolfiletotrace创建生成控制文件的语句
h）ALTERSYSTEMSETcontrol_files=
i）'C:
\app\Administrator\oradata\ora11g\CONTROL01.CTL',
j）'C:
\app\Administrator\flash_recovery_area\ora11g\control02.ctl',
k）'C:
\CONTBAK\CONTROL03.CTL'SCOPE=SPFILE;
l）控制文件恢复
m）STARTUPNOMOU
升级会员 