Oracle的基本操作

基础知识

数据字典的定义和组成

定义：数据字典是oracle数据库中最重要的组成部分，记录了数据库的系统信息，它是只读表和视图的集合，数据字典的所有者为sys用户；用户只能在数据字典上执行查询操作,而其维护和修改是由系统自动完成的！

数据字典的组成：包括数据字典基表和数据字典视图 ，其中基表存储数据库的基本信息,普通用户不能直接访问数据字典的基表，数据字典视图是基于数据字典基表所建立的视图，普通用户可以通过查询数据字典视图取得系统信息.数据字典视图主要包括user_xxx,all_xxx,dba_xxx三种类型

1)user_tables：用于显示当前用户所拥有的所有表，它只返回用户所对应方案的所有表
比如:select table_name from user_tables;

2)all_tables:用于显示当前用户可以访问的所有表，它不仅会返回当前用户方案的所有表，还会返回当前用户可以访问的其他方案的表
如：select table_name from all_tables;

3）dba_tables:它会显示所有方案拥有的数据库表，但是查询这种数据库字典视图，要求用户必须是dba角色或是有select any table的系统权限
如：select table_name from dba_tables;

在建立用户时，oracle会把用户的信息存放到数据字典中，当给用户授予权限或角色时，oracle会将权限和角色的信息存放到数据字典中;

a)通过查询dba_users可以显示所有数据库用户的详细信息;
b)通过查询数据字典视图dba_sys_privs，可以显示用户具有的系统权限
c)通过查询数据字典视图dba_tab_privs可以显示用户具有的对象权限
d)通过查询数据字典dba_col_privs可以显示用户具有的列权限
e)通过查询数据库字典视图dba_role_privs可以显示用户所具有的角色。

基本操作

查看用户信息

1.查看所有用户：

select * from dba_users;
select * from all_users;
select * from user_users; //查看当前用户

查看角色

1.当前用户被激活的全部角色

select * from session_roles;

2.当前当前用户被授予的角色

select * from user_role_privs;

3.全部用户被授予的角色

select * from dba_role_privs;

4.查看某个用户所拥有的角色

select * from dba_role_privs where grantee='用户名';

5.查看某个角色所拥有的权限

select * from dba_sys_privs where grantee='CONNECT';

6.查看所有角色

select * from dba_roles; 

查看权限

1.基本权限查询：

select * from session_privs; --当前用户所拥有的全部权限
select * from user_sys_privs;--当前用户的系统权限
select * from user_tab_privs;--当前用户的对象权限
select * from dba_sys_privs ;--查询某个用户所拥有的系统权限
select * from role_sys_privs;--查看角色(只能查看登陆用户拥有的角色)所包含的权限

2. 查看用户的系统权限(直接赋值给用户或角色的系统权限)

select * from dba_sys_privs;
select * from user_sys_privs;

2.查看用户的对象权限：

select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;

3.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)

select * from v$pwfile_users;

扩展

1.以下语句可以查看Oracle提供的系统权限

select name from sys.system_privilege_map

2.查看一个用户的所有系统权限(包含角色的系统权限)

select privilege from dba_sys_privs where grantee='SCOTT'  
union  
select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='SCOTT' ); 

3.查询当前用户可以访问的所有数据字典视图。

select * from dict where comments like '%grant%';   

4.显示当前数据库的全称

select * from global_name;

5.查询表中存在的索引

select * from user_indexes where table_name = 'table_name';

问题集

问题 1：如何查询一个角色包括的权限？
a.一个角色包含的系统权限

select * from dba_sys_privs where grantee='角色名'  
select * from dba_sya_privs where grantee='COONNECT'; // connect要大写

另外也可以这样查看：

select * from role_sys_privs where role='角色名' 

b.一个角色包含的对象权限

select * from dba_tab_privs where grantee='角色名'   

问题 2：Oracle究竟有多少种角色?

select * from dba_roles;   

问题 3：如何查看某个用户，具有什么样的角色？

select * from dba_role_privs where grantee='用户名'  

问题4：查看哪些用户具有DBA的角色

select grantee from dba_role_privs where granted_role='DBA'; 

注意事项

1.删除表。drop table 表名：删除内容和定义，释放空间。truncate table 表名：删除内容，释放空间，但不删除定义。表被清空后，表和表的索引将重新设置成初始化大小。隐式提交，不能对truncate使用rollback命令。delete from 表名：删除内容，不删除定义，不释放空间。删除表中的数据是一行一行的删，并在事务日志中为删除的每行记录一项。释放空间的理解：delete删除行数据，再增加时ID不连续；truncate删除数据，再增加时ID是连续的。

2.having子句对group by子句所确定的行组进行控制，having子句条件中只允许涉及常量，聚合函数或group by子句中的列。

3.尽量少用IN操作符，基本上所有的IN操作符都可以用EXISTS代替。

4.不用NOT IN操作符，可以用NOT EXISTS或者外连接+替代。原因：因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开发人员在建表时，把需要索引的列设成NOT NULL。如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引，详情请查阅oracle中的位图索引资料）。

5.不用“<>”或者“!=”操作符。对不等于操作符的处理会造成全表扫描，可以用“<” or “>”代替。原因：下面的查询即使在cust_rating列有一个索引，查询语句仍然执行一次全表扫描。

select cust_Id,cust_name
from customers
where cust_rating <> 'aa';

把上面的语句改成如下的查询语句，这样，在采用基于规则的优化器而不是基于代价的优化器（更智能）时，将会使用索引。

select cust_Id,cust_name
from customers
where cust_rating < 'aa' or cust_rating > 'aa';

特别注意：通过把不等于操作符改成OR条件，就可以使用索引，以避免全表扫描

6.应该使用索引的第一个列 。如果索引是建立在多个列上 , 只有在它的第一个列 (leading column) 被 where 子句引用时 , 优化器才会选择使用该索引。这也是一条简单而重要的规则，当仅引用索引的第二个列时 , 优化器使用了全表扫描而忽略了索引。

7.Where子句中出现IS NULL或者IS NOT NULL时，Oracle会停止使用索引而执行全表扫描。办法：可以考虑在设计表时，对索引列设置为NOT NULL。这样就可以用其他操作来取代判断NULL的操作。

8.当通配符“%”或者“_”作为查询字符串的第一个字符时，索引不会被使用。

9.对于有连接的列“||”，最后一个连接列索引会无效。尽量避免连接，可以分开连接或者使用不作用在列上的函数替代。

10.如果索引不是基于函数的，那么当在Where子句中对索引列使用函数时，索引不再起作用。

select empno,ename,deptno
from emp
where trunc(hiredate)='01-MAY-81';

把上面的语句改成下面的语句，这样就可以通过索引进行查找。

select empno,ename,deptno
from emp
where hiredate<(to_date('01-MAY-81')+0.9999); 

11.Where子句中避免在索引列上使用计算，否则将导致索引失效而进行全表扫描。

12.对数据类型不同的列进行比较时，会使索引失效。比较不匹配的数据类型也是比较难于发现的性能问题之一。注意下面查询的例子，account_number是一个VARCHAR2类型，在account_number字段上有索引。下面的语句将执行全表扫描。

select bank_name,address,city,state,zip
from banks 
where account_number = 990354;

Oracle可以自动把where子句变成to_number(account_number)=990354，这样就限制了索引的使用,改成下面的查询就可以使用索引：

select bank_name,address,city,state,zip
from banks
where account_number ='990354';

特别注意：不匹配的数据类型之间比较会让Oracle自动限制索引的使用，即便对这个查询执行Explain Plan也不能让您明白为什么做了一次“全表扫描”。

13.用“>=”替代“>”。

14.UNION操作符会对结果进行筛选，消除重复，数据量大的情况下可能会引起磁盘排序。如果不需要删除重复记录，应该使用UNION ALL。

15.Oracle从下到上处理Where子句中多个查询条件，所以表连接语句应写在其他Where条件前，可以过滤掉最大数量记录的条件必须写在Where子句的末尾。

16.Oracle从右到左处理From子句中的表名，所以在From子句中包含多个表的情况下，将记录最少的表放在最后。

17.Order By语句中的非索引列会降低性能，可以通过添加索引的方式处理。严格控制在Order By语句中使用表达式。

18.不同区域出现的相同的Sql语句，要保证查询字符完全相同，以利用SGA共享池，防止相同的Sql语句被多次分析。

19.当在Sql语句中连接多个表时，使用表的别名，并将之作为每列的前缀。这样可以减少解析时间。

20.SELECT 子句中避免使用 *：ORACLE 在解析的过程中 , 会将*依次转换成所有的列名 , 这个工作是通过查询数据字典完成的 , 这意味着将耗费更多的时间。

21.sql 语句用大写的 ；因为 oracle 总是先解析 sql 语句，把小写的字母转换成大写的再执行

22.在 java 代码中尽量少用连接符“＋”连接字符串！

23.优化 GROUP BY：提高 GROUP BY 语句的效率 , 可以通过将不需要的记录在 GROUP BY 之前过滤掉 . 下面两个查询返回相同结果但第二个明显就快了许多 .
低效：

SELECT JOB , AVG(SAL) 
FROM EMP 
GROUP JOB 
HAVING JOB = ‘PRESIDENT' 
OR JOB = ‘MANAGER'

高效：

SELECT JOB , AVG(SAL) 
FROM EMP 
WHERE JOB = ‘PRESIDENT' 
OR JOB = ‘MANAGER' 
GROUP JOB

24.用 TRUNCATE 替代 DELETE：当删除表中的记录时 , 在通常情况下 , 回滚段 (rollback segments ) 用来存放可以被恢复的信息 . 如果你没有 COMMIT 事务 ,ORACLE 会将数据恢复到删除之前的状态 ( 准确地说是 恢复到执行删除命令之前的状况 )而当运用 TRUNCATE 时 , 回滚段不再存放任何可被恢复的信息 . 当命令运行后 , 数据不能被恢复 . 因此很少的资源被调用 , 执行时间也会很短 .( 译者按 : TRUNCATE 只在删除全表适用 ,TRUNCATE 是 DDL 不是 DML)。

25.尽量多使用 COMMIT：只要有可能 , 在程序中尽量多使用 COMMIT, 这样程序的性能得到提高 , 需求也会因为 COMMIT 所释放的资源而减少：COMMIT 所释放的资源：a. 回滚段上用于恢复数据的信息，b. 被程序语句获得的锁，c. redo log buffer 中的空间，d. ORACLE 为管理上述 3 种资源中的内部花费。