Oracle创建表空间和用户及权限赋予和快速清库

上一章讲了Oracle数据库创建的整个过程这一章讲一下建表空间和用户，一起对其用户的权限赋予。

第一让我们先来认识一下什么是表空间。

	表空间是数据库的逻辑划分，一个表空间只能属于一个数据库。所有的数据库对象都存放在指定的表空间中。但主要存放的是表， 所以称作表空间。
	Oracle数据库中至少存在一个表空间，即SYSTEM的表空间。SQL Server数据库与Oracle数据库之间最大的区别要属表空间设计。Oracle数据库开创性地提出了表空间的设计理念，这为Oracle数据库的高性能做出了不可磨灭的贡献。可以这么说，Oracle中很多优化都是基于表空间的设计理念而实现的。
	典型应用一：控制用户所占用的表空间配额。在一些大型的数据库应用中，我们需要控制某个用户或者某一组用户其所占用的磁盘空间。这就好像在文件服务器中，需要为每个用户设置磁盘配额一样，以防止硬盘空间耗竭。所以，在数据库中，我们也需要限制用户所可以使用的磁盘空间大小。为了达到这个目的，我们就可以通过表空间来实现。我们可以在Oracle数据库中，建立不同的表空间，为其设置最大的存储容量，然后把用户归属于这个表空间。如此的话，这个用户的存储容量，就受到这个表空间大小的限制。
典型应用二：控制数据库所占用的磁盘空间。
有时候，在Oracle数据库服务器运行过程中，可能运行不止一个服务。除了数据库服务器外，可能还有邮件服务器等应用系统服务器。为此，就需要先对Oracle数据库的磁盘空间作个规划，否则，当多个应用程序服务所占用的磁盘空间都无限增加时，最后可能导致各个服务都因为硬盘空间的耗竭而停止。所以，在同一台服务器上使用多个应用程序服务时，我们需要先为各个应用服务规划分配磁盘空间，各服务的磁盘空间都不能够超过我们分配的最大限额，或者超过后及时地提醒我们。只有这样，才能够避免因为磁盘空间的耗竭而导致各种应用服务的崩溃。
典型应用三：灵活放置表空间，提高数据库的输入输出性能。数据库管理员还可以将不同类型的数据放置到不同的表空间中，这样可以明显提高数据库输入输出性能，有利于数据的备份与恢复等管理工作。因为我们数据库管理员在备份或者恢复数据的时候，可以按表空间来备份数据。如在设计一个大型的分销系统后台数据库的时候，我们可以按省份建立表空间。与浙江省相关的数据文件放置在浙江省的表空间中，北京发生业务记录，则记录在北京这个表空间中。如此，当浙江省的业务数据出现错误的时候，则直接还原浙江省的表空间即可。很明显，这样设计，当某个表空间中的数据出现错误需要恢复的时候，可以避免对其他表空间的影响。另外，还可以对表空间进行独立备份。当数据库容量比较大的时候，若一下子对整个数据库进行备份，显然会占用比较多的时间。虽然说Oracle数据库支持热备份，但是在备份期间，会占用比较多的系统资源，从而造成数据库性能的下降。为此，当数据库容量比较大的时候，我们就需要进行设置多个表空间，然后规划各个表空间的备份时间，从而可以提高整个数据库的备份效率，降低备份对于数据库正常运行的影响。
典型应用四：大表的排序操作。我们都知道，当表中的记录比较多的时候，对他们进行查询，速度会比较慢。第一次查询成功后，若再对其进行第二次重新排序，仍然需要这么多的时间。为此，我们在数据库设计的时候，针对这种容量比较大的表对象，往往把它放在一个独立的表空间，以提高数据库的性能。
典型应用五：日志文件与数据文件分开放，提高数据库安全性。默认情况下，日志文件与数据文件存放在同一表空间。但是，这对于数据库安全方面来说，不是很好。所以，我们在数据库设计的过程中，往往喜欢把日志文件，特别是重要日志文件，放在一个独立的表空间中，然后把它存放在另外一块硬盘上。如此的话，当存放数据文件的硬盘出现故障时，能够马上通过存放在另一个表空间的重做日志文件，对数据库进行修复，以减少企业因为数据丢失所带来的损失。当然，表空间的优势还不仅仅这些，企业对于数据库的性能要求越高，或者数据库容量越大，则表空间的优势就会越大。
建立表空间与建立用户的顺序关系
在数据库设计的时候，我们建议数据库管理员按如下顺序设置表空间。
第一步：建立表空间。
在设计数据库的时候，首先需要设计表空间。我们需要考虑，是只建立一个表空间呢，还是需要建立多个表空间，以及各个表空间的存放位置、磁盘限额等等。
到底设计多少个表空间合理，没有统一的说法，这主要根据企业的实际需求去判断。如企业需要对用户进行磁盘限额控制的，则就需要根据用户的数量来设置表空间。当企业的数据容量比较大，而其又对数据库的性能有比较高的要求时，就需要根据不同类型的数据，设置不同的表空间，以提高其输入输出性能。
第二步：建立用户，并制定用户的默认表空间。
在建立用户的时候，我们建议数据库管理员要指定用户的默认表空间。因为我们在利用CREATE语句创建数据库对象，如数据库表的时候，其默认是存储在数据库的当前默认空间。若不指定用户默认表空间的话，则用户每次创建数据库对象的时候，都要指定表空间，显然，这不是很合理。
另外要注意，不同的表空间有不同的权限控制。用户对于表空间A具有完全控制权限，可能对于表空间B就只有查询权限，甚至连连接的权限的都没有。所以，合理为用户配置表空间的访问权限，也是提高数据库安全性的一个方法。

第二让我们了解一下如何创建一个表空间和一个临时表空间

1.表空间

创建一个表空间sql如下，但前提是你必须在权限高的用户下才能进行创建。

建立表空间(一般建N个存数据的表空间和一个索引空间)：
create tablespace 表空间名
datafile ' 路径(要先建好路径)\***.dbf  ' size *M
tempfile ' 路径\***.dbf ' size *M
autoextend on  --自动增长
--还有一些定义大小的命令，看需要
 default storage(
 initial 100K,
 next 100k,
);

例

创建表空间：create tablespace ts1 datafile 'C:\tablespace\ts1.dbf' size 50M;
自动扩展大小：create tablespace ts2 datafile 'C:\tablespace\ts2.dbf' size 50M autoextend on next 10M;
设置最大空间：create tablespace ts3 datafile 'C:\tablespace\ts3.dbf' size 50M autoextend on next 10M maxsize 1024M;
 
表空间改名：alter tablespace ts1 rename to tss1;
删除表空间：drop tablespace ts2 including contents and datafiles;

2.临时表空间

创建一个临时表空间sql语句如下其和创建表空间的区别在于多了个TEMPORARY

CREATE TEMPORARY TABLESPACE tbs_temp        --创建临时表空间，要在TABLESPACE前加上TEMPORARY
  TEMPFILE 'd:\tbs_temp01.dbf'SIZE 50M,     --这里使用TEMPFILE
           'e:\tbs_temp02.dbf'SIZE 50M,
AUTOEXTEND ON NEXT 2M;

三让我们了解一些表空间的操作

调整表空间的大小
ALTER DATABASE DATAFILE '数据文件全路径' RESIZE <新的大小>
改变表空间的读写状态
ALTER TABLESPACE <表空间名> READ WRITE;--使表空间可读写
删除表空间
DROP TABLESPACE <表空间名>
查询表空间
SELECT * from dba_tablespaces;
利用数据字典dba_data_files查看数据文件位置 
SELECT * from dba_data_files;
修改账户默认表空间：alter user 账户名 default tablespace 表空间名;
修改账户默认临时表空间：alter user 账户名 temporary tablespace 表空间名;

四让我们了解一下怎么创建一个用户并给其赋权限

创建用户sql如下

建立：create user 用户名 identified by "密码";

给用户赋予不同的权限sql如下：

alter user 用户名 quota unlimited on 表空间;
或 alter user 用户名 quota *M on 表空间
授予用户的基本权限
grant create session to 用户名;赋予用户创建回话的权利
grant create table to  用户名;赋予用户创建表的权力
grant create tablespace to  用户名;赋予用户创建表空间的权利
grant create view to  用户名;赋予用户创建视图的权利
grant connect,resource to demo;赋予用户connect,resource（三大权限中的俩一般给普通用户赋予的权限）
RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
CONNECT：拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。
grant create any sequence to demo;赋予用户创建序列的权利
grant create any table to demo;赋予用户创建表删除表修改表增加查询表的权利
grant delete any table to demo;
grant insert any table to demo;
grant select any table to demo;
grant update any table to demo;
grant unlimited tablespace to demo;
1. 系统权限unlimited tablespace是隐含在dba, resource角色中的一个系统权限. 当用户得到dba或resource的角色时, unlimited tablespace系统权限也隐式受权给用户.
2. 系统权限unlimited tablespace不能被授予role, 可以被授予用户.
3. 系统权限unlimited tablespace不会随着resource, dba被授予role而授予给用户.
grant execute any procedure to demo;赋予用户创建存储过程的权利
grant create any view to demo;赋予用户创建视图的权限

五给用户赋予dba权限

给用户赋予dba权限sql如下

--已知被赋予权限的用户名为：batch
--第一步 登陆
sqlplus /nolog
sql>conn /as sysdba;
--第二步 查询所有用户名，可以找到batch用户
sql>select  username from dba_users;
--第三步 赋予权限
sql>grant dba to batch;
--解除权限是：
sql>revoke dba from batch;

解释：
sqlplus /nolog
--运行sqlplus命令，进入sqlplus环境，nolog参数表示不登录。这种登陆方式比较安全；
sql>conn /as sysdba
--以系统管理员(sysdba)的身份连接数据库；如果需要对数据库进行管理操作，那么需要以这种方式登录数据库，或者connect   sys@service_name as sysdba，其中你配置的客户tnsname服务名
--操作系统身份认证登陆。oracle在登录时，有三种身份认证方式：操作系统身份认证、密码文件认证、数据库认证。而conn /as sysdba是属于操作系统认证。 为什么这样说呢？你当前电脑开机时登录的用户，也就是进入操作系统的用户，例如是gooooal，它在你电脑的ora_dba组中。
可以在“我的电脑”单击右键，找到“管理”，选择“本地用户和组”，发现有一个组叫“ora_dba”，双击它，看到成员列表中有“gooooal”。
也就是在conn /as sysdba，oracle会进行操作系统验证，发现你当前登录的用户就属于ora_dba组，因此才可以登录成功。
sql>select username from dba_users;
--查询oracle中所有用户名
sql>grant dba to batch;
--给用户赋予dba权限，dba是一个角色，该角色具有数据库所有的权限
sql>revoke dba from username;

扩展：
oracle数据库中涉及到用户权限的三个表，dba_users，all_users，user_users有什么区别?
答：DBA_*意为DBA拥有的或可以访问的所有的对象。就是是查全库所有的；ALL_*意为某一用户拥有的或可以访问的所有的对象。就是当前用户可以看到的；USER_*意为某一用户所拥有的所有的对象。就是当前用户的。

六清除当前用户的所有表及序列

一下是利用查询拼出删除sql语句将其复制利用命令行界面统一执行实现删除。

oracle删除当前用户下的所以表、视图、序列、函数、存储过程、包

--delete tables 
select 'drop table ' || table_name ||';'||chr(13)||chr(10) from user_tables;  
 
--delete views   
select 'drop view ' || view_name||';'||chr(13)||chr(10) from user_views;   

--delete seqs 
select 'drop sequence ' || sequence_name||';'||chr(13)||chr(10) from user_sequences; 
 
--delete functions 
select 'drop function ' || object_name||';'||chr(13)||chr(10) from user_objects where object_type='FUNCTION';   

--delete procedure 
select 'drop procedure ' || object_name||';'||chr(13)||chr(10) from user_objects where object_type='PROCEDURE';   

--delete package  
select 'drop package ' || object_name||';'||chr(13)||chr(10) from user_objects where object_type='PACKAGE';   

什么是package？

在一个大型项目中，可能有很多模块，而每个模块又有自己的过程、函数等。、而这些过程、函数默认是放在一起的（如在PL/SQL中，过程默认都是放在一起 的，即Procedures中），这些非常不方便查询和维护，甚至会发生误删除的事件。PL/SQL为了满足程序模块化的需要，引入了包的构造。通过使用包就可以分类管理过程和函数等。
（1）包是一种数据库对象，相当于一个容器。将逻辑上相关的过程、函数、变量、常量和游标组合成一个更大的单位。用户可以从其他 PL/SQL 块中对其进行引用
（2）包类似于C++和JAVA语言中的类，其中变量相当于类中的成员变量，过程和函数相当于类方法。把相关的模块归类成为包，可使开发人员利用面向对象的方法进行开发，具有面向对象程序设计语言的特点，
（4）PL/SQL的包具有信息隐蔽性(information hiding)，仅在算法和数据结构设计有关层可见。可将过程说明和过程体组成一个程序单位。也可将过程说明与它的过程体分开。也可在包中定义过程，而该过程在包说明中没有定义过程说明，这样定义的过程仅在包内使用。
（5）在PL/SQL程序设计中，使用包不仅可以使程序设计模块化，对外隐藏包内所使用的信息（通过使用私用变量），而写可以提高程序的执行效率。因为，当程序首次调用包内函数或过程时，ORACLE将整个包调入内存，当再次访问包内元素时，ORACLE直接从内存中读取，而不需要进行磁盘I/O操作，从而使程序执行效率得到提高。
想要看Oracle数据库的创建请看上一篇：上一篇