| Oracle® Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-01 |
|
|
View PDF |
| Oracle® Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-01 |
|
|
View PDF |
Before you can create a tablespace, you must create a database to contain it. The primary tablespace in any database is the SYSTEM tablespace, which contains information basic to the functioning of the database server, such as the data dictionary and the system rollback segment. The SYSTEM tablespace is the first tablespace created at database creation. It is managed as any other tablespace, but requires a higher level of privilege and is restricted in some ways. For example, you cannot rename or drop the SYSTEM tablespace or take it offline.
The SYSAUX tablespace, which acts as an auxiliary tablespace to the SYSTEM tablespace, is also always created when you create a database. It contains information about and the schemas used by various Oracle products and features, so that those products do not require their own tablespaces. As for the SYSTEM tablespace, management of the SYSAUX tablespace requires a higher level of security and you cannot rename or drop it. The management of the SYSAUX tablespace is discussed separately in "Managing the SYSAUX Tablespace".
The steps for creating tablespaces vary by operating system, but the first step is always to use your operating system to create a directory structure in which your datafiles will be allocated. On most operating systems, you specify the size and fully specified filenames of datafiles when you create a new tablespace or alter an existing tablespace by adding datafiles. Whether you are creating a new tablespace or modifying an existing one, the database automatically allocates and formats the datafiles as specified.
To create a new tablespace, use the SQL statement CREATE TABLESPACE or CREATE TEMPORARY TABLESPACE. You must have the CREATE TABLESPACE system privilege to create a tablespace. Later, you can use the ALTER TABLESPACE or ALTER DATABASE statements to alter the tablespace. You must have the ALTER TABLESPACE or ALTER DATABASE system privilege, correspondingly.
You can also use the CREATE UNDO TABLESPACE statement to create a special type of tablespace called an undo tablespace, which is specifically designed to contain undo records. These are records generated by the database that are used to roll back, or undo, changes to the database for recovery, read consistency, or as requested by a ROLLBACK statement. Creating and managing undo tablespaces is the subject of Chapter 14, "Managing Undo".
The creation and maintenance of permanent and temporary tablespaces are discussed in the following sections:
Multiple Temporary Tablespaces: Using Tablespace Groups
See Also:
Chapter 2, "Creating and Configuring an Oracle Database" and your Oracle Database installation documentation for your operating system for information about tablespaces that are created at database creation
Oracle Database SQL Language Reference for more information about the syntax and semantics of the CREATE TABLESPACE, CREATE TEMPORARY TABLESPACE, ALTER TABLESPACE, and ALTER DATABASE statements.
"Specifying Database Block Sizes" for information about initialization parameters necessary to create tablespaces with nonstandard block sizes
Locally managed tablespaces track all extent information in the tablespace itself by using bitmaps, resulting in the following benefits:
Fast, concurrent space operations. Space allocations and deallocations modify locally managed resources (bitmaps stored in header files).
Enhanced performance
Readable standby databases are allowed, because locally managed temporary tablespaces do not generate any undo or redo.
Space allocation is simplified, because when the AUTOALLOCATE clause is specified, the database automatically selects the appropriate extent size.
User reliance on the data dictionary is reduced, because the necessary information is stored in file headers and bitmap blocks.
Coalescing free extents is unnecessary for locally managed tablespaces.
All tablespaces, including the SYSTEM tablespace, can be locally managed.
The DBMS_SPACE_ADMIN package provides maintenance procedures for locally managed tablespaces.
See Also:
"Creating a Locally Managed SYSTEM Tablespace", "Migrating the SYSTEM Tablespace to a Locally Managed Tablespace", and "Diagnosing and Repairing Locally Managed Tablespace Problems"
"Bigfile Tablespaces" for information about creating another type of locally managed tablespace that contains only a single datafile or tempfile.
Oracle Database PL/SQL Packages and Types Reference for information on the DBMS_SPACE_ADMIN package
Create a locally managed tablespace by specifying LOCAL in the EXTENT MANAGEMENT clause of the CREATE TABLESPACE statement. This is the default for new permanent tablespaces, but you must specify the EXTENT MANAGEMENT LOCAL clause if you want to specify either the AUTOALLOCATE clause or the UNIFORM clause. You can have the database manage extents for you automatically with the AUTOALLOCATE clause (the default), or you can specify that the tablespace is managed with uniform extents of a specific size (UNIFORM).
If you expect the tablespace to contain objects of varying sizes requiring many extents with different extent sizes, then AUTOALLOCATE is the best choice. AUTOALLOCATE is also a good choice if it is not important for you to have a lot of control over space allocation and deallocation, because it simplifies tablespace management. Some space may be wasted with this setting, but the benefit of having Oracle Database manage your space most likely outweighs this drawback.
If you want exact control over unused space, and you can predict exactly the space to be allocated for an object or objects and the number and size of extents, then UNIFORM is a good choice. This setting ensures that you will never have unusable space in your tablespace.
When you do not explicitly specify the type of extent management, Oracle Database determines extent management as follows:
If the CREATE TABLESPACE statement omits the DEFAULT storage clause, then the database creates a locally managed autoallocated tablespace.
If the CREATE TABLESPACE statement includes a DEFAULT storage clause, then the database considers the following:
If you specified the MINIMUM EXTENT clause, the database evaluates whether the values of MINIMUM EXTENT, INITIAL, and NEXT are equal and the value of PCTINCREASE is 0. If so, the database creates a locally managed uniform tablespace with extent size = INITIAL. If the MINIMUM EXTENT, INITIAL, and NEXT parameters are not equal, or if PCTINCREASE is not 0, the database ignores any extent storage parameters you may specify and creates a locally managed, autoallocated tablespace.
If you did not specify MINIMUM EXTENT clause, the database evaluates only whether the storage values of INITIAL and NEXT are equal and PCTINCREASE is 0. If so, the tablespace is locally managed and uniform. Otherwise, the tablespace is locally managed and autoallocated.
The following statement creates a locally managed tablespace named lmtbsb and specifies AUTOALLOCATE:
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
AUTOALLOCATE causes the tablespace to be system managed with a minimum extent size of 64K.
The alternative to AUTOALLOCATE is UNIFORM. which specifies that the tablespace is managed with extents of uniform size. You can specify that size in the SIZE clause of UNIFORM. If you omit SIZE, then the default size is 1M.
The following example creates a tablespace with uniform 128K extents. (In a database with 2K blocks, each extent would be equivalent to 64 database blocks). Each 128K extent is represented by a bit in the extent bitmap for this file.
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
You cannot specify the DEFAULT storage clause, MINIMUM EXTENT, or TEMPORARY when you explicitly specify EXTENT MANAGEMENT LOCAL. If you want to create a temporary locally managed tablespace, use the CREATE TEMPORARY TABLESPACE statement.
Note:
When you allocate a datafile for a locally managed tablespace, you should allow space for metadata used for space management (the extent bitmap or space header segment) which are part of user space. For example, if specify theUNIFORM clause in the extent management clause but you omit the SIZE parameter, then the default extent size is 1MB. In that case, the size specified for the datafile must be larger (at least one block plus space for the bitmap) than 1MB.In a locally managed tablespace, there are two methods that Oracle Database can use to manage segment space: automatic and manual. Manual segment space management uses linked lists called "freelists" to manage free space in the segment, while automatic segment space management uses bitmaps. Automatic segment space management is the more efficient method, and is the default for all new permanent, locally managed tablespaces.
Automatic segment space management delivers better space utilization than manual segment space management. It is also self-tuning, in that it scales with increasing number of users or instances. In an Oracle Real Application Clusters environment, automatic segment space management allows for a dynamic affinity of space to instances. In addition, for many standard workloads, application performance with automatic segment space management is better than the performance of a well-tuned application using manual segment space management.
Although automatic segment space management is the default for all new permanent, locally managed tablespaces, you can explicitly enable it with the SEGMENT SPACE MANAGEMENT AUTO clause.
The following statement creates tablespace lmtbsb with automatic segment space management:
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
The SEGMENT SPACE MANAGEMENT MANUAL clause disables automatic segment space management.
The segment space management that you specify at tablespace creation time applies to all segments subsequently created in the tablespace. You cannot change the segment space management mode of a tablespace.
Notes:
If you set extent management to LOCAL UNIFORM, then you must ensure that each extent contains at least 5 database blocks.
If you set extent management to LOCAL AUTOALLOCATE, and if the database block size is 16K or greater, then Oracle manages segment space by creating extents with a minimum size of 5 blocks rounded up to 64K.
Locally managed tablespaces using automatic segment space management can be created as single-file or bigfile tablespaces, as described in "Bigfile Tablespaces".
A bigfile tablespace is a tablespace with a single, but very large (up to 4G blocks) datafile. Traditional smallfile tablespaces, in contrast, can contain multiple datafiles, but the files cannot be as large. The benefits of bigfile tablespaces are the following:
A bigfile tablespace with 8K blocks can contain a 32 terabyte datafile. A bigfile tablespace with 32K blocks can contain a 128 terabyte datafile. The maximum number of datafiles in an Oracle Database is limited (usually to 64K files). Therefore, bigfile tablespaces can significantly enhance the storage capacity of an Oracle Database.
Bigfile tablespaces can reduce the number of datafiles needed for a database. An additional benefit is that the DB_FILES initialization parameter and MAXDATAFILES parameter of the CREATE DATABASE and CREATE CONTROLFILE statements can be adjusted to reduce the amount of SGA space required for datafile information and the size of the control file.
Bigfile tablespaces simplify database management by providing datafile transparency. SQL syntax for the ALTER TABLESPACE statement lets you perform operations on tablespaces, rather than the underlying individual datafiles.
Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment space management, with three exceptions: locally managed undo tablespaces, temporary tablespaces, and the SYSTEM tablespace.
Notes:
Bigfile tablespaces are intended to be used with Automatic Storage Management (ASM) or other logical volume managers that supports striping or RAID, and dynamically extensible logical volumes.
Avoid creating bigfile tablespaces on a system that does not support striping because of negative implications for parallel query execution and RMAN backup parallelization.
Using bigfile tablespaces on platforms that do not support large file sizes is not recommended and can limit tablespace capacity. Refer to your operating system specific documentation for information about maximum supported file sizes.
To create a bigfile tablespace, specify the BIGFILE keyword of the CREATE TABLESPACE statement (CREATE BIGFILE TABLESPACE ...). Oracle Database automatically creates a locally managed tablespace with automatic segment space management. You can, but need not, specify EXTENT MANAGEMENT LOCAL and SEGMENT SPACE MANAGEMENT AUTO in this statement. However, the database returns an error if you specify EXTENT MANAGEMENT DICTIONARY or SEGMENT SPACE MANAGEMENT MANUAL. The remaining syntax of the statement is the same as for the CREATE TABLESPACE statement, but you can only specify one datafile. For example:
CREATE BIGFILE TABLESPACE bigtbs
DATAFILE '/u02/oracle/data/bigtbs01.dbf' SIZE 50G
...
You can specify SIZE in kilobytes (K), megabytes (M), gigabytes (G), or terabytes (T).
If the default tablespace type was set to BIGFILE at database creation, you need not specify the keyword BIGFILE in the CREATE TABLESPACE statement. A bigfile tablespace is created by default.
If the default tablespace type was set to BIGFILE at database creation, but you want to create a traditional (smallfile) tablespace, then specify a CREATE SMALLFILE TABLESPACE statement to override the default tablespace type for the tablespace that you are creating.
The following views contain a BIGFILE column that identifies a tablespace as a bigfile tablespace:
DBA_TABLESPACES
USER_TABLESPACES
V$TABLESPACE
You can also identify a bigfile tablespace by the relative file number of its single datafile. That number is 1024 on most platforms, but 4096 on OS/390.
You can encrypt any permanent tablespace to protect sensitive data. Tablespace encryption is completely transparent to your applications, so no application modification is necessary. Encrypted tablespaces primarily protect your data from unauthorized access by means other than through the database. For example, when encrypted tablespaces are written to backup media for travel from one Oracle database to another or for travel to an off-site facility for storage, they remain encrypted. Also, encrypted tablespaces protect data from users who try to circumvent the security features of the database and access database files directly through the operating system file system.
Tablespace encryption does not address all security issues. It does not, for example, provide access control from within the database. Any user who is granted privileges on objects stored in an encrypted tablespace can access those objects without providing any kind of additional password or key.
When you encrypt a tablespace, all tablespace blocks are encrypted. All segment types are supported for encryption, including tables, clusters, indexes, LOBs (BASICFILE and SECUREFILE), table and index partitions, and so on.
Note:
There is no need to use LOB encryption onSECUREFILE LOBs stored in an encrypted tablespace.To maximize security, data from an encrypted tablespace is automatically encrypted when written to the undo tablespace, to the redo logs, and to any temporary tablespace. There is no need to explicitly create encrypted undo or temporary tablespaces, and in fact, you cannot specify encryption for those tablespace types.
For partitioned tables and indexes that have different partitions in different tablespaces, it is permitted to use both encrypted and non-encrypted tablespaces in the same table or index.
Tablespace encryption uses the transparent data encryption feature of Oracle Database, which requires that you create an Oracle wallet to store the master encryption key for the database. The wallet must be open before you can create the encrypted tablespace and before you can store or retrieve encrypted data. When you open the wallet, it is available to all session, and it remains open until you explicitly close it or until the database is shut down.
To encrypt a tablespace, you must open the database with the COMPATIBLE initialization parameter set to 11.1.0 or higher. The default setting for COMPATIBLE for a new Oracle Database 11g Release 1 installation is 11.1.0. Any user who can create a tablespace can create an encrypted tablespace.
Transparent data encryption supports industry-standard encryption algorithms, including the following Advanced Encryption Standard (AES) and Triple Data Encryption Standard (3DES) algorithms:
3DES168
AES128
AES192
AES256
The encryption key length is implied by the algorithm name. For example, the AES128 algorithm uses 128-bit keys. You specify the algorithm to use when you create the tablespace, and different tablespaces can use different algorithms. Although longer key lengths theoretically provide greater security, there is a trade-off in CPU overhead. If you do not specify the algorithm in your CREATE TABLESPACE statement, AES128 is the default. There is no disk space overhead for encrypting a tablespace.
Examples
The following statement creates an encrypted tablespace with the default encryption algorithm:
CREATE TABLESPACE securespace DATAFILE '/u01/app/oracle/oradata/orcl/secure01.dbf' SIZE 100M ENCRYPTION DEFAULT STORAGE(ENCRYPT);
The following statement creates the same tablespace with the AES256 algorithm:
CREATE TABLESPACE securespace DATAFILE '/u01/app/oracle/oradata/orcl/secure01.dbf' SIZE 100M ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);
Restrictions
The following are restrictions for encrypted tablespaces:
You cannot encrypt an existing tablespace with an ALTER TABLESPACE statement. However, you can use Data Pump or SQL statements such as CREATE TABLE AS SELECT or ALTER TABLE MOVE to move existing table data into an encrypted tablespace.
Encrypted tablespaces are subject to restrictions when transporting to another database. See "Limitations on Transportable Tablespace Use".
When recovering a database with encrypted tablespaces (for example after a SHUTDOWN ABORT or a catastrophic error that brings down the database instance), you must open the Oracle wallet after database mount and before database open, so the recovery process can decrypt data blocks and redo.
In addition, see Oracle Database Advanced Security Administrator's Guide for general restrictions for transparent data encryption.
Querying Tablespace Encryption Information
The DBA_TABLESPACES and USER_TABLESPACES data dictionary views include a column named ENCRYPTED. This column contains YES for encrypted tablespaces.
The view V$ENCRYPTED_TABLESPACES lists all currently encrypted tablespaces. The following query displays the name and encryption algorithm of encrypted tablespaces:
SELECT t.name, e.encryptionalg algorithm FROM v$tablespace t, v$encrypted_tablespaces e WHERE t.ts# = e.ts#; NAME ALGORITHM ------------------------------ --------- SECURESPACE AES128
See Also:
Oracle Database 2 Day + Security Guide for more information about transparent data encryption and for instructions for creating and opening wallets
"Consider Encrypting Columns That Contain Sensitive Data" for an alternative to encrypting an entire tablespace
Oracle Real Application Clusters Administration and Deployment Guide for information on using an Oracle wallet in an Oracle Real Application Clusters environment
Oracle Database SQL Language Reference for information about the CREATE TABLESPACE statement
A temporary tablespace contains transient data that persists only for the duration of the session. Temporary tablespaces can improve the concurrency of multiple sort operations that do not fit in memory and can improve the efficiency of space management operations during sorts.
Temporary tablespaces are used to store the following:
Intermediate sort results
Temporary tables and temporary indexes
Temporary LOBs
Temporary B-trees
Within a temporary tablespace, all sort operations for a particular instance share a single sort segment, and sort segments exist for every instance that performs sort operations that require temporary space. A sort segment is created by the first statement after startup that uses the temporary tablespace for sorting, and is released only at shutdown.
By default, a single temporary tablespace named TEMP is created for each new Oracle Database installation. You can create additional temporary tablespaces with the CREATE TABLESPACE statement. You can assign a temporary tablespace to each database user with the CREATE USER or ALTER USER statement. A single temporary tablespace can be shared by multiple users.
You cannot explicitly create objects in a temporary tablespace.
Note:
The exception to the preceding statement is a temporary table. When you create a temporary table, its rows are stored in your default temporary tablespace, unless you create the table in a new temporary tablespace. See "Creating a Temporary Table" for more information.Default Temporary Tablespace
Users who are not explicitly assigned a temporary tablespace use the database default temporary tablespace, which for new installations is TEMP. You can change the default temporary tablespace for the database with the following command:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tablespace_name;
To determine the current default temporary tablespace for the database, run the following query:
SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; PROPERTY_NAME PROPERTY_VALUE -------------------------- ------------------------------ DEFAULT_TEMP_TABLESPACE TEMP
Space Allocation in a Temporary Tablespace
You can view the allocation and deallocation of space in a temporary tablespace sort segment using the V$SORT_SEGMENT view. The V$SORT_USAGE view identifies the current sort users in those segments.
When a sort operation that uses temporary space completes, allocated extents in the sort segment are not deallocated; they are just marked as free and available for reuse. The DBA_TEMP_FREE_SPACE view displays the total allocated and free space in each temporary tablespace. See "Viewing Space Usage for Temporary Tablespaces" for more information. You can manually shrink a locally managed temporary tablespace that has a large amount of unused space. See "Shrinking a Locally Managed Temporary Tablespace" for details.
See Also:
Oracle Database Security Guide for information about creating users and assigning temporary tablespaces
Oracle Database Concepts for more information about the default temporary tablespace
Oracle Database Reference for more information about the V$SORT_SEGMENT, V$SORT_USAGE, and DBA_TEMP_FREE_SPACE views
Oracle Database Performance Tuning Guide for a discussion on tuning sorts
Because space management is much simpler and more efficient in locally managed tablespaces, they are ideally suited for temporary tablespaces. Locally managed temporary tablespaces use tempfiles, which do not modify data outside of the temporary tablespace or generate any redo for temporary tablespace data. Because of this, they enable you to perform on-disk sorting operations in a read-only or standby database.
You also use different views for viewing information about tempfiles than you would for datafiles. The V$TEMPFILE and DBA_TEMP_FILES views are analogous to the V$DATAFILE and DBA_DATA_FILES views.
To create a locally managed temporary tablespace, you use the CREATE TEMPORARY TABLESPACE statement, which requires that you have the CREATE TABLESPACE system privilege.
The following statement creates a temporary tablespace in which each extent is 16M. Each 16M extent (which is the equivalent of 8000 blocks when the standard block size is 2K) is represented by a bit in the bitmap for the file.
CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE '/u02/oracle/data/lmtemp01.dbf'
SIZE 20M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
The extent management clause is optional for temporary tablespaces because all temporary tablespaces are created with locally managed extents of a uniform size. The default for SIZE is 1M. But if you want to specify another value for SIZE, you can do so as shown in the preceding statement.
Note:
On some operating systems, the database does not allocate space for the tempfile until the tempfile blocks are actually accessed. This delay in space allocation results in faster creation and resizing of tempfiles, but it requires that sufficient disk space is available when the tempfiles are later used. Please refer to your operating system documentation to determine whether the database allocates tempfile space in this way on your system.Just as for regular tablespaces, you can create single-file (bigfile) temporary tablespaces. Use the CREATE BIGFILE TEMPORARY TABLESPACE statement to create a single-tempfile tablespace. See the sections "Creating a Bigfile Tablespace" and "Altering a Bigfile Tablespace" for information about bigfile tablespaces, but consider that you are creating temporary tablespaces that use tempfiles instead of datafiles.
The DBA_TEMP_FREE_SPACE dictionary view contains information about space usage for each temporary tablespace. The information includes the space allocated and the free space. You can query this view for these statistics using the following command.
SELECT * from DBA_TEMP_FREE_SPACE; TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE ----------------------------------- --------------- --------------- ---------- TEMP 250609664 250609664 249561088
A tablespace group enables a user to consume temporary space from multiple tablespaces. Using a tablespace group, rather than a single temporary tablespace, can alleviate problems caused where one tablespace is inadequate to hold the results of a sort, particularly on a table that has many partitions. A tablespace group enables parallel execution servers in a single parallel operation to use multiple temporary tablespaces.
A tablespace group has the following characteristics:
It contains at least one tablespace. There is no explicit limit on the maximum number of tablespaces that are contained in a group.
It shares the namespace of tablespaces, so its name cannot be the same as any tablespace.
You can specify a tablespace group name wherever a tablespace name would appear when you assign a default temporary tablespace for the database or a temporary tablespace for a user.
You do not explicitly create a tablespace group. Rather, it is created implicitly when you assign the first temporary tablespace to the group. The group is deleted when the last temporary tablespace it contains is removed from it.
The view DBA_TABLESPACE_GROUPS lists tablespace groups and their member tablespaces.
See Also:
Oracle Database Security Guide for more information about assigning a temporary tablespace or tablespace group to a userYou create a tablespace group implicitly when you include the TABLESPACE GROUP clause in the CREATE TEMPORARY TABLESPACE or ALTER TABLESPACE statement and the specified tablespace group does not currently exist.
For example, if neither group1 nor group2 exists, then the following statements create those groups, each of which has only the specified tablespace as a member:
CREATE TEMPORARY TABLESPACE lmtemp2 TEMPFILE '/u02/oracle/data/lmtemp201.dbf'
SIZE 50M
TABLESPACE GROUP group1;
ALTER TABLESPACE lmtemp TABLESPACE GROUP group2;
You can add a tablespace to an existing tablespace group by specifying the existing group name in the TABLESPACE GROUP clause of the CREATE TEMPORARY TABLESPACE or ALTER TABLESPACE statement.
The following statement adds a tablespace to an existing group. It creates and adds tablespace lmtemp3 to group1, so that group1 contains tablespaces lmtemp2 and lmtemp3.
CREATE TEMPORARY TABLESPACE lmtemp3 TEMPFILE '/u02/oracle/data/lmtemp301.dbf'
SIZE 25M
TABLESPACE GROUP group1;
The following statement also adds a tablespace to an existing group, but in this case because tablespace lmtemp2 already belongs to group1, it is in effect moved from group1 to group2:
ALTER TABLESPACE lmtemp2 TABLESPACE GROUP group2;
Now group2 contains both lmtemp and lmtemp2, while group1 consists of only tmtemp3.
You can remove a tablespace from a group as shown in the following statement:
ALTER TABLESPACE lmtemp3 TABLESPACE GROUP '';
Tablespace lmtemp3 no longer belongs to any group. Further, since there are no longer any members of group1, this results in the implicit deletion of group1.
Use the ALTER DATABASE...DEFAULT TEMPORARY TABLESPACE statement to assign a tablespace group as the default temporary tablespace for the database. For example:
ALTER DATABASE sample DEFAULT TEMPORARY TABLESPACE group2;
Any user who has not explicitly been assigned a temporary tablespace will now use tablespaces lmtemp and lmtemp2.
If a tablespace group is specified as the default temporary tablespace, you cannot drop any of its member tablespaces. You must first remove the tablespace from the tablespace group. Likewise, you cannot drop a single temporary tablespace as long as it is the default temporary tablespace.
