ORA-01653: unable to extend table by in tablespace ORA-06512

Question

I tried to generate some test data by running the following sql.

BEGIN    
  FOR i IN 1..8180 LOOP
    insert into SPEEDTEST
    select 'column1', 'column2', 'column3', 'column4', 'column5', 'column6', 'column7', 'column8', 'column9', 'column10', 'column11', 'column12', 'column13', 'column14', 'column15', 'column16', 'column17', 'column18', 'column19', 'column20', 'column21', 'column22', 'column23', 'column24', 'column25', 'column26', 'column27', 'column28', 'column29', 'column30', 'column31', 'column32', 'column33', 'column34', 'column35', 'column36', 'column37', 'column38', 'column39', 'column40', 'column41', 'column42', 'column43', 'column44', 'column45', 'column46', 'column47', 'column48', 'column49', 'column50', 'column51', 'column52', 'column53', 'column54', 'column55', 'column56', 'column57', 'column58', 'column59', 'column60', 'column61', 'column62', 'column63', 'column64', 'column65', 'column66', 'column67', 'column68', 'column69', 'column70', 'column71', 'column72', 'column73', 'column74', 'column75', 'column76', 'column77', 'column78', 'column79', 'column80', 'column81', 'column82', 'column83', 'column84', 'column85', 'column86', 'column87', 'column88', 'column89', 'column90', 'column91', 'column92', 'column93', 'column94', 'column95', 'column96', 'column97', 'column98', 'column99', 'column100', i from dual;
  END LOOP;
END;
/
commit;

and it gave me following error:

ORA-01653: unable to extend table LEGAL.SPEEDTEST by 128 in tablespace LEGAL_DATA
ORA-06512: at line 4

Which indicates that I ran out of space, how do I add more and how to know how much do I need? What 128 stands for?

Answer 1

Just add a new datafile for the existing tablespace

ALTER TABLESPACE LEGAL_DATA ADD DATAFILE '/u01/oradata/userdata03.dbf' SIZE 200M;

To find out the location and size of your data files:

SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'LEGAL_DATA';

Answer 2

To resolve this error:

ORA-01653 unable to extend table by 1024 in tablespace your-tablespace-name

The simplest approach is normally to run this SQL command for extended tablespace size automatically on-demand:

alter database datafile '<your-tablespace-name>.dbf' autoextend on maxsize unlimited;

You can run this command without stopping processing or restarting the database.

Note: Smallfile datafiles typically have a limit of 32GB per file. (If you're using bigfiles or a non-standard block size, the files can be larger.) If you need more than one file can hold, you should add a new data file to your existing tablespace.

More info: alter_autoextend_on

Answer 3

You could also turn on autoextend for the whole database using this command:

ALTER DATABASE DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF'
AUTOEXTEND ON NEXT 1M MAXSIZE 1024M;

Just change the filepath to point to your system.dbf file.

Credit Here