2、表空间管理

[TOC]

### 表空间管理

#### 1. 创建表空间

以表空间 `aaaaa` 为例：

```sql
CREATE TABLESPACE aaaaa DATAFILE '+DATA' SIZE 30G;
```

#### 2. 扩展表空间

##### 非ASM

扩展前先查看文件系统剩余空间：

1. **查文件系统空间**：

```sh
df -h
```

2. **查询表空间数据文件**：

```sql
SELECT file_name, file_id, tablespace_name, bytes/1024/1024/1024 AS size_gb, status 
FROM dba_data_files 
WHERE tablespace_name = 'AAAAA';
```

示例输出：

```sql
FILE_NAME                                FILE_ID   TABLESPACE_NAME   SIZE_GB   STATUS
---------------------------------------- ---------- ---------------- --------- ---------
/u03/app/11.2.0/oradata/wtest/aaaaa01.dbf 8         USERS             1         AVAILABLE 
```

3. **扩展表空间**：

```sql
ALTER TABLESPACE AAAAA ADD DATAFILE '/u03/app/11.2.0/oradata/wtest/aaaaa02.dbf' SIZE 1G;
```

4. **观察结果**：

```sql
SELECT file_name, file_id, tablespace_name, bytes/1024/1024/1024 AS size_gb, status 
FROM dba_data_files 
WHERE tablespace_name = 'AAAAA';
```

##### ASM

1. **查看ASM磁盘组当前大小**：

```sql
SELECT name, total_mb, free_mb 
FROM v$asm_diskgroup;
```

2. **添加数据文件**：

```sql
ALTER TABLESPACE AAAAA ADD DATAFILE '+data' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 30G;
```

3. **检查添加情况**：

```sql
SELECT file_name, file_id, tablespace_name, bytes/1024/1024/1024 AS size_gb, status 
FROM dba_data_files 
WHERE tablespace_name = 'AAAAA';
```

#### 3. 删除表空间

```sql
DROP TABLESPACE AAAAA INCLUDING CONTENTS AND DATAFILES;
```

#### 4. 查询表空间使用情况

```sql
SELECT b.tablespace_name,
       ROUND(b.all_byte) TOTAL,
       ROUND(b.all_byte - a.free_byte) USED,
       ROUND(a.free_byte) FREE,
       ROUND((a.free_byte / b.all_byte) * 100) "% FREE"
FROM (SELECT tablespace_name, SUM(NVL(bytes, 0)) / 1024 / 1024 AS free_byte 
      FROM dba_free_space 
      GROUP BY tablespace_name) a,
     (SELECT tablespace_name, SUM(NVL(bytes, 0)) / 1024 / 1024 AS all_byte 
      FROM dba_data_files 
      GROUP BY tablespace_name) b
WHERE b.tablespace_name = a.tablespace_name(+);
```

#### 5. 查询表空间对应的数据文件

```sql
SELECT tablespace_name, file_id, bytes / 1024 / 1024 AS size_mb, 
       file_name, autoextensible, increment_by 
FROM dba_data_files 
WHERE tablespace_name = '&tablespace_name' 
ORDER BY file_id;
```

#### 6. 查询表空间占用情况

```sql
SELECT * 
FROM (SELECT SEGMENT_NAME, SUM(bytes) / 1024 / 1024 / 1024 AS sx 
      FROM dba_segments 
      WHERE tablespace_name = 'SYSTEM' 
      GROUP BY segment_name) 
WHERE sx > 1  
ORDER BY sx DESC;
```

#### 7. 查询表空间和数据文件的基本信息

- **表空间信息**：

```sql
SELECT tablespace_name, block_size, status, logging, 
       SEGMENT_SPACE_MANAGEMENT, BIGFILE, COMPRESS_FOR 
FROM dba_tablespaces;
```

- **数据文件信息**：

```sql
SELECT file_name, file_id, tablespace_name, bytes / 1024 / 1024 / 1024 AS size_gb, status 
FROM dba_data_files;
```

#### 8. 设置表空间数据文件自动增长

```sql
SELECT tablespace_name, file_id, bytes / 1024 / 1024 AS size_mb, 
       file_name, autoextensible 
FROM dba_data_files 
WHERE tablespace_name = 'AAAAA' 
ORDER BY file_id;
```

- 设置文件ID为10的datafile，每次自动增长10M，文件最大尺寸10G：

```sql
ALTER DATABASE DATAFILE 10 AUTOEXTEND ON NEXT 10M MAXSIZE 10G;
```

### 关于Bigfile Tablespace与Smallfile Tablespace

#### Bigfile Tablespace

Bigfile Tablespace仅包含一个文件，该文件最大可包含4G的blocks。一般的Smallfile Tablespace可以包含多个小数据文件。

**优点**：

1. 支持更大的表空间，在8K的db block下可达到32T，在32K时可达到128T。
2. 减少数据文件个数，提升SGA管理性能。
3. 简化数据库管理。

**缺点**：

1. 仅适用于Oracle ASM、支持条带的逻辑卷管理软件或RAID中。
2. 禁止在不支持条带化的系统中创建Bigfile Tablespace，以避免性能降低。

#### Bigfile Tablespace管理

- **创建Bigfile Tablespace**：

```sql
CREATE BIGFILE TABLESPACE bts_name DATAFILE '+data' SIZE 2G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;
```

- **查询Bigfile Tablespace**：

```sql
SELECT * FROM DBA_TABLESPACES;
SELECT bigfile FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'SYSTEM';
```

- **修改表空间尺寸**：

```sql
ALTER TABLESPACE BTS_NAME RESIZE 10G;
```

- **禁用自动扩展**：

```sql
ALTER TABLESPACE STOCK AUTOEXTEND OFF;
```

FYF

导航1234

最近发表

友情链接