Oracle

After installing Oracle XE 10.2.0 on Ubuntu 8.4, check that the permission of the socket files in `/var/tmp/.oracle and, if needed, correct them to oracle:dba. If the permissions are wrong the listener fails to start.

To install Oracle XE 11gR2 on Ubuntu 11.04 x86_64 look at the Oracle Forums

Connect with:

$ sqlplus username/password@SID
$ sqlplus SYS@SID AS SYSDBA

def VAR = value      Defines a variable (referenced as &VAR)
def VAR              Echoes a variable
@SCRIPT.SQL          Sources (run) script
start SCRIPT.SQL     Sources (run) script
SPOOL logfile.txt    Logs output to logfile
SPOOL OFF            Stop logging
SET TERMOUT OFF      Do not log STARTed commands to stdout
SET ECHO ON          Log statements AND commands
EXIT                 Exit SQL*Plus
SET HEADING OFF      Don't print column headings
SET VERIFY OFF       Don't display the replacing of variables in commands
SET FEEDBACK OFF     Don't display the number of results returned
SET PAGESIZE 0       Suppress all headings, page breaks, titles

Recommended for running SQL script:

SET ECHO ON

Look in the Oracle home for a file named tnsnames.ora. It contains the SID, the IPs of the DB server(s) etc. It should be in $ORACLE_HOME/network/admin/tnsnames.ora

See the SQL*Plus FAQ

rocket99 contains several sample command, snippets and documentation.

With a “dummy” select:

  SELECT 1 FROM DUAL

  SELECT blah FROM footbl WHERE ROWNUM <= N

  CREATE TABLE mytable (id INTEGER, junk VARCHAR2(100));
  INSERT INTO mytable SELECT rownum, 'RANDOMJUNKSTRING'
    FROM dual CONNECT BY LEVEL <= 100000;

If you get a ORA-01843: not a valid month there's probably a mismatch in the language used by the DB and in the DMLs.

See the language parameters with:

SELECT * FROM nls_session_parameters;

…and change the wrong ones with:

ALTER session SET PARAM='VALUE';
-- Sample:
ALTER session SET NLS_DATE_LANGUAGE='ITALIAN';
ALTER session SET NLS_DATE_FORMAT='DD-MON-RR';

ALTER tablespace <TBSPC> offline
-- move file with OS commands
ALTER tablespace <TBSPC> RENAME datafile '/u01/oracle/U1/data01.dbf' TO '/u02/oracle/U1/data04.dbf'
ALTER tablespace <TBSPC> online

CREATE TABLESPACE MYTABLESPACE
  DATAFILE '/some/where/MYTABLESPACE.dbf'
  SIZE 64M AUTOEXTEND ON NEXT 64M MAXSIZE 2048M
  LOGGING
  EXTENT MANAGEMENT LOCAL
  ONLINE
  SEGMENT SPACE MANAGEMENT AUTO;

ALTER user <SCHEMA> DEFAULT tablespace <NEWTBSPC>

ALTER TABLE <SCHEMA.TABLE> MOVE TABLESPACE <NEWTBSPC>
ALTER TABLE <SCHEMA.TABLE> MOVE LOB (<LOBNAME>) STORE AS(TABLESPACE <NEWTBSPC>)
ALTER INDEX <SCHEMA.INDEX> REBUILD TABLESPACE <NEWTBSPC>

SELECT 'ALTER TABLE ' || OWNER || '.'|| TABLE_NAME || ' MOVE TABLESPACE ' || &NEWTBSPC || '
/'
  FROM SYS.ALL_ALL_TABLES
  WHERE TABLESPACE_NAME = 'FHL_TABLE' AND OWNER = &SCHEMA
  ORDER BY TABLE_NAME

SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME ||
    ' MOVE LOB (' || COLUMN_NAME || ') STORE AS(TABLESPACE ' || &NEWTBSPC || ')
/'
    FROM SYS.ALL_LOBS
WHERE TABLESPACE_NAME = 'FHL_TABLE' AND OWNER = &SCHEMA
ORDER BY TABLE_NAME

SELECT 'ALTER INDEX ' || OWNER || '.'|| INDEX_NAME || ' REBUILD TABLESPACE ' ||  &NEWTBSPC || '
/'
  FROM SYS.ALL_INDEXES
  WHERE TABLESPACE_NAME = 'FHL_TABLE' AND OWNER = &SCHEMA
  ORDER BY INDEX_NAME

Find the character set with either:

SELECT * FROM NLS_DATABASE_PARAMETERS;
SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET';

From Unix commandline

exp OLDUSER/PASSWD@SID FILE=database.dmp LOG=export.log FEEDBACK=1000 BUFFER=2000000
imp NEWUSER/PASSWD@SID FILE=database.dmp FROMUSER=OLDUSER TOUSER=NEWUSER LOG=import.log FEEDBACK=1000 BUFFER=2000000

To get help on the parameters use:

exp help=y
imp help=y

The destination tablespace will be the same of the source

Synonyms and grants will refer to the old schema

Note that exdp/impdp are the new recommended tools using DataPump.

You will need a database directory. From 10g R2 there is a default directory, called DATA_PUMP_DIR. Find it with (as SYSDBA):

SELECT directory_path FROM dba_directories WHERE directory_name = 'DATA_PUMP_DIR';

To grant access to this dir to a non-privileged user you can do:

GRANT read, write ON DIRECTORY DATA_PUMP_DIR TO scott;

DBUSER=scott
SID=XE
echo === Exporting ${DBUSER}@${SID}
expdp ${DBUSER}@${SID} DUMPFILE=${DBUSER}.dmp LOGFILE=${DBUSER}.log \
  SCHEMAS=${DBUSER} DIRECTORY=DATA_PUMP_DIR

NEWUSER=scott2
SID=XE
impdp ${NEWUSER}@${SID} DUMPFILE=${DBUSER}.dmp LOGFILE=${DBUSER}-import.log \
  SCHEMAS=${DBUSER} DIRECTORY=DATA_PUMP_DIR \
  REMAP_TABLESPACE=oldtbspc:newtbspc REMAP_TABLESPACE=oldtbspc2:newtbspc2 \
  REMAP_SCHEMA=${DBUSER}:${NEWUSER}

alter database datafile '<full_file_name>' resize <size>M

Usually you'll need to do a REORG to compact the datafile before issuing the command.

-- Number of active sessions
SELECT * FROM v$license
 
-- Session details
SELECT s.sid,s.serial#,s.STATUS,s.osuser,s.username,s.program
FROM v$session s WHERE s.osuser NOT IN ('oracle','SYSTEM') AND s.STATUS = 'INACTIVE'
 
-- Kill a session
ALTER system KILL session '<sid>,<serial#>'
ALTER system KILL session '12,34567'

Kill all sessions and disable an user

-- LUSER = User to kill&lock
-- Run as SYSDBA
 
def LUSER=TESTUSER
 
SET heading off
SET verify off
SET termout off
 
host rm -f killora.tmp
spool killora.tmp
 
SELECT 'ALTER SYSTEM KILL SESSION ''' ||s.sid || ',' || s.serial# || ''' IMMEDIATE;'
 FROM v$session s WHERE s.username = '&LUSER';
 
SELECT 'ALTER USER "&LUSER" ACCOUNT LOCK;' FROM DUAL;
 
spool off
 
SET verify ON
SET termout ON
 
@killora.tmp

As SYS:

SELECT * FROM SYS.DBA_TABLESPACES
SELECT OWNER,TABLE_NAME FROM SYS.DBA_TABLES WHERE OWNER = 'MY_SCHEMA_NAME'
SELECT USERNAME FROM SYS.DBA_USERS

As an unprivileged user:

-- All tablespaces
SELECT * FROM SYS.USER_TABLESPACES
-- All schemas
SELECT USERNAME FROM SYS.ALL_USERS
-- My tables
SELECT TABLE_NAME FROM SYS.USER_TABLES
-- My indexes
SELECT INDEX_NAME FROM SYS.USER_INDEXES
-- My triggers
SELECT TRIGGER_NAME FROM SYS.USER_TRIGGERS

Look in SYS.pending_trans$ , SYS.dba_2pc_pending , SYS.dba_2pc_neighbors

Cfr http://download.oracle.com/docs/cd/E17904_01/web.1111/e13731/thirdpartytx.htm#i1089081

To prepare the database for XA, perform these steps:

• Log on to sqlplus as system user, for example, sqlplus sys/passwd@DBALIAS
• Execute the following command:

  @xaview.sql

The xaview.sql script resides in the $ORACLE_HOME/rdbms/admin directory

• Grant the following permissions:

  grant select on v$xatrans$ to public (or <user>);
  grant select on pending_trans$ to public;
  grant select on dba_2pc_pending to public;
  grant select on dba_pending_transactions to public;

• when using the Oracle Thin driver 10.1.0.3 or later:

  grant execute on dbms_system to <user>;

If the above steps are not performed on the database server, normal XA database queries and updates may work fine. However, when the Application Server Transaction Manager performs recovery on a re-boot after a crash, recover for the Oracle resource faisl with XAER_RMERR. Crash recovery is a standard operation for an XA resource.

oracle$ sqlplus sys@MYSID as sysdba
SQL> -- Take a snapshot every 20', keep for two days 
SQL> execute dbms_workload_repository.modify_snapshot_settings(60*24,20);
SQL> -- Take initial snapshot
SQL> execute dbms_workload_repository.create_snapshot;
SQL> -- ... execute the workload...
SQL> -- Take final snapshot
SQL> execute dbms_workload_repository.create_snapshot;
SQL> -- Reset to default values
SQL> execute dbms_workload_repository.modify_snapshot_settings(10080,60);
SQL> quit
oracle$ # Run report
oracle$ cd $ORACLE_HOME/rdbms/admin
oracle$ sqlplus sys@MYSID as sysdba @awrrpt.sql

Using the wizard:

  $ORACLE_HOME/bin/dbca

-- Schema size in a tablespace:
SELECT sum(bytes)/1048576, owner
FROM SYS.DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'MYTABLESPACE'
GROUP BY owner
ORDER BY sum(bytes) DESC
 
-- All tables w/schema
SELECT OWNER, TABLE_NAME
  FROM SYS.ALL_ALL_TABLES
WHERE TABLESPACE_NAME = 'MYTABLESPACE'
 
-- All indexes w/schema
SELECT OWNER, INDEX_NAME
 FROM SYS.ALL_INDEXES
WHERE TABLESPACE_NAME = 'MYTABLESPACE'
 
-- All the LOBs w/schema and column name
SELECT  OWNER, TABLE_NAME, COLUMN_NAME
    FROM SYS.ALL_LOBS
    WHERE TABLESPACE_NAME = 'MYTABLESPACE'
 
-- Which users have this tablespace as their default?
SELECT username, default_tablespace FROM dba_users
  WHERE DEFAULT_TABLESPACE = 'MYTABLESPACE'
 
-- Size of the LOBs for owner MYOWNER
SELECT l.owner, l.TABLE_NAME, l.column_name, l.index_name, s.bytes
  FROM SYS.ALL_LOBS l, SYS.DBA_SEGMENTS s
  WHERE l.owner = 'MYOWNER' AND s.SEGMENT_NAME = l.SEGMENT_NAME
  ORDER BY s.bytes DESC
 
-- Largest tables (including LOBs)
SELECT trunc(sum(s.bytes/1024/1024)) MEGS, S.OWNER, COALESCE (L.TABLE_NAME, S.SEGMENT_NAME) NAME
 FROM SYS.DBA_SEGMENTS s LEFT OUTER JOIN SYS.ALL_LOBS l
 ON L.SEGMENT_NAME = S.SEGMENT_NAME
 GROUP BY S.OWNER, COALESCE (L.TABLE_NAME, S.SEGMENT_NAME)
 ORDER BY MEGS DESC
 
-- Default tablespaces for all owners
SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE 
    FROM SYS.DBA_USERS
    ORDER BY USERNAME
 
-- My default tablespace
SELECT DEFAULT_TABLESPACE FROM SYS.USER_USERS
 
-- Which indices are broken (ORA-01502) and have to be rebuilt?
SELECT * FROM DBA_INDEXES WHERE STATUS = 'INVALID'
 
-- What's the size of "my" schema?
SELECT sum(bytes)/1048576
FROM SYS.USER_SEGMENTS

Connect to an Oracle DB via jruby/jdbc: see Using JRuby with Oracle Database