Oracle interview questions /Answer Set-5

Explain user defined exceptions in oracle.

A User-defined exception has to be defined by the programmer. User-defined exceptions are declared in the declaration section with their type as exception. They must be raised explicitly using RAISE statement, unlike pre-defined exceptions that are raised implicitly. RAISE statement can also be used to raise internal exceptions.

Exception:

DECLARE
userdefined EXCEPTION;


BEGIN

RAISE userdefined;


EXCEPTION
WHEN userdefined THEN

END;


Explain the concepts of Exception in Oracle. Explain its type.

Exception is the raised when an error occurs while program execution. As soon as the error occurs, the program execution stops and the control are then transferred to exception-handling part.
There are two types of exceptions:
1. Predefined : These types of exceptions are raised whenever something occurs beyond oracle rules. E.g. Zero_Divide
2. User defined: The ones that occur based on the condition specified by the user. They must be raised explicitly using RAISE statement, unlike pre-defined exceptions that are raised implicitly.


How exceptions are raised in oracle?

There are four ways that you or the PL/SQL runtime engine can raise an exception:
• Exceptions are raised automatically by the program.
• The programmer raises a user defined exceptions.
• The programmer raises pre defined exceptions explicitly.


What is tkprof and how is it used?

tkprof is used for diagnosing performance issues. It formats a trace file into a more readable format for performance analysis. It is needed because trace file is a very complicated file to be read as it contains minute details of program execution.


What is Oracle Server Autotrace?

It is a utility that provides instant feedback on successful execution of any statement (select, update, insert, delete). It is the most basic utility to test the performance issues.

Oracle DBA interview questions/Answer Set-1

Explain the difference between a hot backup and a cold backup.
A cold backup is done when there is no user activity going on with the system. Also called as offline backup, is taken when the database is not running and no users are logged in. all files of the database are copied and no changes during the copy are made.
A hot backup is taken when the database needs to run all the time. It is an online backup. All files of the database are copied and there may be changes to the database during the copy.


How many memory layers are in the oracle shared pool? Explain them
Oracles shared pool consists of two layers namely, Library cache and Data dictionary cache.
Library cache: This layer has information about SQL statements that were parsed, information about cursors and any plan data.
Data Dictionary cache: this Layer has information about the accounts of the users, their privileges and segments information.


What is the cache hit ratio, what impact does it have on performance of an Oracle database and what is involved in tuning it?
When the cache client like a CPU, web browser etc finds a successful entry in the cache memory, it is called as a cache hit. The percentage of these successful cache hits is called as cache hit ratio. Higher the cache hit ratio better will be the performance because data read from the cache is faster than the memory.


Explain the concept of the DUAL table.
On installing Oracle database, DUAL table Is present by default. It is a special table with just one row. It has a single column called DUMMY. The data type of this column in VARCHAR2(1). It has a value “X”. It is most commonly used to select pseudo columns in Oracle like sysdate.
Select sysdate from dual


What are the ways tablespaces can be managed and how do they differ?
Objects can be assigned to a table space. The related objects can be then grouped together. Table space can also be managed using extents. Extents consist of a specific number of contiguous data blocks. For the required extent, the free extent closest in size is determined.


Explain what are Oracle Catalog and Oracle Archive log?
Oracle catalog contains tables and views to get information about the database. It helps user to understand the available tables, attributes, constraints etc.
Oracle Archive log mode of a database in Oracle, ensures, the online redo logs are not overwritten before they are archived. This ensures that recovery is possible.
What are PCT Free and PCT Used? What is PCT increase parameter in segment?
PCTFREE is a parameter used to find how much space should be left in a database block for future updates. This means that if the PCTFREE = 20, new rows will be added in the block until it is 80% full.
PCTUSED is a parameter helps Oracle to find when it should consider a database block to be empty enough to be added to the freelist. This means that if the PCTFREE = 50, new rows will be not be added in the block until sufficient rows are deleted from the block so that it falls below 40% empty.
PCTINCREASE parameter is used to find how much will the each subsequent segment will grow. This value is in %.


What is dump destination? What are bdump, cdump and udump?
Trace files for Oracle processes are stored in dump destination.
Bdump- Oracle writes to the trace log and creates trace files for background processes in background dump destination. If this directory becomes full and more files cannot be written, debugging becomes difficult.
Cdump- Oracle writes core files and background processes in Core dump destination directory. If this directory becomes full and more files cannot be written, debugging becomes difficult.
Udump – Oracle creates trace files for the user processes in the User Dump directory if this directory becomes full and more files cannot be written, debugging becomes difficult.


How do you increase the performance of %LIKE operator?
LIKE% works the fastest because it uses the index to search on the column provided an index is specified on the column. Using % after LIKE, results in faster results.


Why use materialized view instead of a table
Materialized views are basically used to increase query performance since it contains results of a query. They should be used for reporting instead of a table for a faster execution.


Why and how the deadlock situation arises
A deadlock situation arises when two or more users wait for the same resource locked by one anther or two or more processes wait to update rows which are locked by other processes. Oracle if detects a deadlock, rolls back the session chosen by the deadlock victim.


What are standby databases? Difference between Physical and logical standby databases
A standby database is a replica of the original database. In order to keep both the database synchronized, archived redo logs can be used. It is mainly used in disaster protection. It can also be opened in read only mode which allows it to be used independently for reporting.
A logical standby database allows new database objects like tables, indexes to be added to the database. On the other hand, the physical standby database is a physical or structural copy of primary database. They can be opened in read only for disaster recovery.


What is Cache Fusion Technology?
In Cache fusion, multiple buffers join to act as one. It eliminates disk i/o operaions by making use of a scalable shared cache. It treats multiple buffer caches as one thereby resolving data consistency issues. Cash fusion technology can provide more resources and increases concurrency of users.


What is the difference between Cloning and Standby databases?
The clone database is a copy of the database which can be opened in read write mode. It is treated as a separate copy of the database that is functionally completely separate. The standby database is a copy of the production database used for disaster protection. In order to update the standby database; archived redo logs from the production database can be used. If the primary database is destroyed or its data becomes corrupted, one can perform a failover to the standby database, in which case the standby database becomes the new primary database.




What is dump destination? What are bdump, cdump and udump?


Trace files for Oracle processes are stored in dump destination.


Bdump- Oracle writes to the trace log and creates trace files for background processes in background dump destination. If this directory becomes full and more files cannot be written, debugging becomes difficult.


Cdump- Oracle writes core files and background processes in Core dump destination directory. If this directory becomes full and more files cannot be written, debugging becomes difficult.


Udump – Oracle creates trace files for the user processes in the User Dump directory if this directory becomes full and more files cannot be written, debugging becomes difficult.




What are PCT Free and PCT Used? What is PCT increase parameter in segment?


PCTFREE is a parameter used to find how much space should be left in a database block for future updates. This means that if the PCTFREE = 20, new rows will be added in the block until it is 80% full.


PCTUSED is a parameter helps Oracle to find when it should consider a database block to be empty enough to be added to the freelist. This means that if the PCTFREE = 50, new rows will be not be added in the block until sufficient rows are deleted from the block so that it falls below 40% empty.


PCTINCREASE parameter is used to find how much will the each subsequent segment will grow. This value is in %




Explain what are Oracle Catalog and Oracle Archive log?


Oracle catalog contains tables and views to get information about the database. It helps user to understand the available tables, attributes, constraints etc.


Oracle Archive log mode of a database in Oracle, ensures, the online redo logs are not overwritten before they are archived. This ensures that recovery is possible

Networking interview questions/Answer set-3

What is PPP protocol? Explain PPP packet format.

Point to Point protocol helps communication between 2 computers over a serial cable, phone line or other fiber optic lines, e.g. Connection between an Internet Service Provider and a host. PPP also provides authentication. PPP operates by sending Request packets and waiting for Acknowledge packets that accept, reject or try to change the request. The protocol is also used to negotiate on network address or compression options between the nodes.

Packet format

Flag field: 1 byte: - Indicates frames beginning or end

Address field: 1 byte: - Used for broadcast address (destination address)

Control field: 1 byte: - Used as a control byte

Protocol field: - 1 or 2 bytes: - Setting of protocol in information field (of datagram)

Information: - 0 or more bytes: - Datagram (whether it contains data or control information)

Padding: - 0 or more bytes: - optional padding

FCS: - 2 or more bytes: - error check sum

What is IP Spoofing and how can it be prevented?

IP spoofing is a mechanism used by attackers to gain unauthorized access to a system. Here, the intruder sends messages to a computer with an IP address indicating that the message is coming from a trusted host. This is done by forging the header so it contains a different address and make it appear that the packet was sent by a different machine.

Prevention
Packet filtering: - to allow packets with recognized formats to enter the network
using special routers and firewalls.
Encrypting the session

Explain IP datagram, Fragmentation and MTU.

IP datagram can be used to describe a portion of IP data. Each IP datagram has set of fields arranged in an order. The order is specific which helps to decode and read the stream easily. IP datagram has fields like Version, header length, Type of service, Total length, checksum, flag, protocol, Time to live, Identification, source and destination ip address, padding, options and payload.

MTU: Maximum Transmission Unit is the size of the largest packet that a communication protocol can pass. The size can be fixed by some standard or decided at the time of connection

Fragmentation is a process of breaking the IP packets into smaller pieces. Fragmentation is needed when the datagram is larger than the MTU. Each fragment becomes a datagram in itself and transmitted independently from source. When received by destination they are reassembled.


What is an application gateway?

An application gateway is an application program that runs on a firewall between two networks. An application gateway is used for establishing connection between client program and destination service. The client negotiates with the gateway to communicate with the service of destination. Here, gateway can be called as a proxy. Hence, two connections are made; One between client and proxy; other between proxy and destination service. Connections take place behind the firewall.

Explain Circuit Level Gateway.

A circuit level gateway is used to find if a session in TCP handshaking is legitimate or not. It can be considered as a layer between application layer and transport layer. They protect the information of the private network they protect. Circuit level gateways do not filter packets.

What is 'Gateway of Last Resort'?

A Gateway of Last Resort or Default gateway is a route used by the router when no other known route exists to transmit the IP packet. Known routes are present in the routing table. Hence, any route not known by the routing table is forwarded to the default route. Each router which receives this packet will treat the packet the same way, if the route is known, packet will be forwarded to the known route.

Networking interview questions/Answer set-2

Explain IP, TCP and UDP.

TCP – Transmission control Protocol is used to establish communication between nodes or networks and exchange data packets. It guarantees delivery of data packets in the order they were sent. Hence it is most commonly used in all applications that require guaranteed delivery of data. It can handle both timeouts (if packets were delayed) and retransmission (if packets were lost). The stream of data is transmitted in segments. The segment header is 32 bit. it is a connectionless communication protocol at the third level (network) of the OSI model.

IP – Internet protocol is used for transmission of data over the internet. IP uses IP addresses to identity each machine uniquely. Message is sent using small packets. The packet contains both the sender and receivers address. IP does not guarantee the delivery in the same order as sent. This is because the packets are sent via different routes. It is a connectionless communication protocol at the third level (network) of the OSI model.

UDP – User Data Protocol is a communication protocol. It is normally used as an alternative for TCP/IP. However there are a number of differences between them. UDP does not divide data into packets. Also, UDP does not send data packets in sequence. Hence, the application program must ensure the sequencing. UDP uses port numbers to distinguish user requests. It also has a checksum capability to verify the data.




What is multicasting?

Multicasting allows a single message to be sent to a group of recipients. Emailing, teleconferencing, are examples of multicasting. It uses the network infrastructure and standards to send messages.

Explain the functionality of PING.

Ping Is particularly used to check if the system is in network or not. It also gives packet lost information. In windows ping command is written as ping ip_address. The output returns the data packets information. The number of packets sent, received and lost is returned by PING.

What is a MAC address?

A Media Access Control address is a unique identifier that is assigned to the network adapters or NICs by the manufacturers for the purpose of identification and used in the Media Access Control protocol sub layer. It is a 12 digit hexadecimal number. A MAC address usually encodes the registered identification of the manufacturer, if the address is assigned by the manufacturer. It some times also called as Ethernet Hardware Address / physical address/ adapter address.

Explain Spanning-Tree protocols.

Spanning Trees are a standard technique implemented in LAN connections. On a mesh topology, a set of spanning tree algorithms were developed for prevention of redundant transmission of data along intermediate hops between a source and a destination host. In the absence of spanning trees, a mesh network is flooded and rendered unusable by messages by circulating within a loop that is infinite, between hosts. An algorithm used in transparent bridges which determines the best path from source to destination to avoid bridge loops.

At the time of STP initialization in a network, its first action is to utilize the Spanning Tree Algorithm for selection of a root bridge and a root port. The root bridge is the network which has lowest-value bridge identifier. All the switches on the network use Bridge Protocol Data Units to broadcast the bridge IDs to the other switches in that network. Soon after selection of the root bridge, determination of the root ports on all other bridges is done.

What is the use of IGMP protocol?

Internet Group Management Protocol: - It allows internet hosts to participate in multicasting. The IGMP messages are used to learn which hosts is part of which multicast groups. The mechanism also allows a host to inform its local router that it wants to receive messages.

What are Ping and Tracert?

Ping and tracert are the commands used to send information to some remote computers to receive some information. Information is sent and received by packets.

Ping I particularly used to check if the system is in network or not. It also gives packet lost information. In windows ping command is written as ping ip_address
Tracert is called as trace route. It is used to track or trace the path the packet takes from the computer where the command is given until the destination. In windows ping command is written as tracert ip_address

Explain RSVP. How does it work?

Resource Reservation protocol is used to reserve resources across a network. It is used for requesting a specific Quality of Service (QoS) from the network.

This is done by carrying the request (that needs a reservation of the resource) of the host throughout the network. It visits each node in the network. RSVP used two local modules for reservation of resources. Admission control module confirms if there are sufficient available resources while policy module checks for the permission of making a reservation. RSVP offers scalability. On a successful completion of both checks RSVP uses the packet classifier and packet scheduler for the desired Qos requested.

Explain the concept of DHCP.

Dynamic Host Configuration Protocol is used assigning IP addresses to computers in a network. The IP addresses are assigned dynamically. Certainly, using DHCP, the computer will have a different IP address every time it is connected to the network. In some cases the IP address may change even when the computer is in network. This means that DHCP leases out the IP address to the computer for sometime. Clear advantage of DHCP is that the software can be used to manage IP address rather than the administrator.


What are the differences between a domain and a workgroup?

In a domain, one or more computer can be a server to manage the network. On the other hand in a workgroup all computers are peers having no control on each other.

In a domain, user doesn’t need an account to logon on a specific computer if an account is available on the domain. In a work group user needs to have an account for every computer.

In a domain, Computers can be on different local networks. In a work group all computers needs to be a part of the same local network.


Explain how NAT works

Network Address Translation translates and IP address used in a network to another IP address known within another network. A NAT table is maintained for global to local and local to mapping of IP’s. NAT can be statically defined or dynamically translate from a pool of addresses. The NAT router is responsible for translating traffic coming and leaving the network. NAT prevents malicious activity initiated by outside hosts from reaching local hosts by being dependent on a machine on the local network to initiate any connection to hosts on the other side of the router.

Networking interview questions/Answer set-1

What is LAN?

LAN is a computer network that spans a relatively small area. Most LANs are confined to a single building or group of buildings. However, one LAN can be connected to other LANs over any distance via telephone lines and radio waves. A system of LANs connected in this way is called a wide-area network (WAN). Most LANs connect workstations and personal computers. Each node (individual computer) in a LAN has its own CPU with which it executes programs, but it also is able to access data and devices anywhere on the LAN. This means that many users can share expensive devices, such as laser printers, as well as data. Users can also use the LAN to communicate with each other, by sending e-mail or engaging in chat sessions.


What's the difference Between an Intranet and the Internet?

There's one major distinction between an intranet and the Internet: The Internet is an open, public space, while an intranet is designed to be a private space. An intranet may be accessible from the Internet, but as a rule it's protected by a password and accessible only to employees or other authorized users.

From within a company, an intranet server may respond much more quickly than a typical Web site. This is because the public Internet is at the mercy of traffic spikes, server breakdowns and other problems that may slow the network. Within a company, however, users have much more bandwidth and network hardware may be more reliable. This makes it easier to serve high-bandwidth content, such as audio and video, over an intranet.

Define the term Protocol.

Protocol is a standard way of communicating across a network. A protocol is the "language" of the network. It is a method by which two dissimilar systems can communicate. TCP is a protocol which runs over a network.

What is FTP (File Transfer Protocol)?

FTP is File Transfer Protocol. It used to exchange files on the internet. To enable the data transfer FTP uses TCP/IP, FTP is most commonly used to upload and download files from the internet. FTP can be invoked from the command prompt or some graphical user interface. FTP also allows to update (delete, rename, move, and copy) files at a server. It uses a reserved port no 21.

Explain the 7 Layers of OSI.

Layer 1: Physical layer
It represents all the electrical and physical specifications for devices.


Layer 2: Data link layer
It provides the functional and procedural means to transfer data between network entities and to detect and possibly correct errors that may occur in the Physical layer.

Layer 3: Network layer
The Network layer provides the functional and procedural means of transferring variable length data sequences from a source to a destination via one or more networks.

Layer 4: Transport layer
It provides transparent transfer of data between end users.

Layer 5: Session layer
It controls the sessions between computers. It connects, manages and terminates the connections between the local and remote application.

Layer 6: Presentation layer
It transforms data to provide a standard interface for the Application layer.

Layer 7: Application layer
It provides a means for the user to access information on the network through an application.

What is a network? What are the different kinds of network? Explain them

A network is a group of computers or nodes connected together. They are connected with each other by communication paths.

Types of Networks:

LAN – Local Area Network connects a group of nodes covering a small physical area. LAN’s are most commonly seen in offices, building etc. LAN’s enable higher transfer rate of data, smaller coverage of area and hence less wiring.

WAN – Wide Area Network connects a group of nodes covering a wide area. WAN typically connects and allow communication between regions or national boundaries. The most common example of WAN is internet.

VPN – Virtual Private Network connects or links nodes in some larger area by open connections or virtual circuits in some larger network (e.g., the Internet) instead of by physical wires. It is used for secure communication through the public internet. VPN alone may not support explicit security features, such as authentication or content encryption.

Intranet – It is a set of networks under the control of a single administrative person. It can be considered as an internal network of an organization. If it is large, web servers are used to provide information to the users.

Extranet – It is a network that restricts itself within a single organization. It can be categorized as WAN, MAN etc. however; it cannot have a single LAN. It must have a connection (at least one) with external network.

What are network topologies? Explain Ring, Bus and Star topology.

A network topology describes the layout of a network. It describes how different nodes and elements are connected to each other. Different types of topology:

a. Ring:-

All nodes connected with another in a loop.
Each device is connected to one or more another device on either side.

b. Bus

All nodes connected to a central and a common cable called as a back bone.
In bus topology, the server is at one end and the clients are connected at different positions across the network.
Easy to manage and install.
If the backbone fails, the entire communication fails.

c. Star

All nodes connected to a central hub.
The communication between the nodes is through the hub.
Relative requires more cables as compared to BUS. However if any node fails, it wont affect the entire LAN.

Oracle interview questions /Answer Set-5

Explain user defined exceptions in oracle.

A User-defined exception has to be defined by the programmer. User-defined exceptions are declared in the declaration section with their type as exception. They must be raised explicitly using RAISE statement, unlike pre-defined exceptions that are raised implicitly. RAISE statement can also be used to raise internal exceptions.

Exception:

DECLARE
userdefined EXCEPTION;


BEGIN

RAISE userdefined;


EXCEPTION
WHEN userdefined THEN

END;


Explain the concepts of Exception in Oracle. Explain its type.

Exception is the raised when an error occurs while program execution. As soon as the error occurs, the program execution stops and the control are then transferred to exception-handling part.
There are two types of exceptions:
1. Predefined : These types of exceptions are raised whenever something occurs beyond oracle rules. E.g. Zero_Divide
2. User defined: The ones that occur based on the condition specified by the user. They must be raised explicitly using RAISE statement, unlike pre-defined exceptions that are raised implicitly.


How exceptions are raised in oracle?

There are four ways that you or the PL/SQL runtime engine can raise an exception:
• Exceptions are raised automatically by the program.
• The programmer raises a user defined exceptions.
• The programmer raises pre defined exceptions explicitly.


What is tkprof and how is it used?

tkprof is used for diagnosing performance issues. It formats a trace file into a more readable format for performance analysis. It is needed because trace file is a very complicated file to be read as it contains minute details of program execution.


What is Oracle Server Autotrace?

It is a utility that provides instant feedback on successful execution of any statement (select, update, insert, delete). It is the most basic utility to test the performance issues.

Oracle interview questions /Answer Set-4

What are transaction isolation levels supported by Oracle?

Oracle supports 3 transaction isolation levels:
a. Read committed (default)
b. Serializable transactions
c. Read only


What is SQL*Loader?

SQL*Loader is a loader utility used for moving data from external files into the Oracle database in bulk. It is used for high performance data loads.


What is Program Global Area (PGA)?

The Program Global Area (PGA): stores data and control information for a server process in the memory. The PGA consists of a private SQL area and the session memory.


What is a shared pool?

The shared pool is a key component. The shared pool is like a buffer for SQL statements. It is to store the SQL statements so that the identical SQL statements do not have to be parsed each time they're executed.


38. What is snapshot in oracle?

A snapshot is a recent copy of a table from db or in some cases, a subset of rows/cols of a table. They are used to dynamically replicate the data between distributed databases.

What is a synonym?

A synonym is an alternative name tables, views, sequences and other database objects.


What is a schema?

A schema is a collection of database objects. Schema objects are logical structures created by users to contain data. Schema objects include structures like tables, views, and indexes.




What are Schema Objects?

Schema object is a logical data storage structure. Oracle stores a schema object logically within a tablespace of the database.



What is a sequence in oracle?

Is a column in a table that allows a faster retrieval of data from the table because this column contains data which uniquely identifies a row. It is the fastest way to fetch data through a select query. This column has constraints to achieve this ability. The constraints are put on this column so that the value corresponding to this column for any row cannot be left blank and also that the value is unique and not duplicated with any other value in that column for any row.


Difference between a hot backup and a cold backup

Cold backup: It is taken when the database is closed and not available to users. All files of the database are copied (image copy). The datafiles cannot be changed during the backup as they are locked, so the database remains in sync upon restore.

Hot backup: While taking the backup, if the database remains open and available to users then this kind of back up is referred to as hot backup. Image copy is made for all the files. As, the database is in use the entire time, so there might be changes made when backup is taking place. These changes are available in log files so the database can be kept in sync


What are the purposes of Import and Export utilities?

Export and Import are the utilities provided by oracle in order to write data in a binary format from the db to OS files and to read them back.

These utilities are used:
• To take backup/dump of data in OS files.
• Restore the data from the binary files back to the database.
• move data from one owner to another


Difference between ARCHIVELOG mode and NOARCHIVELOG mode

Archivelog mode is a mode in which backup is taken for all the transactions that takes place so as to recover the database at any point of time.
Noarichvelog mode is in which the log files are not written. This mode has a disadvantage that the database cannot be recovered when required. It has an advantage over archivelog mode which is increase in performance.







What are the original Export and Import Utilities?

SQL*Loader, External Tables


What are data pump Export and Import Modes?

It is used for fast and bulk data movement within oracle databases. Data Pump utility is faster than the original import & export utilities.


What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

SQLCODE: It returns the error number for the last encountered error.
SQLERRM: It returns the actual error message of the last encountered error.

Oracle interview questions /Answer Set-3

Explain the attributes of implicit cursor

a. %FOUND - True, if the SQL statement has changed any rows.
b. %NOTFOUND - True, if record was not fetched successfully.
c. %ROWCOUNT - The number of rows affected by the SQL statement.
d. %ISOPEN - True, if there is a SQL statement being associated to the cursor or the cursor is open.


Explain the attributes of explicit cursor.

a. %FOUND - True, if the SQL statement has changed any rows.
b. %NOTFOUND - True, if record was not fetched successfully.
c. %ROWCOUNT - The number of rows affected by the SQL statement.
d. %ISOPEN - True, if there is a SQL statement being associated to the cursor or the cursor is open.


What is the ref cursor in Oracle?

REF_CURSOR allows returning a recordset/cursor from a Stored procedure.
It is of 2 types:
Strong REF_CURSOR: Returning columns with datatype and length need to be known at compile time.
Weak REF_CURSOR: Structured does not need to be known at compile time.
Syntax till Oracle 9i
create or replace package REFCURSOR_PKG as
TYPE WEAK8i_REF_CURSOR IS REF CURSOR;
TYPE STRONG REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;
end REFCURSOR_PKG;
Procedure returning the REF_CURSOR:
create or replace procedure test( p_deptno IN number , p_cursor OUT
REFCURSOR_PKG.WEAK8i_REF_CURSOR)
is
begin
open p_cursor FOR
select *
from emp
where deptno = p_deptno;
end test;
Since Oracle 9i we can use SYS_REFCURSOR
create or replace procedure test( p_deptno IN number,p_cursor OUT SYS_REFCURSOR)
is
begin
open p_cursor FOR
select *
from emp
where deptno = p_deptno;
end test;
For Strong
create or replace procedure test( p_deptno IN number,p_cursor OUT REFCURSOR_PKG.STRONG
REF_CURSOR)
is
begin
open p_cursor FOR
select *
from emp
where deptno = p_deptno;
end test;


What are the drawbacks of a cursor?

Cursors allow row by row processing of recordset. For every row, a network roundtrip is made unlike in a Select query where there is just one network roundtrip. Cursors need more I/O and temp storage resources, thus it is slower.


What is a cursor variable?

In case of a cursor, Oracle opens an anonymous work area that stores processing information. This area can be accessed by cursor variable which points to this area. One must define a REF CURSOR type, and then declare cursor variables of that type to do so.
E.g.:
/* Create the cursor type. */
TYPE company_curtype IS REF CURSOR RETURN company%ROWTYPE;

/* Declare a cursor variable of that type. */
company_curvar company_curtype;


What is implicit cursor in Oracle?

PL/SQL creates an implicit cursor whenever an SQL statement is executed through the code, unless the code employs an explicit cursor. The developer does not explicitly declare the cursor, thus, known as implicit cursor.
E.g.:
In the following UPDATE statement, which gives everyone in the company a 20% raise, PL/SQL creates an implicit cursor to identify the set of rows in the table which would be affected.
UPDATE emp
SET salary = salary * 1.2;


Can you pass a parameter to a cursor? Explain with an explain

Parameterized cursor:
/*Create a table*/
create table Employee(
ID VARCHAR2(4 BYTE)NOT NULL,
First_Name VARCHAR2(10 BYTE)
);
/*Insert some data*/
Insert into Employee (ID, First_Name) values (‘01’,’Harry’);
/*create cursor*/
declare
cursor c_emp(cin_No NUMBER)is select count(*) from employee where id=cin_No;
v_deptNo employee.id%type:=10;
v_countEmp NUMBER;
begin
open c_emp (v_deptNo);
fetch c_emp into v_countEmp;
close c_emp;
end;

/*Using cursor*/
Open c_emp (10);





What is a package cursor?

A Package that returns a Cursor type is a package cursor.
Eg:
Create or replace package pkg_Util is
cursor c_emp is select * from employee;
r_emp c_emp%ROWTYPE;
end;
/*Another package using this package*/
Create or replace package body pkg_aDifferentUtil is
procedure p_printEmps is
begin
open pkg_Util.c_emp;
loop
fetch pkg_Util.c_emp into pkg_Util.r_emp;
exit when pkg_Util.c_emp%NOTFOUND;
DBMS_OUTPUT.put_line(pkg_Util.r_emp.first_Name);
end loop;
close pkg_Util.c_emp;
end;
end;


Explain why cursor variables are easier to use than cursors.

Cursor variables are preferred over a cursor for following reasons:
A cursor variable is not tied to a specific query.
One can open a cursor variable for any query returning the right set of columns. Thus, more flexible than cursors.
A cursor variable can be passed as a parameter.
A cursor variable can refer to different work areas.


What is locking, advantages of locking and types of locking in oracle?

Locking is a mechanism to ensure data integrity while allowing maximum concurrent access to data. It is used to implement concurrency control when multiple users access table to manipulate its data at the same time.

Advantages of locking:
a. Avoids deadlock conditions
b. Avoids clashes in capturing the resources
Types of locks:
a. Read Operations: Select
b. Write Operations: Insert, Update and Delete

Oracle interview questions /Answer Set-2

What is object data type in oracle?

New/User defined objects can be created from any database built in types or by their combinations. It makes it easier to work with complex data like images, media (audio/video). An object types is just an abstraction of the real world entities. An object has:
• Name
• Attributes
• Methods
Example:
Create type MyName as object (first varchar2(20), second varchar2(20));

Now you can use this datatype while defining a table below:

Create table Emp (empno number(5),Name MyName);
One can access the Atributes as Emp.Name.First and Emp.Name.Second



What is composite data type?

Composite data types are also known as Collections .i.e RECORD, TABLE, NESTED TABLE, VARRAY.
Composite data types are of 2 types:
PL/SQL RECORDS
PL/SQL Collections- Table, Varray, Nested Table


Differences between CHAR and NCHAR in Oracle

NCHAR allow storing of Unicode data in the database. One can store Unicode characters regardless of the setting of the database characterset


Differences between CHAR and VARCHAR2 in Oracle

CHAR is used to store fixed length character strings where as Varchar2 can store variable length character strings. However, for performance sake Char is quit faster than Varchar2.
If we have char name[10] and store “abcde”, then 5 bytes will be filled with null values, whereas in case of varchar2 name[10] 5 bytes will be used and other 5 bytes will be freed.


Differences between DATE and TIMESTAMP in Oracle

Date is used to store date and time values including month, day, year, century, hours, minutes and seconds. It fails to provide granularity and order of execution when finding difference between 2 instances (events) having a difference of less than a second between them.
TimeStamp datatype stores everything that Date stores and additionally stores fractional seconds.
Date: 16:05:14
Timestamp: 16:05:14:000

Define CLOB and NCLOB datatypes.

CLOB: Character large object. It is 4GB in length.
NCLOB: National Character large object. It is CLOB datatype for multiple character sets , upto 4GB in length.


What is the BFILE datatypes?

It refers to an external binary file and its size is limited by the operating system.


What is Varrays?

Varrays are one-dimensional, arrays. The maximum length is defined in the declaration itself. These can be only used when you know in advance about the maximum number of items to be stored.
For example: One person can have multiple phone numbers. If we are storing this data in the tables, then we can store multiple phone numbers corresponding to single Name. If we know the maximum number of phone numbers, then we can use Varrays, else we use nested tables.


What is a cursor? What are its types?

Cursor is used to access the access the result set present in the memory. This result set contains the records returned on execution of a query.
They are of 2 types:
1. Explicit
2. Implicit

Oracle interview questions /Answer

Explain the difference between trigger and stored procedure.

Trigger in act which is performed automatically before or after a event occur

Stored procedure is a set of functionality which is executed when it is explicitly invoked.

Explain Row level and statement level trigger.

Row-level: - They get fired once for each row in a table affected by the statements.

Statement: - They get fired once for each triggering statement.

Advantage of a stored procedure over a database trigger

Firing of a stored procedure can be controlled whereas on the other hand trigger will get fired whenever any modification takes place on the table.

What are cascading triggers?

A Trigger that contains statements which cause invoking of other Triggers are known as cascading triggers. Here’s the order of execution of statements in case of cascading triggers:

·         Execute all BEFORE statement triggers that apply to the current statement.

·         Loop for each row affected statement.

·         Execute all BEFORE row triggers that apply to the current statement in the loop.

·         Lock and change row, perform integrity constraints check; release lock.

·         Execute all AFTER row triggers that apply to the current statement.

·         Execute all AFTER statement triggers that apply to the current statement.

What is a JOIN? Explain types of JOIN in oracle.

A JOIN is used to match/equate different fields from 2 or more tables using primary/foreign keys. Output is based on type of Join and what is to be queries i.e. common data between 2 tables, unique data, total data, or mutually exclusive data.

Types of JOINS:

JOIN Type	Example	Description
Simple JOIN	SELECT p.last_name, t.deptName FROM person p, dept t WHERE p.id = t.id;	Find name and department name of students who have been allotted a department
Inner/Equi/Natural JOIN	SELECT * from Emp INNER JOIN Dept WHERE Emp.empid=Dept.empid	Extracts data that meets the JOIN conditions only. A JOIN is by default INNER unless OUTER keyword is specified for an OUTER JOIN.
Outer Join	SELECT distinct * from Emp LEFT OUTER JOIN Dept Where Emp.empid=Dept.empid	It includes non matching rows also unlike Inner Join.
Self JOIN	SELECT a.name,b.name from emp a, emp b WHERE a.id=b.rollNumber	Joining a Table to itself.

Linux interview questions/Answer 2

What is Bash Shell?

Bash is a free shell for UNIX. It is the default shell for most UNIX systems. It has a combination of the C and Korn shell features.  Bash shell is not portable. any Bash-specific feature will not function on a system using the Bourne shell or one of its replacements, unless bash is installed as a secondary shell and the script begins with #!/bin/bash. It supports regular and expressions. When bash script starts, it executes commands of different scripts.

Explain Some Network-Monitoring Tools in Linux: ping, traceroute, tcpdump, ntop

Network monitoring tools are used to monitor the network, systems present on the network, traffic etc.

Ping: Ping command is used to check if the system is in the network or not. To check if the host is operating.

e.g. ping ip_address

When the command is executed, it returns a detailed summary of the host. Packets   sent, received, lost by estimating the round trip time.

Traceroute : the command is used to trace the path taken by the packet across a network. Tracing the path here means finding out the hosts visited by the packet to reach its destination. This information is useful in debugging. Roundtrip time in ms is shown for every visit to a host.

Tcpdump: commonly used to monitor network traffic. Tcdump captures and displays packet headers and matching them against criteria or all. It interprets Boolean operators and accepts host names, ip address, network names as arguments.

Ntop : Network top shows the network usage. It displays summary of network usage by machines on the network in a format as of UNIX top utility. It can also be run in web mode, which allows the display to be browsed with a web browser. It can display network traffic statistics, identify host etc. Interfaces are available to view such information.

Explain file system of linux. The root "/" filesystem, /usr filesystem, /var filesystem, /home filesystem, /proc filesystem.

Root "/" file system: The kernel needs a root file system to mount at start up. The root file system is generally small and should not be changed often as it may interrupt in booting. The root directory usually does not have the critical files. Instead sub directories are created. E.g. /bin (commands needed during bootup), /etc (config files) , /lib(shared libraries).

/usr filesystem : this file system is generally large as it contains the executable files to be shared amongst different machines. Files are usually the ones installed while installing Linux. This makes it possible to update the system from a new version of the distribution, or even a completely new distribution, without having to install all programs again. Sub directories include /bin, /include, /lib, /local (for local executables)

/var filesystem : this file system is specific to local systems. It is called as var because the data keeps changing. The sub directories include /cache/man (A cache for man pages), /games (any variable data belong to games), /lib (files that change), /log (log from different programs), /tmp (for temporary files)

/home filesystem: - this file system differs from host to host. User specific configuration files for applications are stored in the user's home directory in a file. UNIX creates directories for all users directory. E.g /home/my_name. Once the user is logged in ; he is placed in his home directory.

/proc filesystem :  this file system does  not exist on the hard disk.

 It is created by the kernel in its memory to provide information about the system. 

This information is usually about the processes. Contains a hierarchy of special files which

 represent the current state of the kernel .Few of the Directories include /1

 (directory with information about process num 1, where 1 is the identification number),

 /cpuinfo (information about cpu), /devices (information about devices installed), 

/filesystem (file systems configured), /net (information about network protocols),

 /mem (memory usage)

What are the process states in Linux?

Process states in Linux:

Running: Process is either running or ready to run

Interruptible: a Blocked state of a process and waiting for an event or signal from another process

Uninterruptible:-  a blocked state. Process waits for a hardware condition and cannot handle any signal

Stopped: Process is stopped or halted and can be restarted by some other process

Zombie: process terminated, but information is still there in the process table.

What is a zombie? 

Zombie is a process state when the child dies before the parent process. In this case the structural information of the process is still in the process table. Since this process is not alive, it cannot react to signals. Zombie state can finish when the parent dies. All resources of the zombie state process are cleared by the kernel

Explain each system calls used for process management in linux.

System calls used for Process management:

Fork () :- Used to create a new process

Exec() :- Execute a new program

Wait():- wait until the process finishes execution

Exit():- Exit from the process

Getpid():- get the unique process id of the process

Getppid():- get the parent process unique id

Nice():- to bias the existing property of process