Trouble extending a Tablespace max size past 65gb?


I am running 10g on a 2003 server with NTFS and I have a 200gb partition that am trying to load several thousand images to. I have a tablespace that I am importing the images into and everything was going ok till I hit 64gb for the datafile. I went into the EM and tried to iincrease the tablespace to 100gb and it returned an error stating that I can not go past 64gb. Is there another setting I am missing?

Who is Participating?
jrb1Connect With a Mentor Commented:
The maximum number of datafiles in Oracle9i and Oracle 10g Database is 65,536. However, the maximum number of blocks in a data file increase from 4,194,304 (4 million) blocks to 4,294,967,296 (4 billion) blocks.

Here's some details:
What's the exact error message? Can you post the results of this query?

set linesize 121
col file_name format a45
col tablespace_name format a20

SELECT file_name, tablespace_name,
       bytes/1024/1024 MB, blocks, AUTOEXTENSIBLE
FROM dba_data_files
where file_name = 'yourfilename';

can u please put the error message and number

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Increase the data file size.
Use, alter databse datafile '<file_path>\<file_name>.dbf' resize 120G;

Mehul ShahIT consultantCommented:
seisAuthor Commented:

When I try to add additional space and click the apply I get this as an error:

Failed to commit: ORA-03206: maximum file size of (4258240) blocks in AUTOEXTEND clause is out of range

Also if I attempt to change from value to Unlimited I don't get any errors but it simply places it back to vaule 65535 MB.

OK,that's what I was looking for.  You can create a new data file and add it to your tablspace:

CREATE DATAFILE '{newdatafilename}' SIZE 36M
AS '{yourtablespacename}';
seisAuthor Commented:
jrb1, I have added another datafile to the tablespace. So just for clearfication, I can add several datafiles to a tablespace and place them on various disk if need be and as one datafile fills up it simply starts to import to the second data file? Also there must be a datafile limit, where is this stated in the docs, or is it left out since one can keep adding datafile after datafile, (much smaller file sizes than the max of 65GB)... just wondering...

Thanks for pointing me in the right direction! I will not be able to test this second datafile till this afternoon, I'll post you once I give it a shoot.

seisAuthor Commented:
Thanks jrb1 !!! Adding the second datafile to the table space did the trick. Also I am using 16K Blocks so thus there was the 64GB limit.

I really appreciate all your help! OUTSTANDING.!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.