Oracle Table Space Query ____oracle

1. Query user (data) Table space

Select UPPER (f.tablespace_name) "Table space name",
D.TOT_GROOTTE_MB "Table space size (M)",
D.tot_grootte_mb-f.total_bytes "used Space (M)",
To_char (ROUND (d.tot_grootte_mb-f.total_bytes)/d.tot_grootte_mb * 100,
2),
' 990.99 ') "Use ratio",
F.total_bytes "free Space (M)",
F.max_bytes "Max Block (M)"
From (SELECT Tablespace_name,
ROUND (SUM (BYTES)/(1024 * 1024), 2) Total_bytes,
ROUND (MAX (BYTES)/(1024 * 1024), 2 max_bytes
From SYS. Dba_free_space
GROUP by Tablespace_name) F,
(SELECT DD. Tablespace_name,
ROUND (SUM (DD). BYTES)/(1024 * 1024), 2 TOT_GROOTTE_MB
From SYS. Dba_data_files DD
GROUP by DD. Tablespace_name) D
WHERE D.tablespace_name = F.tablespace_name
ORDER BY 4 DESC

2. Query temporary table space

Select D.status "Status", D.tablespace_name "name", d.contents "Type",
D.extent_management "Extent Management",
To_char (NVL (a.bytes/1024/1024, 0), ' 99,999,990.900 ') "Size (M)",
To_char (NVL (t.bytes, 0)/1024/1024, ' 99999999.999 ')
|| '/'
|| To_char (NVL (a.bytes/1024/1024, 0), ' 99999999.999 ') "Used (M)",
To_char (NVL (t.bytes/a.bytes * 0), ' 990.00 ') "Used%"
From Sys.dba_tablespaces D,
(SELECT tablespace_name, SUM (BYTES) BYTES
From Dba_temp_files
GROUP by Tablespace_name) A,
(SELECT tablespace_name, SUM (bytes_cached) bytes
From V$temp_extent_pool
GROUP by Tablespace_name) t
WHERE D.tablespace_name = a.tablespace_name (+)
and D.tablespace_name = T.tablespace_name (+)
and d.extent_management like ' local '
and d.contents like ' temporary '

--Shrinking the temp table space
Alter Tablespace tbs_mk_temp Coalse;

3. Query the space occupied by a user's table
Select T.segment_name,sum (t.bytes)/1024/1024
From User_segments t
Where segment_name like ' tb% '
GROUP BY T.segment_name
ORDER by sum (t.bytes)/1024/1024 desc

4. Whether the query table is a partitioned table, the size occupied
SELECT *
From User_segments t
Where segment_name like ' tb% '
and T.segment_name= ' Tb_fi_fig_winner_cust_mon '

5. Query the locked process (DBA login)

Select sess.sid,sess.serial#, Lo.oracle_username,lo.os_user_name,ao.object_name, Sess. Logon_time,lo.locked_mode
From V$locked_object lo,dba_objects ao,v$session sess
where ao.object_id = lo.object_id
and lo.session_id = Sess.sid
Order by Sess. Logon_time;

6. Kill the process of being locked down

Alter system kill session ' 1997,33097 ';