Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Unanswered: Temp Tablesapce resize

    Hi,

    My temporary tablespace (datafile) is more than 8Gb, and I would like to shorten it.
    Here there is an error after attempts to resize it:

    SQL> alter database datafile '/export/oracle/oradata/pmdb/temp01.dbf' resize 200M;
    alter database datafile '/export/oracle/oradata/pmdb/temp01.dbf' resize 200M
    *
    ERROR at line 1:
    ORA-04031: unable to allocate 4180 bytes of shared memory ("shared
    pool","unknown object","sga heap","state objects")

    I have:
    Oracle 8.1.7.4 32bit
    Sun Salaris Sparc 64bit
    512Mb of RAM
    shared_pool_size = 52428800
    large_pool_size = 262144000

    Have anybody ever met with the Error after attempt to resize? Help, please...
    Vitaly

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    the mentioned error does not reaaly indicate that u cannot resize the temp tablespace but gives u the error that it can not allocate enough free memory to perform the operation.
    Try to coalesce the free space before resizing by using the coalesce command.
    (alter tablespace <tablespacename> coalesce).
    Otherwise, u might try to exclude the user from the db, assigning a large rollbacksegment to your transaction and retry resizing.

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

  3. #3
    Join Date
    Oct 2003
    Location
    Jordan
    Posts
    28

    Cool

    Hi


    As mentioned this error is related to memory issues. A very good way to go around this issue is create a new Temp tablesspace and moved schemas to it.


    Regards,



    ___________________
    Firas arramli
    SEI Systems Analyst

  4. #4
    Join Date
    Nov 2002
    Posts
    833
    alter system flush shared_pool;

    then resize the temp tablespace
    probably you need to free all the fragmented extends first by issuing
    alter tablespace temp default storage (pctincrease 0); als a workaround to do it

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    The other easy way to resize temp is straight after database startup when the temp tablespace will be empty. There are ways to clear out temp but it does depend on your Oracle version, just do a search on google 'oracle temp tablespace resize'

    Alan

  6. #6
    Join Date
    Oct 2003
    Posts
    2

    Thumbs up

    Thank you all!
    Here is the sequence of actions caused successfull resize:

    SQL> alter system flush shared_pool;
    SQL> commit;
    SQL> alter tablespace temp coalesce;
    SQL> commit;
    SQL> shutdown immediate;
    SQL> startup;
    SQL> alter database datafile '/export/oracle/oradata/pmdb/temp01.dbf' resize 200M;
    SQL> commit;

    Vitaly

Posting Permissions

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