Prinzip der temporären Tabellen

Die Daten werden automatisch vom Datenbanksystem gelöscht.
Die Lebensdauer ist einstellbar: bis zum Ende der laufenden Transaktion oder bis zum Ende der Session.
Die Daten der parallel laufenden Sessions sind automatisch vollständig voneinander getrennt. Es ist nicht notwendig, die Daten über zusätzliche Spalten den einzelnen Sessions zuzuordnen.
Aufgrund der automatischen Trennung der Daten zwischen den einzelnen Sessions sind keine Sperren notwendig und auch nicht verfügbar.
Typische Anwendung: komplexe Auswertungen, die Zwischendaten halten müssen.
Es werden keine Redo-Informationen geschrieben.
Die Verwendung von temporären Tabellen bietet deutliche Performance-Vorteile gegenüber „normalen“ Tabellen.

Wichtigste Einschränkungen

Es sind keine foreign key constraints mit temporärer Tabelle als Ziel erlaubt.
Eine temporäre Tabelle ist nicht als index-organized table definierbar.

Beispiele

Daten werden bis Transaktionsende gehalten

create global temporary table tmp_artikel (
artikel_nr number(5),
umsatz number(10,2))
on commit delete rows;

Die Daten können mit den üblichen Befehlen (insert, update, delete, select) bearbeitet werden.
Nach dem Ende der Transaktion ist die Tabelle automatisch leer.

Daten werden bis Sessionende gehalten

create global temporary table tmp_artikel (
artikel_nr number(5),
umsatz number(10,2))
on commit preserve rows;

Die Daten dieser temporären Tabelle sind so lange verfügbar, bis die Datenbank-Session beendet wird.

Select nls characterset

This select shows how to select the used Character-Set in ORACLE

SELECT *
FROM V$NLS_PARAMETERS
WHERE parameter = 'NLS_CHARACTERSET';

PARAMETER VALUE
----------------- --------
NLS_CHARACTERSET AL32UTF8

Sonderzeichen/Special Character in SQL escaping/entwerten

Escaping in a SQL-Statement All Tables starting with ‚T_‘.

select * from user_tables
where table_name like 'T\_%' escape '\';

All tables without underscore (‚_‘)

select * from user_tables
where table_name not like '%\_%' excape '\';

Escaping in a SQL-Plus-Session: All Tables, starting with ‚T_‘.

set escape '\'
select * from user_tables
where table_name like 'T\_%';

Searching in Data-Dictionary-Views for Views user_views, all_views…

select * from user_views
where dbms_xmlgen.getxml
('select text from user_views where view_name = || view_name || ')
like '%w_spci_order_types%'

First Possibility: Adding the Attribute in one alter step

— mark table as uncompressed (only mark, here no uncompressing!)
ALTER TABLE <table> nocompress;
/
–uncompress table in the same tablespace
ALTER TABLE l<table> move;
/

ALTER TABLE <table> nocompress;
/

— Add Attribute with defaultvalue
ALTER TABLE <table> ADD ( start_dt DATE DEFAULT TO_DATE(‚31.12.9999′,’dd.mm.yyyy‘) NOT NULL );
/

— compress table
ALTER TABLE load_dim_business_parties compress;
/

Second Possibility: Adding the Attribute without not null / default value an do an separate update
Here is the possibility to do the update in parallel.

BEGIN
EXECUTE IMMEDIATE ‚drop table compress_add_attribute‘;
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
/

CREATE TABLE compress_add_attribute(id number) compress;

BEGIN
FOR ii in 1 .. 10000
loop
insert into compress_add_attribute (id) values(ii);
end loop;
commit;
END;

——————————————————————————————-
——————————————————————————————-

— restartability
BEGIN
EXECUTE IMMEDIATE ‚ALTER TABLE compress_add_attribute ADD ( begin_dt DATE, end_dt DATE)‘;
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
/

COMMENT ON COLUMN compress_add_attribute.begin_dt IS ‚Beginn History.‘;
/
COMMENT ON COLUMN compress_add_attribute.end_dt IS ‚End History.‘;
/

DECLARE

l_row_cnt_1      NUMBER;
l_row_cnt_2      NUMBER;
l_tablespace   VARCHAR2(30);

BEGIN

pkg_utl_log.log ( ‚Start update compress_add_attribute: ‚
|| TO_CHAR ( SYSDATE
, ‚dd.mm.yyyy hh24:mi:ss‘ ) );

EXECUTE IMMEDIATE ‚ALTER SESSION FORCE PARALLEL DML PARALLEL 8‘;

EXECUTE IMMEDIATE ‚ALTER SESSION FORCE PARALLEL DDL PARALLEL 8‘;

EXECUTE IMMEDIATE ‚ALTER TABLE compress_add_attribute NOCOMPRESS‘;

— Select the tablespace with the most free space
SELECT tablespace_name
INTO l_tablespace
FROM (SELECT tablespace_name
FROM ts_usage
WHERE tablespace_name LIKE ‚TS_WORK‘ –place here some %-pattern
ORDER BY free_gb DESC
)
WHERE ROWNUM = 1;

EXECUTE IMMEDIATE ‚ALTER TABLE compress_add_attribute MOVE TABLESPACE ‚
|| l_tablespace;

UPDATE compress_add_attribute
SET begin_dt = to_date(‚19000101′,’yyyymmdd‘);

l_row_cnt_1   := sql%ROWCOUNT;

COMMIT;

UPDATE compress_add_attribute
SET end_dt   = to_date(‚99991231′,’yyyymmdd‘);

l_row_cnt_2   := sql%ROWCOUNT;

COMMIT;

pkg_utl_log.log(‚compress_add_attribute: updated begin_dt/end_dt ‚||l_row_cnt_1|| ‚/’||l_row_cnt_2|| ‚ rows).‘);

EXECUTE IMMEDIATE   ‚ALTER TABLE compress_add_attribute COMPRESS‘;

— Select the tablespace with the most free space.
SELECT tablespace_name
INTO l_tablespace
FROM (   SELECT tablespace_name
FROM ts_usage
WHERE tablespace_name LIKE ‚TS_WORK‘ –place here some %-pattern
ORDER BY free_gb DESC
)
WHERE ROWNUM = 1;

EXECUTE IMMEDIATE   ‚ALTER TABLE compress_add_attribute MOVE TABLESPACE ‚
|| l_tablespace;

EXECUTE IMMEDIATE ‚ALTER TABLE compress_add_attribute
MODIFY ( begin_dt NOT NULL, end_dt NOT NULL )‘;

pkg_utl_log.log ( ‚Finished update compress_add_attribute: ‚
|| TO_CHAR ( SYSDATE
, ‚dd.mm.yyyy hh24:mi:ss‘ ) );
EXCEPTION

WHEN OTHERS THEN
pkg_utl_log.log ( ‚IN EXCEPTION‘ );
pkg_utl_log.log ( SQLERRM
, pkg_utl_log.gc_fatal
, SQLCODE );
RAISE;
END;

This is a solution if the scd-2 history of a dimension is not complete so there history is holey. This SQL adds all missing data.

SELECT   -98,
a.party_id_2,
1 as relation_type_id,
a.end_dt,
b.begin_dt
FROM   (SELECT   ROWNUM rn1,
a.PARTY_ID_1,
a.PARTY_ID_2,
a.RELATION_TYPE_ID,
a.BEGIN_DT,
a.END_DT
FROM   (  SELECT   DISTINCT a.PARTY_ID_1,
a.PARTY_ID_2,
a.RELATION_TYPE_ID,
a.BEGIN_DT,
a.END_DT
FROM   VW_PROSPECTS_TO_CUSTOMERS a,
VW_PROSPECTS_TO_CUSTOMERS b
WHERE   a.party_id_2 = b.party_id_2
AND a.begin_dt <> b.begin_dt
ORDER BY   a.party_id_2, a.begin_dt, a.end_dt) a) a,
(SELECT   ROWNUM rn2,
a.PARTY_ID_1,
a.PARTY_ID_2,
a.RELATION_TYPE_ID,
a.BEGIN_DT,
a.END_DT
FROM   (  SELECT   DISTINCT a.PARTY_ID_1,
a.PARTY_ID_2,
a.RELATION_TYPE_ID,
a.BEGIN_DT,
a.END_DT
FROM   VW_PROSPECTS_TO_CUSTOMERS a,
VW_PROSPECTS_TO_CUSTOMERS b
WHERE   a.party_id_2 = b.party_id_2
AND a.begin_dt <> b.begin_dt
ORDER BY   a.party_id_2, a.begin_dt, a.end_dt) a) b
WHERE       a.party_id_2 = b.party_id_2
AND rn1 = rn2 – 1
AND a.end_dt <> b.begin_dt