ORACLE数据库表空间扩展

1、查询表空间使用情况

SELECT a.tablespace_name "tn", round(total / (1024 * 1024 * 1024), 2) "total(G)", round(free / (1024 * 1024 * 1024), 2) "free(G)", round((total - free) / (1024 * 1024 * 1024), 2) "used(G)", round((total - free) / total, 4) * 100 "p%" FROM (SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) total FROM dba_data_files GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name;                                                        e / (1024 * 1024 * 1024), 2) "free(G)", round((total - free) / (1024 * 1024 * 1024), 2) "used(G)                                                        ", round((total - free) / total, 4) * 100 "p%" FROM (SELECT tablespace_name, SUM(bytes) free FRO                                                        M dba_free_space GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) total FROM dba                                                        _data_files GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name;

2、查询各表空间路径

SELECT TABLESPACE_NAME "name", BYTES/1024/1024 "total(M)", FILE_NAME "path",FILE_ID FROM DBA_DATA_FILES order by TABLESPACE_NAME,FILE_NAME;

3、扩容原表空间文件

ALTER DATABASE DATAFILE '/opt/oracle/oradata/QZDATA.dbf' RESIZE 25000M;

4、增加表空间文件

ALTER TABLESPACE QZDATA ADD DATAFILE  '/opt/oracle/oradata/QZDATA01.dbf' size 30000M;