轻松掌握Oracle数据库管理

作为一个DBA的职责如下:
1,安装升级数据库
2,建表,表空间,表,视图,索引...
3,制定并实施备份与恢复计划
4,数据库权限管理,调优,故障排除.
5,还要能参与项目开发,会写sql语句,存储过程,触发器,规则,约束和包.

查看初始化参数命令:
show parameter;

数据库表的备份与恢复
导出具体分为:导出表,导出方案,导出数据库.
我们可以用exp命令来完成导出的操作.
该命详解:

导出自已的表:
exp userid=scott/tiger@test tables=(emp,dept) file=d:/e1.dmp;
特别说明:
在导入和导出的时候,要到Oracle目录的Bin目录下去导.
在导出其他用户的表时只要在表名前指名方案名即可,如:
exp userid=system/manager@test tables=(scott.emp) file=d:/xx.dmp;

导出表结构
exp userid=scott/tiger@test tables=(emp) file=d:/tmp.dmp rows=n;

使用直接导出方式,用于处理大表,速度较快
exp userid=scott/tiger@test tables=(emp) file=d:/tmp.dmp direct=y;

导出方案:
导出自已的方案:
exp scott/tiger@test owner=scott file=d:/scott.dmp;

导出别人的方案,需要相应的权限:
exp system/manager@test owner=(system,scott) file=d:/tmp.dmp;

导出数据库,花的时间可能会比较长,必须要有相应的权限:
exp userid=system/manager@test full=y inctype=complete file=d:/tmp.dmp;
-----------------------------------------------
导入
导入自已的表(由谁导出的就得由谁导入,不然会发出警告,注意外键关系
如果是空表的话, 任意用户都可导入):
imp userid=scott/tiger@test tables=(emp) file=d:/xx.dmp;

导入表到其它用户:
imp userid=system/manager@test tables=(emp) file=d:/xx.dmp touser=scott;

导入表结构:
imp userid= tables=() file= rows=n;

导入数据:
imp userid=scott/tiger@test tables=(emp) file=d:/tmp.dmp ignore=y;

导入方案:
导入自已的方案
import userid=scott/tiger@test file=d:/tmp.dm;

导入别人的方案:
imp userid=system/manager file=xx fromuser=system touser=scott;

导入数据库:
imp userid=system/manager full=y file=d:/tmp.dmp;

=====================================================
数据字典与动态性能视图
数据字典是数据库中最重要的部分,动态性能视图是启动后的信息.
数据字典记录数据库的系统信息.用户只能在数据字典上进行查询操作,
它的维护和修改是由系统自动完成的.
数据字典视图包括:user_xx,all_xx,dba_xx三种类型.
用法如下:

显示当前用户所拥有的所有的表
select table_name from user_tables;

显示当前用户可以访问到的表
select table_name from all_tables;

显示所有方案中的所有表:
select table_name from dba_tables;

=================================
用户名,权限,角色
可以通过查询dba_users可以显示所有数据库用户的详细信息.
select username, password from dba_users;

查看scott拥有的角色
select * from dba_role_privs where grantee='SCOTT';

查看Oracle中所有角色
select * from dba_roles;

查看角色中的权限
select * from dba_role_privs where grantee='用户名';

查看所有的系统权限
select * from system_privilege_map order by name;

查看所有的对象权限
select distinct privilege from dba_tab_privs;

Oracle中的权限分为系统权限和对象权限.

创建用户
create user ken identified by 123446;

为用户授于相应的系统权限.
grant create session,create table to ken with admin option;
grant create view to ken;

回收系统权限
revoke create session from ken;

* 请注意系统权限不是级联回收.

授于对象权限一般是由对象的所有者进行的,但是在9I后dba用户可以授权,授权时如带with grant option,表示授权授权权力.
create user monkey identified by s123;
grant create session to monkey; // 系统权限,create session 表示可登录权限.

我们利用scott用户为monkey用户授权
grant select on emp to monkey; // 查询对象权限
grant update on emp to monkey; // 修改
grant delete on emp to monkey; // 删除

显然上面的方法比较麻烦,因此我们可如下操作:
grant all on emp to monkey; // 表示对象上的所有权限.

同时,我们也可对表中的某列进行授权
grant update on emp(sal) to monkey; // 只能修改emp表上的sal这个字段
grant select on emp(ename,sal) to monkey;
grant alter on emp to monkey;
grant index on scott.emp to monkey [with grant option]; // 授于建立索引权限

回收对象权限,可以由授于者收回,也可由dba用户收回,同时,对象权限是会级联回收的, 这一点与系统权限是不一样的.
create user u1 identified by u1;
create user u2 identified by u2;
grant connect,resource to u1/u2;
grant update on scott.emp to u1 with grant option;
conn u1/u1;
grant update on scott.emp to u2;
conn / as sysdba;
revoke update on scott.emp from u1;
此时u2的相应的更新权限也被回收了,进一步说明了对象权限与系统权限是不同的,前者是级联回收的,而后者却不是.

而为了简化管理在此要引入角色的概念,角色是相关权限的命令集合.

常见预定义角色connect,resource,dba

对于一般应用的开发人员需要的大部分权限都包含在了connect,resource中了.而dba角色爱上你的眼睛有所有的系统权限,而dba角色不具备sysdba和sysoper的特权.不能关闭打开数据库.

自定义角色首先需要相应的权限,不然是创建不了的.
create role 角色名 not identified; // 修改这个角色时不需要输入密码
create role 角色名 indetified by manager; // 当修改时需要密码,为manager

为角色授权
grant create session to 角色名 with admin option;
conn scott/tiger@test;
grant slect on scott.emp to 角色名
grant insert,update,delete on scott.emp to 角色名

角色有了,要分配给用户才会有意义,一般是由dba来完成的.
grant 角色名 to user_xx [with admin option]; // with admin option表示可继续将此角色授下去.

删除角色,一旦删除,那么拥有此角色的用户会失去相应的权限
conn system/manager;
drop role 角色名;

显示所有角色: select * from dba_roles;

动态性能视图
系统在启动时会建立,而在退出时会删除

---------------------------------------
管理表空间和数据文件
表空间是数据库的逻辑组成部分.

数据库的逻辑结构:表空间,段,区,块

利用表空间我们可以把不同类型不同作用的数据文件放到不同的磁盘,从而提高了IO效率与并发性能,并且利于备份和恢复.

建立表空间(需要相应的权限: create tablespace ):
create tablespace data01 datafile 'd:/xx.dbf' size 20M uniform size 128k

有了表空间后,我们可以使用表空间:
create table xxx ... tablespace data01;

表空间脱机
alter tablespace users offline;

表空间联机
alter tablespce users online;

只读表空间
alter tablespace 表空间名 read only;

删除表空间
drop tablespace 表空间名 including contents and datafiles;

扩展表空间
增加数据文件
alter tablespace ts01 add datafile 'xxx' size 20M;

增加数据文件的大小
alter tablespace 表空间名 'd:/xx.dbf' resize 20M;

设置文件的自动增长
alter tablespace 表空间名 'd:/xx.dbf' autoextend on next 10M maxsize 500M;

==============================
维护数据完整性
数据的完整性用于确保数据库数据遵从一定的商业和逻辑规则.
我们可以通过约束,触发器,应用程序(过程,函数)三种方法来实现,而约束易于维护,性能高,所以是首选.
约束包括:not null, unique, primary key, foreign key, check;
unique定义后允许为空.
一张表最多只能有一个主键,但是可以有多个unique约束.

增加指定列不允许为空
alter table goods modify goodsName not null;

增加unique约束
alter table customer add constraint unique_idcard unique(cardId);
alter table customer add constraint add_check check(addr in(xx,xx,xx));

删除约束
alter table customer drop constraint 约束名称;

特别说明:在删除主键约束时,可能会碰到错误,因为存在外键引用的况 ,在这种情况下我们可以这样做:
alter table xx drop primary key cascade;

查看约束信息可通过数据字典user_constraints;

-------------------
索引,对于我们要经常查询而又不常更新的列,我们常常会建立相应的索引.

创建单列索引
create index name_index on customer(name);

复合索引
create index 索引名 on 表名(字段1,字段2);

使用原则
1,在大表上建立索引才会有意义.
2,在where子句或是连接条件上经常引用的列上建立索引.
3,索引层次不超过4层.

索引缺点分析
1,建立索引,系统要占用为表的1.2倍的空间
2,更新时,索引也要用时间来对其更新

实践表明,不好的索引不但于事无补,反而会降低系统性能.

在如下情况建立索引是不恰当的
1,很少用的.
2,逻辑性字段.

显示索引信息数据字典:dab_indexs, user_indexs

Oracle会为unique字段加上索引.