in Data Industry

Oracle What and How

What are views in Oracle?

A View in Oracle is the representation of a SQL statement that is stored in memory. It can easily be re-used. 

The following is an example query:

SELECT customerid, customername FROM customers WHERE countryid='US';

To create a view use the CREATE VIEW command as seen in the following example:

CREATE VIEW view_uscustomers 
AS 
SELECT customerid, customername FROM customers WHERE countryid='US';

This command creates a new view called view_uscustomers. Note that this command does not result in anything being actually stored in the database at all except for a data dictionary entry that defines this view. This means that every time you query this view, Oracle has to go out and execute the view and query the database data. We can query the view like this:

SELECT * FROM view_uscustomers WHERE customerid BETWEEN 100 AND 200;

And Oracle will transform the query into this:

SELECT *  FROM (select customerid, customername from customers WHERE countryid='US')  WHERE customerid BETWEEN 100 AND 200
  • Commonality of code being used. Since a view is based on one common set of SQL, this means that when it is called it’s less likely to require parsing.
  • Security. Views have long been used to hide the tables that actually contain the data you are querying. Also, views can be used to restrict the columns that a given user has access to.
  • Predicate pushing

[source https://stackoverflow.com/a/256703/6108341]

What is an Oracle SID?

The Oracle SID (System ID) is used to uniquely identify a particular database on a system and it is the name the unique name of the INSTANCE. Oracle considers the “Database” to be the files. For this reason, one cannot have more than one database with the same SID on a computer system. [source https://www.orafaq.com/wiki/ORACLE_SID#:~:text=The%20Oracle%20System%20ID%20(SID,database%20must%20have%20unique%20SID’s.]

What is Service Name in Oracle?

The difference between Oracle System ID and Service name is that Service name is alias to an INSTANCE (or multiple instances). This is required in the following case when one is running a cluster and the client requests to connect to SALES.acme.com, the DBA can on the spot change the number of instances which are available at the given example SALES.acme.com requests, or even move to a different database without any need to change the settings.

How to find SID in Oracle?

There are two ways to find the SID in Oracle. One of them is trying to find it in Oracle database. This path will not require to write any new code or query, but one will need to have windows operating system. The following video is step by step tutorial from Manish Sharma which explains pretty clearly how to find SID in Oracle database.

As mentioned before there are two ways to find SID one of them is searching in Oracle database and another one is by writing a simple SQL query.

What is Oracle Client?

A Client is a class library (DLL) that allows to connect remotely to the underlying database of an application. A client can also be called a .NET Data Provider. There can be multiple data providers based on the underlying database engine with which the application is working.

There was System.Data.OracleClient (deprecated), provided by Microsoft. There is Oracle.Data.Client, which is actually the best ever built Oracle Client, or Oracle .NET Data Provider.

Remember though, always use Oracle.Data.Client namespace in the Oracle.DataAccess.dll assembly.

Source [https://stackoverflow.com/a/2870874/6108341]

Difference between is Oracle Database and Oracle Client?

A database server is the Oracle software managing a database, and a client is an application that requests information from a server. Each computer in a network is a node that can host one or more databases. Each node in a distributed database system can act as a client, a server, or both, depending on the situation.

[source https://stackoverflow.com/a/47395287/6108341]

What is TNS entry in Oracle?

The TNS (Transparent Network Substrate) is the sql*net configuration file that defines database address which is used by the system to connect to oracle database. It is the name of the entry in tnsnames.ora file which is kept in $ORACLE_HOME/network/admin.