1.曾經不小心把開發庫的數據庫表全部刪除，當時嚇的要死。結果找到下面的語句恢復到了1個小時之前的數據！很簡單。
注意使用管理員登錄系統：
select * from 表名 as of timestamp sysdate-1/12 //查詢兩個小時前的某表數據！既然兩小時以前的數據都得到了，繼續怎麼做，知道了吧。。
如果drop了表，怎麼辦？？見下面：
drop table 表名;
數據庫誤刪除表之後恢復,不過要記得刪除了哪些表名。
flashback table 表名 to before drop;
2.查詢得到當前數據庫中鎖，以及解鎖：
查鎖
SELECT 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.關於查詢數據庫用戶，權限的相關語句：
查看所有用戶：
select * from dba_user;
select * from all_users;
select * from user_users;
查看用戶系統權限：
select * from dba_sys_privs;
select * from all_sys_privs;
10.select * from user_sys_privs;
查看用戶對象權限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
查看所有角色：
20.select * from dba_roles;
查看用戶所擁有的角色：
select * from dba_role_privs;
select * from user_role_privs;
幾個經常用到的oracle視圖：注意表名使用大寫....................
查詢oracle中所有用戶信息
select * from dba_user;
只查詢用戶和密碼
select username,password from dba_users;
查詢當前用戶信息
select * from dba_ustats;
查詢用戶可以訪問的視圖文本
select * from dba_varrays;
查詢數據庫中所有視圖的文本
select * from dba_views;
查詢全部索引
select * from user_indexes;
查詢全部表格
select * from user_tables;
查詢全部約束
select * from user_constraints;
查詢全部對象
select * from user_objects;
查看當前數據庫中正在執行的語句，然後可以繼續做很多很多事情，例如查詢執行計劃等等
(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 );
(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 );
查詢表的結構：表名大寫！！
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
行列互換：
Sql代碼
建立一個例子表：
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 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' cn, c1 cv
FROM t_col_row
where c1 is not null
UNION ALL
SELECT id, 'c2' cn, c2 cv
FROM t_col_row
where c2 is not null
UNION ALL
SELECT id, 'c3' cn, c3 cv
FROM t_col_row
where c3 is not null;
Sql代碼
建立一個例子表：
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 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' cn, c1 cv
FROM t_col_row
where c1 is not null
UNION ALL
SELECT id, 'c2' cn, c2 cv
FROM t_col_row
where c2 is not null
UNION ALL
SELECT id, 'c3' cn, c3 cv
FROM t_col_row
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 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' cn, c1 cv
FROM t_col_row
where c1 is not null
UNION ALL
SELECT id, 'c2' cn, c2 cv
FROM t_col_row
where c2 is not null
UNION ALL
SELECT id, 'c3' cn, c3 cv
FROM t_col_row
where c3 is not null;
下面可能是dba經常使用的oracle視圖吧。呵呵
Sql代碼
示例：已知hash_value:3111103299，查詢sql語句：
select * from v$sqltext
where hashvalue='3111103299'
order by piece
查看消耗資源最多的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;
查看某條SQL語句的資源消耗：
SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls
FROM V$SQLAREA
WHERE hash_Value = 228801498AND address = hextoraw('CBD8E4B0');
查詢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;
查詢oracle的版本:
select * from v$version;
查詢數據庫的一些參數:
select * from v$parameter
查找你的session信息
SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS
FROM V$SESSION WHERE audsid = userenv('SESSIONID');
當machine已知的情況下查找session
SELECT SID, OSUSER, USERNAME, MACHINE, TERMINAL
FROM V$SESSION
WHERE terminal = 'pts/tl' AND machine = 'rgmdbs1';
查找當前被某個指定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
Sql代碼
示例：已知hash_value:3111103299，查詢sql語句：
select * from v$sqltext
where hashvalue='3111103299'
order by piece
查看消耗資源最多的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;
查看某條SQL語句的資源消耗：
SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls
FROM V$SQLAREA
WHERE hash_Value = 228801498AND address = hextoraw('CBD8E4B0');
查詢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;
查詢oracle的版本:
select * from v$version;
查詢數據庫的一些參數:
select * from v$parameter
查找你的session信息
SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS
FROM V$SESSION WHERE audsid = userenv('SESSIONID');
當machine已知的情況下查找session
SELECT SID, OSUSER, USERNAME, MACHINE, TERMINAL
FROM V$SESSION
WHERE terminal = 'pts/tl' AND machine = 'rgmdbs1';
查找當前被某個指定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
示例：已知hash_value:3111103299，查詢sql語句：
select * from v$sqltext
where hashvalue='3111103299'
order by piece
查看消耗資源最多的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;
查看某條SQL語句的資源消耗：
SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls
FROM V$SQLAREA
WHERE hash_Value = 228801498AND address = hextoraw('CBD8E4B0');
查詢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;
查詢oracle的版本:
select * from v$version;
查詢數據庫的一些參數:
select * from v$parameter
查找你的session信息
SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS
FROM V$SESSION WHERE audsid = userenv('SESSIONID');
當machine已知的情況下查找session
SELECT SID, OSUSER, USERNAME, MACHINE, TERMINAL
FROM V$SESSION
WHERE terminal = 'pts/tl' AND machine = 'rgmdbs1';
查找當前被某個指定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
樹形結構connect by 排序：
Sql代碼
查詢樹形的數據結構，同時對一層裡面的數據進行排序
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 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
Sql代碼
查詢樹形的數據結構，同時對一層裡面的數據進行排序
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 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
查詢樹形的數據結構，同時對一層裡面的數據進行排序
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 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
有時候寫多了東西，居然還忘記最基本的sql語法，下面全部寫出來，基本的oracle語句都在這裡可以找到了。是很基礎的語句！
Sql代碼
在數據字典查詢約束的相關信息：
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';
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, last_name,
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, last_name, 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表空間裡面)
