Find the sql_text using ospid
Find top pids of oracle user $top -u oracle Enter the os pid and find the sql id select sid, sql_id from v$session s, v$process p where s.paddr = p.addr…
Read MoreQuery to find all executing sql
Query to find all executing sql select sql_text from v$sqlarea where users_executing > 0 / Query to find a particular users current sql Select sql_text from v$sqlarea where (address, hash_value)…
Read MoreQuery to find all constraints in a table
col type format a10 col cons_name format a30 select decode(constraint_type, ‘C’, ‘Check’, ‘O’, ‘R/O View’, ‘P’, ‘Primary’, ‘R’, ‘Foreign’, ‘U’, ‘Unique’, ‘V’, ‘Check view’) type , constraint_name cons_name , status…
Read MoreQuery to find the table with most physica i/o
select table_name,total_phys_io from (select owner ||’.’|| object_name as table_name, sum(value) as total_phys_io from v$segment_statistics where owner!=’SYS’ and object_type=’TABLE’ and statistic_name in (‘physica l reads’,’physical reads direct’, ‘physical writes’,’physical writes direct’)…
Read MoreQuery to find which user using temp tablespace
select username, tablespace ,sql_id from v$tempseg_usage; (or) select S.sid,S.username,U.tablespace,S.sql_hash_value ||’/’|| U.sqlhash hash_value, U.segtype,u.contents,U.blocks from v$session S, v$tempseg_usage U where S.saddr=U.session_addr order by U.blocks; (or) select S.sid, S.username “DB_USER”, P.username “OS_USER”…
Read MoreORA-12154: TNS:could not resolve service name
If you are using local naming (TNSNAMES.ORA file): Make sure that “TNSNAMES” is listed as one of the values of the NAMES.DIRECTORY_PATH parameter in the Oracle Net profile (SQLNET.ORA) Verify…
Read MoreUse of TABLE_EXISTS_ACTION parameter in IMPDP
Table_Exists_Action used in impdp if table is already exists in the database where you want to import. TABLE_EXISTS_ACTION = SKIP Table_Exists_Action =Skip: skip is for skipping the table which are…
Read MoreORA-04044: procedure, function, package, or type is not allowed here
Note: Tables containing ‘$’ SYMBOL and when ever we use tables like v$session,v$log,v$logfile etc shell script take ‘$’ SYMBOL as shell variables and then while reading script it gives error…
Read MoreScript to know the history of an sql_id and its plan change details
selectSQL_ID, PLAN_HASH_VALUE, sum(EXECUTIONS_DELTA) EXECUTIONS, sum(ROWS_PROCESSED_DELTA) CROWS, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60) ELA_MINSfrom DBA_HIST_SQLSTATwhere SQL_ID in (‘&sqlid’)group by SQL_ID , PLAN_HASH_VALUEorder by SQL_ID, CPU_MINS/
Read MoreScript will generate the list of users which are currently under waiting
set pages 50000 lines 32767col username format a12col sid format 9999col state format a15col event format a50col wait_time format 99999999set pagesize 100set linesize 120 select s.sid, s.username, se.event, se.state, se.wait_timefrom…
Read More