oracle 12c rac扩表空间

1.表空间查看

set pages 999
set linesize 999
SELECT a.tablespace_name "tablespace_name", 
100-ROUND((NVL(b.bytes_free,0)/a.bytes_alloc)*100,2) "occupation(%)",
ROUND(a.bytes_alloc/1024/1024,2) "size(M)",
ROUND(NVL(b.bytes_free,0)/1024/1024,2) "free(M)",
ROUND((a.bytes_alloc-NVL(b.bytes_free,0))/1024/1024,2) "use(M)", 
TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss') "Sampling time"   
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;

tablespace_name                occupation(%)    size(M)    free(M)     use(M) Sampling time
------------------------------ ------------- ---------- ---------- ---------- -------------------
SYSAUX                                 94.82       1620      83.94    1536.06 2018-07-18 14:44:25
UNDOTBS1                               99.83   32767.98         56   32711.98 2018-07-18 14:44:25
USERS                                  83.71     153600   25021.44  128578.56 2018-07-18 14:44:25
SYSTEM                                 26.78       1080     790.75     289.25 2018-07-18 14:44:25
UNDO_2                                 68.07      32760   10459.13   22300.88 2018-07-18 14:44:25

2.查看数据文件

SQL> select a.tablespace_name,a.FILE_NAME,bytes/1024/1024||'M' "size",a.AUTOEXTENSIBLE,a.MAXBYTES,a.INCREMENT_BY  from  dba_data_files a order by a.FILE_NAME;

TABLESPACE_NAME                FILE_NAME                                                                                                                                                             size                                       AUT   MAXBYTES INCREMENT_BY
------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------- --- ---------- ------------
SYSAUX                         +DATA/GNNT/6900B0297CC66549E053650DA8C0B038/DATAFILE/sysaux.293.972569467                                                                                             1620M                                      YES 3.4360E+10         1280
SYSTEM                         +DATA/GNNT/6900B0297CC66549E053650DA8C0B038/DATAFILE/system.292.972569467                                                                                             1080M                                      YES 3.4360E+10         1280
UNDO_2                         +DATA/GNNT/6900B0297CC66549E053650DA8C0B038/DATAFILE/undo_2.295.974139333                                                                                             32760M                             YES 3.4360E+10          640
UNDOTBS1                       +DATA/GNNT/6900B0297CC66549E053650DA8C0B038/DATAFILE/undotbs1.291.972569465                                                                                           32767.984375M                              YES 3.4360E+10          640
USERS                          +DATA/GNNT/6900B0297CC66549E053650DA8C0B038/DATAFILE/users.287.973099325                                                                                              30720M                             YES 3.2212E+10        12800
USERS                          +DATA/GNNT/6900B0297CC66549E053650DA8C0B038/DATAFILE/users.288.973099375                                                                                              30720M                             YES 3.2212E+10        12800
USERS                          +DATA/GNNT/6900B0297CC66549E053650DA8C0B038/DATAFILE/users.289.973098773                                                                                              30720M                             YES 3.2212E+10        12800
USERS                          +DATA/GNNT/6900B0297CC66549E053650DA8C0B038/DATAFILE/users.290.973099407                                                                                              30720M                             YES 3.2212E+10        12800
USERS                          +DATA/GNNT/6900B0297CC66549E053650DA8C0B038/DATAFILE/users05.dbf                                                                                                      30720M                             YES 3.4360E+10        32000

9 rows selected.

USERS表空间是我们本次扩展的对象，扩展前有4个文件，都是32G左右。我们需要再增加一个文件，大小保持一致。

3.asm查看

SQL> select name,total_mb, free_mb from v$asm_diskgroup;

NAME                             TOTAL_MB    FREE_MB
------------------------------ ---------- ----------
ARCH                               450548        872
DATA                               901096     644868
GRID                               153588      35580

rac的库在asm上。

查看剩余可扩空间，发现+DATA剩余空间足够

4.确定扩展大小

SQL> show parameter db_block;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers                     integer     0
db_block_checking                    string      FALSE
db_block_checksum                    string      TYPICAL
db_block_size                        integer     8192

扩展大小：8192*32120/1024/1024M约等于250M（db_block_size*INCREMENT_BY，块大小*块数=自动扩展的大小）

5.扩展表空间

alter tablespace users add datafile size 30G；

扩展空间比较大，时间有点长，完成后再查看表空间。