ORACLE - 管理表空间和数据文件

ORACLE表空间是一个逻辑分区,一个数据文件只能属于一个表空间,一个表空间可以拥有多个数据文件。

一般情况下,如果一个实例分配给多个应用使用,需要创建不同的表空间,每个用户使用自己的表空间。

一、表空间的创建与授权

首先查看表空间的使用情况:

select tablespace_name,sum(bytes)/1024/1024 as MB from dba_data_files group by tablespace_name;

---剩余容量(在固定大小的情况下)
select tablespace_name, count(*) as extends, round(sum(bytes) / 1024 / 1024, 2) as MB, sum(blocks) as blocks from dba_free_space group by tablespace_name;

--查看表空间与数据文件对应关系
select tablespace_name, file_name, bytes/1024/1024 from dba_data_files;

--查询表空间存放的对象及其拥有者
 select distinct segment_type, owner, tablespace_name from dba_segments where tablespace_name=‘SC‘;

 

1. 创建表空间

create tablespace you_tabs_name datafile  /opt/oracle/oradata/orcl/ts_01.dbf size 50m autoextend on;

2. 创建临时表空间(一般用于数据查询时排序操作,在数据量较大内存不足时)

SQL> create temporary tablespace temp2 tempfile /opt/oracle/oradata/temp02.dbf size 100m autoextend on;
--可以为新创建的表空间增加数据文件
SQL> alter tablespace temp2 add tempfile /opt/oracle/oradata/temp03.dbf size 100m ;

3. 创建一个用户指定默认表空间和临时表空间

create user youuname identified by “yourpwd” default tablespace ts_01 temporary tablespace temp2 profile default quota unlimited on ts_01

4. 创建完成后可查询某个用户的默认表空间

select default_tablespace, temporary_tablespace, d.username  from dba_users d where d.username=SCOTT;

5.如果用户已经创建,可以授权新的表空间给用户使用

alter user uname quota unlimited on  ts_01;
alter user scott default tablespace ts_01; -- 更改用户的默认表空间

 

6. 当一个表空间不再使用时,可对该表空间下的数据文件离线(离线前千万别在操作系统层删除数据文件)

alter database datafile /opt/oracle/oradata/orcl/ts_01.dbf offline;

 

7. 设置表空间为只读,用于备份和恢复

表空间只读状态下,不能进行insert,update,delete,但可以删除索引和目录。
必须满足以下条件:
(1)    必须为online
(2)    不能包含任何回滚段
(3)    表空间不能在归档模式或数据发行中
SQL>alter tablespace tablespace_name read only;
恢复读写状态
SQL>alter tablespace tablespace_name read write;

 

8. 重命名表空间

更变名称后,原表空间存放的对象也同时更变,包括用户对于这表空间的权限也同步更改
条件:
(1)    只能对一般表空间更名,不能更变system,sysaux更名
(2)    必须处于online状态
SQL>alter tablespace old_name rename to new_name;

 

 

二、临时表空间操作

临时表空间一般用于数据量比较大的查询结果中排序操作(当可使用内存比较小的情况下):

1. 查看目前现有的临时表空间

SQL> select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
SQL> select name,bytes/1024/1024 from v$tempfile;

2. 创建临时表空间和更改一个用户临时表空间

SQL> create temporary tablespace temp2 tempfile ‘/opt/oracle/oradata/temp02.dbf‘ size 100m autoextend on;
--可以为新创建的表空间增加数据文件
SQL> alter tablespace temp2 add tempfile ‘/opt/oracle/oradata/temp03.dbf‘ size 100m ;

--更改用户临时表空间

  SQL> alter user scott temporary tablespace temp1;

 

3. 可修改数据库管理默认使用的临时表空间

SQL> alter database default temporary tablespace temp1;

4. 如果一个临时表空间没有用户在使用时,可删除

SQL> drop tablespace temp;
然后再通过操作系统命令删除数据文件即可

5. 更改表空间大小或设置自动扩展

alter database tempfile /opt/oracle/oradata/orcl/sc_temp_01.dbf resize 50M;
alter database tempfile ‘opt/oracle/oradata/oracle/sc_temp_01.dbf‘ autoextend on next 3M maxsize unlimited;

6. 删除一个数据文件

alter database tempfile /opt/oracle/oradata/orcl/sc_temp_01.dbf offline;//先把文件离线
alter database tempfile ‘/opt/oracle/oradata/orcl/sc_temp_01.dbf‘ drop;

--彻底删除一个临时表空间,谨慎使用
drop tablespace temp1 including contents and datafiles

 

三、UNDO表空间

undo表空间作用:回退事务,读一致性,事务恢复(断电,内存故障,后台进程故障等),闪回操作,UNDO 不属于任何用户,由库公用,默认是自动增长的,直至耗完磁盘.

 

--显示正在使用的undo表空间
show parameter undo_tablespace;

--查询所有undo表空间
select tablespace_name from dba_tablespaces where contents=UNDO;

 

 

 

1 .创建UNDO表空间,这里设置固定大小2G

SQL>create undo tablespace undotbs02 datafile /xx/xxx/ size 2G;

2. UNDO表空间离线和删除

SQL>alter system set undo_tablespace=undotbs02;
SQL>alter tablespace tablespace_name offline;
SQL>drop tablespace undotbs02;

离线时,必须要有替代的undo表空间。若某个事务正在使用,则还不能离线,查询SQL如下:

SQL>SELECT SEGMENT_NAME, XACTS, V.STATUS FROM V$ROLLSTAT V, DBA_ROLLBACK_SEGS WHERE TABLESPACE_NAME = undotbs02 AND SEGMENT_ID = USN; 

当没有数据行时才可以离线,如果有数据行,可查看是否有事务在进行:
SELECT S.SID, S.SERIAL#, S.USERNAME, R.NAME
"ROLLBACK" FROM V$SESSION S, V$TRANSACTION T, V$ROLLNAME R WHERE S.TADDR = T.ADDR AND T.XIDUSN = R.USN;

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。