Taking export of ROLES and GRANTS

 When you are taking export of schema, roles are not exported. This is useful method to export the roles and grants. 

Take export of roles and grants

vi role.par

full=y

directory=DATA_PUMP_DIR

INCLUDE=ROLE_GRANT,grant

dumpfile=role.dmp

nologfile=yes

content=metadata_only

INCLUDE=ROLE:"IN (select role from dba_roles where ORACLE_MAINTAINED='N')"

Run the expdp 

expdp \'/ as sysdba\'  parfile=role.par

Generate the ddl using dump file in sql format 

impdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=role.dmp sqlfile=role.sql nologfile=yes EXCLUDE=PROC_SYSTEM_GRANT

Run the SQL file on the target database for creating the role and grants. 

How to Gather Statistics on Objects Owned by the 'SYS' User and 'Fixed' Objects

Gather_schema_stats gathers statistics for objects owned by the SYS Schema. We recommend gathering statistics for the SYS schema, specifically if you are using Oracle APPS.

If your database encounters a lot of changes (DMLs) for SYS schema objects, then it is recommended to collect SYS schema statistics. The collection of statistics on SYS Schema objects will optimize the performance of internal recursive queries and application queries on SYS schema objects.

To gather dictionary stats, execute one of the following:

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');
SQL> exec DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE);
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;




Gather_fixed_objects_stats also gathers statistics for dynamic tables, e.g. the X$ tables which loaded in SGA during the startup. Gathering statistics for fixed objects would normally be recommended if poor performance is encountered while querying dynamic views ,e.g. V$ views. Since fixed objects record current database activity, statistics gathering should be done when database has a representative load so that the statistics reflect the normal database activity.

To gather the fixed objects stats, use the following:

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;


Gather fixed objects stats if the load is heavy and if the system is busy.

Reference : Oracle Doc ID 457926.1