Oracle Common Statement Collection

To query all table spaces in the library:

Select Tablespace_name from Dba_tablespaces;
Select Tablespace_name from User_tablespaces;

Query the table space used:

Select distinct tablespace_name from Dba_all_tables;
Select distinct tablespace_name from User_all_tables;

Query the table space for all table names:

Select table_name from dba_all_tables where tablespace_name = Tablespacename;

Query system users:

SELECT * FROM All_users
SELECT * FROM Dba_users

To query the currently connected User:

SELECT * FROM V$session

To query the current user permissions:

SELECT * FROM Session_privs

To view all functions and stored procedures:

SELECT * FROM User_source

To View table space usage:
Select a.file_id "Fileno",
a.tablespace_name "Table space",
a.bytes "bytes",
a.bytes-sum (NVL (b.bytes, 0)) "used",
sum (NVL (b.bytes, 0)) "Idle",
sum (NVL (b.bytes, 0))/a.bytes * 100 "idle percentage"
From dba_data_files A, dba_free_space b
where a.file_id = b.file_id (+)
GROUP by A.tablespace_name, a.file_id, A.bytes
order by A.tablespace_name;

Oracle Common Statement Collection