Posts

Generate the DDL of users in oracle 8i and 9i

-
select 'alter user '||username||' profile default;'||' alter user '||username||' identified by values '''||b. PASSWORD||''';'||' alter user '||username||' profile '||PROFILE||';' from dba_users,sys.user$ b where username in (select username from dba_users) and username=b.name; ---- set pagesize 0 set escape on select 'create user ' || U.username || ' identified ' || DECODE(password,       NULL, 'EXTERNALLY',       ' by values ' || '''' || password || ''''       ) || chr(10) || 'default tablespace ' || default_tablespace || chr(10) || 'temporary tablespace ' || temporary_Tablespace || chr(10) || ' profile ' || profile || chr(10) || 'quota ' || decode ( Q.max_bytes, -1, 'UNLIMITED', NULL, 'UNLIMITED', Q.max_bytes) || ' on ' || default_tablespace
Post a Comment
Read more

Queries to identify the sessions identifying Undo,Temp & Redo Segments

-
1. Query to identify the sessions consuming undo set lines 500 col ORAUSER for a20 col PROGRAM for a30 col SID_SERIAL for a15 col Undo for a10 SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,            NVL(s.username, 'None') orauser,            s.program,            r.name undoseg,            t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"       FROM sys.v_$rollname    r,            sys.v_$session     s,            sys.v_$transaction t,            sys.v_$parameter   x     WHERE s.taddr = t.addr       AND r.usn   = t.xidusn(+)       AND x.name  = 'db_block_size'; ---      select s.sid,         s.username,        sum(ss.value) / 1024 / 1024 as undo_size_mb from  v$sesstat ss   join v$session s on s.sid = ss.sid   join v$statname stat on stat.statistic# = ss.statistic# where stat.name = 'undo change vector size' and s.type <> 'BACKGROUND' and s.u
Post a Comment
Read more