SET SERVEROUTPUT ON
DECLARE
v_owner VARCHAR2(100);
BEGIN
FOR cur IN (SELECT username FROM all_users WHERE username NOT IN ('SYS', 'SYSTEM'))
LOOP
v_owner := cur.username;
-- Output schema name
DBMS_OUTPUT.PUT_LINE('Schema: ' || v_owner);
-- Retrieve table information
FOR tbl IN (SELECT table_name FROM all_tables WHERE owner = v_owner)
LOOP
-- Output table name
DBMS_OUTPUT.PUT_LINE('Table: ' || tbl.table_name);
-- Output column headers
DBMS_OUTPUT.PUT_LINE('Column Name' || CHR(9) || 'Data Type');
-- Retrieve column information
FOR col IN (SELECT column_name, data_type FROM all_tab_columns WHERE owner = v_owner AND table_name = tbl.table_name)
LOOP
-- Output column information
DBMS_OUTPUT.PUT_LINE(col.column_name || CHR(9) || col.data_type);
END LOOP;
-- Output object privileges
DBMS_OUTPUT.PUT_LINE('Object Privileges:');
FOR priv IN (SELECT privilege FROM all_tab_privs WHERE owner = v_owner AND table_name = tbl.table_name)
LOOP
-- Output privilege information
DBMS_OUTPUT.PUT_LINE('- ' || priv.privilege);
END LOOP;
-- Output column privileges
DBMS_OUTPUT.PUT_LINE('Column Privileges:');
FOR col_priv IN (SELECT grantee, privilege, column_name FROM all_col_privs WHERE owner = v_owner AND table_name = tbl.table_name)
LOOP
-- Output column privilege information
DBMS_OUTPUT.PUT_LINE('- Grantee=' || col_priv.grantee || ', Privilege=' || col_priv.privilege || ', Column=' || col_priv.column_name);
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
END LOOP;
END;
/