oracle表空间扩容

查询表空间数据文件剩余

set wrap off
set linesize 220
set pagesize 999
col tablespace_name for a40
select tablespace_name,
megs_alloc  init_sizeM,
max  total_sizeM,
max-megs_used free_sizeM,
megs_used used_sizeM,
round((max-megs_used)/(max+0.001) *100,1) free_rate
from
(select  a.tablespace_name,
round(a.bytes_alloc / 1024 / 1024, 2) megs_alloc,
round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) megs_free,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) megs_used,
round(maxbytes/1048576,2) Max
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 (+)
  union all
  select tablespace_name,
         round(sum(AllowUseSize_MB),2) megs_alloc,
         round(sum(FreeSize_MB),2) megs_free,
         round(sum(UsedSize_MB),2) megs_used,
         round(sum(MaxSize_MB),2) max
   from
    ( select d.tablespace_name, d.file_name,
           round(decode(d.autoextensible, 'YES', greatest(d.maxbytes, d.bytes), d.bytes)/1024/1024,2) MaxSize_MB,
           round(d.bytes/1024/1024,2) AllowUseSize_MB,
        nvl(round((d.bytes-p.bytes_cached)/1024/1024,2),0) FreeSize_MB,
           nvl(round(p.bytes_cached/1024/1024,2),0) UsedSize_MB,
      nvl(round((decode(d.autoextensible, 'YES', greatest(d.maxbytes, d.bytes), d.bytes) - p.bytes_cached)/1024/1024,2),0)
      from dba_temp_files d, v$temp_extent_pool p, dba_tablespaces ts
     where d.tablespace_name = p.tablespace_name(+)
       and d.file_id = p.file_id(+)
    and d.tablespace_name = ts.tablespace_name(+))
 group by tablespace_name)
  order by 6;

查询表空间数据文件路径
select file_name from dba_data_files where tablespace_name='NL_UPRIGHT3';

增加数据文件
alter tablespace NL_UPRIGHT3 add datafile '/u01/app/oracle/oradata/zbank/NL_UPRIGHT3_1.dbf' size 500m autoextend on next 50m maxsize unlimited;