How to get the Tablespace details in Oracle

This article helps you to get more information about Tablespace in Oracle along with a few commands which helps in your day to day activity.

Table of Contents

1 Related Posts

How to get the list of available Tablespaces

SQL> select tablespace_name, sum(bytes)/power(1024,2), sum(maxbytes)/power(1024,2), AUTOEXTENSIBLE from dba_data_files group by tablespace_name, AUTOEXTENSIBLE;

This list down all the tablespaces that are available in the database

How to get the default tablespace

SQL> select username, default_tablespace from dba_users where default_tablespace not like ‘SYS%’;

How to get the default tablespace

SQL> select username, default_tablespace from dba_users where default_tablespace not like ‘SYS%’;

How to get the tablespace usage or limit

SQL> select host_name, tablespace_name, count(1) from v$instance, dba_tables where tablespace_name in (‘TS1′,’TS2’) group by host_name, tablespace_name;

SQL> select host_name, tablespace_name, count(1) from v$instance, dba_segments where tablespace_name in (‘TS1′,’TS2’) group by host_name, tablespace_name;

SQL> select count(1) from dba_segments where tablespace_name = ‘TS1’;

How to drop a tablespace

SQL> drop tablespace <Tablespace_name> including contents and datafiles;

How to increase the Size of a Tablespace

SQL> alter database datafile ‘location_of_the_.dbf’ RESIZE 4096M;

Know more about Tablespace here

Database related issues, please go through this article.

Please leave us a comment for any assistance.

Ramesh Kunamaneni (RK)

Greetings!! This is Ramesh Kunamaneni (RK), I have a decade experience in IT and worked for various MNCs. I am happy to share my knowledge in this blog especially on Java, Groovy, Python, DevOps, Agile Methodology, Banking and Payments Domain. Please do watch this space for more interesting articles and updates!!