ORACLE 8I GLOBAL TEMPORARY TABLES

By Montse Guilla

In Oracle 8i, the CREATE GLOBAL TEMPORARY TABLE command creates a temporary table which can be transaction specific or session specific. For transaction-specific temporary tables, data exists for the duration of the transaction while for session-specific temporary tables, data exists for the duration of the session. Data in a temporary table is private to the session. Each session can only see and modify its own data. The table definition itself is not temporary.

You can create indexes for temporary tables using the CREATE INDEX command. Indexes created on temporary tables are also temporary and the data in the index has the same session or transaction scope as the data in the temporary table.

You can perform DDL commands (ALTER TABLE, DROP TABLE, CREATE INDEX, and so on) on a temporary table only when no session is currently bound to it. A session gets bound to a temporary table when an INSERT is performed on it. The session gets unbound by a TRUNCATE, at session termination, or by doing a COMMIT or ABORT for a transaction-specific temporary table.

Temporary tables use temporary segments. Temporary segments are de-allocated at the end of the transaction for transaction-specific temporary tables. For session-specific temporary tables, they are de-allocated at the end of the session.

Multi-session scenarios:

1- Two different users try to create a global temporary table with the same name

Both tables get created. Each user sees his/her own table.

2- Two sessions by a single user try to create a global temporary table with the same name.

Once the table has been created in one of the sessions, the table exists and an error is given to the second session when trying to create the table. This behavior occurs whether or not the table structure is defined to be the same in both sessions.

3- Two sessions by a single user insert rows into a global temporary table.

Each session can insert rows into the table, no matter which session created the table.

4- Two sessions by the same user select from a global temporary table.

A given session will only see the rows that it has inserted, as if the table was private to that session.

5- A session tries to alter a temporary table that another session is also using.

Columns can not be added/deleted/modified as long as the table is bound to a session.

Renaming, however, can be done even when another session has rows in the table.

6- A session attempts to rename a global temporary table in which another session has inserted some rows

The table gets renamed and rows are preserved. After the rename has taken place, both sessions must use the new name to refer to that table.

There are two kinds of Sybase temporary tables:

Shareable (among different sessions for the same user) temporary tables. They are created by specifying tempdb as part of the table name in the create table statement. The table exists until its owner drops it using drop table (or the server is brought down?)

Pound sign (#) tables. Accessible only by the current session or procedure. They exist until the current session ends or until its owner drops it.

Both of these types of temporary tables are private to the user that creates them and Sybase takes care of the cleanup. Furthermore, Sybase assigns a different internal name to every temporary table, in such a way that even if two users give their tables the same name, Sybase ensures that each user will access their own table.

USER LEVEL:

In both, Sybase and Oracle, different users do not share temporary tables. In Sybase, it is impossible. In Oracle, it MAY be possible to share a temporary table, if permissions are granted (we have not tested this).

SESSION LEVEL:

Sybase # tables:

Table definition and data are session specific. Both get dropped when the session that created the table ends.

Sybase tempdb..

Table definition and data are shared across sessions by a single user. Both remain in the system when the session ends. Both disappear when the Sybase server is brought down.

Oracle 8I Global temporary tables:

Table definition is shared across sessions. Data cannot be shared across sessions. Table definition remains even when database is brought down. Data disappears when the session ends.

TRANSACTION LEVEL:

Sybase # and tempdb.. tables:

Table definition and data remain across transactions in the same session.

Oracle global temporary tables

Table definition remains across transactions (actually, across sessions and even when database is brought down). Data can be kept or discarded when the transaction ends. Which one happens is decided at the time the table is created.

PRIVILEGES

The Sybase documentation indicates that a user needs create table privilege in tempdb before he/she can create a temporary table. In the current setup at IU, every user that has a login to the Sybase server is able to create tables in tempdb.

In Oracle a user requires create table privilege to create a global temporary table.

STORAGE AND CLEANUP

Sybase temporary tables are all created in tempdb space, not in the current database. Tempdb space is shared among all users. A single user session can fill up all the space in tempdb. Other users will not be able to create tables in tempdb. More importantly, other server processes that require tempdb for other purposes such as sorting or query execution will be blocked by tempdb filling up. Unless this situation is reached, cleanup is not an issue, since it all gets dropped when the session ends.

Oracle global temporary tables:

Which tablespace do they go to? The userss default temporary tablespace

Who owns them? The user that created them.

Do they count against the users quota? Not clear yet.

Oracle global temporary tables are being explored as a replacement to Sybase temporary tables. Users and service providers have been taking advantage of the Sybase temporary table feature in IUDSS for the following purposes:

1- DSS Batch processes often use temporary (pound) tables instead of regular tables to store temporary results. This is particularly advantageous because the data and the tables get dropped when the process ends, making cleanup unnecessary. More importantly, the data and tables also go away when the process abends, which simplifies restart procedures.

2- Some customer applications (PDQ in FDRS) use temporary (pound) tables for the purpose of storing intermediate results, but also to present a customized result set to the user running a particular query. The same table name can be used in the application code, but each time the query executes a different table gets created, which guarantees each user will get only their data.

3- Some sophisticated users create ad-hoc temporary tables (pound or tempdb..) to hold intermediate results (this set of customers could be better described as departmental database programmers). Because Sybase takes care of the uniqueness of names, there are never conflicts between table names created by different users.

IMPLEMENTATION

Oracle temporary tables could be used in batch processes to hold temporary results instead of Sybase temporary tables. There are, however two issues that would need to be handled differently:

1. Because DSS batch processes all run under the same userid, tables used in different processes will need to have different names to avoid conflicts. Naming standards will need to be in place for Oracle temporary tables used in scripts to avoid conflicts, or processes would have to be written in such a way that they would be using names which are guaranteed to be unique via some other mechanism.
2. Currently, DSS batch processes create the temporary table on the fly, which is automatically dropped by the system when the process ends or abends. Because Oracle temp tables dont get dropped, this approach could result in errors, when a process tries to create a table that already exists. This could be avoided by having an if statement that only creates the table when it doesnt already exist or by calling some sort of function that would return unique names and use those names to create tables dynamically. Since the data does get cleared after the session ends or abends, there are no issues related to too much wasted space. All these options seem feasible and DSS needs to select the one that seems more appropriate.
3. Since the team developing batch processes already has privileges to create tables in the DSS environment, no special privileges are required.

EVALUATION

PROS:

This option is supported by Oracle. No extra development to implement.

Data is cleared by the system.

Even if name was not unique across multiple sessions by a single user, different runs of the same script could use the same table and each run would only see its own data.

No special privileges required, since create table is already necessary for batch process userids.

CONS:

Unique names needed for each process

May need to worry about cleanup

ISSUES/UNKNOWNS:

Storage.

It is still not clear where these tables get stored. If its all stored in a central shared location and there are no quotas that would be equivalent to the current Sybase scenario. If there are quotas that can be assigned for different usernames, then this could turn to be a plus. DBA team (Paul Albertson) researching this.

ALTERNATIVES:

1. Use regular tables instead of temporary tables: On the negative side, we would have to cleanup not only the table definitions, but also the data and we would still have to worry about unique names. On the positive side, we know that way quotas can be established, which would limit the amount of space a particular userid could take in the instance.
2. Avoid the use of tables altogether by rewriting batch processes using nested queries and other mechanisms.

RECOMMENDATION:

Several Oracle consultants have said to us that in Oracle there is no need for temporary tables. For new processes we should probably explore writing code that does not rely so heavily on temporary tables.

For existing processes, we may prefer to limit the amount of changes during the conversion to Oracle. In that case, Oracle 8I temporary tables seem to be a better choice than regular tables, because data is discarded as soon as the process has finished. Since our user ids already have create table privileges, the use of temporary tables does not require any extra privileges in this case.

Table names should be dynamically generated and tables created on the fly and discarded, because there is no guarantee that the table structure would not change from one nightly run to another (due to development).

IMPLEMENTATION:

The FDRS PDQ application uses Sybase # temporary tables in a similar way as the DSS batch processes, but there is one additional requirement which makes matters a little more complicated. DSS batch processes are run once every certain period of time (usually once a day) , and only one copy of a particular process runs at a time. FDRS PDQs are designed to be used by multiple users at the same time, but a shared userid is used to access the tables. Furthermore, currently, that application is written in such a way that the table structure used by a particular instance of a PDQ varies depending on the parameters selected by the users. This creates an additional requirement, which is table names must be unique across different sessions of a single userid.

Given the nature of Oracle temporary tables (and also Oracle tables in general), these are the available options:

1. Not using tables to store temporary results. Theoretically, the great majority of queries can be rewritten in Oracle, in such a way that temporary results arent necessary. Even though this is theoretically possible, the conversion effort would be a lot greater if every PDQ needs to be rewritten in this fashion. Our goal is to minimize the conversion effort, so this is not a very desirable option.
2. Change the PDQ application so that it does not use a shared ID. Naming conflicts only seem to occur among tables created by the same user. Instead of a single user ID, the application could use the users Sybase ID. This should be feasible, since every FDRS user already has a Sybase login. However, most users belong to the public group, which currently does not have access to base tables. Most PDQs are such that they go against the base tables. This would be a significant change in policy, the consequences of which would have to be considered (especially in the light of possible upcoming row-level access restrictions). Furthermore, this would still leave unresolved the problems caused by a single user running multiple instances of a single PDQ, each with different parameters, which is quite a plausible scenario.
3. Rewrite queries in such a way that the table structure is not dependent on user parameters. Instead of selecting into a temporary table the columns that correspond to the user parameters, the query could use a table that has all the columns a user can request and then fill in the ones selected by the user in the appropriate fields. This does involve some rewriting of the query logic, but the magnitude of that effort is unknown. The amount of effort this rewrite to that question needs to be researched by the FDRS developers and weighed against other options.

 

4. Finally, there is the option of building into the PDQ application some mechanism by which unique names can be generated for every run of a given PDQ and those names used to dynamically create tables on the fly. Generating table names that are unique within the PDQ application can be accomplished in a number of ways and should be a pretty trivial matter. What may not be so trivial is to have an SQR report use those names to dynamically create tables. SQR is not a very sophisticated language, and it does have some limitations. At the same time, there are some creative workarounds, which SQR developers have come up with out of necessity. There is a good chance that this is a viable option. Like option 3, it needs to be researched and compared to other options.

Several options were discussed in the PDQ implementation section. Option 1 and 2 (extensive rewrite without the use of tables and removing the shared ID) dont use temporary tables and dont seem very feasible, given our constraints.

Both option 3 and 4 use temporary tables. The main differences between 3 and 4 have to do with how to re-code the SQR queries. On the database side, the only difference is that for option 3 the table names will be known ahead of time, whereas in option 4 the table names will be generated automatically. Otherwise, the issues are pretty much the same as the DSS batch processes.

EVALUATION:

PROS

Unlike regular tables, data is automatically cleaned up.

Unlike option of using users own id, no need to change security setup and it solves problem completely.

Unlike the option of not using tables at all, no need to do extensive rewrite of PDQs

CONS

There are no session-specific temporary table definitions in Oracle, so some level of re-coding is necessary.

ISSUES/UNKNOWNS

All of the unknowns listed in the section on DSS batch apply here as well. In addition to those, there are a few new issues/unknowns:

Session-specific table name vs. one table per PDQ

Both options need to be evaluated by FDRS developers to decide which one is more convenient for them to use. From our point of view, both options are pretty much equivalent.

Privileges

How could we grant temporary table privilege to the application, without also granting the privilege to create regular tables. It may be possible to accomplish this even if there is no special privilege, by making sure users do not have any quota on any tablespace. This needs to be tried in hands-on research, possibly working with DBA team.

ALTERNATIVES

1. Not using tables for holding temporary results (i.e. extensive re-write)
2. Not using FMSWEB shared id (security and not complete solution)
3. Use regular tables instead of temp tables (same issues with naming, doesnt handle data cleanup, but does allow us to set limits on the amount of space used, even thought wed probably end up using more space because data isnt cleaned up automatically).

RECOMMENDATION

An extensive re-write of the FDRS pre-defined queries is not desirable, given our conversion target dates. Not using a shared ID has some undesirable consequences from a security perspective. Furthermore, it does not work when the same user tries to run multiple instances of the same query with different parameters. The only choice seems to be using regular tables or temporary tables. As far as I was able to find out, they both require create table privilege, which would be a change with respect to the current environment. With regular tables it is possible to have quotas that limit the amount of space used. I have not been able to confirm that for temporary tables, but in our current Sybase scenario we dont have that kind of control and it doesnt seem to be a big problem. Temporary tables have the advantage that the data is discarded when the session ends. Because of that, it is likely that the PDQ application will require less space if it uses temporary tables. Finally, the FDRS developers need to decide whether they would rather dynamically create session-specific tables, or if they would rather use a single table structure for every pre-defined query.

A minority of our DSS users has been using their access to Sybase tempdb to store temporary result sets, which they would then use in further joins. Some of them are departmental programmers, and others are users who run queries developed by those programmers. In either case, queries are run with the userid of the person who runs the query. Some of the queries run once, others are ran on a periodic basis, but overall, the level of reuse of these queries is much smaller than that of the PDQs or DSS batch processes, which run at least once a day.

Just like in the above cases, the choices are:

1. Rewrite all queries so that there are no temporary results
2. Regular tables
3. Global temporary tables

Our users that fall under this category, have already let us know that option 1 is not a good option for their needs. In many cases their queries are extremely complex and re-writing them without temporary results would be too onerous.

The choice then remains between regular tables and temporary tables. If it is possible to restrict the amount of space that can be taken up by a temporary table via quotas, the temporary tables would probably be a better choice. Otherwise, we would just have to grant create table permission on some user tablespaces so and associate quotas. Queries would have to take care of the cleanup.

DBAS:

Provide info on how to grant create temp table but not regular table privileges (via quotas?)

Provide information on temporary table storage

DSS:

Decide whether to use temporary tables or regular tables for FDRS and AD-HOC

Select best approach for DSS batch process temp table creation: on the fly or static

FDRS:

Select best approach for how to deal with name conflicts in SQR (unique names or unique tables)

The question was raised recently if whether or not PL/SQL Tables and Records can be used for temporary tables. It turns out, that although they seem like they might provide some promise in this area, neither of them are a good fit, for the reasons described below. Believe it or not, I looked at both of these mechanisms for the same purpose about 5 yrs ago and ran into the same limitations....its too bad Oracle hasn't expanded their functionality since then. For more details, see: PL/SQL Tables and Records.