Creating User in Oracle with required Grants.

Creating User in Oracle  with required Grants.

Example:

create user lookup_fis
  identified by lookup_fis
  default tablespace users
  temporary tablespace TEMP
  profile DEFAULT;

-- Grant/Revoke role privileges
grant connect to lookup_fis;
grant dba to lookup_fis;
grant resource to lookup_fis;
-- Grant/Revoke system privileges
grant alter session to lookup_fis;
grant comment any table to lookup_fis;
grant create any table to lookup_fis;
grant create materialized view to lookup_fis;
grant create procedure to lookup_fis;
grant create sequence to lookup_fis;
grant create session to lookup_fis;
grant create synonym to lookup_fis;
grant create trigger to lookup_fis;
grant create type to lookup_fis;
grant create view to lookup_fis;
grant drop any table to lookup_fis;
grant unlimited tablespace to lookup_fis;
-- Set the user's default roles
alter user lookup_fis
  default role connect;

Avoid count(1) In Inner Query and add sum and group by in outer query

If the requirement to  find the count from different tables avoid count and group by in inner query  in which cost will be more we can avoid that by adding below changes

 SELECT try.employee_id,
            Officer_name,
            (SELECT (FIRST_NAME_EN || MIDDLE_NAME_EN || LAST_NAME_EN)
               FROM EMPLOYEE
              WHERE employee_id = try.employee_id)
               Name_en,
             SUM (no_of_entry) no_of_entry,
            SUM (no_of_exits) no_of_exits,
          trg_date AS Transaction_date
       FROM ( (SELECT au.employee_id,
                      AU.USER_GROUP_ID,
                      au.user_name Officer_name,
                      1 no_of_entry,
                      NULL no_of_exits,
                      NULL cs_application_count,
                      NULL no_entrt_permits,
                      NULL no_approved_ras,
                      NULL no_outpass_application,
                      NULL no_approved_nsd,
                      NULL no_approved_bl,
                      TRUNC (travel_date) trg_date
                 FROM travel_detail_log td, application_user au
                WHERE     td.created_by = AU.CREATED_BY
                      AND TRAVEL_TYPE_ID = 1
                      AND td.revtype <> 2
                      AND td.is_settled = 0)
             UNION ALL
             (SELECT au.employee_id,
                     AU.USER_GROUP_ID,
                     au.user_name Officer_name,
                     NULL no_of_entry,
                     1 no_of_exits,
                     NULL cs_application_count,
                     NULL no_entrt_permits,
                     NULL no_approved_ras,
                     NULL no_outpass_application,
                     NULL no_approved_nsd,
                     NULL no_approved_bl,
                     TRUNC (travel_date) trg_date
                FROM travel_detail_log td, application_user au
               WHERE     td.created_by = AU.CREATED_BY
                     AND TRAVEL_TYPE_ID = 2
                     AND td.revtype <> 2
                     AND td.is_settled = 0)
          ) try
     GROUP BY try.employee_id,
            Officer_name,
            trg_date,
            user_group_id      

Solution:
By adding group by in and sum  outer query we can bring down the cost of the Query