环境
确保当前用户有DBA权限,否则有些视图或者表不能访问
-- 查看数据库权限
select * from user_role_privs where GRANTED_ROLE = 'DBA';
-- 查看oracle版本 oracle 12c
select * from v$version;
-- dba 授权
grant dba to "IRS";
查看表空间
SELECT a.tablespace_name "表空间名",
round(a.bytes / 1024 / 1024 / 1024, 3) "表空间大小(GB)",
round((a.bytes - b.bytes) / 1024 / 1024 /1024, 3) "已使用空间(GB)",
round(b.bytes / 1024 / 1024 / 1024, 3) "空闲空间(GB)",
round(((a.bytes - b.bytes) / a.bytes) * 100, 3) "使用比"
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
ORDER BY ((a.bytes - b.bytes) / a.bytes) DESC
SELECT FILE_ID, FILE_NAME , TABLESPACE_NAME, STATUS, autoextensible, increment_by,
round(BYTES /1024/1024/1024, 2) as "表空间大小(GB)",
round(MAXBYTES /1024/1024/1024, 2) as "可扩展最大表空间大小(GB)"
FROM dba_data_files
where TABLESPACE_NAME = 'IRS'
order by FILE_ID
表空间 IRS 的存储位置(FILE_NAME ):/u01/app/oracle/oradata/EE/irs.dbf
表空间是否自动扩展(autoextensible):yes(如果磁盘空间不足,自动扩展必然失败)
表空间大小(BYTES ):2.83GB
可扩展最大表空间大小(MAXBYTES ):32GB
查看磁盘空间
[root@localhost ~]# df -hl /u01
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/rhel-root 20G 20G 46M 100% /
磁盘空间不够,增加磁盘或者新增一个表空间挂载其他目录下
扩大表空间
增加数据文件数量
alter tablespace IRS add datafile '/oradata/irs002.dbf' size 2G
autoextend on maxsize 32G;