Could you explain the areas where tuning of database is required?

Correct Answer: We need to first login to the data base as: SQLPLUS followed by user_name/password Then we need to execute the following query: CREATE USER user_name IDENTIFIED BY abcd; in this query a user_name suggest the user that is created which has a password abcd which is required for login in database

Correct Answer: An index is a object which is used to improve performance during retrieval of records CREATE [UNIQUE] INDEX index_name ON employee[emp_id, emp_name,dept_id] [COMPUTE STATISTICS] The UNIQUE keyword is used when combined values of the index should be unique The COMPUTE STATISTICS during the creation of index optimizes the plan of execution of the SQL statement and improves performance

Correct Answer: The CACHE option means how many sequences will be stored in memory for access by the application objects The performance is faster However in case of the database is down the data is memory is lost for the sequence The NO CACHE option means values are not stored in memory So there might be some performance issue

Correct Answer: A sequence is a database object that is used to generate sequential number CREATE SEQUENCE seqname [increment] [minimum value][maximum value][start][cache][cycle] Nextval and currval lets us get the next value and current value from the sequence

Correct Answer: A package is a collection of related PL/SQL objects The package contains a body and a specification The package specification has the declaration which is public and can be used in the PL/SQL sub programs inside the package The package body holds the implementation of all the PL/SQL objects declared in the specification Example of a PL/SQL Package CREATE OR REPLACE PACKAGE emp_data AS PROCEDURE add_employee ( ename VARCHAR2, job VARCHAR2, mgr NUMBER, sal NUMBER, deptno NUMBER); END emp_actions; CREATE OR REPLACE PACKAGE BODY emp_data AS PROCEDURE add_employee ( ename VARCHAR2, job VARCHAR2, mgr NUMBER, sal NUMBER, deptno NUMBER) IS BEGIN INSERT INTO emp VALUES (empno_seqNEXTVAL, ename, job, mgr, SYSDATE, comm, deptno); END add_employee; END emp_data; Advantages of PL/SQL packages : Packages are easier for application designing, encapsulating data, additional functionality and better performance An application has various modules which can be placed in packages and handled easier

Correct Answer: Basic Replication : Basic replication is implemented using standard CREATE SNAPSHOT or CREATE MATERIALIZED VIEW statements It can only replicate data and not procedures, indexes replication is always one-way, and snapshot copies are read only Advanced Replication : Advanced replication supports various configurations of updatable snapshot, multi-master and update anywhere replication It is more difficult to configure but allows data and other database objects like indexes and procedures to be replicated Differences between Basic and Advanced replications: - With basic replication, data replicas provide read-only access to the table data whereas advanced replication features extend the capabilities of basic read-only replication by allowing applications to update table replicas throughout a replicated database system - With Basic Replication applications can query data from local data replicas On the other hand with advanced replication, data replicas anywhere in the system can provide both read and update access to a table's data

Correct Answer: Oracle HTTP Server is the Web server component of Oracle Database It is based on the Apache HTTP Server It is robust and reliable server due to following features: - Provide high availability infrastructure integration with Oracle Process Manager and Notification Server (OPMN), for process management, death detection and failover for Oracle HTTP Server processes - Provide Dynamic Monitoring Services (DMS) metrics that give runtime performance statistics for Oracle HTTP Server processes - Enable securing of transactions with Secure Sockets Layer (SSL) technology - Execute Perl scripts in the same process as the Oracle HTTP Server, or as CGI script

Correct Answer: Oracle HTTP Server is managed by OPMN which stands for Oracle Process Manager and Notification Server With the use of opmnctl utility we can start, stop and restart Oracle HTTP Server If we don?t use this utility the configuration management infrastructure cannot detect or communicate with the Oracle HTTP Server processes, and problems may occur The startproc command for the case of Windows is: ORACLE_HOME\opmn\bin> opmnctl [verbose] startproc ias-component=HTTP_Server To stop Oracle HTTP Server, use the stopproc command: Windows: ORACLE_HOME\opmn\bin> opmnctl [verbose] stopproc ias-component=HTTP_Server

Correct Answer: We use SQLPASSTHROUGH options when we need applications to access data both in the front-end and back-end data servers with a single connection SQL statements that cannot be executed in the solidDB front-end server are passed over to the back end SQLPASSTHROUGH mode can be set as per session or per transaction The connection between the front end and back end is made with a back-end compatible ODBC driver which is loaded dynamically in solidDB server

Correct Answer: Personal Oracle lite is single user database for distributed and mobile computing It supports a full range of Java database features as well as advanced replication Or in other words it is Oracle's lightweight, fully-functional, single-user object- relational database A small, zero administration embedded database Some of the features supported: - Java Support - Java Store Procedure Support - Java triggers Supported - Multi-column indexing and outer joins - Easy to use graphical user interface - Supports both object and relational database models - Simultaneous viewing of classes and relational tables