My personal quick reference for Oracle

Working with Oracle is traumatic enough without having to remember things about it too.

Clearing caches

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;

http://www.dba-oracle.com/t_flush_buffer_cache.htm

Shutting down

CONNECT SYSTEM@instance/pwd AS SYSDBA
SHUTDOWN NORMAL;
then wait for everyone to disconnect.
Or, wait for active transactions to finish by
SHUTDOWN TRANSACTIONAL;
Or, rollback active transactions and disconnect clients
SHUTDOWN IMMEDIATE;
Or if that fails...
SHUTDOWN ABORT;

http://docs.oracle.com/cd/B10501_01/server.920/a96521/start.htm#6398

Discover the oracle instances’ parameter configuration

    column c1 heading 'Name' format a20;
    column c2 heading 'Value' format a20;
    SELECT NAME c1, DISPLAY_VALUE c2 FROM V$PARAMETER;

Explaining Plans

EXPLAIN PLAN FOR <mysqlstatement>;
then
SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
or
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

How much free space in tablespaces?

SELECT /* + RULE */  df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+)  = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+)  = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 1 DESC;
SELECT * FROM DBA_TEMP_FREE_SPACE;

Rebuilding indexes

http://psoug.org/reference/dbms_index_utl.html

Revert to an older version of the query optimizer

ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.2.0.4';

Discover what previous versions you can use by..

SELECT value FROM v$parameter_valid_values WHERE name = 'optimizer_features_enable';

Shrinking the temporary tablespace

First, remove any locks by performing a shutdown (follow the steps above for shutting down).

SHUTDOWN NORMAL;

Find what the temporary file is called internally by examining the output from

SELECT * FROM V$TABLESPACE;

Lets imagine it’s TEMP.DBF as below.

Shrink it

ALTER DATABASE TEMPFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\TEMP.DBF' RESIZE 1000M;

or, to make a restricted size tempfile

CREATE TEMPORARY TABLESPACE temp2 TEMPFILE 'c:\oraclexe\app\oracle\oradata\xe\temp2.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE 1100M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

You might have to alter the default for specific users too.

http://dbafix.blogspot.co.uk/2010/08/how-to-drop-and-recreate-temp.html

Slow oracle queries using cartesian joins

My experience of Oracle 11gr1 was never a pleasant one. But I did encounter a situation where the query optimizer would chose a terrible cartesian join and bloat temp DB to the eyeballs. This was fixed in 11gr2, as far as I can tell.

create or replace
TRIGGER moodsessions AFTER LOGON ON SCHEMA
BEGIN
execute immediate 'ALTER SESSION SET "_optimizer_cartesian_enabled"=false';
END;
/

Hints in SQL Queries

SELECT /* +RULE*/ blah FROM blahTable; // rule based optimizations
SELECT /* +ORDERED*/ blah FROM blahTable; // joins should be done in the order I say
SELECT /* +OPT_PARAM('_optimizer_cartesian_enabled','false') */ blah FROM blahTable; // disable cartesian joins
SELECT /* +OPT_PARAM('optimizer_search_limit',2) */ blah FROM blahTable; // do not test 4 joins, just test 2.
SELECT /* NO_QUERY_TRANSFORMATION */ blah FROM blahTable;

http://www.dba-oracle.com/art_otn_cbo_p1.htm

Meta data

You can substitute the prefix user_ for all_ to restrict or expand the results in or out of the current user’s objects.

SELECT table_name,constraint_name FROM user_constraints;

http://ss64.com/orad/USER_CONSTRAINTS.html

SELECT trigger_name FROM user_triggers;

http://ss64.com/orad/USER_TRIGGERS.html

SELECT table_name, index_name from user_indexes;

http://ss64.com/orad/USER_INDEXES.html

SELECT table_name,column_name,nullable FROM user_tab_cols;

http://ss64.com/orad/USER_TAB_COLUMNS.html

Are there invalid triggers in my database?

SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE='TRIGGER' AND STATUS='INVALID'

Compiling triggers

A subtlety in Oracle is that triggers are not compiled when REPLACE is used. So compiling them before they fail on first use is handy.

ALTER TRIGGER <SCHEMANAME>.<TRIGGERNAME> COMPILE;

IF EXISTS equivalent in Oracle

IF EXISTS is really handy in SQL Server, but Oracle is behind the times here. What is a one liner in SQLServer turns into this:

DECLARE
L_EXIST NUMBER;
BEGIN  
 SELECT COUNT(x) INTO L_EXIST FROM /* MYQUERY*/;  

 IF L_EXIST>0 THEN  
  /* DO SOMETHING */  
 END IF;
END;

Unlocking an account

alter USER myaccountname ACCOUNT UNLOCK;

Preventing password expiry

You can discover the profile which needs altering by running…

select username,profile,lock_date,expiry_date from DBA_USERS where username='myusername';

Then… (assuming the profile is DEFAULT)

alter profile DEFAULT limit password_life_time UNLIMITED;

ORA-06550, PLS-00103 Encountered the symbol <blah> when expecting one of the following

Oracle is a pain. When there’s no obvious typo like a missing quote or just end-user stupidity in syntax application you might experience this cryptic error.

One of my common pitfalls is trying to execute schema level operations inside some normal SQL logic branch. E.g. If index B exists, drop it.

Simply put, DDL likes to run without other SQL statements around. This can happen when sending SQL via ODBC just as easily as it can within the PL/SQL window.

http://arjudba.blogspot.co.uk/2009/02/how-to-run-ddl-statements-within-plsql.html

Advertisements

One thought on “My personal quick reference for Oracle

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s