Very useful Oracle DB queries

show all tablespaces used in this DB:

select unique TABLESPACE_NAME from DBA_TABLES;


show all indexes on a table:

SELECT i.index_name, ic.column_position, ic.column_name
FROM user_indexes i JOIN user_ind_columns ic
ON i.index_name = ic.index_name
WHERE i.TABLE_NAME = 'MYTABLE';



select * from V$DATABASE,

and

select * from GV$INSTANCE;

tell you a lot about SID, SERVICE_NAME and other properties of your instance.