Tuesday, December 15, 2009

What is the difference between table & View?

View is a virtual table made of one or tables of the database using complex/simple join logic. The view has some useful purposes. Some are as under:

1. Simplification of database:

A schema may have hundreds of tables. Applications can be simplified if they can access predefined views which embed complex join logic.

2. Easy Maintenance:

Views can contain complex column expressions that are transparent in the views. Changes to these expressions need only be made in the view.

3. Change management:

Views can reduce maintenance to the application as they can hide schema changes that affect the view's query.

4. Security purpose:

Views can embed selection logic and present a subset of a table's content. You can create additional schema that contains just views of the main schema, limiting what may be accessed. Different user groups enter through one of these other schema, each group having a different view of the overall database.

Monday, December 14, 2009

How to free up database space in Tablespace in Oracle?

Oracle uses the high-water mark after deleted rows in database, you can free up this space at the table level with following methods.

1 . using export/import of table -

For a complete restructuring of table and space freeing up export/import of a table allows us to restructure our files and release the lost space.

2. using dbms_redefinition -

This procedure will reorganize a table while it remains online for updates.

3. using alter table tmp shrink -

If you are using Oracle 10g and later, you could use
alter table tmp shrink space compact.

4. using coalesing table -

It removes space above the high-water mark, It puts together the uncontiguous fragmented extents.

The honeycomb fragmentation and Swiss Cheese fragmentation are mainly occured in oracle. When the free extents are side by side, its in honeycomb fragmentation, and when the extents are separated by live segments, its in Swiss Cheese fragmentation.

alter table tmp coalesce;

5. using deallocate unused space -

Oracle uses it to explicitly deallocate unused space at the end of a segment and makes that space available for other segments within the tablespace.

alter table tmp deallocate unused space;

Oracle deallocates unused space beginning from the end of the objects and moving downwards toward the beginning of the object, continuing down until it reaches the high water mark (HWM). For indexes, deallocate unused space coalesces all leaf blocks within same branch of b-tree, and quickly frees up index leaf blocks for use.

How to reclaim or release the size of fragmented data files of tablespaces?

All Database objects like, tables,triggers,procedures,packages,functions,etc. are resided in tablespaces will naturally fragment as a function of update activity and Oracle has many methods for reclaiming disk space.

The Segment Advisor which will recommend us when database objects will get benefit from a reorganization to free up disk space.

Oracle has several tools to help reclaim disk space:

1 . Alter database datafile tempdb.dbf resize 50 M

This will remove spaces by physically. if the datafile, and this will not work, any of the segments of the tablespace are extended beyond your resize boundary. So, apply proper datafile size while using this.

2. Alter tablespace tempdb coalesce

This will reclaim space from honeycomb fragmentation.

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:
    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.


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.

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.


2. Identifying the User Process and kill it.

SELECT b.object_name,c.SERIAL#,a.*
WHERE a.object_id = b.object_id
order by a.SESSION_ID;


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.

Tuesday, January 20, 2009

Oracle Certification Program

The Oracle Certification Program, a professional certification program, includes the administration of Oracle Databases as one of its main certification paths. It contains three levels:
1. Oracle Certified Associate (OCA)
2. Oracle Certified Professional (OCP)
3. Oracle Certified Master (OCM)

Oracle Database Features in Oracle 9i.

* AUTOMATICE WORKLOAD REPOSITORY (AWR), providing monitoring services to Oracle database installations from Oracle version 10. Previous releases of Oracle using the STATSPACK for facility provided similar functionality.
* RESOURCE MANAGER (DRM), which controls the use of computing resources.
* DATA AUDITING standard security-auditing features
* FLASHBACK for selective data recovery and reconstruction
* iSQL*Plus, a web-browser-based graphical user interface (GUI) for Oracle database
* ORACLE MANAGED FILES (OMF) -- a feature allowing automated naming, creation and deletion of datafiles at the operating-system level.
* RECOVERY MANAGER (RMAN) for database backup, restoration and recovery
* SQL*Plus, a program that allows users to interact with Oracle database via SQL and PL/SQL commands on a command-line. Compare iSQL*Plus.

From the Beginning of Oracle

The Oracle database consists of a relational database management system (RDBMS) produced and marketed by Oracle Corporation. And Today, widely used in database OLTP Processing and database warehousing.

Since 1979 to till date, Several Versions of Oracle from Oracle Corporation.

* Oracle 2.0 , the first commercially-available SQL-based database (1979)
* Oracle 5.0 , the first distributed database (1986)
* Oracle 7.0, the first database product tested to comply with the ANSI SQL standard (1993)
* Oracle 7.0, the first 64-bit database (1995)
* Oracle 8i, the first web database (1997)
* Oracle 8i, the first database to incorporate a native JRE (1999)
* Oracle 9i, the first proprietary RDBMS to become available on Linux and also, supports XML (1999)
* From Oracle 10g, Oracle database supports to a wide variety of operating system platforms like,
Apple Mac OS X Server, HP-UX, HP Tru64 UNIX, HP OpenVMS, IBM AIX5L, Linux: x86, x86-64, PowerPC, zSeries, Itanium,
Microsoft Windows: x86, x86-64, Itanium , Sun Solaris: SPARC, x86, x86-64 etc.
* Oracle 11g, supports all Operating Systems and also for Oracle Applications Release 11i like Oracle e-Business Suite, Oracle Financials , etc. in 2007-2008.