oracle表空间查询、修改

一、表空间基础查询

--查表空间文件路径
select * from sys.dba_data_files;
--查临时表空间文件路径
SELECT * FROM sys.dba_temp_files;
--查user使用的表空间
SELECT DISTINCT
    t.tablespace_name
FROM
    dba_segments s
JOIN
    dba_tablespaces t ON s.tablespace_name = t.tablespace_name
WHERE
    s.owner = 'XG' -- 替换为您要查询的用户名
	ORDER BY t.tablespace_name ; 
--查user默认表空间、临时表空间
SELECT T.DEFAULT_TABLESPACE, T.TEMPORARY_TABLESPACE,t.* FROM dba_users t WHERE T.username = 'XG'; -- 替换为您要查询的用户名

二、查询表空间使用情况

select A.tablespace_name,A."用户名" , B.FILE_NAME,B."分配大小", C."空闲大小" from
(select owner AS "用户名",tablespace_name from dba_segments group by owner ,tablespace_name) A,
(select tablespace_name, FILE_NAME,sum(bytes) / 1024 / 1024 || ' M' as "分配大小" from dba_data_files group by tablespace_name,FILE_NAME) B,
( select tablespace_name, sum(bytes) / 1024 / 1024 || 'M' as "空闲大小" from dba_free_space group by tablespace_name) C
where A.tablespace_name = B.tablespace_name and A.tablespace_name = C.tablespace_name

SELECT * FROM dba_data_files

三、修改表空间大小

alter database datafile 'D:\OracleDB\oradata\uniz\SITRI_DATA01.DBF' resize 512m;
alter database datafile 'D:\OracleDB\oradata\uniz\TBS_MYCIM.DBF' resize 512m; 
alter database datafile 'D:\OracleDB\oradata\uniz\UNDOTBS01.DBF' resize 512m;

–查看表空间大小
SELECT FILE_NAME as 数据文件,
TABLESPACE_NAME as 表空间名称,
AUTOEXTENSIBLE as 自动扩展,
STATUS as 状态
,MAXBYTES as 可扩展最大值
,USER_BYTES as 已使用大小,
INCREMENT_BY as 自动扩展增量
FROM dba_data_files

–自动增长，表空间不足时增加200MB，最大扩展5000MB
alter database datafile ‘D:\DataBase\Test.DBF’ autoextend on next 200m maxsize 5000m;

–扩展无限大空间
alter database DATAFILE ‘D:\DataBase\Test.DBF’ autoextend on maxsize unlimited;

temp表空间的查询、修改：

SELECT
	tablespace_name, ROUND ( free_space / 1024 / 1024 / 1024, 2 ) "free(GB)",
	ROUND ( tablespace_size / 1024 / 1024 / 1024, 2） "total(GB)",
	ROUND ( NVL (free_space, 0) * 100 / tablespace_size, 3 ) "Free percent"
FROM  dba_temp_free_space;

修改临时表空间tmp的大小：
alter database tempfile ‘/home/oracle/oradata/trade/temp01.dbf’ resize 4096m；

查看表空间使用百分比

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
       D.TOT_GROOTTE_MB "表空间大小(M)",
       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
       TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '990.99') || '%' "使用比",
       F.TOTAL_BYTES "空闲空间(M)",
       F.MAX_BYTES "最大块(M)"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
               ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
          FROM SYS.DBA_DATA_FILES DD
         GROUP BY DD.TABLESPACE_NAME) D
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
 ORDER BY 1