首页 > 代码库 > Oracle常用知识总结

Oracle常用知识总结

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       

  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       

  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    

  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    

  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=http://www.mamicode.com/‘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 = http://www.mamicode.com/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 = http://www.mamicode.com/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=http://www.mamicode.com/b.hash_value and a.sid=100  

Sql代码 

示例:已知hash_value:3111103299,查询sql语句:  

select * from v$sqltext   

where hashvalue=http://www.mamicode.com/‘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 = http://www.mamicode.com/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 = http://www.mamicode.com/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=http://www.mamicode.com/b.hash_value and a.sid=100

示例:已知hash_value:3111103299,查询sql语句:

select * from v$sqltext

where hashvalue=http://www.mamicode.com/‘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 = http://www.mamicode.com/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 = http://www.mamicode.com/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=http://www.mamicode.com/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                      

Cambrault                         148        100           

Bates                             172        148           

Bloom                             169        148           

Fox                               170        148           

Kumar                             173        148           

Ozer                              168        148           

Smith                             171        148           

De Haan                           102        100           

Hunold                            103        102           

Austin                            105        103           

Ernst                             104        103           

Lorentz                           107        103           

Pataballa                         106        103           

Errazuriz                         147        100           

Ande                              166        147           

Banda                             167        147           

  

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                      

Cambrault                         148        100           

Bates                             172        148           

Bloom                             169        148           

Fox                               170        148           

Kumar                             173        148           

Ozer                              168        148           

Smith                             171        148           

De Haan                           102        100           

Hunold                            103        102           

Austin                            105        103           

Ernst                             104        103           

Lorentz                           107        103           

Pataballa                         106        103           

Errazuriz                         147        100           

Ande                              166        147           

Banda                             167        147           

  

查询树形的数据结构,同时对一层里面的数据进行排序

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表空间里面)   

        q

Oracle常用知识总结