In unserem heutigen Tipp widmen wir uns dem wichtigsten Konzept einer Datenbank überhaupt: Der Tabelle!
Nachdem ich immer wieder nach Beispielen zu speziellen CREATE TABLE Befehlen für Oracle Datenbanken suchen muss, war es mal an der Zeit eine eigene Zusammenfassung zu erstellen.
Fall 1: Eine Tabelle soll mit den gängigsten numerischen Datentypen erstellt werden:
CREATE TABLE t (
c1 BINARY_DOUBLE,
c2 BINARY_FLOAT,
c3 DEC,
c4 DECIMAL,
c5 FLOAT,
c6 INT,
c7 INTEGER,
c8 NUMBER(9,2),
c9 NUMERIC );
Fall 2: Als nächstes nehmen wir die text und sonstigen Datentypen:
CREATE TABLE t (
c1 char(1),
c2 char,
c3 varchar2(4000),
c4 varchar(4000),
c5 BLOB,
c6 CLOB,
c7 BFILE,
c8 RAW(2000),
c9 date,
c10 timestamp,
c11 rowid );
Fall 3: Die Tabelle soll mit der neuen LOB Speichertechnik (ab 11.1), den Securefiles, erstellt werden:
CREATE TABLE t (
c1 NUMBER,
c2 CLOB,
CONSTRAINT t_pk PRIMARY KEY (c1) )
LOB(c2) STORE AS SECUREFILE my_lob_tbs;
Fall 4: Die Tabelle soll erst dann physisch angelegt werden, wenn die erste Zeile eingetragen wurde:
CREATE TABLE t (
c1 NUMBER )
SEGMENT CREATION DEFERRED;
Fall 5: Die Tabelle soll mit dem Attribut NOLOGGING erstellt werden:
CREATE TABLE t (
c1 INT,
c2 VARCHAR2(4000) ) -- AB 12.2 bis 32767 möglich
NOLOGGING;
Nachträglich kann die Tabelle wieder in LOGGING bzw. NOLOGGING geändert werden:
ALTER TABLE t LOGGING;
ALTER TABLE t NOLOGGING;
Fall 6: Sie wollen einen speziellen Tablespace für die Tabelle angeben:
CREATE TABLE t (
c1 INT,
c2 VARCHAR2(4000) )
TABLESPACE user_tbs;
Fall 7: Die Tabelle soll einen Primärschlüssel besitzen:
CREATE TABLE t (
c1 INT PRIMARY KEY );
Fall 8: Die Tabelle soll einen Fremdschlüssel (Foreign Key) besitzen:
CREATE TABLE t (
c1 INT,
c2 INT,
c3 INT,
CONSTRAINT fk_cons FOREIGN KEY (c1, c2,c3) REFERENCES t2(c1, c2,c3);
Fall 9: Die Tabelle soll eine spezielle Sortierung für eine Spalte eingestellt bekommen. Diese Funktion steht erst ab 12.2 zur Verfügung. Außerdem muss der Parameter max_string_size= EXTENDED gesetzt sein.
CREATE TABLE t (
c1 NUMBER,
c2 VARCHAR2(2000 CHAR) COLLATE GERMAN_CI );
Fall 10: Die Tabelle soll nur die Daten temporär aufbewahren (bis zum Sessionende):
CREATE GLOBAL TEMPORARY TABLE t (
c1 INT,
c2 VARCHAR2(4000) )
ON COMMIT PRESERVE ROWS;
Fall 11: Die Tabelle soll nur die Daten temporär aufbewahren (bis zum Transaktionsende):
CREATE GLOBAL TEMPORARY TABLE t (
c1 INT,
c2 VARCHAR2(4000) )
ON COMMIT DELETE ROWS;
Fall 12: Ab Version 18 kann eine temporäre Tabelle erzeugt werden, die zum Transaktionsende samt Definition gelöscht wird.
Hinweis: Sie muss ein Prefix (ora$ptt) im Namen aufweisen. Dies kann jedoch über den Initialisierungsparameter PRIVATE_TEMP_TABLE_PREFIX geändert werden.
CREATE PRIVATE TEMPORARY TABLE ora$ptt_t (
c1 NUMBER,
c2 VARCHAR2(20) )
ON COMMIT DROP DEFINITION;
Fall 13: Auch erst ab Version 18 verfügbar, ist die zweite Variante der privaten temporären Tabelle, die erst zum Sessionende gelöscht wird:
CREATE PRIVATE TEMPORARY TABLE ora$ptt_t (
c1 NUMBER,
c2 VARCHAR2(20) )
ON COMMIT PRESERVE DEFINITION;
Fall 14: Die Tabelle soll sich die Änderungs-SCN pro Zeile und nicht nur pro Block merken:
CREATE TABLE t (
c1 INT,
c2 VARCHAR2(4000) )
ROWDEPENDENCIES;
Fall 15: Die Tabelle soll auf einer Datei basieren, die ausserhalb der Datenbank liegt:
Als Benutzer mit DBA Rechten ausführen:
CREATE DIRECTORY utl_dir as 'C:\temp';
GRANT read,write ON DIRECTORY utl_dir TO scott;
Als Benutzer SCOTT ausführen:
CREATE TABLE scott.emp_ext (
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) )
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY UTL_DIR
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(
empno,
ename,
job,
mgr,
hiredate CHAR(21) DATE_FORMAT DATE MASK "DD-MON-YYYY HH24:MI:SS",
sal,
comm,
deptno
)
)
LOCATION ('emp.txt')
)
PARALLEL 1
REJECT LIMIT UNLIMITED;
Fall 16: Blockchain Table (Erst ab Version 21c)
CREATE BLOCKCHAIN TABLE bc_tab1 (
bank VARCHAR2(128),
d_date DATE,
d_amount NUMBER )
NO DROP UNTIL 25 DAYS IDLE
NO DELETE UNTIL 31 DAYS AFTER INSERT
HASHING USING "SHA2_512" VERSION v1;
Fall 17: Interval Range-Partitionierte Tabelle (1 Monatsintervall)
CREATE TABLE t
(id NUMBER(6) NOT NULL,
datum DATE NOT NULL)
PARTITION BY RANGE (datum)
INTERVAL (numtoyminterval(1,'MONTH'))
( PARTITION p2021 VALUES LESS THAN (to_date('01.01.2021','dd.mm.yyyy')));
Fall 18: Interval Range-Partitionierte Tabelle (1 Tagesintervall)
CREATE TABLE t
(id NUMBER(6) NOT NULL,
datum DATE NOT NULL)
PARTITION BY RANGE (datum)
INTERVAL (numtodsinterval(1,'DAY'))
( PARTITION p2021 VALUES LESS THAN (to_date('01.01.2021','dd.mm.yyyy')));
Fall 19: Interval Range-Partitionierte Tabelle (1 Jahresintervall)
CREATE TABLE t
(id NUMBER(6) NOT NULL,
datum DATE NOT NULL)
PARTITION BY RANGE (datum)
INTERVAL (numtoyminterval(1,'YEAR'))
( PARTITION p2021 VALUES LESS THAN (to_date('01.01.2021','dd.mm.yyyy')));
Fall 20: Range Partitioninierung mit eigenem Intervall
CREATE TABLE scott.emp_part (
empno NUMBER(4),
ename VARCHAR2(20),
hiredate DATE)
PARTITION BY RANGE (hiredate)(
partition year2019 VALUES LESS THAN (to_date('01.01.2020','DD.MM.YYYY')),
partition year2020 VALUES LESS THAN (to_date('01.01.2021','DD.MM.YYYY')),
partition year2021 VALUES LESS THAN (to_date('01.01.2022','DD.MM.YYYY'))
);