Temporary tablespace
Temporary table is used for operation like sorting and grouping function etc
Each user would be assign for one temporary table space. If no temporary table space is assigned. The user would use the default temporary table space..
List User and its default temporary tablespace
select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,profile from DBA_USERS;
How to find the file location for the TEMP tablespace?
The information can be found at V$TEMPFILE and DBA_TEMP_FILE
select * from V$TEMPFILE;
select * from DBA_TEMP_FILES;
How to find out the current usage for the temp tablespace?
select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;
Create the user temporary table space
Below exercise is to create the temporary tablespace and assign to the user.
Create temporary tablespace myusertemp01 TEMPFILE '/home/oracle/app/oracle/oradata/orcl/usertemp01.dbf' size 100M;
col tablespace_name format a20
col "FILE_NAME" format a20
select DT.TABLESPACE_NAME,DT.contents,DT.STATUS,DT.SEGMENT_SPACE_MANAGEMENT, DT2.FILE_NAME,DT2.BYTES
from DBA_TABLESPACES DT, DBA_TEMP_FILES DT2
where DT.TABLESPACE_NAME= DT2.TABLESPACE_NAME;
create user po3 identified by oracle;
select username,temporary_tablespace from dba_users where username='PO3';
alter user po3 temporary tablespace myusertemp01;
select username,temporary_tablespace from dba_users where username='PO3';
Drop temp tablespace
If we don’t use “INCLUDING CONTENTS AND DATAFILES”, the tablespace would ONLY be drop from data dictionary but the file still exist on the operation. We then need to use OS level command to remove it.
-- Clean env
drop user PO3;
drop tablespace myusertemp01 INCLUDING CONTENTS AND DATAFILES;
temp table space group
Tempgroup
tempgroup is another layer of the logical grouping for the multiple temp tablespace. The purpose for this is to have more then one temp tablespace file to reduce the IO contention or if the temp tablespace has reach the max file system size then we have to add the another file on the different directory to increase the temp tablespace size.
In this example, we create 2 tablespace and assign them to tempgroup_a. If there the tempgroup_a does not exist, it would be created the tempgroup_a automatically when first temp tablespace is added.
select tablespace_name, group_name from DBA_TABLESPACE_GROUPS;
Create temporary tablespace myusertemp01 TEMPFILE '/home/oracle/app/oracle/oradata/orcl/usertemp01.dbf' size 50M;
Create temporary tablespace myusertemp02 TEMPFILE '/home/oracle/app/oracle/oradata/orcl/usertemp02.dbf' size 50M;
-- If there the tempgroup_a does not exist, it would be created the tempgroup_a automatically when first temp tablespace is added.
ALTER TABLESPACE myusertemp01 TABLESPACE GROUP tempgroup_a;
ALTER TABLESPACE myusertemp02 TABLESPACE GROUP tempgroup_a;
select tablespace_name, group_name from DBA_TABLESPACE_GROUPS;
create user po3 identified by oracle;
select username,temporary_tablespace from dba_users where username='PO3';
alter user po3 temporary tablespace tempgroup_a;
select username,temporary_tablespace from dba_users where username='PO3';
Remove the Tempgroup
Use alter tablespace to remove the tablespace from tempgroup. When there is no tablespace left in the tempgroup, tempgroup would be remove automatically.
ALTER TABLESPACE myusertemp01 TABLESPACE GROUP '';
ALTER TABLESPACE myusertemp02 TABLESPACE GROUP '';
select tablespace_name, group_name from DBA_TABLESPACE_GROUPS;
select username,temporary_tablespace from dba_users where username='PO3';
Even the TEMPGROUP_A does not exist anymore, but user PO3 still be able to run the query because at this point, the user PO3 is using the default tablespace.
Shrink temporary tablespace
KEEP keyword would shrink the tablespace to the keep size.
Without the keep keyword, it would shrink to its min size.
Create temporary tablespace myusertemp01 TEMPFILE size 100M; alter tablespace myusertemp01 add tempfile size 100M; select SUM(bytes) from dba_temp_files where tablespace_name='MYUSERTEMP01'; alter tablespace myusertemp01 shrink space keep 50M; select SUM(bytes) from dba_temp_files where tablespace_name='MYUSERTEMP01'; alter tablespace myusertemp01 shrink space; select SUM(bytes) from dba_temp_files where tablespace_name='MYUSERTEMP01';
I am happy to read them.
ReplyDeleteIncall girls
Rolex watches are crafted from the finest raw materials and assembled with scrupulous attention to detail. cheap replica watchesEvery component is designed, replica rolex watchesdeveloped and produced in-house to the most exacting standards.
ReplyDeleteFounded in 1905, Rolex watches have been in the replica rolex watches field of innovation for more than a century. The replica Air-King watches first certified precision chronograph, the first self-winding watch replica Cellini watches and the first effective waterproof case are milestones in Rolex design. Today, the Rolex brand is immediately recognized as a symbol of global reputation, luxury and innovation. Buying a Rolex watch means you are buying a watch history. Here we have a large catalog of used Rolex special watches for sale.
ReplyDeleteThis is such a great resource that you are providing and you give it away for free. I love seeing blog that understand the value. Im glad to have found this post as its such an interesting one! I am always on the lookout for quality posts and articles so i suppose im lucky to have found this! I hope you will be adding more in the future... Unlawful Termination Application
ReplyDelete