Ports used by Oracle Software
files are $ORACLE_HOME/install/setupinfo.txt $ORACLE_HOME/install/portlist.ini
Running some SQL on multiple databases connecting using monitoring userid and password
==>cat tns_entry_list DB1 Db2 Db3 db4 ==>cat find_max_dbfs.ksh #!/bin/ksh for tns_entry in `cat tns_entry_list` do echo “–” echo “——— TNS ENTRY ${tns_entry} ———-” /oracle/app/oracle/product/9.2.0.8/bin/sqlplus monioring_user/monitoring_password@${tns_entry} cat find_max_dbfs.sql set lines 120 pages 200 col “-?db_files_value” format a20 col “MAXDATAFILES” format 9999999999999999 col “USED MAXDATAFILES” format 99999999999999 select ‘-?’ || x.value as “-?db_files_value”, y.records_total “MAXDATAFILES”, y.records_used “USED…
Read More “Running some SQL on multiple databases connecting using monitoring userid and password” »
All Hints for Oracle Databases
SQL Statement Hints – A Summary See Note 35934.1 for CBO issues. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ General: – Syntax: /*+ HINT HINT … */ (In PLSQL the space between the ‘+’ and the first letter of the hint is vital as otherwise the hint may be ignored so /*+ ALL_ROWS */ is fine but /*+ALL_ROWS */ will cause…
DBMS_JOB all example
Job Execution Interval: ‘SYSDATE + 7’ Exactly seven days from the last execution ‘SYSDATE + 1/48’ Every half hour ‘NEXT_DAY(TRUNC(SYSDATE), ”MONDAY”) + 15/24’ Every Monday at 3PM ‘NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, ”Q”), 3), ”THURSDAY”)’ First Thursday of each quarter Remobe the job: BEGIN DBMS_JOB.REMOVE(14144); END; / Change the job: In this example, job number 14144 is altered to…
dbms_job.submit example
VARIABLE jobno NUMBER BEGIN DBMS_JOB.SUBMIT(:jobno, ‘DBMS_DDL.ANALYZE_OBJECT(”TABLE”, ”HR”, ”EMPLOYEES”, ”ESTIMATE”, NULL, 50);’, SYSDATE, ‘SYSDATE + 1’); COMMIT; END; / PRINT jobno JOBNO ———- 14144
Find_stale_dr.sql finding stale physical DR..
col name format a50 col UNRECOVERABLE_CHANGE# format 999999999999999999999 col dt1 format a30 set lines 120 pages 200 SELECT NAME, UNRECOVERABLE_CHANGE#, TO_CHAR (UNRECOVERABLE_TIME,’DD-MON-YYYY HH:MI:SS’) dt1 FROM V$DATAFILE WHERE UNRECOVERABLE_CHANGE# != 0;
Find Stale DR Physical Standby
col name format a50 col UNRECOVERABLE_CHANGE# format 999999999999999999999 col dt1 format a30 set lines 120 pages 200 SELECT NAME, UNRECOVERABLE_CHANGE#, TO_CHAR (UNRECOVERABLE_TIME,’DD-MON-YYYY HH:MI:SS’) dt1 FROM V$DATAFILE WHERE UNRECOVERABLE_CHANGE# != 0;
Oracle 11g RAC on OEL 5 and Vmware 2
http://www.oracle-base.com/articles/11g/OracleDB11gR1RACInstallationOnOEL5UsingVMwareServer2.php
Sequence Resetting
—————— CS Sequences maintenance——————- column last_number format 9999999999999999 select * from user_sequences where sequence_name in (‘UOC_ORDER_SEQ_ID’); select 2300000000 – CurrVAl – 20 LAST_NUMBER from dual; alter sequence UOC_ORDER_SEQ_ID increment by NOCACHE; Increment Once. select UOC_ORDER_SEQ_ID.nextval from dual; Confirm Value comes to 2300000000. After that alter sequence UOC_ORDER_SEQ_ID increment by 1 NOCACHE; alter sequence UOC_ORDER_SEQ_ID maxvalue…
DBMS_SQL for alter session.
SQL> SQL> CREATE OR REPLACE PROCEDURE AlterSession( 2 p_SessionString IN VARCHAR2) AS 3 4 v_CursorID INTEGER; 5 v_Dummy INTEGER; 6 BEGIN 7 v_CursorID := DBMS_SQL.OPEN_CURSOR; 8 9 DBMS_SQL.PARSE(v_CursorID, p_SessionString, DBMS_SQL.NATIVE); 10 v_Dummy := DBMS_SQL.EXECUTE(v_CursorID); 11 END AlterSession; 12 / Procedure created. SQL> SQL> column sysdate format a30 SQL> SELECT SYSDATE FROM dual; SYSDATE —————————— Jun…