Re: Question about bazaar sys views (9.0.1 Win)

From: Pete Sharman <peter.sharman_at_oracle.com>
Date: Fri, 1 Feb 2002 10:40:31 -0800
Message-ID: <JgB68.5$%d2.135@inet-nntp1.oracle.com>

I just checked this with my 9.2 beta release and got this:

SQL> select object_name from dba_objects where substr(object_name,1,1)='_'; OBJECT_NAME

_ALL_INSTANTIATION_DDL
_ALL_REPCOLUMN
_ALL_REPCOLUMN_GROUP
_ALL_REPCONFLICT
_ALL_REPEXTENSIONS
_ALL_REPFLAVOR_OBJECTS
_ALL_REPGROUPED_COLUMN
_ALL_REPL_NESTED_TABLE_NAMES
_ALL_REPPARAMETER_COLUMN
_ALL_REPRESOLUTION
_ALL_REPSITES_NEW_DBA_REPL_NESTED_TABLE_NAMES
_DEFSCHEDULE
_DEFTRANDEST
_USER_REPL_NESTED_TABLE_NAMES
_ALL_INSTANTIATION_DDL
_ALL_REPEXTENSIONS
_ALL_REPSITES_NEW
18 rows selected.

So they're all replication views if you look at them. So next step was to try to recompile them. If you use this sort of syntax:

ALTER VIEW "_ALL_REPSITES_NEW" COMPILE; these views will compile quite happily. You have to use the double quotes to avoid invalid character error messages. So why are they invalid in the case of the original poster (note that I didn't check for invalid ones, just ones that start with an underscore)? There are two possible problems:

An error in catrepc.sql where they are created.
Shared pool too small

In my database, none of these objects are invalid, so I suspect the 2nd one is the answer.

Next question - what are they for? I certainly hadn't seen them before, so I drilled into catrepc.sql to find out. The only comment I can see was in the actual comments at the top of the file. Here's the relevant line:

Rem sbalaram 02/09/01 - Add more "_ALL_*" views for internal use

So it appears they're for internal use and that's all I know!

--
HTH.  Additions and corrections welcome.

Pete
Author of "Oracle8i: Architecture and Administration Exam Cram"

"Controlling developers is like herding cats."
Kevin Loney, Oracle DBA Handbook

"Oh no, it's not.  It's much harder than that!"
Bruce Pihlamae, long-term Oracle DBA

"Tom Dyess" <tdyess_at_dyessindustries.com> wrote in message
news:MrA68.184420$_w.28792402_at_typhoon.tampabay.rr.com...
> Well, the ALL_* views are still there, only they aren't prefixed with an
> underscore, for example  --
>
> ALL_REPCOLUMN
> ALL_REPCOLUMN_GROUP
> ALL_REPCONFLICT
> ALL_REPRESOLUTION
>
> are all there under as their own entities, but there are copies
> (uncompilable) of these views with an underscore before them.
>
> Tom
> www.oraclepower.com
>
> "Andrew Hardy" <nobody_at_spam.from.news.AdvanticaTech.com> wrote in message
> news:a3dm1g$fhu$1_at_sun-cc204.lut.ac.uk...
> > The '_ALL%' views are pretty standard, even if the name is a little
> strange!
> >
> > There is no need to drop them.
> >
> > Andy
> >
> > "Tom Dyess" <tdyess_at_dyessindustries.com> wrote in message
> > news:Qep68.498591$oj3.95176653_at_typhoon.tampabay.rr.com...
> > > When I was cleaning up invalid objects, I ran the following query on
an
> > > Oracle 9.0.1 database on Win2K. I got the following which is bazaar
> > because
> > > you can't have views prefixed with an underscore. Can anyone reproduce
> > this
> > > or have an explanation? (you may want to get rid of the invalid)
> > >
> > > SELECT object_name FROM ALL_OBJECTS
> > > WHERE status = 'INVALID'
> > > AND owner IN ('SYS','SYSTEM')
> > >
> > > OBJECT_NAME
> > > ------------------------------
> > > _ALL_REPCOLUMN
> > > _ALL_REPCOLUMN_GROUP
> > > _ALL_REPCONFLICT
> > > _ALL_REPRESOLUTION
> > > 4 rows retrieved
> > >
> > > Tom
> > > www.oraclepower.com
> > >
> > >
> > >
> >
> >
> >
>
>

Received on Fri Feb 01 2002 - 12:40:31 CST