ORACLE 8I GLOBAL TEMPORARY TABLES
By Montse Guilla
ORACLE TEMPORARY TABLES
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.
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.
SYBASE TEMPORARY TABLES
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.
ORACLE TEMPORARY TABLES VERSUS SYBASE TEMPORARY TABLES
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).
Sybase # tables:
Table definition and data are session specific. Both get dropped when the session that created the table ends.
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.
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.
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.
APPLICATIONS OF ORACLE GLOBAL TEMPORARY TABLES
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.
ORACLE GLOBAL TEMPORARY TABLES FOR BATCH DSS PROCESSES
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:
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.
Unique names needed for each process
May need to worry about cleanup
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.
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).
ORACLE GLOBAL TEMPORARY TABLES FOR FDRS/PDQ
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:
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.
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
There are no session-specific temporary table definitions in Oracle, so some level of re-coding is necessary.
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.
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.
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.
ORACLE GLOBAL TEMPORARY TABLES FOR AD-HOC PURPOSES
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:
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.
Provide info on how to grant create temp table but not regular table privileges (via quotas?)
Provide information on temporary table storage
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
Select best approach for how to deal with name conflicts in SQR (unique names or unique tables)
Hands On Research
PL/SQL Tables and Records
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.