Oracle SQL经典荟萃

?

Oracle SQL经典荟萃

将自己学习，工作中整理的一些经典SQL和大家分享一下。注意，在Oracle 9i中tiger账户默认是开启的，后续版本则改变了策略~同时，关于sql优化尤其是hint的使用，这里不做分享，具体可以查阅民间流传的50大招~

?

Sql代码?

1. --?toy?sql??
2. alter?user?scott?account?unlock;??
3. select?ename,sal*12?as?"annual?sal"?from?emp;??
4. --注意：""保持了字段名的小写状态??
5. select?ename,sal*12?+?comm?from?emp;??
6. select?job||'KaK''a'?from?emp;??
7. select?ename,sal*12?+?nvl(comm,0)?from?emp;??
8. --注意：任何含有空值的数学表达式，计算结果都是空值;字符串连接过程中，单引号使用'转义??
9. select?distinct?deptno,job?from?emp;??
10. --distinct?deptno,job组合唯一??
11. select?ename,sal?from?emp?where?deptno?<>10;??
12. --Oracle中日期型处理技巧。1.1-1月-1982?2。date'1982-01-01'??
13. select?ename,hiredate?from?emp?where?hiredate>'1-1月-1982';??
14. select?ename,hiredate?from?emp?where?hiredate>to_date('1982-1-1','yyyy-mm-dd');??
15. --SQL中不等于的方法??
16. select?ename,sal?from?emp?where?sal?between?800?and?1500;??
17. select?ename,sal?from?emp?where?sal?>=800?and?sal<=1500;??
18. --指定$为转义字符??
19. select?ename?from?emp?where?ename?like?'%$%%'?escape?'$';??

Sql代码?

1. select?lower(ename)?from?emp;??
2. select?upper(ename)?from?emp;??
3. select?substr(ename,1,3)?from?emp;??
4. select?*?from?emp?where?length(ename)=5;??
5. select?upper(substr(ename,1,1))?from?emp?||?select?lower(substr(ename,2,length(ename)-1))from?emp;??
6. select?chr(65)?from?dual;??
7. select?ascii('A')?from?dual;??
8. select?round(25.656,-1)?from?dual;??
9. --L,本地货币符号，C，国际货币符号??
10. select?to_char(sal,'$999,999.999')from?emp;??
11. select?to_char(sal,'L999,999.999')from?emp;??
12. select?to_char(sal,'L000,000.000')from?emp;??
13. select?to_char(hiredate,'YYYY-MM-MM?HH24:MI:SS')?from?emp;??
14. select?ename,hiredate?from?emp?where?hiredate?>?to_date('1981-02-20?12:00:00','YYYY-MM-DD?HH24:MI:SS');??
15. select?sal?from?emp?where?sal>to_number('$1,250.00','$99,999.99');??
16. select?replace(ename,'a','A')?from?emp;??
17. select?sys_context('USERENV','db_name')?from?dual;??
18. select?sys_context('USERENV','current_schema')?from?dual;??
19. --用户和方案的关系:一一对应,Oracle是以方案的方式管理数据对象的，用户名和方案名相同??

Sql代码?

1. select?deptno,job,max(sal)?as?max_sal?from?emp?group?by?deptno,job;??
2. --按照两个字段进行分组??
3. select?deptno,round(avg(sal),2)?from?emp?group?by?deptno;??
4. --注意：出现在select列表中的字段，如果没有出现在组函数里，那么必需出现在group?by语句里！??
5. --分组函数只能出现在选择列表，having，order?by子句中！??
6. select?ename,deptno?from?emp?where?sal?in?(select?max(sal)from?emp?group?by?deptno);??
7. select?ename?from?emp?where?sal?in?--=?(select?max(sal)?from?emp);??
8. select?deptno,max(sal)?from?emp?group?by?deptno;??
9. select?deptno,round(avg(sal))?from?emp?group?by?deptno?having?avg(sal)>2000?order?by?deptno;??
10. --注意：where是对单条语句过滤，而having是对分组进行过滤！??
11. select?ename,deptno?from?emp?order?by?deptno?desc;??
12. select?avg(sal)?from?emp?where?sal>1200?group?by?deptno?having?avg(sal)?>1500?order?by?avg(sal)?desc;??
13. select?*?from?product_component_version;??
14. select?ename,sal?from?emp?where?sal?>?(select?avg(sal)?from?emp);??
15. ??
16. --每个部门中薪水最高的人??
17. select?ename,deptno,sal?from?emp?where?sal?in?(select?max(sal)?from?emp?group?by?deptno);??
18. --仔细分析上述写法，在大数据量条件下，会出现偏差??
19. select?ename,sal,emp.deptno?from?emp?join?(select?max(sal)?as?max_sal,deptno?from?emp?group?by?deptno)?t?on(emp.sal?=?t.max_sal?and?emp.deptno?=?t.deptno);??
20. ??
21. select?t1.ename?as?clerk,t2.ename?as?boss?from?emp?t1?,emp?t2?where?t1.mgr?=?t2.empno;??
22. select?t1.ename?as?clerk,t2.ename?as?boss?from?emp?t1?join?emp?t2?on?(t1.mgr?=?t2.empno);??
23. ??
24. select?t1.ename?as?clerk,t2.ename?as?boss?from?emp?t1?left?outer?join?emp?t2?on?(t1.mgr?=?t2.empno);??
25. select?ename,dname?from?emp?right?outer?join?dept?on?(emp.deptno?=?dept.deptno);??
26. select?ename,dname,grade?from?emp?e,dept?d,salgrade?s?where?e.deptno?=?d.deptno?and?e.sal?between?s.losal?and?s.hisal?and?job?<>?'CLERK';??
27. --超强震撼，99语法将连接条件与数据过滤条件分离，仔细品味吧！??
28. select?ename,dname,grade?from?emp?join?dept?on?(?emp.deptno?=?dept.deptno?)?join?salgrade?on(emp.sal?between?salgrade.losal?and?salgrade.hisal)?where?ename?not?like?'_A%';??
29. ??
30. --SQL?99与SQL?92的区别?:?Where?语句后只跟数据过滤条件?！??
31. select?ename,dname?from?emp,dept;??
32. select?ename,dname?from?emp?cross?join?dept;??
33. --?等值连接??
34. select?ename,dname?from?emp,dept?where?emp.deptno?=?dept.deptno;??
35. select?ename,dname?from?emp?join?dept?on?(?emp.deptno?=?dept.deptno?);??
36. select?ename,dname?from?emp?join?dept?using?(deptno);??
37. --部门平均薪水的等级??
38. select?deptno,avg_sal,grade?from(select?deptno,avg(sal)?as?avg_sal?from?emp?group?by?deptno)?t?join?salgrade?s?on(t.avg_sal?between?s.losal?and?s.hisal);??
39. --部门平均的薪水等级??
40. select?deptno,avg(grade)?from(select?deptno,ename,grade?from?emp?join?salgrade?on(emp.sal?between?salgrade.losal?and?salgrade.hisal))?group?by?deptno;??
41. --雇员中谁是经理人??
42. select?ename?from?emp?where?empno?in?(select?distinct?mgr?from?emp);??
43. --不用聚集函数求最高薪水??
44. select?distinct?sal?from?emp?where?sal?not?in(select?distinct?e1.sal?from?emp?e1?join?emp?e2?on(e1.sal?<?e2.sal));??
45. --平均薪水最高的部门的部门编号??
46. select?deptno,avg_sal?from?(select?deptno,avg(sal)?avg_sal?from?emp?group?by?deptno)???
47. where?avg_sal?=(select?max(avg_sal)?from?(select?deptno,avg(sal)?avg_sal?from?emp?group?by?deptno));??
48. --同样的例子，使用聚集函数嵌套！??
49. select?deptno,avg_sal?from?(select?deptno,avg(sal)?avg_sal?from?emp?group?by?deptno)???
50. where?avg_sal?=(select?max(avg(sal))?from?emp?group?by?deptno);??
51. --平均薪水最高的部门的部门名称??
52. select?dname?from?dept?where?deptno?=?(select?deptno?from?(select?deptno,avg(sal)?avg_sal?from?emp?group?by?deptno)???
53. where?avg_sal?=(select?max(avg_sal)???
54. from?(select?deptno,avg(sal)?avg_sal?from?emp?group?by?deptno)));??
55. --平均薪水等级最低的部门的部门名称???
56. select?t1.deptno,dname,avg_sal,grade?from??
57. (??
58. ???select?deptno,grade,avg_sal?from??
59. ?????(??
60. ????????select?deptno,avg(sal)?as?avg_sal?from?emp?group?by?deptno??
61. ?????)?t?join?salgrade?s?on??
62. ?????????(??
63. ????????????t.avg_sal?between?s.losal?and?s.hisal??
64. ??????????)??
65. )?t1?join?dept?on???
66. ????(??
67. ???????t1.deptno?=?dept.deptno??
68. ????)?where?t1.grade?=??
69. ??????(??
70. ???????????select?min(grade)?from???
71. ????????????(??
72. ????????????????select?deptno,avg_sal,grade?from??
73. ?????????????????(??
74. ???????????????????select?deptno,avg(sal)?avg_sal?from?emp?group?by?deptno??
75. ?????????????????)?t?join?salgrade?s?on??
76. ???????????????????(??
77. ?????????????????????t.avg_sal?between?s.losal?and?s.hisal??
78. ???????????????????)??
79. ???????????)??
80. ????????);??
81. --显示高于自己部门平均工资的员工信息??
82. select?t1.ename,t1.sal,t1.deptno,t2.avg_sal?from?emp?t1,(select?deptno,avg(sal)?avg_sal?from?emp?group?by?deptno)?t2?where?t1.deptno?=?t2.deptno?and?t1.sal?>?t2.avg_sal;??
83. --另类做法??
84. --1.创建视图??
85. create?view?v$dept_avg_sal_info?as???
86. select?deptno,grade,avg_sal?from??
87. ?????(??
88. ????????select?deptno,avg(sal)?as?avg_sal?from?emp?group?by?deptno??
89. ?????)?t?join?salgrade?s?on??
90. ?????????(??
91. ????????????t.avg_sal?between?s.losal?and?s.hisal??
92. ??????????);??
93. --2.使用刚创建的视图进行查询???????????
94. select?t1.deptno,dname,avg_sal,grade?from??
95. (??
96. ???select?deptno,grade,avg_sal?from??
97. ?????(??
98. ????????select?deptno,avg(sal)?as?avg_sal?from?emp?group?by?deptno??
99. ?????)?t?join?salgrade?s?on??
100. ?????????(??
101. ????????????t.avg_sal?between?s.losal?and?s.hisal??
102. ??????????)??
103. )?t1?join?dept?on???
104. ????(??
105. ???????t1.deptno?=?dept.deptno??
106. ????)?where?t1.grade?=??
107. ??????(??
108. ???????????select?min(grade)?from?v$dept_avg_sal_info??
109. ??????);??
110. --比普通员工的最高薪水还高的经理人名称??
111. select?ename?from?emp?where?empno?in???
112. (?????
113. ????select?distinct?mgr?from?emp?where?mgr?is?not?null??
114. )??
115. ??and?sal?>??
116. ????(??
117. ??????select?max(sal)?from?emp?where?empno?not?in(select?distinct?mgr?from?emp?where?mgr?is?not?null)??
118. ?????);??
119. --备份表??
120. create?table?emp2?as?select?*?from?emp;??
121. grant?dba?to?scott;??
122. ??
123. grant?create?table,create?view?to?scott;??
124. ??
125. --逻辑备份??
126. --1.?exp??
127. --2.?create?user?**?identified?by?**?default?tablespace?users?quota?10M?on?users??
128. --3.?grant?create?session,create?table,create?view?to?**??
129. --4.?imp??
130. --另类insert??
131. insert?into?emp2?select?*?from?emp;??
132. ??
133. select?ename?from?(select?rownum?r?,ename?from?emp)?where?r?>?10;??
134. --薪水最高的前5名雇员??
135. --错误的写法！（错误原因：先取出前5个，才排序）??
136. select?ename,sal?from?emp?where?rownum?<=5?order?by?sal?desc;??
137. --正确的写法！??
138. select?ename,sal?from?(select?ename,sal?from?emp?order?by?sal?desc)?where?rownum?<=5;??
139. ??
140. --薪水最高的第6个到第10个人??
141. select?ename,sal,rownum?r?from?(select?ename,sal,rownum?r?from(select?ename,sal?from?emp?order?by?sal?desc))where?r<=10?and?r>=6;??
142. --hibernate?写法??
143. select?*?from?(?select?row_.*,?rownum?rownum_?from?(?select?ename,sal?from?emp?order?by?sal?desc)?row_?where?rownum?<=?10)?where?rownum_?>=6;??
144. --显示比部门30的所有员工的工资都高的员工姓名，工资和部门号??
145. select?ename,sal,deptno?from?emp?where?sal>?all--any?(select?sal?from?emp?where?deptno=30);??
146. select?ename,sal,deptno?from?emp?where?sal?>(select?max(sal)?from?emp?where?deptno?=30);--效率高??
147. --查询与SMITH的部门和岗位完全相同的所有员工??
148. select?*?from?emp?where?(deptno,job)=(select?deptno,job?from?emp?where?ename='SMITH');??
149. --希望SCOTT员工的岗位，工资，津贴与SMITH员工一样??
150. update?emp?set(job,sal,comm)=(select?job,sal,comm?from?emp?where?ename='SMITH')?where?ename='SCOTT';??
151. --查询8个月前入职的员工??
152. select?*?from?emp?where?sysdate>add_months(hiredate,8);??
153. --显示满10个月服务年限的员工姓名和受雇日期??
154. select?ename,hiredate?from?emp?where?sysdate>=add_months(hiredate,12*10);??
155. --显示每个雇员入职天数??
156. select?ename,floor(sysdate-hiredate)?as?'入职天数'?from?emp;??
157. --各月倒数第3天受雇的员工??
158. select?*?from?emp?where?last_day(hiredate)-2=hiredate;??
159. --SQL注入漏洞??
160. select?*?from?emp?where?empno='7369'?and?ename='SMITH'?or?1='1';??
161. --MS?SQL中起作用??
162. select?*?from?emp?where?empno='7369';delete?from?emp;--'and?ename='SMITH';??
163. --查询员工信息（包括其直接上司）??
164. select?e1.empno,e1.ename,e1.mgr,e2.ename?from?emp?e1?left?join?emp?e2?on?e1.mgr=e2.empno???
165. select?e1.empno,e1.ename,e1.mgr,(select?e2.ename?from?emp?e2?where?e2.empno?=?e1.mgr)?as?mgrname?from?emp?e1??
166. --查询员工7902的所有上级??
167. select?empno,ename,level?from?emp?connect?by?empno?=?PRIOR?mgr?start?with?empno?=?7902;??
168. --查询员工7902的所有下级??
169. select?empno,ename,level?from?emp?connect?by?PRIOR?empno?=?mgr?start?with?empno?=?7902;??
170. --使用rowid删除重复项，保留rowid最大一项，模式如：??
171. select?rowid,t.product_type,t.order_datetime?from?tb_order_view?t?where?rowid?!=?(select?max(rowid)from?tb_order_view?b?where?b.product_type?=?t.product_type?and?b.order_datetime?=?t.order_datetime)??
172. --case...when...then??
173. select?e1.empno,??
174. ???????e1.ename,??
175. ???????e1.job,??
176. ???????e1.hiredate,??
177. ???????e1.sal,??
178. ???????case??
179. ?????????when?e1.sal?<?1000?then??
180. ??????????'低'??
181. ?????????else??
182. ??????????(case??
183. ????????????when?e1.sal?<=?3000?then??
184. ?????????????'中'??
185. ????????????else??
186. ?????????????'高'??
187. ??????????end)??
188. ???????end?as?grade??
189. ??from?emp?e1??
190. ????
191. select?count(case?when?sal<1000?then?1?else?null?end)low,??
192. ???????count(case?when?sal?between?1000?and?3000?then?1?else?null?end)mid,??
193. ???????count(case?when?sal?>3000?then?1?else?null?end)?high?from?emp??
194. ??
195. ??
196. --数据库完整性实现方式之-约束（5种）,包括列级定义，表级定义??
197. --1.非空?2.唯一?3.主键?4.外键?5.Check??
198. create?table?stu(??
199. id?number(6),??
200. name?varchar2(20)?constraint?stu_name_nn--自定义约束名字?not?null,??
201. sex?char(2)?default?'男'?check(sex?in('男','女')),??
202. age?number(3)?check(age>0),??
203. sdate?date,??
204. grade?number(2)?default?1,??
205. class?number(4)?--references?class(id),??
206. email?varchar2(50),??
207. constraint?stu_fk?foreign?key?(class)?references?class(id),??
208. constraint?stu_id_pk?primary?key(id),??
209. constraint?stu_unique?unique(email,name)???
210. );??
211. create?table?class(??
212. id?number(4)?primary?key,??
213. name?varchar2(20)??
214. );??
215. alter?table?class?modify?name?not?null;??
216. alter?table?class?add?constraint?nameUnique?unique(name);??
217. select?constraint_name,constraint_type,status,validated?from?user_constraints?where?table_name='emp';??
218. select?column_name,position?from?user_cons_columns?where?constraint_name='nameUnique';??
219. --null值，数据库不认为是重复值??
220. ??
221. --表修改??
222. alter?table?stu?add(addr?varchar2(100));??
223. alter?table?stu?drop(addr);??
224. alter?table?stu?drop?column?addr;??
225. rename?stu?to?student;--修改表名字??
226. alter?table?stu?modify(addr?varchar2(50));--修改字段长度或类型（空表）??
227. --约束条件操作??
228. alter?table?stu?drop?constraint?stu_fk;??
229. alter?table?stu?add?constraint?stu_fk?foreign?key?(class)?references?class(id);??
230. ??
231. --数据字典表??
232. desc?user_tables;??
233. select?table_name?from?user_tables;??
234. select?view_name?from?user_views;??
235. select?constraint_name?from?user_constraints;??
236. select?constraint_name,table_name?from?user_constraints;??
237. ??
238. desc?dictionary;??
239. select?table_name?from?dictionary;??
240. ??
241. --索引与视图(主键约束和唯一约束)??
242. create?index?index_stu_email?on?stu(email);--也可以设置复合索引，注意顺序不同（区分度高的字段放后面），索引不同，索引的层次不要超过4层。??
243. drop?index?index_stu_email;??
244. select?index_name?from?user_indexes;??
245. select?view_name?from?user_views;??
246. --索引的缺点。1.系统需要为索引开辟大约为表存储容量1.2倍的空间（硬盘空间和内存空间）。2.更新数据时，系统需要额外的时间来对索引进行同步更新。??
247. select?index_name,index_type?from?user_indexes?where?table_name='emp';??
248. select?table_name,column_name?from?user_id_columns?where?index_name='emp';??
249. ??
250. create?sequence?seq;??
251. select?seq.nextval?from?dual;??
252. ??
253. create?view?v$view_table?as?select?M.column_name?FieldName?from?(select?*?from?user_tab_columns?where?upper(Table_name)?=?upper('BLB'))?M?left?join?user_col_comments?A?ON?M.COLUMN_NAME=A.COLUMN_NAME?and?M.Table_Name?=?A.Table_Name??
254. left?join?(select?a.table_name,b.column_name?from?user_constraints?a,?user_cons_columns?b?where?a.constraint_name=b.constraint_name?and?upper(a.table_name)=upper('BLB')?and?a.constraint_type='P')?B?ON?M.Table_Name?=?B.TABLE_NAME?and?M.COLUMN_NAME=B.COLUMN_NAME?order?by?M.column_id;??
255. ??
256. insert?into?[用户表](id)?select?*?from?v$view;??
257. ??
258. create?or?replace?procedure?insert_ID?is??
259. begin??
260. ??for?i?in?1..196?loop??
261. ??????insert?into?lcqkbt(id)?values(seq.nextval);??
262. ??end?loop;??
263. ??commit;??
264. end;??
265. ??
266. alter?database?datafile?''?resize?100M;??
267. ??
268. --dba?daily?work??
269. create?table?errorlog(??
270. ???id?number?primary?key,??
271. ???errcode?number,??
272. ???errmsg?varchar2(1024),??
273. ???errdate?date??
274. );??
275. create?sequence?seq_errorlog_id?start?with?1?increment?by?1;??
276. declare???
277. ??v_deptno?dept.deptno%type?:=10;??
278. ??v_errcode?number;??
279. ??v_errmsg?varchar2(1024);??
280. begin??
281. ??delete?from?dept?where?deptno?=?v_deptno;??
282. ??commit;??
283. exception??
284. ??when?others?then??
285. ???????rollback;??
286. ??????????????v_errcode?:=SQLCODE;??
287. ??????????????v_errmsg:=SQLERRM;??
288. ???????insert?into?errorlog?values(seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate);??
289. ???????commit;??
290. end;??
291. ??
292. declare??
293. ??cursor?c?is??
294. ?????????select?*?from?emp;??
295. ??v_emp?c%rowtype;??
296. begin??
297. ??open?c;??
298. ??loop??
299. ???????fetch?c?into?v_emp;??
300. ???????exit?when(c%notfound);??
301. ???????dbms_output.put_line(v_emp.ename);??
302. ??end?loop;??
303. ??close?c;??
304. end;??
305. ??
306. declare??
307. ??cursor?c?is??
308. ?????????select?*?from?emp;??
309. ??v_emp?c%rowtype;??
310. begin??
311. ??open?c;??
312. ???????fetch?c?into?v_emp;??
313. ???????while(c%found)?loop??
314. ???????dbms_output.put_line(v_emp.ename);??
315. ???????fetch?c?into?v_emp;??
316. ??end?loop;??
317. ??close?c;??
318. end;??
319. ??
320. declare??
321. ??cursor?c?is??
322. ?????????select?*?from?emp;??
323. begin??
324. ????for?v_emp?in?c?loop??
325. ???????dbms_output.put_line(v_emp.ename);??
326. ??end?loop;??
327. end;??
328. ??
329. update?dept?set?deptno?=?99?where?deptno?=?10;??
330. create?or?replace?trigger?cascadeUpdate??
331. ???after?update?on?dept??
332. ???for?each?row??
333. begin??
334. ???update?emp?set?deptno?=?:NEW.deptno?where?deptno=:OLD.deptno;??
335. end;??
336. ??
337. create?or?replace?procedure?bbs(v_pid?article.pid%type,v_level?binary_integer)?is??
338. ???cursor?c?is?select?*?from?article?where?pid=?v_pid;??
339. ???v_preStr?varchar2(1024):='';??
340. begin??
341. ???for?i?in?1..v_level?loop??
342. ??????v_preStr?:=?v_preStr?||'****';??
343. ???end?loop;??
344. ?????
345. ???for?v_article?in?c?loop??
346. ???????dbms_output.put_line(v_preStr||v_article.cont);??
347. ???????if(v_article.isleaf?=?0)?then??
348. ???????????p(v_article.id,v_level+1);??
349. ???????end?if;???
350. ???end?loop;??
351. end;??
352. ??
353. --Oracle?date默认格式:11-12月-1997?or?11-12月-97??
354. --更改输入格式??
355. alter?session?set?nls_date_format='yyyy-mm-dd?hh:mi:ss';??
356. ??
357. 1.insert?into...??
358. 1.savepoint?a;??
359. 3.delete?from?stu;--删除所有记录,表结构还在,写日志，可以恢复，速度慢??
360. 4.rollback?to?a;??
361. ??
362. truncate?table?stu；--删除所有记录，表结构还在，不写日志，无法恢复，速度快??
363. set?timing?on;??
364. ??
365. --union?all和union的区别在于后者会去掉重复行???
366. --minus，intersect??
367. ??
368. --创建数据库??
369. --使用?Configuration?and?Migration?Tools->Database?Configuration?Assistant?8步，默认20进程??
370. ??
371. --只读事务??
372. set?transaction?read?only;??
373. ??
374. --预定义角色是在数据库安装后，系统自动创建的一些常用的角色??
375. --1．CONNECT,RESOURCE,DBA?---这些预定义角色主要是为了向后兼容。其主要是用于数据库管理。oracle建议用户自己设计数据库管理和安全的权限规划，而不要简单的使用这些预定角色。将来的版本中这些角色可能不会作为预定义角色。??
376. --2．DELETE_CATALOG_ROLE,EXECUTE_CATALOG_ROLE,SELECT_CATALOG_ROLE?---这些角色主要用于访问数据字典视图和包。??
377. --3.?EXP_FULL_DATABASE,IMP_FULL_DATABASE?---这两个角色用于数据导入导出工具的使用。??
378. select?*?from?role_sys_privs??where?role=upper('connect');??
379. --对象权限就是指在表、视图、序列、过程、函数或包等对象上执行特殊动作的权利。有九种不同类型的权限可以授予给用户或角色??
380. --系统权限需要授予者有进行系统级活动的能力,如连接数据库,更改用户会话、建立表或建立用户等等。你可以在数据字典视图SYSTEM_PRIVILEGE_MAP上获得完整的系统权限。对象权限和系统权限都通过GRANT语句授予用户或角色。需要注意的是在授予对象权限时语句应该是WITH?GRANT?OPTION子句，但在授予系统权象时语句是WITH?ADMIN?OPTION;??
381. --查看死锁信息&解除死锁??
382. select?sess.sid,??
383. ???????sess.serial#,??
384. ???????lo.oracle_username,??
385. ???????lo.os_user_name,??
386. ???????ao.object_name,??
387. ???????lo.locked_mode??
388. ??from?v$locked_object?lo,?dba_objects?ao,?v$session?sess??
389. ?where?ao.object_id?=?lo.object_id??
390. ???and?lo.session_id?=?sess.sid;??
391. ?????
392. ???SELECT?a.sid,?b.owner,?object_name,?object_type??
393. ??FROM?v$lock?a,?all_objects?b??
394. ?WHERE?TYPE?=?'TM'??
395. ???and?a.id1?=?b.object_id;??
396. ?????
397. alter?system?kill?session?'3267,62017'??
398. ??
399. --查看死锁机器??
400. select?username,?lockwait,?status,?machine,?program??
401. ??from?v$session??
402. ?where?sid?in?(select?session_id?from?v$locked_object)??
403. ???
404. ?--查看死锁字段??
405. ?select?sql_text??
406. ??from?v$sql??
407. ?where?hash_value?in??
408. ???????(select?sql_hash_value??
409. ??????????from?v$session??
410. ?????????where?sid?in?(select?session_id?from?v$locked_object));??