Temporary Tablespace in oracle:
Temporary tablespace is created by default during oracle database installation. By default temp tablespace is created and it is assigned to all users if we have not specified any tablespace . users used this tablespace while doing and executing big operations such as sorting,joining and union so on. During these big operations a huge amount of memory is required to store such information to avoid this oracle assigned temporary tablespace to do these jobs.View temporary tablespace in oracle database:
To view temporary tablespace we are using dba_temp_files data dictionary table. Need to execute the below command.SQL>select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 from dba_temp_files;
Here TEMP is created by default while installing the oracle database and the temporary file name is temp01.dbf with size 29mb.
How to create temporary tablespace in oracle :
Now we have one temporary tablespace . Now we want to create one more temporary tablespace as per our requirement. We can assign one temporary tablespace for some users and another temporary tablespace for some other users. We can distribute it to get better performance. To do this we need to execute the below command.Note: To create a temporary tablespace we must use this temporary keyword. If we miss this keyword normal tablespace will be created.
SQL>create temporary tablespace TEMP01 tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' size 10m;
Now temporary tablespace TEMP01 is created with the tempfile temp02.dbf with 10mb.
How to resize temporary tablespace in oracle :
Now we have two temporary tablespaces named TEMP and TEMP01. We want to resize temp02.dbf file with size 10mb to 15mb we can execute the below command.alter database tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' resize 15m;
View and set default temporary tablespace in oracle database:
we have two temporary tablespaces and need to view which one is default. We need to execute the below command.SQL>select * from database_properties where property_name like '%TABLESPACE%';
SQL>alter database default temporary tablespace TEMP01;
Now the default temporary tablespace is TEMP01 we can not drop it because it default temporary tablespace to drop this tablespace we need to set TEMP default temporary tablespace then we can drop it otherwise not.
In this tutorial we have learned what is temporary tablespace, to view temporary tablespace using oracle data dictionary table ,how to create temporary tablespace ,how to resize temporary tablespace and how to set default temporary tablespace.
In the next article we will learn :
.
0 comments:
Post a Comment