SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled

September 18, 2014 Comments off

SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled

# Problem while turning on autotrace with statistics
Error:-
SQL>set autotrace on statistics
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS reportCause:-
autotrace privs not exists.
Solution:-
cd $ORACLE_HOME/rdbms/admin
sqlplus system
@utlxplan
create public synonym plan_table for plan_table;
grant all on plan_table to public;
exit
cd $ORACLE_HOME/sqlplus/admin
sqlplus / as sysdba
@plustrce
grant plustrace to public
Categories: Oracle DBA

DBA INTERVIEW Qs.

June 26, 2014 Comments off

Few interview questions which may be asked in the interview for DBAs.

Patching

1) What is the user of PSU.

2) How you will plan for PSU.

3) What are the pre steps for patching.

4) What are the post_steps for patching.

5) I said, as a part of pre-req, we will take the backup of ORACLE_HOME then the question from her was
that why we need to take the backup of ORACLE_HOME.

RMAN

1) What is sync command in RMAN.

2) How to restore control file from RMAN command.

3) What is reset logs option.

DG:

1) How you will create Physical DB?

2) How you will stop and start the recovery

RAC:

1) How you will stop two instances of a cluster where 4 nodes.

2) What is the SCAN?

Performance tuning

1) If user complaining that the sql is running fine till yesterday and today it is performing very poorly, how you will solve this problem. what is the approach you will start.

2) What you the method you will approach if you find “DB sequential read” in the top 5 events.

3) In which scenarios, you will consider to increase shared_pool_size.

4) In which scenarios, you will consider to tune pga_aggregate_target.

5) How you will avoid disk sorts.

6) How you will tune undo tablespace.

7) How you will enable the trace for a session.

 Recovery: 

1) What is instance recovery? how recovery will happen.

2) What is incomplete recovery and how you will open the database after incomplete recovery?

3) How to recover a data file?

Categories: INTERVIEW_QS

How to compile synonyms.

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

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

Categories: Oracle DBA

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.

 

 

 

 

Categories: DB_LEVEL

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

 

Categories: ISSUES

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.

 

 

Categories: 11g

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

Categories: 11g
Follow

Get every new post delivered to your Inbox.