|
![]() 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:
sqlplus system/manager
select password from dba_users where username = 'SCOTT';
PASSWORD
------------------------------
F894844C34402B67
alter user scott identified by hello;
sqlplus scott/hello
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 Scriptutlexcpt.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
|
|
浙公网安备 33010602011771号