(转)oracle回复误删除数据，解除锁定的等sql语句

注意:数据库版本是10g,不过大部分9i的也适用,闪回9i就没有.

?

1.曾经不小心把开发库的数据库表全部删除，当时吓的要死。结果找到下面的语句恢复到了1个小时之前的数据！很简单。

注意使用管理员登录系统：

select * from 表名 as of timestamp sysdate-1/12?? //查询两个小时前的某表数据！既然两小时以前的数据都得到了，继续怎么做，知道了吧。。

?

如果drop了表，怎么办？？见下面：

drop table 表名;

数据库误删除表之后恢复:（?绝对ok，我就做过这样的事情，汗?）不过要记得删除了哪些表名。?
flashback table 表名 to before drop;

?

2.查询得到当前数据库中锁，以及解锁：

查锁
SELECT /*+ rule */ s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT NULL;

解锁
alter system kill session 'sid,serial';?
如果解不了。直接倒os下kill进程kill －9 spid

?

?ORA-28000:账户被锁定

因为密码输入错误多次用户自动被锁定.

解决办法:alter user user_name account unlock;

?

3.关于查询数据库用户，权限的相关语句：

Sql代码??

1. 1.查看所有用户：??
2. select?*?from?dba_user;??
3. select?*?from?all_users;??
4. select?*?from?user_users;??
5. ??
6. ??
7. 2.查看用户系统权限：??
8. select?*?from?dba_sys_privs;??
9. select?*?from?all_sys_privs;??
10. select?*?from?user_sys_privs;??
11. ??
12. ??
13. 3.查看用户对象权限：??
14. select?*?from?dba_tab_privs;??
15. select?*?from?all_tab_privs;??
16. select?*?from?user_tab_privs;??
17. ??
18. ??
19. 4.查看所有角色：??
20. select?*?from?dba_roles;??
21. ??
22. ??
23. 5.查看用户所拥有的角色：??
24. select?*?from?dba_role_privs;??
25. select?*?from?user_role_privs;??

?

4.几个经常用到的oracle视图：注意表名使用大写....................

Sql代码??

1. 1.?查询oracle中所有用户信息??
2. ???????select??*?from?dba_user;??
3. ???2.?只查询用户和密码??
4. ???????select?username,password?from?dba_users;??
5. ???3.?查询当前用户信息??
6. ???????select?*?from?dba_ustats;??
7. ???4.?查询用户可以访问的视图文本??
8. ???????select?*?from?dba_varrays;??
9. ???5.?查询数据库中所有视图的文本??
10. ???????select?*?from?dba_views;??
11. 6.查询全部索引???
12. select?*?from?user_indexes;??
13. 查询全部表格??
14. ??????select?*?from?user_tables;??
15. ?????????查询全部约束??
16. ??????select?*?from?user_constraints;??
17. ??????????查询全部对象??
18. ??????select?*?from?user_objects;??

?

5.查看当前数据库中正在执行的语句，然后可以继续做很多很多事情，例如查询执行计划等等

Sql代码??

1. (1).查看相关进程在数据库中的会话?????
2. ??Select???a.sid,a.serial#,a.program,???a.status???,?????
3. ??substr(a.machine,1,20),???a.terminal,b.spid?????
4. ??from???v$session???a,???v$process???b?????
5. ??where???a.paddr=b.addr?????
6. ??and???b.spid???=???&spid;?????
7. ??????
8. ??(2).查看数据库中被锁住的对象和相关会话?????
9. ??select???a.sid,a.serial#,a.username,a.program,?????
10. ??c.owner,???c.object_name???????
11. ??from???v$session???a,???v$locked_object???b,???all_objects???c?????
12. ??where???a.sid=b.session_id???and?????
13. ??c.object_id???=???b.object_id;?????
14. ??????
15. ??(3).查看相关会话正在执行的SQL?????
16. ??select???sql_text???from???v$sqlarea???where???address???=???????
17. ??(???select???sql_address???from???v$session???where???sid???=???&sid???);?????

?

6.查询表的结构：表名大写！！

select t.COLUMN_NAME,
?????? t.DATA_TYPE,
?????? nvl(t.DATA_PRECISION, t.DATA_LENGTH),
?????? nvl(T.DATA_SCALE, 0),
?????? c.comments
? from all_tab_columns t, user_col_comments c
?whEre t.TABLE_NAME = c.table_name
?? and t.COLUMN_NAME = c.column_name
?? and t.TABLE_NAME = UPPER('OM_EMPLOYEE_T')
?order by t.COLUMN_ID?????

?

7.行列互换：

Sql代码??

1. 建立一个例子表：??
2. CREATE?TABLE?t_col_row(???
3. ID?INT,???
4. c1?VARCHAR2(10),???
5. c2?VARCHAR2(10),???
6. c3?VARCHAR2(10));???
7. INSERT?INTO?t_col_row?VALUES?(1,?'v11',?'v21',?'v31');???
8. INSERT?INTO?t_col_row?VALUES?(2,?'v12',?'v22',?NULL);???
9. INSERT?INTO?t_col_row?VALUES?(3,?'v13',?NULL,?'v33');???
10. INSERT?INTO?t_col_row?VALUES?(4,?NULL,?'v24',?'v34');???
11. INSERT?INTO?t_col_row?VALUES?(5,?'v15',?NULL,?NULL);???
12. INSERT?INTO?t_col_row?VALUES?(6,?NULL,?NULL,?'v35');???
13. INSERT?INTO?t_col_row?VALUES?(7,?NULL,?NULL,?NULL);???
14. COMMIT;???
15. ??
16. 下面的是列转行:创建了一个视图??
17. CREATE?view?v_row_col?AS??
18. SELECT?id,?'c1'?cn,?c1?cv??
19. FROM?t_col_row??
20. UNION?ALL??
21. SELECT?id,?'c2'?cn,?c2?cv??
22. FROM?t_col_row??
23. UNION?ALL??
24. SELECT?id,?'c3'?cn,?c3?cv?FROM?t_col_row;??
25. ??
26. 下面是创建了没有空值的一个竖表:??
27. CREATE?view?v_row_col_notnull?AS??
28. SELECT?id,?'c1'?cn,?c1?cv??
29. ?FROM?t_col_row???
30. where?c1?is?not?null??
31. UNION?ALL??
32. SELECT?id,?'c2'?cn,?c2?cv??
33. ?FROM?t_col_row??
34. where?c2?is?not?null??
35. UNION?ALL??
36. SELECT?id,?'c3'?cn,?c3?cv??
37. ?FROM?t_col_row???
38. where?c3?is?not?null;??

?

8.下面可能是dba经常使用的oracle视图吧。呵呵

Sql代码??

1. 1.示例：已知hash_value:3111103299，查询sql语句：??
2. select?*?from?v$sqltext???
3. where?hashvalue='3111103299'??
4. order?by?piece???
5. 2.查看消耗资源最多的SQL：??
6. SELECT?hash_value,?executions,?buffer_gets,?disk_reads,?parse_calls??
7. FROM?V$SQLAREA??
8. WHERE?buffer_gets?>?10000000OR?disk_reads?>?1000000??
9. ORDERBY?buffer_gets?+?100?*?disk_reads?DESC;??
10. ??
11. 3.查看某条SQL语句的资源消耗：??
12. SELECT?hash_value,?buffer_gets,?disk_reads,?executions,?parse_calls??
13. FROM?V$SQLAREA??
14. WHERE?hash_Value?=?228801498AND?address?=?hextoraw('CBD8E4B0');??
15. ??
16. 4.查询sql语句的动态执行计划:??
17. ????????首先使用下面的语句找到语句的在执行计划中的address和hash_code??
18. ????????SELECT?sql_text,?address,?hash_value?FROM?v$sql?t??
19. ????????????????where?(sql_text?like?'%FUNCTION_T(表名大写!)%')??
20. ????????然后:??
21. ????????SELECT?operation,?options,?object_name,?cost?FROM?v$sql_plan??
22. ????????????????WHERE?address?=?'C00000016BD6D248'?AND?hash_value?=?664376056;??
23. ??
24. 5.查询oracle的版本:??
25. select?*?from?v$version;??
26. ??
27. 6.查询数据库的一些参数:??
28. select?*?from?v$parameter??
29. ??
30. 7.查找你的session信息??
31. SELECT?SID,?OSUSER,?USERNAME,?MACHINE,?PROCESS??
32. FROM?V$SESSION?WHERE?audsid?=?userenv('SESSIONID');??
33. ??
34. 8.当machine已知的情况下查找session??
35. SELECT?SID,?OSUSER,?USERNAME,?MACHINE,?TERMINAL??
36. FROM?V$SESSION??
37. WHERE?terminal?=?'pts/tl'?AND?machine?=?'rgmdbs1';??
38. ??
39. 9.查找当前被某个指定session正在运行的sql语句。假设sessionID为100??
40. select?b.sql_text???
41. from?v$session?a,v$sqlarea?b???
42. where?a.sql_hashvalue=b.hash_value?and?a.sid=100??

?

9.树形结构connect by 排序：

Sql代码??

1. 查询树形的数据结构，同时对一层里面的数据进行排序??
2. SELECT?last_name,?employee_id,?manager_id,?LEVEL??
3. ??????FROM?employees??
4. ??????START?WITH?employee_id?=?100??
5. ??????CONNECT?BY?PRIOR?employee_id?=?manager_id??
6. ?????<span?style="background-color:?#ff0000;">?ORDER?SIBLINGS?BY?last_name;</span>??
7. ??
8. ??
9. ??
10. ??
11. ??
12. ??
13. 下面是查询结果??
14. LAST_NAME?????????????????EMPLOYEE_ID?MANAGER_ID??????LEVEL??
15. -------------------------?-----------?----------?----------??
16. King??????????????????????????????100?????????????????????1??
17. Cambrault?????????????????????????148????????100??????????2??
18. Bates?????????????????????????????172????????148??????????3??
19. Bloom?????????????????????????????169????????148??????????3??
20. Fox???????????????????????????????170????????148??????????3??
21. Kumar?????????????????????????????173????????148??????????3??
22. Ozer??????????????????????????????168????????148??????????3??
23. Smith?????????????????????????????171????????148??????????3??
24. De?Haan???????????????????????????102????????100??????????2??
25. Hunold????????????????????????????103????????102??????????3??
26. Austin????????????????????????????105????????103??????????4??
27. Ernst?????????????????????????????104????????103??????????4??
28. Lorentz???????????????????????????107????????103??????????4??
29. Pataballa?????????????????????????106????????103??????????4??
30. Errazuriz?????????????????????????147????????100??????????2??
31. Ande??????????????????????????????166????????147??????????3??
32. Banda?????????????????????????????167????????147??????????3??
33. ???

?

10.有时候写多了东西，居然还忘记最基本的sql语法，下面全部写出来，基本的oracle语句都在这里可以找到了。是很基础的语句！

Sql代码??

1. 1.在数据字典查询约束的相关信息：??
2. SELECT?constraint_name,?constraint_type,search_condition??
3. FROM????????user_constraints?WHERE????????table_name?=?'EMPLOYEES';??
4. ????????//这里的表名都是大写！??
5. 2对表结构进行说明：??
6. ???desc?Tablename??
7. 3查看用户下面有哪些表??
8. ???select?table_name?from?user_tables;??
9. 4查看约束在那个列上建立：??
10. ???SELECT?constraint_name,?column_name??
11. ???FROM????????user_cons_columns??
12. ???WHERE??????????table_name?=?'EMPLOYEES';??
13. 10结合变量查找相关某个表中约束的相关列名：??
14. ??select?constraint_name,column_name?from?user_cons_columns?where?table_name?=?'&tablename'??
15. 12查询数据字典看中间的元素：??
16. SELECT???object_name,?object_type??
17. FROM?????user_objects??
18. WHERE????object_name?LIKE?'EMP%'???????
19. OR???????object_name?LIKE?'DEPT%'??
20. 14查询对象类型：??
21. SELECT?DISTINCT?object_type?FROM???????????user_objects?;??
22. 17改变对象名：（表名，视图,序列）??
23. ??rename??emp?to?emp_newTable??
24. 18添加表的注释：??
25. ??COMMENT?ON?TABLE?employees?IS?'Employee?Information';??
26. 20查看视图结构：??
27. ???describe?view_name??
28. 23在数据字典中查看视图信息：??
29. ??select?viewe_name,text?from?user_views??
30. 25查看数据字典中的序列：??
31. ??select?*?from?user_sequences??
32. 33得到所有的时区名字信息：??
33. ????????select??*?from?v$timezone_names??
34. 34显示对时区‘US/Eastern’的时区偏移量??
35. ????????select?TZ_OFFSET('US/Eastern')?from?DUAL--dual英文意思是‘双重的’??
36. ???显示当前会话时区中的当前日期和时间:??
37. ???ALTER?SESSION?SET?NLS_DATE_FORMAT?=?'DD-MON-YYYY?HH24:MI:SS';--修改显示时间的方式的设置??
38. ???ALTER?SESSION?SET?TIME_ZONE?=?'-5:0';--修改时区??
39. ???SELECT?SESSIONTIMEZONE,?CURRENT_DATE?FROM?DUAL;--真正有用的语句！??
40. ??SELECT?CURRENT_TIMESTAMP?FROM?DUAL;--返回的时间是当前日期和时间，含有时区??
41. ??SELECT?CURRENT_TIMESTAMP?FROM?DUAL;--返回的时间是当前日期和时间，不含有时区！！！????
42. 35显示数据库时区和会话时区的值：??
43. ????????select?datimezone,sessiontimezone?from?dual;??
44. ??
45. 13普通的建表语句：??
46. CREATE?TABLE?dept??
47. (deptno?????????NUMBER(2),??
48. dname?????????VARCHAR2(14),??
49. loc?????????VARCHAR2(13));??
50. 15使用子查询建立表:??
51. ?CREATE?TABLE?????????dept80??
52. ??AS??SELECT??employee_id,?last_name,???
53. ????????????salary*12?ANNSAL,???
54. ????????????hire_date???FROM????employees???WHERE???department_id?=?80;??
55. 6添加列：//?alter?table?EMP?add?column?(dept_id?number(7))；错误！！??
56. ??alter?table?EMP?add?(dept_id?number(7));??
57. 7删除一列：??
58. ??alter?table?emp?drop?column?dept_id;??
59. 8添加列名同时和约束：??
60. ?alter?table?EMP?add?(dept_id?number(7)???
61. ???constraint?my_emp_dept_id_fk??references?dept(ID));??
62. 9改变列：//注意约束不能够修改?的！！??
63. ?alter?table?dept80?modify(last_name?varchar2(30));//这里使用的是modify而不是alter!??
64. 24增加一行：??
65. ??insert?into?table_name?values();??
66. ??
67. 5添加主键:??
68. ?alter?Table?EMP??add?constraint?my_emp_id_pk?primary?key?(ID);??
69. 11添加一个有check约束的新列：??
70. ??alter?table?EMP??
71. ??add?(COMMISSION?number(2)?constraint?emp_commission_ck?check(commission>0))??
72. 16删除表：??
73. ???drop?table?emp;??
74. 19创建视图：??
75. ???CREATE?VIEW?????????empvu80??
76. ?AS?SELECT??employee_id,?last_name,?salary??
77. ????FROM????employees?????WHERE???department_id?=?80;??
78. 21删除视图：??
79. ???drop?view?view_name??
80. 22找到工资最高的5个人。（top-n分析）（行内视图）??
81. ?select?rownum,employee_id?from?(select?employee_id,salary?from??
82. ?employees?order?by?salary?desc)??
83. ??where?rownum<5;??
84. 26建立同义词：??
85. ??create?synonym?同义词名?for?原来的名字??
86. 或者??create?public?synonym?同义词名?for?原来的名字??
87. 27建立序列：（注意，这里并没有出现说是哪个表里面的序列！！）??
88. ??CREATE?SEQUENCE?dept_deptid_seq??
89. ????????????????INCREMENT?BY?10??
90. ????????????????START?WITH?120??
91. ????????????????MAXVALUE?9999??
92. ????????????????NOCACHE??
93. ????????????????NOCYCLE????
94. 28使用序列：??
95. ????????insert?into?dept(ID,NAME)?values(DEPT_ID_SEQ.nextval,'Administration');??
96. 29建立索引：//默认就是nonunique索引，除非使用了关键字：unique??
97. ????????CREATE?INDEX?emp_last_name_idx?ON?employees(last_name);??
98. 30建立用户：（可能有错，详细查看帮助）??
99. ????????create?user??username(用户名)??
100. ????????identified?by?oracle(密码)??
101. ????????default?tablespace??data01(表空间名//默认存在system表空间里面)??
102. ????????quota?10M(设置大小，最大为unlimited)??on?表空间名//必须分配配额！??
103. 31创建角色：create?ROLE?manager??
104. ????赋予角色权限：grant?create?table,create?view?to?manage??
105. ????赋予用户角色：grant?manager?to?DENHAAN,KOCHHAR(?两个用户)??
106. 32分配权限：??
107. ????????GRANT??update?(department_name,?location_id)??
108. ????????ON?????departments??
109. ????????TO?????scott,?manager;??
110. ????回收权限??
111. ????????REVOKE??select,?insert??
112. ????????ON??????departments??
113. ????????FROM????scott;??
114. 36从时间中提取年，月，日：使用函数extract??
115. ????????select?extract(year?from?sysdate)?year,extract(month?from?sysdate),??
116. extract(day?from?sysdate)?from?dual;??
117. 37使用函数得到数月之后的日期：to_yminterval（‘01-02’）表示加上1年2月，不能够到天！！??
118. ????????select?hire_date,hire_date?+to_yminterval('01-02')?as?hire_date_new?from?employees?where?department_id=20??
119. ????得到多少天之后的日期：直接日期加数字！??
120. ????????select?hire_date?+3?from?employees?where?department_id=20??
121. 38一般的时间函数：??
122. ????????MONTHS_BETWEEN?('01-SEP-95','11-JAN-94')--两个日期之间的月数，返回一个浮点数??
123. ????????ADD_MONTHS?('11-JAN-94',6)--添加月数??
124. ??????????NEXT_DAY?('01-SEP-95','FRIDAY')?--下一个星期五的日期??
125. ????????LAST_DAY('01-FEB-95')--当月的最后一天！??
126. ????????ROUND(SYSDATE,'MONTH')?????????--四舍五入月??
127. ????????ROUND(SYSDATE?,'YEAR')???????--四舍五入年??
128. ????????TRUNC(SYSDATE?,'MONTH')????????--阶段月??
129. ?????????TRUNC(SYSDATE?,'YEAR')????????--截断年????
130. 39?group语句：和高级的应用语句：??
131. ????????SELECT???department_id,?job_id,?SUM(salary),??COUNT(employee_id)?FROM?????employees??
132. ????????????????GROUP?BY?department_id,?job_id?;??
133. ????????使用having进行约束：??
134. ????????1.group?by?rollup:对n列组合得到n+1种情况??
135. ????????SELECT???department_id,?job_id,?SUM(salary)????????FROM?????employees??WHERE????department_id?<?60?GROUP?BY?ROLLUP(department_id,?job_id);??
136. ????????2.group?by?cube:得到2的n次方种情况??
137. ????????SELECT???department_id,?job_id,?SUM(salary)?FROM?????employees??WHERE????department_id?<?60?GROUP?BY?CUBE?(department_id,?job_id)?;??
138. ????????3.使用grouping得到一行中构成列的情况，只是返回1和0：是空的话就返回1，否则返回0(注意不要弄反了！)??
139. ????????SELECT???department_id?DEPTID,?job_id?JOB,??SUM(salary),????GROUPING(department_id)?GRP_DEPT,????GROUPING(job_id)?GRP_JOB??
140. ????????????????FROM?????employees?WHERE????department_id?<?50?GROUP?BY?ROLLUP(department_id,?job_id);??
141. ????????4.grouping?sets:根据需要得到制定的组合情况??
142. ????????SELECT???department_id,?job_id,?manager_id,avg(salary)?FROM?????employees?GROUP?BY?GROUPING?SETS?((department_id,job_id),?(job_id,manager_id));??
143. 40from中使用子查询：返回每个部门中大于改部门平均工资的与员工信息??
144. ????????SELECT??a.last_name,?a.salary,?a.department_id,?b.salavg??FROM????employees?a,--下面的地方就是子查询了，主要返回的是一组数据！??
145. ?????????(SELECT???department_id,?AVG(salary)?salavg???FROM?????employees??GROUP?BY?department_id)?b??
146. ????????WHERE???a.department_id?=?b.department_id??
147. ????????AND?????a.salary?>?b.salavg;??
148. 41exists语句的使用：??
149. ????????SELECT?employee_id,?last_name,?job_id,?department_id??
150. ????????FROM???employees?outer--下面的?exists里面的select选择出来的是随便的一个字符或者数字都可以??
151. ????????WHERE??EXISTS?(?SELECT?'X'???FROM???employees?WHERE??manager_id?=??outer.employee_id);??
152. 42厉害的with语句：??
153. ????????WITH???
154. ?????????dept_costs??AS?(--定义了一个临时的表??
155. ???????????????????SELECT??d.department_name,?SUM(e.salary)?AS?dept_total--其间定义了一个临时的列dept_total??
156. ???????????????????FROM????employees?e,?departments?d??
157. ???????????????????WHERE???e.department_id?=?d.department_id??
158. ???????????????????GROUP?BY?d.department_name),/*注意这里有逗号*/??
159. ????????avg_cost????AS?(??
160. ???????????SELECT?SUM(dept_total)/COUNT(*)?AS?dept_avg??
161. ???????????FROM???dept_costs)--这里的第二张临时表里面就引用了前面定义的临时表和之间的列！??
162. ????????SELECT?*??FROM???dept_costs??WHERE??dept_total?>??(SELECT?dept_avg???FROM?avg_cost)?ORDER?BY?department_name;---最后的查询语句中使用了前面的临时表??
163. 43遍历树：??
164. ????????SELECT?employee_id,?last_name,?job_id,?manager_id??
165. ????????FROM???employees??
166. ????????START??WITH??employee_id?=?101??
167. ????????CONNECT?BY?PRIOR?manager_id?=?employee_id?;--自底向上的遍历树。??
168. ??
169. 44.更新语句??
170. UPDATE?employees?SET???
171. ????job_id?=?'SA_MAN',?salary?=?salary?+?1000,?department_id?=?120???
172. ????WHERE?first_name||'?'||last_name?=?'Douglas?Grant';???
173. ??
174. UPDATE?TABLE?(SELECT?projs???
175. ???????????FROM?dept?d?WHERE?d.dno?=?123)??p???
176. ??SET?p.budgets?=?p.budgets?+?1???
177. ??WHERE?p.pno?IN?(123,?456);??

?

11.导入导出dmp文件:

imp [email protected] ignore=y file=备份文件 log=D:\DBtest\db_bak\imp.log

?

exp [email protected] file=d:\daochu.dmp full=y

?

?12.大对象字段blob:查看blob字段的大小:

?? select??dbms_lob.getLength?(字段名)??from?表名?;?

?

13.下面收集的是有意思的sql语句,说不定正是你需要的:

?

Java代码??

1. --创建一个只允许在工作时间访问的视图??
2. create?or?replace?view?newviewemp??
3. as??
4. select?*?from?表名??
5. ?where?exists(select?1?from?dual?where?sysdate?>=??
6. ???????to_date(to_char(sysdate,?'yyyy-mm-dd?')?||?'08:00:00',?'yyyy-mm-dd?hh24:mi:ss')??
7. ???and?sysdate?<??
8. ???????to_date(to_char(sysdate,?'yyyy-mm-dd?')?||?'18:00:00',?'yyyy-mm-dd?hh24:mi:ss'))??
9. ???

?

14.存储过程中执行ddl语句：

Create Or Replace Procedure My_Proc As
Sqlddl Varchar2(1000);
Begin
Sqlddl := 'create table MyTable(ID Number(5), Name Varchar2(20))';
Dbms_Output.Put_Line(Sqlddl);
Execute Immediate Sqlddl;?
End;