Oracle表空间维护总结-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

Oracle表空间维护总结

简介:

1. 概念:
表空间:最大的逻辑存储文件,与物理上的一个或多个数据文件对应,每个数据库至少拥有一个表空间,表空间的大小等于构成表空间的所有数据文件的大小总和,用于存储用户在数据库中存储的所有内容。

2. 种类:
分为基本表空间、临时表空间、大文件表空间、非标准数据块表空间和撤销表空间。
基本表空间:用于存储用户的永久性数据
临时表空间:排序、汇总时产生的临时数据
大文件表空间:存储大型数据,如LOB
非标准数据块表空间:创建数据块大小不同的表空间
撤销表空间:存储事务的撤销数据,在数据恢复时使用

3. 系统默认表空间:
system:系统表空间,用于存储系统的数据字典、系统的管理信息和用户数据表等。
sysaux:辅助系统表空间,减少系统表空间负荷,体改系统作业效率,Oracle系统自动维护,一般不用于存储数据结构。
temp:临时表空间。
undotbsl:撤销表空间,用于在自动撤销管理方式下存储撤销信息。
users:用户表空间。

4. 表空间的状态
表空间的状态属性主要有在线(online),离线(offline),只读(read only)和读写(read write)。SQL> select 
-- 查看表空间的状态
select tablespace_name,status from dba_tablespaces;
-- 更改表空间状态
alter tablespace XXX offline/online/read only/read write;

5. 创建表空间语句:
create [temporary|undo]tablespace tablespace_name[datafile|tempfile] 'filename' size
size K|M[reuse] //已经存在是否指定reuse
[autoextend off|on //数据文件是否自动扩展
[next number K|M maxsize unlimited|number K|M]
][……]
[mininum extent number K|M]
[blocksize number K] //初始化参数数据库大小,只能用于持久表空间
[online|offline] //online表空间可用
[logging|nologging]
[force logging] 
[default storage storage] //设置默认存储参数
[compress|nocompress] //压缩数据段内数值
[premanent|temporary] //持久保存数据库对象|临时
[extent management dictionary|local //数据字典管理方式|本地管理,一般本地
[autoallocate|uniform size number K|M]]
[segment space management auto|manual]; //表空间段的管理方式自动|手动

-- 创建临时表空间:
create temporary tablespace XXXX tempfile 'XXXXXXXtemp.dbf' size 50m 
autoextend on next 50m maxsize 20480m extent management local;

-- 创建数据表空间:

create tablespace KMYQ datafile '/oradata/testdb2/KMYQ01.dbf' size 200m 
autoextend off
segment space management auto 
extent management local 
uniform size 4M;

-- 创建临时表空间创建用户并指定表空间:
create user XXXX identified by XXXXX default tablespace XXX temporary tablespace XXXX_temp;

6. 默认表空间:
初始状态下(未修改)默认永久性表空间为system,临时为temp
-- 查询默认表空间:
select default_tablespace from user_users;

-- 查询默认新用户表空间与默认临时表空间
SQL> col PROPERTY_VALUE for a40

select property_name,property_value
from database_properties where property_name
in ('DEFAULT_PERMANENT_TABLESPACE','DEFAULT_TEMP_TABLESPACE');

-- 修改默认临时表空间:
alter database default tablespace XXXX;

7. 查看表空间物理文件的名称及大小 
SELECT tablespace_name, 
file_id, 
file_name, 
round(bytes / (1024 * 1024), 0) total_space 
FROM dba_data_files 
ORDER BY tablespace_name;

8. 查看表空间的使用情况

数据表空间使用率:
SELECT a.tablespace_name, 
a.bytes/(1024*1024) total_M, 
b.bytes/(1024*1024) used_M, 
c.bytes/(1024*1024) free_M, 
(b.bytes * 100) / a.bytes "% USED ", 
(c.bytes * 100) / a.bytes "% FREE " 
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c 
WHERE a.tablespace_name = b.tablespace_name 
AND a.tablespace_name = c.tablespace_name;

临时表空间使用率:
SELECT temp_used.tablespace_name, 
total - used as "Free_M", 
total as "Total_M", 
round(nvl(total - used, 0) * 100 / total, 3) "Free percent" 
FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used 
FROM GV_$TEMP_SPACE_HEADER 
GROUP BY tablespace_name) temp_used, 
(SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total 
FROM dba_temp_files 
GROUP BY tablespace_name) temp_total 
WHERE temp_used.tablespace_name = temp_total.tablespace_name;

9. 查询表空间每天的使用情况

select a.name, b.* 
from v$tablespace a, 
(select tablespace_id, 
trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) datetime, 
max(tablespace_usedsize * 8 / 1024) used_size 
from dba_hist_tbspc_space_usage 
where trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) > 
trunc(sysdate - 30) group by tablespace_id, 
trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) order by 
tablespace_id, trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'))) b 
 where a.ts# = b.tablespace_id ;

10. 表空间扩容
-- 修改建立的数据文件的大小
SQL> col file_name for a60
SQL> select file_name,bytes/1024/1024 from dba_data_files;
SQL> alter database datafile '/home/oracle/app/oradata/orcl/users01.dbf'resize 51M;
SQL> select file_name,bytes/1024/1024 from dba_data_files;

-- 增加表空间的数据文件
SQL> alter tablespace andy add datafile '/home/oracle/app/oradata/orcl/andy02.dbf'size 1M 
autoextend on next 1m maxsize 2m ;

11. 删除表空间
-- 删除所有数据库对象与删除数据文件
drop tablespace XXX including contents and contents;

12. 重命名表空间
alter tablespace tablespace_name rename to new_table_name;
alter tablespace andy rename to newandy;

13. 移动表空间的数据文件
SQL> select tablespace_name,file_name from dba_data_files where tablespace_name = 'NEWANDY'; 
SQL> alter tablespace newandy offline;
[oracle@11g ~]$ mv /home/oracle/app/oradata/orcl/andy01.dbf /home/oracle/app/oradata/andy01.dbf
SQL> alter tablespace newandy rename datafile '/home/oracle/app/oradata/orcl/andy01.dbf' to '/home/oracle/app/oradata/andy01.dbf';
SQL> alter tablespace newandy online;
SQL> select tablespace_name,file_name from dba_data_files where tablespace_name = 'NEWANDY';

14. 修改表空间的自动扩展性
SQL> select tablespace_name,status,extent_management,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;
SQL> alter database datafile file_name autoextend off|on [next number K|M maxsize unlimited|number K|M]
SQL> select tablespace_name,status,extent_management,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;

 

文章可以转载,必须以链接形式标明出处。


本文转自 张冲andy 博客园博客,原文链接: http://www.cnblogs.com/andy6/p/6653431.html  ,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享: