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

User · Accepted Answer

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

User · Answer

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

User · Answer

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   lt your-tablespace-name gt  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

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

Examples related to oracle

Examples related to tablespace