wiki:OracleTips

dbconsole reference: ticket:477#comment:61

in progress: reviewing Dan's logs of #oracle on FreeNode

Oracle Documentation

Nice search service: Search and Download Oracle Database, Application Server, and Collaboration Suite Documentation

spotter: RJarett1 in #oracle on FreeNode.

Star Schema Optimzations etc.

I hope to look into these soon...

Performance: Star Transformations

  • Star Transformation by subrata.dass Aug 2011, which cites
    • Star Transformation in Siebel Analytics (OBIEE) from 2009

Performance: Outlines

Performance: Table Statistics

Performance: Memory

Performance: Indexes

B-Tree Vs Bitmap http://www.oracle.com/technetwork/articles/sharma-indexes-093638.html

How B-tree indexes work http://mattfleming.com/node/192

Oracle Performance Tuning with Indexes http://www.dba-oracle.com/art_9i_indexing.htm

Performance: Generate Automatic Workload Repository(AWR) Report

Backups

Restart Database

Note: Be sure to have a second set of eyes in a production environment

  1. Su to oracle
    sudo -E su -s /bin/bash oracle
    
  2. Get SID name of server ps -ef | grep smon
    • ie. ora_smon_dbname dbname is the SID
  3. Export SID and call sqlplus internal to connect (note full path to sqlplus needed if $PATH not set correctly)
    export ORACLE_SID=dbname;$ORACLE_HOME/bin/sqlplus / as sysdba
    
  4. Verify you've got the correct database:
    SQL> select * from global_name;
    
    GLOBAL_NAME
    --------------------------------------------------------------------------------
    DBNAME
    
  5. Shutdown immediate
    SQL> shutdown immediate
    
  6. If it hangs longer then 5 min do a shutdown abort and then startup
    Ctrl-C
    SQL> shutdown immediate
    ^CORA-01013: user requested cancel of current operation
    
    SQL> shutdown abort
    ORACLE instance shut down.
    SQL> startup
    ORACLE instance started.
    
    Total System Global Area 1174405120 bytes
    Fixed Size                  2083464 bytes
    Variable Size             721421688 bytes
    Database Buffers          436207616 bytes
    Redo Buffers               14692352 bytes
    Database mounted.
    Database opened.
    

Start DBConsole

# As Oracle user
/opt/oracle/product/11.2.0.3/bin> ./emctl start dbconsole

Starting the Listener

# As Oracle user
/opt/oracle/product/11.2.0.3/bin> lsnrctl start

Datapump

Security

Auditing

Verify Auditing Setup

Configuring and Administrating Auditing

Auditing with 10gR2

sqlplus

What is this / thing, anyhow?

Ah... sqlplus syntax. / (slash) in 10.2 SQL*Plus docs.

PL/SQL: When and How

We mostly stick to plain SQL and avoid PL/SQL, but for when it's needed: Oracle® Database PL/SQL User's Guide and Reference 10g Release 2 (10.2)

Oracle SQL Developer

nifty. java app. easy enough to find/download todo: md5 for audit purposes here

oracle sql developer

Installing Oracle SQL Developer in Ubuntu April 8, 2009

see also UsingVersionControl re SQL formatting conventions

Date Display

alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI' 

spotter: Russ, in attachment:RussEpicScratchpadOct192010.sql:wiki:HospitalEpicSource

If you want to make a particular date format the default, to to Tools->Preferences->Database->NLS and change the "Date Format" field. And example formatting that shows year, month, day, hour, minute, second would be: YYYY-MM-DD HH24:MI:SS.

Source from this: article describing default date formatting.

Performance: Using Explain Plan

First, prefix your query by...

explain plan for
select ...

then, to show the result:

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

source: Using Explain Plan

Interpreting Explain Plan seems helpful.

Uncommitted Sessions/Transactions

If an insert/update waiting seems to be waiting on something, this might show what it's waiting on. From Sep 2009 How to query uncommited transactions thread:

select t.start_time,s.sid,s.serial#,s.username,s.status,s.schemaname,
s.osuser,s.process,s.machine,s.terminal,s.program,s.module,to_char(s.logon_time,'DD/MON/YY HH24:MI:SS') logon_time
from v$transaction t, v$session s
where s.saddr = t.ses_addr
order by start_time;

To find/kill a runaway session/process

It's better to kill a process via the Oracle administration console or with the following "alter session" command taken from oracle-base.com.

ALTER SYSTEM KILL SESSION 'sid,serial#';

sid and serial# can be determined by clicking on Tools->Monitor Sessions... in SQL developer. Then, determine the sid / serial based on the username / machine.

Toward Oracle Client on Ubuntu

see PythonVirtualEnvironment#PythonandOracle

Memory

Write Performance Tuning

Arvinder was researching NOLOGGING and write performance; a -- Jan 2011 post says that even we are on no archivelog mode, redo is generated, which seems confusing.

Other interesting articles:

  1. Managing Tables: Logging versus Nologging September 13, 2006 By Steve Callan
  1. Chapter 14 Managing Tables in the Oracle 10g docs
  1. Getting To The Bottom Of NOLOGGING, NOARCHIVELOG and Standby Databases May 28th, 2005

Renaming a database: possible, but not straightforward

See May 2010 serverfault discussion.

Nearby: end-of-line issues in shell scripting

If you see

Linux shell scripting: bad interpreter: No such file or directory 

Try this clue from Linux shell scripting: bad interpreter: No such file or directory Mon, Sep 15. 2008.

Last modified 3 years ago Last modified on 06/24/14 15:51:07