'$oracle\_home/oradata/$oracle\_sid/undotbs01.dbf' size 5G '$oracle\_home/oradata/$oracle\_sid/temp01.dbf' size 5G '$oracle\_home/oradata/$oracle\_sid/sysaux01.dbf' size 256Mĭefault temporary tablespace temp tempfile '$oracle\_home/oradata/$oracle\_sid/redo03.log' size 1G '$oracle\_home/oradata/$oracle\_sid/redo02.log' size 1G, '$oracle\_home/oradata/$oracle\_sid/redo01.log' size 1G, '$oracle\_home/oradata/$oracle\_sid/system01.dbf' size 512M User system identified by "$system\_password" The TEMP tablespace was created during the database creation create database "$oracle\_sid" The problem with this is that the TEMP tablespace is small and the extents are not set to grow to the size I need them to grow during the procedure. The Oracle docs state that the global temporary table will be created in the user's temp tablespace (I assume this is TEMP).
' at the start, and INSERT /*+ APPEND */ INTO at some later point)Īll of the permanent tables have been created in a big tablespace BIGTABLESPACE This global temp table will be used in the procedure instead of the permanent table. I'm creating an equivalent global temporary table with rows that should be preserved until the end of the session for each existing permanent table. Should become CREATE GLOBAL TEMPORARY TABLE my_table ( I've looked at the stats on these permanent tables and some have tens of millions of rows of data and are on the order if gigabytes in size (up to 10 GB). I'm trying to convert the permanent tables used in a stored procedure to global temp tables.