More Tablespace Needed

Last updated
Save as PDF
Share
1. Share
2. Tweet
3. Share

Article Type: General
Product: Aleph
Product Version: 20

Description:
I tried to perform a UTIL-F-1-13 (Immediate Update of a Single Document) for a record.
I get a couple of Oracle Tablespace Errors.

Sample Log Errors

Oracle error: io_z97_write
ORA-01653: unable to extend table XXX01.Z97 by 8192 in tablespace TS3D

Oracle error: io_z98_write
ORA-01653: unable to extend table XXX01.Z98 by 8192 in tablespace TS3D
Z98 WRITE ERROR -074006116083000000001

How can we overcome these errors?

Resolution:
As you observed, there was not sufficient space in one of the tablespaces.

Using an internally-created script, we observed that the problem was limited to tablespace TS3D.
>> csh -f tblspc.csh
This displays information for tablespaces used by Aleph:
NAME M TOTAL M USED M FREE % FREE
--------------- --------- --------- --------- ------
TS0 4096 3446 648 15.82%
TS2D 2048 1771 276 13.48%
TS2X 4048 1995 2051 50.67%
TS3D 6144 6130 12 .20%
TS3X 6144 4608 1534 24.97%
TS1X 2024 386 1635 80.78%
TS1D 1024 384 638 62.30%
TS1 4096 2294 1800 43.95%

Tablespace size should be monitored regularly.
Your DBA can setup Oracle Enterprise Manager (recommended) to notify you when the tablespace falls below 10% free.
Another method would be to try the SQL query provided below (the output is displayed above) and incorporate it in a job deamon or crontab to run periodically.
*** Please note that this script is provided for your convenience and its use is optional. It is not supported by Ex Libris. ***

>> cd $alephm_scratch
>> vi tblspc.csh
- - - - - enter the text below - - - - -
echo " NAME M TOTAL M USED M FREE % FREE"
echo "--------------- --------- --------- --------- ------"
sqlplus -s << ! | more
$ALEPH_ADMIN
set heading off
set feedback off
set pause off
set line 80
set newpage 0
set space 0
set pagesize 0
set verify off
column NAME format A15
select t.tablespace_name "NAME"
,t.total
,u.used
,f.free
,to_char(f.free/t.total*100,'99999.99')||'%' "%FREE"
from (select tablespace_name
,trunc(sum(bytes)/1024/1024) "TOTAL"
from dba_data_files
where tablespace_name like 'TS%'
group by tablespace_name
) t,
(select tablespace_name
,trunc(sum(bytes)/1024/1024) "USED"
from dba_extents
where tablespace_name like 'TS%'
group by tablespace_name
) u,
(select tablespace_name
,trunc(sum(bytes)/1024/1024) "FREE"
from dba_free_space
where tablespace_name like 'TS%'
group by tablespace_name
) f
where u.tablespace_name = t.tablespace_name
and f.tablespace_name = u.tablespace_name;
- - - - - end of file - - - - -

(An alternate version that may work on non-Linux machines is attached to this SKB)

Since additional space needed to be added to TS3D, we ran UTIL-O-13-1 to check tablespace TS3D file sizes:
TS3D /exlibris1/oradata/aleph20/aleph20_ts3d_01.dbf 4194304 13
TS3D /exlibris1/oradata/aleph20/aleph20_ts3d_02.dbf 2097152 14
Traditionally, these files are usually 4Gb each. Other sizes are permissable, but this is standard practice.
Since aleph20_ts3d_02.dbf was only about 2Gb, we decided to add the additional space there. We estimated that 3.2 Gb of additional would be sufficient to bring us to our goal of between 10% and 20% free space.

To add the additional space, we ran UTIL-O-13-2, as follows:
username/password: ALEPH_DBA/ALEPH_DBA
Enter Tablespace name: TS3D
Tablespace TS3D consist of the following files:
/exlibris1/oradata/aleph20/aleph20_ts3d_01.dbf
/exlibris1/oradata/aleph20/aleph20_ts3d_02.dbf
Enter file name to resize: /exlibris1/oradata/aleph20/aleph20_ts3d_02.dbf [note that the full path is required]
Enter new file size (MB): 3200 [note that the size is in MEGAbytes, not gigabytes]

This can take a few minutes to complete.
Once the job was complete, we checked the free space again:
NAME M TOTAL M USED M FREE % FREE
--------------- --------- --------- --------- ------
TS0 4096 3446 648 15.82%
TS2D 2048 1771 276 13.48%
TS2X 4048 1995 2051 50.67%
TS3D 7296 6194 1100 15.08%
TS3X 6144 4608 1534 24.97%
TS1X 2024 386 1635 80.78%
TS1D 1024 384 638 62.30%
TS1 4096 2294 1800 43.95%

These sizes are all reasonable. If any of them approaches 10%, that tablespace should be increased.

An obvious question that might accompany this process would be: Is there enough disk space?
The answer can be found via
>> df -h
This returns a list of the mounts and free space. Since we know that the above tablespace is found in the exlibris1 directory, the relevant mount is exlibris1:
... 146G 38G 101G 28% /exlibris1
The disk is only 28% full. Up to 80% full should be ok. The overall picture must be considered: db size, upcoming plans

Article last edited: 10/8/2013