Fake Blogger

select TABLESPACE_NAME,
INITIAL_EXTENT,
NEXT_EXTENT,
MIN_EXTENTS,
MAX_EXTENTS,
PCT_INCREASE,
STATUS,
CONTENTS
from dba_tablespaces
order by TABLESPACE_NAME;

Oracle-Tablespace Coalesced Exts:
set linesize 2000
select TABLESPACE_NAME,
TOTAL_EXTENTS,
EXTENTS_COALESCED,
PERCENT_EXTENTS_COALESCED,
TOTAL_BYTES,
BYTES_COALESCED,
TOTAL_BLOCKS,
BLOCKS_COALESCED,
PERCENT_BLOCKS_COALESCED
from dba_free_space_coalesced
order by TABLESPACE_NAME;

PROMPT = Importent Buffer Hits
PROMPT ——————————————————————————–
PROMPT — Buffer Hit Ratio (Faustregel: groesser 95% im Verh√§ltnis zu I/O)
PROMPT ——————————————————————————–
SELECT round((1-(sum(decode(name,’physical reads’,value,0))/
(sum(decode(name,’db block gets’,value,0)) + sum(decode(name,’consistent gets’,value,0)))))*100,2) ” Buffer hit ratio”
FROM gv$sysstat;
PROMPT ——————————————————————————–
PROMPT — Dictionary Cache Rate (Faustregel: groesser 90%)
PROMPT ——————————————————————————–
SELECT round((1-(sum(getmisses)/sum(gets)))*100,2) “Rowcache Hit Ratio”FROM gv$rowcache;
PROMPT ——————————————————————————–
PROMPT — Library Cache Rate (Faustregel: […]

column size_for_estimate format 999,999,999,999 heading ‘Cache Size (m)’
column buffers_for_estimate format 999,999,999 heading ‘Buffers’
column estd_physical_read_factor format 999.90 heading ‘Estd Phys|Read Factor’
column estd_physical_reads format 999,999,999 heading ‘Estd Phys| Reads’
SELECT size_for_estimate
, buffers_for_estimate
, estd_physical_read_factor
, estd_physical_reads
FROM V$DB_CACHE_ADVICE
WHERE name = ‘DEFAULT’
AND block_size = (SELECT value FROM V$PARAMETER WHERE name = ‘db_block_size’)
AND advice_status […]

SELECT vss.owner, vss.object_name, vss.statistic_name, vss.VALUE,
ds.BYTES segsize, ds.BUFFER_POOL
FROM v$segment_statistics vss, dba_segments ds
WHERE vss.statistic_name = ‘physical reads’
AND vss.VALUE > 5000000
AND ds.segment_type = vss.object_type
AND ds.segment_name = vss.object_name
AND ds.owner = vss.owner
AND ds.BUFFER_POOL = ‘DEFAULT’
ORDER BY VALUE
/
WITH segstat AS
(
SELECT object_type, object_name, owner, subobject_name,
MAX (CASE statistic_name
WHEN ‘physical reads’
THEN VALUE
END) pr,
MAX (CASE statistic_name
WHEN ‘physical reads direct’
THEN VALUE
END
) prd
FROM v$segment_statistics vss
WHERE statistic_name IN
(’physical […]

SELECT vss.owner, vss.object_name, vss.statistic_name, vss.VALUE,
ds.BYTES segsize, ds.BUFFER_POOL
FROM v$segment_statistics vss, dba_segments ds
WHERE vss.statistic_name = ‘physical reads’
AND vss.VALUE > 5000000
AND ds.segment_type = vss.object_type
AND ds.segment_name = vss.object_name
AND ds.owner = vss.owner
AND ds.BUFFER_POOL = ‘DEFAULT’
ORDER BY VALUE
/
WITH segstat AS
(
SELECT object_type, object_name, owner, subobject_name,
MAX (CASE statistic_name
WHEN ‘physical reads’
THEN VALUE
END) pr,
MAX (CASE statistic_name
WHEN ‘physical reads direct’
THEN VALUE
END
) prd
FROM v$segment_statistics vss
WHERE statistic_name IN
(’physical […]

SELECT vss.owner, vss.object_name, vss.statistic_name, vss.VALUE,
ds.BYTES segsize, ds.BUFFER_POOL
FROM v$segment_statistics vss, dba_segments ds
WHERE vss.statistic_name = ‘physical reads’
AND vss.VALUE > 5000000
AND ds.segment_type = vss.object_type
AND ds.segment_name = vss.object_name
AND ds.owner = vss.owner
AND ds.BUFFER_POOL = ‘DEFAULT’
ORDER BY VALUE
/
WITH segstat AS
(
SELECT object_type, object_name, owner, subobject_name,
MAX (CASE statistic_name
WHEN ‘physical reads’
THEN VALUE
END) pr,
MAX (CASE statistic_name
WHEN ‘physical reads direct’
THEN VALUE
END
) prd
FROM […]

SQL>
set long 900000
select DBMS_METADATA.GET_DDL(’TABLESPACE’,’tablespace_name’) from dual;
select DBMS_METADATA.GET_DDL(’TABLE’,’table_name’) from dual;

set pagesize 85col username format a10
col osuser format a15
col sid format 9999
col serial format 99999
col type format a2
col request format 9
col lmode format 9
col lmode_desc format a16
col type_desc format a30 wrap
SELECT /*+ FIRST_ROWS ORDERED */ username,
s.osuser osuser , s.sid sid , s.serial# serial, l.lmode lmode ,
decode(L.LMODE,1,’No Lock’,
2,’Row Share’,
3,’Row Exclusive’,
4,’Share’,
5,’Share Row Exclusive’,
6,’Exclusive’,'NONE’) lmode_desc, l.type type ,
decode(l.type,
‘BL’,'Buffer […]