August 3, 2012 at 6:35 am
Hi, if I crate the default TEMP DB and declare the tablespace as temp I have no chance to create datafiles with a fix size. In our environment all datafiles are on the same lun and distributed in /ORACLE/SID/sapdata## folders. If I create now a data file for a data tablespace it could be that the TEMP DB can not grow up enougt and the sort operation failes with:
inux-x86_64 Error: 28: No space left on device
Additional information: 4
Additional information: 758400
Additional information: -1
ORA-01114: IO error writing block to file 259 (block # 758400)
ORA-27072: File I/O error
Do I have a chance to create a default TEMPDB (temporarly tablespcae) with fix sized data files? I need to see the allocated space not only in BRSPACE; I need to see the maximum size allready allocated in the file system.
Best Regards; René
August 4, 2012 at 3:41 am
You'll have to specify nogrowth for the datafile like (no autoextend clause)
Like
CREATE TEMPORARY TABLESPACE temp TEMPFILE 'd:\temp\tempfile1.dbf' SIZE 4096M ;
It pays to read the oracle concepts
August 4, 2012 at 10:26 am
Just for documentation purposes I wouldn't name my TEMP tablespace TEMPDB 'cause it feels like the SQL Server concept of tempdb which does not exist in the Oracle world.
Space allocated on your Oracle temporary tablespace will be flushed as transactions complete.
In the case of a stable database I would keep datafiles from growing automatically 'cause a transaction going crazy may blow up your file systems.
In the case of a new database and until I understand how it behaves I would create temp datafiles with an initial and max size - setup some monitoring to tell me if they are moving up so to give myself the opportunity to check what is going on.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 6, 2012 at 2:20 am
PaulB-TheOneAndOnly (8/4/2012)
In the case of a stable database I would keep datafiles from growing automatically 'cause a transaction going crazy may blow up your file systems.In the case of a new database and until I understand how it behaves I would create temp datafiles with an initial and max size - setup some monitoring to tell me if they are moving up so to give myself the opportunity to check what is going on.
>>automatically 'cause a transaction going crazy may blow up your file systems
And this is the cause why I initiated this thread. I would like to set min and max size to the same value to protect the filesystem against an overflow.
Thanks for the explanation of the Oracle concept, but this was not what I requested.
August 6, 2012 at 7:41 am
solved by myself...
Note 548221 - Temporary Files are created as sparse files
"--sparse=NEVER" is the keyword <> http://en.wikipedia.org/wiki/Sparse_file
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply