How to change crontab's default editor

I wanted to update a cron job in one of our server. As usual I entered crontab -e command. I was not able to edit it. The editor was not VI. Then I figured out it was using emacs editor.

To make vi default editor for crontab set EDITOR parameter to vi.

$ echo $EDITOR
/usr/bin/emacs

$ export EDITOR=vi

$ echo $EDITOR
vi

If still crontab uses emacs editor, then set VISUAL parameter also to vi:

$ echo $VISUAL
/usr/bin/emacs

$ export VISUAL=vi


$ echo $VISUAL
vi

ADRCI Command

ADRCI is a command line interface tool to view or manage diagnostic information of a database. Alter log, listener log, incidents etc can be viewed using this command.

List all the homes

$ adrci
adrci> show homes
ADR Homes:
diag/tnslsnr/test1/listener
diag/rdbms/db11gr2/db11gr2
diag/rdbms/ora11g/ora11g

To set a home

adrci> set home diag/rdbms/ora11g/ora11g

To view alert log

adrci> show alert

To view 'tail' of alert log file

adrci> set home db11g
adrci> show alter -tail
adrci> show alter -tail -f

To search (grep) alter log for errors

adrci> set home db11g
adrci> show alter -P "MESSAGE_TEXT LIKE '%ORA-01578%'" -term

To unset home

adrci> show base
adrci> set base /u10/app/oracle

Running adrci command in Non-interactive mode
Without getting into adrci prompt we can run adrci command from Unix prompt.

$ adrci exec ="show homes; show incidents"
$ adrci exec ="set home db11g; show alter -term"
$ adrci exec ="set home db11g; show alter -term"|tail
$ adrci exec ="set home db11g; show alter -term"|grep ORA

To view incident and problem

adrci> show homes
adrci> set home diag/rdbms/ora11g/ora11g
adrci> show incident

ADR Home = /home/oracle/app/oracle/diag/rdbms/ora11g/ora11g:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                      CREATE_TIME                           
-------------------- --------------------- ----------------------------------------
12161                ORA 25319              2011-05-26 17:10:12.536000 +05:30    
12121                ORA 25319              2011-05-26 17:10:32.408000 +05:30    
12122                ORA 25319              2011-05-26 17:10:50.947000 +05:30    


adrci> show incident -mode detail -p "incident_id=2601"

adrci> show problem

To create incident packages

adrci> show homes
adrci> set home diag/rdbms/ora11g/ora11g
adrci> show incidents
adrci> ips create package incident 14553

It creates a logical package. In the above example 14553 is an incident ID. We can also add an incident to an existing package.

adrci> ips add incident 14554 package 1

Generating package

adrci> ips generate package 1 in /u01/app/oracle

It creates a physical package. Compressed file will be in /u01/app/oracle.

ACCEPT and PROMPT commands

Usage of ACCEPT and PROMPT commands in SQL script.

SQL> PROMPT 'Please enter your name:'

SQL> ACCEPT name CHAR FORMAT a20 

Here is a script which prompts for username and accepts it.

It also creates user with that username and a table

 UNDEF username

ACCEPT username PROMPT 'Enter username:'

--Create user
CREATE USER &username IDENTIFIED BY passwd;
GRANT CREATE SESSION TO &username;
GRANT CREATE TABLE TO &username;
ALTER USER &username QUOTA 5M ON users;

-- Create table
CREATE TABLE &username..test AS SELECT * FROM dual;

SQL> @create.sql
Enter username:harish
old   1: CREATE USER &username IDENTIFIED BY passwd
new   1: CREATE USER harish IDENTIFIED BY passwd

User created.

old   1: GRANT CREATE SESSION TO &username
new   1: GRANT CREATE SESSION TO harish

Grant succeeded.

old   1: GRANT CREATE TABLE TO &username
new   1: GRANT CREATE TABLE TO harish

Grant succeeded.

old   1: ALTER USER &username QUOTA 5M ON users
new   1: ALTER USER harish QUOTA 5M ON users

User altered.

old   1: CREATE TABLE &username..test AS SELECT * FROM dual
new   1: CREATE TABLE harish.test AS SELECT * FROM dual

Table created.

Please not that I have used two dots in create table script after schema name.

Password reset

DBMS_RANDOM package can be used to generate an alphanumerical string that can be used to reset a password.

Example 1:

SELECT INITCAP(DBMS_RANDOM.STRING('X',8)) FROM DUAL;

NEW_PWD
-----------------------------------------------------------------
Xzcf996c

Example 2:

 
SELECT DBMS_RANDOM.STRING('A',5)||ROUND(DBMS_RANDOM.VALUE(100,999),0) FROM DUAL;

NEW_PWD
----------------------------------------------------------------------------------------------------------------------------
cPwER827

Standby Database Issue - Part 2

Issue 1:

 PING[ARC0]: Heartbeat failed to connect to standby  'stand'. Error is 1031        

Reason for the above error:
Standby database password file was missing.

Solution:
Copy the primary database password file to standby server.


Issue 2:

 PING[ARC0]: Heartbeat failed to connect to standby 'stand'. Error is 16047         

Reason for the above error:
For LOG_ARCHIVE_DEST_2 db_unique_name was wrongly specified.
LOG_ARCHIVE_DEST_2='service=standby valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=prim'

Solution:
Changed db_unique_name from 'prim' to 'stand'
LOG_ARCHIVE_DEST_2='service=standby valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=stand'



Issue 3: While creating spfile from pfile got the below error:

 ORA-00600: internal error code, arguments: [kspsetpao1]

Solution:
Metalink DOC: 444917.1

Creating spfile/pfile from memory

Now we can even create a spfile/pfile from memory. It is a new feature in the 11g. Prior to 11g, we were able to create spfile from pfile or pfile from spfile.


Syntax:

CREATE SPFILE FROM MEMORY;
CREATE PFILE FROM MEMORY;

Examples:

SYS@orcl11g>CREATE SPFILE='D:\backup\ORCL11G\spfile_bkp_18Jan2010.ora' FROM MEMORY;

File created.

SYS@orcl11g>CREATE PFILE='D:\backup\ORCL11G\pfile_bkp_18Jan2010.ora' FROM MEMORY;

File created.

SYS@orcl11g>

Read-Only Table

In Oracle 11g we can make a table read-only. It is a new feature in the Oracle 11g. Earlier we were able to make a non-system tablespace read-only.The Oracle 11g goes one step further and allows us to make a table read-only.

We have a new column called 'read_only' in dba_tables, user_tables and all_tables views which tells whether a table is read only or not.

We will not be able to execute DML statements like INSERT,UPDATE and DELETE on a read-only table. And all so we cannot add/remove columns.

Syntax:

  
ALTER TABLE table_name READ ONLY;
ALTER TABLE table_name READ WRITE;

Examples:

 
SCOTT@orcl11g>DROP TABLE test_tbl;

Table dropped.


SCOTT@orcl11g>CREATE TABLE test_tbl AS SELECT * FROM dual;

Table created.


SCOTT@orcl11g>SELECT * FROM test_tbl;

D
-
X


SCOTT@orcl11g>INSERT INTO test_tbl VALUES ('A');

1 row created.


SCOTT@orcl11g>ALTER TABLE test_tbl READ ONLY;

Table altered.


SCOTT@orcl11g>SELECT table_name,read_only FROM user_tables WHERE table_name='TEST_TBL';

TABLE_NAME                     READ_ONLY
------------------------------ ----------
TEST_TBL                       YES


SCOTT@orcl11g>INSERT INTO test_tbl VALUES ('A');
INSERT INTO test_tbl values ('A')
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."TEST_TBL"


SCOTT@orcl11g>UPDATE test_tbl SET dummy='Y' WHERE dummy='X';
UPDATE test_tbl SET dummy='Y' WHERE dummy='X'
       *
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."TEST_TBL"


SCOTT@orcl11g>DELETE FROM test_tbl;
DELETE FROM test_tbl
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."TEST_TBL"


SCOTT@orcl11g>ALTER TABLE test_tbl ADD (name varchar2(10));
ALTER TABLE test_tbl ADD (name varchar2(10))
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."TEST_TBL"


SCOTT@orcl11g>ALTER TABLE test_tbl DROP COLUMN name;
ALTER TABLE test_tbl DROP COLUMN name
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."TEST_TBL"


SCOTT@orcl11g>ALTER TABLE test_tbl READ WRITE;

Table altered.


SCOTT@orcl11g>INSERT INTO test_tbl VALUES('A');

1 row created.

SCOTT@orcl11g>

Case_sensitive passwords

In Oracle 11g passwords are case sensitive by default. We can enable or disable this feature by setting the SEC_CASE_SENSITIVE_LOGON parameter.
The SEC_CASE_SENSITIVE_LOGON parameter is a dynamic parameter. In the earlier version of the oracle like 10g & 9i passwords were not case sensitive.

 
SYS@orcl11g>show parameter SEC_CASE_SENSITIVE_LOGON

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE

Examples:

 
SYS@orcl11g>alter user scott identified by Tiger account unlock;

User altered.

SYS@orcl11g>show parameter SEC_CASE_SENSITIVE_LOGON

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE

SYS@orcl11g>exit

sqlplus scott/tiger

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 18 10:56:52 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:


sqlplus scott/Tiger

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 18 11:05:54 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SCOTT@orcl11g>

sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 18 10:57:30 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@orcl11g>alter system set SEC_CASE_SENSITIVE_LOGON=false scope=both;

System altered.

SYS@orcl11g>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

sqlplus scott/tiger

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 18 10:57:52 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SCOTT@orcl11g>

Minimum Dependent Object Recompilation After Online Table Redefinition

Starting from Oracle 11g, If you add a column to a table, then no need to recompile its dependent objects.

Let us see what happens to a view when we add a column to a table in Oracle 9i:

 C:\Documents and Settings\hnaik>sqlplus scott/tiger

SQL*Plus: Release 9.2.0.8.0 - Production on Mon Dec 21 04:15:41 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> create table t (n number,v varchar2(10));

Table created.

SQL> insert into t values (1,'H');

1 row created.

SQL> create view tv as select * from t;

View created.

SQL> select object_name,object_type,status from user_objects where object_name='TV';

OBJECT_NAM OBJECT_TYP STATUS
---------- ---------- -------
TV         VIEW       VALID

SQL> alter table t add (m number);

Table altered.

SQL> select object_name,object_type,status from user_objects where object_name='TV';

OBJECT_NAM OBJECT_TYP STATUS
---------- ---------- -------
TV         VIEW       INVALID

SQL>

Now let us see what happens to a view when we add a column to a table in Oracle 11g:

C:\Documents and Settings\hnaik>sqlplus scott/tiger

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Dec 21 15:54:25 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SCOTT@orcl11g>create table t (n number,v varchar2(10));

Table created.

SCOTT@orcl11g>insert into t values (1,'H');

1 row created.

SCOTT@orcl11g>create view tv as select * from t;

View created.

SCOTT@orcl11g>select object_name,object_type,status from user_objects where object_name='TV';

OBJECT_NAM OBJECT_TYP STATUS
---------- ---------- -------
TV         VIEW       VALID

SCOTT@orcl11g>alter table t add (m number);

Table altered.

SCOTT@orcl11g>select object_name,object_type,status from user_objects where object_name='TV';

OBJECT_NAM OBJECT_TYP STATUS
---------- ---------- -------
TV         VIEW       VALID

SCOTT@orcl11g>

Reference:
Oracle® Database New Features Guide 11g Release 1 (11.1)

Invisible Index

Invisible index is a new feature of Oracle 11g. If we create an invisible index Oracle optimizer will not use it. This can be used to test a query without effecting the application. We can also make an index invisible/visible by using ALTER INDEX index_name [visible/invisible].

Syntax:

CREATE INDEX index_name ON table_name(column_name) INVISIBLE;
ALTER INDEX index­_name INVISIBLE;
ALTER INDEX index­_name VISIBLE;

Examples:

SYSTEM@orcl11g>show parameter optimizer_use_invisible_indexes 
NAME TYPE VALUE

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

optimizer_use_invisible_indexes boolean FALSE



SYSTEM@orcl11g>desc test

Name Null? Type

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

N NUMBER

NAME VARCHAR2(10)



SYSTEM@orcl11g>create index idx1 on test(n) invisible;

Index created.



SYSTEM@orcl11g>select owner,index_name,visibility from dba_indexes

2 where visibility = 'INVISIBLE';



OWNER INDEX_NAME VISIBILIT

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

SYSTEM IDX1 INVISIBLE



SYSTEM@orcl11g>select * from test where n=5;



N NAME

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

5 H



Execution Plan

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

Plan hash value: 1357081020



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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| TEST | 1 | 20 | 3 (0)| 00:00:01 |

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



SYSTEM@orcl11g>alter index idx1 visible;

Index altered.



SYSTEM@orcl11g>select * from test where n=5;



N NAME

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

5 H



Execution Plan

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

Plan hash value: 1175071277



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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 20 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX1 | 1 | | 1 (0)| 00:00:01 |

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

Use 1: Adding an index

You think adding an index will improve a query but you are not sure. Just create an invisible index. Test the query and decide.

Use 2: Dropping an index

You want to drop an index but don’t know how it will affect the application. Make that index invisible and analyze the performance. If it is not affecting the performance of the application, you can drop it.