User Tools

Site Tools


middleware:oracle

Oracle

Oracle XE

  • Before installation, make sure that the hostname resolves (check with ping -c 1 `hostname`)
  • Prepare the shell environment for the oracle user:
source /u01/app/oracle/product/*/xe/bin/oracle_env.sh" >> $HOME/.bash_profile
  • Login as sqlplus sys@XE as sysdba
  • Enable XA (if needed)
@/u01/app/oracle/product/11.2.0/xe/rdbms/admin/xaview.sql
grant select on v$xatrans$ to public;
grant select on pending_trans$ to public;
grant select on dba_2pc_pending to public;
grant select on dba_pending_transactions to public;
grant execute on dbms_system to public;

Oracle 10g on Centos

Override the OS version prerequisite checks launching the installer with:

./runInstaller -ignoreSysPrereqs

You can find some guideline on http://www.oracle-base.com/articles/10g/OracleAS10g3InstallationOnRedHatAndCentOS.php and http://ivan.kartik.sk/oracle/install_ora10gR2_redhat.html

SQL*Plus

Connect with:

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

Commands:

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
SET LINESIZE 1000    All in one line 

Recommended for running SQL script:

SET ECHO ON
whenever sqlerror exit failure rollback;

Finding the SID

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

Documentation

See the SQL*Plus FAQ

SQL

Sample snippets

rocket99 contains several sample command, snippets and documentation.

Test the connection

With a “dummy” select:

  SELECT 1 FROM DUAL

Limit the result size

  SELECT blah FROM footbl WHERE ROWNUM <= N

Fill of junk a table

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

Date format issues

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';

Connect to an alternate instance

Define the ORACLE_SID environment variable. Example:

ORACLE_SID=MYOTHERINSTANCE sqlplus / as SYSDBA

DBA

Move a Datafile

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 a tablespace

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;

Assign the default tablespace for an user

ALTER USER <SCHEMA> DEFAULT tablespace <NEWTBSPC>

Move a table to a different tablespace

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>

Move a schema to a different tablespace

Move tables

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

Move LOBs

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

Recreate indexes

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

Character set

Find the character set with either:

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

Migrate/Clone a Schema

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.

Backup and restore with Datapump

Database directory

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 SYS.dba_directories WHERE directory_name = 'DATA_PUMP_DIR';
GRANT read, write ON DIRECTORY DATA_PUMP_DIR TO scott;
  • With Oracle 12 Pluggable DBs you cannot use DATA_PUMP_DIR, you must create your own directory for your PDB:
CREATE DIRECTORY MY_DATAPUMP AS '/home/oracle/tmp';
GRANT read, write ON DIRECTORY MY_DATAPUMP TO scott;

Export with expdp

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

Import with impdp

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

Examine the contents of a dump

 impdp SYSTEM DIRECTORY=DATA_PUMP_DIR DUMPFILE=mydumpfile.dmp LOGFILE=mydump.log SQLFILE=mydump.sql

It generates a DDL file (without actually importing anything) that you can examine to fine schemas, tablespaces &C

Shrink a datafile

  • Compact LOBs (if needed):
ALTER TABLE MYTABLE MODIFY LOB (MYCOLUMN) (SHRINK SPACE);
ALTER TABLE MYTABLE enable row movement;
ALTER TABLE MYTABLE shrink space cascade;
  • REORG the datafile
  • Shrink it:
alter database datafile '<full_file_name>' resize <size>M

Sessions

-- 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'
 
-- Users with the most connections
SELECT
  COUNT(1) COUNT, machine, username, osuser, program
  FROM v$session
  GROUP BY machine, username, osuser, program
  ORDER BY COUNT DESC;
 
-- 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

Show existing tablespaces, tables and schema

As SYS:

SELECT * FROM SYS.DBA_TABLESPACES
SELECT NAME,FILE#, STATUS,CHECKPOINT_CHANGE# "CHECKPOINT" FROM V$DATAFILE;
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
-- My synonyms
SELECT SYNONYM_NAME FROM SYS.USER_SYNONYMS

Show system grants

def THEUSER = MYUSERID
SELECT PRIVILEGE FROM sys.dba_sys_privs 
  WHERE grantee = '&THEUSER'
  UNION
  SELECT PRIVILEGE FROM dba_role_privs rp JOIN role_sys_privs rsp ON (rp.granted_role = rsp.role)
    WHERE rp.grantee = '&THEUSER'
    ORDER BY 1;

Pending / in-doubt transactions

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

To force commit or rollback of all in-doubt transactions use:

select 'COMMIT FORCE ''' || LOCAL_TRAN_ID || ''';' from SYS.DBA_2PC_PENDING where STATE != 'forced commit' AND state != 'forced rollback';
select 'ROLLBACK FORCE ''' || LOCAL_TRAN_ID || ''';' from SYS.DBA_2PC_PENDING where STATE != 'forced commit' AND state != 'forced rollback';

To manually purge the forced transaction (in case the DB doesn't clean them itself after a while) use:

execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('XXX.XXX.XXX');

Example:

select 'execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(''' || LOCAL_TRAN_ID || ''');
COMMIT;' from SYS.DBA_2PC_PENDING;

Give an user the right to force his transactions with:

GRANT FORCE TRANSACTION TO THEUSER;

Locks

Find locks:

SELECT DO.owner, DO.object_name, DO.object_type, lo.session_id, lo.oracle_username, lo.OS_USER_NAME
  FROM dba_objects DO, v$locked_object lo
  WHERE DO.object_id = lo.object_id;

Enable XA on the Database Server

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.

Create an AWR report

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

Create a DB Instance

Using the wizard:

  $ORACLE_HOME/bin/dbca

Disable password expiration

ALTER PROFILE "DEFAULT" LIMIT
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
PASSWORD_LOCK_TIME UNLIMITED
FAILED_LOGIN_ATTEMPTS UNLIMITED

Cleanup all objects in a schema

  • Generate a deletion script (use spool and set head off feed off verify off if using sqlplus:
SELECT 'drop '||object_type||' '|| object_name||  DECODE(OBJECT_TYPE,'TABLE',
 ' CASCADE CONSTRAINTS','') || ';'
 FROM user_objects;
  • Run the deletion script, ignore errors
  • Purge the recyclebin and check that everything has been deleted. If it's not, repeat
purge recyclebin;
SELECT * FROM user_objects;

Sample code for SQL*Plus:

SET head off feed off verify off
spool purge.sql
SELECT 'drop '||object_type||' '|| object_name||  DECODE(OBJECT_TYPE,'TABLE',
 ' CASCADE CONSTRAINTS','') || ';'
 FROM user_objects;
spool off
START purge.sql
purge recyclebin;
SELECT * FROM user_objects;
! rm purge.sql

Count sessions

SELECT COUNT(*) FROM gv$session;
SELECT username, COUNT(*) FROM gv$session GROUP BY username;

Show SGA sizing

SHOW parameter SGA

Misc samples

-- 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
 
-- Size of tablespaces
SELECT SUM(bytes)/1048576, TABLESPACE_NAME
FROM SYS.DBA_SEGMENTS
GROUP BY TABLESPACE_NAME
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
 
-- Tablespace usage percent
SELECT TABLESPACE_NAME, trunc(USED_PERCENT)
FROM SYS.DBA_TABLESPACE_USAGE_METRICS
ORDER BY USED_PERCENT DESC;
 
-- Max Tablespace size
SELECT TABLESPACE_NAME, trunc(SUM(decode(AUTOEXTENSIBLE, 'YES', MAXBYTES, BYTES))/1048576) "MAX_SIZE Mb"
FROM   DBA_DATA_FILES
GROUP  BY TABLESPACE_NAME;

Connecting programmatically

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

middleware/oracle.txt · Last modified: 2017/10/26 07:12 by ap

Informativa sui cookie