Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2003
    Posts
    71

    Unanswered: Removing Datafile

    I have accidentally created a datafile for a incorrect tablespace in Oracle 8i, how can I remove the datafile, also when I try to take it offline it doesn't allow me to do so, neither does it allow me to take it offline.

    Any help is highly appreciated

    Thanks
    Reema

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    What message do you get when you

    ALTER TABLESPACE "tablespace_name" OFFLINE NORMAL;

  3. #3
    Join Date
    Oct 2003
    Posts
    71
    Originally posted by gbrabham
    What message do you get when you

    ALTER TABLESPACE "tablespace_name" OFFLINE NORMAL;
    I am able to take the tablespace offline but there is no option for deleting a datafile.

    Is there any way to rename the datafile.

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    From the manual :

    To rename datafiles in multiple tablespaces, follow these steps.

    Ensure that the database is mounted but closed.

    Copy the datafiles to be renamed to their new locations and new names, using operating system commands.

    Make sure the new copies of the datafiles have different fully specified filenames from the datafiles currently in use.

    Use ALTER DATABASE to rename the file pointers in the database's control file.

    For example, the following statement renames the datafiles filename1 and filename2 to filename3 and filename4, respectively:

    ALTER DATABASE
    RENAME FILE '/u02/oracle/rbdb1/sort01.dbf',
    '/u02/oracle/rbdb1/user3.dbf'
    TO '/u02/oracle/rbdb1/temp01.dbf',
    '/u02/oracle/rbdb1/users03.dbf;

    You could also resize the "bad" file to a minimal size
    alter database datafile 'c:\ ....\' resize 4k;

    HTH
    Gregg

  5. #5
    Join Date
    Oct 2003
    Posts
    71
    Thanks I will try it.

    Originally posted by gbrabham
    From the manual :

    To rename datafiles in multiple tablespaces, follow these steps.

    Ensure that the database is mounted but closed.

    Copy the datafiles to be renamed to their new locations and new names, using operating system commands.

    Make sure the new copies of the datafiles have different fully specified filenames from the datafiles currently in use.

    Use ALTER DATABASE to rename the file pointers in the database's control file.

    For example, the following statement renames the datafiles filename1 and filename2 to filename3 and filename4, respectively:

    ALTER DATABASE
    RENAME FILE '/u02/oracle/rbdb1/sort01.dbf',
    '/u02/oracle/rbdb1/user3.dbf'
    TO '/u02/oracle/rbdb1/temp01.dbf',
    '/u02/oracle/rbdb1/users03.dbf;

    You could also resize the "bad" file to a minimal size
    alter database datafile 'c:\ ....\' resize 4k;

    HTH
    Gregg

  6. #6
    Join Date
    Oct 2003
    Posts
    71
    Another question if the database is in mounted stage will sys login allow me to rename datafile?
    Originally posted by reemagupta
    Thanks I will try it.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If sys can't do it, do it as
    $ sqldba lmode=y
    > connect internal
    > alter ...

  8. #8
    Join Date
    Jan 2004
    Location
    India
    Posts
    113
    Did you get an answer for deleting the datafile?

    If yes then please ignore this update.
    Or else u could try this

    Alter database datafile 'filename' offline drop;

    This statement executes successfully but if you query from dba_data_files u will still find the entry for the datafile.
    Moreover from V$datafile u will see the status of the datafile is Recover.
    For which control file needs to be recreated.
    But that does not create any problem when u restart the DB.

    Another way is there which is a bit tricky.

    Issue the command
    Alter database backup control file to trace;

    The above command creates a trace file for the control file.
    Scoop out the create database command from it and save it as .sql file
    Now in that file u will find entry for all the datafiles.
    Just delete the entry for the datafile which u want to delete and run the sql file.
    Startup ur Db I don't think u will find any problem.
    Delete the .ora or .dbf file from ur file system.

    The above operation is a bit risky so before doing it in ur live Db should be tested in a test DB or proper backup is a must.


    All the best,
    KAUSHIK
    Last edited by kausik_m123; 04-08-04 at 13:18.

  9. #9
    Join Date
    Jan 2004
    Posts
    370
    You drop a datafile - you must drop the tablespace and rebuild it.
    Woe betide you if you don't!

    Do not hack the controlfile.
    Do not pass Go and collect 200.

    Go directly to your backups and rebuild.

    Otherwise, live with gbrabham's suggestion:

    You could also resize the "bad" file to a minimal size
    alter database datafile 'c:\ ....' resize 4k;

  10. #10
    Join Date
    Jan 2004
    Location
    India
    Posts
    113
    I think after the statement alter database datafile <name> offline drop ;
    u need to recreate the control file.
    So that the V$datafile,dba_data_files does not show the entry for the deleted datafile.

    But as the above update goes saying about hacking control file. I don't see the term 'HACKING' appropriate to describe it.

    Actually every DBA has his own way of doing things ..
    This site only provides the people with other DBA's viewpoint or rather what would I have done in such a scenario? Just a sharing of knowlege .

    But the decision should always be taken firmly by the person himself.


    Cheers
    Kaushik

  11. #11
    Join Date
    Jan 2004
    Posts
    370
    If you offline drop a datafile it doesn't remove it from the database.
    It offlines it.

    Rebuilding the controlfile doesn't remove the entry, and it doesn't remove it from the data dictionary.

    To remove a datafile from a data dictionary managed tablespace you must drop the tablespace and rebuild 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
  •