Oracle : ASM Important Queries

by Vinod SiramORACLE ASM

Rebalance status : column “Diskgroup” format A30 column “Imbalance” format 99.9 Heading “Percent|Imbalance” column “Variance” format 99.9 Heading “Percent|Disk Size|Variance” column “MinFree” format 999.99 heading “Minimum|Percent|Free” column “DiskCnt” format 9999 Heading “Disk|Count” column “Type” format A10 Heading “Diskgroup|Redundancy” SELECT g.name “Diskgroup”, 100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) “Imbalance”,100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) “Variance”, 100*(min(d.free_mb/d.total_mb))… Read More

Oracle – Checking Fragmentation in Database

by Vinod SiramOracle DB Admin

Fragmented_Tables_List in HTML FORMAT : col spoolname new_value spoolname select ‘Fragmented_tables’||to_char(sysdate, ‘YYYYMONDD’)||’.html’ spoolname from dual; spool ‘&spoolname’ set pagesize 200 set markup html on WITH STAT_SPACE AS( SELECT OWNER, TABLE_NAME, NUM_ROWS, AVG_ROW_LEN, NUM_ROWS*AVG_ROW_LEN/1024/1024/1024 tbl_STAT_SPACE FROM DBA_TABLES WHERE OWNER=’SCOTT’), SEG_SPACE AS ( SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE,… Read More

Oracle : Change AWR Retention

by Vinod SiramOracle DB Admin

Check existing retention and current snapshot interval. set linesize 300 select extract( day from snap_interval) *24*60+extract( hour from snap_interval) *60+extract( minute from snap_interval ) snapshot_interval, extract( day from retention) *24*60+extract( hour from retention) *60+extract( minute from retention ) retention_interval, topnsql from dba_hist_wr_control; Below query is… Read More

Oracle Metadata information

by Vinod SiramOracle DB Admin

Table Metadata Definition : set pagesize 0 set long 90000 select dbms_metadata.get_ddl(‘TABLE’,’&TABLE_NAME’,’&OWNER’) FROM DUAL; Procedure Metadata Definition : set pagesize 0 set long 90000 select dbms_metadata.get_ddl(‘PROCEDURE’,’&PROCEDURE_NAME’,’&OWNER’) FROM DUAL; Tablespace Metadata Definition : set pagesize 0 set long 90000 select dbms_metadata.get_ddl(‘TABLESPACE’,’&TABLESPACE_NAME’) FROM DUAL; Index Metadata Definition… Read More