一、Oracle
--查看表空间使用情况
SELECT a.tablespace_name "表空间名称",
round(total/(1024*1024*1024),2) "表空间总量(G)",
round(free/(1024*1024*1024),2) "表空间剩余(G)",
round((total-free)/(1024*1024*1024),2) "表空间使用(G)",
round((total-free)/total,4)*100 "使用率%"
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
--表空间物理文件的名称及是否自增等
SELECT
T.TABLESPACE_NAME "表空间名称",
D.FILE_NAME "文件",
D.AUTOEXTENSIBLE "是否自增",
round(D.BYTES/(1024*1024*1024),0) "现在大小(G)",
round(D.MAXBYTES/(1024*1024*1024),0) "最大额度(G)",
D.STATUS "状态"
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
ORDER BY T.TABLESPACE_NAME,D.FILE_NAME;
注:Oracle单个数据文件最大为32G。
--1、新增数据文件,并且设置自增(文件初始1G,自增步长100M,默认可自增扩展到32G)
ALTER TABLESPACE UNDOTBS1 ADD DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\JDCJG\UNDOTBS05.DBF' SIZE 1G AUTOEXTEND ON NEXT 100M;
--2、设置数据文件自增(自增步长100M,可自增扩展到2G)
ALTER DATABASE DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\JDCJG\UNDOTBS05.DBF' AUTOEXTEND ON NEXT 100M MAXSIZE 2G;
--3、手动扩展数据文件(文件大小改为2G)
ALTER DATABASE DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\JDCJG\UNDOTBS05.DBF' RESIZE 2G;
--4、删除表空间数据文件
ALTER TABLESPACE UNDOTBS1 DROP DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\JDCJG\UNDOTBS03.DBF';