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?

3 Answers

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'; 

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

To resolve this error:

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

Just run this PL/SQL command for extended tablespace size automatically on-demand:

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

I get this error in import big dump file, just run this command without stopping import routine or restarting the database.

Note: each data file has a limit of 32GB of size if you need more than 32GB you should add a new data file to your existing tablespace.

More info: alter_autoextend_on

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJoaXBpYm6AeIKOqKmaZWBmg3Z%2FjK6lmpqcmnq1u4yer62dnpl6ta3BpZxmmqlitq9505qZpZ2jpa6ksYyoqZplYGuCcn4%3D