Category: Oracle

set lines 120 pages 200col dd format a20col inst_id format 99col metric_name format a30col value format 99.99 select x.dd, x.inst_id, x.metric_name, x.value “%CPU”, y.sessfrom(select to_char(sysdate, ‘DD-MON-RRRR:HH24:MI’) DD, inst_id, metric_name, valuefrom gv$sysmetricwhere metric_name like ‘Host CPU Utilization%’ and group_id=2 ) x,(select inst_id, count(1) sess from gv$sessionwhere status = ‘ACTIVE’ and osuser != ‘oracle’group by inst_id) ywhere…

Read More “findinfo.sql (SQL for getting CPU and Active session info)” »

col SAMPLE_TIME format a25col program format a20col SQL_EXEC_START format a25col machine format a15set lines 140 pages 200alter session set nls_date_format=’DD-MON-RRRR:HH24:MI:SS’; select SAMPLE_TIME, SESSION_ID, SESSION_SERIAL#, USER_ID, sql_ID, Machine, program, SQL_EXEC_STARTfrom gv$active_session_historywhere session_id = &v_session_idand sample_time > sysdate – 1/24order by sample_time/

DB Users are required to reset the password periodically for better security. For some service accounts,  we need to make sure the password does not expire and does not impact the application. For this purpose, we need to update that user’s Profile. Each Profile has multiple security settings that controls Password_life_time, password_reuse_time, password_reuse_max  settings. These…

Read More “Creating never expiring DB user accounts in Oracle” »

Converting rows to single column: create table emp ( empno number, empname varchar2(100), deptno number); insert into emp values( 1, ’emp1′, 10); insert into emp values( 2, ’emp2′, 20); insert into emp values( 3, ’emp3′, 30); insert into emp values( 4, ’emp4′, 10); insert into emp values( 5, ‘paresh’, 30); insert into emp values( 6,…

Read More “Convert multiple rows to single column” »