Category: SQL scripts

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” »

Run following query as sysdba. alter session set nls_date_format=’DD-MON-RRRR:HH24:MI:SS’; alter session set nls_date_format=’DD-MON-RRRR:HH24:MI:SS’; select username, created, account_status /* Users who granted role with DML privs */ from dba_users where oracle_maintained = ‘N’ and username in (select grantee from dba_role_privs where granted_role in (select role from dba_roles where role in (select GRANTEE from dba_tab_privs where privilege…

Read More “Find all users who have DML privileges” »

/********************************************Author: Paresh MehtaScript Name : find_planinfo.sql********************************************/set lines 200set pages 500set echo offcol execs for 999,999,999col avg_etime for 999,999.999col avg_lio for 999,999,999.9col begin_interval_time for a30col node for 99999col plan_hash_value format 99999999999999col sql_profile format a30col intrvl format a29col component format a20alter session set nls_date_format=’DD-MON-RRRR:HH24:MI:SS’;–break on plan_hash_value,sql_profile on startup_time skip 1accept v_sql_id prompt ‘Enter SQL_ID ->’select s.instance_number, to_char(begin_interval_time,’DD-MON:HH24:MI’)…

Read More “Find_planinfo.sql” »