Hi,

Another  benefit of SQL environment variable, Today I would like to tell our experience about this variable. few days back for a deployment one of my friend faced this issue,  though it is a simple matter it took more than an hour to find and fix.

The issue here was before deploying the scripts we set our environment variable scripts in which it is SCAN=Off which suppresses the processing of substitution variables. SCAN=on is default one.

From Doc
SET SCAN {ON|OFF} (obsolete)

Controls scanning for the presence of substitution variables and parameters. OFF suppresses processing of substitution variables and parameters; ON enables normal processing.

ON functions in the same manner as SET DEFINE ON.

Here is our experiment

SQL> sho scan
scan ON
SQL> conn a/a
Connected.
SQL> define schema_name=a
SQL> update &schema_name..t1 set rno=3;
old   1: update &schema_name..t1 set rno=3
new   1: update a.t1 set rno=3

700 rows updated.

SQL> set scan off
SQL> update &schema_name..t1 set rno=4;

SP2-0552: Bind variable "SCHEMA_NAME" not declared.

SQL> set scan on
SQL> update &schema_name..t1 set rno=4;
old   1: update &schema_name..t1 set rno=4
new   1: update a.t1 set rno=4

700 rows updated.

So whenever you get this error first we can check this variable and proceed with other things any..,

Happy Reading :) ..

Archive log Destination change

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            C:\APP\ATHIL\ADMIN\ORCL\arch
...

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=C:\Athil\Arch\ORCL' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=C:\Athil\Arch\ORCL' scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16019: cannot use LOG_ARCHIVE_DEST_1 with LOG_ARCHIVE_DEST or
LOG_ARCHIVE_DUPLEX_DEST


SQL> sho parameter arc

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                     string      C:\APP\ATHIL\ADMIN\ORCL\arch

SQL> alter system set LOG_ARCHIVE_DEST='LOCATION=C:\Athil\Arch\ORCL' scope=both
  2  ;
alter system set LOG_ARCHIVE_DEST='LOCATION=C:\Athil\Arch\ORCL' scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16032: parameter LOG_ARCHIVE_DEST destination string cannot be translated
ORA-07286: sksagdi: cannot obtain device information.
Linux-x86_64 Error: 2: No such file or directory

After checking in Doc 

LOG_ARCHIVE_DEST

Property	Description
Parameter type	String
Syntax	LOG_ARCHIVE_DEST = filespec

ALTER SYSTEM SET LOG_ARCHIVE_DEST = filespec, where filespec is the new archive destination.

NO "LOCATION " for LOG_ARCHIVE_DEST

SQL> alter system set LOG_ARCHIVE_DEST='C:\Athil\Arch\ORCL' scope=both;

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            C:\Athil\Arch\ORCL
...

SQL>  alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> host dir C:\Athil\Arch\ORCL

06/08/2012  10:45 AM        38,752,256 ORCL_1_137_780346113.ARC
06/08/2012  10:45 AM             1,024 ORCL_1_138_780346113.ARC
06/08/2012  10:45 AM             3,072 ORCL_1_139_780346113.ARC

alter database link .. 

Instead of DB link recreation after password change of remote schema from 11gr2 we can use “alter database link”

alter database link SCOTT_DBLINK connect to scott identified by abc;

Check the below link for detailed info.

http://aprakash.wordpress.com/2012/05/11/change-in-database-link-11gr2/

To know last utlrp run,

we can find an entry from alert log as below

Tue May  8 19:27:43 2012
SERVER COMPONENT id=UTLRP_BGN: timestamp=2012-05-08 19:27:43
Tue May  8 19:27:55 2012
SERVER COMPONENT id=UTLRP_END: timestamp=2012-05-08 19:27:55

Top 15 sqls from cursor and Workload repository

from Oracle 10g release there is a script named SQLTRPT.SQL at ORACLE_HOME/rdbms/admin
which lists top 15 sqls from cache cursor and workload repository and its Tuning recommendations.

SQL>  col SQL_TEXT_FRAGMENT for a67
SQL> set pages 999
SQL> @sqltrpt

--DUE TO CLUMSY FORMAT here removed from display--
 
Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: b6usrg82hwsa3

Sql Id specified: b6usrg82hwsa3

Tune the sql
~~~~~~~~~~~~

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : TASK_331359
Tuning Task Owner                 : SYS
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 1800
Completion Status                 : COMPLETED
Started at                        : 05/08/2012 17:54:55
Completed at                      : 05/08/2012 17:54:55

-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID     : b6usrg82hwsa3
SQL Text   : call dbms_stats.gather_database_stats_job_proc (  )

-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- Type of SQL statement not supported.

-------------------------------------------------------------------------------