Cannot alter a tablespace online
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Cannot alter a tablespace online

  1. #1
    Join Date
    Nov 2000
    Posts
    440

    Cannot alter a tablespace online

    We transferered datafiles from 1 disk to another.
    On fedora core 4
    On oracle 10g.

    the first 2 datafile went ok.

    The last one is giving us trouble.

    When we try to alter the tablespace online, it gives us the error:
    "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!"
    and
    MMNL absent for 26040 secs; Foregrounds taking over


    The datafile are online. And seem ok.
    The tablespace does not want to go online.

    It is a big datafile. But should go well, it went well with the first 2 datafiles.

    Any idea?

  2. #2
    Join Date
    Nov 2000
    Posts
    440
    Those anyone had trouble putting a tablespace online after putting the datafile on a different disk?

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    error?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Dec 2006
    Location
    hyderabad
    Posts
    21

    Tablespace transfer

    I think while you want to copy datafiles from on disk to other

    1)take the corresponding tablespace to offline

    2)you need to copy the datafiles at the os level(from disk1to disk2)

    3)you could rename the the tablespace with the alter tablespace rename command

    eg: alter tablespace xyz offline;

    cp /disk1/xyz.dbf /disk2/abc.dbf

    alter tablespace xyz rename datafile /disk1/xyz.dbf /disk2/abc.dbf

    dont forget to online the above tablespace at the last
    Last edited by girirajdba; 01-10-2007 at 01:14 PM.

  5. #5
    Join Date
    Nov 2000
    Posts
    440
    I did all that, my first 2 datafile worked well.


    When we try to alter the tablespace online, it takes forever, and it gives us the error in the alert_file
    "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!"
    and
    MMNL absent for 26040 secs; Foregrounds taking over



    By stopping session to connect and disabling job that want to read my datafile, i think il get rid of the "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" error.

    But it still take forever, it is usualy a small time process.

  6. #6
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Do you use RMAN?

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    It was a bug in 9i even in earlier releases also. And it seems to be that oracle carried over it in 10g also. I think you need to contact oracle support to get a suitable patch.

    Tamil

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