Oracle Database Administration

USER session from ASH select a.snap_id,a.dbid,a.instance_number,a.session_id,a.session_serial#,a.sql_id,a.user_id,a.session_state,a.blocking_session, a.time_waited,to_char(a.sample_time,'mm-dd-yyyy hh24:mi:ss') as stime,a.event from dba_hist_active_sess_history a where sample_time between TIMESTAMP'2012-09-13 12:25:00' AND TIMESTAMP'2012-09-13 12:30:00' select snap_id,END_INTERVAL_TIME from dba_hist_snapshot where END_INTERVAL_TIME > sysdate-1  order by 1; It runs faster if you use the snap_id; Lock prompt +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ prompt    Following output gives the details of blocker sessions prompt ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ select * from gv$lock where block=1; select distinct blocking_instance,blocking_session from gv$session where blocking_session is not null order by 1,2; set lines 199 select /*+ rule */ inst_id blocker_instance, sid blocker_sid, s...

Performing Database health checks, when there is an issue reported by Application users. 1. Check the Database details 2. Monitor the consumption of resources 3. Check the Alert Log 4. Check Listener log 5. Check Filesystem space Usage 6. Generate AWR Report 7. Generate ADDM Report 8. Finding Locks,Blocker Session and Waiting sessions in a oracle database 9. Check for alerts in OEM 1. Check the Database details :- ============================= set pages 9999 lines 300 col OPEN_MODE for a10 col HOST_NAME for a30 select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance; For RAC: ------- set pages 9999 lines 300 col OPEN_MODE for a10 col HOST_NAME for a30 select INST_ID,INSTANCE_NAME, name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from...

What is difference between oracle SID and Oracle service name? Oracle SID is the unique name that uniquely identifies your instance/database where as the service name is the TNS alias can be same or different as SID. What are the steps to install oracle on Linux system? List two kernel parameter that effect oracle installation? Initially set up disks and kernel parameters, then create oracle user and DBA group, and finally run installer to start the installation process. The SHMMAX & SHMMNI two kernel parameter required to set before installation process. What are bind variables? With bind variable in SQL, oracle can cache queries in a single time in the SQL cache area. This avoids a hard parse each time, which saves on various locking and latching resource we use to check object existence and so on. What is the difference between data block/extent/segment? A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks ...