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