No question at this time
DBA Top 10
1 M. Cadot 3900
2 B. Vroman 700
2 P. Tsongayinwe 700
2 P. Wisse 700
5 M. Pagac 400
6 G. De Paep3 200
6 A. Deledda 200
6 D. Walgude 200
6 J. Schnackenberg 200
6 B. M 200
About
DBA-Village
Advertise here
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48757
Total active users1320
Act. users last 24h0
Act. users last hour0
Registered user hits last week24
Registered user hits last month1443
Go up

ORA-01653: unable to extend table table_name by 8192 in tablespace tablespace _name
Next thread: sqlserver sql removes script execution plan/stats immediately after completion
Prev thread: oracle 19c installation problem

Message Score Author Date
Hi Team We have an issue with a tablespace size...... Phuti Tsongayinwe May 26, 2022, 09:32
1/ 32G is most likely the biggest size you can h...... Score: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 Pts Michel Cadot May 26, 2022, 09:55
Thanks, Michel Below please find the exact comm...... Phuti Tsongayinwe May 26, 2022, 10:34
Hi Team I tried to check triggers on table GGS_...... Phuti Tsongayinwe May 26, 2022, 11:08
Hello Phuti, maybe your second file is already ...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Bruno Vroman May 26, 2022, 12:16
Thanks, Bruno The file is not yet created but I...... Phuti Tsongayinwe May 26, 2022, 13:21
I killed all the import jobs but I still get the e...... Phuti Tsongayinwe May 26, 2022, 15:11
Thanks, Guys The issue is resolved, @Michel you...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Phuti Tsongayinwe May 26, 2022, 15:20
Glad you found the solution when I was offline. ...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot May 26, 2022, 17:12
Thanks a lot !!!... Phuti Tsongayinwe May 26, 2022, 18:06

Follow up by mail Click here

Subject: ORA-01653: unable to extend table table_name by 8192 in tablespace tablespace _name
Author: Phuti Tsongayinwe, South Africa
Date: May 26, 2022, 09:32, 487 days ago
Os info: solaris 11
Oracle info: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
Message: Hi Team

We have an issue with a tablespace size, now when we try to add a datafile we get the error :

ORA-01653: unable to extend table table_name by 8192 in tablespace tablespace_name.

We are busy running the import and now it is in a resumable state.

The tablespace has one datafile that is on an autoextend and it has reached 32G.

How can I resolve this?

Please help !!!

Thanks in advance...

Phuti
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: ORA-01653: unable to extend table table_name by 8192 in tablespace tablespace _name
Author: Michel Cadot, France
Date: May 26, 2022, 09:55, 487 days ago
Score:   Score: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 Pts
Message:
1/ 32G is most likely the biggest size you can have for a file in your version
2/ It is a great pity you didn't post the complete command you executed and error message you got but, if it is really on a command adding a file to the tablespace:
2a/ If the error is on a SYS base table we can't say anything without knowing which table it is
2b/ if it is on a custom table then most likely the error comes from a DDL trigger, you have to find which one to disable it and retry the command.

Regards
Michel

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: ORA-01653: unable to extend table table_name by 8192 in tablespace tablespace _name
Author: Phuti Tsongayinwe, South Africa
Date: May 26, 2022, 10:34, 487 days ago
Message: Thanks, Michel

Below please find the exact command... 


SQL> ALTER TABLESPACE GGS_DATA ADD DATAFILE '/u01/app/oracle/oradata/orcl/ggs_data_02.dbf' SIZE 1G REUSE AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED;

ALTER TABLESPACE GGS_DATA ADD DATAFILE '/u01/app/oracle/oradata/orcl/ggs_data_02.dbf' SIZE 1G REUSE AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01653: unable to extend table GGS_OWNER.GGS_MARKER by 8192 in tablespace

GGS_DATA

ORA-06512: at line 1288

ORA-01653: unable to extend table GGS_OWNER.GGS_MARKER by 8192 in tablespace

GGS_DATA


It's a custom table so I will check the trigger as you have suggested.

Much appreciated !!!

Thanks Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: ORA-01653: unable to extend table table_name by 8192 in tablespace tablespace _name
Author: Phuti Tsongayinwe, South Africa
Date: May 26, 2022, 11:08, 487 days ago
Message: Hi Team

I tried to check triggers on table GGS_OWNER.GGS_MARKER but I don't see any triggers.

Thanks
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: ORA-01653: unable to extend table table_name by 8192 in tablespace tablespace _name
Author: Bruno Vroman, Belgium
Date: May 26, 2022, 12:16, 487 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hello Phuti,

maybe your second file is already exhausted and you need more space?
Can you check the size of the tablespace in the source database to have an idea?

You can add more datafiles (I prefer to use 5 files of ~20G instead of 3 files of 32GB): you don't have to wait for a file to reach its max size to add a new one.

Remark: I don't like the "REUSE" and "UNLIMITED" clauses...
ALTER TABLESPACE GGS_DATA ADD DATAFILE '/u01/app/oracle/oradata/orcl/ggs_data_03.dbf' SIZE 1G AUTOEXTEND ON NEXT 512M MAXSIZE 30G;

ALTER TABLESPACE GGS_DATA ADD DATAFILE '/u01/app/oracle/oradata/orcl/ggs_data_04.dbf' SIZE 1G AUTOEXTEND ON NEXT 512M MAXSIZE 30G;
Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: ORA-01653: unable to extend table table_name by 8192 in tablespace tablespace _name
Author: Phuti Tsongayinwe, South Africa
Date: May 26, 2022, 13:21, 487 days ago
Message: Thanks, Bruno

The file is not yet created but I have import jobs that are still running. I kill the jobs and try to add the datafile.

Thanks
Phuti
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: ORA-01653: unable to extend table table_name by 8192 in tablespace tablespace _name
Author: Phuti Tsongayinwe, South Africa
Date: May 26, 2022, 15:11, 487 days ago
Message: I killed all the import jobs but I still get the errors.

Thanks
Phuti
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: ORA-01653: unable to extend table table_name by 8192 in tablespace tablespace _name
Author: Phuti Tsongayinwe, South Africa
Date: May 26, 2022, 15:20, 487 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Thanks, Guys

The issue is resolved, @Michel you were spot on, we have a GGS_DDL_TRIGGER_BEFORE that needed to be disabled. 


SQL> alter trigger GGS_DDL_TRIGGER_BEFORE disable ;



Trigger altered.



SQL> ALTER TABLESPACE GGS_DATA ADD DATAFILE '/u01/app/oracle/oradata/orcl/ggs_data_02.dbf' SIZE 1G AUTOEXTEND ON NEXT 512M MAXSIZE 30G;



Tablespace altered.



SQL>


Thanks a lot...

Much appreciated !!!
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: ORA-01653: unable to extend table table_name by 8192 in tablespace tablespace _name
Author: Michel Cadot, France
Date: May 26, 2022, 17:12, 487 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
Glad you found the solution when I was offline. :)

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: ORA-01653: unable to extend table table_name by 8192 in tablespace tablespace _name
Author: Phuti Tsongayinwe, South Africa
Date: May 26, 2022, 18:06, 486 days ago
Message: Thanks a lot !!!
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here