Oracle 表空间

1. 查表空间详情
```
SELECT
	a.tablespace_name AS "表空间名",
	a.bytes / 1024 / 1024 AS "表空间大小(M)",
	(a.bytes-b.bytes)/ 1024 / 1024 AS "已使用空间(M)",
	b.bytes / 1024 / 1024 AS "空闲空间(M)",
	round(((a.bytes - b.bytes) / a.bytes) * 100, 2) AS "使用率"
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
```

2. 查数据文件及路径
```
SELECT * FROM dba_data_files t WHERE t.tablespace_name='ECOLOGY';
```

3. 数据文件操作
- 3.1 增大原有数据文件 30G
```
alter database datafile '/u01/app/oracle/oradata/ecology/ecology' resize 30720M;
```
- 3.2 新增一个数据文件
```
alter tablespace ECOLOGY add datafile '/u01/app/oracle/oradata/ecology/ecology2' size 1024M autoextend on maxsize 30G;
```
- 3.3 验证已经增加的数据文件
```
SELECT file_name, file_id, tablespace_name
FROM dba_data_files
WHERE tablespace_name = 'ECOLOGY'
```