what is materialized view?
Materialized views are disk based and update periodically base upon the query definition.
Views are virtual only and run the query definition each time
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
http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns008.htm
ROWID is a
It is 18 characters long,
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
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
from employee2
where rownum<4
order by salary desc;
ROWID
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
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
Instance = SGA + Background Process
Database is the physical files that
3 types of physical files are:
Control File
Redo-Log
- What is
stucture of Database?
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
Logical Structure of Database:
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?
One or more
two are more redo log files,
one or more control files
- What are the components of logical database structure of Oracle database?
- What is a
tablespace ?
A
- What is SYSTEM
tablespace and when is it created?
Every Oracle database contains a
The SYSTEM
- Explain the relationship among database,
tablespace and data file
Each databases logically divided into one or more
or
- What is schema?
A schema is
- 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 schemareside in differenttablespaces ?
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
- 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,
- How are the index updates?
Indexes
Changes to table data
- What are clusters?
Clusters are groups of one or more tables physically stores together to share common columns and
Group of tables physically stored together because they share common columns and
- What is
data block?
One data block corresponds to a specific number of bytes of physical database space on disk
- What is an extent?
An extent is a specific number of contiguous data blocks,
- What is a segment?
A segment is a set of extents allocated for a certain logical structure
- What are the characteristics of data files?
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
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
- 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
from employee2
where rownum<4
order by salary desc;
ROWID
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
If you're using clustering, two records from different tables could theoretically share the same ROWID.
