How to compile synonyms.

April 5, 2014 § Leave a comment


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

spool select_all.sql

select ‘select count(1) ‘ || OWNER||’.’||OBJECT_NAME || ‘;’
from
dba_objects where
STATUS = ‘INVALID’ and
OBJECT_TYPE in ( ‘SYNONYM’ )
order by
OBJECT_TYPE, OBJECT_NAME;
spool off

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’;

How to generate 10053 trace for any sql.

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 !>
EXIT;

the output will be in user_dump_dest

 

Note1:-

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.

 

 

 

 

User has no SELECT privilege on V$SESSION

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’));

PLAN_TABLE_OUTPUT
——————————————————————————–
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>;

 

Re-creating the AWR repository.

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).

startup restrict

2) run below commands one by one.
@?/rdbms/admin/catnoawr.sql
alter system flush shared_pool;
@?/rdbms/admin/catawrtb.sql
@?/rdbms/admin/execsvrm.sql <– only for 11g.
@?/rdbms/admin/utlrp.sql

3) shutdown database and restart.

shutdown immediate;

startup
My observation:-

a) Restricted mode is not required.

b) Bouncing is not required.

 

 

Few points on Oracle statistics.

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>’);

HOW TO CHECK HISTOGRAMS PRESENT FOR A TABLE OR NOT

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 ‘

‘;

Even you can owner also.

I thought of putting my entire RD, so that i can refer in the future.

ground preparation.

1) create a table test(srno number, name varchar2(20));

2) insert into test values(&srno, ‘&name’));

3) collect the stats with col size 1.

4) check the stats.

5) collect the stats with col size auto.

6) check the stats.

7) do some select using srno.

8) collec the stats with col size auto.

9) check the stats.

10) collect the status with col size with some value > 1

11) check the stats.

Observations:-

1) No entries will be there in DBA_TAB_COL_STATISTICS table if we do not run stats collection irrespective of col size.
2) Even if i specify auto in collection, it will not collect the histograms until there is a real need.
3) Even if i specify more num of buckets, it will take only as needed.

Please fidn the entire log here with. i have done the testing in 11g database.

SQL> CREATE TABLE TEST(SRNO NUMBER, NAME VARCHAR2(20));

Table created.

SQL> insert into test values(&srno, ‘&name’)
2 ;
Enter value for srno: 1
Enter value for name: 111
old 1: insert into test values(&srno, ‘&name’)
new 1: insert into test values(1, ’111′)

1 row created.

SQL> /
Enter value for srno: 2
Enter value for name: 22
old 1: insert into test values(&srno, ‘&name’)
new 1: insert into test values(2, ’22′)

1 row created.

SQL> /
Enter value for srno: 3
Enter value for name: 33
old 1: insert into test values(&srno, ‘&name’)
new 1: insert into test values(3, ’33′)

1 row created.

SQL> /
Enter value for srno: 4
Enter value for name: 44
old 1: insert into test values(&srno, ‘&name’)
new 1: insert into test values(4, ’44′)

1 row created.

SQL> commit;

Commit complete.

SQL> 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 ‘TEST’;SQL> SQL> SQL> SQL>

no rows selected

SQL> exec dbms_stats.gather_table_stats(ownname=>’system’,tabname=>’test’, method_opt=>’for all columns size 1′,cascade=>true);

PL/SQL procedure successfully completed.

SQL> 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 ‘TEST’;SQL> SQL> SQL> SQL>

OWNER TABLE_NAME HISTOGRAM COLUMN_NAME NUM_BUCKETS
————— ——————– ————— —————————— ———–
SYSTEM TEST NONE NAME 1
SYSTEM TEST NONE SRNO 1

SQL> exec dbms_stats.gather_table_stats(ownname=>’system’,tabname=>’test’, method_opt=>’for all columns size auto’,cascade=>true);

PL/SQL procedure successfully completed.

SQL> 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 ‘TEST’;SQL> SQL> SQL> SQL>

OWNER TABLE_NAME HISTOGRAM COLUMN_NAME NUM_BUCKETS
————— ——————– ————— —————————— ———–
SYSTEM TEST NONE NAME 1
SYSTEM TEST NONE SRNO 1

SQL> select * from test where srno=&1
2 ;
Enter value for 1: 2
old 1: select * from test where srno=&1
new 1: select * from test where srno=2

SRNO NAME
———- ——————–
2 22

SQL> /
Enter value for 1: 3
old 1: select * from test where srno=&1
new 1: select * from test where srno=3

SRNO NAME
———- ——————–
3 33

SQL> exec dbms_stats.gather_table_stats(ownname=>’system’,tabname=>’test’, method_opt=>’for all columns size 1′,cascade=>true);

PL/SQL procedure successfully completed.

SQL> 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 ‘TEST’;SQL> SQL> SQL> SQL>

OWNER TABLE_NAME HISTOGRAM COLUMN_NAME NUM_BUCKETS
————— ——————– ————— —————————— ———–
SYSTEM TEST NONE NAME 1
SYSTEM TEST NONE SRNO 1

SQL> exec dbms_stats.gather_table_stats(ownname=>’system’,tabname=>’test’, method_opt=>’for columns size 2 srno’,cascade=>true);

PL/SQL procedure successfully completed.

SQL> 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 ‘TEST’;SQL> SQL> SQL> SQL>

OWNER TABLE_NAME HISTOGRAM COLUMN_NAME NUM_BUCKETS
————— ——————– ————— —————————— ———–
SYSTEM TEST NONE NAME 1
SYSTEM TEST HEIGHT BALANCED SRNO 2

SQL> exec dbms_stats.gather_table_stats(ownname=>’system’,tabname=>’test’, method_opt=>’for columns size 5 srno for columns size 4 name ‘,cascade=>true);

PL/SQL procedure successfully completed.

SQL> 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 ‘TEST’;SQL> SQL> SQL> SQL>

OWNER TABLE_NAME HISTOGRAM COLUMN_NAME NUM_BUCKETS
————— ——————– ————— —————————— ———–
SYSTEM TEST HEIGHT BALANCED NAME 4
SYSTEM TEST HEIGHT BALANCED SRNO 4

SQL>

SQL script find the long operations and their details.

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

SELECT s.sid,
s.serial#,
s.machine,
SL.OPNAME,
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,
v$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial#;

Follow

Get every new post delivered to your Inbox.