16

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

user2115594
  • 183
  • 1
  • 1
  • 8
  • You talk about tablespaces in your title but partitions and subpartitions in the text. Which one is it? And what have you tried? There's a lot of stuff available with a simple search, why didn't the basic stuff meet your needs? – Mat Feb 27 '13 at 14:23
  • @user2115594:dude you need to measure the tablespace size or each partition size in a particular table? – Thiyagu ATR Feb 27 '13 at 14:54
  • Yeah i´m sorry what i really need is to measure the subpartition size in my origin table. Each origin tables where created with partitions and subpartitions but they all insert data into 3 tablespaces. Next step is to create one tablespace for each subpartition – user2115594 Feb 27 '13 at 15:56

4 Answers4

47

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 <> 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 = <owner_name>;
stefanglase
  • 10,296
  • 4
  • 32
  • 42
Thiyagu ATR
  • 2,224
  • 7
  • 30
  • 44
18

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 & 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);
PhatHV
  • 8,010
  • 6
  • 31
  • 40
Gurupreet Singh Bhatia
  • 708
  • 6
  • 17
0

One way to check the tablespace size in oracle is to run this query

SELECT   TABLESPACE_NAME, ROUND (SUM (BYTES) / 1048576) "Total Size MB"
    FROM     DBA_DATA_FILES
    GROUP BY TABLESPACE_NAME
Dharman
  • 30,962
  • 25
  • 85
  • 135
Hassan Abdelrahman
  • 27
  • 2
0

SELECT b.tablespace_name, tbs_size SizeGb, a.free_space FreeGb FROM ( SELECT tablespace_name, ROUND (SUM (bytes) / 1024 / 1024 / 1024, 2) AS free_space FROM dba_free_space GROUP BY tablespace_name) a, ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 / 1024 AS tbs_size FROM dba_data_files GROUP BY tablespace_name) b WHERE a.tablespace_name(+) = b.tablespace_name ORDER BY 1

Vikas Katiyar
  • 41
  • 3