e.g
sqlplus "/as sysdba"
Investigate current tablespace state:
column "Total MB" format 99,999,999
select fs.tablespace_name "Tablespace",(df.totalspace - fs.freespace) "Used MB",fs.freespace "Free MB",
df.totalspace "Total MB",round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from
dba_data_files group by tablespace_name) df,(select tablespace_name,
round(sum(bytes) / 1048576) FreeSpace from dba_free_space
group by tablespace_name) fs where df.tablespace_name = fs.tablespace_name;
Tablespace Used MB Free MB Total MB Pct. Free
------------------- ----------- ----------- ----------- ----------
CENTERDS 1 1,023 1,024 100
TRD 2,712 52 32,764 0
SYSAUX 981 109 1,090 10
UNDOTBS1 183 23,689 23,872 99
TRD_INDX 1,729 95 1,824 5
VC50 1 1,023 1,024 100
CVCDDB 214 810 1,024 79
TRXADMIN55 86 938 1,024 92
USERS 4 1 5 20
SYSTEM 967 3 970 0
11 rows selected.
TRD tablespspace is running 0% free. That was the root cause for "ORA-1653: unable to extend table".
32gig is a standard maximum logical size an Oracle DBA could allocate for a tablespace. That is an indication that, this tablespace can no longer be extended/expanded but need to add a brand new datafile to it.
Every tablespace contains one or more datafiles. User should find out where they are and if they are autoextend.
select file_id, file_name, round(bytes/1024/1024, 2) MB, status, autoextensible, round(maxbytes/1024/1024,2) max_mb from dba_data_files where tablespace_name like upper('TRD') order by file_id;
FILE_ID FILE_NAME MB STATUS AUT MAX_MB
------- -------------------------------------- --- ------- ----- -----------
7 /home/oracle/app/oracle/oradata/orcl/TRD.dbf 32764 AVAILABLE YES 32767.98
Once finding out what is the situation for the tablespace. User should choose the right options below and implement it. User should not overallocate the tablespace extend. Gradually increasing would be ideal if the user is not familiar with the application growth rate.
Option 1: Add new datafile to existing tablespace TRD. Set maximum size to 5G and autoextend. It will grow as it needs up to 5 gig.
SYS> alter tablespace TRD add datafile '/home/oracle/app/oracle/oradata/orcl/TRD3.dbf' size 200M autoextend on maxsize 5G;
Tablespace altered.
Option 2: Add a new TRD tablespace datafile as TRD4.dbf without autoextend. This is likely the most commonly used option. This will pre-allocate all the space from the physical storage for 5 gig regardless it is used or not.
SYS> alter tablespace TRD add datafile '/home/oracle/app/oracle/oradata/orcl/TRD4.dbf' size 5G;
Tablespace altered.
Option 3: If user realized later on that 5 gig isn't sufficient, s/he can use "resize" to grow it further the datafile has not reaching its max of 32G as yet.
SYS> alter database datafile '/home/oracle/app/oracle/oradata/orcl/TRD4.dbf' resize 6G;
Database altered.
Option 4: similar to option 3 except this is to grow the maxsize and flipping it to AUTOEXTEND mode.
SYS> alter database datafile '/home/oracle/app/oracle/oradata/orcl/TRD4.dbf' autoextend on maxsize 7G;
Database altered.
User should check an overall space allocation for the tablespace and physical storage consumption. Use this query to check how the tablespace or datafiles allocation looks like after the changes.
select file_id, file_name, round(bytes/1024/1024, 2) MB, status, autoextensible, round(maxbytes/1024/1024,2) max_mb from dba_data_files where tablespace_name like upper('TRD') order by file_id;
User should also monitor the grow of the physical filesystem that housing the datafiles.
SYS> !df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00 156G 133G 15G 90% /
/dev/sda2 9.5G 6.4G 2.7G 71% /stage
/dev/sda1 99M 13M 82M 14% /boot
tmpfs 16G 4.0G 12G 26% /dev/shm
/dev/mapper/VolGroup02-backup 59G 36G 21G 63% /backup
/dev/mapper/VolGroup02-u01
