| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Partition recovery question
No problem, thanks for the feedback.
I learned as well!
>>> DWILLIAMS_at_LIFETOUCH.COM 02/21/03 04:34PM >>> Thanks Darrell. I ended up moving the data, dropping and recreating the table, and moving the data back.
At least I received more ideas from this list than from Oracle Support (zero). Since I didn't have an active error, it got rated a priority 4. Oh well, I'm just glad to have that over with, hopefully the load this weekend will be problem-free. IT'S MILLER TIME!!
Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
Sent: Friday, February 21, 2003 2:00 PM
To: Multiple recipients of list ORACLE-L
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).
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). Received on Fri Feb 21 2003 - 16:58:58 CST
 |
 |