SELECT a.tablespace_name "表空间名",
a.bytes / 1024 / 1024 "表空间大小(M)",
(a.bytes - b.bytes) / 1024 / 1024 "已使用空间(M)",
b.bytes / 1024 / 1024 "空闲空间(M)",
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "使用比"
FROM (SELECT tablespace_name, sum(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, sum(bytes) bytes, max(bytes) largest
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
ORDER BY ((a.bytes - b.bytes) / a.bytes) DESC
SELECT * FROM dba_data_files;
SELECT * FROM dba_data_files t WHERE t.tablespace_name='XX_NOA';
FILE_NAME:文件名
BYTES:大小
AUTOEXTENSIBLE:是否自动扩展
MAXBYTES:最大大小,如果设为maxsize unlimited,则值为 34359721984,30G?
INCREMENT_BY:自动扩展的块数
块大小:show parameter db_block,db_block 为块大小,如块大小8192,块数64000,则自动扩展为 500M
--删除空的表空间,但是不包含物理文件
drop tablespace tablespace_name;
--删除非空表空间,但是不包含物理文件
drop tablespace tablespace_name including contents;
--删除空表空间,包含物理文件
drop tablespace tablespace_name including datafiles;
--删除非空表空间,包含物理文件
drop tablespace YNTSJY_EFLOW including contents and datafiles;
ALTER TABLESPACE app_data ADD DATAFILE
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF' SIZE 50M;
ALTER TABLESPACE app_data ADD DATAFILE
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP04.DBF' SIZE 50M
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF'
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP02.DBF'
RESIZE 100M;
硬盘空间满了,可增加数据文件在别的盘上解决