1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Cannot identify/lock data file 201

Discussion in 'SQL PL/SQL' started by Revathi Thirunagari, Jul 12, 2017.

  1. Revathi Thirunagari

    Revathi Thirunagari Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Hyderabad
    Hi All,

    I have procedure which have cursor and this cursor is loading the data into some collection variables.
    When I am executing this procedure and when it is loading the cursor values into collection varaibles
    I got the following exception :

    --FAILED-ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
    ORA-01110: data file 201: '/ora02/oradata/pujty/TEMP_01.dbf.

    even i am unable to see that file at that location also .
    Could you please help me to understand this

    Thanks
    Revathi.T
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    733
    Likes Received:
    143
    Trophy Points:
    830
    Location:
    Russian Federation
    This database is developer database?
    Try this:


    Code (Text):

    alter database tempfile '/ora02/oradata/pujty/TEMP_01.dbf'  drop;

    select tablespace_name, file_name from dba_temp_files;

    alter tablespace temp2 add tempfile '/ora02/oradata/pujty/TEMP_01.dbf' size 5m;
     
    may be helpful :ora-01157
     
    Last edited: Jul 12, 2017
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,531
    Likes Received:
    360
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You've answered your own question by stating you can't find the file in the specified location; Oracle can't lock something it can't find.

    You're probably better off creating a new temporary tablespace and setting that as the default temporary tablespace, then restarting the database to release any extents in the old temporary tablespace and dropping it.
     
  4. Revathi Thirunagari

    Revathi Thirunagari Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Hyderabad
    Thanks for your help. its working fine .