Professional Web Applications Themes

Lost datafile for dataless partition - Oracle Server

I don't have access to a DB with the partitioning option installed at the moment so I apologize in advance for offering up a suggestion that I haven't tried. I think you should be able to offline drop the datafile in question and drop the partition. If that works, you can drop the tablespace. Hope this helps, John "CT" <CTnomail.com> wrote in message news:auj0l4$gas$1news6.svr.pol.co.uk... > Oracle 8.0.5, on Tru64. > > I have a partitioned table which has lost (physically deleted) one of it's > datafiles for the partitions. The partition in question contains no data, so > no data ...

  1. #1

    Default Re: Lost datafile for dataless partition

    I don't have access to a DB with the partitioning option installed at the
    moment so I apologize in advance for offering up a suggestion that I haven't
    tried. I think you should be able to offline drop the datafile in question
    and drop the partition. If that works, you can drop the tablespace.
    Hope this helps,

    John
    "CT" <CTnomail.com> wrote in message
    news:auj0l4$gas$1news6.svr.pol.co.uk...
    > Oracle 8.0.5, on Tru64.
    >
    > I have a partitioned table which has lost (physically deleted) one of it's
    > datafiles for the partitions. The partition in question contains no data,
    so
    > no data has been lost, how can I recreate/remove this partition given the
    > absence of any viable physical backups (yes...I know....!) and the
    absence
    > of archive logs.
    >
    > Oracle doesn't seem to like the deletion of the tablespace (and associated
    > datafile) for this partition because the datafile isn't there for it to
    > interrogate and remove contents.
    >
    > Is there a way to force the deletion given I knwo the partition has not
    been
    > used, or associate a datafile with this paritions tablespace.
    >
    > Thanks
    > CT
    >
    >
    >
    >
    >

    John Darrah Guest

  2. #2

    Default Re: Lost datafile for dataless partition

    Thanks again John, but alas my problem still exists

    Tried that, got this.....

    #################
    SVRMGR> alter database datafile '/ct1/oradata1/revenue/DATA_20000612.dbf' offline drop;
    Statement processed.

    SVRMGR> select * from v$datafile where
    2> file#=1234
    3> /
    FILE# CREATION_C CREATION_ TS# RFILE# STATUS ENABLED CHECKPO
    INT CHECKPOIN UNRECOVERA UNRECOVER LAST_CHANG LAST_TIME OFFLINE_CH ONLINE_CHA ON
    LINE_TI BYTES BLOCKS CREATE_BYT BLOCK_SIZE NAME

    ---------- ---------- --------- ---------- ---------- ------- ---------- -------
    --- --------- ---------- --------- ---------- --------- ---------- ---------- --
    ------- ---------- ---------- ---------- ---------- ----------------------------
    ----------------------------------------------------
    1234 620418445 23-JAN-20 6109 666 OFFLINE READ WRITE
    0 0 680794398 02-JAN-20 0 0
    0 0 31457280 8192 /ct1/oradata1/revenue/DATA_2
    0000612.dbf
    1 row selected.

    SVRMGR> alter database open;
    Statement processed.

    SVRMGR> alter table scott.my_revenue drop partition REV_D_20000612;
    alter table scott.my_revenue drop partition REV_D_20000612
    *
    ORA-00376: file 1234 cannot be read at this time
    ORA-01110: data file 1234: '/ct1/oradata1/revenue/DATA_20000612.dbf'

    SVRMGR> drop tablespace MY_DATA_REV_20000612 including contents;
    drop tablespace MY_DATA_REV_20000612 including contents
    *
    ORA-14404: partitioned table contains partitions in a different tablespace
    SVRMGR>

    > oerr ora 14404
    14404, 00000, "partitioned table contains partitions in a different tablespace"
    // *Cause: An attempt was made to drop a tablespace which contains tables
    // whose partitions are not completely contained in this tablespace
    // *Action: find tables with partitions which span the tablespace being
    // dropped and some other tablespace(s). Drop these tables or move
    // partitions to a different tablespace

    CT: The table in question spans this tablespace among others. I'm looking to drop this
    particular partition of this table. I can't move the partition, it complains about not being able
    to access the datafile.


    SVRMGRL> alter tablespace MY_DATA_REV_20000612 offline;
    alter tablespace MY_DATA_REV_20000612 offline
    *
    ERROR at line 1:
    ORA-01191: file 1234 is already offline - cannot do a normal offline
    ORA-01110: data file 1234:
    '/ct1/oradata1/revenue/DATA_20000612.dbf'

    ################


    However Metalink has a similar occurance, but no hint of a missing datafile, but more clues none the less


    From: Finn Jorgensen
    Subject: drop tablespace including contents with partitioning.

    RDBMS Version: 8.1.6
    Operating System and Version: Solaris 2.6
    Error Number (if applicable): ORA-14404
    Product (i.e. SQL*Loader, Import, etc.): Partitioning
    Product Version:

    drop tablespace including contents with partitioning.

    Hi.

    How come it's not possible to drop a tablespace including contents if it
    contains a partition of a table, but not the entire table?
    I would have thought that it would have dropped that partition along with
    all local index partitions for that partition and invalidated global
    indexes.
    Instead I get this error message (14404) that says that I have to drop the
    entire table before dropping the tablespace, but I would like to keep the
    rest of the partitions for that table.

    I can obtain the same by exchanging partitions with tables and back, but
    it's bersome and I think the above would be the right way to do it.

    Is this on it's way in a later version of Oracle8i?

    Thanks
    Finn
    ---------------

    From: Oracle, Helen Schoone <mailto:helen.schooneoracle.com>15-Mar-00
    20:46
    Subject: Re : drop tablespace including contents with partitioning.

    Hi. There is no way to automatically drop a table partition when there are
    other partitions in a different tablespace when dropping a tablespace. Your
    options are either to drop the partition or move it to another tablespace or
    exchange it with a table prior to dropping the tablespace. What you
    experienced would be considered expected behavior and I do not believe it
    will change in future versions.
    Regards,
    Helen Schoone
    Oracle Server EE yst

    -----------------
    From: Krishna Boppana

    Subject: Re : drop tablespace including contents with partitioning.


    I ran into the same problem. To get around it, I put the tablespaces in
    Offline state, and dropped the tablespaces which have partitions.
    Fortunately, I can drop the tablespaces that have partitions for the tables.


    It makes sense only if you can plug in tablespace set you should also be
    able to drop them.

    -Krishna
    #################


    Cheers
    CT


    CT Guest

  3. #3

    Default Re: Lost datafile for dataless partition

    The solution to the problem given by the ORA-14404 is to convert the affected partition into a table.

    Create a temporary table with the exact same properties as the original
    CREATE TABLE <temp table> AS SELECT * FROM <orig table> where 1=2;

    Use 'exchange partitions' on the affected partition to convert it into a table
    ALTER TABLE <table name> EXCHANGE PARTITION <part name> WITH TABLE <temp table> WITHOUT VALIDATION;

    The tablespace should now be droppable. (assuming no other partitoins exist within it)

    ......if you really want it back as empty, recreate and re-exchange...

    Cheers
    CT


    CT Guest

Similar Threads

  1. Searching in a ?second? adn 'third' datafile
    By Synner in forum Macromedia ColdFusion
    Replies: 3
    Last Post: June 8th, 08:19 PM
  2. Boot Partition Lost in SCSI Drive
    By adnan in forum Linux / Unix Administration
    Replies: 6
    Last Post: September 3rd, 02:37 PM
  3. Delete a datafile from a tablespace
    By Billy Verreynne in forum Oracle Server
    Replies: 8
    Last Post: June 25th, 08:41 AM
  4. corrupt datafile
    By Karsten Farrell in forum Oracle Server
    Replies: 8
    Last Post: January 8th, 04:26 PM
  5. Moving SYSTEM datafile
    By Rick Denoire in forum Oracle Server
    Replies: 4
    Last Post: December 17th, 11:11 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139