查看表空间和解决表空间扩容ORA-01119：ORA-27040问题

1、登录数据库查看表空间使用情况
su - oracle
sqlplus / as sysdba
SQL> select df.tablespace_name “tablespace_name”,
2 totalspace - freespace “used_mb”,
3 totalspace “total_spaceM”,
4 freespace “free_spaceM”,
5 round((1 - freespace / totalspace) * 100, 2) “used%”
6 from (select tablespace_name, round(sum(bytes) / 1024 / 1024) totalspace
7 from dba_data_files
8 group by tablespace_name) df,
9 (select tablespace_name, round(sum(bytes) / 1024 / 1024) freespace
10 from dba_free_space
11 group by tablespace_name) fs
12 where df.tablespace_name = fs.tablespace_name(+) order by round((1 - freespace / totalspace) * 100, 2) desc;

tablespace_name used_mb total_spaceM free_spaceM used%

USERS 443895 572412 128517 77.55
SYSAUX 826 1894 1068 43.61
SYSTEM 733 1764 1031 41.55
UNDOTBS1 30 1960 1930 1.53

2、查看数据文件路径位置
SQL> select file#,name from v$datafile;

FILE#                  ----------NAME-----------------
          1                      /u01/app/oracle/oradata/bck/system01.dbf

          2                    /u01/app/oracle/oradata/bck/sysaux01.dbf

          3                    /u01/app/oracle/oradata/bck/undotbs01.dbf

          4                    /kshfs/users01.dbf

          5                    /kshfs/users02.dbf

          6                    /kshfs/users03.dbf
1
2
3
4
5
6
7
8
9
10
11
12
3、表空间扩容（以最末尾文件为准依次添加数据文件，
本机末尾是users18.dbf）
SQL> alter tablespace USERS add datafile ‘/kshfs/users19.dbf’ size 31G;
alter tablespace USERS add datafile ‘/kshfs/users19.dbf’ size 31G
*
ERROR at line 1:
ORA-01119: error in creating database file ‘/kshfs/users19.dbf’
ORA-27040: file create error, unable to create file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 1
报错：本次是由于目录权限导致添加失败做以下解决：
出错原因：因为数据文件目录不是Oracle：oinstall权限导致失败
4、# chown -R oracle:oinstall kshfs

ls -g
total 2824
drwxr-xr-x 3 system 256 Aug 28 13:45 .java
-rw------- 1 system 15448 Nov 21 11:57 .sh_history
drwx------ 2 system 256 Aug 18 02:50 .ssh
drwx------ 2 system 256 Aug 18 10:23 .topasrecrc
-rw------- 1 system 28 Aug 20 15:23 .vi_history
drwxr-xr-x 4 system 256 Aug 15 12:24 admin
lrwxrwxrwx 1 system 8 Nov 15 14:13 bin
drwxrwxr-x 5 system 16384 Nov 21 00:00 dev
drwxr-xr-x 5 system 4096 Nov 05 13:00 esa
drwxr-xr-x 42 system 24576 Nov 15 14:15 etc
drwxrwxr-x 3 system 256 Apr 19 2012 export
-rw-r–r-- 1 system 72 Nov 18 02:00 fsout.log
drwxr-xr-x 8 bin 256 Aug 16 04:14 home
drwxr-xr-x 3 system 256 Aug 18 01:38 itjkfs
drwxr-xr-x 12 oinstall 4096 Nov 20 10:21 kshfs
6、再次创建数据文件成功
SQL> alter tablespace USERS add datafile ‘/kshfs/users19.dbf’ size 31G;

Tablespace altered.