Allokierter Speicher in kB der ges. Instanz

 select /*+ use_nl(n,s) */ sum(value) "Memory Allocated in bytes"
  from v$statname n, v$sesstat s
 where s.statistic# = n.statistic#
   and name = 'session uga memory'

 
 


 Zurück

 

Statistik zu den Datendateien

 select substr (name, 1, 40) "Datei",
	phyblkrd "Blk Lesen",
	phyblkwrt "Blk Schreiben",
	phyblkrd + phyblkwrt "BlK Gesamt"
	from v$datafile df, v$filestat fs
		where df.file# = fs.file#
		order by phyblkrd + phyblkwrt desc;
 
 


 Zurück

 

Anzahl der phys. Schreib- und Lesezugriffe pro Sec für jedes DB-File

select substr(NAME,1,30) "NAME",PHYRDS,PHYWRTS,PHYBLKRD,PHYBLKWRT
  from V$DBFILE DF, V$FILESTAT FS
 where DF.FILE#=FS.FILE#
 order by NAME

 
 


 Zurück

 

Informationen zu den Datendateien

select substr (tablespace_name, 1, 15)
"tablespace",
substr (file_name, 1, 33) "Datei",
bytes,
blocks,
status,
autoextensible,
maxbytes,
maxblocks,
increment_by
from dba_data_files
order by tablespace_name, file_name;
 
 


 Zurück

 

Partition Indizes

select i.table_owner,
       i.index_name,
       c.table_name,
       c.column_name,
       decode(i.uniqueness,'UNIQUE','YES','NONUNIQUE','NO','???') uni
  from dba_ind_columns c, dba_indexes i
 where i.table_name = c.table_name
   and i.index_name = c.index_name
   and i.table_name like upper('%')
   and i.table_owner = c.table_owner
   and i.partitioned = 'YES'
order by i.table_owner,
         c.table_name,
         i.uniqueness desc,
         c.index_name,
         c.column_position
 
 


 Zurück

 

Rollenzuordnung zu Benutzern und Rollen

select  to_char(sysdate, 'MM/DD/YY') datum,
  substr(grantee,1,27) grantee,
  substr(granted_role,1,30) granted_role,
  substr(default_role,1,20) default_role,
  substr(admin_option,1,10) admin_option
from dba_role_privs
where grantee not in ('SYS','SYSTEM')
order by 1,2,3

 
 


 Zurück

 

Fragmentierte Objekte mit mehr als 3 Extents

 select  to_char(sysdate, 'MM/DD/YY') datum,
    owner,
    tablespace_name,
    segment_type,
    segment_name,
    count(extent_id) extent_id,
    sum(bytes) bytes
  from dba_extents
 where substr(owner,1,10) not in ('SYS')
group by
  owner,
  tablespace_name,
  segment_type,
  segment_name
having count(extent_id) > 3
order by 1,2,3,4
 

Zurück