Oracle Global Temporary Table / PHP interaction question

Question

I've never used the Global Temporary Tables however I have some questions how they will work in a php environment.

How is data shared: Assuming persistent connections to oracle through php using oci8. Is the data tied to a database id? is it done based on the Apache httpd demons? Or is each individual request unique?

When is the data for the session cleared from the global temporary table? I'm assuming (or rather hoping) that its done when the php script exits. Alternatively if not I'm assuming I'll need to remove it before script exit.

Answer 1

If I remember correct, the data in global temporary tables is available only from one active session and only for this active session (I mean session = connection). So you can see only data which was inserted before in active session. Therefore I belive, this data is cleared after closing session. No matter which language are you using.

At least I think so. :D

As is it written here: http://www.oracle-base.com/articles/8i/TemporaryTables.php

The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session.

Data in temporary tables is automatically delete at the end of the database session, even if it ends abnormally.

Sorry for my bad english.

Answer 2

The global temporary table is simply the logical definition of a table structure (Name, column names, column data types etc). When a session references it by inserting data, a data segment is created in a temporary tablespace to hold only that session's data. Different sessions can therefore reference the same logical table definition because they each have their own dedicated data segment which can be purged easily on commit or when the session disconnects without affecting other sessions.

The purging of the data in the GTT can either be on commit or when the session ends, depending on the option with which it was created. In either case you do not have to attend to the purging yourself before disconnecting.

A useful alternative to the GTT is the subquery factoring clause ("WITH"), in which you can create multiple relations that can reference those previously declared in that SQL statement. These can be materialised as a data segment in a temporary tablespace either automatically by Oracle when they exceed a certain memory usage, or manually by using the MATERIALIZE optimizer hint.