Learn how to a build a cloud-first strategyRegister Now


Trouble extending a Tablespace max size past 65gb?

Posted on 2005-04-19
Medium Priority
Last Modified: 2008-01-09

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?

Question by:seis
LVL 25

Expert Comment

ID: 13821293
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';

Expert Comment

ID: 13821607

can u please put the error message and number

LVL 11

Expert Comment

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.


Expert Comment

by:Mehul Shah
ID: 13822208

Author Comment

ID: 13824070

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.

LVL 25

Expert Comment

ID: 13824114
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}';

Author Comment

ID: 13824328
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.

LVL 25

Accepted Solution

jrb1 earned 2000 total points
ID: 13825758
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:


Author Comment

ID: 13827086
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.!


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses
Course of the Month20 days, 19 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question