表空间
ORACLE的表空间
基本概念
表空间概述
Oracle的表空间属于Oracle中的存储结构，是一种用于存储数据库对象(如：数据文件)的逻辑空间，是Oracle中信息存储的最大逻辑单元，其下还包含有段、区、数据块等逻辑数据类型。表空间是在数据库中开辟的一个空间，用于存放数据库的对象，一个数据库可以由多个表空间组成。可以通过表空间来实现对Oracle的调优。（Oracle数据库独特的高级应用）
表空间的分类
永久表空间：存储数据库中需要永久化存储的对象，比如二维表、视图、存储过程、索引。
临时表空间：存储数据库的中间执行过程，如：保存order by数据库排序，分组时产生的临时数据。操作完成后存储的内容会被自动释放。临时表空间是通用的，所的用户都使用TEMP作为临时表空间。一般只有tmp一个临时表空间，如果还需要别的临时表空间时，可以自己创建。
UNDO表空间：保存数据修改前的副本。存储事务所修改的旧址，即被修改之前的数据。当我们对一张表中的数据进行修改的同时会对修改之前的信息进行保存，为了对数据执行回滚、恢复、撤销的操作。
引言
Oracle数据库的数据文件的位置和信息都被记录在控制文件中，rm或cp命令是不会也不可能更改控制文件记录的，这时必须通过alter操作去更改刷新数据库控制文件中数据文件的相关信息，以此确保数据库能够正常运行。
因为表空间的大小是由其所拥有的数据文件的数量和大小决定的，因此我们可以通过为表空间添加数据文件、改变现有的数据文件的大小、
改变数据文件的扩展方式都可以达到改变表空间的大小。
但是要注意对于大文件表空间不能通过添加数据文件的方式来改变表空间的大小，因为大文件表空间就一个文件。
1、增加数据文件方式
a、为表空间添加数据文件来改变表空间的大小（永久表空间）
alter tablespace t1 add datafile '/u01/app/oracle/t1.dbf' size 100M;
b、（临时表空间）
alter tablespace temp add tempfile '/u01/app/oracle/temp02.dbf' size 100M;
2、通过改变数据文件的大小改变表空间的大小。
a、重新设置已经存在的表空间的大小，如果指定的文件的大小低于当前已用的数据文件的大小，那么就会报错。
alter database datafile '/u01/app/oracle/t1.dbf' resize 200M;
3、改变数据文件的扩展方式
如果表空间在创建时没有指定他的扩展方式为AUTOEXTEND ON，那么该表空间就是固定大小的表空间，那么我们就可以通过设置表空间的扩展方式为自动扩展来
修改该表空间的方式。
alter database datafile '/u01/app/oracle/t1.dbf' AUTOEXTEND ON next 5M maxsize 200m;
修改该表空间为自动扩展，每次扩展5M最大值为200M。
4、改变大文件表空间的大小。
由于大文件表空间只能有用一个数据文件，所以只能通过修改已经存在的数据文件的大小的方式改变表空间的大小。
又因为大文件表空间和数据文件是一一对应的，因此，除了直接对数据文件进行操作以外，还可以通过对表空间进行操作，实现对数据文件的透明操作。
如：
a、将数据库的大文件表空间t2的数据文件t2.dbf的大小修改为100M。可以使用两种方式，对与一般的表空间的却不可以，因为数据文件和表空间不是一一对应的。
1）修改数据文件
alter database datafile '/u01/app/oracle/t2.dbf' resize 100M;
（2）修改表空间
alter tablespace b2 resize 100M;
b、修改数据库的大文件表空间t2为自动扩展，每次扩展10M，最大为150M
（1）直接修改数据文件
alter database datafile '/u01/app/oralce/t2.dbf' AUTOEXTEND ON next 10M maxsize 150m;
（2）修改表空间
alter tablespace t2 AUTOEXTEND ON next 10M maxsize 150M;
常用查询和操作
查询所有表空间使用率
set linesize 300 pages 999
SELECT a.tablespace_name,
ROUND (a.total_size) "total_size(GB)",
ROUND (a.total_size) - ROUND (b.free_size, 2) "used_size(GB)",
ROUND (b.free_size, 2) "free_size(GB)",
ROUND (b.free_size / total_size * 100, 2) || '%' free_rate
FROM ( SELECT tablespace_name, SUM (bytes) /1024 /1024/1024total_size
FROM dba_data_files
GROUP BY tablespace_name) a,
( SELECT tablespace_name, SUM (bytes) /1024/1024/1024 free_size
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+);
and a.tablespace_name = 'MUSICDATA';
或者
set linesize 200 pages 999
col TBS_NAME for a25
set space 2
select a.TABLESPACE_NAME tbs_name,
round(a.BYTES / 1024 / 1024 /1024) Total_GB,
round((a.BYTES - nvl(b.BYTES, 0)) / 1024 / 1024 /1024) Used_GB,
round((1 - ((a.BYTES - nvl(b.BYTES, 0)) / a.BYTES)) * 100, 2) Pct_Free,
round(((a.BYTES - nvl(b.BYTES, 0)) / a.BYTES) * 100, 2) Pct_Used,
nvl(round(b.BYTES / 1024 /1024 / 1024 ), 0) Free_GB,
auto
from (select TABLESPACE_NAME, sum(BYTES) BYTES, max(AUTOEXTENSIBLE) AUTO
from sys.dba_data_files
group by TABLESPACE_NAME) a,
(select TABLESPACE_NAME, sum(BYTES) BYTES
from sys.dba_free_space
group by TABLESPACE_NAME) b
where
/*a.TABLESPACE_NAME='please replease tbs_name over here' and */
a.TABLESPACE_NAME = b.TABLESPACE_NAME(+)
order by ((a.BYTES - b.BYTES) / a.BYTES) desc
/
查询一个数据库总空间大小
SELECT ROUND(SUM(BYTES)/1024/1024/1024,2)||'GB' FROM dba_extents;
查询一个表占多少空间
select bytes/1024/1024 MB, segment_name,tablespace_name,bytes from dba_segments where segment_name='B'and owner='SYSTEM';
查询表空间文件名
select count(*) from dba_data_files where tablespace_name='MUSICLOGIDX';
select * from dba_data_files where tablespace_name='MUSICLOGIDX';
select * from dba_data_files where tablespace_name='TBS_ZBA_DMA'and file_name like '%2017%
查询存储剩余空间（针对ASM存储）
select group_number,name,total_mb/1024 total_gb,trunc(free_mb/1024) free_gb from v$asm_diskgroup;
添加表空间（非ASM）
添加数据文件的方式
alter tablespace TBS_ZBA_DMA add datafile '+DATADG_20170801/tbs_zba_dma_20170829_001.dbf' size 131070m autoextend off;
alter tablespace UNDOTBS1 add datafile '/vgodba02/odba/undotbs1_3.dbf' size 131070m autoextend off;
--扩展无限大空间
alter database DATAFILE 'D:\DataBase\Test.DBF' autoextend on maxsize unlimited;
--自动增长，表空间不足时增加200MB，最大扩展5000MB
alter database datafile 'D:\DataBase\Test.DBF' autoextend on next 200m maxsize 5000m;
添加undo表空间
alter tablespace UNDOTBS1 add datafile '+DATA/mh2db/datafile/undotbs1.dbf' size 20G autoextend off;
alter tablespace UNDOTBS2 add datafile '+DATA/tymhdb/datafile/undotbs2.dbf' size 20G autoextend off;
resize数据文件方式
查看表空间各个数据文件大小：
SELECT FILE_NAME
,TABLESPACE_NAME
,BYTES / 1024 / 1024 / 1024 AS G
,MAXBYTES / 1024 / 1024 / 1024 MAX_G
,AUTOEXTENSIBLE
FROM dba_data_files
where tablespace_name='USERS';
修改各个数据文件大小
alter database datafile '+DATA/test/datafile/test.354.923261927' resize 50M;
alter database datafile '+DATA/ebuslog/datafile/musiclogdata369.dbf' resize 30G;
修改表空间为offline 和 online
alter tablespace offline;
alter tablespace online;
重命名表空间
(注意offline状态下表空间是没法重命名的表空间)
alter tablespace old_name rename to nanme_name;
对于ASM磁盘组直接使用如下语句添加（ASM）
alter tablespace MUSICLOGDATA add datafile '+DATA' size 30G autoextend off;
自动增加表空间
show parameter db_create_file_dest
alter tablespace MUSICLOGIDX add datafile '+DATA' size 30G autoextend off;
完全删除表空间和数据：
drop tablespace test INCLUDING CONTENTS and datafiles;
表空间的一些限制
表空间扩容时，可能会遇到ORA-01144问题：
问题描述：每个数据库最多有1024－2＝1022个文件（2个文件预留），
每个文件最多有4M个块，如果用默认的oracle block，即8192byte，那么，每个datafile最大为：4M×8K＝32GB；
1、扩容时表空间大小不能超过32G；如果表空间已经快到32G还想再扩容，就只能创建新的datafile，容量也不能超过32G。
2、在数据库库设计时，如果数据容量很大，在oracle 10g版本以后可以创建bigfile tablespace，datafile最大容量为32T。
问题描述：如果小于32G，依然失败，考虑是你的内存空间不足；
解决方案：重新执行扩容语句，将容量调整为合适的大小
表空间数据文件容量与DB_BLOCK_SIZE有关，在初始建库时，DB_BLOCK_SIZE要根据实际需要，设置为 4K,8K、16K、32K、64K等几种大小，ORACLE的物理文件最大只允许4194304个数据块(由操作系统决定)
即：
4k最大表空间为：16384M
8K最大表空间为：32768M
16k最大表空间为：65536M
32K最大表空间为：131072M
64k最大表空间为：262144M
所以将maxsize设置为unlimited也并非能无限扩展，还受限于区块大小，即blocksize大小，如一般blocksize默认8k，数据文件大小最大也只能扩展到32GB。当然可以设置bigfile（大文件表空间），8k的blocksize表空间文件理论上最大可以扩展32TB，但是实际上受操作系统的文件系统限制。使用大文件表空间（bigfile tablespace）可以大幅度增强Oracle数据库的存储能力，简化数据库管理工作。与此同时，付出的代价是增加备份与恢复的时间。如果是中小型项目，数据量在TB级以内，建议使用小文件表空间。一个小文件表空间（smallfile tablespace）最多可以包含1022个数据文件（datafile）。详细可看Oracle物理文件限制大小表格Physical Database Limits
添加表空间数据文件sql脚本如下：
使用OFM创建表空间
OMF,全称是Oracle_Managed Files,即Oracle文件管理，使用OMF可以简化管理员的管理工作，不用指定文件的名字、大小、路径，其名字，大小，路径由oracle 自动分配。在删除不再使用的日志、数据、控制文件时，OMF也可以自动删除其对应的OS文件。
OMF支持下列文件的自动管理：
- 表空间
- 日志文件(联机)
- 控制文件
前提条件：需要为这些类型文件设定相关参数。
一.数据文件的OMF管理
--注意：使用OMF的时候会将物理文件删除，应特别注意使用
数据文件管理参数：db_create_file_dest
db_create_file_dest：Oracle创建数据文件、临时文件时，在未明确指定路径的情况下的缺省路径，当db_create_online_log_dest_n未指定时，也作为联机日志文件和控制文件的缺省路径。
1.假定需要创建表空间s及数据文件
SQL> CREATE TABLESPACE s; /*收到了错误信息*/
create tablespace s;
ERROR at line 1:
ORA-02199: missing DATAFILE/TEMPFILE clause
2.查看数据文件管理参数
SQL> show parameter db_create_file /*查看db_create_file_dest参数*/
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
3.设定db_create_file_dest参数
SQL> ALTER SYSTEM SET db_create_file_dest = '/u01/app/oracle/oradata/orcl';
System altered.
4.设定参数后，创建成功
SQL> CREATE TABLESPACE s;
Tablespace created.
SQL> SELECT NAME FROM v$datafile; /*可以看到ORCL/datafile/o1_mf_s_5vrl1t7h_.dbf是Oralce自动创建的数据文件*/
/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_s_5vrl1t7h_.dbf
5.设定参数后，也可以指定文件大小，创建成功
SQL> CREATE TABLESPACE t DATAFILE SIZE 10m; /*也可指定数据文件的大小为MB*/
SQL> SELECT NAME FROM v$datafile;
/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_t_5vrlgqtl_.dbf
6.删除OFM类型的表空间
--删除表空间时，对于使用了OMF生成的数据文件将随着表空间的删除一起被删除
SQL> DROP TABLESPACE t;
Tablespace dropped.
--查看物理文件，已经不存在
--对于未采用OMF来创建的表空间，在删除表空间之后，其数据文件并没有删除，v$datafile视图中被删除
--下面是未使用OMF创建的x表空间及数据文件
SQL> CREATE TABLESPACE x DATAFILE '/u01/app/oracle/oradata/orcl/x.dbf' SIZE 10m;
SQL> DROP TABLESPACE x;
SQL> SELECT NAME FROM v$datafile; /*视图中已不存在x.dbf的数据文件*/
SQL> ho ls /u01/app/oracle/oradata/orcl/x.dbf /*操作系统中依然存在*/
--使用下面的方法重建x表空间，然后使用including contents and datafiles 彻底删除表空间及物理文件
SQL> CREATE TABLESPACE X DATAFILE '/u01/app/oracle/oradata/orcl/x.dbf' REUSE;
SQL> DROP TABLESPACE x INCLUDING CONTENTS AND DATAFILES;
总结：使用OMF管理文件的方法
1.使用ALTER SYSTEM SET db_create_file_dest = '
2.查看刚刚的设置SHOW PARAMETER db_create_file_dest;
3.创建表空间及数据文件CREATE TABLESPACE tablespace_name
4.单独创建表空间CREATE TABLESPACE <> DATAFILE '
5.也可以创建undo和temporary tablespace 。CREATE UNDO TABLESPACE tablespace_name ;CREATE TEMPORARY TABLESPACE tablespace_name;
6.删除表空间DROP TABLESPACE tablespace_name ;OMF情况下则删除物理文件，等效于未使用OMF创建，使用INCLUDING CONTENTS AND DATAFILES 删除方式
移动表空间的数据文件
数据文件是存储在磁盘中的物理文件，大小受磁盘大小限制.
如果数据文件所在的磁盘空间不够，就需要将数据文件移动到新的磁盘中.
Alter tablespace可以作用于除了system和sysaux，undotbs和临时表空间以外的任何表空间。
Alter database 可以作用于所有表空间，因此alter database方式需要关闭实例，但是alter tablespace方式不需要。
一、使用alter database 方式移动数据文件
1、作为sysdba连接数据库，并关闭实例
2、使用操作系统的命令移动数据文件
3、以mount方式打开数据库
4、使用alter database 改变对数据库中数据文件的引用
5、以open模式打开数据库
6、对包括控制文件的数据库执行增量备份或者完整备份
方法1.
主要步骤：
1、关闭数据库；
2、复制数据文件到新的位置；
3、启动数据库到mount状态；
4、通过SQL修改数据文件位置；
5、打开数据库；
1.关闭数据库
SQL> shutdown immediate;
2.复制数据文件到新的位置
cp /u01/app/oracle/oradata/cifdb.dbf /u01/app/oracle/oradata/CIFDB/cifdb.dbf
3.启动数据库到mount状态
SQL> startup mount;
4.修改数据文件位置
SQL> alter database rename file '/u01/app/oracle/oradata/cifdb.dbf' to '/u01/app/oracle/oradata/CIFDB/cifdb.dbf';
5.打开数据库
SQL> alter database open;
6.检查数据文件
SQL> select name from v$datafile;
或者
SQL> select file_name, tablespace_name from dba_data_files where tablespace_name='cifd
二、alter tablespace 方式移动数据文件
1、使用具有alter tablespacede 权限的账户，对表空间脱机处理
2、使用操作系统命令移动数据文件
3、使用alter tablespace 改变对数据库中的数据文件的引用
4、将表空间放回到联机状态
方法2
主要步骤：
1、offline表空间：alter tablespace tablespace_name offline;
2、复制数据文件到新的目录；
3、rename修改表空间，并修改控制文件；
4、online表空间；
1.offline表空间
alter tablespace cifdb offline;
- 复制数据文件到新的目录
cp /u01/app/oracle/oradata/cifdb.dbf /u01/app/oracle/oradata/CIFDB/cifdb.dbf
3.rename修改表空间
SQL> alter tablespace cifdb rename datafile '/u01/app/oracle/oradata/cifdb.dbf' to '/u01/app/oracle/oradata/CIFDB/cifdb.dbf';
4.online表空间
SQL> alter tablespace cifdb online;
5.检查数据文件
SQL> select name from v$datafile;
或者：
SQL> select file_name, tablespace_name from dba_data_files where tablespace_name='cifdb';
temp表空间释放：
临时表空间主要用途是在数据库进行排序运算、管理索引、访问视图等操作时提供临时的运算空间，当运算完成之后系统会自动清理，但有些时候我们会遇到临时段没有被释放，TEMP表空间几乎满使用率情况；
引起临时表空间增大主要使用在以下几种情况：
1、order by or group by (disc sort占主要部分)；
2、索引的创建和重创建；
3、distinct操作；
4、union & intersect & minus sort-merge joins；
5、Analyze 操作；
6、有些异常也会引起TEMP的暴涨。
shutdown immediate;
startup mount
host copy C:\ORCL\SYSTEM01.DBF e:\disk1\
alter database rename file '/u01/app/oracle/oradata/gjorcl/temp01.dbf' to '/data/tempdir/temp01.dbf';
alter database open;
select file#,name,status fromv$datafile;
解决方法一：用上述方法给temp增加表空间文件
解决方法二：在服务器资源空间有限的情况下，重新建立新的临时表空间替换当前的表空间
--1.查看当前的数据库默认表空间：
select * from database_properties
where property_name='DEFAULT_TEMP_TABLESPACE';
--2.创建新的临时表空间
create temporary tablespace TEMP01 tempfile
'/home/temp01.dbf' size 31G;
--3.更改默认临时表空间
alter database default temporary tablespace TEMP01;
--4.删除原来的临时表空间
drop tablespace TEMP02 including contents and datafiles;
--如果删除原来临时表空间报错ORA-60100：由于排序段，已阻止删除表空间...
--（说明有语句正在使用原来的临时表空间，需要将其kill掉再删除，此语句多为排序的语句）
--查询语句
Select se.username,se.sid,se.serial#,su.extents,su.blocks*to_number(rtrim(p.value))as Space,
tablespace,segtype,sql_text
from v$sort_usage su,v$parameter p,v$session se,v$sql s
where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash
and s.address=su.sqladdr
order by se.username,se.sid;
--删除对应的'sid,serial#'
alter system kill session 'sid,serial#'
修改表空间为自动扩展
用具有dba权限的用户执行：
select file_name,autoextensible,increment_by from dba_data_files
1、查看表空间情况
SQL>select tablespace_name,file_name,autoextensible from dba_data_files where tablespace_name = '表空间名称';
2、修改表空间数据文件，使其达到表空间自动扩展
alter database datafile '/u01/app/oracle/oradata/orcl/user01.dbf' autoextend on;
关闭自动扩展功能语法：
alter database datafile '对应的数据文件路径信息' autoextend off;
本文来自博客园，作者：数据库小白（专注），转载请注明原文链接：https://www.cnblogs.com/xulinforDB/p/17496384.html
