Vinod Siram – Page 13

Oracle : ASM Important Queries

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

7 years ago 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 Dataguard standby sync issues

7 years ago by Vinod SiramOracle Dataguard

Below are different kind of problems where you have recovery issues in standby. Before you take any decision check with your colleagues. 1) Check on source : SQL> select dest_id,error from v$archive_dest_status; 2) To know the redo apply rate : set linesize 500col item format… Read More

Oracle RAC : VD and OCR migration online

7 years ago by Vinod Siram11gR2 RAC

Dear Readers, In this article we will see VD and OCR migration online. 1) Check existing setup by running below commands : +ASM1@cloud-server-1:/opt/oracle> crsctl query css votedisk 2) Check the OCR/VD backup : 3) Ask SA to take new backup using… Read More

Oracle : Change AWR Retention

8 years ago 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 Delete Set of rows in a table

8 years ago by Vinod SiramOracle Adhoc SQL Scripts

DECLAREcount1 NUMBER := 0;total NUMBER := 0;CURSOR del_record_cur ISSELECT rowid FROM scott.emp WHERE empno<=20000;BEGINFOR rec IN del_record_cur LOOPDELETE FROM scott.emp WHERE rowid = rec.rowid;total := total + 1;count1 := count1 + 1;IF (count1 >= 1000) THEN COMMIT;count1 := 0;END IF;END LOOP;COMMIT;DBMS_OUTPUT.PUT_LINE(‘Deleted ‘ || total ||… Read More

Oracle – How to Gather Stats for Table where stats of that table is locked.

8 years ago by Vinod SiramOracle DB Admin

Check Stats of Table : –example : ‘DEPT’,’EMP’,’SALGRADE’ set linesize 300 set pagesize 100 col owner for a15 col LAST_ANALYZED for a30 col table_name for a40 select OWNER,TABLE_NAME , NUM_ROWS , BLOCKS , EMPTY_BLOCKS , CHAIN_CNT, AVG_ROW_LEN CACHE, to_char(LAST_ANALYZED,’DD-MON-YYYY HH24:MI:SS’) LAST_ANALYZED from dba_tables where table_name… Read More

Oracle Metadata information

8 years ago 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

Oracle : Generating Million Random Records for Temporary Table.

8 years ago by Vinod SiramOracle Adhoc SQL Scripts

Step 1 : Create Temporary Table : CREATE TABLE unpar_table ( a NUMBER, y number, name VARCHAR2(100), date_used date); Step 2 : load table with 1,000,000 rows begin for i in 1 .. 1000 loop for j in 1 .. 1000 loop insert into unpar_table values… Read More

Generating Oracle AWR reports on RAC and Non RAC DB’s

8 years ago by Vinod SiramOracle DB Admin

AWR Difference Report for NON RAC: @?/rdbms/admin/awrddrpt.sql AWR Difference Report for RAC : @?/rdbms/admin/awrddrpi.sql AWR Report for Non RAC : @?/rdbms/admin/awrrpt AWR Report for RAC : @?/rdbms/admin/awrrpti Global AWR RAC Report : @?/rdbms/admin/awrgrpt.sql ASH Report : @?/rdbms/admin/ashrpt.sql GLOBAL AWR RAC Differenct Report : @?/rdbms/admin/awrgdrpt.sql AWR… Read More