Today's tip is dedicated to the most important concept of a database: the table!
Since I keep having to search for examples of special CREATE TABLE commands for Oracle databases, it was time to create my own summary.
Case 1: A table is to be created with the most common numeric data types:
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 );
Case 2: Next, we take the text and other data types:
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 );
Case 3: The table is to be created with the new LOB storage technology (from 11.1), the secure files:
CREATE TABLE t (
c1 NUMBER,
c2 CLOB,
CONSTRAINT t_pk PRIMARY KEY (c1) )
LOB(c2) STORE AS SECUREFILE my_lob_tbs;
Case 4: The table should only be physically created once the first line has been entered:
CREATE TABLE t (
c1 NUMBER )
SEGMENT CREATION DEFERRED;
Case 5: The table is to be created with the NOLOGGING attribute:
CREATE TABLE t (
c1 INT,
c2 VARCHAR2(4000) ) -- AB 12.2 bis 32767 möglich
NOLOGGING;
The table can subsequently be changed back to LOGGING or NOLOGGING:
ALTER TABLE t LOGGING;
ALTER TABLE t NOLOGGING;
Case 6: You want to specify a special tablespace for the table:
CREATE TABLE t (
c1 INT,
c2 VARCHAR2(4000) )
TABLESPACE user_tbs;
Case 7: The table should have a primary key:
CREATE TABLE t (
c1 INT PRIMARY KEY );
Case 8: The table should have a foreign key:
CREATE TABLE t (
c1 INT,
c2 INT,
c3 INT,
CONSTRAINT fk_cons FOREIGN KEY (c1, c2,c3) REFERENCES t2(c1, c2,c3);
Case 9: The table should have a special sorting for a column. This function is only available from 12.2 onwards. In addition, the parameter max_string_size= EXTENDED must be set.
CREATE TABLE t (
c1 NUMBER,
c2 VARCHAR2(2000 CHAR) COLLATE GERMAN_CI );
Case 10: The table should only store the data temporarily (until the end of the session):
CREATE GLOBAL TEMPORARY TABLE t (
c1 INT,
c2 VARCHAR2(4000) )
ON COMMIT PRESERVE ROWS;
Case 11: The table should only store the data temporarily (until the end of the transaction):
CREATE GLOBAL TEMPORARY TABLE t (
c1 INT,
c2 VARCHAR2(4000) )
ON COMMIT DELETE ROWS;
Case 12: From version 18, a temporary table can be created which is deleted at the end of the transaction together with its definition.
Note: It must have a prefix (ora$ptt) in the name. However, this can be changed via the initialization parameter PRIVATE_TEMP_TABLE_PREFIX.
CREATE PRIVATE TEMPORARY TABLE ora$ptt_t (
c1 NUMBER,
c2 VARCHAR2(20) )
ON COMMIT DROP DEFINITION;
Case 13: Also only available from version 18, this is the second variant of the private temporary table, which is only deleted at the end of the session:
CREATE PRIVATE TEMPORARY TABLE ora$ptt_t (
c1 NUMBER,
c2 VARCHAR2(20) )
ON COMMIT PRESERVE DEFINITION;
Case 14: The table should remember the change SCN per line and not just per block:
CREATE TABLE t (
c1 INT,
c2 VARCHAR2(4000) )
ROWDEPENDENCIES;
Case 15: The table is to be based on a file that is outside the database:
Als Benutzer mit DBA Rechten ausführen:
CREATE DIRECTORY utl_dir as 'C:\temp';
GRANT read,write ON DIRECTORY utl_dir TO scott;
do it as SCOTT User:
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;
Case 16: Blockchain table (only from 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;
Case 17: Interval range partitioned table (1 month interval)
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')));
Case 18: Interval Range-Partitioned Table (1 day interval)
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')));
Case 19: Interval Range-Partitioned Table (1 year interval)
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')));
Case 20: Range partitioning with own interval
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'))
);