Oracle tablespace Query

1. query user (data) tablespace

Select upper (F. tablespace_name) "tablespace name ",
D. tot_grootte_mb "tablespace 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 ),
'1970. 99') "usage ratio ",
F. total_bytes "Idle space (m )",
F. max_bytes "maximum 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 tablespace

Select D. Status "status", D. tablespace_name "name", D. Contents "type ",
D. extent_management "extent Management ",
To_char (nvl (A. Bytes/1024/1024, 0), '000000') "size (m )",
To_char (nvl (T. bytes, 0)/1024/1024, '2017. 100 ')
| '/'
| To_char (nvl (A. Bytes/1024/1024, 0), '2017. 100') "used (m )",
To_char (nvl (T. Bytes/a. bytes * 100, 0), '2014. 00') "used %"
From SYS. dba_tablespaces D,
(Select tablespace_name, sum (bytes) bytes
From dba_temp_files
Group by tablespace_name),
(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'

-- Shrink temporary tablespace
Alter tablespace tbs_mk_temp coalse;

3. query the table space occupied by a user.
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. Check whether the table is a partition table.
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 logon)

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 locked Process

Alter system kill session '2017 97 ';