Temporary Table space
The tablespace group name must be an existing tablespace group name or one that does not exists. In addition, it must not be the name of an existing tablespace or you will get an error such as the following that notifies you that the tablespace already exists.
We can also switch a tablespace's group, add a tablespace to a group or create a new tablespace group by using the following statement.
If you wanted to remove a tablespace from a tablespace group and not assign it to a new tablespace group, you would use the following statement where the TABLESPACE GROUP clause is used but an empty string is used for the group.
You cannot get rid of a tablespace group explicitly. In order for a tablespace group to be implicitly removed from the database, you need to alter those tablespaces to either use another tablespace group or alter them not to use any tablespace groups by assigning them to an explicit tablespace.
DBA_TABLESPACE_GROUPS View
You can always take a look at what tablespace groups you have defined by looking at the DBA_TABLESPACE_GROUPS view. Issue the following SQL and you can see what we have already defined for our running example.
Setting the Default Temporary Tablespace
Now that we have defined multiple tablespaces to be part of a tablespace group called GTEMP, we can now use this tablespace group just as any other tablespace. One use of a tablespace group can be for the default temporary tablespace. If you wish to assign this new tablespace group as the default temporary tablespace, just issue the following SQL. Now any user who you have not given a temporary tablespace at user creation time will be assigned this new tablespace group as their temporary tablespace.
The temporary tablespace within large organizations that require many sort operations typically would experience high levels of contention. It was then left up to the DBA to segregate sort operations by users and create islands of temporary tablespaces so that those operations would not contend for similar resources that are associated with a single temporary tablespace. The problem with this is that the very large operations would typically need independent large temporary tablespaces. This would lead to wasted resources. With the addition of tablespace groups, Oracle now allows us to group multiple temporary tablespaces together so that we have one more choice in the usage patterns of these temporary tablespaces. We now need to ask ourselves if an operation truly needs its own individual temporary tablespace or the operation can span multiple temporary tablespaces and work without contention with the other operations that are occurring. If so, we can consolidate, reduce disk consumption, and possibly contention.
Temporary Tablespaces
Temporary tablespaces are used for special operations,
particularly for sorting data results on disk. For SQL with millions of rows
returned, the sort operation is too large for the RAM area and must occur on
disk. The temporary tablespace is where this takes place.
Each database should have one temporary tablespace that
is created when the database is created. You create, drop and manage
tablespaces with create temporary tablespace, drop temporary tablespace and
alter temporary tablespace commands, each of which is like it’s create
tablespace counterpart.
The only other difference is that a temporary tablespace
uses temporary files (also called tempfiles) rather than regular datafiles.
Thus, instead of using the datafiles keyword you use the tempfiles keyword when
issuing a create, drop or alter tablespace command as you can see in these
examples:
CREATE TEMPORARY TABLESPACE temp
TEMPFILE ‘/ora01/oracle/oradata/booktst_temp_01.dbf’ SIZE 50m;
DROP TEMPORARY TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
Tempfiles are a bit different than datafiles in that you
may not immediately see them grow to the size that they have been allocated
(this particular functionality is platform dependent). Hence, don’t panic if
you see a file that looks too small.
Data Dictionary View
dba_temp_files
Describes each tempfile in the database
The Evolution of the TEMPORARY
Tablespace
1. Oracle allowed DBAs to create a normal tablespace and assign this to users to use as a temporary tablespace through the CREATE USER or ALTER USER commands. These tablespaces could hold temporary segments and permanent segments. Oracle's position at that time was to advise everyone not to place permanent segments into these tablespaces that were also being used as TEMPORARY tablespaces for users.
2. Oracle then implemented the notion of a true TEMPORARY tablespace. These TEMPORARY tablespaces were to be assigned to users at creation time and could only hold temporary segments. This was a great step in the right direction because now we could not place permanent objects into them and no longer could users be assigned permanent tablespaces as their temporary tablespaces.
3. Oracle then went one step further and allowed us to create a DEFAULT TEMPORARY tablespace for the entire database. The benefit this gives is, if we forget to specify a temporary tablespace for the user, they will inherit the DEFAULT TEMPORARY tablespace as theirs.
4. Now in Oracle 10g, Oracle lets us group temporary tablespaces together to gain a bit more flexibility that allows us to create multiple temporary tablespaces and to create tablespace groups and assign these tablespace groups to users.
Tablespace Groups
Oracle now has the concept of grouping multiple tablespaces together in what they call a tablespace group. This allows you to create multiple temporary tablespaces, assign these temporary tablespaces to a tablespace group, and then use the tablespace group as the default temporary tablespace for the database or user. The benefit this has is that a single SQL statement or set of SQL statements may use more than the one temporary tablespace you have created in the past and now span multiple tablespaces. This has the following benefits if you create your temporary tablespaces and tablespace groups properly.
1. You can tailor user load to individual temporary tablespaces.
2. You can allow large sort operations to span and consume multiple temporary tablespaces if needed.
3. Finer granularity so you can distribute operations across temporary tablespaces.
4. Reduced contention when multiple temporary tablespaces are defined.
The Life of a Tablespace Group
You can implicitly create a tablespace group during the creation of a temporary tablespace with the CREATE TEMPORARY TABLESPACE command and by specifying the TABLESPACE GROUP clause. There is no CREATE TABLESPACE GROUP statement. Therefore, during the creation of a temporary tablespace called GTEMP01 we can add this tablespace to a tablespace group called GTEMP by using the following statement.
1. Oracle allowed DBAs to create a normal tablespace and assign this to users to use as a temporary tablespace through the CREATE USER or ALTER USER commands. These tablespaces could hold temporary segments and permanent segments. Oracle's position at that time was to advise everyone not to place permanent segments into these tablespaces that were also being used as TEMPORARY tablespaces for users.
2. Oracle then implemented the notion of a true TEMPORARY tablespace. These TEMPORARY tablespaces were to be assigned to users at creation time and could only hold temporary segments. This was a great step in the right direction because now we could not place permanent objects into them and no longer could users be assigned permanent tablespaces as their temporary tablespaces.
3. Oracle then went one step further and allowed us to create a DEFAULT TEMPORARY tablespace for the entire database. The benefit this gives is, if we forget to specify a temporary tablespace for the user, they will inherit the DEFAULT TEMPORARY tablespace as theirs.
4. Now in Oracle 10g, Oracle lets us group temporary tablespaces together to gain a bit more flexibility that allows us to create multiple temporary tablespaces and to create tablespace groups and assign these tablespace groups to users.
Tablespace Groups
Oracle now has the concept of grouping multiple tablespaces together in what they call a tablespace group. This allows you to create multiple temporary tablespaces, assign these temporary tablespaces to a tablespace group, and then use the tablespace group as the default temporary tablespace for the database or user. The benefit this has is that a single SQL statement or set of SQL statements may use more than the one temporary tablespace you have created in the past and now span multiple tablespaces. This has the following benefits if you create your temporary tablespaces and tablespace groups properly.
1. You can tailor user load to individual temporary tablespaces.
2. You can allow large sort operations to span and consume multiple temporary tablespaces if needed.
3. Finer granularity so you can distribute operations across temporary tablespaces.
4. Reduced contention when multiple temporary tablespaces are defined.
The Life of a Tablespace Group
You can implicitly create a tablespace group during the creation of a temporary tablespace with the CREATE TEMPORARY TABLESPACE command and by specifying the TABLESPACE GROUP clause. There is no CREATE TABLESPACE GROUP statement. Therefore, during the creation of a temporary tablespace called GTEMP01 we can add this tablespace to a tablespace group called GTEMP by using the following statement.
CREATE TEMPORARY TABLESPACE GTEMP01 TEMPFILE 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DATEN\GTEMP01.DBF' SIZE 100M TABLESPACE GROUP GTEMP;
The tablespace group name must be an existing tablespace group name or one that does not exists. In addition, it must not be the name of an existing tablespace or you will get an error such as the following that notifies you that the tablespace already exists.
CREATE TEMPORARY TABLESPACE GTEMP TEMPFILE 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DATEN\GTEMP.DBF' SIZE 100M SQL> / CREATE TEMPORARY TABLESPACE GTEMP * ERROR at line 1: ORA-01543: tablespace 'GTEMP' already exists
We can also switch a tablespace's group, add a tablespace to a group or create a new tablespace group by using the following statement.
ALTER TABLESPACE GTEMP02 TABLESPACE GROUP GTEMP; ALTER TABLESPACE GTEMP03 TABLESPACE GROUP GTEMP;
If you wanted to remove a tablespace from a tablespace group and not assign it to a new tablespace group, you would use the following statement where the TABLESPACE GROUP clause is used but an empty string is used for the group.
ALTER TABLESPACE GTEMP03 TABLESPACE GROUP '';
You cannot get rid of a tablespace group explicitly. In order for a tablespace group to be implicitly removed from the database, you need to alter those tablespaces to either use another tablespace group or alter them not to use any tablespace groups by assigning them to an explicit tablespace.
DBA_TABLESPACE_GROUPS View
You can always take a look at what tablespace groups you have defined by looking at the DBA_TABLESPACE_GROUPS view. Issue the following SQL and you can see what we have already defined for our running example.
SQL> SELECT group_name, tablespace_name FROM dba_tablespace_groups; GROUP_NAME TABLESPACE_NAME ------------------------------ ------------------- GTEMP GTEMP01 GTEMP GTEMP02
Setting the Default Temporary Tablespace
Now that we have defined multiple tablespaces to be part of a tablespace group called GTEMP, we can now use this tablespace group just as any other tablespace. One use of a tablespace group can be for the default temporary tablespace. If you wish to assign this new tablespace group as the default temporary tablespace, just issue the following SQL. Now any user who you have not given a temporary tablespace at user creation time will be assigned this new tablespace group as their temporary tablespace.
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE GTEMP; Database altered.
The temporary tablespace within large organizations that require many sort operations typically would experience high levels of contention. It was then left up to the DBA to segregate sort operations by users and create islands of temporary tablespaces so that those operations would not contend for similar resources that are associated with a single temporary tablespace. The problem with this is that the very large operations would typically need independent large temporary tablespaces. This would lead to wasted resources. With the addition of tablespace groups, Oracle now allows us to group multiple temporary tablespaces together so that we have one more choice in the usage patterns of these temporary tablespaces. We now need to ask ourselves if an operation truly needs its own individual temporary tablespace or the operation can span multiple temporary tablespaces and work without contention with the other operations that are occurring. If so, we can consolidate, reduce disk consumption, and possibly contention.
0 comments:
Post a Comment