Monday, November 21, 2011

oracle interview questions

  • what is   materialized view?
A materialized view is a database object that stores the results of a query (possibly from a remote database). Materialized views are sometimes referred to as snapshots.
Materialized views are disk based and update periodically base upon the query definition.
Views are virtual only and run the query definition each time they are accessed

http://orafaq.com/wiki/Oracle_Materialized_Views
http://stackoverflow.com/questions/93539/what-is-the-difference-between-views-and-materialized-views-in-oracle

  • What are materialized views & where do we use them?

A view which has been materialized to become a "table"
The big difference between a View and a Materialized View is a View executes a query on-the-fly toreturn results.
A Materialized View executes a query but then stores (or caches) the results.
You can then query the MVIEW.

References:
http://www.dwbiconcepts.com/tutorial/10-interview/6-top-50-dwbi-interview-questions-with-answers.html
http://blog.sqlauthority.com/2007/07/26/sql-server-data-warehousing-interview-questions-and-answers-part-1/

  • what is rowid?

For each row in the database, the ROWID pseudocolumn returns the address of the row. Oracle Database rowid values contain information necessary to locate a row:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns008.htm

ROWID is a pseudo column attached to each row of a table.

It is 18 characters long, blockno, rownumber are the components of ROWID


Questions:
How do I limit the number of rows returned by a query?
How do I write a query to get the Top-N salaries from the employee table?
How can I add unique, sequential numbers to an existing table?
How can I differentiate between two completely identical rows?
How can I find a faster way to retrieve a queried row?
How can I find the last row processed in a big batch?

listing highest paid employees

select first_name,last_name,salary
from employee2
where rownum<4
order by salary desc;


ROWID actually represents the physical location of the record/row in the database.
The fastest way to retrieve a particular row. Faster than an index

Can you use ROWID to differentiate between duplicate rows?
Yes, you can
Since it actually represents the physical location of a row, no two rows within the same table will have the same ROWID.
If you're using clustering, two records from different tables could theoretically share the same ROWID.


http://thinkoracle.blogspot.com/2005/10/rownum-and-rowid.html

  • What is the difference between Instance and a Database?


Instance is the memory structures and background processes that are used to interact with Database.
Instance = SGA + Background Process

Database is the physical files that are used to store information.
3 types of physical files are:
Datafiles,
Control File
Redo-Log fileS(minimum 2).



  • What is stucture of Database ?
Oracle database usually contains one database and a single instance.
Oracle 9i, 10g RAC (Real Application Clusters) can have multiple instances to interact with a single database for high availability.

Instance is non-persistent, memory based background processes and structures.
Database is persistent, disk based, data and control files


Physical Structure of Database:
One or more datafiles, control file(s), Redolog file(s) and init.ora file

Logical Structure of Database:
Tablespaces, segments, extents, blocks

Reference:
http://www.geekinterview.com/question_details/16281
http://www.geekinterview.com/question_details/45132



  • What are the components of physical database structure of Oracle database?

Oracle database is comprised of three types of files.
One or more datafiles,
two are more redo log files,
one or more control files


  • What are the components of logical database structure of Oracle database?

tablespaces and database's schema objects.


  • What is a tablespace?

A database is divided into Logical Storage Unit which is called tablespace.
A tablespace is used to group related logical structures



  • What is SYSTEM tablespace and when is it created?

Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created.
The SYSTEM tablespace always contains the data dictionary tables for the entire database.


  • Explain the relationship among database, tablespace and data file

Each databases logically divided into one or more tablespaces one
or more data files are explicitly created for each tablespace


  • What is schema?

A schema is collection of database objects of a user.
The set of objects owned by user account is called the schema.


  • What are Schema Objects?

Schema objects are the logical structures that directly refer to the database's data.
Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links


  • Can objects of the same schema reside in different tablespaces?

Yes


  • Can a tablespace hold objects from different schemas?

Yes


  • What is an Oracle view?

A view is a virtual table. Every view has a query attached to it


  • Do a view contain data?

Views do not contain or store data


  • Can a view based on another view?

Yes


  • What are the advantages of views?

-Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.
- Hide data complexity.
- Simplify commands for the user.
- Present the data in a different perspective from that of the base table.
- Store complex queries


  • What is an Oracle sequence?

A sequence generates a serial list of unique numbers for numerical columns of a database's tables


  • What is a synonym?

A synonym is an alias for a table, view, sequence or program unit.Two types:private and public


  • What are synonyms used for?

- Mask the real name and owner of an object.
- Provide public access to an object
- Provide location transparency for tables, views or program units of a remote database.
- Simplify the SQL statements for database users.


  • What is an Oracle index?

An index is an optional structure associated with a table to have direct access to rows,
which can be created to increase the performance of data retrieval.
Index can be created on one or more columns of a table


  • How are the index updates?

Indexes are automatically maintained and used by Oracle.
Changes to table data are automatically incorporated into all relevant indexes



  • What are clusters?

Clusters are groups of one or more tables physically stores together to share common columns and are often used together
Group of tables physically stored together because they share common columns and are often used together is called cluster


  • What is data block?

Oracle database's data is stored in data blocks.
One data block corresponds to a specific number of bytes of physical database space on disk
A data block size is specified for each Oracle database when the database is created.
Block size is specified in init.ora file and can not be changed later.


  • What is an extent?

An extent is a specific number of contiguous data blocks,
obtained in a single allocation and used to store a specific type of information


  • What is a segment?

A segment is a set of extents allocated for a certain logical structure


  • What are the characteristics of data files?

A data file can be associated with only one database.
One or more data files form a logical unit of database storage called a tablespace


  • What is the function of redo log?

The primary function of the redo log is to record all changes that are made to data
The information in a redo log file is used only to recover the database from a system
or media failure prevents database data from being written to a database's data files



  • What is the use of control file?

When an instance of an Oracle database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed.
It is also used in database recovery.



  • What is a database instance?

A database instance (Server) is a set of memory structure and background processes that access a set of database files.
The processes can be shared by all of the users



  • What is SGA?

The System Global Area in an Oracle database is the area in memory to facilitate the transfer of information between users
The structure is database buffers, dictionary cache, redo log buffer and shared pool area.



  • What is a shared pool?

The data dictionary cache is stored in an area in SGA called the shared pool.
This will allow sharing of parsed SQL statements among concurrent users



  • What is meant by Program Global Area (PGA)?

It is an area in memory that is used by a single Oracle user process.


  • What is Database Buffers?

Database buffers are cache in the SGA used to hold the data blocks that are read from the data segments in the database


  • What is dictionary cache?

Dictionary cache is information about the database objects stored in a data dictionary table


  • What is a transaction?

Transaction is logical unit between two commits, commit and rollback

  • What is the usage of SAVEPOINTS?

SAVEPOINTS are used to subdivide a transaction into smaller parts.
It enables rolling back part of a transaction.
Maximum of five save points are allowed


  • What is ON DELETE CASCADE?

When ON DELETE CASCADE is specified, Oracle maintains referential integrity by automatically removing dependent foreign key values if a referenced primary or unique key value is removed.


Reference:
http://www.scribd.com/doc/7196849/Oracle-DBA-Interview-Questions-Nil




  • Explain the difference between a hot backup and a cold backup and the benefits associated with each.

A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode
A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode


  • Where would you look for errors from the database engine?

In the alert log


  • Differentiate between TRUNCATE and DELETE

The Delete command will log the data changes in the log file where as the truncate will simply remove the data without it.
Hence Data removed by Delete command can be rolled back but not the data removed by TRUNCATE.
Truncate is a DDL statement whereas DELETE is a DML statement.


  • Difference between procedure and function

A function always returns a value, while a procedure does not.
When you call a function you must always assign its value to a variable


  • What is a cursor?

A cursor is a mechanism by which you can assign a name to a “select statement” and manipulate the information within that SQL statement.


  • Difference between an implicit and an explicit cursor.

The implicit cursor is used by Oracle server to test and parse the SQL statements
the explicit cursors are declared by the programmers


  • What is difference between UNIQUE and PRIMARY KEY constraints?

An UNIQUE key can have NULL whereas PRIMARY key is always not NOT NULL.
Both bears unique values.



  • What is difference between SQL and SQL*PLUS?

SQL is the query language to manipulate the data from the database.
SQL*PLUS is the tool that lets you use SQL to fetch and display the data


  • What is the difference between alias and synonym ?

Alias is temporary and used with one query. Synonym is permanent and not used as alias.


  • What is the difference between a view and a synonym ?

Synonym is just a second name of table used for multiple link of database.
View can be created with many tables, and with virtual columns and with conditions.
But synonym can be on view


  • What is implicit cursor and how is it used by Oracle ?

An implicit cursor is a cursor which is internally created by Oracle.
It is created by Oracle for each individual SQL



  • What is a transaction ?

A transaction is a set of SQL statements between any two COMMIT and ROLLBACK statements.


  • What is PL/SQL?

PL/SQL is Oracle's Procedural Language extension to SQL


  • Explain the difference between a FUNCTION, PROCEDURE and PACKAGE

A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task.
While a procedure does not have to return any values to the calling application, a function will return a single value.
A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application


  • Describe what redo logs are.

Redo logs are logical and physical structures that are designed to hold all the changes made to a database and are intended to aid in the recovery of a database.


Reference:
http://www.scribd.com/doc/7196849/Oracle-DBA-Interview-Questions-Nil
http://techpreparation.com/computer-interview-questions/oracle-interview-questions-answers2.htm
http://www.databasejournal.com/features/oracle/article.php/3114381/Oracle-Technical-Interview-Questions-Answered---Part2.htm



  • what is oracle-partitioning?



Oracle Partitioning

Oracle Partitioning, an option of Oracle Database 11g Enterprise Edition, enhances the manageability, performance, and availability of a wide variety of applications. Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity.



Benefits

Improves the performance of certain queries and maintenance operations by an order of magnitude

Greatly reduces the total cost of data ownership using a tiered archiving approach that keeps older information on low cost storage devices



http://www.oracle.com/us/products/database/options/partitioning/overview/index.html




Explain what partitioning is and what its benefit is.
A table partition is also a table segment, and by using partitioning technique we can enhance performance of table access
http://www.orafaq.com/wiki/Interview_Questions

Table Partitioning
Now a days enterprises run databases of hundred of Gigabytes in size. 
These databases are known as Very Large Databases (VLDB)
Oracle has provided the feature of table partitioning i.e. you can partition a table according to some criteria . 
For example you have a SALES table with the following structure
Suppose this table contains millions of records, but all the records belong to four years only i.e. 1991, 1992, 1993  and 1994.  
And most of the time you are concerned about only one year

select sum(amt) from sales where year=1991;

whenever you give queries like this Oracle will search the whole table. 
If you partition this table according to year, then the performance is improve since oracle will scan only a single partition instead of whole table.
http://www.oracle-dba-online.com/sql/oracle_table_partition.htm

Partitioning
Oracle Partitioning is a separately licensed option of the Oracle database that is only available with the Enterprise Edition of the database. Partitioning allows DBAs to split large tables into more manageable "sub-tables", called partitions, to improve database performance, manageability and availability.

Partitioning is basically a divide-and-conquer approach to scale to very large database sizes. It improves manageability by allowing partitions to be added, loaded, indexed and removed while the database in on-line and users are working against these tables. Partitioning can potentially make the database faster the bigger it gets.

http://www.orafaq.com/wiki/Partitioning





  • What are HINTS in ORACLE?


HINTS  are nothing but the comments used in a SQL statement to pass instructions to the Oracle optimizer
The optimizer uses these hints as suggestions for choosing an execution plan for the statement.

A statement block can have only one comment containing hints, and that comment must follow the SELECT, UPDATE, INSERT, or DELETE keyword.
http://www.geekinterview.com/question_details/32950



Hint are used to alter execution plans of SQL statements. by using hints you will be able to choose the execution plans.

or eg. in a SELECT statement your will be able to specify whether an index is to be used or not . If you want to use and index, You can also specify which index is to  be used.

The following query won't use any index even if there is an index on name.

SELECT /*+ FULL(e) */ id, name
  FROM employees e 
  WHERE name LIKE 'J%';
The above query will give you better results if the names starting with 'J' are more than 25% of total rows.

http://www.geekinterview.com/question_details/37579


How to use hints in Oracle sql for performance
With hints one can influence the optimizer. The usage of hints (with exception of the RULE-hint) causes Oracle to use the Cost Based optimizer.
The following syntax is used for hints:

select /*+ HINT */ name
from emp
where id =1;

Where HINT is replaced by the hint text. 
When the syntax of the hint text is incorrect, the hint text is ignored and will not be used.

http://www.oradev.com/hints.jsp


Optimizer hints can be used with SQL statements to alter execution plans.
Hints let you make decisions usually made by the optimizer. As an application designer, you might know information about your data that the optimizer does not know. Hints provide a mechanism to instruct the optimizer to choose a certain query execution plan based on the specific criteria.

http://docs.oracle.com/cd/B19306_01/server.102/b14211/hintsref.htm




  • Decode Function

In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement.

You could use the decode function in an SQL statement as follows:

    SELECT supplier_name,
    decode(supplier_id, 10000, 'IBM',
    10001, 'Microsoft',
    10002, 'Hewlett Packard',
    'Gateway') result
    FROM suppliers;


The above decode statement is equivalent to the following IF-THEN-ELSE statement:

    IF supplier_id = 10000 THEN
         result := 'IBM';

    ELSIF supplier_id = 10001 THEN
        result := 'Microsoft';

    ELSIF supplier_id = 10002 THEN
        result := 'Hewlett Packard';

    ELSE
        result := 'Gateway';

    END IF;


http://www.techonthenet.com/oracle/functions/decode.php


  • how to use wild chars in sql

decode(     xx.column1,0,0,((xx.column2/xx.column3)*100) )  as "BILLING %",



  • list of all users but all users may not have created objects in it.So we can not call them as schema.

select username from dba_users;




  • defining directory output in oracle

CREATE DIRECTORY dir_reports_allparners AS 'c:\reports\mydocz';
GRANT READ, WRITE ON directory DIR_REPORTS_MYDOCZ TO schemaname;
select * from dba_directories;

  • how to backup table with contents
CREATE TABLE schema_name.table1_backup AS SELECT * FROM schema_name.table;


  • how to apply time difference

and trunc(event.creation_date+country.time_diff/24)>='09-07-2009'

trunc(dd.creation_date)>='21-Jan-2018'       and trunc(dd.creation_date)>= trunc(aa.creation_date)


timestamp
AND (event.CREATION_DATE+(CN.TIME_DIFF/24))>= to_timestamp('2018/12/22 00:00:00', 'YYYY/MM/DD HH24:MI:SS')       
AND (event.CREATION_DATE+(CN.TIME_DIFF/24))< to_timestamp('2018/12/28 23:59:59', 'YYYY/MM/DD HH24:MI:SS')



sysdate
AND TRUNC(event.CREATION_DATE+(CN.TIME_DIFF/24))>= trunc(sysdate-7)        
AND TRUNC(event.CREATION_DATE+(CN.TIME_DIFF/24))<  trunc(sysdate) 


  • To_Timestamp Function
In Oracle/PLSQL, the to_timestamp function converts a string to a timestamp.



    to_timestamp('2003/12/13 10:13:18', 'YYYY/MM/DD HH:MI:SS')

would return '13-DEC-03 10.13.18.000000000 AM' as a timestamp value.

    to_timestamp('2003/DEC/13 10:13:18', 'YYYY/MON/DD HH:MI:SS')

would also return '13-DEC-03 10.13.18.000000000 AM' as a timestamp value.

http://www.techonthenet.com/oracle/functions/to_timestamp.php


  • Trunc Function (with dates)

In Oracle/PLSQL Built-In Functions, the trunc function returns a date truncated to a specific unit of measure.

The syntax for the trunc function is:

trunc ( date, [ format ] )


trunc(to_date('22-AUG-03'), 'YEAR') would return '01-JAN-03'
trunc(to_date('22-AUG-03'), 'Q') would return '01-JUL-03'
trunc(to_date('22-AUG-03'), 'MONTH') would return '01-AUG-03'
trunc(to_date('22-AUG-03'), 'DDD') would return '22-AUG-03'
trunc(to_date('22-AUG-03'), 'DAY') would return '17-AUG-03'

http://www.techonthenet.com/oracle/functions/trunc_date.php



  • To_Char Function

In Oracle/PLSQL, the to_char function converts a number or date to a string.

The following are date examples for the to_char function.

    to_char(sysdate, 'yyyy/mm/dd'); would return '2003/07/09'
    to_char(sysdate, 'Month DD, YYYY'); would return 'July 09, 2003'
    to_char(sysdate, 'FMMonth DD, YYYY'); would return 'July 9, 2003'
    to_char(sysdate, 'MON DDth, YYYY'); would return 'JUL 09TH, 2003'
    to_char(sysdate, 'FMMON DDth, YYYY'); would return 'JUL 9TH, 2003'
    to_char(sysdate, 'FMMon ddth, YYYY'); would return 'Jul 9th, 2003'
    
http://www.techonthenet.com/oracle/functions/to_char.php



  • oracle table backup

create table test2 as select * from test1;

  • the list of schemas available
select distinct owner from dba_objects;

  • connect as sys

sqlplus /nolog
connect sys/passwd@db as sysdba;


  • enable archivelog

shutdown immediate

startup mount

alter database archivelog;
alter database open;

  • disable archivelog
shutdown immediate
startup mount
alter database noarchivelog;
alter database open;
archive log list;

to see whether database is in archivelog mode
select log_mode from v$database;
OR
archive log list


  • what is limits of output of between sql query?

employee2 table


02 Alison
03 James
04 Celia
05 Robert
06 Linda
07 David
08 James
13 New





select id,first_name
from employee2 
where id between 4 and 8;



04 Celia
05 Robert
06 Linda
07 David
08 James



  • ROWNUM is a "pseudocolumn" that assigns a number to each row returned by a query

http://stackoverflow.com/questions/174595/in-an-oracle-database-what-is-the-difference-between-rownum-and-row-number

ROWNUM is a pseudo-column that returns a row's position in a result set.
ROWNUM is evaluated AFTER records are selected from the database and BEFORE the execution of ORDER BY clause
http://www.orafaq.com/wiki/ROWNUM


Question:  Does Oracle make a distinction between a ROWID and ROWNUM?  If so, what is the difference between ROWNUM and ROWID?

Answer:  Just as your home address uniquely identifies where you live, an Oracle ROWID uniquely identifies where a row resides on disk.  The information in a ROWID gives Oracle everything he needs to find your row, the disk number, the cylinder, block and offset into the block.
The ROWNUM is a "pseudo-column", a placeholder that you can reference in SQL*Plus.
http://www.dba-oracle.com/t_rownum_rowid_difference.htm




listing highest paid employees

select first_name,last_name,salary
from employee2
where rownum<4
order by salary desc;


ROWID actually represents the physical location of the record/row in the database.
The fastest way to retrieve a particular row. Faster than an index

Can you use ROWID to differentiate between duplicate rows?
Yes, you can
Since it actually represents the physical location of a row, no two rows within the same table will have the same ROWID.
If you're using clustering, two records from different tables could theoretically share the same ROWID.







No comments:

Post a Comment