玩转Oracle表空间和数据文件(转自百度空间)

玩转Oracle表空间和数据文件
2011-04-07 12:10
1、Oracle表空间和数据文件：
介绍：表空间是数据库的逻辑组成部分，从物理上见，数据库数据存放在数据文件中，从逻辑上见，数据库则是存放在表空间中，表空间有一个或是多个数据文件组成；

2、数据库的逻辑结构；
Oracle中逻辑结构包括表空间，段，区和块；
数据库由表空间构成，而表空间有是有段构成，而段有又是由区构成，而区又是由Oracle块构成的这样一种结构，可以提高数据库的效率；

3、表空间：表空间从逻辑上组织数据库的数据，数据库逻辑上是由一个或者多个表空间组成，通过表空间可以达到如下作用：
a、控制数据库占用的磁盘空间；
b、dba可以将不同的数据类型部署到不同的为位置，这样有利于提高i/o的性能，同时利于备份和恢复等管理工作；

4、建立表空间：
建立表空间是使用create tablespace命令完成的，需要注意的是，一般情况下，建立表空间是特权用户或者是dba来执行的，如果用其他用户来创建表空间，该用户必须具有create tablespace的系统权限；

5、建立数据库表空间；
在建立数据库后，为便于管理，最好建立自己的表空间；
SQL> create tablespace sp001 datafile 'c:\sp\sp001.dbf' size 20M uniform size 128k;

在逻辑上建立一个名为sp001的表空间，并将物理上的c:\sp\sp001.dbf文件放在逻辑上的表空进内，也就是为该表空间间建立一个数据文件：sp001.dbf，并给文件分配20M的大小，最后给表空间的区指定128K的大小；

6、使用数据表空间；
SQL> create table mypart(deptno number(4) , dname varchar2(14) , loc varchar2(13)) tablespace sp001;

Table created.
上面的语句在表空sp001中创建一张表，表名称为mypart，以前如果我们没有指定表空间，则该表就被自动创建在了默认的system表空间内；



7、改变表空间的状态；
当 建立表空间的时候，该表空间处于联机的（online）状态，此时该表空间是可以访问的，并且该表空间是可以读写的，即，可以查询该表空间的数据，而其还 可以在表空间中执行各种语句，但是在进行系统维护或是数据维护时，可能需要改变表空间的状态，一般情况下游特权用户或是dba来执行；
1）、让表空间脱机；
alter tablespace mypart offline;
2)、使表空间联机；
alter tablespace mypart online;
3)、只读表空间；
当建立表空间时候，表空间是可以读写的，如果不希望该表空间上执行update，delete，insert等操作，那么就可以将表空间修改为只读模式；
alter tablespace mypart read only；
SQL> alter tablespace sp001 read write;

注意：在将一个表空间改成只读的时候，被修改的表空间不能是系统表空进，否则会出错；

SQL> alter tablespace system read only;
alter tablespace system read only
*
ERROR at line 1:
ORA-01643: system tablespace can not be made read-only


4）、知道表空间名字，显示该表空间包含的所有的表：
select * from all_tables where tablespace_name='sp001';
5)、知道表明。查看属于哪个表空间；
注意：在查询表名的时候，表的名字一定要大写，否则就会出现查不出来；
SQL> select tablespace_name , table_name from user_tables where table_name='emp';

no rows selected

SQL> select tablespace_name , table_name from user_tables where table_name='EMP';

TABLESPACE_NAME                TABLE_NAME
------------------------------ -----
-------------------------
USERS                          EMP

SQL> select tablespace_name , table_name from user_tables where table_name='MYPART';

TABLESPACE_NAME                TABLE_NAME
------------------------------ ------------------------------
SP001                          MYPART

6）、删除表空间：
一般情况下，有特权用户或是有dba来操作，如果是其他用户，那么要求该用户具有drop tablespace的系统权限；

drop tablespace 'SP001' including contents and datafiles;

SQL> drop tablespace sp001 including contents and datafiles;

Tablespace dropped.

说明：including contents 表示删除表空进是后，删除该空间中的所有数据库对象，而datafiles表示就爱那个数据库文件也删除；

7）、扩展表空间：
表空间是有数据文件组成的，表空间的大小实际上就是数据文件相加后的大小，那么我们可以想象，假定表employee存放到data01表空间上，出示大小就是2M，当数据满2M的空间后，如果在想employee表中插入数据，就会显示空间不足的错误；
SQL> insert into mypart select * from mypart;
insert into mypart select * from mypart
*
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.MYPART by 16 in tablespace SP001

这是就需要扩展表空间，有三种方法：

1、增加数据文件：
SQL> alter tablespace sp001 add datafile 'e:/temp/sp002.dbf' size 30m;
2、增加数据文件的大小；

SQL> alter database datafile 'c:/sp001.dbf' resize 30m;
这里需要注意，数据问价的大小不要超过500M；
3、设置文件自动增长；

SQL> alter database datafile 'e:/temp/sp001.dbf' autoextend on next 5M maxsize 500M;

Database altered.


8)、移动数据文件；
有的时候，如果你的数据文件所在的磁盘损坏时，该数据文件将不能再使用，问了能够重新使用，需要将这些文件的副本移动到其他的磁盘，然后恢复；
下面举一个移动数据文件的例子；

1、确定数据文件所在的表空间；

SQL> select tablespace_name from dba_data_files where file_name='E:\TEMP\SP001.DBF';

TABLESPACE_NAME
------------------------------
SP001

注意，在知名文件路径是，文件路径名字一定要大写每个字符；
2、让表空间脱机；

SQL> alter tablespace sp001 offline;
这是为了使数据文件的一致性，将表空间转表为offline状态；
3、使用命令移动数据文件到指定的目标位置；
SQL> host move E:\temp\SP001.DBF  C:\SP001.DBF;
移动了         1 个文件。

SQL> host move E:\temp\SP002.DBF  C:\SP002.DBF;
移动了         1 个文件。

注意：这是物理上的移动；

4、执行alter tablespace命令；
当在物理上移动的数据后，还必须执行alter tablespace 命令对数据文件进行逻辑修改；

SQL> alter tablespace sp001 rename datafile 'E:/temp/sp001.dbf' to 'c:/sp001.dbf';

Tablespace altered.

SQL> alter tablespace sp001 rename datafile 'E:/temp/sp002.dbf' to 'c:/sp002.dbf';

Tablespace altered.

5、使表空间联机：
在移动了数据文件后，为了使用户可以访问该表空间，必须将其转变为online状态；
SQL> alter tablespace sp001 online;

Tablespace altered.

9）、显示表空间信息：
可以通过查询数据字典dba_tablespaces显示表空间信息；
SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
SP001



10）、显示表空间所包含的数据文件；

通过查询数据字典dba_data_files，可以显示表空间所包含的数据文件；
SQL> select file_name , bytes from dba_data_files where tablespace_name='SP001';

FILE_NAME
--------------------------------------------------------------------------------
     BYTES
----------
C:\SP001.DBF
  20971520

C:\SP002.DBF
  31457280



8、表空间小结：
1）、了解表空间和数据文件的作用；
2）、掌握使用表空间，undo表空间和临时表空间的创建方法；
3）、了解表空间的各个状态（online ， offline， read only ， read write）；
4）、了解移动数据文件的原因，以及使用alter tablespace 和alter datatable命令移动数据文件的方法；

其他表空间：索引表空间，undo表空间，临时表空间，非标准块的表空间；