| |
Table space is a temporary disk space used primarily to store a user execute order by interim statements lamp sort data generated or aggregated. By default, all users are using temp as the default temporary table space. But allows the use of other temporary tablespace as the default temporary table space, which needs to be specified when creating the user.
Create a temporary table space is required to use temprary keyword and the corresponding temporary table space is a temporary file, specified by the tempfile keyword, that is, the temporary table space is no longer used in the data file, but with temporary files.
As follows:
SQL> create temporary tablespace mytemp tempfile 'f: \ Oracledata \ mytemp.dbf' size 5m autoextend on next 2m maxsize 20m;
Table space has been created.
At this point in the directory f oracledata disk files can be found mytemp.dbf
SQL> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TEMPGROUP ONLINE
TEMPGROUP02 ONLINE
MYBIGSPACE ONLINE
BLOCKSPACE ONLINE
INSPUR ONLINE
TESTSPACE ONLINE
TEST ONLINE
MYTEMP ONLINE
Line 12 has been selected.
In this case we can find MYTEMP table space table space.
Modify the temporary table space
After you create a temporary table space, you can modify the table space. Since the temporary file does not store temporary data during operation permanent data storage sorting knowledge generated, and create after the operation, the data stored in the temporary files deleted by the user in the system, so under normal circumstances do not need to adjust the temporary table space. But when multiple users, especially the art of war, and the operation is more complicated, may be insufficient temporary table space occur. In this case, the administrator can add temporary data files to increase the temporary table space.
If you need to increase or decrease the temporary file, you can use the add tempfile clause.
If you need to modify the size of the temporary file, you can use the resize keyword.
You can also change the temporary status of offline or online.
SQL> select file #, status, name from v $ tempfile;
FILE # STATUS NAME
-------------------------------------------------- ---------------------------------------------
1 ONLINE D: \ ORACLE \ ORADATA \ ORACLE12C \ TEMP01.DBF
2 ONLINE D: \ ORACLE \ ORADATA \ ORACLE12C \ PDBSEED \ PDBSEED_TEMP012015-01-03_05-20-33-PM.DBF
3 ONLINE D: \ ORACLE \ ORADATA \ ORACLE12C \ PDBORCL \ PDBORCL_TEMP012015-01-03_05-44-35-PM.DBF
4 ONLINE F: \ ORACLEDATA \ MYTEMP.DBF
6 ONLINE F: \ ORACLEDATA \ TEMPGROUP01.DBF
7 ONLINE F: \ ORACLEDATA \ TEMPGROUP02.DBF
Selected 6 rows.
SQL> alter tablespace mytemp add tempfile 'f: \ oracledata \ mytemp2.dbf' size 10m;
Table space has changed.
SQL> select file #, status, name from v $ tempfile;
FILE # STATUS NAME
-------------------------------------------------- --------------------------------------------
1 ONLINE D: \ ORACLE \ ORADATA \ ORACLE12C \ TEMP01.DBF
2 ONLINE D: \ ORACLE \ ORADATA \ ORACLE12C \ PDBSEED \ PDBSEED_TEMP012015-01-03_05-20-33-PM.DBF
3 ONLINE D: \ ORACLE \ ORADATA \ ORACLE12C \ PDBORCL \ PDBORCL_TEMP012015-01-03_05-44-35-PM.DBF
4 ONLINE F: \ ORACLEDATA \ MYTEMP.DBF
5 ONLINE F: \ ORACLEDATA \ MYTEMP2.DBF
6 ONLINE F: \ ORACLEDATA \ TEMPGROUP01.DBF
7 ONLINE F: \ ORACLEDATA \ TEMPGROUP02.DBF
As indicated above, f disk oracledata folder created under a file mytemp2.dbf |
|
|
|