ORA-02030: can only select from fixed tables/views

Description :
***********
A quick solution to solve ORA-02030 error.

Problem :
********
When i try to grant select right to a user on v$views, you get an error - "ORA-02030: can only select from fixed tables/views"

SQL> grant select on v$LOCK to USER1;
grant select on v$LOCK to USER1
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views



Reason :

********

You can not grant select right to V$ views directy, looks we need to find the base table name here and apparently the object also name should be in capital letters, so, you Should grants the select rights on base tables and not on v$views.

Solution:

********

SELECT 'grant select on ' || table_name || ' to <username>;'

  FROM dba_synonyms

 WHERE LOWER (synonym_name) IN (SELECT LOWER (object_name)

                                  FROM dba_objects

                                 WHERE LOWER (object_name) IN ('v$_lock',

                                                               'v$sqlarea',

                                                               'v$process'));

Sample Output :

**************

SQL> grant select on  V_$SQLAREA to USER1;

Grant succeeded.

Automatic Oracle Statistics

Some Information and Queries on "

Automatically Collecting Statistics on Tables

"

--will show you what kind of statistics level and whether AUTO is enabled  
   
    select * from V$STATISTICS_LEVEL;
    select * from v$parameter where name like '%statistics%';
   
--Query to Find which level of statistics Set in DB:
    select * from v$parameter where name like '%statistics%';

--Two Scheduler windows are predefined upon installation of Oracle Database:
--WEEKNIGHT_WINDOW starts at 10 p.m. and ends at 6 a.m. every Monday through Friday.
--WEEKEND_WINDOW covers whole days Saturday and Sunday.

--Query to find When statistics were last gathered? :
    SELECT owner, table_name, last_analyzed FROM all_tables ORDER BY last_analyzed DESC NULLS LAST; --Tables.
    SELECT owner, index_name, last_analyzed FROM all_indexes ORDER BY last_analyzed DESC NULLS LAST; -- Indexes.

--Query to find Status of automated statistics gathering?:
    SELECT * FROM dba_autotask_client WHERE client_name = 'auto optimizer stats collection';

--Query to find whether the statistics have been collected or not  for particular table, and also gives information like when was the last Analyze done:
    select * from DBA_TAB_STATISTICS where table_name = '<TABLE NAME>'


--Query to complete History of table analyze done:
    select * from DBA_TAB_STATS_HISTORY where table_name = '<TABLE NAME>' order by stats_update_time desc

--Query to find Windows Groups? :
    SELECT window_group_name, window_name FROM dba_scheduler_wingroup_members;

--Query to Window Schedules? :
    SELECT window_name, start_time, duration FROM dba_autotask_schedule;

--How to Manually gather Database Statistics in this Schema:
    EXEC dbms_stats.gather_schema_stats(ownname=>NULL, cascade=>TRUE); -- cascade=>TRUE means include Table Indexes too.

--How to Manually gather Database Statistics in all Schemas!
-- Probably need to CONNECT / AS SYSDBA
    EXEC dbms_stats.gather_database_stats;

--Some Additional Information given by Mark D Powell  and  John Thorton
--**********************************************************************
--Query to find which JOB responsible for Collecting statistics.

    select job_name from dba_scheduler_jobs order by 1;

-- Oracle updates the CBO statistics every night during the DBMS_SCHEDULER job maintenance window and every weekend based on the
-- staleness setting for the optimizer or object if you have set table level DBO parameters.  You should not need to make any changes.
--- -
--You can collect the statistics via dbms_stats for any table when some event has made the statistics incorrect or
--sub-optimal and you can use SET_TABLE_PREFS to adjust the CBO statistics collection parameters for a specific table where the defaults are not working as well as you would like.

Grants needed for AWR and ADDM reports on Oracle 11G

Following grants are necessary for a normal user to produce AWR and ADDM  reports.

For AWR
***********
GRANT SELECT ON SYS.V_$DATABASE TO  <username>;
GRANT SELECT ON SYS.V_$INSTANCE TO <username>;
GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO <username>;
GRANT SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE TO <username>;
GRANT SELECT ON SYS.DBA_HIST_SNAPSHOT TO <username>;

For ADDM
************
GRANT SELECT ANY DICTIONARY TO <username>;
GRANT ADVISOR TO <USERNAME>;