Friday, April 27, 2012

Analyze Process and Lock

›
Analyze table estimate or compute statistics will acquire an exclusive lock on the library cache object, preventing any ddl changes, howeve...

CURSOR_SHARING

›
CURSOR_SHARING The default value of parameter Cursor_sharing is Exact. Who changed it? CURSOR_SHARING is a parameter which decides whe...

trigger to fire under certain conditions

›
Issue I do not want a trigger to fire under certain conditions like running on some stored procedure. I cannot disable the trigger. For e...
Tuesday, April 24, 2012

Access Network Services (UTL_INADDR,UTL_TCP,UTL_HTTP, UTL_SMTP, UTL_MAIL) in Oracle 11g ( ora 24247 network access denied)

›
From 11g the built-in packages which access the network resources e.g. UTL_HTTP, UTL_SMTP, UTL_MAIL etc. now requires an access control list...
Monday, April 23, 2012

Auto-Changing SQL Prompt

›
You have to insert the following line of code in glogin.sql which is usually found in $ORACLE_HOME/sqlplus/admin set termout off set ech...

Connect as an Oracle User Without Knowing the Password

›
SQL> alter user ldbo grant connect through ksh; User altered. SQL> connect n/ksh$1#@apx1112srv; Connected. SQL> show user US...

exp-00091 exporting questionable statistics

›
Use statistics=NONE in exp statement

ORA-01092 ORACLE instance terminated. Disconnection forced ORA-00704 bootstrap process failure

›
shut immediate startup upgrade @D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catupgrd.sql @D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catpro...

ORA-00600: internal error code, arguments: [kjhn_post_ha_alert0-862]

›
Fri Apr 20 12:24:30 2012 Errors in file d:\oracle\product\10.2.0\admin\tss1112\bdump\tss5_mmon_2416.trc: ORA-00600: internal error code, a...

Job Scheduler for RMAN backup

›
1) RMAN TARGET SYS/.....@KSH1213SRV CONFIGURE RETENTION POLICY TO REDUNDANCY 1; CONFIGURE BACKUP OPTIMIZATION ON; CONFIGURE CONTROLFILE ...
Friday, April 20, 2012

Oracle 10g Scheduler Job Email Notification Wrapped

›
------------------------job_email_notification.sql------------------------------ prompt Enter an outgoing e-mail SMTP server host define e...

Oracle 10g Scheduler Job Email Notification

›
----------------------------------------------------start job_notification.sql--------------------- -- Run this script as SYS with 2 para...

Oracle Job Scheduler for Old Analyze Method / Full Analyze

›
create or replace procedure AnalyzeFull as BEGIN  FOR cur_rec IN (SELECT distinct table_name                   FROM   dba_tables) LOOP  ...

Oracle Job Scheduler for Rename Export dump file / OS files

›
UTL_FILE.FRENAME ( location  IN VARCHAR2,    filename  IN VARCHAR2,    dest_dir  IN VARCHAR2,    dest_file IN VARCHAR2,    overwrite IN ...

Oracle Job Scheduler for Remove export files / OS files

›
select * from dba_directories; exec utl_file.fremove('MYDIRECTORY', 'test.txt'); EXEC UTL_FILE.FREMOVE ('EXPORT_AUTO...

Oracle Job Scheduler Archivelog Deletion

›
CREATE OR REPLACE PROCEDURE archive_dir_setup AS archive_dir VARCHAR2(40); BEGIN EXECUTE IMMEDIATE 'SELECT DESTINATION '|| ' FRO...
Tuesday, April 17, 2012

Difference Oracle on Windows & Unix

›
Advantages of Oracle UNIX: Significant performance improvement Provides High Availability Contains in-depth system utilities and open-source...
Monday, April 16, 2012

Compile Invalid Objects

›
SET HEADING OFF spool c:\temp\invalid.sql ; select OBJECT_NAME from dba_objects where STATUS='INVALID'; select 'ALTER ' || O...

Job Scheduler for EXPDP Dump

›
CREATE DIRECTORY DPUMP_DIR1 AS 'f:\expdp1213'; GRANT read, write ON DIRECTORY EXPORT_AUTO TO ldbo; begin dbms_scheduler.create_job ...

Job Scheduler for EXP Dump

›
begin dbms_scheduler.create_job (job_name => 'exp1213', job_type => 'EXECUTABLE', job_action => 'exp...