Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Oct 10, 2003

Oracle's Default TEMPORARY Tablespaces - Page 2

By James Koopmann

The TEMPORARY Tablespace

You can create a TEMPORARY tablespace by issuing the statement in Listing 3. This is the simplest form of the CREATE TEMPORARY command. You may also issue a statement such as in Listing 4. The difference in the two statements is the specifying of the TEMPFILE location. In the first example, Oracle will use the initialization parameter DB_CREATE_FILE_DEST if specified. If this parameter is not specified you must use the statement in Listing 4 or the statement will fail. If you would like to set the DB_CREATE_FILE_DEST, just issue a statement such as that in Listing 5. If you are wondering how the files look for the tablespaces just created you can look at Listing 6.

Listing 3
Creating a TEMPORARY tablespace

CREATE TEMPORARY TABLESPACE temp01

Listing 4
Creating a TEMPORARY tablespace

CREATE TEMPORARY TABLESPACE temp02
                 TEMPFILE 'C:\ORACLE\ORADATA\HCMC\ts02.tmp' SIZE 100M;

Listing 5
Specifying the DB_CREATE_FILE_DEST parameter

alter system set db_create_file_dest = 'C:\ORACLE\ORADATA\HCMC';

Listing 6
Outcome of creating TEMPORARY tablespaces

-SQL> select tablespace_name,file_name,bytes/1024/1024 from dba_temp_files;

TABLESPACE_NAME   FILE_NAME                                         BYTES/1024/102
----------------- ------------------------------------------------  --------------
TEMP01            C:\ORACLE\ORADATA\HCMC\O1_MF_TEMP01_ZR0VCW00_.TMP 100
TEMP02            C:\ORACLE\ORADATA\HCMC\TS02.TMP                   100

 

The DEFAULT TEMPORARY Tablespace

The default temporary tablespace is nothing more than a temporary tablespace that has been designated a global temporary tablespace for all users that have not been explicitly defined a temporary tablespace at user creation time. You can specify a default temporary tablespace by issuing a statement such as in Listing 7.

Listing 7
Specify the DEFAULT TEMPORARY Tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp01;

Now if we were to create a user, their temporary tablespace would be TEMP01. The best thing about using default temporary tablespaces is that if you were to switch the default temporary tablespace, all users that were assigned the initial default temporary tablespace would inherit the new default temporary tablespace. Take a look at Listing 8 to see the progression of switching a default temporary tablespace and the users' inheriting it.

Listing 8
Users inheriting the DEFAULT TEMPORARY Tablespace

-SQL> CREATE USER tempuser IDENTIFIED BY tempuser;
User created.

-SQL> select username,temporary_tablespace from dba_users where username = 'TEMPUSER';
USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
TEMPUSER                       TEMP01

-SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp02;
Database altered.

-SQL> select username,temporary_tablespace from dba_users where username = 'TEMPUSER';
USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
TEMPUSER                       TEMP02

The DEFAULT Tablespace

Ok, too much of a good thing. Now the last remaining problem with creation of a user is the DEFAULT tablespace. When we create a user and do not specify the default tablespace, the user is assigned the SYSTEM tablespace. Look at Listing 9. I personally will look to Oracle to change this very soon. I understand the reasoning behind assigning temporary tablespaces and default tablespaces to the SYSTEM tablespace in earlier versions. Most of the systems were not that large and many installations were just running with the SYSTEM tablespace. The unfortunate thing is that today, a single tablespace cannot hold all the data and performance is much more important in the systems of today.

Listing 9
Creation of a user being assigned a default tablespace of SYSTEM

-SQL> select username,temporary_tablespace,default_tablespace 
        from dba_users where username = 'TEMPUSER';

USERNAME        TEMPORARY_TABLESPACE           DEFAULT_TABLESPACE
--------------- ------------------------------ ------------------------------
TEMPUSER        TEMP02                         SYSTEM

In previous releases of Oracle, if you forgot to assign a temporary tablespace to a user, the SYSTEM tablespace was used. This could and did cause some major contention and performance issues. Oracle has always stated that it was a bad practice to assign the SYSTEM tablespace to a users' default or temporary tablespace. While getting the DEFAULT tablespace assignment still requires some intervention, the assigning of a TEMPORARY tablespaces has become a bit less worrisome.

» See All Articles by Columnist James Koopmann



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date