Zurück

Akadia Information Technology


Wo sind die Default NLS-Parameter definiert
Datum Arithmetic mit Oracle
How to implement Sleep Funktion in PL/SQL
The mistery of VARCHAR2
The mistery of Oracle NULL's
The mistery of ROWNUM and ORDER BY
Can I replicate only certain partitions of a partitioned table
How to set «seminfo_semmni» on Sun-Solaris / Oracle ?
Help with SQL -- aus unserem Support Alltag
Geändertes UPDATE Verhalten ab Oracle 8i
Wie findet man den maximalen Wert aus einer Gruppe ?
How to create a read-only table ?
Multi-Threaded Server (Average Wait Time)
Force a rollback segment offline
Anzeige des letzten Checkpoints in den File Headern
Anzahl Query Reparsings im Shared Pool
Freier Platz im Shared Pool anzeigen
Hints und ANALYZE für Bitmap Index Zugriff
How to set date format for best Y2k Compliance ?
How to protect PL/SQL Source Code ?
How to switch to another Orcale User as DBA ?
Show object sizes in the database
Viewing The SQL Statements of the CPU-Intensive Oracle Processes
How to find out which object is corrupted
How to create an empty copy of an existing table ?
How to remove duplicate rows from a table ?
How to create a new Control file, how to document the Database ?
How to enable / disable Archiving (Online-Backup)
How many valid / invalid objects exists owned by this oracle user ?
How many extents are used by a rollback segment
Startup with a corrupted Redo-Log
Cache a Table in the SGA
Increase Speed with Large Imports
Tracking and validating objects
What is Read Consistence ?
UNRECOVERABLE unter Oracle-7
SQL-Statements im Shared Pool
Wait -Statistiken auf Datenbuffer, Rollback-Segmenten
Verfügbare V$ und X$ Tabellen, Definierte Views mit SQL-Text
User mit offenen Transaktionen
Cachen von Tabellen im Memory
Empfehlungen zu Storage Parametern
Index in anderen Tablespace verlegen während des laufenden Betriebes
Free Space in Tablespaces
Coalesce von Free Space
Identifikation von kritischen Tabellen und Indexes
Zuordnung einer Transaktion zu einem Rollback-Segment
Undokumentierte INIT.ORA Parameter
Wichtiges zu Referential Integrity und Constraints
Diverse hilfreiche Kommandos


Wo sind die Default NLS-Parameter definiert ?

Der Parameter NLS_DATE_FORMAT ist wichtig, wenn man keine expliziten Datumsmasken verwendet.
Man findet die NLS-Paramter auf Session, Instance und Database Level wie folgt:

SELECT * FROM nls_session_parameters;
SELECT * FROM nls_instance_parameters;
SELECT * FROM nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_DATE_FORMAT                DD-MON-YY
NLS_DATE_LANGUAGE              AMERICAN
NLS_CHARACTERSET               WE8ISO8859
NLS_SORT                       BINARY
NLS_CALENDAR                   GREGORIAN
NLS_RDBMS_VERSION              7.3.4.4.0

Datum Arithmetic mit Oracle

Ein oft gestellte Frage im Zusammenhang mit Oracle lautet: Wie kann man 1 Minute oder 10 Sekunden zu einem gegebenen Datum addieren oder subtrahieren. Dazu der folgende Ausschnitt aus dem Oracle SQL Manual:

You can add and subtract number constants as well as other dates from dates. Oracle interprets number constants in arithmetic date expressions as numbers of days. For example, SYSDATE + 1 is tomorrow. SYSDATE - 7 is one week ago. SYSDATE + (10/1440) is ten minutes from now. Subtracting the HIREDATE column of the EMP table from SYSDATE returns the number of days since each employee was hired. You cannot multiply or divide DATE values. Oracle provides functions for many common date operations. For example, the ADD_MONTHS function lets you add or subtract months from a date. The MONTHS_BETWEEN function returns the number of months between two dates. The fractional portion of the result represents that portion of a 31-day month.

Unsere Happy New Year 2000 Function lautet also:

set serveroutput on;
declare
  oldDate DATE;
  newDate DATE;
begin
  oldDate := to_date('31.12.1999:23:59:59','DD.MM.YYYY:HH24:MI:SS');
  newDate := oldDate + 1/86400;
  dbms_output.put_line(
  'newDate=' ||to_char(newDate,'DD.MM.YYYY:HH24:MI:SS');
end;
/

newDate=01.01.2000:00:00:00

PL/SQL procedure successfully completed.

How to implement Sleep Funktion in PL/SQL ?

Gibt es unter Oracle PL/SQL eine SLEEP Funktion ? .... Ja. Die folgende kleine Funktion versucht eine System Ressource zu erhalten, zB wenn ein Index kreiert werden soll auf einer "Busy" Tabelle. Man wartet eine gewisse Zeit und versucht es solange bis der Zähler das Maximum erreicht hat.

CREATE OR REPLACE PROCEDURE TryToGetSlot IS
  GotIt  BOOLEAN := FALSE;
  Count  NUMBER  := 0;
BEGIN
  WHILE (NOT GotIt AND NOT (Count > 10)) LOOP
    BEGIN
      -- Try to get free slot, if OK, set GotIt = TRUE
      -- else EXCEPTION will automatically fire.
      (Insert Code here)
      GotIt := TRUE;
    EXCEPTION
      WHEN OTHERS THEN
        GotIt := FALSE;
        DBMS_LOCK.SLEEP(10);
        Count := Count + 1;
    END;
  END LOOP;
END;
/

The mistery of VARCHAR2

As I understand it, industry standard VARCHAR types can store 'empty' strings, but Oracle currently does not, although it reserves the right to change the functionality of VARCHAR types to have this feature. Oracle invented the VARCHAR2 type which is the non-standard varchar (which changes empty strings to nulls), and *currently* the VARCHAR is just a synonym for VARCHAR2. Oracle advises everyone to use VARCHAR2 and not VARCHAR if they wish to maintain backward compatiblity.

The mistery of Oracle NULL's

If a column in a row has no value, then column is said to be null, or to contain a null. Nulls can appear in columns of any datatype that are not restricted by NOT NULL or PRIMARY KEY integrity constraints. Use a null when the actual value is not known or when a value would not be meaningful. Do not use null to represent a value of zero, because they are not equivalent. (Oracle currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as NULLs.) Any arithmetic expression containing a null always evaluates to null. For example, null added to 10 is null. In fact, all operators (except concatenation) return null when given a null operand. Oracle is usually careful to maintain backward compatibility, so I'd be slightly surprised to see this change even in Oracle 9 and very surprised to see it change in any version of Oracle 8.

The mistery of ROWNUM and ORDER BY

From the Oracle7 Server SQL Reference Manual: ROWNUM

Oracle7 assigns a ROWNUM value to each row as it is retrieved, before rows are sorted for an ORDER BY clause, so an ORDER BY clause normally does not affect the ROWNUM of each row. However, if an ORDER BY clause causes Oracle7 to use an index to access the data, Oracle7 may retrieve the rows in a different order than without the index, so the ROWNUMs may differ than without the ORDER BY clause.

You can use ROWNUM to limit the number of rows returned by a query, as in this example:

SELECT * FROM emp WHERE ROWNUM < 10;

You can also use ROWNUM to assign unique values to each row of a table, as in this example:

UPDATE tabx SET col1 = ROWNUM;

Note that conditions testing for ROWNUM values greater than a positive integer are always false.

For example, this query returns no rows:

SELECT * FROM emp WHERE ROWNUM > 1;

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row
to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition
false. All rows subsequently fail to satisfy the condition, so no rows are returned.

Can I replicate only certain partitions of a partitioned table ?

If so, how ? ..... Solution Summary: YOU HAVE TO REPLICATE THE ENTIRE TABLE

Solution Description: Oracle8 Server Replication: Appendix A: New Features:
"Oracle8 supports the replication of partitioned tables and indexes. ...."

Chapter: Administering A Replicated Environment Partitioned Tables and Indexes:

"With masters, you must still replicate the entire table; you cannot replicate a partition. If you want to replicate selected paritions only rather than the entire table use updatable snapshots."

Workaround: Create a Snapshot withe a WHERE Clause, which SELECT's only from one Partition.

How to set «seminfo_semmni» on Sun-Solaris / Oracle ?

Nach einer Hardware Migration unter SUN-Solaris / Oracle konnte die grosse Datenbank eines Kunden nicht mehr gestartet werden. Kernelparameter werden unter Sun-Solaris in /etc/system eingestellt. Besonders wichtig im Zusammenhang sind Parameter, welche die Anzahl Semaphoren und die Grösse des Shared Memory festlegen:

set semsys:seminfo_semmni=400

Diese Zeile limitiert die "Number of semaphore identifiers". Der Wert muss mindestens so gross wie PROCESSES in initSID.ora sein. Bei mehreren DB's die Summe aller PROCESSES. Nach der Änderung dieses Kernelparameters muss ein System-Reboot durchgeführt werden.

Help with SQL -- aus unserem Support Alltag

Frage

Wie kann ich folgende zwei SQL Statements in einem zusammenfassen ?

SELECT ms_id
  FROM msisdn
WHERE msisdn = '799990001';
INSERT INTO onp (onp_id, ms_id, request)
VALUES (1, $ms_id, 'x1');

Beispielsweise etwa so ?

INSERT INTO ONP (onp_id, ms_id, request)
VALUES (1,(SELECT ms_id FROM msisdn WHERE msisdn='799990001'),'x1');

Antwort

Die selektierten Werte eines SQL SELECT's müssen nicht zwingend aus einer Tabelle stammen wie die folgende einfache Lösung demonstriert:

INSERT INTO onp (onp_id,ms_id,request)
SELECT 1,ms_id,'x1'
  FROM msisdn
WHERE msisdn = '799990001';

Geändertes UPDATE Verhalten ab Oracle 8i

Sie möchten alle Werte eines Attributes einer Tabelle mit einem Wert aus einer anderen Tabelle erhöhen, oder etwas "mathematischer" ausgedrückt:
TABLE1.VALUE1 = TABLE1.VALUE1 + TABLE2.VALUE2 über alle Rows von TABLE1.

Beispiel: ACCOUNT und BOOKING Tabelle

CREATE TABLE booking (
acc_id NUMBER(15) NOT NULL,
amount NUMBER(15) NOT NULL
);

CREATE TABLE account (
acc_id NUMBER(15) NOT NULL,
balance NUMBER(15) NOT NULL
);

INSERT INTO account VALUES (1,100);
INSERT INTO account VALUES (2,200);
INSERT INTO account VALUES (3,300);
INSERT INTO account VALUES (4,400);
INSERT INTO account VALUES (5,500);

INSERT INTO booking VALUES (1,10);
INSERT INTO booking VALUES (2,20);
INSERT INTO booking VALUES (3,30);
INSERT INTO booking VALUES (4,40);
INSERT INTO booking VALUES (5,50);
COMMIT;

Unter Oracle 8i kann dies mit dem folgenden, einfach verständlichen SQL Statement durchgeführt werden:

UPDATE account A
SET balance = balance + (SELECT amount
                           FROM booking B
                          WHERE B.acc_id = A.acc_id)
WHERE EXISTS (SELECT 'X'
                FROM booking B
               WHERE B.acc_id = A.acc_id);

Bei allen Oracle Releases < 8.1.5 erhält man folgende Fehlermeldung:

ERROR at line 2:
ORA-00936: missing expression

Man muss in diesem Fall das SQL Statement wie folgt formulieren:

UPDATE account A
SET A.balance = (SELECT B.amount + A.balance
                   FROM booking B
                  WHERE B.acc_id = A.acc_id)
WHERE EXISTS (SELECT 'X'
                FROM booking B
               WHERE B.acc_id = A.acc_id);

Als Ergebnis erhält man in beiden Fällen das folgende Resultat:

ACC_ID BALANCE
------ -------
1      110
2      220
3      330
4      440
5      550

Wie findet man den maximalen Wert aus einer Gruppe ?

Diese simple Aufgabenstellung wird häufig an uns gestellt, da sie offenbar auf den ersten Blick einfach erscheint, dann aber meistens zu falschen Ergebnissen führt. Wir möchten diesen Sachverhalt an einem typischen Beispiel erläutern.

Gegeben ist folgende Liste mit drei Gruppen:

SELECT acq_id "AcqID",
       TO_CHAR(date_sent,'DD.MM.YYYY:HH24:MI:SS') "DateSent"
FROM ccrsettlement
WHERE date_sent IS NOT NULL
ORDER BY acq_id,date_sent DESC;

AcqID DateSent
----- -------------------
    1 02.08.1999:22:55:40
    1 29.07.1999:12:17:07
    2 09.08.1999:07:39:31  <-- Man möchte diesen Wert selektieren
    2 09.08.1999:07:25:24
    2 24.07.1999:13:53:55
    3 19.08.1999:05:53:05
    3 02.08.1999:13:58:02
    3 02.08.1999:13:52:19
    3 02.08.1999:13:42:02

Man ist versucht, als ersten Ansatz das folgende Query zu verwenden, das aber nicht das gewünschte Resultat liefert.

SELECT acq_id "AcqID",
       TO_CHAR(date_sent,'DD.MM.YYYY:HH24:MI:SS') "DateSent"
FROM ccrsettlement
WHERE acq_id = 2
AND date_sent = GREATEST(date_sent);

    AcqID DateSent
--------- -------------------
        2 09.08.1999:07:25:24
        2 09.08.1999:07:39:31
        2 24.07.1999:13:53:55

Lösung: WHERE Bedingung und GROUP BY in Subquery verpacken

Mittels folgendem Subquery kann der maximale Wert einer Gruppe herausgefunden werden. Besonders interessant dabei ist, dass man auch alle Attribute selektieren kann, da die
WHERE und GROUP BY Klausel im Subquery untergebracht ist. Dies ist oft ein Problem bei Queries der Form SELECT * FROM ... GROUP BY ..., da hier die selektieren Attribute mit den gruppierten Attributen übereinstimmen müssen (ORA-00979: not a GROUP BY expression).

SELECT acq_id "AcqID",
       TO_CHAR(date_sent,'DD.MM.YYYY:HH24:MI:SS') "DateSent"
FROM ccrsettlement
WHERE (acq_id,date_sent) IN (SELECT acq_id,max(date_sent)
                               FROM ccrsettlement
                              WHERE acq_id = 2
                              GROUP BY acq_id);

    AcqID DateSent
--------- -------------------
        2 09.08.1999:07:39:31

oder alle Attribute selektieren

SELECT *
FROM ccrsettlement
WHERE (acq_id,date_sent) IN (SELECT acq_id,max(date_sent)
                               FROM ccrsettlement
                              WHERE acq_id = 2
                              GROUP BY acq_id);

How to create a read-only table ?

Oracle-7 und 8 ermöglicht es, Tablespaces als «Read-Only» zu definieren. Manchmal möchte man jedoch nur eine einzelne Tabelle mit diesem Attribut versehen, dies natürlich für alle Applikationen.

CREATE TRIGGER tab_readonly
BEFORE DELETE OR INSERT OR UPDATE
ON emp
FOR EACH ROW
BEGIN
  RAISE_APPLICATION_ERROR(-20201, 'Table Status: READ ONLY.');
END;

How to remove «in-doubt» transactions ?

In doubt transactions may occur at double phase commit time for network, break node ... reason. Normally if a double phase commit failed, you may have some entries in views DBA_2PC_PENDING and DBA_2PC_NEIGHBORS. To force the in doubt transaction to rollback you have to use the command:

ROLLBACK FORCE <transaction_id>;

Unfortunately, sometime the entries are still there ... and you may discover in your alert<sid>.log file something like: ora-600 [18104] ... This point to bug 445604 Fixes in version Oracle 7.3.4

Now it's possible to use package DBMS_TRANSACTION to solve the problem if rollback force do not clean all entries.

Do as follow:

EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('xid');

Where 'xid' come from:

SELECT local_tran_id FROM DBA_2PC_PENDING;

Multi-Threaded Server (Average Wait Time)

If wait time gets to be excessive, then more dispatchers need to be activated. If the value increases as the number of concurrent users increases, increase the number of dispatchers. The query to determine this is.

COLUMN wait_time FORMAT 99999 heading "Average Wait"
COLUMN protocol FORMAT A60 heading "Protocol"

SELECT network protocol,
       DECODE (sum(totalq),0,' No responses',
       TO_CHAR(sum(wait)/sum(totalq)*100,'9.99') ||
       ' Sec Wait Per Response') wait_time
  FROM v$queue q, v$dispatcher d
WHERE q.type = 'DISPATCHER'
   AND  q.paddr = d.paddr
GROUP BY network;

This query should be run multiple times daily during both light and heavy usage.

Force a rollback segment offline

If you are using public rollback segments it may be difficult to force a rollback segment offline and keep it there, unless you use the following INIT.ORA paramater. This is useful when a rollback segment has been corrupted or must be dropped and rebuild.

_offline_rollback_segments = <RBS-name>

Anzeige des letzten Checkpoints in den File Headern

select FILE# file_nr,
       to_char(CHECKPOINT_TIME,'DD.MM.YYYY:HH24:MI:SS') checkpoint_time,
       name file_name
from v$datafile_header;

        Checkpoint
File#   Time                  Filename
------- -------------------- --------------------------------------
1 17.05.1999:11:29:56 /opt/oradata/RAB1/sys/RAB1_sys1.dbf
2 17.05.1999:11:29:56 /opt/oradata/RAB1/rbs/RAB1_rbs1.dbf
3 17.05.1999:11:29:56 /opt/oradata/RAB1/tmp/RAB1_temp1.dbf

Anzahl Query Reparsings im Shared Pool

Wie viele Queries müssen neu geparst werden im Vergleich zu den total Ausgeführten ?

select sum(pins) "SQL Executions",
       sum(reloads) "Necessary SQL Reparsing"
from v$librarycache;

Executions Necessary SQL Reparsing
---------- -----------------------
3436                              9

Freier Platz im Shared Pool anzeigen

select * from v$sgastat
where name = 'free memory';

Hints und ANALYZE für Bitmap Index Zugriff

Damit ein Bitmap Index Zugriff vom COST based Optimizer favorisiert und ausgeführt wird müssen die Optimizer Statistiken korrekt aufgesetzt sein. Es reicht jedoch nicht aus, nur das entsprechende Schema einem ANALYZE zu unterziehen, es muss auf das entsprechende Table-Column ausgeweitet werden. Der Hint muss (wie im Tuningguide beschrieben) mit der exakten Tabellenbezeichnung gemacht werden, d.h. wenn ein Tablealias angewendet wird, MUSS im Hint auch die Tabelle mit dem Alias referenziert werden.

ANALYZE für Bitmap Index Zugriff auf dem Column STATUS

ANALYZE INDEX bitmap_index ESTIMATE STATISTICS;
ANALYZE TABLE xyz ESTIMATE STATISTICS FOR COLUMNS STATUS;

Angabe des Hint

SELECT /*+ INDEX(tab_alias bitmap_index) */ ......,

How to set date format for best Y2k Compliance ?

Oracle suggest the date format NLS_DATE_FORMAT='YY-MON-RR' in order to overwrite the default of 'YY-MON-YY'. This way, any program that was developed assuming the default date format of 'YY-MON-YY', will work properly. To facilitate year 2000 compliance for applications that use the two-digit year format the Oracle7 Server and Oracle8 Server provides a special year
format mask 'RR'.

The Oracle RDBMS has always stored dates using a four-digit year (the ‘OraDate’ format), hence customers using the DATE data type should not have any application level problems. To facilitate year 2000 compliance for applications that use the two-digit year format the Oracle7 Server and Oracle8 Server provides a special year format mask ‘RR’. Using the ‘RR’ format, any two-digit year entered will be converted thus:

Current Year: Last Two Digits

Two-Digit Year Specified

Year ‘RR’ Format Returns

0-49

0-49

Current Century

50-99

0-49

One Century after current

0-49

50-99

One Century before current

50-99

50-99

Current Century

Therefore, regardless of the current century at the time the data is entered the 'RR' format will ensure that the year stored in the database is as follows:

If the current year is in the second half of the century (50 - 99)

  • and a two-digit year between ‘00’ and ‘49’ is entered: This will be stored as a next century year.
    E.g. ‘02’ entered in 1996 will be stored as ‘2002’.
  • and a two-digit year between ‘50’ and ‘99’ is entered: This will be stored as a current century year.
    E.g. ‘97’ entered in 1996 will be stored as ‘1997’.

If the current year is in the first half of the century (00 - 49)

  • and a two-digit year between ‘00’ and ‘49’ is entered: This will be stored as a current century year.
    E.g. ‘02’ entered in 2001 will be stored as ‘2002’.
  • and a two-digit year between ‘50’ and ‘99’ is entered: This will be stored as a previous century year.
    E.g. ‘97’ entered in 2001 will be stored as ‘1997’.

The ‘RR’ date format is available for inserting and updating DATE data in the database. It is not required for retrieval/query of data already stored in the database as Oracle has always stored the YEAR component of a date in it’s four-digit form.

How to protect PL/SQL Source Code ?

PL/SQL V-2.2, available with Oracle 7.2, implements a binary wrapper for PL/SQL to protect the source code. This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original). This way you can distribute software without having worry about exposing your proprietary algorithms and methods. SQLPLUS and SVRMGRL will still understand and know to execute such scripts. Just be careful, there is no "decode" command available, the syntax is:

wrap iname=myplsqlsource.sql oname=myplsqlsource.plb  (UNIX)
wrap80 iname=myplsqlsource.sql oname=myplsqlsource.plb   (NT)

How to switch to another Orcale User as DBA ?

Many batch jobs must change a user password to a "dummy" password to login as this user temporarilly. The problem is always the encrypted password which cannot be de-crypted so that the password can be reset to the original. There is a way to do this, using the VALUES keyword, this is exactly what IMPORT does when it must create users. We show you how to perform this task using the famous account of SCOTT/TIGER. If a DBA doesn't know the password "TIGER", he can temporarilly change this password to another password, logon as user SCOTT, and set the password back after the jobs as user SCOTT is done. Follow the instructions below:

  • Connect as a DBA User and get the encypted passord for User SCOTT

sqlplus system/manager
select password from dba_users where username = 'SCOTT';


PASSWORD
------------------------------
F894844C34402B67

  • Change the password for User SCOTT temporarilly

alter user scott identified by hello;

  • Connect as User SCOTT with the temporary password 'hello' and do the necessary jobs as SCOTT

sqlplus scott/hello

  • Reset the original password for SCOTT as a DBA

alter user SCOTT identified by VALUES 'F894844C34402B67';

Show object sizes in the database

It's often very interesting to know the size of each object in the database. For example if you need to pin a package, trigger in the memory. Note, that NOT the size of a table including the rows will be shown, but the size of the table itself.

Object                                           Source      Parsed      Code
Owner    Name                   Type                Size       Size      Size
-------- --------------------- -------------   ---------  --------- ---------
PPB      CDR_TOOL               PACKAGE              788        388       107
PPB      CDR_TOOL               PACKAGE BODY        2078          0      1736
PPB      CLEANUP_PARTIAL_CDR    PROCEDURE           3382       6296      4790
PPB      CDR                    TABLE                  0       2091         0
PPB      TRG_CLT_MSG            TRIGGER                0          0       453

Show Source

Viewing The SQL Statements of the CPU-Intensive Oracle Processes

First get the top 10 CPU-intensive Oracle processes on the operating system with the first column giving the %CPU used, the second column unix PID, the third column USER , the fourth column TERMINAL, and the last column Unix PROCESS (works only for UNIX).

ps -eaf -o pcpu,pid,user,tty,comm | grep ora |
    grep -v \/sh | grep -v ora_ | sort -r | head -20

Now you can specify the found PID in the following SQL-Statement:

column username format a9
column sql_text format a70

SELECT a.username, b.sql_text
  FROM v$session a, v$sqlarea b, v$process c
WHERE (c.spid = '&PID' OR a.process = '&PID')
   AND a.paddr = c.addr
   AND a.sql_address = b.address
/

How to find out which object is corrupted ?

During a SELECT statement, we get the error: ORA-1578: file#1 block#3840 corrupted Oracle block.

It is telling us, that the corruption took place in file#1. This always a SYSTEM tablespace file. This may mean that the corrption problem may only be resolved with the recreation of the database follwed by a full database import. Only if this is the case, do a full database export with the database unavailable to users immediately followed by the database recreation and import in order to prevent loss of data. Now how to find out exactly which object is corrupted. It may be a rollback segment or an index that can simply be recreated. Let's see how we can find out this.

Connect to an DBA account:

SELECT segment_type, segment_name
  FROM dba_extents
WHERE file_id = 1
   AND block_id < 3840
   AND block_id + blocks >= 3840;
 
SEGMENT_TYPE      SEGMENT_NAME
----------------- ----------------------------------------
INDEX             I_SOURCE1

or use:

SELECT segment_type, segment_name
  FROM dba_extents
WHERE file_id = 1
   AND 3840 BETWEEN block_id AND block_id + blocks -1;

SEGMENT_TYPE      SEGMENT_NAME
----------------- ----------------------------------------
INDEX             I_SOURCE1

This is a simulated example, altough file#1 ALWAYS belongs to the SYSTEM tablespace, block #3840 could be anything. Lower block numbers in the #1 file are likely bootstrap segments. In this case I_SOURCE is corrupted, this is a SYS Index that cannot be dropped. In this case only recreating the database will resolve the problem. If it is a table it must be understood that the data within the corrupted blocks is lost. You can try to save as much of the data in the corrupted table before the bad block and create a new table around the corrupted part. Get the rowid's around the corrupted block with:

SELECT ROWIDTOCHAR(rowid) FROM <table-name>;

How to create an empty copy of an existing table ?

CREATE TABLE new_table AS
  SELECT * FROM old_table
   WHERE 1 = 2;

Table created.

How to remove duplicate rows from a table ?

SQL> select * from address;

NAME     VORNAME  BIRTH
-------- -------- ----
zahn     martin   1954
zahn     martin   1954
hodler   kurt     1962
wyss     bruno    1965

Get the Duplicates with:

SELECT name,vorname FROM address
GROUP BY name,vorname
HAVING COUNT(*) > 1;

NAME     VORNAME
-------- --------
zahn     martin
 
Delete the Duplicates with
 
DELETE from address A
WHERE (A.name, A.vorname, A.birth) IN
    (SELECT B.name, B.vorname, B.birth FROM address B
      WHERE A.name = B.name AND A.vorname = B.vorname
        AND A.birth = B.birth AND A.rowid > B.rowid);

1 row deleted.

How to create a new Control file, how to document the Database ?

Connect as SYSTEM or SYS and do:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
SELECT * FROM v$dbfile;
SELECT * FROM v$logfile;

The TRACE File for the Controlfile can be found in the directory defined by the init.ora Parameter: core_dump_dest.

How to enable / disable Archiving (Online-Backup)

Check that the following parameters are set in init<SID>.ora:

LOG_ARCHIVE_DEST
LOG_ARCHIVE_FORMAT
LOG_ARCHIVE_START = TRUE/FALSE

Enable or disable Archiving with

svrmgrl
connect internal oder connect <user> as SYSDBA
startup mount;
archive log list;

   Database log mode No Archive Mode
   Automatic archival Disabled
   Archive destination /oracle/v805/oradata/arc
   Oldest online log sequence 340
   Current log sequence 341
alter system archive log start / stop;
archive log list;
   Database log mode No Archive Mode
   Automatic archival Enabled
   Archive destination /oracle/v805/oradata/arc
   Oldest online log sequence 340
   Current log sequence 341
alter database archivelog / noarchivelog;
archive log list;
   Database log mode Archive Mode
   Automatic archival Enabled
   Archive destination /oracle/v805/oradata/arc
   Oldest online log sequence 340
   Next log sequence to archive 341
   Current log sequence 341
alter database open;

Manually archives the current log file group, forcing a log switch

alter system archive log current;

Manually archives all full unarchived redo log file groups

alter system archive log all;

How many valid / invalid objects exists owned by this oracle user ?

Often we should know, how many valid and invalid objects an oracle user ows. Specially if we compare a schema on two different databases.

SELECT DISTINCT (object_type) object, status, COUNT(*)
FROM user_objects
GROUP BY object_type, status;

OBJECT        STATUS   COUNT(*)
------------- ------- ---------
INDEX         VALID          95
PACKAGE       INVALID         1
PACKAGE BODY  INVALID         1
PROCEDURE     INVALID         1
PROCEDURE     VALID           2
SEQUENCE      VALID          20
TABLE         VALID          63
TRIGGER       INVALID         3
TRIGGER       VALID           1

How many extents are used by a rollback segment

If you are running large transactions it is important to have enough rollback segment space. To check the current allocated number of extents and the max number of extens in a rollback segment, use the following query:

SELECT COUNT(*) FROM dba_extents
 WHERE segment_name = 'SYSTEM'
   AND segment_type = 'ROLLBACK';

COUNT(*)
---------
4

SELECT max_extents FROM dba_rollback_segs
WHERE segment_name = 'SYSTEM';

MAX_EXTENTS
-----------
249

DBA_EXTENTS: This view lists the extents comprising all segments.
V$ROLLSTAT: This view contains segment statistics.
DBA_ROLLBACK_SEGS: This view contains descriptions of the rollback segments.

Startup with a corrupted Redo-Log

There may be situations where you need to startup with a corrupted redo log. In this case there is an init.ora parameter that allows this: _ALLOW_RESETLOGS_CORRUPTED=TRUE. However, this will startup even if it results in corrupted data, so only use it if you have to and only to startup long enough to get an export of critical data. Use only under the guidance of Oracle Support.

Cache a Table in the SGA

In order to cache a table in the SGA it has to be smaller than CACHE_SIZE_THRESHOLD as set in the init.ora file. However, the cost based analyzer doesn't take table cacheing into account when doing optimization so you may want to force the table using hints.

Increase Speed with Large Imports

For large imports, import with INDEXES=N and build your indexes seperately to increase import speed.

Tracking and validating objects

Oracle can use event setpoints in the initialization file for tracking and validation of objects. For example:

event="10210 trace name context forever, level 10" sets integrity checking for tables
event="10211 trace name context forever, level 10" sets integrity checking for indexes
event="10231 trace name context forever, level 10" tells a full table scan to skip any corrupted blocks

The last event setting (10231) will allow export to extract all data, except the corrupted block, from a table. You should only use these under guidance from Oracle support.

Read Consistence

Garantiert die konsistente Sicht der Daten, während des gleichzeitigen Schreibens von Transaktionen durch andere Sessions.

SET TRANSACTION READ ONLY;
SELECT ....
SELECT ....
SELECT ....
COMMIT oder ROLLBACK;

Die drei SELECT's sind lesekonsistent, das heisst man sieht die Daten wie sie zum Zeitpunkt des Kommandos SET TRANSACTION READ ONLY waren. In PL/SQL kann die Funktion DBMS_TRANSACTION.READ_ONLY; verwendet werden.

UNRECOVERABLE unter Oracle-7

Bei folgenden Statements möglich:

CREATE TABLE xxx AS SELECT * FROM xxx UNRECOVERABLE;
CREATE INDEX xxx ON xxx(xxx) UNRECOVERABLE;
ALTER INDEX xxx REBUILD UNRECOVERABLE;

SQL-Statements im Shared Pool

SELECT sql_text, executions,
  first_load_time, invalidations
  FROM v$sqlarea;

Aktuelle Befehle in der SGA

SELECT COUNT(*) FROM v$sqlarea;

Anzahl Befehle in der SGA

SELECT sql_text FROM v$sqlarea
WHERE users_executing > 0;

Gerade in Ausführung

ALTER SYSTEM FLUSH SHARED POOL;

Alle SQL-Statements aus dem Shared Pool entfernen, schafft Platz für grosses Statement.

Wait -Statistiken auf Datenbuffer, Rollback-Segmenten

SELECT * FROM v$waitstat;
TIMED_STATISTICS = TRUE muss gesetzt sein, zB mit
ALTER SYSTEM SET TIMED_STATISTICS = TRUE;

Verfügbare V$ und X$ Tabellen, Definierte Views mit SQL-Text

SELECT * FROM v$fixed_table;
SELECT * FROM v$fixed_view_definition;

User mit offenen Transaktionen

SELECT username, machine, terminal, program
FROM v$session, v$transaction
WHERE addr = taddr;

Cachen von Tabellen im Memory

Normalerweise werden Blöcke, welche über einen Fulltable-Scan gelesen werden nicht in der SGA gehalten, sondern gleich wieder entfernt. Dies kann für "Lookup-Tabellen" verhindert werden, alle Rows bleiben dann auch nach einem Fulltablesan in der SGA.

ALTER TABLE emp CACHE;
ALTER TABLE emp NOCACHE;

Empfehlungen zu Storage Parametern

- PCTFREE und PCTUSED müssen zusammen weniger als 100% ergeben
- PCTFREE für normale UPDATE-Aktivitäten auf 20% setzen. Wenn keine UPDATEs stattfinden kleiner.
- PCTINCREASE immer 0 wählen.

Index in anderen Tablespace verlegen während des laufenden Betriebes

ALTER INDEX xxx REBUILD
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0)
PCTFREE 5
TABLESPCAE newts;

Free Space in Tablespaces

Der freie Platz und dessen Fragmentierung kann im Oracle Tablespace Manager oder mit einem einfachen SQL-Script angezeigt werden. Klicken Sie hier um den Source-Code zu sehen.

Coalesce von Free Space

Wenn für eine Tabelle ein Extent benötigt wird, so sucht Oracle die Liste der freien Extents ab. Die erste ausreichend grosse Lücke wird alloziert. Wird keine ausreichend grosse Lücke gefunden, so wird die Liste nochmals abgesucht, ob es benachbarte freie Lücken gibt, welche zsammen ausreichend gross sind. Falls noch immer keine ausreichend grosse Lücke gefunden werden kann, so wird die Fehlermeldung "failed to allocate extent of size <bytes> in tablespace <tablespace>" generiert (falls nicht ein Datafile mit AUTOEXTEND vergrössert werden kann).

Zusammenhängende freie Lücken können zusammengefügt werden mittels:

ALTER TABLESPACE xxx COALESCE;

Stored Procedure für alle Tablespaces:

CREATE OR REPLACE PROCEDURE coats IS

vCursor   INTEGER   := DBMS_SQL.Open_Cursor;
vDummy    INTEGER;

BEGIN
  FOR tsrec IN (SELECT tablespace_name,
    TO_CHAR(percent_blocks_coalesced,'990.00') percent_blocks_coalesced
    FROM dba_free_space_coalesced
    WHERE percent_blocks_coalesced < 100) LOOP

    DBMS_SQL.Parse(vCursor,'ALTER TABLESPACE '||tsrec.tablespace_name
    ||' coalesce', DBMS_SQL.Native);
    vDummy := DBMS_SQL.Execute(vCursor);

    dbms_output.put('Tablespace ');
    dbms_output.put(RPAD(tsrec.tablespace_name,30,'.'));
    dbms_output.put(': ');
    dbms_output.put(tsrec.percent_blocks_coalesced);
    dbms_output.new_line;
  END LOOP;
  DBMS_SQL.Close_Cursor(vCursor);
END;
/

Identifikation von kritischen Tabellen und Indexes

Wenn der Platz in einem Tablespace knapp ist können Tabellen oder Indexes keine weiteren Extents allozieren. Dies kann auch eintreten, wenn die Extents zu gross spezifiziert wurden oder PCTINCREASE grösser als 0 gewählt wurde. Man kann auf einfache Weise testen, ob ein weiterer Extent alloziert werden kann.

ALTER TABLE emp ALLOCATE EXTENT;
ALTER TABLE emp DEALLOCATE UNUSED;

SQL-Script (V7)
SQL-Script (V8)

Zeigt Tabellen, welche kein Extent mehr allozieren könnten, also als kritisch einzustufen sind

SQL-Script (V7)
SQL-Script (V8)

Zeigt Statistiken zu Füllungsgrad einer Tabelle. Insbesondere  kann verifiziert werden wie viele Rows noch eingefügt werden können ohne einen zusätzlichen Extent zu allozieren.

SQL-Script (V7)
SQL-Script (V8)

Zeigt Indizes, welche keine weiteren Extents mehr allozieren könnten, also als kritisch einzustufen sind

Zuordnung einer Transaktion zu einem Rollback-Segment

Zur Erinnerung sei hier nochmals darauf hingewiesen dass:

- Eine Transaktion kann nur ein Rollback-Segment verwenden
- Ein Rollback-Segment kann von mehreren Transaktionen benutzt werden.

Für lang dauernde Transaktionen ist es oft ein Vorteil ein spezielles Rollback-Segment zu erstellen und dieses der Transaktion zuzuweisen.

SET TRANSACTION USE ROLLBACK SEGMENT rbs01;    /* SQL*Plus */
DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT ('rbs01');  /* PL/SQL */

Mit COMMIT oder ROLLBACK wird die Zuweisung wieder aufgehoben.

Undokumentierte INIT.ORA Parameter

Mit dem folgenden Query können unter dem User SYS die undokumentierten INIT.ORA Parameter abgefagt werden:

set pages 100
col "Parameter" format a40 trunc
col "Session Value" format a17 trunc
col "Instance Value" format a17 trunc
select a.ksppinm  "Parameter",
       b.ksppstvl "Session Value",
       c.ksppstvl "Instance Value"
  from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
   and substr(ksppinm,1,1)='_'
order by a.ksppinm
/

Wichtiges zu Referential Integrity und Constraints

Constraints immer mit einem Namen versehen, da ansonsten nichtssagende Namen (SYS_cnnnn) in Fehlermeldungen erscheinen. Ein Debugging wird dadurch erschwert.

Default Constraint

CREATE TABLE order
 (ord_id  NUMBER NOT NULL,
  ord_date DATE DEFAULT SYSDATE);

Ergänzt bei INSERTS ein Attribut mit einem Default-Wert, falls das INSERT keinen Wert definiert. Sequences können nicht als Default-Werte verwendet werden. Grundsätzlich sollten Default-Werte mit DB-Trigger implementiert werden.

NOT NULL Constraint

CREATE TABLE order
  (ord_id  NUMBER
   CONSTRAINT nn_ord_id NOT NULL,
   ord_date DATE DEFAULT SYSDATE);

Überprüft, dass ein Attribut einen Wert hat.

Nicht verwenden:

CREATE TABLE mm_order
  (ord_id  NUMBER
   CONSTRAINT nn_ord_id
   CHECK (ord_id IS NOT NULL),
   ord_date DATE DEFAULT SYSDATE);

NOT NULL Constraints sollten nicht mit dem Check-Constraint definiert werden, da NOT NULL mit DESCRIBE nicht angezeigt wird (siehe Beispiel).

CHECK Constraint

CREATE TABLE order
  (ord_id  NUMBER
   CONSTRAINT ck_ord_id
   CHECK ord_id IN (0,1,2,3),
ord_date DATE DEFAULT SYSDATE);

Check Constraints dienen vorallem zum Prüfen von Ranges und Domains. Es können keine Subqueries verwendet werden. Andere Attribute können angesprochen werden, zB CONSTRAINT ck_sal CHECK (NVL(sal,0) < com);

UNIQUE Constraint

Default Index (nicht verwenden)

CREATE TABLE order
  (...
   ord_name VARCHAR2(20)
   CONSTRAINT un_ord UNIQUE,
   ...);

Prüfen, dass ein Attribut eindeutig ist, das Attribut darf NULL Werte haben. UNIQUE Attribute können von Foerign Keys referenziert werden. Beim Erstellen des Default Index (Beispiel 1) wird automatisch ein UNIQUE Index angelegt. Disable und Drop Constreint löschen den Index. Ein UNIQUE Constraint kann auch aus mehreren Attributen bestehen.

Constraint und Index getrennt erstellen

CREATE UNIQUE INDEX idx_empno
ON emp(empno)
STORAGE (INITIAL 50K NEXT 50K)
TABLESPACE tab;

ALTER TABLE emp
ADD CONSTRAINT idx_empno
UNIQUE (empno);

Der unique Index und das Constraint können getrennt voneinander erstellt werden. Es ist dabei zu beachten, dass der Indexname und Constraintname identisch sind, im Beispiel idx_ename. Damit können Probleme beim EXP/IMP vermieden werden. Indexe sollten wenn möglich in einem anderen Tablespace als die Tabelle angelegt werden.

Verwenden von USING INDEX

CREATE TABLE order
  (...
   ord_name VARCHAR2(20)
   CONSTRAINT un_ord
   UNIQUE USING INDEX
   STORAGE (INITIAL 50K NEXT 50K)
   TABLESPACE tab,
   ...);

Dies ist die bevorzugte Methode um ein unique Attribut zu erstellen mit der Angabe des unique Index und dessen Storage Parameter.

PRIMARY Key Constraint

Default Index (nicht verwenden)

CREATE TABLE order
  (...
   ord_name VARCHAR2(20)
   CONSTRAINT un_ord PRIMARY KEY,
   ...);

Das PRIMARY KEY Constraint verhält sich sehr ähnlich wie das UNIQUE Constraint. Im Gegensatz zu diesem darf ein Primary Key aber keine NULL-Werte haben. Ein Primary Key kann auch aus mehreren Attributen bestehen.

Constraint und Index getrennt erstellen

CREATE UNIQUE INDEX idx_empno
ON emp(empno)
STORAGE (INITIAL 50K NEXT 50K)
TABLESPACE tab;

ALTER TABLE emp
ADD CONSTRAINT idx_empno
PRIMARY KEY (empno);

Der unique Index und das Constraint können getrennt voneinander erstellt werden. Es ist dabei zu beachten, dass der Indexname und Constraintname identisch sind, im Beispiel idx_ename. Damit können Probleme beim EXP/IMP vermieden werden. Indexe sollten wenn möglich in einem anderen Tablespace als die Tabelle angelegt werden.

Verwenden von USING INDEX

CREATE TABLE order
  (...
   ord_name VARCHAR2(20)
   CONSTRAINT un_ord
   PRIMARY KEY USING INDEX
   STORAGE (INITIAL 50K NEXT 50K)
   TABLESPACE tab,
   ...);

Dies ist die bevorzugte Methode um ein Primary Key zu erstellen mit der Angabe des unique Index und dessen Storage Parameter.

FOREIGN Key Constraint

CREATE TABLE emp
  (empno NUMBER(5) NOT NULL,
   ename VARCHAR2(30),
   deptno NUMBER(2) NOT NULL
   CONSTRAINT fk_depno
   REFERENCES dept(deptno));

Sicherstellen der Master-Detail Beziehung

ON DELETE CASCADE

CREATE TABLE emp
  (empno NUMBER(5) NOT NULL,
   ename VARCHAR2(30),
   deptno NUMBER(2) NOT NULL
   CONSTRAINT fk_depno
   REFERENCES dept(deptno)
   ON DELETE CASCADE);

Mittels ON DELETE CASCADE wird auf der Foreign Key Seite angegeben, dass beim Löschen eines Master-Records auch alle Child-Records gelöscht werden sollen.

Administration von Constraints

ALTER TABLE emp DISABLE PRIMARY KEY;

Entspricht einem DROP INDEX

ALTER TABLE dept DISABLE PRIMARY KEY CASCADE;

Um einen Primary Key oder ein Unique Constraint, welches Teil des Foreign Keys sind zu disablen.

ALTER TABLE emp ENABLE PRIMARY KEY
USING INDEX STORAGE
   (INITIAL 100K NEXT 100K)
   TABLESPCA tab;

Erstellen eines Primary Key an bestehender Tabelle.

ALTER TABLE emp DROP PRIMARY KEY;
ALTER TABLE emp DROP CONSTRAINT emp_pk;

Löschen eines Constraints

ALTER INDEX emp_pk REBUILD
  UNRECOVERABLE
  PARALLEL (DEGREE 2)
  TABLESPACE tab
  PCTFREE 5
  STORAGE (INITIAL 1M
  NEXT 500K
  MINEXTENTS 1
  MAXEXTENTS UNLIMITED
  PCTINCREASE 0);

Rebuild eines Indexes während dem laufenden Betrieb.

Anzeigen von inkonsistenten Daten

Ein ENABLE Constraint lockt die gesamte Tabelle, bis die Constraints aktiviert sind. Falls inkonsistente Daten vorhanden sind, so wird das Constraint nicht aktiviert. Inkonsistente Daten werden in die Exceptions-Tabelle EXCEPTIONS abgespeichert. Diese Tabelle muss mit dem Script utlexcpt.sql (Oracle-Installation) erstellt werden.

ALTER TABLE dept ENABLE PRIMARY KEY
EXCEPTIONS INTO EXCEPTIONS;

SELECT * FROM EXCEPTIONS;

Wichtiges zu Constraints

- Ist ein ON DELETE CASCADE gesetzt, so ist der Index auf dem Foreign Key wesentlich für die Performance.
- Für eine optimale Indexierung auf Primary Keys immer die USING INDEX Clause verwenden.
- Ein CRETAE TABLE AS SELECT * FROM .... verliert alle Constraints ausser NOT NULL.
- Vorsicht bei DISABLE CONSTRAINT, da die Indexes gedroppt werden.
- Constraints immer selber benennen (leichter lesbare Fehlermeldungen).

Diverse hilfreiche Kommandos

SELECT name FROM v$database;
SELECT * FROM v$datafile
SELECT * FROM v$logfile
SELECT * FROM global_name;
ALTER TABLESPACE tab OFFLINE NORMAL;
ALTER TABLESPACE tab OFFLINE IMMEDIATE;

Datenbank Name abfragen.
Welche DB-Files sind vorhanden
Welche Logfiles sind vorhanden
Domain Namen abfragen.
Tablespace offline setzen
Tablespace sofort offline setzen