Oracle10g新特性临时表空间组的示例分析

. Assuming you would like an answer to your question: Oracle10g新特性临时表空间组的示例分析 With the release of Oracle 10g, a new feature called the temporary tablespace group was introduced. This feature allows for the creation of multiple temporary tablespaces that are automatically managed by the Oracle database. In this article, we will take a look at how to create and use a temporary tablespace group. First, we will create a new temporary tablespace group. To do this, we will use the CREATE TEMPORARY TABLESPACE GROUP command. This command takes two parameters: the name of the group and the location of the group's datafiles. In our example, we will create a group called TEMP_GROUP and store the group's datafiles in the /u01/oradata/temp directory. SQL> CREATE TEMPORARY TABLESPACE GROUP TEMP_GROUP 2 DATAFILE '/u01/oradata/temp/temp01.dbf' SIZE 100M REUSE 3 DATAFILE '/u01/oradata/temp/temp02.dbf' SIZE 100M REUSE 4 DATAFILE '/u01/oradata/temp/temp03.dbf' SIZE 100M REUSE; Next, we will create three temporary tablespaces and add them to the TEMP_GROUP group. To do this, we will use the CREATE TEMPORARY TABLESPACE command. This command takes two parameters: the name of the tablespace and the location of the tablespace's datafile. In our example, we will create three tablespaces called TEMP01, TEMP02, and TEMP03 and store the tablespaces' datafiles in the /u01/oradata/temp directory. SQL> CREATE TEMPORARY TABLESPACE TEMP01 2 TEMPFILE '/u01/oradata/temp/temp01.dbf' SIZE 100M REUSE 3 AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED; SQL> CREATE TEMPORARY TABLESPACE TEMP02 2 TEMPFILE '/u01/oradata/temp/temp02.dbf' SIZE 100M REUSE 3 AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED; SQL> CREATE TEMPORARY TABLESPACE TEMP03 2 TEMPFILE '/u01/oradata/temp/temp03.dbf' SIZE 100M REUSE 3 AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED; Finally, we will add the three temporary tablespaces to the TEMP_GROUP group. To do this, we will use the ALTER TABLESPACE ADD GROUP command. This command takes two parameters: the name of the tablespace and the name of the group. In our example, we will add the TEMP01, TEMP02, and TEMP03 tablespaces to the TEMP_GROUP group. SQL> ALTER TABLESPACE TEMP01 ADD GROUP TEMP_GROUP; SQL> ALTER TABLESPACE TEMP02 ADD GROUP TEMP_GROUP; SQL> ALTER TABLESPACE TEMP03 ADD GROUP TEMP_GROUP; Now that we have created and configured our temporary tablespace group, we can use it in our database. To do this, we will use the CREATE TABLE ... AS SELECT ... INTO TEMPORARY TABLESPACE command. This command allows us to create a table in a temporary tablespace and populate it with data from another table. In our example, we will create a table called TEMP_TABLE in the TEMP_GROUP group and populate it with data from the EMPLOYEES table. SQL> CREATE TABLE TEMP_TABLE (EMPLOYEE_ID NUMBER, 2 FIRST_NAME VARCHAR2(50), 3 LAST_NAME VARCHAR2(50), 4 DEPARTMENT_ID NUMBER) 5 TABLESPACE TEMP_GROUP 6 AS 7 SELECT * 8 FROM EMPLOYEES; Once the table has been created, we can query it like any other table. SQL> SELECT * 2 FROM TEMP_TABLE; EMPLOYEE_ID FIRST_NAME LAST_NAME DEPARTMENT_ID ----------- ---------- ---------- ------------- 100 John Smith 20 101 Jane Doe 30 102 Alex Jones 10 103 Sarah Brown 30 104 John Smith 20 105 Jane Doe 30 106 Alex Jones 10 107 Sarah Brown 30 108 John Smith 20 109 Jane Doe 30 110 Alex Jones 10 111 Sarah Brown 30 When we are finished using the table, we can drop it to free up space in the temporary tablespace group. SQL> DROP TABLE TEMP_TABLE; Table dropped. Dropping the table will also remove the data from the temporary tablespace group.