1、查看表空间使用情况
set lines 180
col USED_OF_MAX_PCT for a20
select tbs_used_info.tablespace_name,
tbs_used_info.alloc_mb,
tbs_used_info.used_mb,
tbs_used_info.max_mb,
tbs_used_info.free_of_max_mb,
tbs_used_info.used_of_max || '%' used_of_max_pct
from (select a.tablespace_name,
round(a.bytes_alloc / 1024 / 1024) alloc_mb,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) used_mb,
round(((a.bytes_alloc - nvl(b.bytes_free, 0)) * 100 /
a.maxbytes),2) used_of_max,
round((a.maxbytes - a.bytes_alloc + nvl(b.bytes_free, 0)) /
1048576) free_of_max_mb,
round(a.maxbytes / 1048576) max_mb
from (select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible,
'YES',
f.maxbytes,
'NO',
f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
(select f.tablespace_name, sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)) tbs_used_info
order by tbs_used_info.used_of_max asc;
2、查看表空间数据文件位置以及自动扩展属性是否开启以及最大可扩展大小
select file_name,TABLESPACE_NAME,BYTES/1024/1024/1024,AUTOEXTENSIBLE,MAXBYTES from dba_data_files where tablespace_name='CRM';
3、为表空间添加数据文件(自动扩展开启)
alter tablespace CRM add datafile '/xxxx/xxx/xx/CRM02.dbf' size 30g autoextend on ;