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 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.
- 2 How to get the list of available Tablespaces
- 3 How to get the default tablespace
- 4 How to get the default tablespace
- 5 How to get the tablespace usage or limit
- 6 How to drop a tablespace
- 7 How to increase the Size of a Tablespace
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.
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!!
