Sample Oracle queries run on 11g (and 10g comparisons)
Query to display stored procedures:
select OBJECT_NAME from user_procedures
select OBJECT_NAME from user_objects where object_type = 'PROCEDURE'
both returns list of stored procedures
"select OBJECT_NAME from user_procedures" returns the stored procedures and triggers as well for Oracle 11g while
"select OBJECT_NAME from user_objects where object_type = 'PROCEDURE'" returns list of stored procedures only
select * from user_sequences
select * from user_objects where object_type = 'SEQUENCE'
select * from user_tables
select * from user_objects where object_type = 'TABLE'
select * from user_triggers
select * from user_objects where object_type = 'TRIGGER'
select * from user_indexes in 10g returns the LOB as well
select * from user_objects where object_type = 'INDEX'
Display all the roles, previleges, tables
select * from user_tab_privs order by grantee, table_name
Display all the synonyms for the table owner
SELECT * FROM all_synonyms where table_owner = 'XYZ' order by owner, synonym_name;
Select from a table and concat two or more columns
SELECT column1_name || ' # ' || column2_name as column_both FROM table_name
Grant Privileges on tables/sequences to a role
grant select/insert/update/delete on [Schema].[Table/Sequence] to [Role_Name];
