The following came off a list server from Todd Boss, modified by Teresa Larson. Ed --------------------------------------------------------------------- I compiled the following "oracle cheatsheet" when working in an Oracle environment for the first time (coming to it as a Sybase person). I'd welcome comments/corrections... boss Oracle Knowledge =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= On NT 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. =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 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. - When you startup, Oracle reads a "Parameter file" which i can't find. [ $ORACLE_SID = $DSQUERY to point to the instance name you want to access. There are 2 files that Oracle reads at startup: init${ORACLE_SID}.ora and config${ORACLE_SID}.ora ] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 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. =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 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 t hough to have autocommit on. however, if i drop an object its immediately commi tted. Weird. AND, if i log out, it automatically commits anything pending. =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 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?) [ That's my guess. :-) ] - Referencing an object schema table.column@dblink == server.database.table.owner.column (dblink == database link, akin to server. schema == owner?) [ Yes, basically, schema == owner. All objects owned by user "finance" are referred to as the finance schema. You can think of schemas as similar to databases in that they are a collection of objects (tables) that are related. a DBLINK is another object like a table and is defined for a single table in the remote instance/server. When it's created, you associate a username and password with it. Also, query optimization is done at the receiving instance (or at least it was when I worked with it a couple years back), so the remote instance will return ALL records from a table and then the WHERE clause will be applied by the local/receiving instance. Could be a bummer.] - 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. [ Also, times are only stored to the second -- no miliseconds. Not a big deal for many apps, but scientific applications frequently care. :-) ] - sql*loader == bcp; Oracle's data loading tool. Very efficient at getting data IN, but not so much getting it out. - 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. Can "create table as select.." == select into table creation. [ Try not to think of the various SIZE and MAX parameters as options, even though they are. If you don't define values, then Oracle creates objects with the defaults set up for the tablespace, which can cause performance problems or waste space.] - 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 - decode: a simulated if/then/else. Has for/loop and while/loop constructs. When/then construct for errorhandling. - 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) =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Outstanding questions - How do you get data OUT of Oracle? Can Sql*Loader do that? You can spool file output in Sql*plus...but not exactly what we're looking for. Sql*Loader says it can do this, but no examples and no documentation. Oracle guru suggests cut-n-paste out of TOAD. Blake says exp. [ Nope. Spooling the output from Sql*Plus is it. SQL*Loader is just what its name implies -- a loading utility. :-( ] - learn more about the Oracle directory structure; where do important files reside? where does the server start from? [Start looking at $ORACLE_HOME and $ORACLE_BASE. Then check out the $HOME directory for the "oracle" user -- it is frequently different than $ORACLE_HOME and $ORACLE_BASE. ] - what is a key preserved table? I couldn't update through a view b/c one of the tables wasn't 'key preserved.' - How do you tell which query plan, which index is getting used? [My biggest problem in an Oracle environment are the backups. Oracle doesn't have a native backup utility and so still depends on operating system commands to backup up the data files one at a time. Very cumbersome. While the backup is happening, Oracle can't write to the data files, so data changes are getting written to the log files in datablock chunks instead of rows, so you have to size your log files to handle that if you want online (hot) backups. Another concern is that Oracle never deletes from indexes, so you have to rebuild your indexes periodically to reclaim space from deletes.] Hope it helps Teresa Larson