Some of the queries in the table below can only be run by an admin. These are marked with (PRIV) at the description.
Version
SELECT banner FROM v$version WHERE banner LIKE 'Oracle%';
SELECT banner FROM v$version WHERE banner LIKE 'TNS%';
SELECT version FROM v$instance;
Comments
SELECT 1; -- comment
Current User
SELECT user FROM dual;
List Users
SELECT username FROM all_users ORDER BY username;
SELECT name FROM sys.user$;
List Password Hashes (PRIV)
#Oracle version <= 10g
SELECT name, password, astatus FROM sys.user$. astatus tells you if acct is locked
#Oracle version 11g
SELECT name,spare4 FROM sys.user$
List Privileges (PRIV)
SELECT FROM session_privs;
SELECT GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS;
#List a user's privs
SELECT FROM dba_sys_privs WHERE grantee = 'DBSNMP';
#Find users with a particular priv
SELECT grantee FROM dba_sys_privs WHERE privilege = 'SELECT ANY DICTIONARY';
List DBA Accounts (PRIV)
SELECT DISTINCT grantee FROM dba_sys_privs WHERE ADMIN_OPTION = 'YES';
Current Database
SELECT global_name FROM global_name;
SELECT name FROM v$database;
SELECT instance_name FROM v$instance;
SELECT SYS.DATABASE_NAME FROM DUAL;
List Databases
#List schemas (one per user)
SELECT DISTINCT owner FROM all_tables;
List Tables
SELECT table_name FROM all_tables;
SELECT owner, table_name FROM all_tables;
List Columns
SELECT column_name FROM all_tab_columns WHERE table_name = 'blah';
SELECT column_name FROM all_tab_columns WHERE table_name = 'blah' and owner = 'foo';
Find Tables from Column Name
#NB: table names are upper case
SELECT owner, table_name FROM all_tab_columns WHERE column_name LIKE '%PASS%';
Hostname, IP Address
SELECT UTL_INADDR.get_host_name FROM dual;
SELECT host_name FROM v$instance;
#Gets IP address
SELECT UTL_INADDR.get_host_address FROM dual;
#Gets hostnames
SELECT UTL_INADDR.get_host_name(’10.0.0.1′) FROM dual;
Location of DB Files
SELECT name FROM V$DATAFILE;
Get all tablenames in One String
#When using union based SQLi with only one row
SELECT rtrim(xmlagg(xmlelement(e, table_name || ',')).extract('//text()').extract('//text()') ,',') from all_tables
