查询表结构和行转列以及常用视图

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

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