oracle恢复误删除数据,解除锁定等非常经典实用方法

转载请注明出处： http://renjie120.iteye.com/

注意:数据库版本是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.关于查询数据库用户，权限的相关语句：

1. 1.查看所有用户：
2. select*fromdba_user;
3. select*fromall_users;
4. select*fromuser_users;
7. 2.查看用户系统权限：
8. select*fromdba_sys_privs;
9. select*fromall_sys_privs;
10. select*fromuser_sys_privs;
13. 3.查看用户对象权限：
14. select*fromdba_tab_privs;
15. select*fromall_tab_privs;
16. select*fromuser_tab_privs;
19. 4.查看所有角色：
20. select*fromdba_roles;
23. 5.查看用户所拥有的角色：
24. select*fromdba_role_privs;
25. select*fromuser_role_privs;

1.查看所有用户：
select * from dba_user;
select * from all_users;
select * from user_users;


2.查看用户系统权限：
select * from dba_sys_privs;
select * from all_sys_privs;
select * from user_sys_privs;


3.查看用户对象权限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;


4.查看所有角色：
select * from dba_roles;


5.查看用户所拥有的角色：
select * from dba_role_privs;
select * from user_role_privs;

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

1. 1.查询oracle中所有用户信息
2. select*fromdba_user;
3. 2.只查询用户和密码
4. selectusername,passwordfromdba_users;
5. 3.查询当前用户信息
6. select*fromdba_ustats;
7. 4.查询用户可以访问的视图文本
8. select*fromdba_varrays;
9. 5.查询数据库中所有视图的文本
10. select*fromdba_views;
11. 6.查询全部索引
12. select*fromuser_indexes;
13. 查询全部表格
14. select*fromuser_tables;
15. 查询全部约束
16. select*fromuser_constraints;
17. 查询全部对象
18. select*fromuser_objects;

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

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

1. (1).查看相关进程在数据库中的会话
2. Selecta.sid,a.serial#,a.program,a.status,
3. substr(a.machine,1,20),a.terminal,b.spid
4. fromv$sessiona,v$processb
5. wherea.paddr=b.addr
6. andb.spid=&spid;
8. (2).查看数据库中被锁住的对象和相关会话
9. selecta.sid,a.username,
10. c.owner,c.object_name
11. fromv$sessiona,v$locked_objectb,all_objectsc
12. wherea.sid=b.session_idand
13. c.object_id=b.object_id;
15. (3).查看相关会话正在执行的SQL
16. selectsql_textfromv$sqlareawhereaddress=
17. (selectsql_addressfromv$sessionwheresid=&sid);

(1).查看相关进程在数据库中的会话   
  Select   a.sid,substr(a.machine,b.spid   
  from   v$session   a,v$process   b   
  where   a.paddr=b.addr   
  and   b.spid   =   &spid;   
    
  (2).查看数据库中被锁住的对象和相关会话   
  select   a.sid,c.owner,c.object_name     
  from   v$session   a,v$locked_object   b,all_objects   c   
  where   a.sid=b.session_id   and   
  c.object_id   =   b.object_id;   
    
  (3).查看相关会话正在执行的SQL   
  select   sql_text   from   v$sqlarea   where   address   =     
  (   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.行列互换：

1. 建立一个例子表：
2. CREATETABLEt_col_row(
3. IDINT,
4. c1VARCHAR2(10),
5. c2VARCHAR2(10),
6. c3VARCHAR2(10));
7. INSERTINTOt_col_rowVALUES(1,'v11','v21','v31');
8. INSERTINTOt_col_rowVALUES(2,'v12','v22',NULL);
9. INSERTINTOt_col_rowVALUES(3,'v13',NULL,'v33');
10. INSERTINTOt_col_rowVALUES(4,'v24','v34');
11. INSERTINTOt_col_rowVALUES(5,'v15',NULL);
12. INSERTINTOt_col_rowVALUES(6,'v35');
13. INSERTINTOt_col_rowVALUES(7,NULL);
14. COMMIT;
16. 下面的是列转行:创建了一个视图
17. CREATEviewv_row_colAS
18. SELECTid,'c1'cn,c1cv
19. FROMt_col_row
20. UNIONALL
21. SELECTid,'c2'cn,c2cv
22. FROMt_col_row
23. UNIONALL
24. SELECTid,'c3'cn,c3cvFROMt_col_row;
26. 下面是创建了没有空值的一个竖表:
27. CREATEviewv_row_col_notnullAS
28. SELECTid,c1cv
29. FROMt_col_row
30. wherec1isnotnull
31. UNIONALL
32. SELECTid,c2cv
33. FROMt_col_row
34. wherec2isnotnull
35. UNIONALL
36. SELECTid,c3cv
37. FROMt_col_row
38. wherec3isnotnull;

建立一个例子表：
CREATE TABLE t_col_row( 
ID INT,c1 VARCHAR2(10),c2 VARCHAR2(10),c3 VARCHAR2(10)); 
INSERT INTO t_col_row VALUES (1,'v11','v21','v31'); 
INSERT INTO t_col_row VALUES (2,'v12','v22',NULL); 
INSERT INTO t_col_row VALUES (3,'v13',NULL,'v33'); 
INSERT INTO t_col_row VALUES (4,'v24','v34'); 
INSERT INTO t_col_row VALUES (5,'v15',NULL); 
INSERT INTO t_col_row VALUES (6,'v35'); 
INSERT INTO t_col_row VALUES (7,NULL); 
COMMIT; 

下面的是列转行:创建了一个视图
CREATE view v_row_col AS
SELECT id,'c1' cn,c1 cv
FROM t_col_row
UNION ALL
SELECT id,'c2' cn,c2 cv
FROM t_col_row
UNION ALL
SELECT id,'c3' cn,c3 cv FROM t_col_row;

下面是创建了没有空值的一个竖表:
CREATE view v_row_col_notnull AS
SELECT id,c1 cv
 FROM t_col_row 
where c1 is not null
UNION ALL
SELECT id,c2 cv
 FROM t_col_row
where c2 is not null
UNION ALL
SELECT id,c3 cv
 FROM t_col_row 
where c3 is not null;

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

1. 1.示例：已知hash_value:3111103299，查询sql语句：
2. select*fromv$sqltext
3. wherehashvalue='3111103299'
4. orderbypiece
5. 2.查看消耗资源最多的SQL：
6. SELECThash_value,executions,buffer_gets,disk_reads,parse_calls
7. FROMV$SQLAREA
8. WHEREbuffer_gets>10000000ORdisk_reads>1000000
9. ORDERBYbuffer_gets+100*disk_readsDESC;
11. 3.查看某条SQL语句的资源消耗：
12. SELECThash_value,parse_calls
13. FROMV$SQLAREA
14. WHEREhash_Value=228801498ANDaddress=hextoraw('CBD8E4B0');
16. 4.查询sql语句的动态执行计划:
17. 首先使用下面的语句找到语句的在执行计划中的address和hash_code
18. SELECTsql_text,address,hash_valueFROMv$sqlt
19. where(sql_textlike'%FUNCTION_T(表名大写!)%')
20. 然后:
21. SELECToperation,options,object_name,costFROMv$sql_plan
22. WHEREaddress='C00000016BD6D248'ANDhash_value=664376056;
24. 5.查询oracle的版本:
25. select*fromv$version;
27. 6.查询数据库的一些参数:
28. select*fromv$parameter
30. 7.查找你的session信息
31. SELECTSID,OSUSER,USERNAME,MACHINE,PROCESS
32. FROMV$SESSIONWHEREaudsid=userenv('SESSIONID');
34. 8.当machine已知的情况下查找session
35. SELECTSID,TERMINAL
36. FROMV$SESSION
37. WHEREterminal='pts/tl'ANDmachine='rgmdbs1';
39. 9.查找当前被某个指定session正在运行的sql语句。假设sessionID为100
40. selectb.sql_text
41. fromv$sessiona,v$sqlareab
42. wherea.sql_hashvalue=b.hash_valueanda.sid=100

1.示例：已知hash_value:3111103299，查询sql语句：
select * from v$sqltext 
where hashvalue='3111103299'
order by piece 
2.查看消耗资源最多的SQL：
SELECT hash_value,parse_calls
FROM V$SQLAREA
WHERE buffer_gets > 10000000OR disk_reads > 1000000
ORDERBY buffer_gets + 100 * disk_reads DESC;

3.查看某条SQL语句的资源消耗：
SELECT hash_value,parse_calls
FROM V$SQLAREA
WHERE hash_Value = 228801498AND address = hextoraw('CBD8E4B0');

4.查询sql语句的动态执行计划:
        首先使用下面的语句找到语句的在执行计划中的address和hash_code
        SELECT sql_text,hash_value FROM v$sql t
                where (sql_text like '%FUNCTION_T(表名大写!)%')
        然后:
        SELECT operation,cost FROM v$sql_plan
                WHERE address = 'C00000016BD6D248' AND hash_value = 664376056;

5.查询oracle的版本:
select * from v$version;

6.查询数据库的一些参数:
select * from v$parameter

7.查找你的session信息
SELECT SID,PROCESS
FROM V$SESSION WHERE audsid = userenv('SESSIONID');

8.当machine已知的情况下查找session
SELECT SID,TERMINAL
FROM V$SESSION
WHERE terminal = 'pts/tl' AND machine = 'rgmdbs1';

9.查找当前被某个指定session正在运行的sql语句。假设sessionID为100
select b.sql_text 
from v$session a,v$sqlarea b 
where a.sql_hashvalue=b.hash_value and a.sid=100

9.树形结构connect by 排序：

1. 查询树形的数据结构，同时对一层里面的数据进行排序
2. SELECTlast_name,employee_id,manager_id,LEVEL
3. FROMemployees
4. STARTWITHemployee_id=100
5. CONNECTBYPRIORemployee_id=manager_id
6. <SPANstyle="BACKGROUND-COLOR:#ff0000">ORDERSIBLINGSBYlast_name;</SPAN>
13. 下面是查询结果
14. LAST_NAMEEMPLOYEE_IDMANAGER_IDLEVEL
15. --------------------------------------------------------
16. King1001
17. Cambrault1481002
18. Bates1721483
19. Bloom1691483
20. Fox1701483
21. Kumar1731483
22. Ozer1681483
23. Smith1711483
24. DeHaan1021002
25. Hunold1031023
26. Austin1051034
27. Ernst1041034
28. Lorentz1071034
29. Pataballa1061034
30. Errazuriz1471002
31. Ande1661473
32. Banda1671473

查询树形的数据结构，同时对一层里面的数据进行排序
SELECT last_name,LEVEL
      FROM employees
      START WITH employee_id = 100
      CONNECT BY PRIOR employee_id = manager_id
      ORDER SIBLINGS BY last_name;






下面是查询结果
LAST_NAME                 EMPLOYEE_ID MANAGER_ID      LEVEL
------------------------- ----------- ---------- ----------
King                              100                     1
Cambrault                         148        100          2
Bates                             172        148          3
Bloom                             169        148          3
Fox                               170        148          3
Kumar                             173        148          3
Ozer                              168        148          3
Smith                             171        148          3
De Haan                           102        100          2
Hunold                            103        102          3
Austin                            105        103          4
Ernst                             104        103          4
Lorentz                           107        103          4
Pataballa                         106        103          4
Errazuriz                         147        100          2
Ande                              166        147          3
Banda                             167        147          3

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

1. 1.在数据字典查询约束的相关信息：
2. SELECTconstraint_name,constraint_type,search_condition
3. FROMuser_constraintsWHEREtable_name='EMPLOYEES';
4. //这里的表名都是大写！
5. 2对表结构进行说明：
6. descTablename
7. 3查看用户下面有哪些表
8. selecttable_namefromuser_tables;
9. 4查看约束在那个列上建立：
10. SELECTconstraint_name,column_name
11. FROMuser_cons_columns
12. WHEREtable_name='EMPLOYEES';
13. 10结合变量查找相关某个表中约束的相关列名：
14. selectconstraint_name,column_namefromuser_cons_columnswheretable_name='&tablename'
15. 12查询数据字典看中间的元素：
16. SELECTobject_name,object_type
17. FROMuser_objects
18. WHEREobject_nameLIKE'EMP%'
19. ORobject_nameLIKE'DEPT%'
20. 14查询对象类型：
21. SELECTDISTINCTobject_typeFROMuser_objects;
22. 17改变对象名：（表名，视图,序列）
23. renameemptoemp_newTable
24. 18添加表的注释：
25. COMMENTONTABLEemployeesIS'EmployeeInformation';
26. 20查看视图结构：
27. describeview_name
28. 23在数据字典中查看视图信息：
29. selectviewe_name,textfromuser_views
30. 25查看数据字典中的序列：
31. select*fromuser_sequences
32. 33得到所有的时区名字信息：
33. select*fromv$timezone_names
34. 34显示对时区‘US/Eastern’的时区偏移量
35. selectTZ_OFFSET('US/Eastern')fromDUAL--dual英文意思是‘双重的’
36. 显示当前会话时区中的当前日期和时间:
37. ALTERSESSIONSETNLS_DATE_FORMAT='DD-MON-YYYYHH24:MI:SS';--修改显示时间的方式的设置
38. ALTERSESSIONSETTIME_ZONE='-5:0';--修改时区
39. SELECTSESSIONTIMEZONE,CURRENT_DATEFROMDUAL;--真正有用的语句！
40. SELECTCURRENT_TIMESTAMPFROMDUAL;--返回的时间是当前日期和时间，含有时区
41. SELECTCURRENT_TIMESTAMPFROMDUAL;--返回的时间是当前日期和时间，不含有时区！！！
42. 35显示数据库时区和会话时区的值：
43. selectdatimezone,sessiontimezonefromdual;
45. 13普通的建表语句：
46. CREATETABLEdept
47. (deptnoNUMBER(2),
48. dnameVARCHAR2(14),
49. locVARCHAR2(13));
50. 15使用子查询建立表:
51. CREATETABLEdept80
52. ASSELECTemployee_id,last_name,
53. salary*12ANNSAL,
54. hire_dateFROMemployeesWHEREdepartment_id=80;
55. 6添加列：//altertableEMPaddcolumn(dept_idnumber(7))；错误！！
56. altertableEMPadd(dept_idnumber(7));
57. 7删除一列：
58. altertableempdropcolumndept_id;
59. 8添加列名同时和约束：
60. altertableEMPadd(dept_idnumber(7)
61. constraintmy_emp_dept_id_fkreferencesdept(ID));
62. 9改变列：//注意约束不能够修改的！！
63. altertabledept80modify(last_namevarchar2(30));//这里使用的是modify而不是alter!
64. 24增加一行：
65. insertintotable_namevalues();
67. 5添加主键:
68. alterTableEMPaddconstraintmy_emp_id_pkprimarykey(ID);
69. 11添加一个有check约束的新列：
70. altertableEMP
71. add(COMMISSIONnumber(2)constraintemp_commission_ckcheck(commission>0))
72. 16删除表：
73. droptableemp;
74. 19创建视图：
75. CREATEVIEWempvu80
76. ASSELECTemployee_id,salary
77. FROMemployeesWHEREdepartment_id=80;
78. 21删除视图：
79. dropviewview_name
80. 22找到工资最高的5个人。（top-n分析）（行内视图）
81. selectrownum,employee_idfrom(selectemployee_id,salaryfrom
82. employeesorderbysalarydesc)
83. whererownum<5;
84. 26建立同义词：
85. createsynonym同义词名for原来的名字
86. 或者createpublicsynonym同义词名for原来的名字
87. 27建立序列：（注意，这里并没有出现说是哪个表里面的序列！！）
88. CREATESEQUENCEdept_deptid_seq
89. INCREMENTBY10
90. STARTWITH120
91. MAXVALUE9999
92. NOCACHE
93. NOCYCLE
94. 28使用序列：
95. insertintodept(ID,NAME)values(DEPT_ID_SEQ.nextval,'Administration');
96. 29建立索引：//默认就是nonunique索引，除非使用了关键字：unique
97. CREATEINDEXemp_last_name_idxONemployees(last_name);
98. 30建立用户：（可能有错，详细查看帮助）
99. createuserusername(用户名)
100. identifiedbyoracle(密码)
101. defaulttablespacedata01(表空间名//默认存在system表空间里面)
102. quota10M(设置大小，最大为unlimited)on表空间名//必须分配配额！
103. 31创建角色：createROLEmanager
104. 赋予角色权限：grantcreatetable,createviewtomanage
105. 赋予用户角色：grantmanagertoDENHAAN,KOCHHAR(两个用户)
106. 32分配权限：
107. GRANTupdate(department_name,location_id)
108. ONdepartments
109. TOscott,manager;
110. 回收权限
111. REVOKEselect,insert
112. ONdepartments
113. FROMscott;
114. 36从时间中提取年，月，日：使用函数extract
115. selectextract(yearfromsysdate)year,extract(monthfromsysdate),
116. extract(dayfromsysdate)fromdual;
117. 37使用函数得到数月之后的日期：to_yminterval（‘01-02’）表示加上1年2月，不能够到天！！
118. selecthire_date,hire_date+to_yminterval('01-02')ashire_date_newfromemployeeswheredepartment_id=20
119. 得到多少天之后的日期：直接日期加数字！
120. selecthire_date+3fromemployeeswheredepartment_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. 39group语句：和高级的应用语句：
131. SELECTdepartment_id,job_id,SUM(salary),COUNT(employee_id)FROMemployees
132. GROUPBYdepartment_id,job_id;
133. 使用having进行约束：
134. 1.groupbyrollup:对n列组合得到n+1种情况
135. SELECTdepartment_id,SUM(salary)FROMemployeesWHEREdepartment_id<60GROUPBYROLLUP(department_id,job_id);
136. 2.groupbycube:得到2的n次方种情况
137. SELECTdepartment_id,SUM(salary)FROMemployeesWHEREdepartment_id<60GROUPBYCUBE(department_id,job_id);
138. 3.使用grouping得到一行中构成列的情况，只是返回1和0：是空的话就返回1，否则返回0(注意不要弄反了！)
139. SELECTdepartment_idDEPTID,job_idJOB,GROUPING(department_id)GRP_DEPT,GROUPING(job_id)GRP_JOB
140. FROMemployeesWHEREdepartment_id<50GROUPBYROLLUP(department_id,job_id);
141. 4.groupingsets:根据需要得到制定的组合情况
142. SELECTdepartment_id,avg(salary)FROMemployeesGROUPBYGROUPINGSETS((department_id,job_id),(job_id,manager_id));
143. 40from中使用子查询：返回每个部门中大于改部门平均工资的与员工信息
144. SELECTa.last_name,a.salary,a.department_id,b.salavgFROMemployeesa,--下面的地方就是子查询了，主要返回的是一组数据！
145. (SELECTdepartment_id,AVG(salary)salavgFROMemployeesGROUPBYdepartment_id)b
146. WHEREa.department_id=b.department_id
147. ANDa.salary>b.salavg;
148. 41exists语句的使用：
149. SELECTemployee_id,department_id
150. FROMemployeesouter--下面的exists里面的select选择出来的是随便的一个字符或者数字都可以
151. WHEREEXISTS(SELECT'X'FROMemployeesWHEREmanager_id=outer.employee_id);
152. 42厉害的with语句：
153. WITH
154. dept_costsAS(--定义了一个临时的表
155. SELECTd.department_name,SUM(e.salary)ASdept_total--其间定义了一个临时的列dept_total
156. FROMemployeese,departmentsd
157. WHEREe.department_id=d.department_id
158. GROUPBYd.department_name),/*注意这里有逗号*/
159. avg_costAS(
160. SELECTSUM(dept_total)/COUNT(*)ASdept_avg
161. FROMdept_costs)--这里的第二张临时表里面就引用了前面定义的临时表和之间的列！
162. SELECT*FROMdept_costsWHEREdept_total>(SELECTdept_avgFROMavg_cost)ORDERBYdepartment_name;---最后的查询语句中使用了前面的临时表
163. 43遍历树：
164. SELECTemployee_id,manager_id
165. FROMemployees
166. STARTWITHemployee_id=101
167. CONNECTBYPRIORmanager_id=employee_id;--自底向上的遍历树。
169. 44.更新语句
170. UPDATEemployeesSET
171. job_id='SA_MAN',salary=salary+1000,department_id=120
172. WHEREfirst_name||''||last_name='DouglasGrant';
174. UPDATETABLE(SELECTprojs
175. FROMdeptdWHEREd.dno=123)p
176. SETp.budgets=p.budgets+1
177. WHEREp.pnoIN(123,456);

1.在数据字典查询约束的相关信息：
SELECT constraint_name,search_condition
FROM        user_constraints WHERE        table_name = 'EMPLOYEES';
        //这里的表名都是大写！
2对表结构进行说明：
   desc Tablename
3查看用户下面有哪些表
   select table_name from user_tables;
4查看约束在那个列上建立：
   SELECT constraint_name,column_name
   FROM        user_cons_columns
   WHERE          table_name = 'EMPLOYEES';
10结合变量查找相关某个表中约束的相关列名：
  select constraint_name,column_name from user_cons_columns where table_name = '&tablename'
12查询数据字典看中间的元素：
SELECT   object_name,object_type
FROM     user_objects
WHERE    object_name LIKE 'EMP%'     
OR       object_name LIKE 'DEPT%'
14查询对象类型：
SELECT DISTINCT object_type FROM           user_objects ;
17改变对象名：（表名，视图,序列）
  rename  emp to emp_newTable
18添加表的注释：
  COMMENT ON TABLE employees IS 'Employee Information';
20查看视图结构：
   describe view_name
23在数据字典中查看视图信息：
  select viewe_name,text from user_views
25查看数据字典中的序列：
  select * from user_sequences
33得到所有的时区名字信息：
        select  * from v$timezone_names
34显示对时区‘US/Eastern’的时区偏移量
        select TZ_OFFSET('US/Eastern') from DUAL--dual英文意思是‘双重的’
   显示当前会话时区中的当前日期和时间:
   ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';--修改显示时间的方式的设置
   ALTER SESSION SET TIME_ZONE = '-5:0';--修改时区
   SELECT SESSIONTIMEZONE,CURRENT_DATE FROM DUAL;--真正有用的语句！
  SELECT CURRENT_TIMESTAMP FROM DUAL;--返回的时间是当前日期和时间，含有时区
  SELECT CURRENT_TIMESTAMP FROM DUAL;--返回的时间是当前日期和时间，不含有时区！！！  
35显示数据库时区和会话时区的值：
        select datimezone,sessiontimezone from dual;

13普通的建表语句：
CREATE TABLE dept
(deptno         NUMBER(2),dname         VARCHAR2(14),loc         VARCHAR2(13));
15使用子查询建立表:
 CREATE TABLE         dept80
  AS  SELECT  employee_id,salary*12 ANNSAL,hire_date   FROM    employees   WHERE   department_id = 80;
6添加列：// alter table EMP add column (dept_id number(7))；错误！！
  alter table EMP add (dept_id number(7));
7删除一列：
  alter table emp drop column dept_id;
8添加列名同时和约束：
 alter table EMP add (dept_id number(7) 
   constraint my_emp_dept_id_fk  references dept(ID));
9改变列：//注意约束不能够修改 的！！
 alter table dept80 modify(last_name varchar2(30));//这里使用的是modify而不是alter!
24增加一行：
  insert into table_name values();

5添加主键:
 alter Table EMP  add constraint my_emp_id_pk primary key (ID);
11添加一个有check约束的新列：
  alter table EMP
  add (COMMISSION number(2) constraint emp_commission_ck check(commission>0))
16删除表：
   drop table emp;
19创建视图：
   CREATE VIEW         empvu80
 AS SELECT  employee_id,salary
    FROM    employees     WHERE   department_id = 80;
21删除视图：
   drop view view_name
22找到工资最高的5个人。（top-n分析）（行内视图）
 select rownum,employee_id from (select employee_id,salary from
 employees order by salary desc)
  where rownum<5;
26建立同义词：
  create synonym 同义词名 for 原来的名字
或者  create public synonym 同义词名 for 原来的名字
27建立序列：（注意，这里并没有出现说是哪个表里面的序列！！）
  CREATE SEQUENCE dept_deptid_seq
                INCREMENT BY 10
                START WITH 120
                MAXVALUE 9999
                NOCACHE
                NOCYCLE  
28使用序列：
        insert into dept(ID,NAME) values(DEPT_ID_SEQ.nextval,'Administration');
29建立索引：//默认就是nonunique索引，除非使用了关键字：unique
        CREATE INDEX emp_last_name_idx ON employees(last_name);
30建立用户：（可能有错，详细查看帮助）
        create user  username(用户名)
        identified by oracle(密码)
        default tablespace  data01(表空间名//默认存在system表空间里面)
        quota 10M(设置大小，最大为unlimited)  on 表空间名//必须分配配额！
31创建角色：create ROLE manager
    赋予角色权限：grant create table,create view to manage
    赋予用户角色：grant manager to DENHAAN,KOCHHAR( 两个用户)
32分配权限：
        GRANT  update (department_name,location_id)
        ON     departments
        TO     scott,manager;
    回收权限
        REVOKE  select,insert
        ON      departments
        FROM    scott;
36从时间中提取年，月，日：使用函数extract
        select extract(year from sysdate) year,extract(month from sysdate),extract(day from sysdate) from dual;
37使用函数得到数月之后的日期：to_yminterval（‘01-02’）表示加上1年2月，不能够到天！！
        select hire_date,hire_date +to_yminterval('01-02') as hire_date_new from employees where department_id=20
    得到多少天之后的日期：直接日期加数字！
        select hire_date +3 from employees where department_id=20
38一般的时间函数：
        MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')--两个日期之间的月数，返回一个浮点数
        ADD_MONTHS ('11-JAN-94',6)--添加月数
          NEXT_DAY ('01-SEP-95','FRIDAY') --下一个星期五的日期
        LAST_DAY('01-FEB-95')--当月的最后一天！
        ROUND(SYSDATE,'MONTH')         --四舍五入月
        ROUND(SYSDATE,'YEAR')       --四舍五入年
        TRUNC(SYSDATE,'MONTH')        --阶段月
         TRUNC(SYSDATE,'YEAR')        --截断年  
39 group语句：和高级的应用语句：
        SELECT   department_id,SUM(salary),COUNT(employee_id) FROM     employees
                GROUP BY department_id,job_id ;
        使用having进行约束：
        1.group by rollup:对n列组合得到n+1种情况
        SELECT   department_id,SUM(salary)        FROM     employees  WHERE    department_id < 60 GROUP BY ROLLUP(department_id,job_id);
        2.group by cube:得到2的n次方种情况
        SELECT   department_id,SUM(salary) FROM     employees  WHERE    department_id < 60 GROUP BY CUBE (department_id,job_id) ;
        3.使用grouping得到一行中构成列的情况，只是返回1和0：是空的话就返回1，否则返回0(注意不要弄反了！)
        SELECT   department_id DEPTID,job_id JOB,GROUPING(department_id) GRP_DEPT,GROUPING(job_id) GRP_JOB
                FROM     employees WHERE    department_id < 50 GROUP BY ROLLUP(department_id,job_id);
        4.grouping sets:根据需要得到制定的组合情况
        SELECT   department_id,avg(salary) FROM     employees GROUP BY GROUPING SETS ((department_id,manager_id));
40from中使用子查询：返回每个部门中大于改部门平均工资的与员工信息
        SELECT  a.last_name,b.salavg  FROM    employees a,--下面的地方就是子查询了，主要返回的是一组数据！
         (SELECT   department_id,AVG(salary) salavg   FROM     employees  GROUP BY department_id) b
        WHERE   a.department_id = b.department_id
        AND     a.salary > b.salavg;
41exists语句的使用：
        SELECT employee_id,department_id
        FROM   employees outer--下面的 exists里面的select选择出来的是随便的一个字符或者数字都可以
        WHERE  EXISTS ( SELECT 'X'   FROM   employees WHERE  manager_id =  outer.employee_id);
42厉害的with语句：
        WITH 
         dept_costs  AS (--定义了一个临时的表
                   SELECT  d.department_name,SUM(e.salary) AS dept_total--其间定义了一个临时的列dept_total
                   FROM    employees e,departments d
                   WHERE   e.department_id = d.department_id
                   GROUP BY d.department_name),/*注意这里有逗号*/
        avg_cost    AS (
           SELECT SUM(dept_total)/COUNT(*) AS dept_avg
           FROM   dept_costs)--这里的第二张临时表里面就引用了前面定义的临时表和之间的列！
        SELECT *  FROM   dept_costs  WHERE  dept_total >  (SELECT dept_avg   FROM avg_cost) ORDER BY department_name;---最后的查询语句中使用了前面的临时表
43遍历树：
        SELECT employee_id,manager_id
        FROM   employees
        START  WITH  employee_id = 101
        CONNECT BY PRIOR manager_id = employee_id ;--自底向上的遍历树。

44.更新语句
UPDATE employees SET 
    job_id = 'SA_MAN',salary = salary + 1000,department_id = 120 
    WHERE first_name||' '||last_name = 'Douglas Grant'; 

UPDATE TABLE (SELECT projs 
           FROM dept d WHERE d.dno = 123)  p 
  SET p.budgets = p.budgets + 1 
  WHERE p.pno IN (123,456);

11.导入导出dmp文件:

imp 用户名/密码@数据库 ignore=y file=备份文件 log=D:DBtestdb_bakimp.log

exp system/manager@TEST file=d:daochu.dmp full=y

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

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

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

1. --创建一个只允许在工作时间访问的视图
2. createorreplaceviewnewviewemp
3. as
4. select*from表名
5. whereexists(select1fromdualwheresysdate>=
6. to_date(to_char(sysdate,'yyyy-mm-dd')||'08:00:00','yyyy-mm-ddhh24:mi:ss')
7. andsysdate<
8. to_date(to_char(sysdate,'yyyy-mm-dd')||'18:00:00','yyyy-mm-ddhh24:mi:ss'))

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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!