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.
