在conn as sysdba以后,
执行如下语句:
select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
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;
结果如下:
TABLESPACE_NAME Sum MB used MB free MB percent_used
------------------------------ ---------- ---------- ---------- ------------
PYSA 33928 33926.3125 1.6875 100
TBSTEMPDATA 9100 9072.9375 27.0625 99.7
SYSTEM 510 508.1875 1.8125 99.64
SYSAUX 890 704.625 185.375 79.17
UNDOTBS2 1000 356.625 643.375 35.66
IND 10240 1523.1875 8716.8125 14.87
USERS 5 .4375 4.5625 8.75
UNDOTBS1 3930 200.1875 3729.8125 5.09
USERBACK 1024 2.5 1021.5 .24
发现PYSA 表空间用完
执行如下语句,想看看表空间数据文件的位置,结果却出人意料:
SQL> select file_name,tablespace_name,bytes/1024/1024 "bytes MB",maxbytes/1024/1024 "maxbytes MB" from dba_data_files
2 where tablespace_name='PYSA';
结果如下:
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME bytes MB maxbytes MB
------------------------------ ---------- -----------
/dev/raw/raw21
PYSA 33928 306176
FILE_NAME竟然是/dev/raw/raw21
但是在系统用户下执行df -h命令,结果如下,根本就没有/dev/raw/raw21
[root@racdb01 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/domuvg-root
3.0G 899M 1.9G 32% /
/dev/mapper/domuvg-var
2.0G 650M 1.3G 35% /var
/dev/mapper/domuvg-usr
3.0G 1.4G 1.4G 51% /usr
/dev/mapper/domuvg-tmp
2.0G 21M 1.9G 2% /tmp
/dev/mapper/domuvg-home
1.5G 1.1G 334M 77% /home
/dev/mapper/domuvg-opt
992M 539M 402M 58% /opt
/dev/sda1 251M 30M 208M 13% /boot
tmpfs 7.9G 0 7.9G 0% /dev/shm
/dev/mapper/domuvg-oracle
9.9G 4.1G 5.3G 44% /opt/oracle
/dev/mapper/domuvg-arch1
30G 18G 11G 63% /opt/arch1
/dev/sdb1 67G 37G 27G 58% /opt/backup
/dev/sdc1 458G 238G 198G 55% /opt/fs500g
/dev/mapper/domuvg-backup
1008M 57M 901M 6% /opt/nc_backup
插了一下相关网贴,说是裸设备的缘故,由于找不到原来的数据文件/dev/raw/raw21
所以我想再为表空间添加一个数据文件,于是执行如下语句:
alter tablespace PYSA add datafile '/opt/fs500g/pysadata001.dbf' size 10G autoextend on next 1000m maxsize 40G ;
显示结果如下:
SQL> alter tablespace PYSA
2 add datafile '/opt/fs500g/pysadata001.dbf'
3 size 10G autoextend on next 1000m maxsize 40G ;
alter tablespace PYSA
*
ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace
这样的情况我应该如何去做呢?增加表空间数据文件还是增加原来的数据库文件的大小?