If you want to get a list of all tablespaces used in the current database instance, you can use the DBA_TABLESPACES view as shown in the following SQL script example:

SQL> connect SYSTEM/globalguideline

Connected.

SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS

2 FROM USER_TABLESPACES;

TABLESPACE_NAME   STATUS CONTENTS

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

SYSTEM     ONLINE PERMANENT

UNDO     ONLINE UNDO

SYSAUX     ONLINE PERMANENT

TEMP     ONLINE TEMPORARY

USERS     ONLINE PERMANENT