• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 541
  • Last Modified:

EDIT TEMP DATAFILE

Hello Experts,
  I have a temp datafile that is 12GB in size. The autoextend is obviously on, but 12 GB is killing my server. How can I make this file smaller. I'm a new DBA and I need to figure this out yesterday. Any/All help will be greatly appreciated. Thanks in advance
0
ACSDBA
Asked:
ACSDBA
2 Solutions
 
sujit_kumarCommented:
alter database datafile <file_path\file_name> resize 1G;
0
 
schwertnerCommented:
There is a bug (bug 2193406) filed against 9.2,
although it appears to effect 8i (bug 1003703) as well,
which is an issue where you are unable to resize
a temporary tablespace even though it is not currently
 being used. The bug is assigned to development.
The workaround is to drop and re-add the tempfile(s).


CREATE
    TEMPORARY TABLESPACE "TEMP1" TEMPFILE
    'E:\ORACLE_DB_MAIN\ORADATA\MAIN\TEMP02.DBF' SIZE 200M
    REUSE AUTOEXTEND
    ON NEXT  640K MAXSIZE  32767M EXTENT MANAGEMENT LOCAL UNIFORM
    SIZE 1024K;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP1";

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now