Re: Lost datafile for dataless partition

From: CT <CT_at_nomail.com>
Date: Thu, 2 Jan 2003 22:35:54 -0000
Message-ID: <av2enf$9ms$1@news5.svr.pol.co.uk>

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 cumbersome 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.schoone_at_oracle.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 Analyst

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 Received on Thu Jan 02 2003 - 16:35:54 CST