topic Re: oracle tempspace full in Operating System - HP-UX

oracle tempspace full

Ignacio Rodríguez Arrós — Thu, 12 May 2005 02:15:27 GMT

I had a tempspace with a 2 Gb file autoextend, someone made a process that made this file grow till 5 Gb and made the disk full (lvol at 100%). I added another 2 Gb file to the tempspace, but now i want to reduce the original file to 2 Gb again, so the lvol return to have free space. I use the enterprise manager console, but i don´t know if i can do it with people working.
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

Re: oracle tempspace full

renarios — Thu, 12 May 2005 02:27:39 GMT

Hi ignacio,

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

Re: oracle tempspace full

Ignacio Rodríguez Arrós — Thu, 12 May 2005 03:01:20 GMT

In the enterpries manager there is a tick that say Define as default tempspace, if i create anothe tempspace and mark this tick would it be enaugh.
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 ??

Re: oracle tempspace full

Indira Aramandla — Thu, 12 May 2005 03:25:03 GMT

Hi Iqnacio,

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

Re: oracle tempspace full

Eric Antunes — Thu, 12 May 2005 03:27:11 GMT

Hi Ignacio,

Do it with a cron job!

Create a job.sh file (executable for the bd user at least) with:

ORA_ENVFILE="//[/.../].env"

DB_NAME=""

svrmgrl << EOT

connect / as sysdba;

alter database datafile '/.../temp.dbf' resize 2048000K;

quit
EOT

exit_code=$?

Regards,

Eric Antunes

Re: oracle tempspace full

renarios — Thu, 12 May 2005 03:34:29 GMT

Hi Ignacio,

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

Re: oracle tempspace full

Ignacio Rodríguez Arrós — Thu, 12 May 2005 03:39:10 GMT

Hi Indira, all nigths i shutdown the database, so yesterday nigth i did it, but when i start up the BD again, the file in the tempspace itÂ´s still full, in the enterprise manager i see %used 99,98, althougth i know that it is not true. How can i do to change this (empty the file) and resize to 2

Re: oracle tempspace full

renarios — Thu, 12 May 2005 04:19:37 GMT

Hi Ignacio,

In the OEM (Oracle Enterprise Manager) you click to , , storage, Tablespaces, Temp.
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

Re: oracle tempspace full

Eric Antunes — Thu, 12 May 2005 04:29:51 GMT

Ignacio,

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

Re: oracle tempspace full

Ignacio Rodríguez Arrós — Thu, 12 May 2005 04:41:02 GMT

Where is oracle topsessions in oem, mine is in spanish

Re: oracle tempspace full

Eric Antunes — Thu, 12 May 2005 04:51:16 GMT

Yes (see picture)!

Re: oracle tempspace full

Ignacio Rodríguez Arrós — Thu, 12 May 2005 06:12:43 GMT

I canÂ´t resize the file because it is full, at 99,98% but it canÂ´t be true because at night i shutdown the bd to do the backup, and after that i startup it, but the file in the tempspace didn,t empty... Haw can i force to empty the file to resize

Re: oracle tempspace full

Yogeeraj_1 — Thu, 12 May 2005 07:06:30 GMT

hi,

i would suggest that you create a "new" temporary tablespace,

create TEMPORARY TABLESPACE TEMP2 tempfile .....

than change the temporarry tablespace parameter for all users to TEMP2,

spool reset_user.sql
select 'alter user '||username||' temporary tablespace temp2;' from dba_users;
spool off;
@reset_user.sql

Tomorrow morning, it should be OK. You can drop the old temp tablespace.

hope this helps!

regards
yogeeraj

Re: oracle tempspace full

Eric Antunes — Thu, 12 May 2005 07:18:42 GMT

Inacio,

This must be due to a very heavy session. Execute the following to see witch is the problematic one:

select s.username "User Name",
s.osuser "OS User",
s.status "Status",
lockwait "Lock Wait",
s.program "Program",
s.machine "Machine",
s.logon_time "Connect Time",
p.program "P Program",
si.physical_reads "Physical Reads",
si.block_gets "Block Gets",
si.consistent_gets "Consistent Gets",
si.block_changes "Block Changes",
si.consistent_changes "Consistent Changes",
s.process "Process",
p.spid, p.pid, s.serial#, si.sid
from sys.v_$sess_io si, sys.v_$session s, sys.v_$process p
where s.username is not null and
si.sid(+)=s.sid
and p.addr(+)=s.paddr
order by status,si.consistent_gets+si.block_gets desc

Best Regards,

Eric Antunes

Re: oracle tempspace full

Ignacio Rodríguez Arrós — Thu, 12 May 2005 07:58:56 GMT

I know who was the guilty, and she will be "punished", but now i am going to solve the problem making another temp and deleting the old one.

Re: oracle tempspace full

Eric Antunes — Thu, 12 May 2005 08:23:45 GMT

Hi Ignacio,

Don't "punish" anybody because of me, ok? :)

Cheers,

Eric

Re: oracle tempspace full

Ignacio Rodríguez Arrós — Thu, 12 May 2005 09:36:55 GMT

Dont worry Eric, it will be a "symbolic punish" ;-)

Re: oracle tempspace full

Ignacio Rodríguez Arrós — Fri, 13 May 2005 02:04:56 GMT

Done, everything is allrigth, thanks to all...and sorry for my english..

Re: oracle tempspace full

Volker Borowski — Mon, 16 May 2005 15:33:42 GMT

No,
not all fine, but two things to check !

Looks like your "old" TEMP-TS was in fact a data-tablespace of type "permanent" (concluded from the fact, that a re-cycle of the DB did not reset the allocated space).
If you followed the procedure described above (which is fine !), you now have changed your "old-style" temp-space to a real temporary tablespace.
So you need to check:

a) Depending on your backup type, you might need to extend your documentation how to recreate the tempfiles if they are not part of your backup, or change your backup to include the tempfiles.
I think RMAN takes care of everything, but if you use filesystem/script based backups something has changed now, which "might" lead to additional steps needed in case of recovery.

b) A tempfile is a sparse file by default. So your freespacemonitoring needs adjustment, or you need to convert the files.
http://www.unixguide.net/unix/sparse_file.shtml

Volker

Re: oracle tempspace full

Ignacio Rodríguez Arrós — Tue, 17 May 2005 02:01:44 GMT

Hi Vloker, i make every nigth a full backup of all the system (/), previous to this backup, i shutdown baan and oracle.

About the type of tempspace, do you mean that if i put it like permanent, if it rise, by example, 1Gb it will never be smaller than that (it will not srink automatic), but if i define it like temporary then it will grow and srink automatic.