oracle扩充表空间
时间: 2023-06-15 11:03:29 浏览: 72
在Oracle中,可以通过以下步骤来扩充表空间:
1. 查看当前表空间的使用情况:
```
SELECT tablespace_name, sum(bytes)/1024/1024 AS "Size (MB)",
sum(decode(autoextensible, 'NO', bytes, maxbytes))/1024/1024 AS "Max Size (MB)",
sum(decode(autoextensible, 'NO', bytes, maxbytes) - bytes)/1024/1024 AS "Free Space (MB)"
FROM dba_data_files
GROUP BY tablespace_name;
```
2. 确定需要扩充的表空间名称。
3. 执行以下语句来扩充表空间大小:
```
ALTER DATABASE DATAFILE '<datafile_path>' RESIZE <new_size>;
```
其中,`<datafile_path>` 是需要扩充的表空间文件的完整路径,`<new_size>` 是新的表空间大小。
例如,如果要将表空间 `users` 的大小增加到 5GB,可以执行以下语句:
```
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/mydb/users01.dbf' RESIZE 5G;
```
4. 查看表空间大小是否已经扩充成功:
```
SELECT tablespace_name, sum(bytes)/1024/1024 AS "Size (MB)",
sum(decode(autoextensible, 'NO', bytes, maxbytes))/1024/1024 AS "Max Size (MB)",
sum(decode(autoextensible, 'NO', bytes, maxbytes) - bytes)/1024/1024 AS "Free Space (MB)"
FROM dba_data_files
GROUP BY tablespace_name;
```