OracleBlog

I listened intently to the new Oracle programmer as he described all the struggles he's been having on his first big project. As I've done many times already in his short career, I interrupt with some words of wisdom.

"It's time to add Global Temporary Tables to your toolbelt."

"What are those?" he asks, as he opens the directory with the Oracle documentation. I smile. He has already learned where I always send him first.

"They're the ultimate work tables," I continue. "They're permanent tables, where you can add and modify session-specific data without affecting other sessions."

"What's so special about that?" he asks. "Even with regular tables, you can add and modify the data all you want without affecting other sessions. Just don't commit, and remember to rollback when your session is done."

"Oh yeah? And what about all the persistent work you're doing in your session? How do you commit that?"

"Oh yeah. Does it allow for indexes, and triggers, and views with regular tables?"

"Yep, all of that. See for yourself, it's easy. You've got the manual in front of you, so you drive."

Then I watched as he opened one session, and created a global temporary table.

SQL> CREATE GLOBAL TEMPORARY TABLE worktable (x NUMBER(3));

Table created.

He opened another session and was pleased to see the table there. He then added a row in the first session, committed it, and was planning to use the other session to see if the data was there. But instead he was in for a little surprise.

SQL> INSERT INTO worktable (x) VALUES (1);

1 row created.

SQL> SELECT * FROM worktable;

        X
----------
        1

SQL> commit;

Commit complete.

SQL> SELECT * FROM worktable;

no rows selected

"Hey!" he shouted. Heads popped up in nearby cubicles. "Where did it go?"

"Keep reading," I said, gesturing towards the "ON COMMIT" options for Global Temporary Tables. "By default, every time you commit your data, it is assumed that you want to clear out your work tables. Try PRESERVE."

He dropped the table, and tried again.

SQL> CREATE GLOBAL TEMPORARY TABLE worktable
 2  (x NUMBER(3))
 3  ON COMMIT PRESERVE ROWS;

Table created.

SQL> INSERT INTO worktable (x) VALUES (1);

1 row created.

SQL> SELECT * FROM worktable;

        X
----------
        1

SQL> commit;

Commit complete.

SQL> SELECT * FROM worktable;

        X
----------
        1

"That's better," he smiled. Now let's check the other session.

SQL> SELECT * FROM worktable;

no rows selected

"Excellent. So this data will remain there until my session ends?"

"Yep. Try it." He exited his session, logged back in, and sure enough the data was gone. "This is great. But what if I want to get rid of the data at some point in my session?"

"Truncate. Truncating the work table will only truncate the data in your session, not all the data."

"Hey neat. Thanks, this will be very useful. What are you doing for lunch later?"

"Aren't you even going to try it? What, you're just going to trust me?" I said. I think he was a little surprised that I would I'd rather talk about work tables than lunch. Frankly, so am I. Thankfully, he worked quickly, typing first in his second session.

SQL> INSERT INTO worktable (x) VALUES (2)

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT * FROM worktable;

        X
----------
        2

SQL> TRUNCATE TABLE worktable;

Table truncated.

SQL> SELECT * FROM worktable;

no rows selected

SQL> commit;

Commit complete.

"I don't think you need all those commits," I laughed. "But ok, now look back in your first session. If the row you added previously isn't there, then I'm a big fat liar. Otherwise, we can talk about lunch."

SQL> SELECT * FROM worktable;

        X
----------
        1

"So how about sushi?" he asked.

"Sushi?" I groaned. "Haven't you learned anything from me?"