oracle實用的查詢語句

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.查看所有使用者：  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. 查詢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).查看相關進程在資料庫中的會話       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.行列互換：

建立一個例子表：  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; 

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語句的動態執行計畫:          首先使用下面的語句找到語句的在執行計畫中的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$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 排序：

查詢樹形的資料結構，同時對一層裡面的資料進行排序  SELECT last_name, employee_id, manager_id, LEVEL       FROM employees        START WITH employee_id = 100        CONNECT BY PRIOR employee_id = manager_id       <SPAN style="BACKGROUND-COLOR: #ff0000"> ORDER SIBLINGS BY last_name;</SPAN>                                                                                                                                      下面是查詢結果  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';  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資料表空間裡面)          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, job_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, job_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, job_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,  SUM(salary),    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, job_id, manager_id,avg(salary) FROM     employees GROUP BY GROUPING SETS ((department_id,job_id), (job_id,manager_id));  40from中使用子查詢：返回每個部門中大於改部門平均工資的與員工資訊          SELECT  a.last_name, a.salary, a.department_id, 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, last_name, job_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, last_name, job_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:\DBtest\db_bak\imp.log

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

12.大對象欄位blob:查看blob欄位的大小:

  selectdbms_lob.getLength(欄位名)  from表名 ;  

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

--建立一個只允許在工作時間訪問的視圖  create or replace view newviewemp  as  select * from 表名   where exists(select 1 from dual where sysdate >=         to_date(to_char(sysdate, 'yyyy-mm-dd ') || '08:00:00', 'yyyy-mm-dd hh24:mi:ss')     and sysdate <         to_date(to_char(sysdate, 'yyyy-mm-dd ') || '18:00:00', 'yyyy-mm-dd hh24: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;

本文出自 “IT精英的集中區” 部落格，轉載請與作者聯絡！