oracle表空间临时表空间信息查询教程
2017-12-28 10:27:18         来源：yangdengxian的博客

### 1、查询表空间信息

`select   *   from   dba_tablespaces`

### 2、查看用户和表空间的关系

`select username,default_tablespace from user_users;`

### 3、查询表空间大小，剩余量，利用率，包含临时表空间

```SELECT
*
FROM
(
SELECT
A .tablespace_name,
TO_CHAR (
A .bytes / 1024 / 1024,
'99,999.999'
) total_bytes,
TO_CHAR (
b.bytes / 1024 / 1024,
'99,999.999'
) free_bytes,
TO_CHAR (
A .bytes / 1024 / 1024 - b.bytes / 1024 / 1024,
'99,999.999'
) use_bytes,
TO_CHAR (
(1 - b.bytes / A .bytes) * 100,
'99.99'
) || '%' USE
FROM
(
SELECT
tablespace_name,
SUM (bytes) bytes
FROM
dba_data_files
GROUP BY
tablespace_name
) A,
(
SELECT
tablespace_name,
SUM (bytes) bytes
FROM
dba_free_space
GROUP BY
tablespace_name
) b
WHERE
A .tablespace_name = b.tablespace_name
UNION ALL
SELECT
c.tablespace_name,
TO_CHAR (
c.bytes / 1024 / 1024,
'99,999.999'
) total_bytes,
TO_CHAR (
(c.bytes - D .bytes_used) / 1024 / 1024,
'99,999.999'
) free_bytes,
TO_CHAR (
D .bytes_used / 1024 / 1024,
'99,999.999'
) use_bytes,
TO_CHAR (
D .bytes_used * 100 / c.bytes,
'99.99'
) || '%' USE
FROM
(
SELECT
tablespace_name,
SUM (bytes) bytes
FROM
dba_temp_files
GROUP BY
tablespace_name
) c,
(
SELECT
tablespace_name,
SUM (bytes_cached) bytes_used
FROM
v\$temp_extent_pool
GROUP BY
tablespace_name
) D
WHERE
c.tablespace_name = D .tablespace_name
)```

### 4、扩展临时表空间容量

`alter database tempfile 'tempFilePath' resize 2048M;`

### 5、扩展表空间容量

`alter database datafile 'filePath' resize 4000m`

### 6、查询表空间文件路径，包含临时表空间

```SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES/1024/1024 AS "BYTES(M)"
FROM DBA_DATA_FILES```
```SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES/1024/1024 AS "SPACE(M)"
FROM DBA_TEMP_FILES```

### 7、查询表空间所在表大小情况

`Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name`