oracle表空间管理及操作实用案例

Oracle表空间管理及操作详解（附带案例）一：表空间概述

表空间是数据库的逻辑组成部分，从物理上讲，数据库数据存放在数据文件中；数据库逻辑上讲是由一个或者多个表空间组成，数据文件则是存放在表空间中，表空间有一个或者多个数据文件组成即表空间有数据文件构成的。

数据库，表空间，段，区，块，按大小排列依次是：数据库---》表空间-----》段-----》区----》块。

1.建立表空间的作用：

（1.）控制数据库占用的磁盘空间。（2）.dba可以将不同的数据类型部署到不同的位置，这样有利于提高i/o性能，同时有利与备份和恢复等管理操作。

二：管理表空间和数据文件。

（1）建立表空间create tablespace;

一般情况下建立表空间是特权用户或者dba来执行的，如果其他用户来创建表空间，则用户建必须要具有create tablespace的系统权限。（2）建立数据表空间

在建立数据库后，为便于管理表，最好建立自己的表空间。

create tablespace sp02 datafile 'd:\test\sp02.dbf' size 20m uniform size 128k

说明：执行完上述命令后，会建立名称为sp02的表空间，并为该表空间建立名称为sp02.dbf的数据文件，区的大小为128k。

（3.）在表空间建表

create table tablesp02(ID number(5),name varchar2(20)) tablespace sp02

（4）显示当前用户拥有哪些表。

select * from tab；

（5）.知道表空间名，显示该表空间包括的所有表。

select * from all_tables where tablespace_name='表空间名'。

注：拥有权限不同的用户在进行以上操作得到的结果可能不同。System可以查看得到某空间的所拥有表。（比如users表空间）

（6）知道表名，查看该表属于那个表空间。

select TABLESPACE_NAME, TABLE_NAME from user_tables where TABLE_NAME='EMP';

(7)知道表名，查看该表属于那个表空间,,属于哪个用户。

select owner,TABLESPACE_NAME, TABLE_NAME from

all_tables where TABLE_NAME='EMP';

（8）改变表空间的状态

使表空间可读写

alter tablespace 表空间名 read write；

删除表空间，一般情况下由特权用户或者dba来操作。具有drop tablespace系统权限。

drop tablespace ‘表空间’includeing contens and datafiles；

说明：includeing contens表示删除表空间时，删除该空间的所有数据库对象而datafiles表示将数据库文件也删除。

（9）改变表空间的状态

（1）使用空间脱机

alter tablespace 表空间名 offline

（2）使用空间联机

alter tablespace表空间名online

（3）只读表空间

建立表空间时，表空间可以读写，如果不希望在表空间上执行update，delete，insert操作。那么可以将表空间修改为只读。

alter talbespace sp01 read only；

三：移动数据文件：

（1）确定数据文件所在的表空间(知道表属于哪个表空间)

Select tablespace_name from dba_data_files where file_name='D:\TEST\05.DBF';

（2）使用表空间脱机

确保数据文件的一致性，将表空间转换为offline的状态。alter tablespace sp01 offline；

（3）使用命令移动数据文件到指定的目标位置。

host move d:\test\sp01.dpf c:\test\sp01.dbf；

（4）执行alter tablespace命令

在物理上移动了数据后，还必须执行alter tablespace命令对数据库文件进行逻辑修改。

alter tablespace sp01 rename datefile ‘d：\sp01.dbf’ to 'c:\sp01.dbf'

(5)使得表空间联机

在移动了数据文件后，为了使用户可以访问该表空间，必须将其变为online状态：

alter tablespace sp01 online；

四：显示表空间信息：

查询数据视图字典dba_tablespace,显示表空间的信息：

select tablespace_name from dba_tablespaces

显示某个表空间的详细信息：

select * from dba_tablespaces where

TABLESPACE_NAME='SP05';

显示表空间所包含的数据文件。查询数据字典视图dba_data_files。可显示表空间所包含的数据文件如下：

select file_name,bytes from dba_data_files where tablespace_name='SP05';

常用表空间，undo表空间和临时表空间，非标准块的表空间。了解表空间的各个状态：如何改变表空间的的状态的方法。（online，offline，read write，read only）

system是系统表空间，不能作为只读，如果是普通表空间，那么我们可以设为只读。

五：附加案例：建一个表空间，在表空间里面建表，并且增加表空间的存储空间。

1.建立表空间

create tablespace sp05 datafile 'd:\test\sp05.dbf' size 20m uniform size 128k

2.建表

create table tablesp05(ID number(5),name varchar2(20)) tablespace sp02

3.insert into tablesp05 select * from tablesp05；（重复多步提示空间不足）

4，拓展表空间，扩大存储空间。

alter tablespace sp05 add datafile 'D:\test\05.dbf' size 20m;

拓展表空间的存储空间这里有三种方法：

（1）增加数据文件alter tablespace sp05 add datafile 'D:\test\05.dbf' size 20m;

（2）修改数据文件大小

alter database datafile 'd:\test\sp05.dbf' resize 50m;数据文件不要超过500m。