表空间相关查询

查询表空间总大小

select tablespace_name,sum(bytes)/1024/1024 || 'MB' as "all_size"  from dba_data_files where tablespace_name='TEST' group by tablespace_name;

查询表空间剩余大小

select tablespace_name,sum(bytes)/1024/1024 || 'MB' as "free_size" from dba_free_space where tablespace_name='TEST' group by tablespace_name;

修改表空间大小

表空间扩容

alter tablespace test add datafile '/u01/app/oracle/oradata/orcl/test02.dbf' size 50M autoextend on next 1M maxsize 1G;

缩小表空间大小

查询当前表空间使用大小

select max(block_id)*8/1024 from dab_free_space where tablespace_name='TEST';

修改数据文件大小

alter database datafile '/u01/app/oracle/oradata/orcl/test01.dbf' size 40M;