Oracle Table Space Fundamentals (3)

One, temporary table space

The temporary tablespace contains only the ephemeral data generated during the session, improving the concurrency of multiple sort operations that cannot be done in memory, while also improving the efficiency of space management in sorting operations. Within a temporary tablespace, a sort operation for a particular instance shares a sort segment, which is created when the database is started and released when the database is closed.

After the installation of the database, a temporary tablespace named temp exists in the database, and the temp tablespace can be shared by multiple users within the database. You can also create a temporary tablespace using the Create temporary tablespace statement, and use the Create/alter user statement to specify a temporary tablespace for the user.

Because of the simplicity and efficiency of a locally managed tablespace in space management, temporary tablespace is also appropriate for local management, and the TEMPFILE clause is used when creating a locally managed temporal tablespace.

# Create a temporary tablespace sql> create temporary tablespace tmp_tbs  2  tempfile ' D:\app\oracle\oradata\orcl\temp02.dbf ' size 10m  3  extent management local; table space created. # Modify the default temporary tablespace sql> ALTER DATABASE default temporary tablespace Tmp_tbs; # view default temporal tablespace sql> select Property_name,property_value from database_properties  2  where property_name= ' Default_temp_tablespace '; Property_name                  property_value--------------------------------------------------default_temp_tablespace        temp# View the usage rate of the temporary tablespace sql> select * from dba_temp_free_space where tablespace_name= ' temp '; Tablespace_name      tablespace_size allocated_space free_space-------------------------------------------------- ----------TEMP                        30408704        30408704   29360128

Second, temporary table space Group

The use of temporary tablespace groups prevents the occurrence of a temporary table space that cannot accommodate all user sorts.

# Create a staging tablespace group:sql> create temporary tablespace tmp_ts    2  tempfile ' D:\app\oracle\oradata\orcl\temp03.dbf ' size 5m    3  tablespace group group1; table space is created. # Add temporary tablespace to temporary tablespace group:sql> alter tablespace TMP_TBS tablespace group group1; table space has changed. # Remove a temporary tablespace from the temporary table space group sql> alter tablespace TMP_TBS tablespace Group "; Tablespace changed.

# when the temporary tablespace group is the database default temporary tablespace, deleting the last member will make an error: Ora-10919:defualt temporary tablespace group must be has at least one tablespace, performing the following language Sentence, and then delete the last member, the temporary table space group automatically disappears
Sql>alter database default temporary tablespace temp;
The database has changed.

# Use a temporary tablespace group as the default temporary tablespace sql> ALTER DATABASE ORCL default temporary tablespace group1; The database has changed.  sql> Select Property_name,property_value from database_properties    2  where property_name= ' Default_ Temp_tablespace '; Property_name                  property_value--------------------------------------------------default_temp_tablespace        GROUP1

　

Oracle Table Space Fundamentals (3)