User Tablespaces

alter session enable parallel query;	
	  
define owner='SCOTT'		  --- change if required 
with all_ts_user as
   (
   select tablespace_name 		from dba_lobs   			where 1=1 and OWNER='&owner'         union all
   select tablespace_name 		from dba_clusters  			where 1=1 and OWNER='&owner'         union all
   select tablespace_name 		from dba_indexes 			where 1=1 and OWNER='&owner'         union all
   select tablespace_name 		from dba_rollback_segs 			where 1=1 and OWNER='&owner'         union all
   select tablespace_name 		from dba_tables  			where 1=1 and OWNER='&owner'         union all
   select tablespace_name 		from dba_object_tables 			where 1=1 and OWNER='&owner'         union all
   select def_tablespace_name 	        from dba_part_tables  			where 1=1 and OWNER='&owner'         union all
   select def_tablespace_name 	        from dba_part_indexes 			where 1=1 and OWNER='&owner'         union all
   select tablespace_name 		from dba_tab_partitions  		where 1=1 and TABLE_OWNER='&owner'   union all
   select tablespace_name 		from dba_ind_partitions  		where 1=1 and index_OWNER='&owner'   union all
   select tablespace_name 		from dba_tab_subpartitions 		where 1=1 and TABLE_OWNER='&owner'   union all
   select tablespace_name 		from dba_ind_subpartitions  		where 1=1 and index_OWNER='&owner'   union all
   select def_tablespace_name 	        from dba_part_lobs 			where 1=1 and TABLE_OWNER='&owner'   union all
   select tablespace_name 		from dba_lob_partitions   		where 1=1 and TABLE_OWNER='&owner'   union all
   select tablespace_name 		from dba_lob_subpartitions  		where 1=1 and TABLE_OWNER='&owner'   union all
   select tablespace_name 		from dba_subpartition_templates 	where 1=1 and USER_NAME='&owner'     union all
   select tablespace_name 		from dba_lob_templates  		where 1=1 and USER_NAME='&owner'     union all
   select tablespace_name 		from dba_segments     			where 1=1 and OWNER='&owner'         union all
   select tablespace_name 		from dba_extents   			where 1=1 and OWNER='&owner'         union all
   select tablespace_name 		from dba_undo_extents 			where 1=1 and OWNER='&owner'
   )
   select distinct tablespace_name from all_ts_user
alter session disable parallel query;



TABLESPACE_NAME
------------------------------
USERS
DATA

	
alter session enable parallel query;	

set pagesize 300 	  
define owner='SCOTT'	
		  
with all_possible_ts as
   (
   select 'dba_lobs' From1 			,tablespace_name from dba_lobs   where 1=1 and OWNER= '&owner'         					union all
   select 'dba_clusters'			,tablespace_name from dba_clusters  where 1=1 and OWNER= '&owner'                 		union all
   select 'dba_indexes' 			,tablespace_name from dba_indexes where 1=1 and OWNER= '&owner'                   		union all
   select 'dba_rollback_segs' 			,tablespace_name from dba_rollback_segs where 1=1 and OWNER= '&owner'             		union all
   select 'dba_tables'				,tablespace_name from dba_tables  where 1=1 and OWNER= '&owner'                   		union all
   select 'dba_object_tables' 			,tablespace_name from dba_object_tables where 1=1 and OWNER= '&owner'              		union all
   select 'dba_part_tables' 			,def_tablespace_name from dba_part_tables  where 1=1 and OWNER= '&owner'          		union all
   select 'dba_part_indexes' 			,def_tablespace_name from dba_part_indexes where 1=1 and OWNER= '&owner'          		union all
   select 'dba_tab_partitions' 			,tablespace_name from dba_tab_partitions  where 1=1 and TABLE_OWNER= '&owner'          	union all
   select 'dba_ind_partitions' 			,tablespace_name from dba_ind_partitions  where 1=1 and index_OWNER= '&owner'          	union all
   select 'dba_tab_subpartitions' 		,tablespace_name from dba_tab_subpartitions where 1=1 and TABLE_OWNER= '&owner'        	union all
   select 'dba_ind_subpartitions' 		,tablespace_name from dba_ind_subpartitions  where 1=1 and index_OWNER= '&owner'       	union all
   select 'dba_part_lobs'			,def_tablespace_name from dba_part_lobs where 1=1 and TABLE_OWNER= '&owner'            	union all
   select 'dba_lob_partitions' 			,tablespace_name from dba_lob_partitions   where 1=1 and TABLE_OWNER= '&owner'         	union all
   select 'dba_lob_subpartitions'		,tablespace_name from dba_lob_subpartitions  where 1=1 and TABLE_OWNER= '&owner'       	union all
   select 'dba_subpartition_templates' 	        ,tablespace_name from dba_subpartition_templates where 1=1 and USER_NAME= '&owner'     	union all
   select 'dba_lob_templates' 			,tablespace_name from dba_lob_templates  where 1=1 and USER_NAME= '&owner'            	union all
   select 'dba_segments ' 			,tablespace_name from dba_segments     where 1=1 and OWNER= '&owner'              		union all
   select 'dba_extents' 			,tablespace_name from dba_extents   where 1=1 and OWNER= '&owner'                 		union all
   select 'dba_undo_extents' 			,tablespace_name from dba_undo_extents where 1=1 and OWNER= '&owner'
   )
   select distinct * from all_possible_ts