ORA-01653 – resizing Oracle datafiles

Error MEDNTX/1400 Transaction rolled back (ORA-01653: unable to extend table PROBIT.KUPNO by 128 in tablespace PROBIT_DATA) : ChkRspErr

ORA-01653

Make sure You have backups. Make sure You understand what is going on.

Connect:

sqlplus "/ as sysdba"

Check datafiles sizes:

select name, bytes, blocks, block_size from v$datafile;

Show all datafiles in PROBIT_DATA tablespace:

SELECT * FROM dba_data_files WHERE tablespace_name='PROBIT_DATA';

Purge the recyclebin:

purge recyclebin;

Show sizes:

SELECT tablespace_name,  SUM(bytes/1024/1024)
    FROM dba_segments
    GROUP BY tablespace_name;

Show free space:

SELECT tablespace_name,  SUM(bytes/1024/1024)
    FROM dba_free_space
    GROUP BY tablespace_name;

Resize datafile to 2500M:

ALTER DATABASE DATAFILE
    'C:\ORACLEXE\ORADATA\PROBIT\PRO1.DBF'
    RESIZE 2500M;

Leave a Reply