How to list the Oracle Tablespaces


1. Connect to the database using SQL*plus

COMMAND:

SQL> select * from v$tablespace;

TS# NAME
---------- ------------------------------
0 SYSTEM
1 RBS
2 USERS
3 TEMP
4 TOOLS
5 INDX
6 SYS7334T
7 SYS7334I
8 SVM7334T
9 SVM7334I
10 OBJ7334T

How to query the available space for a Oracle Database

-To list the free space for all Oracle Tablespaces within a Database

SQL> select * from dba_free_space

-To list the free space for  a specific Oracle TablespaceDATABASE>'

SQL> select * from dba_free_space where TABLESPACE_NAME = 'IATOR';

Results shown the free space for a tablespace named 'IATOR'

TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
IATOR 43 12872 1032192 126
43

IATOR 43 10597 532480 65
43

IATOR 43 17483 1007616 123
43

-To list the space ALREADY USED for  a specific Oracle Tablespace

SQL> select * from DBA_SEGMENTS where TABLESPACE_NAME = 'mytablespace';

Oracle: How to list extents for segments?

SQL> select tablespace_name, count(*), max(blocks), sum(blocks) from dba_free_space group by tablespace_name;

TABLESPACE_NAME     COUNT(*)     MAX(BLOCKS)     SUM(BLOCKS)

--------------------------------     ----------         -----------                     -----------

CRPCTLI                                 1                     5779                         5779

CRPCTLT                                1                   12749                      12749

CRPDTAI                                150               76340                     107798