WHAT IF THE TABLESPACE IS TEMPORARY AND MANAGED LOCALLY. THE FREE SPACE DOESN'T SHOW UP IN DBA_FREE_SPACE. IS THERE AN EQUIVALENT VIEW FOR TEMPORARY TS ?.

Hi Tom,
thanks for the lots of info on this site.
I use this query for getting the information concerning freespace in LM TEMP :

-- START
col size_temp_files format 99999 Heading "Size|Temp|Files"
col free_space_in_temp_files format 99999 Heading "Free|Space|In|Temp|Files"
col free_space_in_sort_segment format 99999 Heading "Free|Space|In|Sort|Segment"
col used_space_in_sort_segment format 99999 Heading "Used|Space|In|Sort|Segment"
col total_free format 99999 Heading "Total|Free"
select tsh.tablespace_name
, dtf.omvang size_temp_files
, tsh.free_space_space_header free_space_in_temp_files
, nvl(ss.free_space_sort_segment,tsh.used_space_space_header) free_space_in_sort_segment -- could be empty
, nvl(ss.used_space_sort_segment,0) used_space_in_sort_segment
, tsh.free_space_space_header+nvl(ss.free_space_sort_segment,tsh.used_space_space_header) TOTAL_FREE
from
( select tablespace_name, sum(bytes)/1024/1024 omvang from dba_temp_files
group by tablespace_name ) dtf
,(
select tablespace_name
,sum(BYTES_USED)/1024/1024 USED_SPACE_SPACE_HEADER
,sum(BYTES_FREE)/1024/1024 FREE_SPACE_SPACE_HEADER
from v$temp_space_header
group by tablespace_name
) tsh,
( select tablespace_name
,sum(USED_BLOCKS)/1024/1024 USED_SPACE_SORT_SEGMENT
,sum(FREE_BLOCKS)*par.value/1024/1024 FREE_SPACE_SORT_SEGMENT
from v$sort_segment ss
, v$parameter par
where par.name = 'db_block_size'
group by tablespace_name, value
) ss
where dtf.tablespace_name = tsh.tablespace_name
and ss.tablespace_name (+) = dtf.tablespace_name
/
-- END

The script from Dik Pater is excellent!!!

I've tried Dik Pater's script, having a temporary tablespace called TEMP with 2 tempfiles (one 600 Mb size and the other 400 Mb size) and I get:

TABLESPACE_NAME SIZE_TEMP_FILES FREE_SPACE_IN_TEMP_FILES FREE_SPACE_IN_SORT_SEGMENT USED_SPACE_IN_SORT_SEGMENT TOTAL_FREE
------------------------------ --------------- ------------------------ -------------------------- -------------------------- ----------
TEMP 1000 0 599,75 0 599,75


I don't understand what I get, I guess SIZE_TEMP_FILES=1000 is OK, but what about the others columns?

Can someone help me?

You're right : I updated the code.

select tsh.tablespace_name
, dtf.omvang size_temp_files
, tsh.free_space_space_header free_space_in_temp_files
, nvl(ss.free_space_sort_segment,tsh.used_space_space_header) free_space_in_sort_segment -- could be empty
, nvl(ss.used_space_sort_segment,0) used_space_in_sort_segment
, tsh.free_space_space_header+nvl(ss.free_space_sort_segment,tsh.used_space_space_header) TOTAL_FREE
from
( select tablespace_name, sum(bytes)/1024/1024 omvang from dba_temp_files
group by tablespace_name ) dtf
,(
select tablespace_name
,sum(BYTES_USED)/1024/1024 USED_SPACE_SPACE_HEADER
,sum(BYTES_FREE)/1024/1024 FREE_SPACE_SPACE_HEADER
from v$temp_space_header
group by tablespace_name
) tsh,
( select tablespace_name
,sum(USED_BLOCKS)*par.value/1024/1024 USED_SPACE_SORT_SEGMENT
,sum(FREE_BLOCKS)*par.value/1024/1024 FREE_SPACE_SORT_SEGMENT
from v$sort_segment ss
, v$parameter par
where par.name = 'db_block_size'
group by tablespace_name, value
) ss
where dtf.tablespace_name = tsh.tablespace_name
and ss.tablespace_name (+) = dtf.tablespace_name
/

Dear Mr. Kyte

How r u? Hope u r doing well.
I have a few questions related to the below given situation for you.

I have few users say u1, u2, u3 and they all have been assigned their default tablespace as T1 and temporary tablespace as Tmp. The above default tablespace T1 is a free list Tablespace that was creted by me without using
"Segement Space Management Auto" clause.

Now the thing is, as we all know that the Tablespaces created with their "Segement Space Management Auto", are considered good and hassle free from the performance and maintainance point of view. I want to assign the above users u1, u2, u3 a new tablespace which has its segement space management auto. For achieving this, I created a new tablespace T2 with its "Segment Space Management Auto".

-->Now can you pls suggest me, how should i migrate the users which are working on free list tablespace T1 to a tablespace T2 which has "Segment Space Management Auto", along with all of their objects (Tables, Views, Procedures, etc).

-->Will issuing the "ALTER USER... DEFAULT TABLESPACE T2;" serve the purpose? if not, what should I do to achieve the above task?

-->Suppose that I have already issued the "ALTER USER... DEFAULT TABLESPACE T2;" command and users are working on the database, what effect will it have on their objects and database(Incase i have not dropped T1, and assigned T2 to u1, u2, u3)?

-->Does altering a default tablespace of any user, migrates the user along with his objects to the new tablespace or would his objects become invalid and useless or even may get dropped?

--> what will happen to user/user objects if I isuue the "ALTER USER...DEFAULT TABLESPACE T2;" and drop the tablespace T1?

Sorry i know these are lot many questions, and some may even be Kiddish, but i cant help putting them up to you for thier answers and clarification.

Take car. Regards...
Vikas.

Thanx for the information.
Regards...