How to restore Locally Managed Temporary Tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: How to restore Locally Managed Temporary Tablespace

Hybrid View

  1. #1
    Join Date
    Mar 2001
    Posts
    286
    A data file for Locally Managed Temporary Tablespace was accidentally dropped on the OS level.

    I tried to restore, but got the following errors. How do you solve it?

    SQL> select tablespace_name, file_name, bytes/1048576 "MB" from dba_temp_files;
    select tablespace_name, file_name, bytes/1048576 "MB" from dba_temp_files
    *
    ERROR at line 1:
    ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
    ORA-01110: data file 201: '/u03/oradata/ocom/big_temp01.dbf'


    SQL> ALTER TABLESPACE BIG_TEMP ADD TEMPFILE '/u03/oradata/ocom/big_temp01.dbf' SIZE 1073741824;
    ALTER TABLESPACE BIG_TEMP ADD TEMPFILE '/u03/oradata/ocom/big_temp01.dbf' SIZE 1073741824
    *
    ERROR at line 1:
    ORA-01537: cannot add data file '/u03/oradata/ocom/big_temp01.dbf' - file already part of database

    SQL> alter database create datafile '/u03/oradata/ocom/big_temp01.dbf' as '/u03/oradata/ocom/big_temp01.dbf';
    alter database create datafile '/u03/oradata/ocom/big_temp01.dbf' as '/u03/oradata/ocom/big_temp01.dbf'
    *
    ERROR at line 1:
    ORA-01516: nonexistent log file, datafile or tempfile '/u03/oradata/ocom/big_temp01.dbf'


  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Since it was a temp table, you can just drop it and recreate one, without any worry of loosing information.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Do you know how to drop and recreate it?


  4. #4
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    Hi

    since its a temproray tablespace you are safe to drop and recreate it.


    drop tablespace
    -----------------------
    ALTER DATABASE BIG_TEMP '/u03/oradata/ocom/big_temp01.dbf' DROP;

    here the control file would be written to saying that your tempfile is dropped


    recreate the tablespace
    ----------------------------
    CREATE TEMPORARY TABLESPACE BIG_TEMP
    TEMPFILE '/u03/oradata/ocom/big_temp01.dbf'
    SIZE 20M REUSE
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;


    regards
    hrishy

  5. #5
    Join Date
    Jun 2001
    Posts
    316
    this just drops the datafile virtually
    but physically it still xists on ur machine
    u would have to delete the file manually...
    it wont create any kinda problem..but it does eat up the space alloted to it...

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