Queries to explore a database you don't know much about - Oracle SQL quick ref 4

This is the last of the posts I will do for now on Oracle quick refereces.
I decided to write this 4 blog articles to save me time using Google in the future.  So this last one is a bit peculiar, there are just a bunch of SQL commands that can be very useful for exploring a new database you don't know much about.

-- Finds all the columns in those tables that contain the string ESS
-- in the table name and orders them alphabetically
SELECT table_name, column_name
FROM ALL_TAB_COLS
WHERE table_name LIKE '%ESS%'
AND owner != 'MY_SCHEMA'
ORDER BY table_name;

-- Same as the previous one but includes more data and excludes the owner SYS
SELECT *
FROM ALL_TAB_COLS
WHERE table_name LIKE '%ESS%'
AND owner != 'SYS';

-- All the constraints for the tables in all schemas in the database
SELECT * 
FROM all_constraints;

-- All the constraints for the tables in all an specific schema
SELECT * 
FROM all_constraints
WHERE UPPER(r_owner) LIKE 'MY_SCHEMA';

-- All the foreign key constraints for the tables in all an specific schema
SELECT * 
FROM all_constraints
WHERE UPPER(r_owner) LIKE 'MY_SCHEMA'
AND UPPER(constraint_type) LIKE 'R';

-- This finds the primary key of a table
SELECT UPPER(constraint_name) 
FROM all_constraints
WHERE constraint_type IN ('P', 'U')
AND UPPER(table_name) LIKE UPPER(:r_table_name)
AND UPPER(owner) LIKE 'MY_SCHEMA';
   
-- Using the previous command as input to the following, allows us to find all of the
-- children of a particular table(Have as FK the PK of their parent)
SELECT table_name, constraint_name, status, owner
FROM all_constraints
WHERE UPPER(r_owner) LIKE 'MY_SCHEMA'
AND UPPER(constraint_type) LIKE 'R'
AND UPPER(r_constraint_name) IN
 (
   SELECT UPPER(constraint_name) 
   FROM all_constraints
   WHERE constraint_type IN ('P', 'U')
   AND UPPER(table_name) LIKE UPPER(:r_table_name)
   AND UPPER(owner) LIKE 'MY_SCHEMA'
 )
ORDER BY table_name, constraint_name;

--Find the parents of a table
SELECT * 
  FROM all_constraints
   WHERE constraint_type IN ('P', 'U')
   AND UPPER(owner) LIKE 'MY_SCHEMA'
   AND UPPER(constraint_name) in
   (
    SELECT UPPER(r_constraint_name)
    FROM all_constraints
    WHERE UPPER(table_name) LIKE UPPER(:r_table_name)
    AND UPPER(constraint_type) LIKE 'R'
   );
   
--Finding tables that contain a word in their name   
SELECT table_name, owner
FROM all_tables
WHERE UPPER(table_name) LIKE '%something%';