|
 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
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');
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.
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;
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);
ERROR at line 2:
ORA-00936: missing expression
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);
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.
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
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:
|
|
|
|
|
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 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
|
|
