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;