April 5, 2014 Comments Off
Today we did successfully one upgrade and as a part of that we found many synonyms got invalidated. So following have been used.
1) To recompile NON-PUBLIC SYNONYMS use below script.
set pagesize 0
set linesize 79
set verify off
set echo off
select ‘select count(1) ‘ || OWNER||’.’||OBJECT_NAME || ‘;’
STATUS = ‘INVALID’ and
OBJECT_TYPE in ( ‘SYNONYM’ )
2) To re-compile PUBLIC synonyms we use below script.
select ‘alter public synonym ‘||object_name||’ compile;’ from all_objects where status=’INVALID’ and owner=’PUBLIC’ and object_type=’SYNONYM’;
March 30, 2014 Comments Off
As a part of our tunning activity we may need to generate trace for different SQLs. Following sqls may be handy during the same.
alter system set trace_enabled=true;
ALTER SESSION SET max_dump_file_size = unlimited;
ALTER SESSION SET tracefile_identifier=’10053′;
ALTER SESSION SET events ’10053 trace name context forever, level 1′;
EXPLAIN PLAN FOR
<Your Query Here. Remember to force hard parse !>
the output will be in user_dump_dest
1) Please note <level 1> can be changed as per your requirement.
2) Please note in 11g, trace will not be generated becuase of bug, trace_enabled will be false. It should be allways true.
March 29, 2014 Comments Off
Today when i was working on a support request, i was asked to get the explain plan after execution of the SQL using below SQL.
<Execute the Problem Sql Statement>
SET LINESIZE 150
SET PAGESIZE 2000
SELECT * FROM TABLE(dbms_xplan.display_cursor(FORMAT=>’ALLSTATS LAST’));
When i run above command, i am getting below error.
SQL> select * from table(dbms_xplan.display_cursor(FORMAT=>’ALLSTATS LAST’));
User has no SELECT privilege on V$SESSION
Solution is as follows.
grant the select privs on
v_$session, v_$sql_plan_statistics_all,v_$sql_plan, v_$sql to user. so the problem will be resolved.
to give select, run below command.
grant select on v_$sql to <username>;
grant select on v_$sql_plan to <username>;
grant select on v_$sql_plan_statistics_all to <username>;
grant select on v_$session to <username>;
March 24, 2014 Comments Off
Re-creating the AWR repository.
following are the steps to follow.
1) start the DB in restricted more(Not compulsary).
2) run below commands one by one.
alter system flush shared_pool;
@?/rdbms/admin/execsvrm.sql <– only for 11g.
3) shutdown database and restart.
a) Restricted mode is not required.
b) Bouncing is not required.
March 24, 2014 Comments Off
How to delete statistics of a table or schame.
Use below method to deelete
1) check the existing stats. if possible store them.
select * from dba_tab_col_Statistics where table_name like ‘<TABLE_NAME>’ and owner=”;
2) EXEC DBMS_STATS.DELETE_TABLE_STATS (OwnName => ‘OWNER’,TabName => ‘TABLE_NAME’);
3) select * from dba_tab_col_Statistics where table_name like ‘<TABLE_NAME>’ and owner=”;
this should not return any records.
How to lock statistics.
you can use below method to lock the stats.
EXEC dbms_stats.lock_table_stats(”, ‘<TABLE_NAME>’);
March 20, 2014 Comments Off
How to check weather histograms available for a particular table or not.
Today i was asked to check weather histograms are available or not, immediately i googled and found a way for the same. You can use below command to check weather histograms got created or not.
set lines 120
set pages 120
column owner format a15
column table_name format a20
SELECT OWNER, TABLE_NAME, HISTOGRAM,column_name, NUM_BUCKETS FROM DBA_TAB_COL_STATISTICS where table_name like ‘
March 18, 2014 Comments Off
Today when i am working on index creation/GATHER STATS activity i was curious to find the remaining time to complete the activity. As a part of the same i did google and found good script. i did slight modification and presenting the same.
COLUMN sid FORMAT 999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
ROUND(sl.elapsed_seconds/60) || ‘:’ || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ‘:’ || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM v$session s,
WHERE s.sid = sl.sid
AND s.serial# = sl.serial#;