Re: create table X(...) => insert into obj$ values (... 'X',...)

From: Liggs <liggs_at_hotmail.com>
Date: Wed, 29 May 2002 18:54:29 +0000 (UTC)
Message-ID: <ad3852$bcj$1@knossos.btinternet.com>

If you want to know all there is all about Oracle then this says it all (apologies if you've seen this before ) :-

Genesis of a DBA Universe

---

In the beginning was the disk array, and all was empty and raw, and UNIX moved over the face of the platters. And the DBA said: Let there be Oracle. And there was Oracle. And the environmental variables were set and the disks were striped and mirrored and the OFA was established, and behold spindle was rent asunder from spindle. And the DBA saw that all was in spec.

And it was day and it was evening of the first day.

And the DBA said: Let there be scripts. And sql.bsq brought forth myriad crawling things upon the face of the array. And catalog.sql brought forth all manner of tables and views that swim unseen beneath the waters. And catproc.sql brought forth all the built-in programs and all the hosts of the air, that the users might be given wings and take fight over the data.

And it was day and it was evening of the second day.

And the DBA said: Let there be tablepaces. And there were tablespaces. And the network administrator looked upon the disk array and did see what the tablespaces had wrought upon the disk arrays, and he did gnash his teeth and seek a new work upon the Internet with an engine of search.

And it was day and it was evening of the third day.

And the DBA created users. Male and female he created them. And he said unto the users: Thou mayest create tables and views as thou wilt. Yea, though mayest create even indexes upon the data. Only meddle not with the system tablespace, for it is a holy place, and on the day wherein thou treadest upon it, on that day thy create session shall surely be revoked. And the serpent crept among the users and whispered to them, saying: Thine roles shall not be revoked. Taste ye all of the system tablespace, for ye shall know of b-trees and hints and ye shall be as DBAs. And the users heeded the serpent and filled the system tablespace with crap. And the instance did crash and the client did wax wroth at the DBA. And the DBA did gnash his teeth and partake of the fruit of the vine, for behold the users were permanent employees and the DBA was but a contractor and could not revoke their create session.

And it was day and it was evening of the fourth day.

And the DBA did set default tablespaces and temporary tablespaces and did lock down all that was upon the face of the array with roles and profiles and all manner of quotas, yea even from the rollback segments even unto the archived redo logs.

And it was day and it was evening of the fifth day.

And the DBA created synonyms and links and did tune the server and apply patches upon the face of the database.

And it was day and it was evening of the sixth day.

And on the seventh day the DBA did rest from all the labors of the creation. And his pager did ring and he ceased from resting and did spend his sabbath on the telephone with Oracle support. And by the time the DBA got through to someone who knew wherof they spake behold it was day and it was evening of the eighth day.

And the DBA waxed wroth.

[ Stolen from http://www.orafaq.com/faqfun.htm ]

"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:ad0ueb$c3f$1_at_lust.ihug.co.nz...
> It's a good quesion.
>
> How long did it take to create the Universe, when time didn't exist until
> the Universe was created. Something like that anyway.
>
> Anyway: the point is to go to $ORACLE_HOME/rdbms/admin/bsq.sql and have a
> thorough read. It's this script that creates all the OBJ$, UET$, FET$
> segments; it also creates all the v$ views. When you can recite sql.bsq in
> your sleep you will know (a) you have been doing Oracle too long (b) you
are
> a deeply disturbed person and (c) you have the makings of a DBA.
>
> Regards
> HJR
>
>
> "Marcel Kraupp" <marcel.kraupp_at_gmx.ch> wrote in message
> news:332bb004.0205272053.1e7f177a_at_posting.google.com...
> > Hello
> >
> > I am trying to get into deeper knowledge on the x$, v$ and X$ (such
> > as user$, fet$, obj$) tables/views.
> >
> > Are the following assumptions correct:
> >
> > x$
> > if the sql parser sees an x$ table, it knows, it's not physically
> > stored on harddisk (or tablespaces) but rather is referencing
> > some bytes in memory. Therefor, it will return the values
> > of the memory disguised as table data. They cannot be altered
> > (using insert, updated and delete)
> >
> > v$
> > the same goes for v$ views. The parser realizes these are not
> > physically stored anywhere but are referencing data that the
> > instance stores (or accumulates over time). They somethimes
> > can be altered but this will not generate redo nor undo
> > (as this is not data on the hd but in the memory)
> >
> > X$
> > These tables are undistinguishable from ordinary (user created)
tables.
> > They reside on a tablespace (system); accordingly, these tables
> > _could_ (but is not allowed to do by oracle inc.) be altered and would
> > generate redo/undo if one did.
> >
> >
> > I also understand, that if I issue a statement like
> > create table XYZ (a number, b varchar2(34))
> > this will somewhere translate into a
> > insert into obj$ values (....,'XYZ',...)
> > (though I am not 100% sure of this)
> >
> > But how can the table obj$ be created if this table must be
> > around when (not if) the table is created.
> >
> > Anything?
> >
> > MK
>
>
Received on Wed May 29 2002 - 13:54:29 CDT