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?

Stands for bytes.Oracle uses tablespaces to store data.Here is an exaple where you add a datafile to a tablesapce `ALTER TABLESPACE LEGAL_DATA ADD DATAFILE ‘/u01/oradata/ userdata03. dbf’ SIZE 200M;` Make sure you cahnge it to your address — Mihai, Nov 25 '14 at 14:38
Or resize an existing datafile `alter database datafile '' resize M;` — Mihai, Nov 25 '14 at 14:45

score 71 · Accepted Answer · edited Oct 11 '17 at 02:17

71

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

edited Oct 11 '17 at 02:17

Toolkit

10,779
8
59
68

answered Nov 25 '14 at 14:46

Mihai

26,325
7
66
81

Are the spaces intended? I adjusted the path to my needs, but left the spaces in, so I ended up with: `ALTER TABLESPACE SYSTEM ADD DATAFILE ‘/usr/lib/oracle/oradata/XE/ userdata03. dbf’ SIZE 200M;` The file in my filesystem has the spaces, but it think this is wrong. However, I cannot rename the file, as I cannot take SYSTEM tablespace offline. – phm Aug 05 '15 at 07:17
@phm No the spaces are not necessary,I think Oracles ignores them. – Mihai Aug 05 '15 at 07:21
If you'd like to fix them, you *can*. Shutdown the database (e.g. `shutdown immediate`); rename the files per your OS; Restart in mount mode (`startup mount`) and check paths (`Select rpad(name, 50) as Name from v$datafile;`); simply rename the path `alter database rename file ' – The Nate Oct 01 '16 at 07:03

Celt · Answer 2 · 2016-01-11T14:35:22.770

54

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

edited Jan 11 '16 at 14:35

answered Aug 12 '15 at 13:23

Celt

2,469
2
26
43

1

Make sure you restart the database after you make the change. – J Brun Aug 26 '19 at 16:19
1

@JBrune I've never had to restart a database to to extend the maxsize of a datafile before. Maybe that's necessary for some specific combination of the SYSTEM tablespace, Oracle version, and Express Edition, but in general this is not an operation that needs a restart. (Perhaps part of the issue is that Express Edition intentionally limits users to 10GB?) – Jon Heller Mar 09 '23 at 05:30

score 2 · Answer 3 · edited Mar 09 '23 at 05:35

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

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

3 Answers3

Linked