Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id h1M2pWJ25227 for ; Fri, 21 Feb 2003 20:51:32 -0600 X-ClientAddr: 209.68.248.164 Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id h1M2pWQ25220 for ; Fri, 21 Feb 2003 20:51:32 -0600 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id PAA48811; Fri, 21 Feb 2003 15:35:00 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 005556DA; Fri, 21 Feb 2003 14:58:58 -0800 Message-ID: Date: Fri, 21 Feb 2003 14:58:58 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Darrell Landrum" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Darrell Landrum" Subject: RE: Partition recovery question Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 8bit No problem, thanks for the feedback. I learned as well! >>> DWILLIAMS@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@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@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@lifetouch.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: DWILLIAMS@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@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@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@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: DENNIS WILLIAMS INET: DWILLIAMS@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@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@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@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).