Tuesday, September 8, 2009

How can I change password of databse user 'DBSNMP'?

  1. Remove all Jobs and Events currently registered against this database.
  2. Stop the Intelligent Agent

    Oracle7 / 8i % lsnrctl dbsnmp_stop
    Oracle9i / 10g / 11g % agentctl stop

  3. Edit the $ORACLE_HOME/network/admin/snmp_rw.ora file. Add the following parameter:
    SNMP.CONNECT..NAME=
    SNMP.CONNECT..PASSWORD=
    The variable is the exact listing of the database name as it appears in the snmp_ro.ora file. If is the default (DBSNMP), there is no need to specify the user here. Only the password is required.
    On UNIX, set the following permission on the "SNMP_RW.ORA" file:%chmod 600 snmp_rw.ora
  4. Change the DBSNMP password on the database. You can use either Security Manager, Sqlplus, or Server Manager. If you use SQLPlus or Server Manager, you can issue the following command:

    SQL> alter user "dbsnmp" identified by "newpassword";

  5. Stop and restart the Intelligent Agent.

What does I can do after logging in user DBSNMP in Oracle?

The Oracle Intelligent Agent requires a database logon for each SID that it manages.

DATABASE USERNAME : DBSNMP
DEFAULT PASSWORD : DBSNMP

The user name and/or password should be changed from the default but you will need to make a few additional modifications. It Supports Oracle SNMP (Simple Network Management Protocol).

Friday, June 26, 2009

ORA-01536: space quota exceeded for tablespace 'USERS'. Whats Soluition of this?

ORA-01536: space quota exceeded for table space "USER".

"DBA removed the user from the RESOURCE role As effect of it, User losts the ability to create tables,even though the CONNECT role explicitely allows CREATE TABLE."


This Error is due to User Permissions.
My User can update a table having CONNECT role granted to it. But, It can't write due to user quota specified by DBA. I thought that using the "default" profile would keep this sort of problem. As a Resolution of this Problem -"
One Question with me is Why it showing quota error ! why not it is a user permissions related error?

Monday, March 9, 2009

Oracle Errors while coding of PL/SQL Blocks

ORA-00001 Unique constraint violated. (Invalid data has been rejected)
Solution: Check PL/SQL Code.You are inserting data that already exists in database.

ORA-00600 Internal error (contact support)
Solution : Check the connection with Oracle Database.

ORA-03113 End-of-file on communication channel (Network connection lost)
Solution : Reconnect to Oracle

ORA-03114 Not connected to ORACLE
Solution : Reconnect to Oracle

ORA-00942 Table or view does not exist
Solution : Table or view in which you are entering or fetching data from table/view is not exists in database.

ORA-01017 Invalid User name/Password
Solution : Your are trying to connect with database with wrong password or wrong username.

ORA-01031 Insufficient privileges
Solution : You are currently accessing data from which table or view or any object not granted to the user through which you are currently logged in.

ORA-01034 Oracle not available (the database is down)
Solution : Database connection is not available. Please restart the database or database is in Mount Stage.

ORA-01403 No data found
Solution : While fetching data, No data found from the written Query.

Oracle Date Conversion function - TO_DATE()

TO_DATE in Oracle pl/sql is to convert char data type to a value of DATE data type.

TO_DATE( String, format, nls_param )

String : is the string to convert to date
format : is the format that will be used to convert string to a date.
nls_param : is the nls_languageused to convert string to a date.

Examples:
SQL> select to_date('APR/03/2009' ,'MON-DD-YYYY HH24:MI:SS') DATE from dual;
SQL> select to_date('20080312','YYYYMMDD') from dual;
SQL> select to_date('20080312','YYYYDDMM') from dual;
SQL> select to_date('2006/11/14 18', 'yyyy/mm/dd hh24') from dual;

Thursday, February 19, 2009

How to Kill Oracle Sessions?

There are two main ways to Kill Oracle User Sessions.
1. Directly the session of User.

ALTER SYSTEM KILL SESSION;

2. Identifying the User Process and kill it.

SELECT b.object_name,c.SERIAL#,a.*
FROM V$LOCKED_OBJECT a,DBA_OBJECTS b,v$session c
WHERE a.object_id = b.object_id
AND a.SESSION_ID = c.SID
order by a.SESSION_ID;


ALTER SYSTEM KILL SESSION 'sid,serial#'

In these cases the session will be "marked for kill". It will then be killed as soon as possible.

Friday, February 6, 2009

Oracle Database Architecture

The Oracle instance have the background processes, memory structures, and disk resources that comprise the Oracle instance, and also of the methods in which they act together to allow users to access information. Several memory structures exist on the Oracle database to improve performance on various areas of the database. The memory structures of an Oracle instance include the System Global Area (SGA) and the Program Global Area (PGA).

System Global Area (SGA)

The SGA, in turn, consists of a minimum of three components: the data block buffer cache, the shared pool, and the redo log buffer. Corresponding to several of these memory areas are certain disk resources. These disk resources are divided into two categories: physical resources and logical resources.

Program Global Area (PGA)

The PGA is an area in memory that helps user processes execute, such as bind variable information, sort areas, and other aspects of cursor handling. From the prior discussion of the shared pool, the DBA should know that the database already stores parse trees for recently executed SQL statements in a shared area called the library cache. So, why do the users need their own area to execute? The reason users need their own area in memory to execute is that, even though the parse information for SQL or PL/SQL may already be available, the values that the user wants to execute the search or update upon cannot be shared. The PGA is used to store real values in place of bind variables for executing SQL statements.

Background Processes

In any Oracle instance, there will be user processes accessing information. . Likewise, the Oracle instance will be doing some things behind the scenes, using background processes. There are several background processes in the Oracle instance. It was mentioned in the discussion of the SGA that no user process ever interfaces directly with I/O. This setup is allowed because the Oracle instance has its own background processes that handle everything from writing changed data blocks onto disk to securing locks on remote databases for record changes in situations where the Oracle instance is set up to run in a distributed environment. The following list presents each background process and its role in the Oracle instance.