how to determine size of tablespace oracle 11g

Question

I have a database with three tables  I need to move historic partitioned data to other schema Now that i  m planning to creatre the new  historic  tables  I don  t know how to measure the size of partition and subpartitions  Can u help me  please gimme some advices   Thanxs in advance  Lou

User · Answer

One of the way is Using below sql queries  --Size of All Table Space  --1  Used Space SELECT TABLESPACE NAME TO CHAR SUM NVL BYTES 0   1024 1024 1024   99 999 990 99   AS  USED SPACE IN GB   FROM USER SEGMENTS GROUP BY TABLESPACE NAME --2  Free Space SELECT TABLESPACE NAME TO CHAR SUM NVL BYTES 0   1024 1024 1024   99 999 990 99   AS  FREE SPACE IN GB   FROM   USER FREE SPACE GROUP BY TABLESPACE NAME  --3  Both Free  amp  Used SELECT USED TABLESPACE NAME  USED USED BYTES AS  USED SPACE IN GB     FREE FREE BYTES AS  FREE SPACE IN GB   FROM  SELECT TABLESPACE NAME TO CHAR SUM NVL BYTES 0   1024 1024 1024   99 999 990 99   AS USED BYTES FROM USER SEGMENTS GROUP BY TABLESPACE NAME  USED INNER JOIN  SELECT TABLESPACE NAME TO CHAR SUM NVL BYTES 0   1024 1024 1024   99 999 990 99   AS FREE BYTES FROM  USER FREE SPACE GROUP BY TABLESPACE NAME  FREE ON  USED TABLESPACE NAME   FREE TABLESPACE NAME

User · Answer

The following query can be used to detemine tablespace and other params   select df tablespace name  Tablespace          totalusedspace  Used MB           df totalspace - tu totalusedspace   Free MB          df totalspace  Total MB          round 100      df totalspace - tu totalusedspace   df totalspace    Pct  Free    from  select tablespace name                 round sum bytes    1048576  TotalSpace           from dba data files           group by tablespace name  df          select round sum bytes   1024 1024   totalusedspace                 tablespace name           from dba segments           group by tablespace name  tu  where df tablespace name   tu tablespace name     and df totalspace  lt  gt  0    Source  https   community oracle com message 1832920  For your case if you want to know the partition name and it s size just run this query   select owner         segment name         partition name         segment type         bytes   1024 1024  MB     from dba segments   where owner    lt owner name gt

[oracle11g] how to determine size of tablespace oracle 11g

Examples related to oracle11g

Examples related to tablespace