当前位置： 代码迷 >> SQL >> oracle sql 话语（一）
  详细解决方案

oracle sql 话语（一）

热度：59   发布时间：2016-05-05 14:21:55.0
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;?? 
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视图：注意表名使用大写....................

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;?? 
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.查看当前数据库中正在执行的语句，然后可以继续做很多很多事情，例如查询执行计划等等

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???);????? 
(1).查看相关进程在数据库中的会话     Select   a.sid,a.serial#,a.program,   a.status   ,     substr(a.machine,1,20),   a.terminal,b.spid     from   v$session   a,   v$process   b     where   a.paddr=b.addr     and   b.spid   =   &spid;         (2).查看数据库中被锁住的对象和相关会话     select   a.sid,a.serial#,a.username,a.program,     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.行列互换：

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;?? 
建立一个例子表：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, NULL, 'v24', 'v34'); INSERT INTO t_col_row VALUES (5, 'v15', NULL, NULL); INSERT INTO t_col_row VALUES (6, NULL, NULL, 'v35'); INSERT INTO t_col_row VALUES (7, NULL, NULL, NULL); COMMIT; 下面的是列转行:创建了一个视图CREATE view v_row_col ASSELECT id, 'c1' cn, c1 cvFROM t_col_rowUNION ALLSELECT id, 'c2' cn, c2 cvFROM t_col_rowUNION ALLSELECT id, 'c3' cn, c3 cv FROM t_col_row;下面是创建了没有空值的一个竖表:CREATE view v_row_col_notnull ASSELECT id, 'c1' cn, c1 cv FROM t_col_row where c1 is not nullUNION ALLSELECT id, 'c2' cn, c2 cv FROM t_col_rowwhere c2 is not nullUNION ALLSELECT id, 'c3' cn, c3 cv FROM t_col_row 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?? 
1.示例：已知hash_value:3111103299，查询sql语句：select * from v$sqltext where hashvalue='3111103299'order by piece 2.查看消耗资源最多的SQL：SELECT hash_value, executions, buffer_gets, disk_reads, parse_callsFROM V$SQLAREAWHERE buffer_gets > 10000000OR disk_reads > 1000000ORDERBY buffer_gets + 100 * disk_reads DESC;3.查看某条SQL语句的资源消耗：SELECT hash_value, buffer_gets, disk_reads, executions, parse_callsFROM V$SQLAREAWHERE hash_Value = 228801498AND address = hextoraw('CBD8E4B0');4.查询sql语句的动态执行计划:        首先使用下面的语句找到语句的在执行计划中的address和hash_code        SELECT sql_text, address, hash_value FROM v$sql t                where (sql_text like '%FUNCTION_T(表名大写!)%')        然后:        SELECT operation, options, object_name, cost FROM v$sql_plan                WHERE address = 'C00000016BD6D248' AND hash_value = 664376056;5.查询oracle的版本:select * from v$version;6.查询数据库的一些参数:select * from v$parameter7.查找你的session信息SELECT SID, OSUSER, USERNAME, MACHINE, PROCESSFROM V$SESSION WHERE audsid = userenv('SESSIONID');8.当machine已知的情况下查找sessionSELECT SID, OSUSER, USERNAME, MACHINE, TERMINALFROM V$SESSIONWHERE terminal = 'pts/tl' AND machine = 'rgmdbs1';9.查找当前被某个指定session正在运行的sql语句。假设sessionID为100select b.sql_text from v$session a,v$sqlarea b 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. LAST_NAME?????????????????EMPLOYEE_ID?MANAGER_ID??????LEVEL??
  14. -------------------------?-----------?----------?---------- ??
  15. King??????????????????????????????100?????????????????????1 ??
  16. Cambrault?????????????????????????148????????100??????????2 ??
  17. Bates?????????????????????????????172????????148??????????3 ??
  18. Bloom?????????????????????????????169????????148??????????3 ??
  19. Fox???????????????????????????????170????????148??????????3 ??
  20. Kumar?????????????????????????????173????????148??????????3 ??
  21. Ozer??????????????????????????????168????????148??????????3 ??
  22. Smith?????????????????????????????171????????148??????????3 ??
  23. De?Haan???????????????????????????102????????100??????????2 ??
  24. Hunold????????????????????????????103????????102??????????3 ??
  25. Austin????????????????????????????105????????103??????????4 ??
  26. Ernst?????????????????????????????104????????103??????????4 ??
  27. Lorentz???????????????????????????107????????103??????????4 ??
  28. Pataballa?????????????????????????106????????103??????????4 ??
  29. Errazuriz?????????????????????????147????????100??????????2 ??
  30. Ande??????????????????????????????166????????147??????????3 ??
  31. Banda?????????????????????????????167????????147??????????3 ??
  32. ???
查询树形的数据结构，同时对一层里面的数据进行排序SELECT last_name, employee_id, manager_id, 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                     1Cambrault                         148        100          2Bates                             172        148          3Bloom                             169        148          3Fox                               170        148          3Kumar                             173        148          3Ozer                              168        148          3Smith                             171        148          3De Haan                           102        100          2Hunold                            103        102          3Austin                            105        103          4Ernst                             104        103          4Lorentz                           107        103          4Pataballa                         106        103          4Errazuriz                         147        100          2Ande                              166        147          3Banda                             167        147          3

?

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. ??
  相关解决方案

代码迷 - 您身边的软件异常，代码异常，编程异常助手（发现，解决，分享）
Copyright © 2009-2013 DAIMAMI.COM. All rights reserved.