Lab 4

q1) select group#,(bytes/1024),members,status from v$log ;

group# (bytes/1024) members status ---------- ------------ ---------- ---------------2 10240 2 current 3 10240 1 inactive 4 10240 1 inactive 5 10240 2 inactive 4 rows selected. _________________________________________________________ q2) select group#,(bytes/1024),members,status from v$log where status = 'current';

group# (bytes/1024) members status ---------- ------------ ---------- ---------------2 10240 2 current 1 row selected.

_________________________________________________________ q3) select group#,(bytes/1024),members,status from v$log where members in ( select max(members) from v$log);

group# (bytes/1024) members status ---------- ------------ ---------- ---------------2 10240 2 current 5 10240 2 inactive 2 rows selected. _________________________________________________________ q4) select group#,substr(member,1,30),type from v$logfile;

group# ---------3 2 5 4 2 5 5

substr(member,1,30) -----------------------------c:\oracle\oradata\o10g\logo10g c:\oracle\oradata\o10g\redo02. c:\oracle\oradata\o10g\logo10g c:\oracle\oradata\o10g\logo10g c:\oracle\oradata\o10g\redo022 c:\oracle\oradata\o10g\logo10g c:\oracle\oradata\o10g\logo10g

type ------online online online online online online online

7 rows selected.

_________________________________________________________ q5) select count(distinct group#) from v$logfile where type = 'online';

count(distinctgroup#) --------------------4 1 row selected. _________________________________________________________ q6) select group#,substr(member,1,30),type from v$logfile where group# in (select group# from v$log where status = 'inactive');

group# ---------3 4

substr(member,1,30) -----------------------------c:\oracle\oradata\o10g\logo10g c:\oracle\oradata\o10g\logo10g

type ------online online

2 rows selected.

_________________________________________________________ q7) select f.group#, substr(member,1,30) name, bytes/1024/1024 bytemb from v$log l join v$logfile f on f.group#=l.group#; group# name bytemb ---------- ------------------------------ ---------3 c:\oracle\oradata\o10g\logo10g 10

2 5 4 2 5 5

c:\oracle\oradata\o10g\redo02. c:\oracle\oradata\o10g\logo10g c:\oracle\oradata\o10g\logo10g c:\oracle\oradata\o10g\redo022 c:\oracle\oradata\o10g\logo10g c:\oracle\oradata\o10g\logo10g

10 10 10 10 10 10

7 rows selected. ________________________________________________________ q8) select count(*) noofmembers from v$logfile; noofmembers ----------7 1 row selected. ________________________________________________________ q9) select ts#, name from v$tablespace;

ts# ---------0 1 2 4 3 6 7 13 87 88 89

name -----------------------------system undotbs1 sysaux users temp example staff student is337 is337a is337b

11 rows selected. _______________________________________________________ 10) select t.name, status from v$datafile j join v$tablespace t on t.ts#= j.ts#;

name

status

-----------------------------system undotbs1 sysaux users example staff student is337 is337a is337b example

------system online online online online online online online online online online

11 rows selected. _______________________________________________________ q11) select t.ts#, t.name, block_size/1024 blocksize from v$datafile j join v$tablespace t on t.ts#= j.ts#;

ts# ---------0 1 2 4 6 7 13 87 88 89 6

name blocksize ------------------------------ ---------system 8 undotbs1 8 sysaux 8 users 8 example 8 staff 8 student 8 is337 8 is337a 8 is337b 8 example 8

11 rows selected. _______________________________________________________ q12) select file#, name, to_char(bytes/1024/1024,'9999999.9') sizeinmb from v$datafile;

file# name sizeinmb -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------1 c:\oracle\oradata\o10g\system01.dbf 460.0 2 c:\oracle\oradata\o10g\undotbs01.dbf 35.0 3 c:\oracle\oradata\o10g\sysaux01.dbf 380.0 4 c:\oracle\oradata\o10g\users01.dbf 5.0 5 c:\oracle\oradata\o10g\example01.dbf 150.0 6 c:\oracle\oradata\o10g\staff.ora 5.0 7 c:\oracle\oradata\o10g\student.ora 50.0 68 c:\oracle\oradata\o10g\is337.ora 200.0 77 c:\oracle\oradata\o10g\is337a.ora 200.0 85 c:\oracle\oradata\o10g\is337b.ora 200.0 102 c:\oracle\oradata\o10g\.ora 5.0 11 rows selected. ______________________________________________________ q13) select d.ts#, t.name, to_char(sum(bytes)/1024/1024,'9999999.9') sizeinmb from v$tablespace t join v$datafile d on t.ts#=d.ts# group by d.ts#,t.name;

ts# ---------0 1 2 4 6 7 13 87 88 89

name sizeinmb ------------------------------ ---------system 460.0 undotbs1 35.0 sysaux 380.0 users 5.0 example 155.0 staff 5.0 student 50.0 is337 200.0 is337a 200.0 is337b 200.0

10 rows selected. ______________________________________________________ q14)

select d.ts#, t.name, to_char(sum(bytes)/1024/1024,'9999999.9') sizeinmb from v$tablespace t join v$datafile d on t.ts#=d.ts# having sum(bytes)=(select max(sum(bytes)) from v$datafile group by ts#) group by d.ts#,t.name;

ts# name sizeinmb ---------- ------------------------------ ---------0 system 460.0 1 row selected.