Tools

Squirrel

-Xmx500m

SQL

SQL Links

• SQL Oracle Tutorial
• SQL in 21 Tagen
• SQL Tutorial
• Einführung in SQL
• Oracle SQL Befehle

SQL Data Retrieval

SELECT

SELECT * FROM foo WHERE myid=42;
SELECT * FROM foo WHERE myid=42 AND myotherid=12;
SELECT foo.myid,bar.otherid from foo,bar;
SELECT * FROM foo WHERE mytime BETWEEN
  TO_DATE('2007-07-01','yyyy-mm-dd') AND
  TO_DATE('2007-07-14 22:09:59','yyyy-mm-dd HH24:MI:SS');
SELECT id, foo FROM bar ORDER BY foo DESC, id ASC;

SELECT f.numbers AS numbers2,
       f.*
  FROM foo f

SELECT DISTINCT

SELECT DISTINCT myname FROM foo;

SELECT CASE

select foo, CASE
             WHEN b.x<0 THEN 'S'
             WHEN b.x>0 THEN 'G'
            ELSE 'E'
            END AS tg
            from bar b where IS NOT NULL b.x;

SELECT DECODE

SELECT decode(A,
              42, 'Mr Bar',
               3, 'Mrs Bar'
             ) FROM  foo

INTERSECT

SELECT myname FROM foo INTERSECT SELECT myname FROM bar;

MINUS

SELECT myname FROM foo
MINUS
SELECT myname FROM bar;

Alias

SELECT foo.nm "Name", SUM(foo.nr) "Total Number" FROM foo ...;

JOIN

• INNER JOIN
  Liefert alle Werte aus der ersten und der zweiten Tabelle bei der die Vergleichsschlüssel übereinstimmen. Ist der Vergleichsschlüssel in einer der beiden Tabellen leer wird er nicht angezeigt.
  Im obigen Beispiel erscheint also weder Frank, noch John, noch 33 wenn der Vergleichsschlüssel myid ist
• NATURAL JOIN
  Wie INNER JOIN, nur dass man den Vergleichsschlüssel nicht angeben muss wenn es in beiden Tabellen eine Spalte gleichen Namens gibt (im obigem Beispiel myid).
  
• LEFT OUTER JOIN
  Findet man zu einem Eintrag in der rechten Tabelle keinen Eintrag in der linken Tabelle würde er bei einem normalen JOIN gar nicht angezeigt. Da man dabei Einträge verlieren würde, die man möglicherweise sehen möchte, werden solche Einträge bei einem LEFT OUTER JOIN angezeigt und die Spalten aus der linken Tabelle werden mit null aufgefüllt. Ein typischer Fall, in dem kein passender Eintrag in der linken Tabelle gefunden werden kann, ist wenn die Vergleichspalte in einer der beiden Tabellen (oder sogar in beiden) null ist.
• RIGHT OUTER JOIN
  Wie LEFT OUTER JOIN, nur dass Einträge aus der linken Tabelle auch dann angezeigt werden wenn es keinen passenden Eintrag in der rechten Tabelle gibt.
  

SELECT name FROM foo             JOIN bar WHERE foo.myid=bar.myid;
SELECT name FROM foo NATURAL     JOIN bar;
SELECT name FROM foo NATURAL     JOIN bar WHERE foo.myid=42;
SELECT *    FROM foo LEFT  OUTER JOIN bar;
SELECT *    FROM foo RIGHT OUTER JOIN bar ON foo.myid=bar.myid;

SELECT a.name, SUM(a.nr) a.nr FROM foo a, bar b WHERE a.name    = b.name(+) GROUP BY a.name;
SELECT a.name, SUM(a.nr) a.nr FROM foo a, bar b WHERE a.name(+) = b.name    GROUP BY a.name;

UNION

SELECT myname FROM foo UNION SELECT myname FROM bar;

ORA-00932: inconsistent datatypes: expected - got CLOB

LIKE

SELECT * FROM x WHERE name LIKE 'Peter%';
SELECT * FROM x WHERE name LIKE 'Peter_'

GROUP BY

SELECT MYID1,sum(MYYESNOCOL) AS MYSUM1 FROM MYTABLE1 WHERE MYTABLE1.MYID1=MYTABLE1.MYID2 GROUP BY MYTABLE1.MYYESNOCOL;

HAVING

• Reihen mit bestimmten Namen ausgewählt werden (WHERE Bedingung)
• alle mit gleichem Namen gruppiert werden (GROUP BY)
• pro gruppierten Element eine Summe gebildet werden (SUM)
• nur die Summen angezeigt werden die eine bestimmte Größe haben (HAVING)

SELECT myname, SUM(myid) FROM foo WHERE ( myname='ABC' or myname='DEF' )
 GROUP BY myname HAVING SUM(myid) > 300;

GROUP BY jeweils nur eine beliebige Zeile ausgeben

SELECT a, MIN(b), MIN(c) FROM foo group by a

KEEP (DENSE_RANK FIRST ORDER BY B,C)

SELECT a,
       MIN(b) KEEP (DENSE_RANK FIRST ORDER BY b,c) "B",
      MIN(c) KEEP (DENSE_RANK FIRST ORDER BY b,c) "C"
FROM foo
GROUP BY a;

Innere Abfrage

SELECT SUM(mynr) FROM foo
 WHERE myname IN (SELECT myothername FROM bar WHERE myid > 5 );
UPDATE foo SET foo.price=(SELECT bar.price FROM bar WHERE foo.id=bar.id);

UPDATE foo f SET (id,      name) =
(
 SELECT           b.number,b.description
 FROM bar b
 WHERE f.ref = s.ref
)

IN Bedingung an mehrere Spalten gleichzeitig stellen

SELECT * FROM foo WHERE ( a=42 AND b=9 ) OR ( a=3 AND b=1)

SELECT * FROM foo WHERE a || '_' || b IN ( '42_9', '3_1' )

In einem CLOB suchen

SELECT * FROM BLOBTEST where       MYCLOB like '%Bar%'

         SELECT * FROM BLOBTEST where instr(MYCLOB, 'Bar')>0

Aktuelles Datum beziehen

SELECT SYSDATE FROM dual;

Anzahl der Ergebniszeilen beschränken

SELECT a FROM foo LIMIT 20;

SELECT a FROM foo WHERE ROWNUM<=20;

DML Data Manipulation Language

INSERT, UPDATE, DELETE

INSERT INTO MYTABLE1 (MYCOL1, MYCOL2, ...) VALUES (MYVALUE1, MYVALUE2, ...);
DELETE FROM MYTABLE1 WHERE MYCOL1 = 'MYVALUE1';
UPDATE      MYTABLE1 SET MYCOL1='MYVALUE3', MYCOL2='MYVALUE4' WHERE MYCOL2='MYVALUE2';

Neue Einträge aus vorhandenen erzeugen

INSERT INTO foo (a, b,         c)
     SELECT      a, '42' as b, c  FROM bar WHERE a IS NOT NULL

MERGE (REPLACE)

MERGE INTO
  TABLE_DEST foo
USING
  TABLE_SOURCE bar
ON (FOO.id = bar.id)
WHEN     MATCHED THEN UPDATE SET foo.txt = bar.txt
WHEN NOT MATCHED THEN INSERT (id, txt) VALUES (bar.id, bar.txt);

Ersetzen von String durch andere

SELECT replace('TestEn', 'e', 'a') FROM dual

SELECT REGEXP_REPLACE('TestEn','e','a', 1, 0, 'i') from dual

Die ersten oder letzten Einträge bezüglich einer Sortierung

SELECT a, b FROM (
 SELECT
 a,b,
 RANK() OVER (ORDER BY b) r
 FROM
 FOO.BAR
)
 WHERE
 r<4;

DDL Data Definition Language

CREATE

CREATE       DATABASE MYDB1;
CREATE TABLE MYTABLE1 ( MYCOL1 SERIAL, MYCOL2 TEXT NOT NULL);

CREATE TABLE IPAdressen (IPid         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,IPadr      varchar(255) NOT NULL UNIQUE);
CREATE TABLE Email      (EMAILid      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,EMAILadr   varchar(255) NOT NULL UNIQUE);
CREATE TABLE URL        (URLid        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,URLadr     varchar(255) NOT NULL UNIQUE);
CREATE TABLE PATH       (PATHid       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,PATHtxt    varchar(255) NOT NULL UNIQUE);
CREATE TABLE PUBTYPE    (PUBTYPEid    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,PUBTYPEtxt varchar(255) NOT NULL UNIQUE);
CREATE TABLE LANG       (LANGid       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,LANGtxt    varchar(2)   NOT NULL UNIQUE);
CREATE TABLE PAGEID     (PAGEIDid     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,PAGEIDtxt  varchar(255) NOT NULL UNIQUE);
CREATE TABLE UAGENT     (UAGENTid     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,UAGENTtxt  varchar(255) NOT NULL UNIQUE);
CREATE TABLE HTMLERRLOG (HTMLERRLOGid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY, PATHid BIGINT UNSIGNED NOT NULL, 
     URLid BIGINT UNSIGNED NOT NULL, IPid BIGINT UNSIGNED NOT NULL, HTMLERR BIGINT UNSIGNED NOT NULL, PAGEIDid BIGINT UNSIGNED NOT NULL, 
     COUNT BIGINT UNSIGNED NOT NULL DEFAULT 1, LASTMOD TIMESTAMP );

CREATE TABLE foo AS SELECT * FROM bar WHERE id>42;

CREATE TABLE mytable (
 id   NUMERIC(10)  NOT NULL,
 name VARCHAR2(50) NOT NULL,
 age  NUMERIC(3)   NOT NULL,
 CONSTRAINT constr_id_name UNIQUE (id, name)
)

ALTER TABLE mytable ADD CONSTRAINT constr_id_name PRIMARY KEY (ID)

Eigene Datentypen erzeugen

CREATE OR REPLACE TYPE address AS OBJECT
(
   street   CHAR(20),
   city     CHAR(20)
)

CREATE TABLE foo
(
   myname     char(20),
   myaddress  address)

insert into foo values
(
 'John',
 address('Main Street',
         'NY'
        )
)

SELECT f.myaddress.street FROM foo f

desc address

VIEWS

CREATE VIEW v_myview AS
 SELECT myname, myid FROM foo;

CREATE VIEW v_myview AS
 SELECT foo.myname, bar.myothername FROM foo,bar
  WHERE foo.myid = bar.myid GROUP BY foo.myid;

CREATE VIEW v_myview AS
 SELECT * FROM foo;

Invalide Objekte

SELECT * FROM ALL_OBJECTS a WHERE a.status <> 'VALID'

ALTER VIEW foo.bar COMPILE

SELECT 'ALTER VIEW ' || o.owner || '.' || o.OBJECT_NAME || ' COMPILE;' FROM all_objects o where status<>'VALID' and OBJECT_TYPE='VIEW'

select * from dba_synonyms  s where table_owner not in('SYSTEM','SYS') and db_link is null and not exists
 (select  1 from dba_objects o where s.table_owner=o.owner and s.table_name=o.object_name)

Materialized Views

CREATE MATERIALIZED VIEW foo.bar AS
 SELECT * from blub.mytable

grant CREATE ANY MATERIALIZED VIEW to foo;
grant ALTER  ANY MATERIALIZED VIEW to foo;
grant DROP   ANY MATERIALIZED VIEW to foo;
grant CREATE     MATERIALIZED VIEW to foo;
grant CREATE ANY TABLE             to foo;

exec DBMS_MVIEW.REFRESH('foo.bar', 'C', null, FALSE, TRUE, 1,2, null, TRUE);

NULL Spalten und ihr Datentyp

select a,b,c,null,d from foo

select a,b,c,(null + 0),d from foo

select a,b,c,to_date(null),d from foo

select a,b,c,cast(decode('A', 'A', null) as varchar2(255)),d from foo

DROP

DROP TABLE foo;

TRUNCATE

TRUNCATE TABLE foo;

ALTER

ALTER TABLE foo ADD myname VARCHAR;
ALTER table foo change mynaame myname char(50);
alter table foo.bar rename column blub to newblub;
alter table foo.bar add blub varchar2(1);
alter table foo.bar drop column blub;

alter table user.bar rename to foo;

ALTER SESSION SET CURRENT_SCHEMA = foo

Constraints

CREATE foo
(
  a   NUMBER,
  b   NUMBER,
  c   NUMBER,
  CHECK (a > b)
);

ALTER TABLE foo ADD CONSTRAINT foo_check_1 CHECK (a > b);

RENAME

ALTER TABLE foo RENAME COLUMN mynaame TO myname;

DCL Data Control Language

GRANT

GRANT select, insert ON customers TO webuser;

GRANT ALL PRIVILEGES ON MYDB1.* to 'USER1'@'www1.EXAMPLE.COM' IDENTIFIED BY 'MYSECRETPASSWORD' WITH GRANT OPTION;

GRANT SELECT ON foo.bar TO public WITH GRANT OPTION;

REVOKE

REVOKE ... FROM foouser;

Atomare Operationen / Transaktionen

begin; select ...; update ...; end;

Eingebaute Funktionen

SUBSTR

SELECT SUBSTR(name, 3,2) FROM foo WHERE name = 'Bar';

SELECT SUBSTR(foo, 1, length(foo)-2) as foo2 FROM bar;

SELECT SUBSTR(s,
              instr(s, ',',1,2)+2,
              instr(s, ',',1,3)-instr(s, ',',1,2)-2
             )
FROM
    (
      SELECT 'AAA, BBB, CCC, DDD, EEE' s FROM  dual
    )

CONCAT (||)

SELECT CONCAT(name,id) FROM foo WHERE name = 'Bar';

SUM

COUNT

to_char

SELECT to_char(42, '99.99')  FROM dual                    -- 42.00
SELECT to_char(42, '$99.99') FROM dual                    -- $42.00
SELECT to_char(42, '000099') FROM dual                    -- 000042
SELECT to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') FROM dual -- 2008-01-19 18:38:51

TRIM

SELECT LTRIM('   FOO   ') FROM dual;

LPAD

lpad('7', 3, '0')

Datenbanken und Tabellen Anlegen und Verwalten

SHOW     databases;
SHOW     TABLES;
DESCRIBE MYTABLE1;

Schlüssel

Primärschlüssel

CREATE TABLE Customer (SID integer PRIMARY KEY, Last_Name varchar(30));

CREATE TABLE Customer (SID integer, Last_Name varchar(30));
ALTER TABLE Customer ADD PRIMARY KEY (SID);

Vorhandene Tabelle soll technischen Schlüssel bekommen

alter table foo.bar  add myid number unique;
update      foo.bar x set x.myid=rownum;

Foreign Key

CREATE TABLE ORDERS (Order_ID integer primary key, Order_Date date, Customer_SID integer references CUSTOMER(SID), Amount double);

Foreign Key (Customer_SID) references CUSTOMER(SID));

ALTER TABLE ORDERS ADD (CONSTRAINT fk_orders1) FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID);

ALTER TABLE ORDERS ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID);

Indizes

CREATE INDEX IDX_CUSTOMER_LOCATION on CUSTOMER (City, Country);

SELECT * FROM all_ind_columns WHERE INDEX_NAME='FOO'

Worauf sich der Index beziehen sollte

• Index geht auf eine Spalte
  Man greift mit der where Bedingung nur auf diese eine Spalte zu und erwartet (im Vergleich zur Gesamtanzahl) relativ wenig Zeilen für diese where Bedingung.
  
• Mehrere Indizes, die jeweils nur auf eine Spalte gehen
  Man hat mehrere einzelne Statements, die jeweils nur eine der Spalten abfragen (siehe Index geht auf eine Spalte).
  
• Index über mehrere Spalten
  Man hat Abfragen die where Bedingungen über mehrere Spalten beinhalten. Das gilt besonders dann, wenn die Teile der where Bedingungen, die sich auch eine Spalte beziehen die Auswahl noch nicht sehr stark einschränken, also viele Zeilen liefern würden. Hier verhindert der Index dass erst mal sehr viele Zeilen gelesen werden müssen.
  

Trigger

• INSERT
• UPDATE
• DELETE

CREATE OR REPLACE TRIGGER mytrigger BEFORE INSERT OR UPDATE OR DELETE
ON foo FOR EACH ROW
WHEN (new.id>5)
DECLARE
   ...
BEGIN
   IF INSERTING THEN
    ...
   END IF;
   IF UPDATING THEN
    ...
   END IF;
   IF DELETING THEN
    ...
   END IF;
EXCEPTION
   ...
END mytrigger;

• Nur in ROW-Triggern werden die Alten (old) und Neuen (new) Werte der Tabelle zur Verfügung gestellt
• Bei BEFORE-Triggern besteht die Möglichkeit die NEW-Werte zu ändern

create or replace TRIGGER foo.mytrigger
before INSERT OR UPDATE ON foo.mytable
 for each row begin
   SELECT lpad(:NEW.mycolumn,3,'0')
     INTO :NEW.mycolumn
     FROM dual;
end;

Meta Informationen auslesen

Alle Tabelle auflisten

SELECT *           FROM tab;
SELECT table_name  FROM user_tables;
SELECT table_name  FROM all_tables;
SELECT table_name  FROM dba_tables;
SELECT object_name FROM user_objects WHERE object_type = 'TABLE';
SELECT object_name FROM all_objects  WHERE object_type = 'TABLE';
SELECT object_name FROM dba_objects  WHERE object_type = 'TABLE';

SELECT * FROM ALL_SOURCE

Abhängige Objecte

SELECT * FROM all_dependencies where referenced_name='FOO'

Reservierte Oracle Namen als Spaltennamen

select * from  v$reserved_words where KEYWORD in (select column_name from dba_tab_columns where owner = 'FOO')

Aktuelle Datenbankinstanz

show tables;
sp_databases;
sp_tables;

ERROR 1046 (3D000): No database selected

mysql --user=root --password=SECRET
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
| foobar             | 
+--------------------+
3 rows in set (0.03 sec)

mysql> connect foobar;

Alle Spalten einer Tabellen auflisten

SELECT COLUMN_NAME FROM DBA_TAB_COLUMNS WHERE OWNER='FOO' AND TABLE_NAME='BAR'

Quelltext anzeigen

SELECT * FROM DBA_Source where type='FUNCTION' and name='MYFOOFUNCTION' order by owner,name, type, line

Constraint anzeigen

SELECT * FROM ALL_CONSTRAINTS i, ALL_CONS_COLUMNS c where i.CONSTRAINT_NAME='FOO' and c.CONSTRAINT_NAME=i.CONSTRAINT_NAME

Index anzeigen

SELECT * FROM all_indexes i, all_ind_columns c where i.index_name='FOO' and c.INDEX_NAME=i.INDEX_NAME

Sequenzen

CREATE SEQUENCE foo.bar START WITH 1 INCREMENT BY 1 MINVALUE 0

Eine Sequenz um eine bestimmte Anzahl erhöhen

alter sequence foo increment by 124;
select foo.nextval from dual;
alter sequence foo increment by 1;

ALTER SEQUENCE seq_cache NOCACHE;
ALTER SEQUENCE seq_cache 20; -- default

Gegenseitig blockierende Zugriffe

SELECT l1.sid, l2.sid FROM v$lock l1, v$lock l2 WHERE
     l1.block =1 AND l2.request > 0 AND l1.id1=l2.id1 AND l1.id2=l2.id2

SELECT * FROM v$session where sid=FOO1 or sid=FOO2

Auf der DB ausgeführte Befehle ermitteln

SELECT * FROM v$sqlarea ORDER BY FIRST_LOAD_TIME DESC

Tablespace

• Dateien auf dem Dateisystem
• Aus einer oder mehreren Dateien entsteht ein tablespace
• Ein Schema (oder mehrere) liegen auf einem (oder mehreren) tablespace(s)
• In einem Schema liegen dann u.a. die Tabellen

SELECT * FROM dba_data_files order by bytes desc

SELECT * FROM v$tablespace order by name
SELECT * FROM DBA_TABLESPACES order by tablespace_name

select * from DBA_SEGMENTS where TABLESPACE_NAME = 'FOO' order by bytes desc

select TABLESPACE_NAME, sum(BYTES) free from dba_free_space group by TABLESPACE_NAME
 order by free desc

SELECT * FROM (
 select a.TABLESPACE_NAME,
  a.SIZE_BYTES/1024/1024/1024 as SIZE_GB,
  b.FREE_BYTES/1024/1024/1024 as FREE_GB,
  (round(((a.SIZE_BYTES-b.FREE_BYTES)/a.SIZE_BYTES)*100.0*100.0)/100.0) percent_used
 from
  (select TABLESPACE_NAME, sum(BYTES) SIZE_BYTES from dba_data_files group by TABLESPACE_NAME) a,
  (select TABLESPACE_NAME, sum(BYTES) FREE_BYTES from dba_free_space group by TABLESPACE_NAME) b
 where  a.TABLESPACE_NAME=b.TABLESPACE_NAME
) order by percent_used desc

SELECT f.file_name, f.file_id, f.TABLESPACE_NAME, f.bytes/1024/1024/1024 as
Size_GB, count(s.SEGMENT_NAME)
 FROM dba_data_files f, DBA_SEGMENTS s
 where f.TABLESPACE_NAME=s.TABLESPACE_NAME(+)
 group by f.file_name,f.file_id,f.TABLESPACE_NAME,f.bytes
  having count(s.SEGMENT_NAME)=0
 order by Size_GB desc

select owner, TABLESPACE_NAME, sum(bytes)/1024/1024/1024 as SUM_SIZE_GB from
 DBA_SEGMENTS group by owner, tablespace_name order by sum_size_gb desc

select owner, sum(bytes)/1024/1024/1024 as SUM_SIZE_GB from DBA_SEGMENTS group
 by owner order by sum_size_gb desc

SELECT * FROM DBA_TABLES  i where i.TABLESPACE_NAME='FOO';
SELECT * FROM DBA_INDEXES i where i.TABLESPACE_NAME='FOO';

Oracle

Oracle Links

• Einführung in PL / SQL Oracle 8i/9i
• Oracle Tuning Tutorial
• wikibooks Oracle
• Oracle unter Debian installieren
• Oracle unter Debian installieren
• Oracle Online Bücher
  • Database Concepts
  • 2 Day DBA
  • SQL Reference
  • Java Developer's Guide
  • JDBC Developer's Guide and Reference

Installation

deb http://oss.oracle.com/debian unstable main non-free

/var/lib/dpkg/info/oracle-xe-universal.postinst: line 57: bc: command not found

apt-get install bc
apt-get remove --purge oracle-xe-universal
apt-get install oracle-xe-universal

# /etc/init.d/oracle-xe configure
Specify the HTTP port that will be used for Oracle Application Express [8080]:
Specify a port that will be used for the database listener [1521]:
Specify a password to be used for database accounts.  Note that the same
password will be used for SYS and SYSTEM.  Oracle recommends the use of 
different passwords for each database account.  This can be done after
initial configuration: admin
Confirm the password:  admin

cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin
. ./oracle_env.sh
sqlplus / as sysdba

SQL> ALTER USER SYS IDENTIFIED BY admin;

SQL> EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);

# su - oracle
# cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin
# . ./oracle_env.sh
# sqlplus / as sysdba

SQL> CREATE ROLE conn;
Role created.

SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE SYNONYM TO conn;
Grant succeeded.

SQL> CREATE USER foo IDENTIFIED BY bar default tablespace users temporary tablespace temp;
User created.

SQL> ALTER USER foo QUOTA UNLIMITED ON users;
User altered.

SQL> GRANT conn TO foo;
Grant succeeded.

SQL> CONNECT foo

Enter password: bar

Connected.

SQL*Plus

sqlplus    username/passwort@foo.bar.de
sqlplus -S username/passwort@foo.bar.de @myscript.sql > myoutput.txt 2>&1

Allgemeine Hinweise

Befehle

define_editor=emacs

NULL

SELECT * FROM foo WHERE b<>4

SELECT * FROM foo.bar where lnvl(<>4)

bind_variables

SELECT * FROM foo where myid=5;

variable x number
exec :x := 5
SELECT * FROM foo WHERE myid=:x;
exec :x := 42
SELECT * FROM foo WHERE myid=:x;

declare l_myvalue number;
...
execute immediate
 'INSERT INTO foo VALUES (:x1, :x2)'
  USING l_myvalue, l_myvalue;

MTS

Read

• Read Uncommitted
  Erlaubt dirty reads während andere Clients gerade schreiben
• Read Committed
  Beim Lesen wird angehalten wenn die zu lesende Zeile gerade von einer anderen Transaktion bearbeitet wird.
  Ist aber auch unsicher:
  
  • Prozess A soll eine Spalte aufaddieren. Er liest Zeile 1,2,3,4.
  • Jetzt startet Transaktion die von Zeile 1 eine Zahl abzieht und auf 5 draufaddiert.
  • Der lesende Zugriff auf Zeile 5 wird jetzt blockiert bis der Schreibvorgang abgeschlossen ist (hier geht also nichts schief
  • Jetzt liest Prozess A weiter Zeile 5,6,7,8,...
  Jetzt hat Prozess A aber Zeile 1 vor der Änderung gelesen und Zeile 5 nach der Änderung. Die festgestellte Summe hat aber so nie existiert.
• Repeatable Read
  Der zu lesensende Bereich wird während dem kompletten Vorgang gegenüber Änderungen gesichert. Die Ergebnisse sind also reproduzierbar.

SELECT x,y FROM foo FOR UPDATE NOWAIT;

isolation_level

isolation_level=serializable

SET TRANSACTION

...
COMMIT;

SET TRANSACTION READ ONLY NAME 'foo';
SELECT * FROM bar WHERE ...;
COMMIT;

...

• READ ONLY
• READ WRITE
• ISOLATION LEVEL SERIALIZABLE
• ISOLATION LEVEL READ
• ISOLATION LEVEL READ COMMITTED
• USE ROLLBACK SEGMENT foo
• NAME bar

Datenbanktabellentypen

SELECT /* FULL(foo) */ myname from foo;

Indizes

Hints

select /*+ FULL(foo)          */ bar from foo where id=42;
select /*+ INDEX(my_alias idx_bar) */ bar from foo.bar as my_alias where id=42;

Performance

SET TIMING ON;
SET AUTOTRACE ON EXPLAIN;
SET SERVEROUTPUT ON;

ANALYZE TABLE foo COMPUTE STATISTICS FOR ALL INDEXES;
ANALYZE TABLE foo COMPUTE STATISTICS;

dbms_stats.gather_table_stats(ownname          => 'MYUSER',
                              tabname          => 'MYTABLE',
                              method_opt       => 'FOR ALL INDEXED COLUMNS',
                              degree           => 8,
                              granularity      => 'ALL',
                              cascade          => true,
                              no_invalidate    => false
                             );

SELECT TABLE_NAME, COLUMN_NAME, LAST_ANALYZED FROM ALL_TAB_COL_STATISTICS WHERE TABLE_NAME = 'FOO'
SELECT INDEX_NAME, LAST_ANALYZED              FROM ALL_INDEXES            WHERE TABLE_NAME = 'POSCHECKDETAIL'

Lang laufende Statements

SELECT
 i.opname,
 i.start_time,
 i.last_update_time,
 i.time_remaining,
 i.message,
 i.username,
 s.sql_text
FROM
 v$session_longops i,
 gv$sqlarea s
WHERE
    i.sql_address = s.address
and i.time_remaining>0;

Datenbank Link

CREATE DATABASE LINK mylink CONNECT TO mrfoo IDENTIFIED BY mysecretpasswd USING 'XE'
SELECT * FROM bar@mylink

SELECT * FROM ALL_DB_LINKS

Tools

EXP

IMP

SQLLDR

PL/SQL

PL/SQL Beispiele

set serveroutput on
DECLARE
  CURSOR c_mycursor1 IS SELECT distinct foo FROM sys.bar;
  v_myid  sys.bar.foo%TYPE;

BEGIN
  dbms_output.enable(100000);

  dbms_output.put_line('Hello World.');
  dbms_output.put_line('============');

  -- iterate over input from cursor
  FOR c_current IN  c_mycursor1
  LOOP
    dbms_output.put('Debug: '); dbms_output.put_line(c_current.foo);

    v_myid:=null;

    SELECT min(ids) into v_myid FROM public.allids;

    if v_myid IS NULL then
     dbms_output.put_line('Error');
    else
     dbms_output.put('Min: '); dbms_output.put_line(v_myid);
    end if;

  END LOOP;
END;
/

set serveroutput on format wrapped

CREATE OR REPLACE Function MyFunction
 (
  p_MyParameter         in varchar2,
  p_MyOtherParameter    in varchar2,
  p_AndAnotherParameter in varchar2
 )
RETURN varchar2
IS
 myresult varchar2(32767);
BEGIN

 myresult:=p_MyParameter;
 myresult:=REGEXP_REPLACE(myresult, '([^A-Z_])' || p_MyOtherParameter ||'([^A-Z_])', '\1' || p_AndAnotherParameter || '\2', 1, 0, 'i');

 RETURN myresult;

END;
/

select MyFunction('Some long String', 'long', 'really long') from dual;

Führende Leerzeichen verschwinden

set serveroutput on format wrapped

Anzeige der Anzahl der veränderten Zeilen

update foo.bar set x=y;
dbms_output.put_line('Numer of changed entries:' || SQL%ROWCOUNT);

postgresql

• PostgreSQL Dokumentation

Mit der Datenbank verbinden

psql -l
psql -h HOSTNAME DATABASENAME
\l
\d

Eine Datenbank exportieren und wieder importieren

pg_dump FOO -t BAR -d -D -F t -f /tmp/foo.tar

pg_restore --list /tmp/foo.tar > /tmp/foo.list
pg_restore --use-list=/tmp/foo.list --dbname=FOO -v /tmp/foo.tar

MySQL

• MySQL Dokumentation
• MySQL Referenz
• PHP MySQL
• PHP MySQL Referenz

JDBC