Scripts

 

Hier finden Sie einige nützliche Datenbank-Scripte:

Globale Indizes auf partitionierten Tabellen
Index Informationen anzeigen
Ermittlung von Tabellen mit mehr als 5 Indexes
Tabellengrößen ermitteln
Aktuelle Zeilenanzahl aller Tabellen eines Schemas ermitteln
Tablespace-Auslastung ermitteln
Zeilenanzahl aller Tabellen ermitteln
Free Space im Tablespaces
Maximal Werte der Datenbank ermitteln
Arbeitsspeicherauslastung der Instanz
Aktive Benutzer und SQL-Statements
Session-Informationen zu einem Betriebssystemprozeß
Aktive Sessions
Wartende Sessions
Langlaufende Operationen
Sperren / Row Locks
Blockierende Sessions
Aktuelle Transaktionen mit Benutzer und SQL-Statements
Top I/O-Tablespaces
TOP SQL Statements
Performance Script
User duplizieren mit Datapump
Wartezeiten während RMAN Sicherung monitoren
Queues und Queue Tables
Advance Queueing (AQ): Troubleshooting

Globale Indizes auf partitionierte Tabellen

Wird ein globaler Index auf eine partitionierte Tabelle erstellt, so wird der Index implizit ungültig,
sobald  eine Partition der indizierten Tabelle hinzugefügt oder aber eine bestehende gelöscht wird.
Eine Abfrage auf dieser Tabelle resultiert in der Fehlermeldung:
ORA-01502 Oracle Index in Unusable State.

Welche Indizes invalide sind, können Sie mit dem folgenden Statement ermitteln:

invalid Index / unusable state
select * from DBA_INDEXES
where status = 'INVALID';

Die Korrektur ist recht einfach. Ein Index-Rebuild schafft hier Abhilfe:

Index rebuild
alter index index_name REBUILD;

Um von vorneherein zu verhindern, dass ein Index durch Änderungen an Partitionen
der darunterliegenden Tabellen invalide werden, können Indizes auf Tabellen
als lokaler Index erzeugt werden.
Ein lokaler Index ist equipartitioniert zur darunter liegenden Tabelle:

Oracle: Lokalen Index erstellen
CREATE INDEX ON () LOCAL ONLINE;

Um zu prüfen, welche Ihrer partitionierten Tabellen einen globalen Index besitzen,
können Sie die folgende Abfrage verwenden:

Globale Indizes auf lokale Tabellen ermitteln
Geben Sie hier den Namen des Schemas an, das überprüft werden soll:
DEFINE Benutzername=SCOTT
-- Script-Start SET LINES
SET 3000 SET trimspool ON
SET trimout ON
SELECT
a.index_name, b.table_name
FROM ( SELECT owner, index_name FROM dba_indexes
WHERE
owner = '&&Benutzername'
MINUS SELECT index_owner, index_name FROM dba_ind_partitions WHERE index_owner = '&&Benutzername' ) a, dba_indexes b WHERE a.index_name = b.index_name AND a.owner = b.owner AND b.table_name IN (SELECT table_name FROM dba_tab_partitions p WHERE p.table_owner = '&&Benutzername');
/

Index-Informationen anzeigen

Sie möchten sich Informationen zu Indizes und indizierte Spalten anzeigen lassen?
Das folgende Skript gibt Ihnen alle Indizes samt der indizierten Spalten aus:

Index und indizierte Spalten
SET PAGES 3000
SET LINES 120
SET TRIMSPOOL ON
SET TRIMOUT ON
COL table_owner FORMAT a20
COL table_name FORMAT a30
COL column_name FORMAT a30
COL column_name FORMAT a20
BREAK ON table_owner SKIP 1 ON table_name ON index_name SKIP 1    
SELECT table_owner, table_name, index_name, column_name, COLUMN_POSITION
FROM dba_ind_columns
WHERE table_owner NOT LIKE '%SYS%'
ORDER BY table_owner, table_name, column_position;

MÖchten Sie auf Indizes des eigenen Schema-Owners reduzieren,  so ersetzen Sie die View dba_ind_columns durch user_ind_column.    

Tabellen mit mehr als 5 Indizes

Folgendes Skript zeigt Tabellen mit mehr als 5 Indizes. Eine Überindizierung kann zu
Leistungseinbußen bei Insert-, Update- und Delete-Statements führen.
Dies macht sich insbesondere bei Massenaktualisierungen bemerkbar.

Anzahl Indizes je Tabelle
SET PAGES 3000
SET LINES 120
SET TRIMSPOOL ON
SET TRIMOUT ON
COL owner FORMAT a20
COL table_name FORMAT a30
BREAK ON table_owner SKIP 1
SELECT owner, table_name, count (*) ind_count
FROM dba_indexes
WHERE owner LIKE '%SYS%'
GROUP BY owner, table_name
HAVING COUNT(*) > 5 ORDER BY COUNT(*) desc, OWNER, TABLE_NAME;

Tabellengrößen ermitteln

Sie möchte die Größe einer Tabelle oder auch aller Tabellen eines
Datenbankschemas ermitteln? Das folgende SQL-Skript
zeigt Anzahl belegter Datenbankblöcke sowie die Tabellengröße an:

Tabellengröße ermitteln
PROMPT Bitte geben Sie den Schema-Namen ein.
ACCEPT schema
PROMPT 'Für alle Schemata einfach Enter druecken: '
SET LINES 140
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
COLUMN OWNER FORMAT A30
COLUMN TABLE FORMAT A30
COLUMN TABLESPACE
FORMAT A20
SELECT "OWNER","TABLE","DB Blocks",ROUND(DECODE(SIGN("Size"/1048576 -1 ),-1 ,
DECODE(SIGN("Size"/1024 -1), -1, "Size",
"Size"/1024),
"Size"/1048576) ,2) "SIZE",
DECODE(SIGN("Size"/1048576 -1 ),
-1, DECODE(SIGN("Size"/1024 -1),
-1 ,' Byte', ' Kb'), ' Mb') " ","TABLESPACE"
FROM (SELECT owner "OWNER", segment_name "TABLE", SUM(BYTES) "Size",
blocks "DB Blocks",tablespace_name "TABLESPACE"
FROM DBA_SEGMENTS
WHERE segment_type = 'TABLE'
AND DECODE('&&schema', NULL,'X', OWNER) = DECODE('&&schema',NULL,'X',UPPER('&&schema'))
AND OWNER NOT IN ('SYS' , 'SYSTEM')
GROUP BY owner, segment_name, tablespace_name, blocks
ORDER BY owner, segment_name) ;

Aktuelle Zeilenanzahl aller Tabellen
eines Schemas ermitteln

Sie möchten die Zeilenanzahl aller Tabellen Ihres Schemas ermitteln.
Das ist beispielsweise wie folgt möglich:

Oracle-Datenbank: Aktuelle Zeilenanzahl ermitteln
clear screen
SET echo off
SET heading off
SET feedback off
SET linesize 512
PROMPT =======================================================
PROMPT =
PROMPT = Zeilenanzahl aller Tabellen des aktuellen Schemas
PROMPT =
PROMPT ======================================================
PROMPT
SET TERM off
spool count_all.tmp
SELECT 'SELECT '''||table_name||' => ''||count(*)
FROM "'|| table_name||'" having count(*) > 0;'
FROM user_tables WHERE table_name NOT LIKE 'SYS_IOT_OVER_%'
ORDER BY table_name;
spool off
SET TERM ON
@count_all.tmp

Tablespace-Auslastung ermitteln

Mit dem folgenden Skript kann die Auslastung von Tablespaces geprüft werden.
Dabei können zwei Schwellwerte belegt werden: einer für die Ausgabe einer Warnung,
ein zweiter für den Alarm.
Tablespace-Auslastung
SET pagesize 3000
SET linesize 100
SET verify OFF
SET feedback OFF
SET termout OFF
SET echo OFF
SET serveroutput ON SIZE 40000

DECLARE
TSP_ALL NUMBER(12,2);
TSP_FREE NUMBER(12,2);
TSP_MAX_FREE NUMBER(12,2);
TSP_MAX_NEXT_EXT NUMBER(12,2);
TSP_CAP NUMBER(12,2);
TSP_CAP_PROC NUMBER(12,2);
TSP_FREE_PROC NUMBER(12,2);
TSP_CAP_STATUS CHAR(10);
TSP_WARN_SIZE NUMBER(4);
TSP_ALERT_SIZE NUMBER(4);
TSP_DATE CHAR(16);

cursor TSP_CUR IS
SELECT tablespace_name,sum(bytes)
FROM dba_data_files WHERE tablespace_name NOT IN ('TEMP','ROLLBACK')
GROUP BY tablespace_name
ORDER BY tablespace_name;
BEGIN
TSP_WARN_SIZE:=1000;
TSP_ALERT_SIZE:=500;
SELECT to_char(sysdate,'DD/MM/YYYY HH24:MI') INTO TSP_DATE FROM dual;
DBMS_output.put_line('==========================================');
DBMS_output.put_line('Tablespace-Auslastung - Datum '||TSP_DATE);
DBMS_output.put_line('==========================================');
DBMS_output.put_line('Tablespace TOTAL FREI ZUS_FREI MAX_NEXT_EXT
PCT_FREE WARNUNG');
DBMS_output.put_line('------------------------------------------');


FOR FC IN TSP_CUR LOOP

-- Groesse (MB)
SELECT sum(bytes)/1024/1024
INTO TSP_ALL
FROM dba_data_files
WHERE tablespace_name = FC.tablespace_name;

-- Frei (MB)
SELECT nvl(sum(bytes)/1024/1024,0)
INTO TSP_FREE
FROM dba_free_space
WHERE tablespace_name = FC.tablespace_name;

SELECT nvl(max(next_extent)/1024/1024,0)
INTO TSP_MAX_NEXT_EXT
FROM dba_segments
WHERE tablespace_name = FC.tablespace_name;

-- NVL bei Auto-Allocate
SELECT nvl(max(bytes)/1024/1024,0)
INTO TSP_MAX_FREE
FROM dba_free_space
WHERE tablespace_name = FC.tablespace_name;


TSP_CAP := TSP_ALL - TSP_FREE;
TSP_CAP_PROC := 100*TSP_CAP/TSP_ALL;
TSP_FREE_PROC := 100 - TSP_CAP_PROC;

TSP_CAP_STATUS :='OK';


IF (TSP_FREE_PROC<=10 AND TSP_FREE<=TSP_WARN_SIZE) THEN
TSP_CAP_STATUS:='!';
IF (TSP_FREE_PROC<=10 AND TSP_FREE<=TSP_ALERT_SIZE) THEN
TSP_CAP_STATUS:='!!!';
END IF;
END IF;
IF (TSP_MAX_NEXT_EXT > TSP_MAX_FREE) THEN
TSP_CAP_STATUS:='<==!';
END IF;

dbms_output.put_line(rpad(FC.tablespace_name,20)||
lpad(to_char(TSP_ALL),8)
||lpad(to_char(TSP_FREE),10)||lpad(to_char(TSP_MAX_FREE),11)||
lpad(to_char(TSP_MAX_NEXT_EXT),14)||lpad(TSP_FREE_PROC,9)||'% '||lpad(TSP_CAP_STATUS,5));
END LOOP;
END;

Zeilenanzahl aller Tabellen ermitteln

Sie möchten die Zeilenanzahl aller Tabellen Ihres Schemas ermitteln.
Das ist beispielsweise wie folgt möglich:
Aktuelle Zeilenanzahl ermitteln
SET echo off
SET heading off
SET feedback off
SET linesize 512
PROMPT ======================================================= PROMPT =
PROMPT = Zeilenanzahl aller Tabellen des aktuellen Schemas PROMPT =
PROMPT ====================================================== PROMPT
SET TERM off
spool count_all.tmp
SELECT 'SELECT '''||table_name||' => ''||count(*)
FROM "'|| table_name||'"
having count(*) > 0;'
FROM user_tables
WHERE table_name NOT LIKE 'SYS_IOT_OVER_%'
ORDER BY table_name;
spool off
SET TERM ON
@count_all.tmp

Free Space im Tablespaces

Das folgende Skript zeigt die Speicherbelegung nicht nur nach Tablespace,
sondern auch nach Datafile an:

Free Space in Tablespaces
SET FEEDBACK OFF
SET PAGESIZE 2000
SET LINESIZE 260
SELECT substr(df.tablespace_name,1,20) "Tablespace Name", substr(df.file_name,1,40) "File Name", round(df.bytes/1024/1024,2) "Size (M)", round(e.used_bytes/1024/1024,2) "Used (M)", round(f.free_bytes/1024/1024,2) "Free (M)",
rpad(' '||rpad ('X', round(e.used_bytes*10/df.bytes,0),'X'),11,'-') "% Used"
FROM DBA_DATA_FILES DF,
(SELECT file_id, sum(Decode(bytes,NULL,0,bytes)) used_bytes
FROM dba_extents
GROUP by file_id) E,
(SELECT Max(bytes) free_bytes, file_id
FROM dba_free_space GROUP BY file_id) f
WHERE e.file_id (+) = df.file_id
AND df.file_id = f.file_id (+)
ORDER BY df.tablespace_name, df.file_name;

Noch einfacher ist das Monitoring von Speicherplatz in Temporary Tablespaces:

SQL
SELECT *
FROM dba_temp_free_space;

Maximal Werte der Datenbank ermitteln

Mit der folgenden Abfrage können Informationen über die "high water marks"
in der Datenbank abgefragt werden.
SQL
SELECT name, max(water)
FROM (
SELECT name, HIGHWATER water
FROM dba_high_water_mark_statistics
WHERE HIGHWATER > 0
)
GROUP BY name HAVING count(*) > 1;

Arbeitsspeicherauslastung der Instanz

Sie möchten überprüfen, wieiviel Speicher die einzelnen Speicherbereiche
Ihrer Oracle-Instanz benötigen?
Das folgende Skript gibt hierüber Auskunft.
Es unterteilt den Shared Memory (System Global Area, oder auch kurz SGA)
und den Prozess-Speicher einzelner Prozesse (Program Global Area, oder auch kurz PGA)
und zeigt zudem Zusatzinformationen zu Prozessen und Programmen an.
SGA und PGA: Speicherauslastung
SET TRIMOUT ON
SET TRIMPSPOOL ON
SET LINES 300
SET PAGES 3000
COL info FORMAT A30
COL MB FORMAT 999,990.0
COL user_name FORMAT A15
COL logon_time FORMAT A20
COL machine FORMAT A15
COL osuser FORMAT A15
COL program FORMAT A25


SELECT 'SGA: '||name AS info
, round(value/1024/1024, 2) AS MB
, NULL AS program
, NULL AS logon_time
, NULL AS SID
, NULL AS SERIAL
, NULL AS user_name
, NULL AS machine
, NULL AS osuser
FROM v$sga
UNION ALL
SELECT 'PGA: Process-ID '||p.spid AS info
, round(p.pga_alloc_mem/1024/1024,2) AS MB
, p.program AS program
, s.logon_time AS logon_time
, s.sid AS SID
, s.serial# AS SERIAL
, s.username AS user_name
, s.machine AS machine
, s.osuser AS osuser
FROM v$session s
, v$sesstat pcur
, v$process p
WHERE
pcur.statistic# = 20 AND
s.paddr = p.addr AND
pcur.sid = s.sid
ORDER BY 2 DESC;

Aktive Benutzer und SQL-Statements

Sie möchten überprüfen, welche Benutzer gerade aktiv arbeiten
und welche SQL-Statements diese ausführen?
Das folgende Statement gibt Ihnen genau diese Informationen aus:
SQL
SET LINESIZE 120
SET PAGESIZE 60
COLUMN TERMINAL FORMAT A15
COLUMN PROGRAM FORMAT A15
COLUMN USERNAME FORMAT A15
COLUMN SID FORMANT 999999


SELECT SID, SERIAL#,
to_char(logon_time,'dd/mm/rr hh24:mi:ss')
LOGONTIME, USERNAME,TERMINAL, PROGRAM, SQL_TEXT
FROM GV$SESSION, GV$SQLAREA
WHERE USERNAME IS NOT NULL
AND STATUS ='ACTIVE'
AND SQL_HASH_VALUE = HASH_VALUE
ORDER BY TERMINAL;

Session-Informationen zu einem
Betriebssystemprozeß

Oftmals ist im Betriebssystem ein Oracle-Prozess sichtbar, der möglicherweise
eine Menge Ressourcen verbraucht, für den - zumindest aus OS-Sicht -
nicht erkennbar ist, was er eigentlich genau treibt:
Welche Verbindung zur Datenbank von welchem Rechner mit welchem
Program welches SQL-Statements ausführt, kann hier eine hilfreiche
Information sein. Dies zu ermitteln ist mit folgendem Script möglich.
Die View v$process enthält die Spalte SPID, die die Prozess-ID im OS
wiedergibt. Von v$process können Sie über die Spalte addr auf die
View v$session und die Spalte paddr joinen.
U nd von der v$session kommen Sie über die Spalte sql_hash_value
auf die v$sqlarea mit der Spalte hash_value.

Informationen zu einer Oracle-Session nach SPID
SET LINES 200
SET pages 3000
SET long 4000
COLUMN program format a20
COLUMN machine format a30
SELECT p.spid, p.username os_user,s.username db_user, s.program, s.machine, s.STATUS, a.sql_text
FROM v$process p, v$session s, v$sqlarea a
WHERE p.spid = &PROCESS_ID
AND p.addr = s.paddr
AND s.sql_hash_value = a.hash_value;

Wird dieses Skript in SQL*Plus aufgerufen, so werden Sie nach der Server
Prozeß-ID (SPID) gefragt. Angenommen, der Prozeß mit der ID 4711 im
Betriebssystem ist auffälig, so können Sie obiges Skript aufrufen und die
entsprechende SPID eingeben.

Aktive Sessions

Das folgende Statement zeigt Informationen zu aktiven Datenbanksessions,
deren Prozess-ID (PID des Betriebssystems), Wartezustand und Anmeldezeit:
sql
SET PAGESIZE 5000
SET LINESIZE 250

COLUMN logon_time FORMAT A20
COLUMN username FORMAT A15
COLUMN osuser FORMAT A15
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN wait_class FORMAT A15
COLUMN state FORMAT A19

SELECT NVL(a.username, '(ORA-BACKGROUND)') AS USERNAME,
a.osuser AS OS_USER,
a.sid AS SID,
a.serial# AS SERIAL,
d.spid AS PROC_ID,
a.wait_class AS WAIT_CLASS,
a.seconds_in_wait AS SECS_WAITED,
a.state AS STATE,
a.blocking_session AS BLOCKING,
a.blocking_session_status AS BLOCKING_STATUS,
a.module AS MODULE,
to_char (a.logon_Time,'DD.MM.YYYY HH24:MI:SS')
AS logon_time
FROM v$session a,
v$process d
WHERE a.paddr = d.addr
AND a.STATUS = 'ACTIVE'
ORDER BY 1,2;

Wartende Sessions

Sie möchten herausfinden worauf eine Session
aktuell wartet?
Die View gv$session_wait gibt Auskunft:
Wartende Sessions
COL sid FORMAT 999
COL spid FORMAT 9999
COL username FORMAT a20
COL event FORMAT a20
COL machine FORMAT a20
SET LINES 200
SET TRIMSPOOL ON
SET TRIMOUT ON

SELECT sw.sid sid
, p.spid spid
, s.username username
, s.osuser osuser
, sw.event event
, s.machine machine
, s.program program
, decode(sw.event,'db file sequential read', sw.p3,
'db file scattered read', sw.p3,
NULL) blocks
FROM v$session_wait sw
, v$session s
, v$process p
WHERE s.paddr = p.addr
AND sw.wait_class != 'Idle'
AND sw.sid = s.sid;

Langlaufende Operationen

Sie haben einen Job oder eine Abfrage gestartet....
und warten.... und warten .... und warten.
Was tut das Ding eigentlich? Tut es überhaupt etwas?
Oder "hängt" die Verarbeitung beispielsweise an einer
Sperre oder einem Archiver Stuck?

Auskunft gibt hier die View gv$long_operations.
Langlaufende Operationen tragen hier ein, was sie gerade tun,
wie lange sie bereits daran arbeiten, wie weit sie mit ihrer
Arbeit fortgeschritten sind und wann sie (vermutlich) zum
Abschluß kommen werden.

Der Aufbau dieser View ist wie folgt:

und totalwork berechnet? Informationen zuletzt aktualisiert?
NameBeschreibung
INST_IDIm Cluster gibt diese Spalte die Instanz- Nummer der RAC-Instanz an.
SIDDer Session-Identifier der Benutzersession. Den können Sie mit der Spalte SID der View gv$session verbinden und erhalten so Informationen zum Benutzer, dem verwendeten Programm und den OS-User.
SERIAL#Serial-Number der Session
OPNAMEEine Beschreibung der aktuell ausgeführten Operation
TARGETZiel der Operation
TARGET_DESCEine genauere Beschreibung der Operation
SOFARWie weit ist die Operation?
TOTALWORKUnd wieviel ist insgesamt zu erledigen?
UNITSIn welcher Einheit werden die Spalten sofar
START_TIMEWann wurde die Operation gestartet?
LAST_UPDATE_TIMEWann wurden die in dieser View angezeigten
TIME_REMAININGWie lange (in Sekunden) dauert es schätzungsweise noch? Achtung: Hier gibt es den Windows-Fortschrittsbalken-Effekt => Da wird sich manchmal auch ein wenig verschätzt.
ELAPSED_SECONDSWieviel Zeit (in Sekunden) ist bereits vergangen?
MESSAGEZusatzinformationen
USERNAMEAusführender Benutzer
SQL_ADDRESSÜber diese Spalte und die Spalte sql_hash_value
SQL_HASH_VALUElässt sich das aktuell ausgeführte SQL-Statement ermitteln. Man muss nur zur View gv$sqlarea verknüpfen.

Am besten schränken Sie bei der Suche ein:
Nur die Datenzeilen, deren Wert in der Spalte sofar kleiner ist als der in totalwork sind relevant.
Hat die Spalte sofar den Wert von Totalwork erreicht, so ist die Operation bereits
abschgeschlossen. Natürlich können Sie mit den Datum- und Zeit-Spalten auch munter
rechnen und sich eine Art "Fortschrittsübersicht" erstellen.

Hier ein Beispiel:

SELECT inst_id, opname, target, sofar, totalwork,
to_char(sysdate + time_remaining/24/60/60, 'dd.mm.yyyy hh24:mi:ss')
geschaetzte_endzeit,
round(time_remaining/60/60, 2) geschaetzte_dauer_in_stunden
FROM gv$session_longops
WHERE sofar < totalwork;

Sperren / Row Locks

Sie möchten herausfinden, welcher Benutzer einen zweiten blockiert?

Das folgende Skript identifiziert den Benutzer der wartenden Session,
den blockierenden Benutzer sowie die Datenzeile, auf die gewartet wird:

BEGIN
FOR locks
IN (SELECT o.owner, nvl (s1.username, b.name) user_name s2.username locker,
s1.row_wait_obj# row_wait_obj, s1.row_wait_block# row_wait_block,
s1.row_wait_row# row_wait_row, s1.row_wait_file# row_wait_file
, o.object_name
FROM v$session s1,
v$session s2,
v$bgprocess b,
v$process p,
v$lock l1,
v$lock l2,
dba_objects
WHERE b.paddr(+) = s1.paddr
AND p.addr(+) = s1.paddr
AND o.object_id = s1.row_wait_obj#
AND s1.sid = l1.sid
AND s2.sid = l2.sid
AND l1.id1 = l2.id1
AND l1.id2 = l2.id2
AND l2.block = 1
AND l1.request != 0
AND rownum < 50 -- Begrenzung der Zeilenmenge
ORDER BY nvl (s1.username, b.name),o.object_name)
LOOP
dbms_output.put_line('SELECT * FROM ' || locks.owner || '.' || locks.object_name || '
WHERE rowid = ''' || dbms_rowid.rowid_create(1,locks.row_wait_obj, ocks.row_wait_file,
locks.row_wait_block,
locks.row_wait_row) || '''; -- ' || locks.user_name || ' wartet auf ' ||locks.locker);
END LOOP;
END;
/

Sie möchten herausfinden, welche Sperren aktuell in Ihrer Oracle-Datenbank
gehalten werden? Das folgende Skript gibt hierüber Auskunft:

SQL
SELECT o.name object_name, u.name owner, lid.*
FROM (SELECT
s.inst_id, s.SID, s.serial#, p.spid,NVL (s.sql_id, 0),
s.sql_hash_value,
DECODE (l.TYPE,
'TM', l.id1,
'TX', DECODE (l.request,
0, NVL (lo.object_id, -1),
s.row_wait_obj#
),
-1
) AS object_id,
l.TYPE lock_type,
DECODE (l.lmode,
0, 'NONE',
1, 'NULL',
2, 'ROW SHARE',
3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCLUSIVE',
6, 'EXCLUSIVE',
'?'
) mode_held,
DECODE (l.request,
0, 'NONE',
1, 'NULL',
2, 'ROW SHARE',
3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCLUSIVE',
6, 'EXCLUSIVE',
'?'
) mode_requested,
l.id1, l.id2, l.ctime time_in_mode,s.row_wait_obj#,
s.row_wait_block#, s.row_wait_row#, s.row_wait_file#
FROM gv$lock l,
gv$session s,
gv$process p,
(SELECT object_id, session_id, xidsqn
FROM gv$locked_object
WHERE xidsqn > 0) lo
WHERE l.inst_id = s.inst_id
AND s.inst_id = p.inst_id
AND s.SID = l.SID
AND p.addr = s.paddr
AND l.SID = lo.session_id(+)
AND l.id2 = lo.xidsqn(+)) lid,
SYS.obj$ o,
SYS.user$ u
WHERE o.obj#(+) = lid.object_id
AND o.owner# = u.user#(+)
AND object_id -1;

Blockierende Sessions

Sie möchten Benutzer-Sessions identifizieren, die andere Benutzer blockieren?
Das folgende Skript zeigt den Namen des Objektes auf dem eine Sperre besteht,
der Sperr-Modus sowie der blockierende bzw. blockierte Benutzer:
Blockieren Benutzer-Session identifizieren

clear screen
SET heading ON

COLUMN object_name FORMAT A30
COLUMN holder FORMAT A25
COLUMN Waiter FORMAT A25
PROMPT =========================================================
PROMPT =
PROMPT =
PROMPT = Blockierende und wartende Benutzer
PROMPT =
PROMPT =
PROMPT =========================================================
PROMPT Anfrage wird bearbeitet ...

SELECT DISTINCT
o.object_name, sh.sid "SID", sh.SERIAL# "SERIAL"
, sh.username||'('||sh.sid||','||sh.SERIAL#||')' "Holder"
, sw.username||'('||sw.sid||','||sw.SERIAL#||')' "Waiter"
, decode(lh.lmode,
1, 'null',
2, 'row share',
3, 'row exclusive',
4, 'share',
5, 'share row exclusive',
6, 'exclusive')
AS "Lock Type"
FROM gv$session sw
, gv$lock lw
, all_objects o
, gv$session sh
, gv$lock lh
WHERE lh.id1 = o.object_id
AND lh.id1 = lw.id1
AND sh.sid = lh.sid
AND sw.sid = lw.sid
AND sh.lockwait IS NULL
AND sw.lockwait IS NOT NULL
AND lh.type = 'TM'
AND lw.type = 'TM';

Aktuelle Transaktionen mit Benutzer
und SQL-Statements

Sie möchten ermitteln, welche Transaktionen aktuell in der Datenbank
Undo-Records erzeugen und Undo-Blöcke allokieren?
Zusätzlich wäre noch der Name des ausführenden Datenbankbenutzers
sowie das zugehörige SQL-Statement für Sie interessant?
Aktuelle Transaktionen inkl. Benutzer / SQL
SELECT used_ublk, used_urec,
s.osuser, s.username, s.sid,
s.serial#, s.program, s.machine,
a.sql_text, t.start_time
FROM v$transaction t,
v$session s,
v$sqlarea a
WHERE
(
(
s.SQL_ADDRESS = a.ADDRESS AND
s.sql_hash_value = a.HASH_VALUE
)
OR
(
s.PREV_SQL_ADDR = a.ADDRESS AND
s.PREV_hash_value = a.HASH_VALUE
)
)
AND t.ses_addr = s.saddr;

Wird eine Transaktion gerade ausgeführt, so erhöht sich die Anzahl genutzer
Undo-Sätze (used_urec) und Undo-Blöcke (used_ublk).
Wird die Transaktion aktuell zurückgerollt, nehmen die Werte ab.

Bei einem shutdown immediate beispielsweise kann auf
diese Weise gemonitort werden, welche Menge an Undo-Informationen
zurückzurollen sind, bevor die Datafiles geschlossen werden können.

Top I/O-Tablespaces

Sie möchten herausfinden, auf welche Tablespaces
Ihrer Oracle-Datenabnk eine hohe Lese-Schreiblast
verursacht wird?
Das folgende Skript gibt hierüber Auskunft:
I/O-Last auf Tablespaces
------------------------------------------------------
-- Top Tablespaces nach Lese-Schreib-Last
------------------------------------------------------
SET linesize 410
SET trimspool ON
SET trimspool OFF
SET pages 3000
SET echo OFF
SET feedback OFF


PROMPT
PROMPT Top Tablespaces nach Lese-/Schreiblast PROMPT
file_name format a30 HEAD "Datafile"
COLUMN ts_name format a18 HEAD "Tablespace"
COLUMN stat_reads format 99,99,99,99,999 HEAD "Phys Reads"
COLUMN stat_writes format 99,99,99,99,999 HEAD "Phys Writes"
COLUMN stat_breads format 99,99,99,99,999 HEAD "Phys Blk Reads"
COLUMN stat_bwrites format 99,99,99,99,999 HEAD "Phys Blk Writes"
BREAK ON ts_name

SELECT t.name ts_name,
f.name datafile,
s.phyrds stat_reads,
s.phyblkrd stat_breads,
s.phywrts stat_writes,
s.phyblkwrt stat_bwrites
FROM v$tablespace t, v$datafile f, v$filestat s
WHERE t.ts# = f.ts#
AND f.file# = s.file#
ORDER BY s.phyrds DESC, s.phywrts DESC;

TOP SQL Statements

Die View v$sqlarea gibt Auskunft über SQL Statements
und deren Ressourcenverbrauch. Informationen wie Lese-Zugiffe
auf Cache und Disk werden ebenso angezeigt, wie der
CPU-Verbrauch und die Anzahl an Sortiervorgängen.

Überprüft man die komplette View, so wird man mit Informationen
geradezu erdrückt: Alle noch im Library Cache befindlichen SQL
Statements werden angezeigt... und das ist meist nicht wenig.
Eine Einschränkung auf die Top Statemtents dagegen gibt einem
rasch Auskunft, welche SQL Statements die größten
Ressourcenverbraucher sind.
Hieraus wiederum läßt sich etwaiger Optimierungsbedarf ermitteln.

Das folgende Skript gibt die Top 25 SQL-Statements nach CPU-Verbrauch aus:

TOP SQL Statements nach CPU
SET pages 3000
SET LINES 120
SET trimspool ON
SET trimout ON

COLUMNS sql_text FORMAT a40 word_wrapped HEAD 'SQL|Text'
COLUMNS cpu_time HEAD 'CPU|Time'
COLUMNS elapsed_time HEAD 'Elapsed|Time'
COLUMNS disk_reads HEAD 'Disk|Reads'
COLUMNS buffer_gets HEAD 'Buffer|Gets'
COLUMNS rows_processed HEAD 'Rows|Processed'

TTITLE 'SQL-Statements nach CPU-Nutzung'

SELECT * FROM
(SELECT sql_text,
cpu_time/1000000000 cpu_time,
elapsed_time/1000000000 elapsed_time,
disk_reads,
buffer_gets,
rows_processed
FROM v$sqlarea
ORDER BY cpu_time DESC
)
WHERE rownum <=25;

Es läßt sich anpassen, um SQL Statements ermitteln, die viele Buffer
oder Disk-Zugriffe verursacht haben:

Top SQL Statements nach Buffer-Zugriffen
SET pages 3000
SET lines 120
SET trimspool ON
SET trimout ON

COLUMN sql_text FORMAT a40 word_wrapped HEAD 'SQL|Text'
COLUMN cpu_time HEAD 'CPU|Time'
COLUMN elapsed_time HEAD 'Elapsed|Time'
COLUMN disk_reads HEAD 'Disk|Reads'
COLUMN buffer_gets HEAD 'Buffer|Gets'
COLUMN rows_processed HEAD 'Rows|Processed'

TTITLE 'SQL-Statements nach CPU-Nutzung'

SELECT * from
(SELECT sql_text,
cpu_time/1000000000 cpu_time,
elapsed_time/1000000000 elapsed_time,
disk_reads,
buffer_gets,
rows_processed
FROM v$sqlarea
ORDER BY buffer_gets desc
)
WHERE rownum <=25;

Performance Script

Performance Script
prompt
prompt Rollback Segment Statistics
prompt

col name FOR a7
col xacts FOR 9990 head "Actv|Trans"
col InitExt FOR 990.00 head "Init|Ext|(Mb)"
col NextExt FOR 990.00 head "Next|Ext|(Mb)"
col MinExt FOR 99 head "Min|Ext"
col MaxExt FOR 999 head "Max|Ext"
col optsize FOR 9990.00 head "Optimal|Size|(Mb)"
col rssize FOR 9990.00 head "Curr|Size|(Mb)"
col hwmsize FOR 9990.00 head "High|Water|Mark|(Mb)"
col wraps FOR 999 head "W|R|A|P|S"
col extends FOR 990 head "E|X|T|E|N|D|S"
col shrinks FOR 990 head "S|H|R|I|N|K|S"
col aveshrink FOR 990.00 head "AVG|Shrink|(Mb)"
col gets head "Header|Gets"
col waits FOR 99990 head "Header|Waits"
col writes FOR 999,999,990 head "Total|Writes|Since|Startup|(Kb)"
col wpg FOR 9990 head "AVG|Writes|Per|HedGet|(bytes)"
SET LINES 132 pages 40 feed off
break ON report
compute sum of gets ON report
compute sum of waits ON report
compute avg of aveshrink ON report
compute avg of wpg ON report

SELECT name,
XACTS,
initial_extent/1048576 InitExt,
next_extent/1048576 NextExt,
min_extents MinExt,
max_extents MaxExt,
optsize/1048576 optsize,
RSSIZE/1048576 rssize,
HWMSIZE/1048576 hwmsize,
wraps,
extends,
shrinks,
aveshrink/1048576 aveshrink,
gets,
waits,
writes/1024 writes,
writes/gets wpg
FROM v$rollstat,v$rollname,dba_rollback_segs
WHERE v$rollstat.usn=v$rollname.usn
AND dba_rollback_segs.segment_id=v$rollname.usn
ORDER BY name
/

prompt
prompt More Rollback Segment Statistics
prompt

COLUMN "Rollback Segment" format a16
COLUMN "Size (Kb)" format 9,999,999
COLUMN "Gets" format 999,999,990
COLUMN "Waits" format 9,999,990
COLUMN "% Waits" format 90.00
COLUMN "# Shrinks" format 999,990
COLUMN "# Extends" format 999,990

SELECT rn.Name "Rollback Segment", rs.RSSize/1024 "Size (KB)", rs.Gets "Gets",
rs.waits "Waits", (rs.Waits/rs.Gets)*100 "% Waits",
rs.Shrinks "# Shrinks", rs.Extends "# Extends"
FROM sys.v_$RollName rn, sys.v_$RollStat rs
WHERE rn.usn = rs.usn;

/

prompt
prompt Yet some More Rollback Segment Statistics
prompt

col RBS format a5 trunc
col SID format 9990
col USER format a10 trunc
col COMMAND format a78 trunc
col STATUS format a6 trunc

SELECT r.name "RBS", s.sid, s.serial#, s.username "USER", t.status,
t.cr_get, t.phy_io, t.used_ublk, t.noundo,
substr(s.program, 1, 78) "COMMAND"
FROM sys.v_$session s, sys.v_$transaction t, sys.v_$rollname r
WHERE t.addr = s.taddr
AND t.xidusn = r.usn
ORDER BY t.cr_get, t.phy_io;
/

Prompt
Prompt Cache hit ratio
prompt

SELECT 1-(phy.value / (cur.value + con.value)) "Cache Hit Ratio",
round((1-(phy.value / (cur.value + con.value)))*100,2) "% Ratio"
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = 'db block gets' AND
con.name = 'consistent gets' AND
phy.name = 'physical reads';

/

Prompt
Prompt Another Buffer Cache hit ratio Calculation
prompt

COLUMN "logical_reads" format 99,999,999,999
COLUMN "phys_reads" format 999,999,999
COLUMN "phy_writes" format 999,999,999
SELECT a.value + b.value "logical_reads",
c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) /
(a.value+b.value))
"BUFFER HIT RATIO"
FROM v$sysstat a, v$sysstat b, v$sysstat c
WHERE
a.statistic# = 38
AND
b.statistic# = 39
AND
c.statistic# = 40;

/

prompt
prompt DATA Dictionary Hit Ratio should be over 90 percent
prompt

SELECT sum(gets) "Data Dict. Gets",
sum(getmisses) "Data Dict. Cache Misses",
round((1-(sum(getmisses)/sum(gets)))*100) "DATA DICT CACHE HIT RATIO",
round(sum(getmisses)*100/sum(gets)) "% MISSED"
FROM v$rowcache;

/

prompt
prompt Library Cache Miss Ratio
prompt

SELECT sum(pins) "executions",
sum(reloads) "Cache Misses",
round((1-(sum(reloads)/sum(pins)))*100) "LIBRARY CACHE HIT RATIO",
round(sum(reloads)*100/sum(pins)) "% Missed"
FROM v$librarycache;

/

prompt
prompt More Library Cache stats
prompt


SELECT namespace,
trunc(gethitratio*100) "Hit Ratio",
trunc(pinhitratio*100) "Pin Hit Ratio",
reloads "Reloads"
FROM v$librarycache;
/

prompt
prompt Another Library Cache Calculation, total reloads should be AS close TO 0 AS possible.
prompt

COLUMN libcache format 99.99 heading 'Percentage' jus cen
SELECT sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
FROM v$librarycache;

/

prompt
prompt Redo Log Buffer should be AS close TO 0 AS possible
prompt

SELECT substr(name,1,30),value
FROM v$sysstat WHERE name ='redo log space requests';
/

prompt
prompt Redo Log Contention, ALL ratios less than 1
prompt

SET feedback OFF
COLUMN name FORMAT a15
COLUMN gets FORMAT 99999999
COLUMN misses FORMAT 999999
COLUMN immediate_gets FORMAT 99999999 HEADING 'IMM_GETS'
COLUMN immediate_misses FORMAT 99999999 HEADING 'IMM_MISSES'
PROMPT Examining Contention FOR Redo Log Buffer Latches...
PROMPT ----------------------------------------------------

SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');

/


prompt
prompt Disk Vs. Memory Sorts. Try TO keep the disk/memory ratio TO less than .10 BY increasing the sort_area_size
prompt


SET HEADING OFF
SET FEEDBACK OFF
COLUMN name FORMAT a30
COLUMN value FORMAT 99999990

SELECT name, value FROM v$sysstat
WHERE name IN ('sorts (memory)', 'sorts (disk)');

/

prompt
prompt Initialization Parameters
prompt

SELECT substr(name,1,35) "Parameter" ,substr(value,1,35) "Value" FROM v$parameter ORDER BY name ASC;

/

User duplizieren mit Datapump

Sie möchten einen Datenbank-Benutzer inklusive
seiner Objekte und Daten kopieren?
Verwendet man Oracle Export / Import für das Kopieren
eines Datenbank-Benutzers, so wird zunächst eine Datei ins
Dateisystem geschrieben, die anschließend in ein neues Schema
importiert wird.

Ab Oracle Database 10g Release 1 ist der Zwischenschritt
über den Export in eine Datei nicht mehr erforderlich.
Mit Oracle Data Pump kann über einen Datenbank Link ein
Schema einer anderen Datenbank in die eigene importiert
werden. Diese Technik können Sie auch nutzen, um innerhalb
derselben Datenbank ein Schema zu kopieren.
Dazu erstellen Sie einen Loopback Database Link, der auf die
eigene Datenbank verweist.

Zunächst erstellen Sie einen Datenbank Link:
CREATE DATABASE LINK "LOOPBACK.WORLD"
USING
'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)
(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)
(SERVICE_NAME=mein_service_name)))';
Achten Sie darauf, dass Sie den Parameter service_name beim Erstellen
des Database Links anpassen. Testen Sie Ihren Datenbank Link
vorab nochmals, indem Sie eine Abfrage wie
SELECT * FROM all_tables@loopback.world WHERE rownum <= 10;

absetzen. Hier darf keine Fehlermeldung auftreten.

Funktioniert der Datenbank Link, kann die Prozedur implementiert
werden. Der Benutzer, unter dessen Namen die Prozedur implementiert
wird, benötigt Ausführungsrechte auf die Datenbank-Packages
dbms_datapump und dbms_flashback sowie das Prigvileg alter user.
Auf Letzteres kann dann verzichtet werden, wenn Codezeile 50 ff.
(Ändern des Kennwortes des neu erstellten Benutzers) entfernt
oder auskommentiert wird.

Oracle-Datenbanken: Schema kopieren mit Oracle Datapump
CREATE OR REPLACE PROCEDURE kopiere_schema
(i_quell_schema IN VARCHAR2,
i_ziel_schema IN VARCHAR2,
i_neues_passwort IN VARCHAR2,
i_database_link IN VARCHAR2 DEFAULT 'loopback.world')
AS
-- Job Handle
l_job_handle NUMBER;
-- Job Status
l_job_status user_datapump_jobs.state%TYPE;
-- Single Quote
quote VARCHAR2(1) := chr(39);
BEGIN
-- Erstellen eines Import-Jobs
l_job_handle := dbms_datapump.open (
'IMPORT',
'SCHEMA',
i_database_link);
-- Um konsistent zu kopieren, wird die aktuelle
-- System Change Number ermittelt und geesetzt
dbms_datapump.set_parameter (
l_job_handle,
'FLASHBACK_SCN',
dbms_flashback.get_system_change_number);
-- Einschraenkung auf das zu kopierende Schema
dbms_datapump.metadata_filter (
l_job_handle,
'SCHEMA_LIST',
quote || i_quell_schema || quote);
-- Remapping auf das neue Schema
dbms_datapump.metadata_remap(
l_job_handle,
'REMAP_SCHEMA',
i_quell_schema,
i_ziel_schema);
-- Start des Jobs
dbms_datapump.start_job(l_job_handle);
-- Auf Ausfuehrungsende warten
dbms_datapump.wait_for_job(l_job_handle, l_job_status);
-- Aendern des Passwortes fuer den neuen Schema-Benutzer
EXECUTE IMMEDIATE
' ALTER USER '|| i_ziel_schema||
' IDENTIFIED BY '|| i_neues_passwort;
END;
/

Der Aufruf der Prozedur kann nun wie folgt aussehen:

Oracle-Datenbanken: Aufruf der Prozedur zur Schema-Kopie:
BEGIN
kopiere_schema ('SCOTT', 'noch_ein_scott', 'tricksi');
END;

Wartezeiten während RMAN Sicherung
monitoren

Ihre Sicherung läuft und läuft und läuft... und Sie fragen sich,
was macht RMAN da eigentlich gerade?
Das folgende Skript gibt Auskunft über Operationen des
Recovery Managers:
Oracle RMAN: Wait statistics
ALTER SESSION SET NLS_DATE_FORMAT = 'dd.mm.yy hh24:mi:ss';

SET LINES 1200
SET trimout ON
SET trimspoo ON
SET pages 3000
COL object_name FORMAT a30
COL p1text FORMAT a20
COL p2text FORMAT a20
COL p3text FORMAT a20
COL spid FORMAT 9999999


PROMPT Uebersicht
PROMPT =================
SELECT SID AS SID
, START_TIME AS startzeit
, TOTALWORK AS gesamt
, sofar AS soweit
, (sofar/totalwork) * 100 AS erledigt
, sysdate + TIME_REMAINING/3600/24 AS endzeit
FROM v$session_longops
WHERE totalwork > sofar
AND upper(opname) LIKE '%RMAN%';

PROMPT
PROMPT


PROMPT Details
PROMPT =================
SELECT SID
, SPID
, CLIENT_INFO
, event
, seconds_in_wait secs
, p1text
, p1
, p2text
, p2
, p3text
, p3
FROM V$PROCESS p, V$SESSION s
WHERE p.ADDR = s.PADDR
AND upper(CLIENT_INFO) LIKE '%RMAN%';

Queues und Queue Tables

Sie möchten ermitteln, welche Queues und Queue
Tables in Ihrer Datenbank existieren?
Folgendes Skript gibt Ihnen Auskunft:
Abfrage von Oracle Queues und Queue Tables
SELECT *
FROM DBA_QUEUE_TABLES
ORDER BY OWNER, QUEUE_TABLE;

SELECT OWNER, QUEUE_TABLE, NAME, QID
, QUEUE_TYPE, MAX_RETRIES, RETRY_DELAY
, ENQUEUE_ENABLED, DEQUEUE_ENABLED
, RETENTION, USER_COMMENT
FROM DBA_QUEUES
ORDER BY OWNER, QUEUE_TABLE, NAME;

Advance Queueing (AQ): Troubleshooting

Folgende Checks im Rahmen des Troubleshooting für
Oracle Advanced Queuing können hilfreich sein.

Prüfen Sie die View dba_queue_schedules:

Der Status des Queue-Schedulers sollte auf "enabled" stehen.
Zudem sollte schedule_disable auf "N" stehen.
Prüfen Sie die View aq$schedules:

Die Spalte jobno sollte einen Wert ungleich Null enthalten.
In der Tabelle job$ sollte zudem ein Job mit der entsprechenden
jobno zufinden sein.

Prüfen Sie die Durchführung der Propagation wie folgt:

Die Tabelle dba_queue_schedules enthält eine Spalte total_number.
Sie gibt an, wieviele Nachrichten propagiert wurden. Führen Sie die
Abfrage wiederholt durch, sollte der Wert steigen.
Falls die Propagierung nicht läuft, prüfen Sie die Spalten next_run_date
und next_run_time der View dba_queue_schedules.
Eventuell erfolgt die Propagierung erst später.
Prüfen Sie, ob der Datenbank Link zur Zieldatenbank funktioniert.
Dazu können Sie folgende Abfrage verwenden:

select * from dual@remote_db_link;
(wobei remote_db_link durch den Namen des Datenbank Links ersetzt werden muss).
Eine Übersicht aller Datenbank Links finden Sie in der View dba_db_links.
Prüfen Sie, dass mindestens zwei Job Queue Prozesse gestartet sind:

show parameter job_queue

Prüfen Sie auf die Nachrichten in der Source Queue:

select count (*) from AQ$
where q_name = 'source_queue_name';

Prüfen Sie auf Nachrichten in der Destination Queue:

select count (*) from AQ$
where q_name = 'destination_queue_name';

Prüfen Sie, wer aktuell Job Queue Prozesse nutzt:

Fragen Sie dazu die View dba_jobs_running ab.
Möglicherweise nutzen andere Jobs die Job Queue Prozesse.
Prüfen Sie die Queue Tabelle sys.aq$_prop_table_instno in der View
dba_queue_tables enthalten ist.
Die Queue sys.aq$_prop_notify_queue_instno sollte zudem in der
View DBA_QUEUES enthalten und mit dem Status "enabled" für den Enqueue und
Dequeue markiert sein. Sofern Oracle Real Application Clusters (RAC)
eingesetzt werden, müssen diese auf beiden RAC-Knoten zu finden sein.

Aktivieren Sie Tracing mit dem event 24040, level 10.
Die Trace-Informationen werden in den Job Queue Trace Files hinterlegt.
Sie können die Trace-Files auf Fehler und Sende-Informationen untersuchen.