oracle数据库表空间的统计和添加

1.登录DBA用户，先统计表空间

SELECT T.TABLESPACE_NAME,
       TO_CHAR((T.TOTAL_SPACE - nvl(F.FREE_SPACE,0)), '9999,999') "USED(M)",
       TO_CHAR(nvl(F.FREE_SPACE,0), '9999,999') "FREE(M)",
       TO_CHAR(T.TOTAL_SPACE, '9999,999') "TOTAL(M)",
       TO_CHAR(T.AUTO_SPACE, '9999,999') "AUTO(M)",
       TO_CHAR((ROUND((nvl(F.FREE_SPACE,0) / T.TOTAL_SPACE) * 100)), '999999') || ' %' PER_FREE,
       TO_CHAR((ROUND((T.AUTO_SPACE / T.TOTAL_SPACE) * 100)), '999999') || ' %' PER_AUTO
  FROM (SELECT TABLESPACE_NAME,
                ROUND(SUM(BLOCKS * (SELECT VALUE / 1024
                                      FROM V$PARAMETER
                                     WHERE NAME = 'db_block_size') / 1024)) FREE_SPACE
           FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,
       (SELECT TABLESPACE_NAME,
                ROUND(SUM(BYTES / 1048576)) TOTAL_SPACE,
                ROUND(SUM(DECODE(SIGN(BYTES - MAXBYTES),
                                 1,
                                 0,
                                 MAXBYTES - BYTES)) / 1024 / 1024) AUTO_SPACE
           FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) T
 WHERE F.TABLESPACE_NAME(+)= T.TABLESPACE_NAME
   AND (ROUND((nvl(F.FREE_SPACE,0) / T.TOTAL_SPACE) * 100)) < 40
   AND (ROUND((T.AUTO_SPACE / T.TOTAL_SPACE) * 100)) < 40
 ORDER BY TABLESPACE_NAME;

2.抓取datafile路径名：

selectfile_namefromdba_data_fileswheretablespace_name='DB_TOCC';

说明：这个是表空间名称

3.扩展表空间：

ALTERTABLESPACEDB_TOCCADDDATAFILE'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DB_TOCC3'size4GAUTOEXTENDONnext1G;

说明：这个名称需要上面查询结果的最后添加1不能和上面名称重复