0

I have only 2% free on tablespace UNDOTBS1. It consist from 2 datafiles.

When I launch

alter database datafile '+DATADF/dbname/datafile/undotbs1.555.23454732' resize 32G;

I got :

ORA-01144: File size (4194304 blocks) exceeds maximum of 4194303 blocks


How can I extend this tablespace?

0

Forget the filesystem mount options and 32000M (not 32000K).

The first has nothing to do with this.

32000M is also not the maximum size, and such file sizes come from the lack of understanding physical database limits: Physical Database Limits

A smallfile tablespace datafile can contain 2^22-1 * database block size bytes. With the default 8192 byte block size, that means 4194303 * 8192 = 34359730176 bytes, while 32G = 34359738368 bytes.

So this should work with the standard 8192 byte blocksize in a smallfile tablespace:

alter database datafile '+DATADF/dbname/datafile/undotbs1.555.23454732' resize 34359730176;

If you reach that limit, you can add the next datafile to the tablespace, example:

alter tablespace undotbs1 add datafile `+DATADF` size 100M autoextend on next 100m maxsize unlimited;

Where unlimited also means the 2^22-1 * database block size limit.

| improve this answer | |
  • I have two datafiles like this one - '+DATADF/dbname/datafile/undotbs1.555.23454732' I don't know what was the logic of adding numbers to this files, but can I add new file for example like this - '+DATADF/dbname/datafile/undotbs1_file3' ? And how without .dbf extension? Can I do it with working database? – Smenov Feb 25 at 7:32
  • The numbers are added automatically by ASM. Sure you can add a file like that but I would not do that. That will create an alias with that points to an automatically named file (like the ones you alread have) in the background. Just use alter tablespace undotbs1 add datafile '+DATADF'... as I already said. – Balazs Papp Feb 25 at 7:42
0

You have two options.

Your current limit with smallfiles is 2G. As you can see there the limit is 4194303 blocks, which is 4194303 x 512 byte block size = 2,147,483,136 bytes = 2G.

Option 1:

Mount your file systems with the -largefiles option, which will allow you to create 32G and higher.

Option 2:

You can only add 2G datafiles.

alter tablespace UNDOTBS1 add datafile 'path/filename' size 2G;

The -largefiles option has been the default for many years now, so there shouldn't be a problem for your unix admin to change this.

| improve this answer | |
0

I think that the problem is that, for some strange reason in Oracle's internal space calculations, "32G" just doesn't work here.

"32000K" will work perfectly well, although it may be a tiny bit smaller than you'd like.

Alternatively, just add a third Data File of whatever size you like.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.