Little description about Oracle Database

Overview:

- A database is a organised collection of data. eg: address book, either on your phone or in a physical book.

- A Relational Database is a database in which the data is organised according to type with the relationships being maintained between the differing types.

- The latest version of the database is Oracle 12c.

Different editions of Oracle database:

* Enterprise Edition: It is the most robust and secure edition. It offers all features, including superior performance and security.

* Standard Edition: It provides the base functionality for users that do not require Enterprise Edition's robust package.

* Express Edition (XE): It is the lightweight, free and limited Windows and Linux edition.

* Oracle Lite: It is designed for mobile devices.

Some important terms:

- TNSNAMES.ORA: This files contains the information which is used by the system to connect to oracle database.

TNS file contains :
1) PROTOCOL ( Mostly TCP..TransmissionControlProtocol)
2) HOST IP ADDRESS (Where the Database is resided ..Generally we call it as Server.Even the DSCP number acts as substitute)
3) PORTNUMBER ( 1521..Widely used by oracle)
4) SID (the name we provide for DataBase)

5) SERVER (Dedicated/Shared Which is defined at DB CREATION LEVEL)

- SID: System Identifier is the unique name of the database.

To switch between Oracle databases, users must specify the desired SID. The SID is included in the CONNECT DATA parts of the connect descriptors in a TNSNAMES.ORA file, and in the definition of the network listener in the LISTENER.ORA file.
This file is usually located at the following path:

.....\oracle\product\10.2.0\db_1\NETWORK\ADMIN

- DATABASE LINK: A database link is a schema object in one database that enables you to access objects on another database.

 The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.

Use the CREATE DATABASE LINK statement to create a database link.

create public database link mylink
connect to remote_username
identified by mypassword
using 'myserver:1621/MYSID'

The data accessible on the remote database depends on the identity the database link uses when connecting to the remote database:

- If you specify CONNECT TO user IDENTIFIED BY password, then the database link connects with the specified user and password.

- If you specify CONNECT TO CURRENT_USER, then the database link connects with the user in effect based on the scope in which the link is used.

- If you omit both of those clauses, then the database link connects to the remote database as the locally connected user.

Specify PUBLIC to create a public database link visible to all users. If you omit this clause, then the database link is private and is available only to you.

Specify SHARED to create a database link that can be shared by multiple sessions using a single network connection from the source database to the target database.