Oracle Admin Tips

Routine Maintenance

    * Notes on killing Oracle sessions: http://www.oracle-base.com/articles/misc/KillingOracleSessions.php
      SELECT sid,serial#,osuser,program,username FROM v$session;
      ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
    * Finding active sessions:
      SELECT SID, Serial#, UserName?, Status, SchemaName?, Logon_Time? FROM V$Session
      WHERE Status='ACTIVE' AND UserName? IS NOT NULL
    * Important tables: v$process, v$session,
    * Moving an Oracle database: http://www.dba-oracle.com/oracle_tips_db_copy.htm
    * Deleting everything for a user: http://jmatrix.net/dao/case/case.jsp?case=7F000001-E22F2B-10CCCCB2924-862

Performance Tuning

    * SQL Trace: http://www.orafaq.com/wiki/SQL_Trace, http://www.dba-oracle.com/t_10046_tracing_events.htm, http://www.adp-gmbh.ch/ora/misc/trace_file_format.html
    * View process configuration: "show parameter processes"
    * Service Side Connection Pooling: http://blogs.oracle.com/opal/2007/10/connection_pooling_in_php_avai.html
    * Configuring shared servers: http://download.oracle.com/docs/cd/B10501_01/network.920/a96580/mts.htm
    * Good article on Oracle "explain plan" and how to tune indexes: http://www.adp-gmbh.ch/ora/explainplan.html
    * Documentation for "explain plan": http://www.csee.umbc.edu/help/oracle8/server.815/a67775/ch13_exp.htm
    * Oracle 11g Diag/Tuning Workshop: http://download.oracle.com/otndocs/technology/products/database/oracle11g/upgrade/upgrade11gr1_workshop_part2.pdf

Storage/Backup/Recovery

    * RMAN user's guide: http://download.oracle.com/docs/cd/B10501_01/server.920/a96566/toc.htm
    * Deleting backups: delete backup completed before "sysdate-2"
    * "backup archivelog all delete input", well, self-explanatory.
    * "backup incremental level 0 database plus archivelog delete input". Backup database and log at the same time.
    * Finding the largest tables: http://www.dba-oracle.com/t_script_oracle_table_size.htm
    * Space and Undo management: http://www.oracle.com/technology/products/manageability/database/pdf/ow04/1241_minhas_pres.pdf
    * Reclaim undo space: 1) create undo tablespace X datafile '/somewhere/x01.dbf' size 100m autoextend on next 100m maxsize 32000m; 2) alter system set undo_tablespace=X; 3) drop tablespace UNDO1 including contents and datafiles;

Data Migration

    * Import/Export: http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm

General Information

    * Oracle 11g documentation, hosted on an internal server: http://filer01.webexlabs.com/oracle11g/
    * Notes on changing the name of an Oracle database: http://www.adp-gmbh.ch/ora/admin/rename_db.html
    * PL/SQL reference
    * Oracle performance benchmark software from Quest Software: http://www.quest.com/benchmark-factory/
    * PL/SQL date format: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924
    * Recycle bin: http://www.orafaq.com/node/968
    * Start on boot: http://www.symmetricwebsites.com/articles/oracle/installOracle11gOnRHEL4linux.php#four