Re: Partition recovery question

Hey Dennis,

You brought up an interesting situation so I had to go test it. I'll provide my test case in a second, but the summary is that I would copy or export this table. You should still be able to read from and write to this table, including the partition which belongs in the deleted datafile. If, since datafile dropped, your database has been shutdown, you should still be able to read from and write to this table. In the after shutdown scenario, I saw that newly inserted records we're going into the next highest range partition. Basically, this confirms that you don't seem to have a problem with that table. HOWEVER, I'm not sure I'd be comfortable with that and since you can read from it, I would either export it or copy it using create table as select ... and drop this original table. Even though while testing I couldn't find a circumstance where I lose access to this table, it still may be in an unstable condition.

Here's what I did...
Create 3 tablespaces (testdata1, testdata2, testdata3), each locally managed uniform size 1M. Then create table with 3 range partitions, 1 partition in each tablespace. The table was partitioned by date, basically p1 max date of 1-01-2001, p2 max date of 1-01-2002, and p3 max date of 1-01-2003. Then I put data in parts 1 and 3. I then went to the OS and removed the file for tablespace testdata2, which contained empty partition part2. At this point I could read/write to table including date range covered in partition p2. Now, I alter database datafile '/u02/oradata/DBID/testdata2.dbf' offline drop. Shutdown and restart database.
Now, I can still read/write to table including date range which was covered in p2, but that data is now going into partition p3. ((ie... after restart, then analyze compute, SQL> select partition_name, num_rows from user_tab_partitions;

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
P1                                  13752
P3                                    371

Now, drop tablespace testdata2  (which used to contain partition p2).
Now, can still read from and write to table ok and no reference of partition p2 in user_tab_parts.  Also, using dbastudio, I do a show object ddl, and it also shows no reference to partition p2.  It seems to be legitimately and safely gone.
But, personnally, I wouldn't trust this for long term production use.  I would go ahead and copy or export the table.

Thanks,
Darrell

>>> DWILLIAMS_at_LIFETOUCH.COM 02/21/03 08:19AM >>>
I lost a data file that contained the tablespace for an empty partition. I dropped the datafile from Oracle, and the table itself seems okay, but I'm wondering what I can do with that partition. Can I simply merge that partition with another partition? Any ideas especially if you've encountered a similar situation would be welcome.  

Oracle 8.1.6
Alpha Tru64
Range partitions  

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.COM 

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com 
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Darrell Landrum
  INET: DLANDRUM_at_zalecorp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).