7.行列互换： 建立一个例子表：
1. CREATE TABLE t_col_row( 2. ID INT,
3. c1 VARCHAR2(10), 4. c2 VARCHAR2(10), 5. c3 VARCHAR2(10));
6. INSERT INTO t_col_row VALUES (1, 'v11', 'v21', 'v31'); 7. INSERT INTO t_col_row VALUES (2, 'v12', 'v22', NULL); 8. INSERT INTO t_col_row VALUES (3, 'v13', NULL, 'v33'); 9. INSERT INTO t_col_row VALUES (4, NULL, 'v24', 'v34'); 10.INSERT INTO t_col_row VALUES (5, 'v15', NULL, NULL); 11.INSERT INTO t_col_row VALUES (6, NULL, NULL, 'v35'); 12.INSERT INTO t_col_row VALUES (7, NULL, NULL, NULL); 13.COMMIT; 14.
下面的是列转行:创建了一个视图
15.CREATE view v_row_col AS 16.SELECT id, 'c1' cn, c1 cv 17.FROM t_col_row 18.UNION ALL
19.SELECT id, 'c2' cn, c2 cv 20.FROM t_col_row 21.UNION ALL
22.SELECT id, 'c3' cn, c3 cv FROM t_col_row; 23.
下面是创建了没有空值的一个竖表: 24.CREATE view v_row_col_notnull AS 25.SELECT id, 'c1' cn, c1 cv 26. FROM t_col_row 27.where c1 is not null 28.UNION ALL
29.SELECT id, 'c2' cn, c2 cv 30. FROM t_col_row
31.where c2 is not null 32.UNION ALL
33.SELECT id, 'c3' cn, c3 cv 34. FROM t_col_row
35.where c3 is not null;
8.下面可能是dba经常使用的oracle视图
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_calls FROM V$SQLAREA
WHERE buffer_gets > 10000000OR disk_reads > 1000000 ORDERBY buffer_gets + 100 * disk_reads DESC;
3.查看某条SQL语句的资源消耗：
SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls FROM V$SQLAREA
WHERE hash_Value = 228801498AND address = hextoraw('CBD8E4B0');
4.查询sql语句的动态执行计划:
1. 首先使用下面的语句找到语句的在执行计划中的address和hash_code
SELECT sql_text, address, hash_value FROM v$sql t
where (sql_text like '%FUNCTION_T(表名大写!)%')
2. 然后:
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$parameter 7.查找你的session信息
SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS FROM V$SESSION WHERE audsid = userenv('SESSIONID');
8.当machine已知的情况下查找session
SELECT SID, OSUSER, USERNAME, MACHINE, 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.查询树形的数据结构，同时对一层里面的数据进行排序
SELECT last_name, employee_id, manager_id, LEVEL FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
下面是查询结果
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.在数据字典查询约束的相关信息：
SELECT constraint_name, constraint_type,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';
5添加主键:
alter Table EMP add constraint my_emp_id_pk primary key (ID);
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!
10结合变量查找相关某个表中约束的相关列名：
select constraint_name,column_name from user_cons_columns where table_name = '&tablename'