Archive

Archive for the ‘General’ Category

Active Connections to Oracle Database

May 3, 2012 Leave a comment

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;

Source

Advertisements
Categories: General, Uncategorized

Big Tables for Testing

November 29, 2011 Leave a comment

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:

Akadia

Database Session Stats

August 18, 2011 Leave a comment

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
/

Database Users and Roles Stats

August 18, 2011 Leave a comment

Users:

1. List of Users

set pages 999 lines 100
col username	format a20
col status	format a8
col tablespace	format a20
col temp_ts	format a20
select	username
,	account_status status
,	created
,	default_tablespace tablespace
,	temporary_tablespace temp_ts
from	dba_users
order	by username
/

Roles:

1. Find a role

select	*
from 	dba_roles
where	role like '&role'
/

2. List roles granted to a user

select	grantee
,	granted_role
,	admin_option
from	dba_role_privs
where	grantee like upper('&username')
/

3.  List system privileges granted to a role

select	privilege
,	admin_option
from	role_sys_privs
where	role like '&role'
/

4. List table privileges granted to a role

select	owner || '.' || table_name "TABLE"
,	column_name
,	privilege
,	grantable
from	role_tab_privs
where	role like '&role'
/

Links:

Job Scheduling

August 1, 2011 Leave a comment

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
/

Database Size

February 8, 2011 Leave a comment

dba_data_files –  means total size of the data file.

The data file size is total of dba_free_space and dba_segments:
dba_data_files = dba_free_space + dba_segments

dba_segments – means used size of the data file

dba_free_space – means free size of the data file

Size Check Queries:

To get total datafiles size, free and used space in percentage:

select t2.total "TOTAL SIZE",t1.free "FREE SPACE",
 (t1.free/t2.total)*100 "FREE (%)",
 (1-t1.free/t2.total)*100 "USED (%)"
from (select sum(bytes)/1024/1024 free from dba_free_space) t1 ,
 (select sum(bytes)/1024/1024 total from dba_Data_files) t2;

To get the size of all Data files:

select sum(bytes)/1024/1024 "Data files size in MB" from dba_data_files;

To get the size of all TEMP files:

select nvl(sum(bytes),0)/1024/1024 "Temp files size in MB" from dba_temp_files;

To get the size of the on-line redo-logs:

SELECT SUM(BYTES)/1024/1024 "Redo files size in MB" FROM SYS.V_$LOG;

Total Size and free size:

select round(sum(used.bytes) / 1024 / 1024 ) || ' MB' "Database Size"
, round(free.p / 1024 / 1024) || ' MB' "Free space"
from (select bytes from v$datafile
 union all
 select bytes from v$tempfile
 union all
 select bytes from v$log) used
, (select sum(bytes) as p from dba_free_space) free
group by free.p;

Individual tablespace size:

select tablespace_name as "Tablespace Name",sum(bytes)/(1024*1024) as "Size"
from dba_data_files
 group by tablespace_name
 ORDER BY 1;

Individual tablespace used size:

select tablespace_name as "Tablespace Name",sum(bytes)/(1024*1024) as "Used Size"
from dba_segments
 GROUP BY TABLESPACE_NAME
 order by 1;

Individual tablespace free space:

select tablespace_name as "Tablespace Name", sum(bytes)/(1024*1024) as "Free Space"
 from dba_free_space
 group by tablespace_name
 order by 1;

Reference Links: