I'm trying to convert the permanent tables used in a stored procedure to global temp tables. 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). 

So, 

```
CREATE TABLE my_table (  
  column1 NUMBER,  
  column2 NUMBER,  
  etc...  
)  
TABLESPACE BIGTABLESPACE  
NOLOGGING  
NOCOMPRESS  
NOCACHE  
NOPARALLEL  
MONITORING;  

```

should become 

```
CREATE GLOBAL TEMPORARY TABLE my_table (  
  column1 NUMBER,  
  column2 NUMBER,  
  etc..  
)  
ON COMMIT PRESERVE ROWS;  

```

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. This global temp table will be used in the procedure instead of the permanent table.  

(`EXECUTE IMMEDIATE 'TRUNCATE ...'` at the start, and `INSERT /*+ APPEND */ INTO` at some later point) 

All of the permanent tables have been created in a big tablespace `BIGTABLESPACE` 

The Oracle docs state that the global temporary table will be created in the user's temp tablespace (I assume this is `TEMP`). 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 `TEMP` tablespace was created during the database creation 

```
create database "$oracle\_sid"  
   user sys identified by "$sys\_password"  
   user system identified by "$system\_password"  
   set default bigfile tablespace  
   controlfile   reuse  
   maxdatafiles  256  
   maxinstances  $maxinstances  
   maxlogfiles   16  
   maxlogmembers 3  
   maxloghistory 1600  
   noarchivelog  
   character set WE8MSWIN1252  
   national character set AL16UTF16  
   datafile  
      '$oracle\_home/oradata/$oracle\_sid/system01.dbf' size 512M  
   logfile  
      '$oracle\_home/oradata/$oracle\_sid/redo01.log' size 1G,  
      '$oracle\_home/oradata/$oracle\_sid/redo02.log' size 1G,  
      '$oracle\_home/oradata/$oracle\_sid/redo03.log' size 1G  
   sysaux datafile  
      '$oracle\_home/oradata/$oracle\_sid/sysaux01.dbf' size 256M  
   default temporary tablespace temp tempfile  
      '$oracle\_home/oradata/$oracle\_sid/temp01.dbf' size 5G  
   undo tablespace "UNDOTBS1" datafile  
      '$oracle\_home/oradata/$oracle\_sid/undotbs01.dbf' size 5G;  

```

The permanent tables (that I'm planning to replace) were originally created in tablespace `BIGTABLESPACE`

```
-- 50G bigfile datafile size  
create bigfile tablespace "BIGTABLESPACE"  
datafile '$oracle\_home/oradata/$oracle\_sid/bts01.dbf' size 50G  
extent management local  
segment space management auto;  

```

The permanent table indexes were originally created in tablespace `BIGTABLESPACE`

```
-- 20G bigfile datafile size  
create bigfile tablespace "BIGINDXSPACE"  
datafile '$oracle\_home/oradata/$oracle\_sid/btsindx01.dbf' size 20G  
extent management local  
segment space management auto;  

```

* Is replacing these permanent tables with global temporary tables feasable?
* The TEMP tablespace will run into a problem extending the TEMP tablespace. Is there a way to create global temporary tables and their indexes in tablespaces `BIGTABLESPACE` and `BIGINDXSPACE`?
* If not, how can I make the `TEMP` tablespace behave like a bigfile tablespace and achieve index/table separation?
* Can I create two `TEMP` bigfile tablespaces and create indexes into one and tables into another?

I want to use global temporary tables, but the volume of data I am handling in the procedure would seem to be above and beyond the indended design of global temporary tables.
Any suggestions?