Unable to drop a datafile.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Unable to drop a datafile.

  1. #1
    Join Date
    Mar 2010
    Posts
    11

    Red face Unable to drop a datafile.

    i'm unable to drop a datafile from a tablespace.

    SQL> Alter databse drop datafile '/opt/oracle/oradata/ARM/Sample.dbf';

    I get the below error:

    ORA-01900 : logfile keyword expected.

    Let me know what has to be followed?

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    there is no drop datafile command - you cannot do that

  3. #3
    Join Date
    Mar 2010
    Posts
    11
    Thanks friend..

    But if i require to drop the datafile means what has to be done..

    I heard that we have option to drop datafile in 9i and above. Not sure b4 9i.

    Please correct me if i'm wrong...

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    You are wrong in regards to 9i reference.
    If you are on 10g investigate alter tablespace drop datafile, double-check restrictions.
    During normal operation there is no reason to drop a datafile.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Mar 2010
    Posts
    11
    Thanks PAVB for updating me.

    I'm using oracle 9i version 2. So now i need to drop a datafile which has no data in it. Also the datafile is in offline status right now. is there any possible way to drop the datafile or we dont have any such option to drop..?

    Pls clarify and correct if i'm wrong.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by Aravindan.2005 View Post
    i need to drop a datafile which has no data in it
    How do you know?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Mar 2010
    Posts
    11
    Jst now i created a datafile and once created I made it offline.

    FYI.. I'm using test database. Not a production DB.

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Quote Originally Posted by Aravindan.2005 View Post
    Jst now i created a datafile and once created I made it offline.

    FYI.. I'm using test database. Not a production DB.
    you can't do that

    you cannot get rid of a datafile once you have created it

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    mmhhh... either way, as already stated drop datafile functionality was introduced on Ora10gR2.
    Check Metalink Note #111316.1
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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