How do you create a temporary table in an Oracle database?

Yep, Oracle has temporary tables. Here is a link to an AskTom article describing them and here is the official oracle CREATE TABLE documentation.

However, in Oracle, only the data in a temporary table is temporary. The table is a regular object visible to other sessions. It is a bad practice to frequently create and drop temporary tables in Oracle.

CREATE GLOBAL TEMPORARY TABLE today_sales(order_id NUMBER)
ON COMMIT PRESERVE ROWS;

Oracle 18c added private temporary tables, which are single-session in-memory objects. See the documentation for more details. Private temporary tables can be dynamically created and dropped.

CREATE PRIVATE TEMPORARY TABLE ora$ptt_today_sales AS
SELECT * FROM orders WHERE order_date = SYSDATE;

Temporary tables can be useful but they are commonly abused in Oracle. They can often be avoided by combining multiple steps into a single SQL statement using inline views.

Related Posts:

  1. ORA-00904: invalid identifier
  2. how to fix oracle ORA-01722 invalid number error
  3. ORA-00979 not a group by expression
  4. ORA-00907: missing right parenthesis
  5. How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
  6. ORA-01843 not a valid month- Comparing Dates
  7. ORA-00918: column ambiguously defined in SELECT *
  8. Oracle error : ORA-00905: Missing keyword
  9. How do I limit the number of rows returned by an Oracle query after ordering?
  10. Oracle SELECT TOP 10 records
  11. Inserting data into a temporary table
  12. Can a foreign key be NULL and/or duplicate?
  13. How to Select Top 100 rows in Oracle?
  14. ORA-12560: TNS:protocol adaptor error
  15. ORA-00972 identifier is too long alias column name
  16. Oracle Apex column link from LOV values
  17. PLS-00103: Encountered the symbol when expecting one of the following:
  18. What’s the difference between RANK() and DENSE_RANK() functions in oracle?
  19. ORA-00918: column ambiguously defined in SELECT *
  20. ORA-00918: column ambiguously defined in SELECT *
  21. Oracle – ORA-01489: result of string concatenation is too long [duplicate]
  22. ORA-01861: literal does not match format string
  23. What is it exactly a BLOB in a DBMS context
  24. “ORA-01438: value larger than specified precision allowed for this column” when inserting 3
  25. Oracle “Partition By” Keyword
  26. How to insert date values into table
  27. Oracle SQL: Update a table with data from another table
  28. SQL selecting rows by most recent date with two unique columns
  29. ORA-06502: PL/SQL: numeric or value error: character string buffer too small
  30. Comparing Dates in Oracle SQL
  31. Convert timestamp to date in Oracle SQL
  32. How do I reset a sequence in Oracle?
  33. Update statement with inner join on Oracle
  34. MySQL equivalent of DECODE function in Oracle
  35. Temporary table in SQL server causing ‘ There is already an object named’ error
  36. Oracle: If Table Exists
  37. DATEDIFF function in Oracle
  38. How to create id with AUTO_INCREMENT on Oracle?
  39. LEFT function in Oracle
  40. What is the difference between Views and Materialized Views in Oracle?
  41. How do I view the Explain Plan in Oracle Sql developer?
  42. ORA-01779: cannot modify a column which maps to a non key-preserved table
  43. ORA-01735: invalid ALTER TABLE option – Toad
  44. ora-06553 pls-306 wrong number or types of arguments in call to ‘ogc_x’
  45. What is the difference between “INNER JOIN” and “OUTER JOIN”?
  46. Case in Select Statement
  47. Self Join to get employee manager name
  48. SQL error “ORA-01722: invalid number”
  49. MySQL “CREATE TABLE IF NOT EXISTS” -> Error 1050
  50. How can I do an UPDATE statement with JOIN in SQL Server?
  51. How do I use properly CASE..WHEN in MySQL
  52. INSERT statement conflicted with the FOREIGN KEY constraint – SQL Server
  53. SQL SELECT WHERE field contains words
  54. Case statement in MySQL
  55. Drop a temporary table if it exists
  56. MySQL: Invalid use of group function
  57. Compare dates in MySQL
  58. Best way to do nested case statement logic in SQL Server
  59. Get day of week in SQL Server 2005/2008
  60. incorrect syntax error CREATE VIEW must be the only statement in the batch
  61. Is there a combination of “LIKE” and “IN” in SQL?
  62. SQL Error: ORA-01861: literal does not match format string 01861
  63. What is the Oracle equivalent of SQL Server’s IsNull() function?
  64. Determine ROW that caused “unexpected end of file” error in BULK INSERT?
  65. Sql query to insert datetime in SQL Server
  66. Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (Year)
  67. Postgres Error: More than one row returned by a subquery used as an expression
  68. SQL Queries “00904. 00000 – “%s: invalid identifier”
  69. How to divide two columns?
  70. MySQL starts with searching issue
  71. SQL “select where not in subquery” returns no results
  72. ListAGG in SQLSERVER
  73. The used SELECT statements have a different number of columns
  74. using sql count in a case statement
  75. Equivalent of explode() to work with strings in MySQL
  76. How to calculate age (in years) based on Date of Birth and getDate()
  77. Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot
  78. How can I group by date time column without taking time into consideration
  79. Append table to an existing one: SQL Server
  80. How to order by column A and then by column B?
  81. SQL Server – An expression of non-boolean type specified in a context where a condition is expected, near ‘RETURN’
  82. Update query using Subquery in Sql Server
  83. “This SqlTransaction has completed; it is no longer usable.”… configuration error?
  84. register_post_status – show_in_admin_all_list & show_in_admin_status_list does not affect query
  85. prepare() not working
  86. How do I move a WordPress site to another server?
  87. Return number of items in a table with post id
  88. Debugging WordPress
  89. Pull data and display as rows and columns
  90. I need a SQL command to delete specific authors and all posts connected with them
  91. How can i restore only Blogs from a SQL backup file?
  92. DB_HOST – is LOCALHOST speedy than domain name? [closed]
  93. How to change post published date to today date with SQL
  94. Menu Order updating automatically
  95. Do I need to sanitize $_POST[‘keyword’] before send to ‘s’ parameter?
  96. how to SELECT meta values that are not null?
  97. blank page after update my theme
  98. Sql query returns empty. But not
  99. SQL – JOIN last child
  100. Adding profile data to database

Leave a Comment