Oracle Temporary Table

Syntax: CREATE GLOBAL TEMPORARY TABLE ... ON COMMIT DELETE|PRESERVE ROWS;

DELETE ROWS: specify it for a transaction-specific temporary table. This is the default. Oracle database will truncate the table (delete all its rows) after each commit.

PRESERVE ROWS: specify it for a session-specific temporary table. Oracle database will truncate the table (delete all its rows) when the session is terminated.

When a temporary table is created the first time, its table metadata is stored in the data dictionary, but no space is allocated. Space is allocated for the table segement at the time of the first DML operation on the table, onto the user's default temporary tablespace. Multiple sessions/users can use the same temporary table definition but each session will be allocated with its own temporary table segment.

Privilege: for an user to access the temporary table in another schema, it needs the same privileges: SELECT, INSERT, UPDATE or DELETE as a normal table. However the user just needs INSERT privilege in order to use it and be able to DELETE|PRESERVE ROWS, which doesn't depend on the other privileges.

Daylight time saving - issue with cron jobs

I had two Unix cron jobs to stop then start Oracle instances and listeners on Sundays weekly. It had run fine until this Monday morning my developers reported that they couldn't connect to Oracle.

After investigation I found the culprit being the daylight time saving. The stop job was scheduled @2:15am and the start job @3:00am. From the logs I found the stop jobs was actually kicked off @3:00am due to the daylight time saving, as well as the start job, so both jobs were run at the same time, which caused the listeners were not started.

So suggestion is not to schedule jobs between 2~3am on Sundays.