Re: knowing tablespace size.

From: Douglas Hawthorne <douglashawthorne_at_yahoo.com.au>
Date: Mon, 08 May 2006 10:56:03 GMT
Message-ID: <7tF7g.25093$vy1.9991@news-server.bigpond.net.au>

"Shahnaz" <ahamedshahnaz_at_gmail.com> wrote in message news:1147083284.621465.202710_at_y43g2000cwc.googlegroups.com...

> please let me know what is the script to check different tablespaces
> size in bytes?
>

One answer is:
SELECT

      tablespace_name,
      SUM( bytes ) as size_in_bytes
   FROM
      dba_data_files
   GROUP BY
      tablespace_name

/

To be able to do things like this for yourself, you will need to know (from the Oracle Database Concepts manual) that:
(1) Tablespaces consist of one or more data files;
(2) Space is allocated to tablespaces via allocation to data files;
(3) Dynamic Performance Tables expose a lot of information about the
database and instance - these tables are described in the Oracle Reference manual;
(4) The naming conventions for these tables would lead me to look for a name
like DBA_DATA_FILES in order to find out how much space each data file is using;
(5) The Database Reference manual lists TABLESPACE_NAME and BYTES as the
columns I would be interested in for such a query;
(6) Once I know the source table and the columns that I am interested in, I
can then construct the query. Point #1 leads me to use a GROUP BY clause for TABLESPACE_NAME and to use a SUM function on the BYTES column.

An alternative would to be to install OEM and click on a few buttons.

Douglas Hawthorne Received on Mon May 08 2006 - 05:56:03 CDT