Active Connections to Oracle Database
V$SESSION displays session information for each current session.
select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
— b.server,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type=’USER’
order by spid;
Big Tables for Testing
An «ALL_OBJECTS» Table with 1’000’000 Rows
The following Code is from Tom Kyte (http://asktom.oracle.com)
— Create Table with same structure as ALL_TABLES from Oracle Dictionary
create table bigtab
as
select rownum id, a.*
from all_objects a
where 1=0;
alter table bigtab nologging;— Fill 1’000’000 Rows into the Table
declare
l_cnt number;
l_rows number := 1000000;
begin
— Copy ALL_OBJECTS
insert /*+ append */
into bigtab
select rownum, a.*
from all_objects a;
l_cnt := sql%rowcount;
commit;— Generate Rows
while (l_cnt < l_rows)
loop
insert /*+ APPEND */ into bigtab
select rownum+l_cnt,
OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME,
TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY
from bigtab
where rownum <= l_rows-l_cnt;
l_cnt := l_cnt + sql%rowcount;
commit;
end loop;
end;
/alter table bigtab add constraint
bigtab_pk primary key(id);
A Table with Random Data and same Size as ALL_OBJECTS
CREATE TABLE bigtab (
id NUMBER,
weight NUMBER,
adate DATE
);INSERT INTO bigtab (id, weight, adate)
SELECT MOD(ROWNUM,1000),
DBMS_RANDOM.RANDOM,
SYSDATE-1000+DBMS_RANDOM.VALUE(0,1000)
FROM all_objects
/
51502 rows created.
A Table which can be used for Partition Tests
The ID of the table can be used for Range Partitioning
create table bigtab (
id number(12,6),
v1 varchar2(10),
padding varchar2(50)
)
nologging — just to save a bit of time
/insert /*+ append ordered full(s1) use_nl(s2) */
into bigtab
select
3000 + trunc((rownum-1)/500,6),
to_char(rownum),
rpad(‘x’,50,’x’)
from
all_objects s1, — you’ll need the privilege
all_objects s2
where
rownum <= 1000000
/
commit;ID V1 PADDING
———- ———- ————————————————–
3000 1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.002 2 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.004 3 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.006 4 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.008 5 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.01 6 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.012 7 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.014 8 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.016 9 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
A Table with Date’s which can be used for Partition Tests
This code is from http://www.oracle-base.com
CREATE TABLE bigtab (
id NUMBER(10),
created_date DATE,
lookup_id NUMBER(10),
data VARCHAR2(50)
);DECLARE
l_lookup_id NUMBER(10);
l_create_date DATE;
BEGIN
FOR i IN 1 .. 1000000 LOOP
IF MOD(i, 3) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -24);
l_lookup_id := 2;
ELSIF MOD(i, 2) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -12);
l_lookup_id := 1;
ELSE
l_create_date := SYSDATE;
l_lookup_id := 3;
END IF;INSERT INTO bigtab (id, created_date, lookup_id, data)
VALUES (i, l_create_date, l_lookup_id, ‘This is some data for ‘ || i);
END LOOP;
COMMIT;
END;
/
Verify:
SQL> select id,to_char(created_date,’DD.MM.YYYY’),
lookup_id, data
from bigtab where rownum < 10;ID TO_CHAR(CR LOOKUP_ID DATA
———- ———- ———- —————————–
1 21.08.2007 3 This is some data for 1
2 21.08.2006 1 This is some data for 2
3 21.08.2005 2 This is some data for 3
4 21.08.2006 1 This is some data for 4
5 21.08.2007 3 This is some data for 5
6 21.08.2005 2 This is some data for 6
7 21.08.2007 3 This is some data for 7
8 21.08.2006 1 This is some data for 8
9 21.08.2005 2 This is some data for 9
Source:
Database Session Stats
Session
1. Connected users list
set lines 100 pages 999 col ID format a15 select username , sid || ',' || serial# "ID" , status , last_call_et "Last Activity" from v$session where username is not null order by status desc , last_call_et desc /
2. Time since last user activity
set lines 100 pages 999 select username , floor(last_call_et / 60) "Minutes" , status from v$session where username is not null order by last_call_et /
3. Sessions sorted by logon time
set lines 100 pages 999 col ID format a15 col osuser format a15 col login_time format a14 select username , osuser , sid || ',' || serial# "ID" , status , to_char(logon_time, 'hh24:mi dd/mm/yy') login_time , last_call_et from v$session where username is not null order by login_time /
4.User information including OS process ID
col "SID/SERIAL" format a10 col username format a15 col osuser format a15 col program format a40 select s.sid || ',' || s.serial# "SID/SERIAL" , s.username , s.osuser , p.spid "OS PID" , s.program from v$session s , v$process p Where s.paddr = p.addr order by to_number(p.spid) /
5. Show users’ current SQL
Select sql_text from v$sqlarea where (address, hash_value) in (select sql_address, sql_hash_value from v$session where username like '&username') /
6. Session status associated with the specified OS process ID
select s.username , s.sid , s.serial# , p.spid , last_call_et , status from V$SESSION s , V$PROCESS p where s.PADDR = p.ADDR and p.spid='&pid' /
7. Show all active SQL
set feedback off set serveroutput on size 9999 column username format a20 column sql_text format a55 word_wrapped begin for x in (select username||'('||sid||','||serial#||') ospid = '|| process || ' program = ' || program username, to_char(LOGON_TIME,' Day HH24:MI') logon_time, to_char(sysdate,' Day HH24:MI') current_time, sql_address, sql_hash_value from v$session where status = 'ACTIVE' and rawtohex(sql_address) <> '00' and username is not null ) loop for y in (select sql_text from v$sqlarea where address = x.sql_address ) loop if ( y.sql_text not like '%listener.get_cmd%' and y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then dbms_output.put_line( '--------------------' ); dbms_output.put_line( x.username ); dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value); dbms_output.put_line( substr( y.sql_text, 1, 250 ) ); end if; end loop; end loop; end; /
8. List long operations
set lines 100 pages 999 col username format a15 col message format a40 col remaining format 9999 select username , to_char(start_time, 'hh24:mi:ss dd/mm/yy') started , time_remaining remaining , message from v$session_longops where time_remaining = 0 order by time_remaining desc /
9. List open cursors per user
a, set pages 999 select sess.username , sess.sid , sess.serial# , stat.value cursors from v$sesstat stat , v$statname sn , v$session sess where sess.username is not null and sess.sid = stat.sid and stat.statistic# = sn.statistic# and sn.name = 'opened cursors current' order by value / b, set lines 100 pages 999 select count(hash_value) cursors , sid , user_name from v$open_cursor group by sid , user_name order by cursors /
Job Scheduling
1. List Scheduled jobs
set lines 100 pages 999 col schema_user format a15 col fails format 999 select job , schema_user , to_char(last_date, 'hh24:mi dd/mm/yy') last_run , to_char(next_date, 'hh24:mi dd/mm/yy') next_run , failures fails , broken , substr(what, 1, 15) what from dba_jobs order by 4 /