How to check table space in database

 Query : To check tablespace in database 

select t.tablespace,  t.totalspace as " Totalspace(MB)",
     round((t.totalspace-nvl(fs.freespace,0)),2) as "Used Space(MB)",
     nvl(fs.freespace,0) as "Freespace(MB)",
     round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as "%Used",
     round((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free",
     us.USERNAME
     from
     (select round(sum(d.bytes)/(1024*1024)) as totalspace,d.tablespace_name tablespace
     from dba_data_files d
     group by d.tablespace_name) t,
     (select round(sum(f.bytes)/(1024*1024)) as freespace,f.tablespace_name tablespace
     from dba_free_space f
     group by f.tablespace_name) fs,
     (  SELECT USERNAME ,default_tablespace FROM  dba_users)  us     where t.tablespace=fs.tablespace (+)   AND t.tablespace(+) = us.default_tablespace
     order by "% Free";

get/extract char , date , numeric values from varchar column

requirement:

amount column having numeric data , char data and date also

So char /date data into one column and numeric (+ve, -ve ) values in another column

use below query to acheive that:

select

case when  trim(TRANSLATE(replace(substr(amount_in_char,0,1),'-','')||substr(amount_in_char,2), '0123456789,.', ' ')) is not null 

then amount_in_char end getchardtae,

case  when trim(TRANSLATE(replace(substr(amount_in_char,0,1),'-','')||substr(amount_in_char,2), '0123456789,.', ' ')) is  null 

then amount_in_char end getnum,

amount_in_char original from w_plan_fs;