Operating System - HP-UX
1747985 Members
4805 Online
108756 Solutions
New Discussion юеВ

Re: oracle tempspace full

 
SOLVED
Go to solution
Eric Antunes
Honored Contributor

Re: oracle tempspace full

Yes (see picture)!
Each and every day is a good day to learn.

Re: oracle tempspace full

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
Yogeeraj_1
Honored Contributor
Solution

Re: oracle tempspace full

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Eric Antunes
Honored Contributor

Re: oracle tempspace full

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
Each and every day is a good day to learn.

Re: oracle tempspace full

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.
Eric Antunes
Honored Contributor

Re: oracle tempspace full

Hi Ignacio,

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

Cheers,

Eric
Each and every day is a good day to learn.

Re: oracle tempspace full

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

Re: oracle tempspace full

Done, everything is allrigth, thanks to all...and sorry for my english..
Volker Borowski
Honored Contributor

Re: oracle tempspace full

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

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.