Home » RDBMS Server » Server Administration » How to remove deleted entry from sys.ts$ (Oracle 10g 10.1.0.4, Solaris version 9)
How to remove deleted entry from sys.ts$ [message #527161] Sat, 15 October 2011 23:10 Go to next message
suiren97
Messages: 48
Registered: May 2007
Location: Malaysia
Member
### Changes made ###
1 week before we did a change on tablespace segment management - from MANUAL to AUTO by following method:
1. create INVD2 & INVX2 & LOBD tablespace.
2. Move TABLE from INVD to INVD2.
3. Rebuild INDEX from INVX to INVX2.
4. Move LOBSEGMENT from INVD to LOBD tablespace.
5. After confirm no segments exist in old tablespace, offline and drop INVD & INVX.
6. Change default tablespace for INV user to INVD2.
7. RENAME TABLESPACE INVD2 to INVD, INVX2 to INVX.
8. Change default tablespace for INV user to INVD back.
9. Run Gather Schema Stat for INV using UNIX scheduler which work usually. However, error ended with ORA-03113 & ORA-03114.
10. Manual execute with same statement the following day, procedure completed successfull.

After 1 week later, inventory forms detected error FRM-40735 in all forms. Checked the gather schema stat job was run in the morning before user feedback..

AFter refer notes from metalink, I understand this is a bug where RENAME of the tablespace could not rename as the previous one, as the deleted entry is still exist in sys.ts$?

There is no segments exist in the deleted tablespace, or any user default tablespace is assigned to the deleted tablespace.

My Question:
How can we delete the deleted entry from sys.ts$?
And should we rename the tablespace from INVD to INVD3 (or can we use back INVD2) to avoid any unforseen error again?

Your advice is very much appreciated. Thanks.
Re: How to remove deleted entry from sys.ts$ [message #527162 is a reply to message #527161] Sat, 15 October 2011 23:16 Go to previous messageGo to next message
BlackSwan
Messages: 22780
Registered: January 2009
Senior Member
>Your advice is very much appreciated. Thanks.
submit Service Request to MOS
Re: How to remove deleted entry from sys.ts$ [message #527163 is a reply to message #527162] Sun, 16 October 2011 00:18 Go to previous messageGo to next message
suiren97
Messages: 48
Registered: May 2007
Location: Malaysia
Member
Yes, I'd done that.. but still waiting for their reply. That's the reason I'm trying to get any expert advice here.
Re: How to remove deleted entry from sys.ts$ [message #527165 is a reply to message #527163] Sun, 16 October 2011 00:26 Go to previous messageGo to next message
BlackSwan
Messages: 22780
Registered: January 2009
Senior Member
upgrade to supported version V11.2
Re: How to remove deleted entry from sys.ts$ [message #527167 is a reply to message #527163] Sun, 16 October 2011 00:51 Go to previous message
Michel Cadot
Messages: 59076
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
suiren97 wrote on Sun, 16 October 2011 07:18
Yes, I'd done that.. but still waiting for their reply. That's the reason I'm trying to get any expert advice here.


No responsible expert will tell you to delete from sys.ts$ without being at your place to estimate what is the actual situation and what could be the damages.
You have to wait for Oracle support but with a 10.1 version that is no more supported since many years, you are not a priority for them.

Regards
Michel

Previous Topic: Not able to login database
Next Topic: table refresh
Goto Forum:
  


Current Time: Tue Sep 16 03:02:08 CDT 2014

Total time taken to generate the page: 0.12280 seconds