Re: [gnome-db] Oracle, Table Names and Progress

[Biswapesh Chattopadhyay <biswapesh_chatterjee tcscal co in>]

I thought I'd join in the discussion and present what I understand
of Oracle databases. This might help the discussion.

General Note:
-------------

Every piece of information you might need to extract about an Oracle
database can be obtained from the database tables themselves. In almost
all cases, it makes more sense to do SELECTS on these special tables and
views than to resort to special OCI calls.

DB Layout:
----------

A machine can have multiple Oracle installations and multiple listener
daemons (called tnslsnr) on different ports, the main ones being 1521
and 1523. Each listener can cater to multiple databases. So, so far, we
have (forget about listeners a while):

Machine
	-> Oracle Installation
		-> Oracle database instance 1
		-> Oracle database instance 2
	-> Another Oracle installation
		-> Oracle database instance 3
		-> Oracle database instance 4

To the best of my knowledge, there is no portable way to get the
instance names of all databases available in a given machine. There is a
way to get a list of all *running* instances in a machine by looking at
processes with names like ora_pmon_(*) - where (*) is the instance name
(ORACLE_SID in Oraclespeak).

An Oracle database has two views, a physical view and a logical view. 

Physical View:
--------------
A database consists of multiple tablespaces. Each tablespace has one or
more datafiles. Each datafile has segments, each having extents, which
in turn have blocks and so on. A database also has redo logs, archive
logs, control files, etc. So, physically:
	Database Instance 
		-> Redo Logs
		-> Archive logs
		-> Control files
		-> Tablespace
			-> data file 1
			-> data file 2
Sone tablespaces are special, for example tablespaces assigned for
temporary tables, rollback segments, storing schema information (the '$'
tables), etc. Other tablespaces contain data (tables and indexes)

Information about tablespaces is present in the system table TS$. There
are three views on top, DBA_TABLESPACES and USER_TABLESPACES (see below
for explaining on the three views)

Information about data files are contained in the system table FILE$.
There is a overlying view called DBA_DATA_FILES which presents a more
user-friendly information.

There are some useful views on the underlying tables such as
DBA_FREE_SPACE, etc. which can be useful to us.

-------------
Logical View:
-------------

Logically, a database consists of multiple users. Each user 'owns'
multiple database objects, i.e., tables, indexes, etc. There are usually
two special users, SYS and SYSTEM, which own the core database objects.
These are tables that store information about the database itself. So,
	Database
		-> User 1
			-> Table 1
			-> Index 1
		-> User 2
			-> Table 2
			-> Index 2

Users can share information among themselves using roles, synonyms and
grants. For example, a user can grant SELECT privilege on a table to
another user, or to a role. The special users SYS and SYSTEM can grant
roles to other users, each role containing multiple grants. Synonyms are
syntactic sugar to enable users who are now owners to access a object by
its unqualified name (i.e. USER1_TABLE instead of USER1.USER1_TABLE) or
by a different name. There is a special role called PUBLIC: create a
PUBLIC synonym allows all users to access the underlying object using
the synonym name given.

Information about users is stored in the system table USER$. Easier way
to obtain this information is to user the overlying viewa DBA_USERS,
ALL_USERS and USER_USERS.

Details about roles and privilages can be obtained from the views
DBA_ROLES, DBA_ROLE_PRIVS, ALL_ROLE_PRIVS and USER_ROLE_PRIVS. Querying
USER_ROLE_PRIVS first on login is a good way to find out whether the
user is a DBA user and can use the DBA_* synonyms, since maximum
information can be gathered this way. For non-DBA users, it might be
better to use a judicious mixture of USER_* and ALL_* views.

Again, for synonyms, there is a SYN$ system table containing details of
synonyms, with overlying views DBA_SYNONYMS, ALL_SYNONYMS and
USER_SYNONYMS.

------------------------------------
Relating Physical and Logical Views:
------------------------------------

Physical and logical views converge at the tablespace level. Each object
has a owning tablespace, so the object data is always stored in the
owning tablespace. Note that this tablespace can span multiple files, so
the object data can be spread across multiple files. Some objects are
special, which I'll discuss below.

----------------
Oracle objects:
---------------

All Oracle object information are stored in the system table (normally
owner by the user SYS) called OBJ$. There are three views on this table,
namely DBA_OBJECTS (accessible to all users with 'DBA' privilege - 'DBA'
being a role), ALL_OBJECTS (shows all objects a user is allowed to see)
and USER_OBJECTS (objects that a user 'owns').

Objects are of two types, those who take storage on the default users
tablespaces, and those who reside purely as rows in system tables.

The following are the main objects of interest which take storage in
user's tablespaces:

Table:
------
Tables contain data in multiple columns. Each table has a owner and a
containing tablespace. An exception to this are partitioned tables,
where each table has multiple partitions, each of which can belong to a
different tablespace. In essence, a partitioned table is like a view
combining different tables of the same user each having exactly the same
description by different storage characteristics.

Information about table characteristics is stored in the system table
TAB$, which again has three overlying views, DBA_TABLES, ALL_TABLES and
USER_TABLES, which have the same meaning as the *_OBJECT tables
described above. Details about table columns can be obtained from
DBA_TAB_COLUMNS, comments from DBA_TAB_COMMENTS, table privileges from
DBA_TAB_PRIVS, etc.

Index:
------
Indexes store key data + pointers to the relevant table row. Again,
indexes can be partitioned, in exactly the same way as partitioned
tables above.

Index information is stored in the IND$ system table, overlying views
being DBA_INDEXES, ALL_INDEXES and USER_INDEXES. Otehr details are in
DBA_IND_COLUMNS, DBA_IND_PARTITIONS, etc.

Primary and unique keys of a table are combination of a constraint and
an underlying index.


Now for objects which reside purely as rows in system tables. These
tables are owned by the 'SYS' user (normally) and are stored in the
system tablespace (normally called 'SYSTEM'). 

Constraints:
------------
Details about constraints on tables (.e.g unique, primary key, check,
foreign key. etc.). These are stored in DBA_CONSTRAINTS and
DBA_CONS_COLUMNS dba views.

Triggers:
---------

Triggers to be fired when a table row is inserted/updated/deleted. he
type, owning table, code and other details are stored in the trigger$
table. As usual, it is easier to use the DBA_TRIGGERS, ALL_TRIGGERS and
USER_TRIGGERS views.

Packages, procedures and functions:
-----------------------------------

These objects store server-side business logic in the form of PL/SQL
code. You can get details about these (including code) from the system
table SOURCE$ or (better) from teh overlying views DBA_SOURCE,
ALL_SOURCE and USER_SOURCE.

Views:
------

Views are SELECT statements which present the result in a form
indistinguishable from a table. View details are stored in the VIEW$
table and the overlying views are DBA_VIEWS, ALL_VIEWS and USER_VIEWS.
The trick about views is that view columns are *also* stored in
*_TAB_COLUMNS table, just like for ordinary tables. This means that once
the view is identified, the code for describing it is the same as the
code for describing a table. Apart from this, the DBA_VIEWS view
contains the SELECT statement for the view.

------------------------------
Getting Sundry DB Information:
------------------------------

A database is not just a collection of logical and physical objects. It
also contains a lot of information that is gathered dynamically for each
session. This section details some important views and the information
that can be gleaned from them. These tables (actually synonyms on
underlying views and tables) are names V$* and V_$*. Access to these
tables is restricted.

V$SESSION, V$SESSION_CONNECT_INFO, V$SESSTAT, etc.:
----------------------------------------------------
Contains information about current open sessions. Information includes
connection status, program doing the connection, logon time, session id,
schema name, source machine, etc.

V$LOCK, V$_LOCK, V$LOCKED_OBJECT, etc.:
---------------------------------------
Contains information about locked objects. This can be useful to
identify deadlocks, hangs, etc.

V$SGA and V$SGASTAT:
--------------------
Contains information about system global area such as buffer size, etc.


V$SQLAREA and V$SQLTEXT:
------------------------

Contains information about all the SQL statements that have been/are
being executed on this database instance.

There are many more, the above are just examples to whet your appetite
about the possibilities.

----------------------------------------------------------------------

So, as you can see, creating a schema view for Oracle is as trivial as
applying SELECTS on the correct tables and presenting the information in
the proper manner. IMO, the best strategy is:
	Log the user
	Check if the user has DBA role.
	If Yes
		use DBA_ tables.
	Else
		use USER_  or ALL_tables.
	End If
	Query the above tables to create the database view in a tree structure.
Top level nodes should be 'Physical View' and 'Logical View'. Schema
objects and users should come under logica,l view, whcile storage
details, etc, should come under physical view as explained above.
	Code editing is simply extract the code using a SELECT statement and
presenting to the user. On saving, the code should be executed using OCI
calls.
	Most objects have valid/invalid states - there should be an icon to
indicate whether the object is valid or invalid. This is especially
useful for code (packages, triggers, functions and procedures)
	Some objects consist of subobjects (e.g. partioned tables and views).
There should be an option to view the subobjects in a tree structure.

Finally, if someone embarks on complete Oracle schema support for
Mergeant, I'm willing to write the necessary SQL SELECTS etc. and
provide more details.

Hope this helps.
Rgds,
Biswa.