Oracle 表空间的创建与管理

　　Oracle数据库创建之后有一些默认的表空间随之被创建，查询数据字典 dba_data_files 可以得到数据库当前的所有表空间信息。

　　select * from v$tablespace;

　　select tablespace_name, file_name, bytes/1024/1024 as "Size(M)" from dba_data_files;

　　select tablespace_name, file_name,bytes/1024/1024 as "Size(M)" from dba_temp_files;

　　各默认表空间的作用如下图所示：

　　

 

　　1 创建表空间与临时表空间

 

　　create tablespace default_tablespace datafile '/home/oracle/oradata/default_tablespace.dbf' -- 指定表空间对应的数据文件名称与位置
　　size 100m -- 初始大小
　　autoextend on next 50M -- 容量满时是否自动增加，每次增加的幅度
　　maxsize unlimited -- 是否有最大限制
　　extent management local -- 指定表空间扩展方式是使用数据字典管理还是本地化管理，默认为本地化管理

　　logging; -- 表空间内的表在加载数据时是否产生日志，默认为产生日志，不产生日志设为：nologging

 

　　create temporary tablespace temp_tablespace
　　tempfile '/home/oracle/oradata/temp_tablespace.dbf'
　　size 50m
　　autoextend on
　　next 50m maxsize unlimited
　　extent management local;

 

　　2 设置默认表空间

　　Oracle 数据库在创建用户时，如果不指定表空间，则默认的临时表空间是 TEMP, 默认的永久表空间为SYSTEM，这样会导致应用系统与Oracle 系统竞争使用SYSTEM 表空间，极大的影响Oracle 系统效率；因此我们在数据库创建之后要为应用系统设置默认的临时表空间与永久空间；

　　查看当前的默认表空间，默认临时表空间

　　select * from database_properties where property_name='DEFAULT_PERMANENT_TABLESPACE';

　　select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

 

　　修改默认表空间

　　alter database default tablespace default_tablespace;

　　

　　修改默认临时表空间

　　alter database default temporary tablespace temp_tablespace;

 

　　3 维护表空间

　　表空间有两种状态：只读、读写；若设置表空间为只读状态，则用户不能对该表空间中进行DML操作；设置为只读状态可以保证表空间数据的完整性，通常在进行数据备份、恢复及历史数据完整性保护时，可以将表空间设为只读状态。

　　alter tablespace default_tablespace read only;
　　alter tablespace default_tablespace read write;

 

　　4 重名命表空间

　　alter tablespace default_tablespace rename to default_tablespace_new;

　　

　　5 删除表空间

　　drop tablespace test1
　　including contents
　　cascade constraints;

　　

　　6 维护表空间对应的数据文件

　　添加数据文件

　　alter tablespace test1 add datafile '/home/oracle/oradata/test2.dbf'
　　size 50m
　　autoextend on next 20m
　　maxsize unlimited;

 

　　删除数据文件

　　alter tablespace test1 drop datafile '/home/oracle/oradata/test2.dbf';

 

　　修改数据文件　

　　alter database datafile '/home/oracle/oradata/test2.dbf'
　　autoextend on next 10m maxsize unlimited;

 　　

　　alter database datafile '/home/oracle/oradata/test2.dbf'
　　autoextend off;

 

　　7 查询数据库当前使用情况

select a.file_id,b.file_name,
b.tablespace_name,
sum(b.bytes/1024/1024) "Size(M)",
sum(a.bytes)/1024/1024 "Free(M)",
(sum(b.bytes) - sum(nvl(a.bytes, 0)))/1024/1024 "Used(M)",
substr((sum(b.bytes) - sum(nvl(a.bytes, 0))) / sum(b.bytes) * 100, 1, 5) "Used%"
from
(
select file_id, sum(bytes) as bytes
from dba_free_space
group by file_id
) a,

dba_data_files b

where a.file_id = b.file_id
group by b.tablespace_name, b.file_name,a.file_id
order by b.tablespace_name;