-- 查询用户表空间
select username, default_tablespace, u.temporary_tablespace from dba_users u;
-- 查询表空间信息
-- 查询表空间使用情况
SELECT a.tablespace_name "表空间名",
total / 1024 / 1024 "大小[M]",
free / 1024 / 1024 "剩余大小[M]",
(total - free) / 1024 / 1024 "使用大小[M]",
Round((total - free) / total, 4) * 100 "使用率 [%]",
b.file_name "文件路径",
'ALTER DATABASE DATAFILE ''' || file_name || ''' resize 4096M ;' "修改表空间大小",
'ALTER DATABASE DATAFILE ''' || file_name ||
''' AUTOEXTEND ON NEXT 100M MAXSIZE 10G;' "修改自增和最大容量"
FROM (SELECT tablespace_name, Sum(bytes) free
FROM DBA_FREE_SPACE
GROUP BY tablespace_name) a,
(SELECT tablespace_name, Sum(bytes) total, file_name
FROM DBA_DATA_FILES
GROUP BY tablespace_name, file_name) b
WHERE a.tablespace_name = b.tablespace_name
order by "使用率 [%]" desc;
-- 查询表空间文件路径
select tablespace_name, file_name, autoextensible from dba_data_files ;
-- 查询表空间并拼接修改表空间自动增长、表空间最大空间,通过执行结果集更改表空间信息
select 'ALTER DATABASE DATAFILE ''' || file_name ||
''' AUTOEXTEND ON NEXT 100M MAXSIZE 10G;'
from dba_data_files;
-- 查询表空间并拼接修改表空间当前空间大小,通过执行结果集更改表空间信息
select 'ALTER DATABASE DATAFILE ''' || file_name || ''' resize 4096M ; '
from dba_data_files;