Oracle Knowledge (as compared to Sybase)

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
On Windows NT/Windows 2000 specifically
- regedit, then HKEY_LOCAL_MACHINE\software\Oracle for a list of all
environment vars set for Oracle
- Ctrl-panel -> Services: see what services are running 
(ps -fe | grep oracle)
- Oracle Enterprise Manager == SSM or another 3rd party tool used to
graphically manage the database.  Its an administrator tool.	

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Server Environment
- $ORACLE_HOME: directory where Oracle s/w is located. (Eqiv. to $SYBASE)
- $ORACLE_HOME/database: start/stop scripts for database
($SYBASE/RUN scripts)
- Oracle typically stores all data and log devices in flat files
- Oracle stores the commands that created/loaded a table in "control
files."  These can get large and are sometimes archived.
- $ORACLE_SID = $DSQUERY  to point to the instance name you want to access.

- When you startup, Oracle reads two parameter files: 
init${ORACLE_SID}.ora and config${ORACLE_SID}.ora 

Default port Oracle listens on: 1521

---
Client Environment

$ORACLE_HOME/network/admin/tnsnames.ora: definition file for Schema name,
IP address and Listener port.  == interfaces file in Sybase

tnsnames.ora: generated by Net8 Assistant (Oracle install prgm).  Similar
to running sybinit or sqledit to modify the interfaces/sql.ini files in Sybase.

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Terminology versus Sybase

- Database == SQL Server; Datafiles, redo logs, control files
- Instance == SQL server: the combo of the SGA and the Oracle processes.
Once started, the Instance is associated w/ a Database (mounting).
- Schema = the collection of objects that are readable by the user
(somewhat akin to object owner (dbo e.g.) in that objects are associated
with a schema, which is directed by the user login)
- Table Space == database (physically located in datafiles).
- Redo Logs == Transaction Logs.  Oracle records all changes to a
memory buffer (redo log buffer RLB).  Has a job called LGWR that 
periodically writes them to the Rollback disk segments.  Then a
third optional process called the Archiver (ARCH) archives the redo 
log info to a tape/backup medium.
- DBWR == Checkpoint process; writes modified data in memory to
disk asynchronously; manages the Database Buffer Cache
- Database buffer cache (DBC) == Default data cache: basic memory 
containing data read from disk; has dirty and clean pages, uses 
LRU queue strategy like Sybase.
- Rollback Segments == Physical Sybase log segments: database devices

Things that are just integrated into the Sybase Server that are
given seperate terminology in Oracle:
- SGA: System global Area: Shared memory pool, database buffer cache, 
and redo log buffer.
- PGA: Program Global Area: non shared memory area allocated to each 
user connection
- PMON, SMON: Process and System monitor: they are daemons that clean 
up behind users, reclaiming resources.
- RECO: Recoverer process 
- LCKN: Lock process: manages locks inter instance

- Table == table, column == column, View == view, Index == Index
- Blocks, extents, segments = same

- Sequence: a system table that stores a single unique number across
a database; like having a master identity value for a whole Server, not
just one particular table.
- Synonym: a public alias to a object; prevents the need to have to
type schema.table all the time.

- sys and system are akin to sa and dbo in Sybase.  The "dba" account is
like the dbo of a tablespace.

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Datatypes

Char(x): max 255, it blank pads shorter entries up to defined column size
Varchar2(x): max 2000: much larger than Sybase's max of 255
Number(s,p) == sybase's numeric(s,p)
Date = Sybase's datetime [sysdate = getdate() to get current date/time]
Long: variable length data upto 2gb in size; only one per table.  Can't
be put in RI constraints or indexes.  Kinda
like a Sybase text field, but less functional (Sybase has no text size
limit and can have more than one per table)
Raw = Sybase's binary/image datatype.
ROWNUM: store's a Row's address.  No sybase system equivalent < 11.9.2
(however its not static; its internal only)

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
SQL*PLUS

- sql*plus == isql: edit calls up a Notepad-ish editor that will
pass the SQL you write back to the sql*Plus program.

- while retreiving, can define column masks, where to break in displays,
compute values on the fly.
- lots of set commands: reads from $ORACLE_HOME/dbs/login.sql for
preset set commands (or could be $SQLPATH variable).  type "show all"
for a list of all current settings.  Type "show var" to show the
setting for a variable

- can save to a file, get (retreive) a file, and spool (redirect)
output to a file.  spool filename, spool off.

- l[ist] == history in sqsh; can recall previous commands by line number.
* == last command, use numbers otherwise.
- set pause on/off == piping output through more in sqsh.
- set null "NULL" to have null values appear as NULL instead of blanks.

- login.sql (in orant/DBS): .sqshrc file; can preset values

- / re-executes the last known command (like hitting go again in sqsh)
- If you edit something, it will cut off your last char unless you put
in a line at the end consisting of "/" by itself.

- @filename.ext runs stuff just as if you did isql -i filename.ext

- Sql*plus defaults to autocommit off (where as Sybase defaults on; once
you hit "go" the statement is committed to the database).   There is an option though to have autocommit on.  
* Any DDL is autocommitted, despite the setting (truncate, drops, adds)
* Upon logout, automatically commits anything pending.  

- := is the assignment operator (= in Sybase).

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
PL/SQL Info/Commands & Equivalents

- Strings must single quoted like in Informix; double quotes implies
a column-name
- SQL commands end with semicolon, not go (like Informix)
- column names are typically case insensitive; system data is mostly 
uppercase (a mainframe habit?)
- Referencing an object
schema table.column@dblink == server.database.table.owner.column
(dblink == database link, akin to server.  The dblink defines a 
single table in a remote instance/server. schema == owner).
(All objects owned by user xyz are called the "xyz schema")

- You can have defined Synonyms (Aliases) in Oracle
- Metadata: you can store comments on a table and a column right in the
database.  VERY nice.  Imports to the comment field in Erwin too.

- Working w/ dates a bit clunky; Oracle stores the date and time
together like sybase but defaults to only show date.  To get the 
time, you must do something like to_char('date',mm-dd-yy hh:mm:ss)
to get the time out.  Plus, when inserting any date value not in the 
exact form 01-jan-99 you'll have to use a to_date reverse engineered
function b/c Oracle (unlike sybase) cannot interpret and convert
dates on the fly.  Plus the time only goes to the second; if you 
want milliseconds you have to design your own datatype.

- sql*loader/sqlldr == bcp; Oracle's data loading tool.  Very efficient
at getting data IN, but not so much getting it out.  Control files are
the equivalent of format files.  sqlldr only can load *empty* tables;
can't load up to a table w/ existing rows.

- export/import: akin to bcp in binary mode; its Oracle-only format

- create (or replace) [object] feature: nice.
- create table statement has many different options compared to Sybase.
You can cap the size of a table w/ Maxextents option (though its
really not recommended; the defaults are inherited from the tablespace
definition, and specifying others can cause performance issues/waste space).
Can "create table as select.." == select into table creation.
- there is no select into to dynamically create a table.  You'll
have to grab the ddl.  which requires a 3rd party tool/stored proc.
- alter table modify column feature (though you can't change datatype
or shrink the size unless its empty).  You also cannot drop the
column this way in 8.0
- select into has completely different meaning in Oracle.  Its used to
assign values to variables, NOT create tables.
- decode: a simulated if/then/else.  Has for/loop and while/loop
constructs.  When/then construct for errorhandling.  Works in select statements,
NOT in procedures (you cannot call it as a straight forward function)
- dynamic sql: exists!  very straightforward.

- grant, drop table, truncate, insert, update, delete all work the same 
more or less; different options of course.
- drop table cascade constraints: very nice way to not worry about
the foreign key constraints on a table when dropping it.

- Create table; you specify space allocation at create table time.
As you extend, Oracle will allocate more space, but this leads to 
extreme fragmentation.

- desc[ribe] == sp_help [tablename]
- show user == select user_name()  
- password == sp_password (pre Oracle 8.0; alter user)
- alter table a rename to b OR rename a to b == sp_rename.
- select * from v$session == sp_who
- select * from user_view == sp_helpcode [view_name]
- prompt "text" == print "text" (shows text on the screen)

- Triggers: :new == inserted, :old = deleted.  Triggers are far more
flexible than in Sybase.  Before, after, instead of triggers, additional
clauses built in.  



=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Complaints about Oracle

- No easy way to get data OUT of Oracle in non-binary format.  Must
write a program to select out columns with delimiters.  
- Oracle compilation errors useless.  No help at all figuring out
syntactical errors.