this is urgent temp tablespace problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: this is urgent temp tablespace problem

  1. #1
    Join Date
    Mar 2001
    Posts
    65
    i added a datafile to temp tablespace temp02.dbf
    then i gave the cmd alter database datafile offline drop to drop the older one which lot of fragmentation.
    and error of the kind ora3232.
    then i removed it physically.


    the dba_data_files still showed the temp01.dbf as available.
    and queries started failing due to non-availability of temp01.

    under v$datafile temp01 appeared as recover status and 0 bytes.

    even under dba_free_space all records of temp01 are appearing with the original file id of 4.

    recover datafile or tablespace or database are also
    failing

    it's an impasse now


    [Edited by deswal_aja on 04-11-2001 at 07:42 PM]

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    It is not worth the while to drop and recreate the TEMP tablespace, because it will be fragmented pretty badly again. You could only achieve a v.minor performance boost.

    Any how follow these steps, you would be fine:


    1. Mount the database

    2. Query the V$recovery_file and V4datafile to note the filenames that correspond to the file numbers:
    select * from v$recovery_file;

    3. Alter the database to take the file off line and drop

    alter database datafile '.../TEMP01.dbf' OFFLINE DROP;


    4. alter database open;


    7. Drop the TEMP tablespace

    select * from v$tablespace;

    drop tablespace temp;

    8. Create your temprory tablespace as:

    CREATE tablespace temp datafile
    '.../TEMP01.dbf' SIZE 500K REUSE;

    9. Now you are all set.

    Good luck,
    Sam

    [Edited by sambavan on 04-11-2001 at 09:35 PM]
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Mar 2001
    Posts
    65
    though guidance came belated i must thank you sambavan

    When i posted the problem i didn't have much time to
    get the DB online.

    i droped the temp tablespace and created a new one with 2 datafiles.
    that solved the problem

    mine is ora 8.1.6 on nt server with archive log enaled.


    what bugged me was inconsistent behaviour of oracle.

    i went about the task systematically by first adding a new datafile to the temp tablespace and then offlining then dropping followed by physically dropping the original datafile.


    alter db datafile <> offline drop command got the datafile in v$datafile as 0 bytes and recover status.
    and the same cmd when fired repeatedly gave the same response: statement processed.

    why still the datafile appeared in dba_data_files as available and with original size?

    why dba_free_space continued to show all the records for the dropped datafile?


    and why execution of queries failed with error that datafile no.<> is locked/unavailable.

    even export and recover tablespace/datafile cmds failed .
    i can see any logic in this insistence on a particular file for just about any operation.

    i even backed up the control file to trace and edited the trace file removing the line specifying that particular datafile.
    at svrmgr i executed the edited tracefile.
    then at least the dba_datafiles didn't show that datafile.

    but queries like those creating indexes failed.
    export also didn't work

    on oracle 8.1.6 on hp-ux i remember having successfully removed the only datafile in temp tbs and the adding new datafile with no problem being reported later.






  4. #4
    Join Date
    Nov 2000
    Posts
    205
    From my understanding, and I could be wrong, was that you cannot 'normally' drop datafiles, except in recovery procedure. Has this changed, or have I missed something?

    Nirasha

  5. #5
    Join Date
    Mar 2001
    Posts
    65
    adding and dropping datafiles which otherwise is a routine affair seems to somewhat different when it comes to the primary datafile of the temp tablespace.

    i myself don't have a convincing answer to that.
    i'll retrace all these steps on my test db before i can get a clear idea.




  6. #6
    Join Date
    Aug 2000
    Posts
    194
    I don't think it has changed.

    The command is really misleading and tempting many to do the "drop data file".

    There are many threads here which discuss the repercussions of issuing command.


  7. #7
    Join Date
    Mar 2001
    Posts
    3
    1) You can only drop a tablespace but not a datafile, although your can bring a datafile offline.

    2) Deframentation in temp tablespace is not something you should worry.

    3) In case temp tablespace is corrupted or not working, create another temporary tablepsace and change default temporay tablespace of all users to the new temporary tablespace. But it should be done with care, especially during peak time.

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