Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2006
    Location
    Mumbai, India
    Posts
    26

    Unanswered: How to drop a data file from a table space?

    How to drop a data file from a table space?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    As of Oracle 9i, anyway...

    1) Export all tables that are in the affected tablespace
    2) Drop any indexes in the affected tablespace.
    3) Drop tablespace
    4) Rebuild tablespace with the needed files
    5) Import data exported in step 1
    6) Rebuild indexes dropped in step 2.

  3. #3
    Join Date
    Feb 2004
    Location
    Riyadh
    Posts
    24

    What is the need of this option...

    Hi,

    First of all, good question and i understand you are undergoing your DBA training.
    But, in real scenarios, the case always varies from environment to environment. So first you need to analyse your specific requirement as it is a easy job to drop/delete/truncate any thing in the database but difficult to get the same back.
    Before dropping a datafile, you need to keep in mind, that by doing so no problem(s) arises.
    The command to drop a datafile is as below:
    alter database datafile 'file-name' offline drop; -- this command drops a particular datafile (for example, say file1.dbf located in '/u01/test/file1.dbf', then it is -- alter database datafile '/u01/test/file1.dbf' offline drop


    HTH,
    Sudhakar

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Please see the following link. Please be aware that if there are any blocks in the datafile that are being used, you will cause file corruption and probably tablespace corruption. This clause is typically used on a totally empty tablespace.


    http://download-east.oracle.com/docs...htm#sthref1371
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •