- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: oracle tempspace full
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
тАО05-11-2005 07:15 PM
тАО05-11-2005 07:15 PM
My idea is to put the 5Gb file offline, reduce it and then put it online again, but i am not sure if this could made the BD fail or be damaged
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-11-2005 07:27 PM
тАО05-11-2005 07:27 PM
Re: oracle tempspace full
For a workaround:
Create a second temporary tablespace (i.e. temp2), write a little script to alter all users temporary tablespace is temp2.
If the users reconnect, their new tablespace will be temp2.
Drop temporary tablespace temp, create it again, alter all users back to temporary tablespace is temp.
Now drop tempspace temp2.
Don't forget removing the O.S. files manualy for db versions <10g.
Cheerio,
Renarios
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-11-2005 08:01 PM
тАО05-11-2005 08:01 PM
Re: oracle tempspace full
Instead of that, can├В┬┤t i put offline the file of 5 Gb and resize it meanwhile, the new 2 Gb file i had created in the same tempspace is online ??
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-11-2005 08:25 PM
тАО05-11-2005 08:25 PM
Re: oracle tempspace full
Temporary tablespace is used by a sort process. As your temporary tablespace is setup as autoextend, and a huge sort process mush had increased the temporary tablespace to grow. Normally when the process is completed the space should be released. But the file size will still remain in the extended size.
When you login to enterprise manager console and look at the storage----tablespaces--- and on the right-hand pane you will see the tablespaces listsing with sizes (used and used percentage). If the used percentage is 0.008 then you can reduce the file size of the temporary tablespace.
This is how you do. Right-click on the tablespace name and ------View/Edit Details----- and this will open the Edit tablespace window and you can see the tablespace size. All you need to do is to reduce to the size you want and specify in KB or MB. Then apply the chnages and Ok.
You can reduce the temporary tablespace size using the commnad as well.
SQL> ALTER DATABASE DATAFILE '/path/name of temporary tablespace.dbf' RESIZE 99 MB;
Note: You can do this if the temporary tablespace used space has been released.
If your database is not 24x 7 and you can shutdown in the evening or at any time for few minutes maintenance, then then best thing will be to shutdown the database. And if the temporary tablespace is not yet released, then SMON will take some time to clean up this space. Once the database is brought back up, then the temporary space is relased, and you can reduce the size either by Enterprice GUI or command line.
Or you can follow Renarios├в s suggestion as well.
Indir
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-11-2005 08:27 PM
тАО05-11-2005 08:27 PM
Re: oracle tempspace full
Do it with a cron job!
Create a job.sh file (executable for the bd user at least) with:
ORA_ENVFILE="/
DB_NAME="
svrmgrl << EOT
connect / as sysdba;
alter database datafile '/.../temp
quit
EOT
exit_code=$?
Regards,
Eric Antunes
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-11-2005 08:34 PM
тАО05-11-2005 08:34 PM
Re: oracle tempspace full
It is not possible to resize a datafile offline.
You can resize the dafiles of a tablespace, but you can't drop a file of a tablespace.
So, if you resize the datafile (in OEM or sqlplus), there will remain two datafiles. Of you like to keep 2 GB of tempspace, resize both datafiles (tempfiles) to 1 GB each (1+1=2). The other way, if you want to keep just one data(temp)file of 2GB, do it the way I explained above.
Maybe Oracle Metalink is a good place to find more resources. A link for this issue is explaind at this URL:http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=111316.1
Cheerio,
Renarios
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-11-2005 08:39 PM
тАО05-11-2005 08:39 PM
Re: oracle tempspace full
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-11-2005 09:19 PM
тАО05-11-2005 09:19 PM
Re: oracle tempspace full
In the OEM (Oracle Enterprise Manager) you click to
Now you see size: 5000 MB. If you change that to 2000 MB (or 2048 MB), the datafile will be altered to 2GB.
Please do it on a quit moment (performance issues)
Cheerio,
Renarios
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-11-2005 09:29 PM
тАО05-11-2005 09:29 PM
Re: oracle tempspace full
This is normaly caused by a "bad SQL" Oracle process, you must identify it! OEM helps you to do that: enter in the Oracle TopSessions and the first session should be the one causing this!!
Best Regards,
Eric Antunes
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-11-2005 09:41 PM
тАО05-11-2005 09:41 PM