Skip to Main Content

 

Auswahl  

20 CREATE TABLE Beispiele für Oracle (Version 10.2 bis 21c) 

Oracle
SQL
RDBMS 12.x:RDBMS 20.1
20.03.18 (MP)
04.07.23(MP)
CREATE Table, Tabellenerstellung, External Table, Collation, Primary Key, Foreign Key, Temporäre Tabelle

Passende Schulungen zum Thema

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'))
);
Die umfangreiche CREATE TABLE Bibliothek für Oracle