tablespace datafiles sizes in KB MB GB TB PB EB ZB YB

select 
file_id,
tablespace_name,
file_name,
bytes,
case 
when bytes between 0 and 1023 then bytes || ' bytes'
when bytes < power(1024,2) then round(bytes / 1024,4) || ' KB'
when bytes < power(1024,3) then round(bytes / power(1024,2),4) || ' MB'
when bytes < power(1024,4) then round(bytes / power(1024,3),4) || ' GB'
when bytes < power(1024,5) then round(bytes / power(1024,4),4) || ' TB'
when bytes < power(1024,6) then round(bytes / power(1024,5),4) || ' PB'
when bytes < power(1024,7) then round(bytes / power(1024,6),4) || ' EB'
when bytes < power(1024,8) then round(bytes / power(1024,7),4) || ' ZB'
when bytes < power(1024,9) then round(bytes / power(1024,8),4) || ' YB'
else 'INVALID VALUE FOR BYTES'
end as bytes_sizes
from dba_data_files;

------------------------------ to get full detail of imported objects on target db ----------------------

select 

(select host_name from v$instance) "Host", 

(select instance_name from v$instance) "Instance",

owner,

segment_name,

(select 

to_char(cast(created as timestamp with local time zone), 'DD-MONTH-YYYY hh24:mi:ss TZD yyyy') "created" from dba_objects where object_name='&object_name' and owner='&schema_name') "created",

(select to_char( cast(sysdate as timestamp with local time zone), 'DD-MONTH-YYYY hh24:mi:ss TZD yyyy') from dual) "sysdate",

case 

when bytes between 0 and 1023 then bytes || 'bytes'

when bytes < power(1024,2) then round(bytes / power(1024,1),4) || 'KB'

when bytes < power(1024,3) then round(bytes / power(1024,2),4) || 'MB'

when bytes < power(1024,4) then round(bytes / power(1024,3),4) || 'GB'

when bytes < power(1024,5) then round(bytes / power(1024,4),4) || 'TB'

when bytes < power(1024,6) then round(bytes / power(1024,5),4) || 'PB'

when bytes < power(1024,7) then round(bytes / power(1024,6),4) || 'EB'

when bytes < power(1024,8) then round(bytes / power(1024,7),4) || 'ZB'

when bytes < power(1024,9) then round(bytes / power(1024,8),4) || 'YB'

else 'INVALID VALUE FOR BYTES'

end as bytes_sizes

from dba_segments

where segment_name='&table_name' and owner='schema_name';

select tablespace_name,warning_value,critical_value from dba_tablespace_thresholds where tablespace_name=upper('&tablespace_name');

important views in oracle 

dba_tablespace_thresholds

dba_thresholds

dba_triggers

dba_ts_quotas

all_apply_quotas

all_apply_error

all_certificates

all_goldengate_privileges

all_goldengate_rule

all_goldengate_inbound

all_lobs

all_method_params

all_external_locations

all_credentials