- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: temp.dbf - 3GB in size!
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-26-2004 03:37 AM
тАО04-26-2004 03:37 AM
I am a Unix admin looking at an Oracle temp file grow and never give back space. Wondering if something is wrong or if this is normal... If normal - we may have a coding issue that is making this file so big.
The temp.dbf file on one of our devlopment databases has grown to 3GB. The data itself isn't even that big in size.... CAn this file be shrunk? Did something not complete? Not sure where to begin... DBA stated it looks like a sort needed temp space but is unsure if we can reclaim the space now... Anyone have more info?
Here is a look at the files in that directory:
/ora01
-rw-r--r-- 1 oracle orainst 97 Jul 25 2003 afiedt.buf
-rw-r----- 1 oracle orainst 2088960 Apr 26 10:01 control02.ctl
drwxr-xr-x 2 oracle orainst 96 Jul 23 2003 lost+found
-rw-r----- 1 oracle orainst 104858624 Apr 26 01:48 redo2a.rdo
-rw-r--r-- 1 oracle orainst 3307216896 Apr 22 09:40 temp.dbf
-rw-r----- 1 oracle orainst 94380032 Apr 26 01:48 users.dbf
Here is total size for db:
667666 /ora00/
3426323 /ora01/
411658 /ora02/
230497 /ora03/
317553 /ora04/
225377 /ora05/
Thanks!
Lisa
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-26-2004 03:50 AM
тАО04-26-2004 03:50 AM
Re: temp.dbf - 3GB in size!
To much data in the temp database instead of the data being assigned to proper tablespaces.
There is a procedure with oracle to after moving stuff out of the temp tablespace to reduce its space usage and consolidate extents.
Ours is much smallter, and its generally used during installations and by the application when it creates temporary tables and such. No permanent data is stored there.
Talk to Your DBA.
SEP
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-26-2004 03:54 AM
тАО04-26-2004 03:54 AM
Re: temp.dbf - 3GB in size!
fuser -u "temp.dbf" -- what processes are using it. What are these processes then.
Should these process run all the time?
If file being used is used as temporary file? why not trim it when processes no longer access it.
> tmp.dbf
Anil
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-26-2004 04:00 AM
тАО04-26-2004 04:00 AM
Re: temp.dbf - 3GB in size!
fuser -u "temp.dbf"
temp.dbf: 15738o(oracle) 15732o(oracle) 15734o(oracle)
oracle 15738 1 0 01:48:42 ? 0:04 ora_smon_dbname
oracle 15732 1 4 01:48:41 ? 0:01 ora_dbw0_dbname
oracle 15734 1 4 01:48:41 ? 0:01 ora_lgwr_dbname
Maybe if I get enough knowledge on what I am looking at I can walk over and talk to them about it :) "dbname" was put in place opf the actual name :)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-26-2004 04:37 AM
тАО04-26-2004 04:37 AM
Re: temp.dbf - 3GB in size!
Don't just trim the file at the UNIX level, no matter what you do! You will render the database useless and will require recovery. Details Oracle expects about the datafiles and associated tablespaces are stored internally so modifications must be made from within Oracle.
Are you sure it wasn't created that large to begin with? The reason I ask is I thought datafiles had to be on contiguous blocks so it wouldn't be possible for Oracle to dynamically expand a datafile. (When more space is needed, a datafile can be added to a tablespace, but not increased in size... was the case with the ancient versions, 7.2.3.0.0 and 7.3.4.5.0, I worked with in my previous job at any rate.)
And you might want to have your DBA make sure the temporary tablespace is actually configured as temporary so permanent objects can't be stored there.
Jeff Traigle
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-26-2004 07:53 AM
тАО04-26-2004 07:53 AM
Re: temp.dbf - 3GB in size!
Oracle-Datafiles can grow if the are set to autoextend. Usually, you should never allow a datafile to grow unlimited esp. if belonging to the system tablespace.
Frist check, if this file does show up in either V$DATAFILE or V$TEMPFILE. If it is in V$TEMPFILE, the tablespace which it belongs to should be of type temporary and should simply be re-creatable when the application is down.
If it is in V$DATAFILE, you need to check, if it contains permanent segments in addition to the temporary stuff. If not, it can be re-created as well, if yes, you need to reorganize the tablespace.
Hope this helps
Volker
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-26-2004 08:39 AM
тАО04-26-2004 08:39 AM
Re: temp.dbf - 3GB in size!
Generally, the SQL code can be tuned to minimize the impact it has on the TEMP tablespace.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-26-2004 09:25 AM
тАО04-26-2004 09:25 AM
Re: temp.dbf - 3GB in size!
Can you do the following, and post it back?
su - oracle
sqlplus "/ as sysdba"
set lines 100 pages 1000
select * from v$version;
select tablespace_name,autoextensible,maxbytes from dba_temp_files;
(If this does not return any rows, run the following)
select tablespace_name,autoextensible,maxbytes from dba_data_files where tablespace_name = 'TEMP';
Thanks,
Brian
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-26-2004 02:08 PM
тАО04-26-2004 02:08 PM
Re: temp.dbf - 3GB in size!
select * from v$version;
Then, take a look at the problem tempfile, whether they are autoextensible:
(Brain has provided SQL already)
You need to find out which process is allocating too much temporary segment.
You need to take a look at where processes which are currently sorting
The following query will allow you to determine who is performing sorts
(which is the major processing of temp segments):
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-26-2004 03:59 PM
тАО04-26-2004 03:59 PM
SolutionAs others had mentioned you need to check the following.
Check if the temp tablespace (datafile) definition has the autoextensable and see if it is of type remporary or permanent. Then see of the segments defined in this TEMP tablespace are being used by the database.
If the TEMPORARY TABLESPACE is of type TEMPORARY, the space is not released
until the instance is shutdown. If the TEMPORARY TABLESPACE is of type PERMANENT, then cleanup is performed by SMON (background process)after completion of the process using it.
If a PERMANENT tablespace is used for sorting, then any temporary segments
created are dropped by SMON on completion of the statement, and the space
is released for use by other objects.
The background process SMON wakes itself every 5 minutes and checks for tablespaces with default pctincrease != 0.
And the most common indicator is the SMON process consuming large amounts of CPU for a long period trying coalesc Free space in Temp tablespace.
Secondly you can ask your DBA to check to see if there are any other objects (like table, indexes) that were created by any users for testing in the temp tablespace and if they can be droped. This will release the space in TEMP tablespace.
Then you can ask your DBA to resize the TEMP tablespace as follows.
To decrease the size of a datafile,
ALTER DATABASE DATAFILE '/
Downsizing a datafile is more complicated than increasing the size of a datafile. You cannot deallocate space from a datafile that is currently being used by database objects. To remove space from a datafile, you have to have contiguous space at the END of the datafile. Check the view DBA_FREE_SPACE to see how much space is not being used in a datafile. For the above file we get:
SELECT * FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME=TEMP
ORDER BY BLOCK_ID;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
------------------ ---------- ---------- ---------- ----------
TEMP 4 2 102400 50
TEMP 4 55 96256 47
TEMP 4 102 1890304 923
As you can see, there are two large extents at the high end of the datafile (BLOCK_ID = 55 and contains 47 blocks, BLOCK_ID=102 and contains 923 blocks). This means there are 1986560 unused bytes at the end of our datafile, almost 2MB. We want to leave some room for growth in our datafile, and depending on how the objects in that datafile allocate new extents, we could remove easily up to 1.89MB of disk space from the datafile without damaging any objects in the tablespace.
I hope this helps.
Indira A