Temp segment size problem!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Temp segment size problem!

Hybrid View

  1. #1
    Join Date
    Feb 2001
    Posts
    286
    Hi!

    Working on Oracle v8i with OS as NT!

    I have this problem:

    The DB constains around 2 GB of data but the Temp segment consisting of temp01.dbf file
    has shot up to 4 GB.This DB was created with default temp segment size and after that
    import was done on this newly created DB with 2 GB of data.

    Suddenly, the Temp segment consisting of temp01.dbf file has shot up to 4 GB.

    Now i am left with only 15 MB of space in D drive where Oracle is stored.Other drives are not
    having much space left either.

    Size of Oradata folder is 6 GB. I need to resize the temp segment to around 500 MB.I hope
    that this size would work with no problems for 2 GB data and that not much sorting would
    be done on this temp size!I hope I am correct!

    Moreover,I canot resize the temp file to 500 MB as Oracle throws up this error message:
    used size is beyond the resize file size.I believe I have to drop the existing temp file of
    4 GB and recreate a new temp file of 500 MB with the following command :

    ALTER TABLESPACE TEMP
    DATAFILE 'D:\ORACLE\ORADATA\PVPL\TEMP01.DBF' SIZE 500M
    AUTOEXTEND ON NEXT 1M;

    Is this command Ok for the temp segment and would the DB work if I drop and recreate a new
    temp segment as above!Pleae advise!


    Please correct me I am wrong!

    Regards,

    Amit.

    Oracle DBA (OCP) v8i,v9i

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Drop the TEMP tablespace and run this:

    Code:
    CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 'D:\ORACLE\ORADATA\PVPL\TEMP01.DBF' 
    SIZE 512M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
    You might face a problem with dropping the TEMP tablespace (the drop command might just hang). If possible, shutdown, startup and then drop and run the statement above.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  3. #3
    Originally posted by julian
    Drop the TEMP tablespace and run this:

    Code:
    CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 'D:\ORACLE\ORADATA\PVPL\TEMP01.DBF' 
    SIZE 512M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
    You might face a problem with dropping the TEMP tablespace (the drop command might just hang). If possible, shutdown, startup and then drop and run the statement above.
    If drop the tablespace hang, so will shutdown immediate,right?
    IF he is using temporary tablespace, the drop should be pretty fast.
    www.cnoug.org

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by chao_ping
    If drop the tablespace hang, so will shutdown immediate,right?
    Right!
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width