Some handy Oracle query’s:
Data dictionary:
1 | SELECT * FROM dict; |
User details:
1 | SELECT * FROM user_users; |
User roles:
1 | SELECT * FROM user_role_privs; |
User privileges:
1 | SELECT * FROM user_sys_privs; |
My triggers:
1 2 3 | SELECT SUBSTR(object_name,1,40), object_type, status FROM user_objects WHERE object_type IN (‘TRIGGER’); |
My procedures and functions:
1 2 3 | SELECT SUBSTR(object_name,1,40), object_type, status FROM user_objects WHERE object_type IN (‘PROCEDURE’,‘FUNCTION’); |
My tables:
1 2 3 | SELECT SUBSTR(object_name,1,40), object_type, status FROM user_objects WHERE object_type IN (‘TABLE’); |
Load java source into oracle (not a query!)
1 | loadjava.bat –user USERNAME/PASSWORD@HOST:PORT:SID –thin –resolve –verbose <PATH-TO-FILE.java> |
All JAVA objects:
1 2 3 4 5 6 7 | SELECT SUBSTR(object_name,1,40), object_type, status FROM user_objects WHERE object_name NOT LIKE ‘SYS%‘ AND object_name NOT LIKE ‘CREATE%‘ AND object_name NOT LIKE ‘JAVA%‘ AND object_name NOT LIKE ‘LOADLOB%‘ AND object_type LIKE ‘JAVA %’; |
Declare external JAVA routine:
1 2 3 4 | CREATE OR REPLACE FUNCTION <NAME> RETURN VARCHAR2 AUTHID CURRENT_USER AS LANGUAGE JAVA NAME ‘<CLASS>.<FUNCTION>(<PARAMETER>) return java.lang.String’; |
All JAVA Exception’s:
1 | select SUBSTR(name,1,40), METHOD_INDEX, EXCEPTION_INDEX, SUBSTR(EXCEPTION_CLASS,1,40) from USER_JAVA_THROWS; |