首页 > 代码库 > 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
因为密码输入错误多次用户自动被锁定.
解决办法: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视图:注意表名使用大写....................
查询全部索引
select * from user_indexes;
查询全部表格
查看当前数据库中正在执行的语句,然后可以继续做很多很多事情,例如查询执行计划等等
(1).查看相关进程在数据库中的会话
(1).查看相关进程在数据库中的会话
查询表的结构:表名大写!!
select t.COLUMN_NAME,
行列互换:
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
where c1 is not null
UNION ALL
SELECT id, ‘c2‘ cn, c2 cv
where c2 is not null
UNION ALL
SELECT id, ‘c3‘ cn, c3 cv
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
where c1 is not null
UNION ALL
SELECT id, ‘c2‘ cn, c2 cv
where c2 is not null
UNION ALL
SELECT id, ‘c3‘ cn, c3 cv
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
where c1 is not null
UNION ALL
SELECT id, ‘c2‘ cn, c2 cv
where c2 is not null
UNION ALL
SELECT id, ‘c3‘ cn, c3 cv
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语句的动态执行计划:
查询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语句的动态执行计划:
查询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语句的动态执行计划:
查询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
下面是查询结果
LAST_NAME
------------------------- ----------- ---------- ----------
King
Cambrault
Bates
Bloom
Fox
Kumar
Ozer
Smith
De Haan
Hunold
Austin
Ernst
Lorentz
Pataballa
Errazuriz
Ande
Banda
Sql代码
查询树形的数据结构,同时对一层里面的数据进行排序
SELECT last_name, employee_id, manager_id, LEVEL
下面是查询结果
LAST_NAME
------------------------- ----------- ---------- ----------
King
Cambrault
Bates
Bloom
Fox
Kumar
Ozer
Smith
De Haan
Hunold
Austin
Ernst
Lorentz
Pataballa
Errazuriz
Ande
Banda
查询树形的数据结构,同时对一层里面的数据进行排序
SELECT last_name, employee_id, manager_id, LEVEL
下面是查询结果
LAST_NAME
------------------------- ----------- ---------- ----------
King
Cambrault
Bates
Bloom
Fox
Kumar
Ozer
Smith
De Haan
Hunold
Austin
Ernst
Lorentz
Pataballa
Errazuriz
Ande
Banda
有时候写多了东西,居然还忘记最基本的sql语法,下面全部写出来,基本的oracle语句都在这里可以找到了。是很基础的语句!
Sql代码
在数据字典查询约束的相关信息:
SELECT constraint_name, constraint_type,search_condition
FROM
2对表结构进行说明:
3查看用户下面有哪些表
4查看约束在那个列上建立:
10结合变量查找相关某个表中约束的相关列名:
12查询数据字典看中间的元素:
SELECT
FROM
WHERE
OR
14查询对象类型:
SELECT DISTINCT object_type FROM
17改变对象名:(表名,视图,序列)
18添加表的注释:
20查看视图结构:
23在数据字典中查看视图信息:
25查看数据字典中的序列:
33得到所有的时区名字信息:
34显示对时区‘US/Eastern’的时区偏移量
35显示数据库时区和会话时区的值:
13普通的建表语句:
CREATE TABLE dept
(deptno
dname
loc
15使用子查询建立表:
6添加列:// alter table EMP add column (dept_id number(7));错误!!
7删除一列:
8添加列名同时和约束:
9改变列://注意约束不能够修改 的!!
24增加一行:
5添加主键:
11添加一个有check约束的新列:
16删除表:
19创建视图:
21删除视图:
22找到工资最高的5个人。(top-n分析)(行内视图)
26建立同义词:
或者
27建立序列:(注意,这里并没有出现说是哪个表里面的序列!!)
28使用序列:
29建立索引://默认就是nonunique索引,除非使用了关键字:unique
30建立用户:(可能有错,详细查看帮助)
Oracle常用知识总结