Background
Using Temporary Table
Outline
- Temporary Table
- Create Temporary Table
- Option:- On Commit
- On Commit Preserve Rows
On Commit Delete Rows
- Option:- On Commit
- Discard all existing Data
- Truncate Table
- Insert Data
- Insert Data using “insert into“
- Fetch Data
- Fetch Data using “select“
Truncate DataTruncate data from temporary table using “truncate table“
- Drop Table
- Drop Table Using “Drop Table“
- Commit
- Commit Session
- Create Temporary Table
SQL
Original
/*
* Create Temporary Table
*/
CREATE GLOBAL TEMPORARY TABLE friendBook
(
friendName VARCHAR2(100) NOT NULL
, dateMet DATE NOT NULL
)
/*
ON COMMIT DELETE ROWS
*/
ON COMMIT PRESERVE ROWS
;
/*
* Discard all data in temporary table
*/
TRUNCATE TABLE friendBook
;
/*
Add Data
*/
INSERT INTO friendBook
(
friendName
, dateMet
)
SELECT
'Bob Wood'
, TO_DATE('2023-01-01', 'YYYY-MM-DD')
FROM DUAL
;
/*
* Get Data
*/
SELECT
tblFB.friendName
AS "friend"
, tblFB.dateMet
AS "dateMet"
FROM friendBook tblFB
ORDER BY
tblFB.dateMet ASC
;
/*
* Discard all data in temporary table
*/
/*
TRUNCATE TABLE friendBook;
*/
/*
* Review Temporary Tables
*/
SELECT
'before' AS "status"
--, tblUT.OWNER
, tblUT.TABLE_NAME
, tblUT.STATUS
, tblUT.TEMPORARY
, tblUT.LOGGING
, tblUT.DURATION
, tblUT.NUM_ROWS
FROM USER_TABLES tblUT
--FROM ALL_TABLES tblUT
WHERE 1 =1
--AND tblUT.OWNER = USER
AND
(
( tblUT.TEMPORARY = 'Y' )
OR ( tblUT.LOGGING = 'NO' )
OR (
tblUT.DURATION IN
(
'SYS$SESSION'
, 'SYS$TRANSACTION'
)
)
)
;
/*
* Drop Table
*/
DROP TABLE friendBook;
/*
* Review Temporary Tables
*/
SELECT
'after' AS "status"
-- , tblUT.OWNER
, tblUT.TABLE_NAME
, tblUT.STATUS
, tblUT.TEMPORARY
, tblUT.LOGGING
, tblUT.DURATION
, tblUT.DROPPED
FROM USER_TABLES tblUT
--FROM ALL_TABLES tblUT
WHERE 1 =1
--AND tblUT.OWNER = USER
AND
(
( tblUT.TEMPORARY = 'Y' )
OR ( tblUT.LOGGING = 'NO' )
OR (
tblUT.DURATION IN
(
'SYS$SESSION'
, 'SYS$TRANSACTION'
)
)
)
;
COMMIT;
Output
Errors
We will get a couple of errors.
Error – ORA-14452
Upon first run, you will get the error:-
ORA-14452: attempt to create, alter or drop an index on temporary table already in use
Image
Textual
Error occurred during SQL script execution Reason: SQL Error [14452] [72000]: ORA-14452: attempt to create, alter or drop an index on temporary table already in use =================================================================================================================== SQL Error [14452] [72000]: ORA-14452: attempt to create, alter or drop an index on temporary table already in use Error : 14452, Position : 11, Sql = DROP TABLE friendBook, OriginalSql = DROP TABLE friendBook, Error Msg = ORA-14452: attempt to create, alter or drop an index on temporary table already in use ORA-14452: attempt to create, alter or drop an index on temporary table already in use
Explanation
The error occurs when we try dropping the table using “drop table“.
Error – ORA-995
On subsequent runs, you will get the error:-
SQL Error [955] [42000]: ORA-00955: name is already used by an existing object
Image
Textual
Error occurred during SQL script execution
Reason:
SQL Error [955] [42000]: ORA-00955: name is already used by an existing object
SQL Error [955] [42000]: ORA-00955: name is already used by an existing object
Error : 955, Position : 66, Sql = /*
* Create Temporary Table
*/
CREATE GLOBAL TEMPORARY TABLE friendBook
(
friendName VARCHAR2(100) NOT NULL
, dateMet DATE NOT NULL
)
/*
ON COMMIT DELETE ROWS
*/
ON COMMIT PRESERVE ROWS
, OriginalSql = /*
* Create Temporary Table
*/
CREATE GLOBAL TEMPORARY TABLE friendBook
(
friendName VARCHAR2(100) NOT NULL
, dateMet DATE NOT NULL
)
/*
ON COMMIT DELETE ROWS
*/
ON COMMIT PRESERVE ROWS
, Error Msg = ORA-00955: name is already used by an existing object
ORA-00955: name is already used by an existing object
Explanation
The error occurs when we try creating the table.
Things work well the first time.
But, fail on subsequent iterations.
Remediation
In Oracle, one can not drop a temporary table that has data in it.
Please truncate data in temporary table using “truncate table” or “delete from’ without a where clause, before issuing the drop table statement.
SQL
Revised
/*
* Create Temporary Table
*/
CREATE GLOBAL TEMPORARY TABLE friendBook
(
friendName VARCHAR2(100) NOT NULL
, dateMet DATE NOT NULL
)
/*
ON COMMIT DELETE ROWS
*/
ON COMMIT PRESERVE ROWS
;
/*
* Discard all data in temporary table
*/
TRUNCATE TABLE friendBook
;
/*
Add Data
*/
INSERT INTO friendBook
(
friendName
, dateMet
)
SELECT
'Bob Wood'
, TO_DATE('2023-01-01', 'YYYY-MM-DD')
FROM DUAL
;
/*
* Get Data
*/
SELECT
tblFB.friendName
AS "friend"
, tblFB.dateMet
AS "dateMet"
FROM friendBook tblFB
ORDER BY
tblFB.dateMet ASC
;
/*
* Discard all data in temporary table
*/
/*
Added truncate table back in
*/
TRUNCATE TABLE friendBook;
/*
* Review Temporary Tables
*/
SELECT
'before' AS "status"
--, tblUT.OWNER
, tblUT.TABLE_NAME
, tblUT.STATUS
, tblUT.TEMPORARY
, tblUT.LOGGING
, tblUT.DURATION
, tblUT.NUM_ROWS
FROM USER_TABLES tblUT
--FROM ALL_TABLES tblUT
WHERE 1 =1
--AND tblUT.OWNER = USER
AND
(
( tblUT.TEMPORARY = 'Y' )
OR ( tblUT.LOGGING = 'NO' )
OR (
tblUT.DURATION IN
(
'SYS$SESSION'
, 'SYS$TRANSACTION'
)
)
)
;
/*
* Drop Table
*/
DROP TABLE friendBook;
/*
* Review Temporary Tables
*/
SELECT
'after' AS "status"
-- , tblUT.OWNER
, tblUT.TABLE_NAME
, tblUT.STATUS
, tblUT.TEMPORARY
, tblUT.LOGGING
, tblUT.DURATION
, tblUT.DROPPED
FROM USER_TABLES tblUT
--FROM ALL_TABLES tblUT
WHERE 1 =1
--AND tblUT.OWNER = USER
AND
(
( tblUT.TEMPORARY = 'Y' )
OR ( tblUT.LOGGING = 'NO' )
OR (
tblUT.DURATION IN
(
'SYS$SESSION'
, 'SYS$TRANSACTION'
)
)
)
;
COMMIT;
Metadata
Btw, you can track temporary tables by accessing USER_TABLES.
Metadata
/*
* Review Temporary Tables
*/
SELECT
'before' AS "status"
--, tblUT.OWNER
, tblUT.TABLE_NAME
, tblUT.STATUS
, tblUT.TEMPORARY
, tblUT.LOGGING
, tblUT.DURATION
, tblUT.NUM_ROWS
FROM USER_TABLES tblUT
--FROM ALL_TABLES tblUT
WHERE 1 =1
--AND tblUT.OWNER = USER
AND
(
( tblUT.TEMPORARY = 'Y' )
OR ( tblUT.LOGGING = 'NO' )
OR (
tblUT.DURATION IN
(
'SYS$SESSION'
, 'SYS$TRANSACTION'
)
)
)
;
Output
Output – Image
Output – Textual
- TABLE_NAME
- FRIENDBOOK
- STATUS
- VALID
- TEMPORARY
- Y
- LOGGING
- NO
- DURATION
- SYS$SESSION
- NUM_ROWS
- [NULL]
Explanation
- USER_TABLES
- USER_TABLES is use specific
- ALL_TABLES
- Please use ALL_TABLES to get a more generalized insight
- Of course, you want to filter on OWNER = USER ( table column OWNER is equal to function USER per the current user name )
- AND OWNER = USER
Source Code Sharing
GitLab
Snippet
- Oracle – Database Engine – Temporary Tables
Link
Summary
Spoiler Alert.
As always made a lot out of nothing!
Yet, made you look.
Learning in the Open 