Oracle Trouble Shooter
Querying Datafiles, ASM and Segments in Oracle11g (including tablespaces) in Oracle11g, 10g and 9i. This is stuff I have online on my main website and in books, but I’ve tweaked it a bit over the years. ASM: set wrap off lines 132 pages 5000; col name format a30 select name,state,total_mb,free_mb from v$asm_diskgroup; select name,state ,round(sum(total_mb/1024),0) …
Continue reading “Querying Datafiles, ASM and Segments in Oracle11g”
Just the partitions: set wrap off linesize 132 pages 5000 col tbs format a10 column tab format a25 col owner format a10 column part format a25 column owner format a10 select t.table_owner “Owner” ,t.tablespace_name tbs ,t.table_name as “Tab” ,t.PARTITION_NAME “Part” ,t.num_rows ,t.last_analyzed from dba_TAB_PARTITIONS t –where t.table_owner=’…’ order by t.table_owner,t.table_name,t.PARTITION_NAME; Add partition keys: set wrap …
Continue reading “Command Line Partitions and Subpartitions”
set lines 148 wrap off; COL Query FORMAT a48; COL Pos FORMAT 90; COL Cost FORMAT 999990; COL Rows FORMAT 999999990; COL Bytes FORMAT 999999990; COL Sort FORMAT 999999990; COL IO FORMAT 99990; COL CPU FORMAT 9999999990; set lines 132 wrap off; SELECT TRIM(LEVEL)||’. ‘||LPAD (‘ ‘, LEVEL – 1)||operation||’ ‘||options||’ on ‘||object_name||’ ‘||other_tag “Query” …