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

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 Cer...
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

How to Drop or Remove or Decommission a Database in Oracle

-
How to Drop or Remove or Decommission a Database in Oracle Database Decommission steps for Oracle 10G and 11g databases Note: ~~~~~~~~ Every organization has its own documented way of removing or decommissioning the active/running database that is supporting an Enterprise’s Application. Here I am presenting some general steps which may coincide with your organization’s standard operation procedure (SOP). 1)   Please get the required approval from Business/Customer to proceed for database decommissioning. 2)   Send the Notification to Business/Customer that we are going to decommission the database. (if required) 3)   Raise a Request for Storage and Backup team to take the required backup (provide all the details in the raised request) and to keep the Backup for a specified period of time, say for next 2 years, (That is called the backup retention period). Retention Period may be varied based on your business needs and SLA. The backup can...
Read more