Re: Bug in 8.0.5.1 with export/import

In article <36C9B749.2D5B_at_mail.esrin.esa.it>,   James Dickson <James.Dickson_at_mail.esrin.esa.it> wrote:
> Finding chaining in object SYS.SOURCE$ and its
> accompanying index SYS.I_SOURCE1, I exported to
> a file, dropped and reimported. Import tried to
> recreate the index with storage parameter:
> FREELIST GROUPS 1
> which (according to page 4-626 of the SQL reference
> manual volume 2 for Oracle 8) is illegal.
> So it failed with:
> ORA-02001: not permitted to create indexes with freelist groups
>
> I've reported it to Oracle as a bug.
>
> James
> --
> james_at_valhall.esrin.esa.it.NOSPAM
>

This post is not ment to be a lecture, but is intended as a waring to new DBA's not to perform certain actions on the sys owned objects.

Sys.source is a dictionary table. I can not believe that Oracle support did not point out that maintenance of dictionary tables is not supported, unless there has been a change in policy. The imp error could still be a real bug, but it could also be the result of trying to export and import a dictionary table.

An alternate approach to eliminate the chaining in a system table would be to  drop and recreate the affected packages or procedures and recreating them.

Personally, I believe that it is actually safe to export and import some of the dictionary tables like you did, but Oracle will not tell you which sys owned tables fall into this group. Obviously, any dictionary table necessary to support a DDL statement like free extents, fet$, could not be dropped and re-created since the very act of doing so would require writing to fet$. I believe that it is best to leave the dictionary tables alone except in an emergency when Oracle support is involved in manipulating internal tables.

I would like to see a system tablespace re-org utility since I believe that such a utility would be beneficial. Re-creating production databases is not feasible in the modern world, and increasing the shared pool size will only do so much to improve efficiency.

Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Feb 17 1999 - 09:26:37 CST