How to find tablespace usage in oracle

-

How to find tablespace usage in oracle

Oracle stores data logically in tablespaces and physically in datafiles associated with the corresponding tablespace. A database is divided into one or more logical storage units called tablespaces. Tablespaces are divided into logical units of storage called segments, which are further divided into extents. Extents are a collection of contiguous blocks.

Tablespaces are the bridge between certain physical and logical components of the Oracle database. Tablespaces are where you store Oracle database objects such as tables, indexes and rollback segments.  You can think of a tablespace like a shared disk drive in Windows. You can store files on your shared drive, move files around and remove files. The same is true with tablespaces. 

A tablespace is made up of one or more database datafiles. The total amount of storage space available in a tablespace is the sum of the physical disk size of all the datafiles associated with that tablespace (less some system overhead). The datafiles are created automatically when the tablespace is defined. In most cases, all datafile space is pre-allocated; that is, the space is set aside when the datafile is created. Thus, when you create a tablespace, you define the initial size of the associated datafile.

So how to find the tablespace usage in the oracle database, here is the SQL script that you can run into Sqlplus and get the answer.


Usage in Megabytes
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

set pages 999
col tablespace_name format a40
col "size MB" format 999,999.99
col "free MB" format 999,999.99
col "% Used" format 999.99
select      tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
,     decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
,     decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% Used"
from  (select tablespace_name, sum(bytes)/1024/1024 used_mb
      from dba_data_files group by tablespace_name union all
      select      tablespace_name || '  **TEMP**'
      ,     sum(bytes)/1024/1024 used_mb
      from dba_temp_files group by tablespace_name) tsu
,     (select tablespace_name, sum(bytes)/1024/1024 free_mb
      from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4
/



Usage in Gigabytes
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

set pages 999
col tablespace_name format a40
col "size GB" format 999,999.99
col "free GB" format 999,999.99
col "% Used" format 999.99
select      tsu.tablespace_name, ceil(tsu.used_gb) "size GB"
,     decode(ceil(tsf.free_gb), NULL,0,ceil(tsf.free_gb)) "free GB"
,     decode(100 - ceil(tsf.free_gb/tsu.used_gb*100), NULL, 100,
               100 - ceil(tsf.free_gb/tsu.used_gb*100)) "% Used"
from  (select tablespace_name, sum(bytes)/1024/1024/1024 used_gb
      from dba_data_files group by tablespace_name union all
      select      tablespace_name || '  **TEMP**'
      ,     sum(bytes)/1024/1024/1024 used_gb
      from dba_temp_files group by tablespace_name) tsu
,     (select tablespace_name, sum(bytes)/1024/1024/1024 free_gb
      from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4
/



Note:

If you want to know usage for particular tablespace(s) like say system, sysaux and users then what you can do is use ‘and’ condition with the where clause like this:

where tsu.tablespace_name = tsf.tablespace_name (+)
and tsu.tablespace_name in (‘SYSTEM’,’SYSAUX’,’USERS’)
order by 4
/

If you wish you can use floor function instead of ceil function.

It is better if you use a text editor to format SQL Code before using it in oracle database.
 




Comments

Post a Comment

Back To Top

Popular posts from this blog

How to save video from Internet Explorer

-
How to save video from Internet Explorer Here I am going to share an experience with you, where I was required to get the video file that I was playing in Internet explorer out of Temporary directory and save it to some permanent disk folder. Note: It is far better if you use video download tools with the web browser. Internet Explorer Version: 7.0.6xxx [Open Internet Explorer – Help – About Internet Explorer] Operating System: Windows Vista I was not playing file from you tube [I do not see any tmp file while watching video from youtube.] I found the file in following directory: C:\Users\<UserName>\AppData\Local\Microsoft\Windows\Temporary Internet Files\Low [ Although the directory is not visible in my computer system, so I directly put it in address bar/location bar in windows explorer. ] File name was fla3870.tmp [May be in your system It will be by different name] You have to find the video file by trick technique for example: 1.
Read more

error 18 at 0 depth lookup: self signed certificate

-
I was trying to test SSL connection between MySQL client and server. For that I created SSL certificate and keys by following the MySQL documentation at: Creating SSL Certificates and Keys Using openssl After finishing up all the commands when I verify the certificates by using: openssl verify -CAfile ca.pem server-cert.pem client-cert.pem I got the following output: error 18 at 0 depth lookup : self signed certificate error server-cert.pem: verification failed client-cert.pem: OK  Reason : The Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. Otherwise, the certificate and key files will not work for servers compiled using OpenSSL. Solution : When OpenSSL prompts you for the Common Name for each certificate, use different names. Common Name (e.g. server FQDN or YOUR name) []: I used values as follows: CA Cert:  ssl-ca-cert Server Cert: ssl-ca-server-cert Client Cert:  ssl-c
Read more

How to check fragmentation in MySQL tables

-
MySQL tables, including MyISAM and InnoDB, two of the most common types, experience fragmentation as data is inserted, updated and deleted randomly. Fragmentation can leave large holes in your table, blocks which must be read when scanning the table. Optimizing your table can therefore make full table scans and range scans more efficient. We will mention how to check fragmentation in MySQL tables using SQL queries. USE < put_your_dbname_here > SELECT TABLE_NAME, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') TOTAL_SIZE, CONCAT(ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2), 'M') DATA_FREE FROM information_schema.TABLES where table_schema = database() and ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2) > 0.00 ORDER BY DATA_FREE DESC; SELECT TABLE_NAME, ROUND(((Data_length - (TABLE_ROWS * Avg_row_length))/Data_length) * 100, 2) FRAG_Percent FROM information_schema.TABLES where table_schema = database() and TABLE_ROWS > 0
Read more