DBA useful query in Oracle

DBA useful query in Oracle

Check the user name SID of SQL Query running

SELECT username,terminal,SID,SERIAL#,SQL_TEXT
FROM V$SESSION, V$SQL
WHERE V$SESSION.SQL_ADDRESS = V$SQL.ADDRESS
and OLD_HASH_VALUE = '1315080088'

Check the query with process id and sid

SELECT RPAD('USERNAME : ' || s.username, 80) ||
RPAD('OSUSER : ' || s.osuser, 80) ||
RPAD('PROGRAM : ' || s.program, 80) ||
RPAD('SPID : ' || p.spid, 80) ||
RPAD('SID : ' || s.sid, 80) ||
RPAD('SERIAL# : ' || s.serial#, 80) ||
RPAD('MACHINE : ' || s.machine, 80) ||
RPAD('TERMINAL : ' || s.terminal, 80) ||
RPAD('SQL TEXT : ' || q.sql_text, 80)
FROM v$session s ,v$process p ,v$sql q
WHERE s.paddr = p.addr ---- AND p.spid = '21502'
AND s.sql_address = q.address
AND s.sql_hash_value = q.hash_value;

Checked partition tables present in schema

select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME from dba_tab_partitions WHERE TABLE_OWNER = 'xxxx';

Checked the size of table:

select table_name,round((blocks*8)/1024/1024,2)||'GB' "size" from user_tables where table_name = 'BIG1';

Checked actual size of table in GB

select table_name,round((num_rows*avg_row_len/1024/1024),2)||'GB' "size" from user_tables where table_name = 'BIG1'

Checked the DBA JOBS Configured

select * from dba_scheduler_jobs
select * from dba_jobs
select d.job_name,d.job_action
from dba_scheduler_jobs d,sys.scheduler$_job s
where d.job_action = s.program_action
and .obj# = &myjobnum;

USER COMMANDS for generate User information

select 'Alter user '||username||' identified by '''||password||''' Default tablespace '||DEFAULT_TABLESPACE||' TEMPORARY TABLESPACE '
||TEMPORARY_TABLESPACE||' '||case when account_status= 'OPEN' then ';' else 'LOCKED;' end from dba_users;

Creation commands and grants for users

select 'CREATE USER '||username||' IDENTIFIED BY VALUES '''||PASSWORD||''' DEFAULT TABLESPACE ' ||DEFAULT_TABLESPACE||' TEMPORARY TABLESPACE '||TEMPORARY_TABLESPACE||' PROFILE '||PROFILE|| ' ACCOUNT '||ACCOUNT_STATUS||';' from dba_users where username='ELF';

Use the command in 11g database

select 'Alter user '||a.username||' identified by values '''||b.password||''' Default tablespace '||DEFAULT_TABLESPACE||' TEMPORARY TABLESPACE '
||TEMPORARY_TABLESPACE||' '||case when account_status= 'OPEN' then ';' else 'LOCKED;' end from dba_users a,sys.user$ b where b.name = a.username;

For getting USER Roles

select 'GRANT '||GRANTED_ROLE||' TO '||Grantee||';' FROM DBA_ROLE_PRIVS WHERE GRANTEE='HR'
union all
select 'GRANT '||PRIVILEGE||' TO '||GRANTEE||';' FROM DBA_SYS_PRIVS WHERE GRANTEE='BOUVETNO1'
union all
select 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE||';' from DBA_TAB_PRIVS where GRANTEE='BOUVETNO1';

Check the database db_cache value

Select size_for_estimate c1, buffers_for_estimate c2, estd_physical_read_factor c3, estd_physical_reads c4 from v$db_cache_advice
where name = 'DEFAULT' and block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size') and advice_status = 'ON';

Query run between specific time:

SELECT sql_id, first_load_time, last_active_time, sql_text
FROM v$sqlarea
WHERE last_active_time BETWEEN TO_DATE ('19/10/2012 14:00:00',
'DD/MM/YYYY HH24:MI:SS'
)
AND TO_DATE ('19/10/2012 15:00:00',
'DD/MM/YYYY HH24:MI:SS'
); SELECT sql_id, first_load_time, last_active_time, sql_text
FROM v$sqlarea
WHERE last_active_time BETWEEN TO_DATE ('19/10/2012 14:00:00',
'DD/MM/YYYY HH24:MI:SS'
)
AND TO_DATE ('19/10/2012 15:00:00',
'DD/MM/YYYY HH24:MI:SS'
);

Create truncate command for tables:

Select 'truncate table ' ||owner||'.'||object_name from dba_objects where object_name in ('GEOPOSITION','TIMESERIEDATA_OLD','METEREDVALUE','VALUE');

Finding query with high temp space usage using ASH views

Select se.username,se.sid,sum(tu.blocks)*8192 from v$tempseg_usage tu,v$session se where tu.session_addr=se.saddr and tu.session_num=se.serial#
group by se.sid,se.username order by se.sid;

Long Operation query:

select sofar Blocks_Processed,totalwork Total_Work,round((sofar/totalwork)*100,2) Percentage_Completed,
totalwork-sofar Total_Work_Left,start_time Start_Time,round((elapsed_seconds/60),0) Elapsed_Minutes,
substr(message,1,33) Message,username
from v$session_longops where rownum 0 order by start_time desc;

CPU usage by session wise

SELECT se.username, ss.sid, ROUND (value/100) "CPU Usage"
FROM v$session se, v$sesstat ss, v$statname st
WHERE ss.statistic# = st.statistic#
AND name LIKE '%CPU used by this session%'
AND se.sid = ss.SID
AND se.username IS NOT NULL
ORDER BY value DESC;

Check archive log generation:

SELECT * FROM (
SELECT * FROM (
SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0)), '999') "00:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0)), '999') "01:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0)), '999') "02:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0)), '999') "03:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0)), '999') "04:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0)), '999') "05:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0)), '999') "06:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0)), '999') "07:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0)), '999') "08:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0)), '999') "09:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0)), '999') "10:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0)), '999') "11:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0)), '999') "12:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0)), '999') "13:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0)), '999') "14:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0)), '999') "15:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0)), '999') "16:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0)), '999') "17:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0)), '999') "18:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0)), '999') "19:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0)), '999') "20:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0)), '999') "21:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0)), '999') "22:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0)), '999') "23:00"
FROM V$LOG_HISTORY
WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
) WHERE ROWNUM < 8

Query that can be used to derive the wait ratio for dispatcher processes is as follows:

SELECT d.network, DECODE(SUM(q.totalq),0,’NONE’,
SUM(q.wait)/SUM(q.totalq))
FROM v$queue q, v$dispatcher d
WHERE q.type = ‘DISPATCHER’ and p.paddr = d.paddr;

Generate Tablespace script:

select 'create tablespace ' || df.tablespace_name || chr(10)
|| ' datafile ''' || df.file_name || ''' size ' || df.bytes
|| decode(autoextensible,'N',null, chr(10) || ' autoextend on maxsize '
|| maxbytes)
|| chr(10)
|| 'default storage ( initial ' || initial_extent
|| decode (next_extent, null, null, ' next ' || next_extent )
|| ' minextents ' || min_extents
|| ' maxextents ' || decode(max_extents,'2147483645','unlimited',max_extents)
|| ') ;' "Script To Recreate Tablespaces"
from dba_data_files df, dba_tablespaces t
where df.tablespace_name=t.tablespace_name;

Advertisements

One thought on “DBA useful query in Oracle

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.