当前位置：首页 > 技能相关 > ORACLE > 正文内容

oracle表空间文件扩容的多种方案ora-01653

admin3年前 (2022-09-26)ORACLE5670 修订时间:2022-10-26 10:26:55

oracle表空间

Oracle的表空间与数据文件是成对出现的，每一个数据文件对应一个表空间，一个表空间可以包含多个数据文件。表空间分为普通表空间和大文件表空间，普通表空间的单个数据文件最大为32G，而大文件表空间的单个数据文件最大可以为128TB。一般设计数据库时，判断普通表空间无法满足应用时就创建大文件表空间。

创建表空间

--创建表空间
create tablespace TABLESPACE_NAME
logging  
datafile 'E:\app\oracle\oradata\ABC.DBF'
size 50m 
autoextend on  
next 50m maxsize 20480m  
extent management local;

创建大文件表空间

--创建大文件表空间
create bigfile tablespace TABLESPACE_NAME
datafile 'E:\app\oracle\oradata\ABC.DBF'size 100G AUTOEXTEND ON;

创建临时表空间

--创建临时表空间 
create temporary tablespace TABLESPACE_NAME
tempfile 'E:\app\oracle\oradata\ABC.DBF' 
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local;

了解了oracle表空间与数据文件原理后，又将如何进行表空间扩容呢？以下将进行相关讲解：

表空间扩容

第一步：查看表空间使用情况

-- 查看表空间使用情况
SELECT 
tablespace_name 表空间名,
sum(totalM) 总大小M,
sum(usedM) 已使用量M,
sum(surplusM) 剩余量M,
sum(usedM)/sum(totalM)*100 已使用率per
FROM
(
SELECT b.file_id ID,
b.tablespace_name tablespace_name ,
b.file_name file_name,
b.bytes/1024/1024 totalM,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 usedM,
sum(nvl(a.bytes,0)/1024/1024) surplusM
FROM dba_free_space a,dba_data_files b
WHERE a.file_id = b.file_id
GROUP BY b.tablespace_name,b.file_name,b.file_id,b.bytes
ORDER BY b.tablespace_name
)
GROUP BY tablespace_name ;

第二步：查询表空间自增设置以及数据文件路径信息

SELECT T.TABLESPACE_NAME,
D.FILE_NAME,
D.AUTOEXTENSIBLE,
D.BYTES,
D.MAXBYTES,
D.STATUS
FROM DBA_TABLESPACES T, DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME, FILE_NAME;

或者如下语句

--查询临时表空间文件路径
select * from dba_temp_files;
--查询表空间文件路径
select * from dba_data_files;

第三步：为指定表空间增加数据文件

ALTER TABLESPACE TABLESPACE_NAME 
ADD DATAFILE 'E:\app\oracle\oradata\ABCD.DBF' 
SIZE 512m AUTOEXTEND ON NEXT 32768m MAXSIZE UNLIMITED;

或者开启表空间自动扩展的语句

ALTER DATABASE DATAFILE 
  'E:\app\oracle\oradata\ABC.DBF' AUTOEXTEND ON NEXT 200M MAXSIZE 10240M;

或者直接指定增加文件的大小（***M为数据文件总大小）

alter database datafile 'E:\app\oracle\oradata\ABC.DBF' resize ***M;

PS：查看临时表空间大小

select a.name "tbs_name",
       round(a.bytes /1024/1024 , 2) "tbs_size(mbytes)",
       round(((a.blocks - nvl(b.used_blocks,0))/a.blocks)*a.bytes / 1024/1024,2)             
       "tbs_free(mbytes)",
       round((nvl(b.used_blocks,0) / a.blocks)*a.bytes /1024/1024 ,2) "tbs_used(mbytes)",
       round((nvl(b.used_blocks,0) / a.blocks)*100,2) "percent %"
from (select t.NAME, sum(f.BYTES) as bytes, sum(f.BLOCKS) as blocks
       from v$tempfile f, v$tablespace t
       where t.TS#=f.TS#
       group by t.name) a,
       (select TABLESPACE_NAME, sum(USED_BLOCKS) as used_blocks
       from gv$sort_segment
       group by TABLESPACE_NAME)b
where a.name = b.tablespace_name(+)
order by length(a.name), a.name;