6

I recently needed to import a .dmp into a new user I created. I also created a new tablespace for the the user with the following command:

create tablespace my_tablespace
 datafile 'C:\My\Oracle\Install\DataFile01.dbf' size 10M
 autoextend on
 next 512K
 maxsize unlimited;

While the import was running I got an error:

ORA-01652 Unable to extend my_tablespace segment by in tablespace

When I examined the data files in the dba_data_files table, I observed the maxsize was around 34gb. Because I knew the general size of the database, I was able to import the .dmp without any issues after adding multiple datafiles to the tablespace.

Why did I need to add multiple datafiles to the tablespace when the first one I added was set to automatically grow to an unlimited size? Why was the maximum size 34gb and not unlimited? Is there a hard cap of 34gb?

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
spots
  • 2,483
  • 5
  • 23
  • 38
  • 3
    What is your DB blocksize? A (smallfile tablespace) [datafile can only have 2^22 blocks](http://docs.oracle.com/cd/E11882_01/server.112/e40402/limits002.htm#REFRN0042), so I guess you're hitting that limit with 8kb blocks? – Alex Poole May 20 '15 at 23:05
  • I believe I am hitting that limit. I had (apparently erroneously) assumed that the specifying "unlimited" will expand until the OS actually runs out of HD space. For clarification, what you're saying is that there IS a hard cap? – spots May 21 '15 at 14:14
  • 1
    Yes; but it varies by block size - the number of blocks is limited, it's not a direct limit on size. The size you specify is a soft limit. As Jon mentions, you can specify multiple datafiles whose soft limits total more than the available disk space. And remember Oracle likes to separate the logical from the physical - the same logical DDL has a different physical limit on two systems with different blocksizes. – Alex Poole May 21 '15 at 14:19
  • I'm still a bit confused though...if "unlimited" is a soft limit, what's the point of specifying a maxsize of "unlimited" when it's not really unlimited? I would have expected specifying a maxsize of unlimited to grow until the OS runs out of space, which in my case isn't a concern as I have lots of extra space and monitors in place to alert us if we start to run out. It seems like the only real way to get unlimited space is to add multiple datafiles and periodically check the consumed space to see if we need to add more. My expectation was that "unlimited" really meant unlimited haha. – spots May 21 '15 at 14:27
  • It's never completely unlimited, even with multiple datafiles (and there's a limit to how many; 1022 for smallfile), again as Jon mentioned. Unlimited means you, as the DB designer/administrator, are not imposing a limit. Is there a better term to use? Or do you want to always set a numeric limit based on the current physical conditions, which you might have to come back and increase later as you add more space, say? If a later Oracle release increases those hard limits, do you want to get that new change automatically, or have to go back and increase your limits manually? – Alex Poole May 21 '15 at 14:37
  • "Unlimited means you, as the DB designer/administrator, are not imposing a limit" - this is the part I was misunderstanding. Ty for your help, this has been very informative. – spots May 21 '15 at 14:41

2 Answers2

8

As you've discovered, and as Alex Poole pointed out, there are limits to an individual data file size. Smallfiles are limited to 128GB and bigfiles are limited to 128TB, depending on your block size. (But you do not want to change your block size just to increase those limits.) The size limit in the create tablespace command is only there if you want to further limit the size.

This can be a bit confusing. You probably don't care about managing files and want it to "just work". Managing database storage is always gonna be annoying, but here are some things you can do:

  1. Keep your tablespaces to a minimum. There are some rare cases where it's helpful to partition data into lots of small tablespaces. But those rare benefits are usually outnumbered by the pain you will experience managing all those objects.
  2. Get in the habit of always adding more than one data file. If you're using ASM (which I wouldn't recommend if this is a local instance), then there is almost no reason not to go "crazy" when adding datafiles. Even if you're not using ASM you should still go a little crazy. As long as you set the original size to low, you're not close to the MAX_FILES limit, and you're not dealing with one of the special tablespaces like UNDO and TEMP, there is no penalty for adding more files. Don't worry too much about allocating more potential space than your hard-drive contains. This drives some DBAs crazy, but you have to weigh the chance of running out of OS space versus the chance of running out of space in a hundred files. (In either case, your application will crash.)
  3. Set the RESUMABLE_TIMEOUT parameter. Then SQL statements will be suspended, may generate an alert, will be listed in DBA_RESUMABLE, and will wait patiently for more space. This is very useful in data warehouses.

Why is it called "UNLIMITED"?

I would guess the keyword UNLIMITED is a historical mistake. Oracle has had the same file size limitation since at least version 7, and perhaps earlier. Oracle 7 was released in 1992, when a 1GB hard drive cost $1995. Maybe every operating system at the time had a file size limitation lower than that. Perhaps it was reasonable back then to think of 128GB as "unlimited".

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • In my case, I don't need to worry about running out of space once it's imported as this database is read-only. What I'm still confused about it why maxsize accepts "unlimited" when it's not really unlimited. For example, if I create an initially small database in an "unlimited" tablespace (using the create tablespace cmd in my example), will I eventually get an ORA-01652 once the database grows to 34GB? From your suggestions I should create an extra datafile for additional space. HD space isn't really an issue for me, what I'd like to prevent is unexpected ORA-01652s. – spots May 21 '15 at 14:21
  • 1
    @spots - I think what Jon is saying is that rather than create an additional datafile when you hit or get close you the 32GB limit, you create 10 (or 100, or whatever) datafiles when you first create the tablespace; they'll all grow and you won't see a problem until they are all full, when you get to 320GB (or 3200GB, ...). – Alex Poole May 21 '15 at 14:50
  • @spots the "unlimited" keyword only relates to autoextend clause. it means that datafiles can grow till they reach their physical limits. Most of the production systems do not use autoextend clause anyway (for some good reasons). For example you can get into serious troubles when datafile size reaches limit of OS, or when datafiles occupy 100% of filesystem, and Oracle is suddenly unable extend the size of controlfile. When this happens it migh be non-trivial to resurect the database. – ibre5041 May 21 '15 at 19:16
  • So it is good idea to have guaranteed that there will be some spare free space on the volume. For example on older Veritas versions FS could not be extended online if there was no free space on it. – ibre5041 May 21 '15 at 19:23
0

Unlimited maxsize is not enough for this operation, also your resumable timeout must be enough, you can set the value as milisecond, if you want unlimited;

alter system set resumable_timeout=0;
Cem
  • 176
  • 8