How to get the Tablespace details in Oracle

Database
Comments

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.

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;

or

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;

 or

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.

Related Posts

Leave a Reply