Menu
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?
313
Answer: The ORA-01653 error is caused because you need to add space to a tablespace. Cause: Failed to allocate an extent of the required number of blocks for a table segment in the tablespace indicated. Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.
To increase the tablespace size, add a new datafile. When you add a new data file, be sure that you choose the right values for AUTOEXTEND, SIZE, and MAXSIZE.
ALTER TABLESPACE users ADD DATAFILE '/u02/oracle/rbdb1/users03. dbf' SIZE 10M AUTOEXTEND ON NEXT 512K MAXSIZE 250M; The value of NEXT is the minimum size of the increments added to the file when it extends. The value of MAXSIZE is the maximum size to which the file can automatically extend.
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
20
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
