ORA-01653: unable to extend table by in tablespace ORA-06512
279,692
Solution 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';
Solution 2
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
Author by
Matas Vaitkevicius
In Javascript Let me take a selfie... var self = this; #SOreadytohelp Like my answer, here's link for donations. https://www.paypal.me/Vaitkevicius
Updated on July 18, 2022Comments
-
Matas Vaitkevicius almost 2 years
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?