Various Oracle Terms, v00.??

Tablespace
Data file
A named set of data files.
You use the name to choose where segments get Database
Block A list of blocks, and some control stuff
To estimate the cost of accessing the blocks, Oracle stored, which may have an effect on how accesses
assumes that they are contiguous on disk to them perform. A (slightly) named collection of
The smallest piece of disk Can be defined as temporary. tablespaces and control stuff.
Could be on a file-system The system tablespace and the SYS user
that oracle rewrites In which case, it must be a multiple of the In which case, work to enable recovery is not
Has timestamps at the beginning and done, so permanent segments cannot be stored are special, because they contain the
file-system block-size, or the file-system will waste data dictionary.
end. If Recovery Manager reads it and work, reading extra for every block. there, because they would not be recovered after
they don't agree, this is a 'fractured a crash. One kind of backup is export/import.
block'; Recovery Manager assumes Could be on a disk partition The contents of the SYS user are not
someone else is writing it, waits (I This is necessary for RAC. It used to be Can contain variable or exported.
don't know how long) and reads again. not-recommended, because it made uniform extent sizes.
tablespace-by-tablespace backups harder. It gives a Uniform (fixed) extent size was introduced to global_name()
. . small cpu-time win, because the OS does not have to reduce fragmentation, and free extents are Don't know what it affects; possibly
operate the file-system. tracked differently. some of the SQL*Net (remote access &
Selecting 'uniform' usually causes fewer and database links) stuff.
v$datafile() larger extents; we do this for all new customers.
dba_data_files() Provides default segment properties
dba_tablespaces()

Extent

A collection of adjacent blocks Control file

Always in a single data file Segment
Size controlled by tablespace and segment properties A mini-database for finding
A named collection of extents datafiles, and recording backups.
DBA_EXTENTS() Always in a single tablespace Recovery manager writes its actions
May not work for uniform sized extents? here, and then reads them later into
Miin/max extents, size, pct_increase
Without 'uniform' these control the extent size; with the recovery catalog.
uniform, they control the number of extens. One way of finding out (some of) what's
in here is 'alter database backup
pct_used, pct_free controlfile to trace', and then finding
Without 'segment management automatic' these control whether the relevant trace file in
blocks are on the free list; blocks go off the free list when $ORACLE_BASE/admin/$ORACLE_SID/udump.
they have <= pct_free free space, so that rows can expand
without being split across blocks; rows go back on the free v$controlfile()
list when they have <= pct_used used space, so that there is gv$controlfile()
likely to be enough space for a new row. Segment management
automatic (which we use on new customers?) does something
with statistics and bitmaps, to simulate and tune this
process. rollback/undo
+dba_segments()
Old values of database blocks
So Oracle can produce a consistent view
from a point in the past, and/or
rollback a transaction which fails.
Might be organised as multiple segments
with segment properties.
This is the old way; the segment
properties control the amount of space
allocated to each transaction.
Cluster Table Might be called an 'undo tablespace'
Oracle choses the allocation of undo
A named collection of rows Index space to transactions.
A named collection of rows
But they are rows from several tables. Not necessarily in a single segment. dba_undo_extents()
You use clusters to keep the rows from Can be partitioned across multiple A named method of turning
several tables (which are probably segments, based on ranges of the key values into row locations
parents and children w.r.t some foreign primary key. We do this with (our Oracle supports three types; hash (but
keys) together on disk. Clusters are customer) statistics and CDRs, so that that is significantly about the layout
part of the arrangments for using we can delete old data by dropping of rows in a table), bitmap (but that
hashing. partitions, rather than by deleting only really works for data warehouses)
rows. and b-(whatever)-tree. We use b-tree.
dba_tables() dba_indexes()
dba_tab_columns() dba_ind_columns()
 View

Views appear to contain data

... like tables. So they can be
queried, permissions can be granted,
inserts/updates/deletes may be
possible, and synonyms can refer to
them. Index-Organised Table
There is no segment.
Instead, the data comes from some A named method of turning
tables that are referred to by a query, Column key values into row locations
which is part of the definition of the Oracle supports three types; hash (but
view. A named collection of values that is significantly about the layout
Inserts may be restricted. ... being a vertical slice through a table. Has a data type, and of rows in a table), bitmap (but that
If the view is defined 'with check often a size. only really works for data warehouses)
option', then you can't insert anything and b-(whatever)-tree. We use b-tree.
Some set of columns is the primary key
that will later not show in the view. ... all components of which must be non-null, and the combined set of dba_indexes()
values of which must be unique in the table. dba_ind_columns()
dba_views() Creating a primary key usually implies creating an index of the same
name, but this may be able to be avoided.
Tables and their indexes are usually in different tablespaces; the
Trigger original rationalle was a mixture of:
- traffic on the indexes will cause fragmentation of the table -
voided by uniform extent sizes
Fires when you do DML - I/O on the indexes would degrade I/O on the table - voided by disk
.. ie insert/update/delete. arrays and stripe mirroring, which randomises the I/O
The database runs some code which can
do arbitrary things. Unique keys are similar
.. but their components can be null, and foreign keys refer to the
On a table: primary key by default.
... the trigger can query and change
data, but not always the data whose
change fired the trigger.
On Insert & Update, the trigger can
modify the new values.
On a view:
... views with joins are not normally
able to receive DML; this is called a
non-updateable view.
An instead-of trigger can intercept the
DML and do something else which, by
convention, should change the view
contents as if the DML had happened. About DBA_%, v$_% and gv$_%

dba_triggers()
All DBA_ views have ALL_ and USER_ equivalents
These are the 'data dictionary views'.
To see DBA_%, you have to be a DBA. It will show you
everything of that kind in the ?instance or ?database. It
is often hard to work out what the primary key (or moral
equivalent) is.
The ALL_% view will show you everything that you have
permission to access.
Constraint The USER_% view will show you only things in the schema you
are connected to.
Materialised View
A named rule limiting some set of values. Many V$_ views have GV$ equivalents
Might apply to one column, one row at a time. These are the 'dynamic performance tables'.
Appears to contain data eg a not-null constraint, or a check constraint, which says that the The v$ version shows you thinks in your current instance.
... like a view. field describing someone's gender must contain 'M' or 'F' or '?' The GV$ version has INST_ID as part of the (equivalent of
a) primary key, and shows you the same kind of things
Does not own the data Might apply to several columns, one row at a time. across all instances.
.. which is defined by a query, like a More sophisticated versions of check constraints.
view. It is possible to get lists of views
Might apply to one or more columns, across one table. They do not look exactly like the published names. Look
Does store the data eg primary key or unique key constraints. for a view whose name you know, using like%; eg replace
... like a table. TABLE in the following with the text you know of the view
Stores a copy of the view. If there is Might apply across tables.
eg a foreign key, which says that, for every row in this table, for name:
a 'materialised view log' on the select view_name from dba_views where view_name like
underlying table, to detect changes, the values in this collection of columns, those values must exist in
the corresponding collection of columns in the other table. The '%TABLE%';
and the defining query is simple and then tidy it up into the standard form.
enough, then the materialised view may columns in the other table must be a primary or unique key.
be able to be updated row-by-row Internally, Oracle uses the key name (r_constraint_name), not the Described in the manual called 'REFERENCE'
(incremental refresh) rather than all column names, to identify the other columns. dba_views()
at once (complete refresh).
dba_constraints()
dba_mviews() dba_cons_columns()
and lots more()
 Redo log
Instance
A list of changes
So that Oracle can keep its promises to The smallest thing that starts up or shuts down
remember users' changes. One per database, unless you're doing
RAC, in which case one per database
Maintained per-instance server.
... but in RAC after failure, one
instance will look at the failed The smallest thing that does recovery after a crash
instance's redo log and the .. because it is the thing that starts
corresponding undo, in order to finish Parameter file up after a crash, and that is when
committed transactions and roll back recovery happens.
uncommitted transactions. A human-readable file of parameters
Normally lives in v$instance()
v$instance() $ORACLE_BASE/admin/$ORACLE_SID/pfile. gv$instance()
gv$instance() Must be linked into $ORACLE_HOME/dbs;
not stored there because it continues
to apply when Oracle is upgraded, which
changes $ORACLE_HOME.
May refer to a non-human-readable, shared file
... the spfile. The spfile is dumped
into SUPPORT_AUDIT.
Guard process
v$instance()
gv$instance()
This is actually a unix concept
It is used for /usr/bin/login and
/usr/bin/su, among other processes.
Have a set-uid binary,
whose owner owns the rights you need
Session note the -s in the following:
Shadow process oracle@usms:/home/oracle> ls -l
/usr/bin/su
This is actually a unix concept -r-sr-xr-x 1 root sys
It is used for /usr/bin/login and This is actually a unix concept 25748 May 24 2005 /usr/bin/su
/usr/bin/su, among other processes. It is used for /usr/bin/login and oracle@usms:/home/oracle> (cd
Have a set-uid binary, /usr/bin/su, among other processes. $ORACLE_HOME/bin; ls -l oracle)
whose owner owns the rights you need Have a set-uid binary, -rwsr-s--x 1 oracle dba
note the -s in the following: whose owner owns the rights you need 112316088 Aug 1 11:12 oracle
oracle@usms:/home/oracle> ls -l note the -s in the following: Access the rights through the guard program
/usr/bin/su oracle@usms:/home/oracle> ls -l
-r-sr-xr-x 1 root sys /usr/bin/su
25748 May 24 2005 /usr/bin/su -r-sr-xr-x 1 root sys
oracle@usms:/home/oracle> (cd 25748 May 24 2005 /usr/bin/su
$ORACLE_HOME/bin; ls -l oracle) oracle@usms:/home/oracle> (cd
-rwsr-s--x 1 oracle dba $ORACLE_HOME/bin; ls -l oracle)
112316088 Aug 1 11:12 oracle -rwsr-s--x 1 oracle dba
Access the rights through the guard program 112316088 Aug 1 11:12 oracle
v$session() Access the rights through the guard program Background process
v$session_wait()
v$session_longops() Starts with the Oracle instance
It is used for /usr/bin/login and /usr/bin/su, among other processes.
Configured with instance parameters
Systematically named
PL/SQL eg for instance SMF:
oracle@usms:/home/oracle> ps -fu oracle #edited
UID PID PPID C STIME TTY TIME CMD
Database programming language oracle 13152 1 0 Aug 05 ? 14:24 ora_smon_SMF
Described in 'Application Developer's Guide' and 'PL/SQL oracle 13138 1 0 Aug 05 ? 181:59 ora_pmon_SMF
Guide and Reference'. Sequence
oracle 13148 1 1 Aug 05 ? 2463:57 ora_lgwr_SMF
Can do anything in the database that the permisions in its oracle 13146 1 0 Aug 05 ? 209:24 ora_dbw1_SMF
current context allows; this might be the session who runs Produces unique numbers oracle 13156 1 0 Aug 05 ? 176:57 ora_cjq0_SMF
it, or the session which defined it. Has more concurrency than locking a oracle 14171 1 0 Aug 05 ? 233:11 ora_j000_SMF
Not guaranteed to have a bounded cost. table for every number. oracle 13154 1 0 Aug 05 ? 0:08 ora_reco_SMF
Most PL/SQL performance problems resolve into performance Loses numbers in instance failures. oracle 13162 1 0 Aug 05 ? 0:20 ora_d000_SMF
problems with the SQL that the PL/SQL is running. oracle 13164 1 0 Aug 05 ? 1:18 ora_d001_SMF
Used for:
Various kinds of code .. generating unique primary keys, in oracle 13182 1 0 Aug 05 ? 1:10 ora_s000_SMF
Procedure - does something triggers. oracle 13184 1 0 Aug 05 ? 0:35 ora_s001_SMF
Function - does something and returns a value; can be oracle 13150 1 0 Aug 05 ? 126:42 ora_ckpt_SMF
selected Used by: oracle 13142 1 0 Aug 05 ? 8:38 ora_mman_SMF
Trigger - does something when data changes select <sequence>.nextval from dual; oracle 13158 1 0 Aug 05 ? 11:05 ora_mmon_SMF
Package - various procedures and functions, and also select <sequence>.currval from dual; oracle 13160 1 0 Aug 05 ? 8:00 ora_mmnl_SMF
preseves per-session state between calls oracle 13140 1 0 Aug 05 ? 7:12 ora_psp0_SMF
dba_sequences()
Does something useful
dba_triggers() PMON = detecting connection failure & rolling back transactiions
dba_source (line by line)() SMON = zillions of jobs including detecting instance failure
DBW# = writing database blocks
lots more, described in the Concepts manual