Modify ASM user password
LIST ASM USERS ASMCMD> lspwusr Username sysdba sysoper sysasm SYS TRUE TRUE TRUE CRSUSER__ASM_001 TRUE FALSE TRUE ASMSNMP TRUE FALSE FALSE MODIFY USER PASSWORD ASMCMD> orapwusr –modify asmsnmp Enter password:…
Read MoreCopy ASM file to remote ASM instance
ASM file can be copied to remote asm instance(diskgroup) using asmcmd command. SYNTAX asmcmd> cp – -port asm_port file_name remote_asm_user/remote_asm_pwd@remote_host:Instancce_name:TARGET ASM_PATH ASMCMD> cp –port 1521 s_srv_new21.dbf sys/oracle@172.20.17.69.+ASM1:+ARCL/s_srv_new21.dbf
Read MoreSQL’s doing full table scan
select sql_id,object_owner,object_name from V$SQL_PLAN where operation=’TABLE ACCESS’ and options=’FULL’ and object_owner not in (‘SYS’,’SYSTEM’,’DBSNMP’);
Read MoreEstimate space required for index creation
SET SERVEROUTPUT ON DECLARE v_used_bytes NUMBER(10); v_Allocated_Bytes NUMBER(10); BEGIN DBMS_SPACE.CREATE_INDEX_COST ( ‘ create index PROD.INDEX1 on PROD.EMP(EMPNO)’, v_used_Bytes, v_Allocated_Bytes ); DBMS_OUTPUT.PUT_LINE(‘Used Bytes MB: ‘ || round(v_used_Bytes/1024/1024)); DBMS_OUTPUT.PUT_LINE(‘Allocated Bytes MB: ‘…
Read MoreSession count Alert ( get mail more than 90 sessions were opened )
Description: Script for more Session count Alert ( get mail more than 90 sessions were opened ) Steps: select to_char(sysdate,’dd-mm-yy:HH:MM:SS’) from dual; set feedback off set serveroutput on — spool…
Read MoreGlobal Temporary Table ( GTT) – key facts
The data in a GTT is written to the temporary tablespace, which is not directly protected by redo, so using a GTT improves performance by reducing redo generation. Unfortunately, prior…
Read MoreOracle 12c SQL Plan Directives – Disable | Enable | Use As Hint
What is it? In previous releases the database stored compilation and execution statistics in a shared sql area which is non persistent. Starting in 12c the database can use a…
Read MoreORA-20005: object statistics are locked (stattype = ALL) – Solution
During tuning a query, I found one table has stale statistics. While running gather stats for that table, got below error. Let me demonstrate with a demo table: SQL> execute…
Read MoreIgnore Duplicate Rows (ORA-00001) with the ignore_row_on_dupkey_index Hint
The simplest method is to add a hint to the query to overcome ORA-00001 Added in 11.2, the ignore_row_on_dupkey_index hint silently ignores duplicate values: insert /*+ ignore_row_on_dupkey_index ( acct (…
Read MoreCreate a Virtual Column:
A virtual column applies a function to a column in the table. The database only computes this at runtime. The value is not stored in the table. So instead of…
Read More