How to find password change date for user
Check user$ table for same.
Category: Oracle
switchover for primary database
ALTER DATABASE commit TO switchover TO physical PRIMARY WITH session shutdown nowait;
Physical Standby switchover with session active
In order to perform a switchover all sessions to the database need to be disconnected. In version 901 this was a manual process. In version 9.2.0 this process has been automated with the “with session shutdown” clause that has been added to the alter database commit to switchover command. If SWITCHOVER_STATUS returns SESSIONS ACTIVE then…
Read More “Physical Standby switchover with session active” »
Monitor Long Running Job
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) “% COMPLETE” FROM V$SESSION_LONGOPS WHERE OPNAME LIKE ‘RMAN%’ AND OPNAME NOT LIKE ‘%aggregate%’ AND TOTALWORK != 0 AND SOFAR TOTALWORK /
Find_table_size.sql
set lines 120 pages 500 col owner format a30 col segment_name format a30 col tab_sum format 999999999999999 col indx_sum format 999999999999999 col MB format 999999999 accept pvm_owner prompt ‘Enter Owner Name -> ‘ accept pvm_table prompt ‘Enter Table Name -> ‘ select tab.owner, tab.segment_name, tab.tablespace_name, (tab_sum+indx_sum)/(1024*1024) MB from (select p.owner, p.segment_name, p.tablespace_name, sum(p.bytes) tab_sum from…
Database link password in user_db_links
Problem Description: ~~~~~~~~~~~~~~~~~~~~ Database link passwords are stored as plaintext. A database link is a mechanism used to provide a method of transparently accessing one server from another. When creating a database link, a user name and password of the account on the remote server can be specified. Creating the database link without credentials works…
find_err.sql for finding errors from dba_errors.
set lines 120 pages 200 col line format 9999 col position format 999 col text format a80 select line, position, text from dba_errors where owner= upper(‘&owner_name’) and name = upper(‘&obj_name’) order by SEQUENCE;
Oracle 10g for solaris 10
Oracle 10g for Solaris 10 I got error message “Checking operating system version: must be 5.8 or 5.9. Actual 5.10 Failed
Error Handling in Proc
There is a standard way of trapping any SQL or database errors in Pro*C.This is done using EXEC SQL WHENEVER SQLERROR DO ; just ensure that the above statement goes before any sql statements whenever an error is encountered , the control goes to the function function_name in the body of the function_name , just…
Proc code
1. EXEC SQL COMMIT WORK RELEASE; (When you want to commit transactions and disconnect;similar to EXIT in SQL*PLUS) 2. EXEC SQL ROLLBACK WORK RELEASE; (When you want to rollback transactions and disconnect;similar to QUIT in SQL*PLUS)