Interview important things to remember

ORACLE

Query Tuning- usage of indexes, how to forcefully use indexes, # hints in sql query like
select /* + index idx_abc */

how to check CPU consumption of a particular query.

Views through which we can take out query details.

V$sql_text

Trace Generation and their study.

# alter session set sql_trace=true, tkprof filename then output file. Trace generated at
user_dump_dest.

Explain plan and its study.

# Utlxplan.sql, explain plan for <SQL statement>, table created is plan_table. Other
utilities for report generation are utlxpls.sql and utlxplp.sql.

Recovery through Archival

# recover database automatic using backup controlfile;

Hot backups:- Taking tablespace offline and starting backup.

Cold backups:- Bringing down the database and backing up datafiles, log files and
controlfiles.

Types of indexes and their usages # Partitioned indexes (generally for partitioned
tables), normal indexes or b-tree indexes (generally for OLTP trans) and Bitmap indexes
(generally for datawarehouses and too costly updates)

How to find culprit queries :-

Joining v$session, v$sqltext and v$sql., users can be taken from v$session and joining
them with v$sql on v$session.SQL_ADDRESS and v$sql.ADDRESS and then joining
this view with v$sqltext on HASH_VALUE of sql.

How would you determine what sessions are connected and what resources they are
waiting for?
Use of V$SESSION and V$SESSION_WAIT

How to kill culprit queries, through Oracle and OS.

Alter session kill (sid, serial#); sid and serial# can be checked through v$session.

Database running slow, how to check?

Database errors
# alert.log

Hot Backups
# rman implementation

Difference and meaning of Oracle Home and Oracle Base

Error Snapshot too old.

Due to rollback segments, if query is taking too long and in the meantime unlocked data
got updated before updation of main query.

Analyze table, command, need, resources etc.

GLOBAL_DATABASE_NAME # meaning, usage etc.

Starting from spfile or pfile #difference and methods. During startup, system always
check for spfile, if not found then pfile.

Parameter to control size of SGA

MAX_SGA_SIZE

High water mark

It is a level above which system loads data in direct load, it do not consider block space
that was used anytime for data and later on was deleted. ( loader etc)

Diff between truncate command and delete command on a table.

Truncate command is a DDL command whereas delete is a DML command, so difference
of committing. Truncate command releases storage spaces, whereas delete command do
not. Truncate command do not generate any rollback whereas delete command do. In
truncate command delete trigger is not fired.

Difference between create or replace a function/package & drop and create command.
In drop command all privileges granted to users/roles etc are dropped but in case of
create or replace they remain as it is.

Can we use commit in triggers.

Commit, savepoint and rollback not allowed in triggers.

How to check costing of a particular SQL.

How to check compilation errors of PL/SQL.

Difference between SQL loader and Import utility.

Explain two kernel parameters that you need to set before installation of database.
LD_LIBRARY_PATH, ORACLE_HOME

What are HOT tables

How to take backup of a controlfile.

IBM AIX

Checking Physical volumes

lspv for physical volumes and internal details lspv -l

Checking Volume groups

lsvg for volume groups and internal details with lsvg -l

Checking logical volumes

lslv for checking listing logical volumes.

How to create volume group, logical volume and File system.

Through smitty

System Activity report (sar)

Sar <time diff in sec.s> < number of times>

Different types of kill commands

For space management in File systems, removal of extra spaces.

Meaning of
iostat
vmstat
netstat

Changing occurrence of a particular string in a file in vi editor.

Listing hidden files.

Other questions to remember

Create database kps

Logfile
Group 1 (‘e:\oracle\oradata\kps\redo01.log’) size 10M,
Group 2 (‘e:\oracle\oradata\kps\redo02.log’) size 10M
Datafile ‘e:\oracle\oradata\kps\system01.dbf’ size 100M
Undo talblespace undotbs datafile ‘e:\oracle\oradata\kps\undo01.dbf’ size 100M
Default temporary tablespace temp
Character set US7ASCII;

1.Explain 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, and one or more control files.

2. Explain the components of logical database structure of Oracle

database?
There are tablespaces and database's schema objects.

3. Explain a tablespace?
A database is divided into Logical Storage Unit called tablespaces. A tablespace
is used to grouped related logical structures together.

4. Explain 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.

5. 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.

6. Explain schema?
A schema is collection of database objects of a user.

7. Explain 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.

8. Can objects of the same schema reside in different tablespaces?

Yes.

9. Can a tablespace hold objects from different schemes?

Yes.

10. Explain Oracle table?

A table is the basic unit of data storage in an Oracle database. The tables of a
database hold all of the user accessible data. Table data is stored in rows and
columns.
11. Explain an Oracle view?
A view is a virtual table. Every view has a query attached to it. (The query is a
SELECT statement that identifies the columns and rows of the table(s) the view
uses.)

12. Do a view contain data?

Views do not contain or store data.

13. Can a view based on another view?

Yes.

14. Explain 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.

15. Explain an Oracle sequence?

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

16. Explain a synonym?

A synonym is an alias for a table, view, sequence or program unit.

17. Explain the types of synonyms?

There are two types of synonyms private and public.

18. Explain a private synonym?

Only its owner can access a private synonym.

19. Explain a public synonym?

Any database user can access a public synonym..

20. Explain 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

21. Explain 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.

22. How are the index updates?

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

23. Explain clusters?

Clusters are groups of one or more tables physically stores together to share
common columns and are often used together.

24. Explain cluster key?

The related columns of the tables in a cluster are called the cluster key.

25. Explain index cluster?

A cluster with an index on the cluster key.

26. Explain hash cluster?

A row is stored in a hash cluster based on the result of applying a hash function
to the row's cluster key value. All rows with the same hash key value are stores
together on disk.

27. When can hash cluster used?

Hash clusters are better choice when a table is often queried with equality
queries. For such queries the specified cluster key value is hashed. The resulting
hash key value points directly to the area on disk that stores the specified rows.

28. Explain database link?

A database link is a named object that describes a "path" from one database to
another.

29. Explain the types of database links?

Private database link, public database link & network database link.

30. Explain private database link?

Private database link is created on behalf of a specific user. A private database
link can be used only when the owner of the link specifies a global object name in
a SQL statement or in the definition of the owner's views or procedures.

31. Explain public database link?

Public database link is created for the special user group PUBLIC. A public
database link can be used when any user in the associated database specifies a
global object name in a SQL statement or object definition.

32. Explain network database link?

Network database link is created and managed by a network domain service. A
network database link can be used when any user of any database in the
network specifies a global object name in a SQL statement or object definition.

33. Explain 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.

34. How to define data block size?

A data block size is specified for each Oracle database when the database is
created. A database users and allocated free database space in Oracle data
blocks. Block size is specified in init.ora file and cannot be changed latter.

35. Explain row chaining?

In circumstances, all of the data for a row in a table may not be able to fit in the
same data block. When this occurs, the data for the row is stored in a chain of
data block (one or more) reserved for that segment.

36. Explain 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.

37. Explain a segment?

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

38. Explain the different types of segments?

Data segment, index segment, rollback segment and temporary segment.

39. Explain a data segment?

Each non-clustered table has a data segment. All of the table's data is stored in
the extents of its data segment. Each cluster has a data segment. The data of
every table in the cluster is stored in the cluster's data segment.

40. Explain an index segment?

Each index has an index segment that stores all of its data.

41. Explain rollback segment?

A database contains one or more rollback segments to temporarily store "undo"
information.

42. Explain the uses of rollback segment?

To generate read-consistent database information during database recovery and
to rollback uncommitted transactions by the users.

43. Explain a temporary segment?

Temporary segments are created by Oracle when a SQL statement needs a
temporary work area to complete execution. When the statement finishes
execution, the temporary segment extents are released to the system for future
use.

44. Explain a datafile?

Every Oracle database has one or more physical data files. A database's data
files contain all the database data. The data of logical database structures such
as tables and indexes is physically stored in the data files allocated for a
database.

45. Explain the characteristics of data files?

A data file can be associated with only one database. Once created a data file
can't change size. One or more data files form a logical unit of database storage
called a tablespace.

46. Explain a redo log?

The set of redo log files for a database is collectively known as the database
redo log.

47. Explain the function of redo log?

The primary function of the redo log is to record all changes made to data.

48. Explain the use of redo log information?

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.

49. What does a control file contains?

- Database name
- Names and locations of a database's files and redolog files.
- Time stamp of database creation.

50. Explain 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.

1. What DBA activities did you to do today?

Wow, this is a loaded question and almost begs for you to answer it with "What
DBA activities do you LIKE to do on a daily basis?." And that is how I would
answer this question. Again, do not get caught up in the "typical" day-to-day
operational issues of database administration. Sure, you can talk about the index
you rebuilt, the monitoring of system and session waits that were occurring, or
the space you added to a data file, these are all good and great and you should
convey that you understand the day-to-day operational issues. What you should
also throw into this answer are the meetings that you attend to provide direction
in the database arena, the people that you meet and talk with daily to answer
adhoc questions about database use, the modeling of business needs within the
database, and the extra time you spend early in the morning or late at night to
get the job done. Just because the question stipulates "today" do not take "today"
to mean "today." Make sure you wrap up a few good days into "today" and talk
about them. This question also begs you to ask the question of "What typical
DBA activities are performed day to day within X Corporation?"

2. What is your typical day like?

If you spend enough time on question 1, this question will never be asked. It is
really a continuation of question 1 to try and get you to open up and talk about
the type of things you like to do. Personally, I would continue with the theme of
question 1 if you are cut short or this question is asked later in the interview
process. Just note that this question is not all geared toward the day-to-day
operational issues you experience as a DBA. This question also gives you the
opportunity to see if they want to know about you as an individual. Since the
question did not stipulate "on the job" I would throw in a few items like, I get up at
5:00am to get into work and get some quiet time to read up on new trends or you
help coach your son/daughter's soccer team. Just test the waters to what is
acceptable. If the interviewer starts to pull you back to "job" related issues, do not
go to personal. Also, if you go to the office of the interviewer please notice the
surroundings, if there are pictures of his/her family, it is probably a good idea to
venture down the personal path. If there is a fly-fishing picture on the wall, do not
say you like deep-sea fishing. You get the picture.

3. What other parts of your organization do you interact with and how?
Again, if you have exhausted question 1 and 2 you may never get to this
question. But if you have been apprehensive to opening up and explaining
yourself, take note that you may have an issue and the interviewer might also be
already getting tired of the interview process. If you get to this question consider
yourself in trouble. You really need to forget all your hang-ups and start
explaining what it is that you like to do as a DBA, and why you want to work for
this particular company. You are going to have to reel this interviewer back into
the interview process or you might not get to the true technical question part of
the interview.

4. Do you consider yourself a development DBA or a production DBA and

why?
I take this as a trick question and explain it that way. Never in my database
carrier have I distinguished between "development" and "production." Just ask
your development staff or VP of engineering how much time and money is lost if
development systems are down. Explain to the interviewer that both systems are
equally important to the operation of the company and both should be considered
as production systems because there are people relying on them and money is
lost if either one of them is down. Ok you may be saying, and I know you are,
that we lose more money if the production system is down. Ok, convey that to the
interviewer and you won't get anyone to disagree with you unless your company
sells software or there are million dollar deals on the table that are expecting the
next release of your product or service.

5. Are you a nuts-n-bolts DBA or a tools-n-props DBA

This question begs for me to give definition around the terms I basically group
DBAs into. These are not good or bad groups but something I like to think about
when talking to DBAs. A nuts-n-bolts DBA is the type that likes to figure out every
little item about how the database works. He/she is a DBA who typically hates a
GUI environment and prefers the command line to execute commands and
accomplish tasks. A nuts-n-bolts DBA like to feel in control of the database and
only feels comfortable at the command line and vi as an editor. The tools-n-props
DBA is mostly the opposite of a nuts-n-bolts DBA, they like the feel of a GUI, the
ease at which things can be accomplished without knowing much about the
database. They want to get the job done with the least amount of intervention
from having to figure out what everything is doing behind the scenes. Now the
answer, I would explain myself as a combination of the two. I, having been in this
business for over 20 years, have grown up in a command line era where the
GUIs never seemed to work. There was high complexity in systems and not
much good documentation on how things worked. Thus, I had to learn everything
about most aspects of the database environment I was working in and thus
became a nuts-n-bolts DBA. I was a true command line and vi bigot. Times have
changed and the GUIs are very reliable, understand the environment they are
installed on, and can generally get the job done quicker for individuals new to
database administration. I too am slowly slipping over to the dark side of GUI
administration. If you find yourself as a tools-n-props DBA, try to convey that you
are aware of some tasks that require you to be a nuts-n-bolts DBA.

1. 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. The benefit of taking a hot backup is that the database is still available for
use while the backup is occurring and you can recover the database to any point
in time. The benefit of taking a cold backup is that it is typically easier to
administer the backup and recovery process. In addition, since you are taking
cold backups the database does not require being in archive log mode and thus
there will be a slight performance gain as the database is not cutting archive logs
to disk.
2. You have just had to restore from backup and do not have any control
files. How would you go about bringing up this database?
I would create a text based backup control file, stipulating where on disk all the
data files where and then issue the recover command with the using backup
control file clause.
3. How do you switch from an init.ora file to a spfile?
Issue the create spfile from pfile command.
4. Explain the difference between a data block, an extent and a segment.
A data block is the smallest unit of logical storage for a database object. As
objects grow they take chunks of additional storage that are composed of
contiguous data blocks. These groupings of contiguous data blocks are called
extents. All the extents that an object takes when grouped together are
considered the segment of the database object.

5. Give two examples of how you might determine the structure of the table
DEPT.
Use the describe command or use the dbms_metadata.get_ddl package.

6. Where would you look for errors from the database engine?
In the alert log.

7. Compare and contrast TRUNCATE and DELETE for a table.

Both the truncate and delete command have the desired outcome of getting rid of
all the rows in a table. The difference between the two is that the truncate
command is a DDL operation and just moves the high water mark and produces
a now rollback. The delete command, on the other hand, is a DML operation,
which will produce a rollback and thus take longer to complete.

8. Give the reasoning behind using an index.

Faster access to data blocks in a table.

9. Give the two types of tables involved in producing a star schema and the
type of data they hold.
Fact tables and dimension tables. A fact table contains measurements while
dimension tables will contain data that will help describe the fact tables.

10. . What type of index should you use on a fact table?

A Bitmap index.

11. Give two examples of referential integrity constraints.

A primary key and a foreign key.

12. A table is classified as a parent table and you want to drop and re-create
it. How would you do this without affecting the children tables?
Disable the foreign key constraint to the parent, drop the table, re-create the
table, enable the foreign key constraint.

13. Explain the difference between ARCHIVELOG mode and

NOARCHIVELOG mode and the benefits and disadvantages to each.
ARCHIVELOG mode is a mode that you can put the database in for creating a
backup of all transactions that have occurred in the database so that you can
recover to any point in time. NOARCHIVELOG mode is basically the absence of
ARCHIVELOG mode and has the disadvantage of not being able to recover to
any point in time. NOARCHIVELOG mode does have the advantage of not
having to write transactions to an archive log and thus increases the performance
of the database slightly.

14. What command would you use to create a backup control file?
Alter database backup control file to trace.
15. Give the stages of instance startup to a usable state where normal
users may access it.
STARTUP NOMOUNT - Instance startup
STARTUP MOUNT - The database is mounted
STARTUP OPEN - The database is opened

16. What column differentiates the V$ views to the GV$ views and how?
The INST_ID column which indicates the instance in a RAC environment the
information came from.

17. How would you go about generating an EXPLAIN plan?

Create a plan table with utlxplan.sql.
Use the explain plan set statement_id = 'tst1' into plan_table for a SQL statement
Look at the explain plan with utlxplp.sql or utlxpls.sql

18. How would you go about increasing the buffer cache hit ratio?
Use the buffer cache advisory over a given workload and then query the
v$db_cache_advice table. If a change was necessary then I would use the alter
system set db_cache_size command.

19. Explain an ORA-01555

You get this error when you get a snapshot too old within rollback. It can usually
be solved by increasing the undo retention or increasing the size of rollbacks.
You should also look at the logic involved in the application getting the error
message.

20. Explain the difference between $ORACLE_HOME and $ORACLE_BASE.

ORACLE_BASE is the root directory for oracle. ORACLE_HOME located
beneath ORACLE_BASE is where the oracle products reside.

21. How would you determine the time zone under which a database was
operating?
select DBTIMEZONE from dual;

22. Explain the use of setting GLOBAL_NAMES equal to TRUE.

Setting GLOBAL_NAMES dictates how you might connect to a database. This
variable is either TRUE or FALSE and if it is set to TRUE it enforces database
links to have the same name as the remote database to which they are linking.
23. What command would you use to encrypt a PL/SQL application?
WRAP

24. 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.

25. Explain the use of table functions.

Table functions are designed to return a set of rows through PL/SQL logic but are
intended to be used as a normal table or view in a SQL statement. They are also
used to pipeline information in an ETL process.

26. Name three advisory statistics you can collect.

Buffer Cache Advice, Segment Level Statistics, & Timed Statistics

27. Where in the Oracle directory tree structure are audit traces placed?
In unix $ORACLE_HOME/rdbms/audit, in Windows the event viewer

28. Explain materialized views and how they are used.

Materialized views are objects that are reduced sets of information that have
been summarized, grouped, or aggregated from base tables. They are typically
used in data warehouse or decision support systems.

29. When a user process fails, what background process cleans up after it?

PMON

30. What background process refreshes materialized views?

The Job Queue Processes.

31. How would you determine what sessions are connected and what
resources they are waiting for?
Use of V$SESSION and V$SESSION_WAIT

32. 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.
33. How would you force a log switch?
ALTER SYSTEM SWITCH LOGFILE;

34. Give two methods you could use to determine what DDL changes have
been made.
You could use Logminer or Streams

35. What does coalescing a tablespace do?

Coalescing is only valid for dictionary-managed tablespaces and de-fragments
space by combining neighboring free extents into large single extents.
36. What is the difference between a TEMPORARY tablespace and a
PERMANENT tablespace?
A temporary tablespace is used for temporary objects such as sort structures
while permanent tablespaces are used to store those objects meant to be used
as the true objects of the database.

37. Name a tablespace automatically created when you create a database.

The SYSTEM tablespace.

38. When creating a user, what permissions must you grant to allow them
to connect to the database?
Grant the CONNECT to the user.

39. How do you add a data file to a tablespace?

ALTER TABLESPACE
ADD DATAFILE <datafile_name> SIZE <size>

40. How do you resize a data file?

ALTER DATABASE DATAFILE <datafile_name> RESIZE <new_size>;

41. What view would you use to look at the size of a data file?
DBA_DATA_FILES

42. What view would you use to determine free space in a tablespace?
DBA_FREE_SPACE

43. How would you determine who has added a row to a table?
Turn on fine grain auditing for the table.

44. How can you rebuild an index?

ALTER INDEX <index_name> REBUILD;

45. Explain what partitioning is and what its benefit is.

Partitioning is a method of taking large tables and indexes and splitting them into
smaller, more manageable pieces.
46. You have just compiled a PL/SQL package but got errors, how would
you view the errors?
SHOW ERRORS

47. How can you gather statistics on a table?

The ANALYZE command.

48. How can you enable a trace for a session?

Use the DBMS_SESSION.SET_SQL_TRACE or
Use ALTER SESSION SET SQL_TRACE = TRUE;
49. What is the difference between the SQL*Loader and IMPORT utilities?
These two Oracle utilities are used for loading data into the database. The
difference is that the import utility relies on the data being produced by another
Oracle utility EXPORT while the SQL*Loader utility allows data to be loaded that
has been produced by other utilities from different data sources just so long as it
conforms to ASCII formatted or delimited files.

50. Name two files used for network connection to a database.

TNSNAMES.ORA and SQLNET.ORA

1.To see current user name

Sql> show user;

2. Change SQL prompt name

SQL> set sqlprompt ?Manimara > ?
Manimara >
Manimara >

3. Switch to DOS prompt

SQL> host

4. How do I eliminate the duplicate rows ?

SQL> delete from table_name where rowid not in (select max(rowid) from table
group by duplicate_values_field_name);
or
SQL> delete duplicate_values_field_name dv from table_name ta where rowid
<(select min(rowid) from table_name tb where ta.dv=tb.dv);
Example.
Table Emp
Empno Ename
101 Scott
102 Jiyo
103 Millor
104 Jiyo
105 Smith
delete ename from emp a where rowid < ( select min(rowid) from emp b where
a.ename = b.ename);
The output like,
Empno Ename
101 Scott
102 Millor
103 Jiyo
104 Smith

5. How do I display row number with records?

To achive this use rownum pseudocolumn with query, like SQL> SQL> select
rownum, ename from emp;
Output:
1 Scott
2 Millor
3 Jiyo
4 Smith

6. Display the records between two range

select rownum, empno, ename from emp where rowid in
(select rowid from emp where rownum <=&upto
minus
select rowid from emp where rownum<&Start);
Enter value for upto: 10
Enter value for Start: 7
ROWNUM EMPNO ENAME
--------- --------- ----------
1 7782 CLARK
2 7788 SCOTT
3 7839 KING
4 7844 TURNER
7. I know the nvl function only allows the same data type(ie. number or char or
date Nvl(comm, 0)), if commission is null then the text ?Not Applicable? want to
display, instead of blank space. How do I write the query?
SQL> select nvl(to_char(comm.),'NA') from emp;
Output :
NVL(TO_CHAR(COMM),'NA')
-----------------------
NA
300
500
NA
1400
NA
NA
8. Oracle cursor : Implicit & Explicit cursors
Oracle uses work areas called private SQL areas to create SQL statements.
PL/SQL construct to identify each and every work are used, is called as Cursor.
For SQL queries returning a single row, PL/SQL declares all implicit cursors.
For queries that returning more than one row, the cursor needs to be explicitly
declared.

9. Explicit Cursor attributes

There are four cursor attributes used in Oracle
cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT,
cursor_name%ISOPEN

10. Implicit Cursor attributes

Same as explicit cursor but prefixed by the word SQL
SQL%Found, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN
Tips : 1. Here SQL%ISOPEN is false, because oracle automatically closed the
implicit cursor after executing SQL statements.
: 2. All are Boolean attributes.

11. Find out nth highest salary from emp table

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT
(DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
Enter value for n: 2
SAL
---------
3700

12. To view installed Oracle version information

SQL> select banner from v$version;

13. Display the number value in Words

SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))
from emp;
the output like,
SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))
--------- -----------------------------------------------------
800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty
If you want to add some text like,
Rs. Three Thousand only.
SQL> select sal "Salary ",
(' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| ' only.'))
"Sal in Words" from emp
/
Salary Sal in Words
------- ------------------------------------------------------
800 Rs. Eight Hundred only.
1600 Rs. One Thousand Six Hundred only.
1250 Rs. One Thousand Two Hundred Fifty only.

14. Display Odd/ Even number of records

Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
1
3
5
Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
2
4
6

15. Which date function returns number value?

months_between

16. Any three PL/SQL Exceptions?

Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others

17. What are PL/SQL Cursor Exceptions?

Cursor_Already_Open, Invalid_Cursor

18. Other way to replace query result null value with a text
SQL> Set NULL ?N/A?
to reset SQL> Set NULL ??

19. What are the more common pseudo-columns?

SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM

20. What is the output of SIGN function?

1 for positive value,
0 for Zero,
-1 for Negative value.

21. What is the maximum number of triggers, can apply to a single table?
12 triggers.

1) What are the Back ground processes in Oracle and what are they.
1) This is one of the most frequently asked question.There are basically 9
Processes but in a general system we need to mention the first five background
processes.They do the house keeping activities for the Oracle and are common
in any system.
The various background processes in oracle are
a) Data Base Writer(DBWR) :: Data Base Writer Writes Modified blocks from
Database buffer cache to Data Files.This is required since the data is not written
whenever a transaction is commited.
b)LogWriter(LGWR) :: LogWriter writes the redo log entries to disk. Redo Log
data is generated in redo log buffer of SGA. As transaction commits and log
buffer fills, LGWR writes log entries into a online redo log file.
c) System Monitor(SMON) :: The System Monitor performs instance recovery at
instance startup.This is useful for recovery from system failure
d)Process Monitor(PMON) :: The Process Monitor peforms process recovery
when user Process fails. Pmon Clears and Frees resources that process was
using.
e) CheckPoint(CKPT) :: At Specified times, all modified database buffers in SGA
are written to data files by DBWR at Checkpoints and Updating all data files and
control files of database to indicate the
most recent checkpoint
f)Archieves(ARCH) :: The Archiver copies online redo log files to archival storal
when they are busy.
g) Recoveror(RECO) :: The Recoveror is used to resolve the distributed
transaction in network
h) Dispatcher (Dnnn) :: The Dispatcher is useful in Multi Threaded Architecture
i) Lckn :: We can have upto 10 lock processes for inter instance locking in
parallel sql.

2) How many types of Sql Statements are there in Oracle

2) There are basically 6 types of sql statments.They are
a) Data Defination Language(DDL) :: The DDL statments define and maintain
objects and drop objects.
b) Data Manipulation Language(DML) :: The DML statments manipulate
database data.
c) Transaction Control Statements :: Manage change by DML
d) Session Control :: Used to control the properties of current session enabling
and disabling roles and changing .e.g :: Alter Statements,Set Role
e) System Control Statements :: Change Properties of Oracle Instance .e.g:: Alter
System
f) Embedded Sql :: Incorporate DDL,DML and T.C.S in Programming
Language.e.g:: Using the Sql Statements in languages such as 'C', Open,Fetch,
execute and close

3) What is a Transaction in Oracle

3) A transaction is a Logical unit of work that compromises one or more SQL
Statements executed by a single User. According to ANSI, a transaction begins
with first executable statment and ends when it is explicitly commited or rolled
back.

4) Key Words Used in Oracle

4) The Key words that are used in Oracle are ::
a) Commiting :: A transaction is said to be commited when the transaction makes
permanent changes resulting from the SQL statements.
b) Rollback :: A transaction that retracts any of the changes resulting from SQL
statements in Transaction.
c) SavePoint :: For long transactions that contain many SQL statements,
intermediate markers or savepoints are declared. Savepoints can be used to
divide a transactino into smaller points.
d) Rolling Forward :: Process of applying redo log during recovery is called rolling
forward.
e) Cursor :: A cursor is a handle ( name or a pointer) for the memory associated
with a specific stament. A cursor is basically an area allocated by Oracle for
executing the Sql Statement. Oracle uses an implicit cursor statement for Single
row query and Uses Explcit cursor for a multi row query.
f) System Global Area(SGA) :: The SGA is a shared memory region allocated by
the Oracle that contains Data and control information for one Oracle Instance.It
consists of Database Buffer Cache and Redo log Buffer.
g) Program Global Area (PGA) :: The PGA is a memory buffer that contains data
and control information for server process.
g) Database Buffer Cache :: Databese Buffer of SGA stores the most recently
used blocks of datatbase data.The set of database buffers in an instance is
called Database Buffer Cache.
h) Redo log Buffer :: Redo log Buffer of SGA stores all the redo log entries.
i) Redo Log Files :: Redo log files are set of files that protect altered database
data in memory that has not been written to Data Files. They are basically used
for backup when a database crashes.
j) Process :: A Process is a 'thread of control' or mechansim in Operating System
that executes series of steps.

5) What are Procedure,functions and Packages

5) Procedures and functions consist of set of PL/SQL statements that are
grouped together as a unit to solve a specific problem or perform set of related
tasks.
Procedures do not Return values while Functions return one One Value
Packages :: Packages Provide a method of encapsulating and storing related
procedures, functions, variables and other Package Contents

6) What are Database Triggers and Stored Procedures

6) Database Triggers :: Database Triggers are Procedures that are automatically
executed as a result of insert in, update to, or delete from table.
Database triggers have the values old and new to denote the old value in the
table before it is deleted and the new indicated the new value that will be used.
DT are useful for implementing complex business rules which cannot be
enforced using the integrity rules.We can have the trigger as Before trigger or
After Trigger and at Statement or Row level.
e.g:: operations insert,update ,delete 3
before ,after 3*2 A total of 6 combinatons
At statment level(once for the trigger) or row level( for every execution ) 6 * 2 A
total of 12.
Thus a total of 12 combinations are there and the restriction of usage of 12
triggers has been lifted from Oracle 7.3 Onwards.
Stored Procedures :: Stored Procedures are Procedures that are stored in
Compiled form in the database.The advantage of using the stored procedures is
that many users can use the same procedure in compiled and ready to use
format.

7) How many Integrity Rules are there and what are they
7) There are Three Integrity Rules. They are as follows ::
a) Entity Integrity Rule :: The Entity Integrity Rule enforces that the Primary key
cannot be Null
b) Foreign Key Integrity Rule :: The FKIR denotes that the relationship between
the foreign key and the primary key has to be enforced.When there is data in
Child Tables the Master tables cannot be deleted.
c) Business Integrity Rules :: The Third Intigrity rule is about the complex
business processes which cannot be implemented by the above 2 rules.

8) What are the Various Master and Detail Relation ships.

8) The various Master and Detail Relationship are
a) NonIsolated :: The Master cannot be deleted when a child is exisiting
b) Isolated :: The Master can be deleted when the child is exisiting
c) Cascading :: The child gets deleted when the Master is deleted.

9) What are the Various Block Coordination Properties

9) The various Block Coordination Properties are
a) Immediate
Default Setting. The Detail records are shown when the Master Record are
shown.
b) Deffered with Auto Query
Oracle Forms defer fetching the detail records until the operator navigates to the
detail block.
c) Deffered with No Auto Query
The operator must navigate to the detail block and explicitly execute a query

10) What are the Different Optimisation Techniques

10) The Various Optimisation techniques are
a) Execute Plan :: we can see the plan of the query and change it accordingly
based on the indexes
b) Optimizer_hint ::
set_item_property('DeptBlock',OPTIMIZER_HINT,'FIRST_ROWS');
Select /*+ First_Rows */ Deptno,Dname,Loc,Rowid from dept
where (Deptno > 25)
c) Optimize_Sql ::
By setting the Optimize_Sql = No, Oracle Forms assigns a single cursor for all
SQL statements.This slow downs the processing because for evertime the SQL
must be parsed whenver they are executed.
f45run module = my_firstform userid = scott/tiger optimize_sql = No
d) Optimize_Tp ::
By setting the Optimize_Tp= No, Oracle Forms assigns seperate cursor only for
each query SELECT statement. All other SQL statements reuse the cursor.
f45run module = my_firstform userid = scott/tiger optimize_Tp = No

11) How do u implement the If statement in the Select Statement

11) We can implement the if statement in the select statement by using the
Decode statement.
e.g select DECODE (EMP_CAT,'1','First','2','Second'Null);
Here the Null is the else statement where null is done .

12)How many types of Exceptions are there

12) There are 2 types of exceptions. They are
a) System Exceptions
e.g. When no_data_found, When too_many_rows
b) User Defined Exceptions
e.g. My_exception exception
When My_exception then

13) What are the inline and the precompiler directives

13) The inline and precompiler directives detect the values directly

14) How do you use the same lov for 2 columns

14) We can use the same lov for 2 columns by passing the return values in
global values and using the global values in the code

15) How many minimum groups are required for a matrix report
15) The minimum number of groups in matrix report are 4

16) What is the difference between static and dynamic lov

16) The static lov contains the predetermined values while the dynamic lov
contains values that come at run time

17) What are snap shots and views

17) Snapshots are mirror or replicas of tables. Views are built using the columns
from one or more tables. The Single Table View can be updated but the view with
multi table cannot be updated

18) What are the OOPS concepts in Oracle.

18) Oracle does implement the OOPS concepts. The best example is the
Property Classes. We can categorise the properties by setting the visual
attributes and then attach the property classes for the
objects. OOPS supports the concepts of objects and classes and we can
consider the peroperty classes as classes and the items as objects

19) What is the difference between candidate key, unique key and primary
key
19) Candidate keys are the columns in the table that could be the primary keys
and the primary key
is the key that has been selected to identify the rows. Unique key is also useful
for identifying the distinct rows in the table.

20)What is concurrency
20) Cuncurrency is allowing simultaneous access of same data by different
users. Locks useful for accesing the database are
a) Exclusive
The exclusive lock is useful for locking the row when an insert,update or delete is
being done.This lock should not be applied when we do only select from the row.
b) Share lock
We can do the table as Share_Lock as many share_locks can be put on the
same resource.

21) Previleges and Grants

21) Previleges are the right to execute a particulare type of SQL statements.
e.g :: Right to Connect, Right to create, Right to resource
Grants are given to the objects so that the object might be accessed
accordingly.The grant has to be
given by the owner of the object.

22)Table Space,Data Files,Parameter File, Control Files

22)Table Space :: The table space is useful for storing the data in the
database.When a database is created two table spaces are created.
a) System Table space :: This data file stores all the tables related to the system
and dba tables
b) User Table space :: This data file stores all the user related tables
We should have seperate table spaces for storing the tables and indexes so that
the access is fast.
Data Files :: Every Oracle Data Base has one or more physical data files.They
store the data for the database.Every datafile is associated with only one
database.Once the Data file is created the size cannot change.To increase the
size of the database to store more data we have to add data file.
Parameter Files :: Parameter file is needed to start an instance.A parameter file
contains the list of instance configuration parameters e.g.::
db_block_buffers = 500
db_name = ORA7
db_domain = u.s.acme lang
Control Files :: Control files record the physical structure of the data files and
redo log files
They contain the Db name, name and location of dbs, data files ,redo log files
and time stamp.

23) Physical Storage of the Data

23) The finest level of granularity of the data base are the data blocks.
Data Block :: One Data Block correspond to specific number of physical
database space
Extent :: Extent is the number of specific number of contigious data blocks.
Segments :: Set of Extents allocated for Extents. There are three types of
Segments
a) Data Segment :: Non Clustered Table has data segment data of every table is
stored in
cluster data segment
b) Index Segment :: Each Index has index segment that stores data
c) Roll Back Segment :: Temporarily store 'undo' information

24) What are the Pct Free and Pct Used

24) Pct Free is used to denote the percentage of the free space that is to be left
when creating a table. Similarly Pct Used is used to denote the percentage of the
used space that is to be used when creating a table
eg.:: Pctfree 20, Pctused 40

25) What is Row Chaining

25) The data of a row in a table may not be able to fit the same data block.Data
for row is stored in a chain of data blocks .

26) What is a 2 Phase Commit

26) Two Phase commit is used in distributed data base systems. This is useful to
maintain the integrity of the database so that all the users see the same values. It
contains DML statements or Remote Procedural calls that reference a remote
object. There are basically 2 phases in a 2 phase commit.
a) Prepare Phase :: Global coordinator asks participants to prepare
b) Commit Phase :: Commit all participants to coordinator to Prepared, Read only
or abort Reply

27) What is the difference between deleting and truncating of tables

27) Deleting a table will not remove the rows from the table but entry is there in
the database dictionary and it can be retrieved But truncating a table deletes it
completely and it cannot be retrieved.

28) What are mutating tables

28) When a table is in state of transition it is said to be mutating. eg :: If a row
has been deleted then the table is said to be mutating and no operations can be
done on the table except select.

29) What are Codd Rules

29) Codd Rules describe the ideal nature of a RDBMS. No RDBMS satisfies all
the 12 codd rules and Oracle Satisfies 11 of the 12 rules and is the only Rdbms
to satisfy the maximum number of rules.

30) What is Normalisation

30) Normalisation is the process of organising the tables to remove the
redundancy.There are mainly 5 Normalisation rules.
a) 1 Normal Form :: A table is said to be in 1st Normal Form when the attributes
are atomic
b) 2 Normal Form :: A table is said to be in 2nd Normal Form when all the
candidate keys are dependant on the primary key
c) 3rd Normal Form :: A table is said to be third Normal form when it is not
dependant transitively

31) What is the Difference between a post query and a pre query
31) A post query will fire for every row that is fetched but the pre query will fire
only once.

32) Deleting the Duplicate rows in the table

32) We can delete the duplicate rows in the table by using the Rowid

33) Can U disable database trigger? How?

33) Yes. With respect to table
ALTER TABLE TABLE
[[ DISABLE all_trigger ]]

34) What is pseudo columns ? Name them?

34) A pseudocolumn behaves like a table column, but is not actually
stored in the table. You can select from pseudocolumns, but you
cannot insert, update, or delete their values. This section
describes these pseudocolumns:
* CURRVAL
* NEXTVAL
* LEVEL
* ROWID
* ROWNUM

35) How many columns can table have?

The number of columns in a table can range from 1 to 254.

36) Is space acquired in blocks or extents ?

In extents .
37) what is clustered index?
In an indexed cluster, rows are stored together based on their cluster key values .
Can not applied for HASH.

38) what are the datatypes supported By oracle (INTERNAL)?

Varchar2, Number,Char , MLSLABEL.

39 ) What are attributes of cursor?

%FOUND , %NOTFOUND , %ISOPEN,%ROWCOUNT

40) Can you use select in FROM clause of SQL select ?

Yes.

GENERAL INTERVIEW QUESTIONS

1.What are the various types of Exceptions ?

User defined and Predefined Exceptions.

2.Can we define exceptions twice in same block ?

No.

3.What is the difference between a procedure and a function ?

Functions return a single variable by value whereas procedures do not return any
variable by value. Rather they return multiple variables by passing variables by
reference through their OUT parameter.

4.Can you have two functions with the same name in a PL/SQL block ?
Yes.

5.Can you have two stored functions with the same name ?
Yes.

6.Can you call a stored function in the constraint of a table ?

No.

7.What are the various types of parameter modes in a procedure ?

IN, OUT AND INOUT.

8.What is Over Loading and what are its restrictions ?

OverLoading means an object performing different functions depending upon the
no. of parameters or the data type of the parameters passed to it.
9.Can functions be overloaded ?
Yes.
10.Can 2 functions have same name & input parameters but differ only by
return datatype
No.

11.What are the constructs of a procedure, function or a package ?

The constructs of a procedure, function or a package are :
variables and constants
cursors
exceptions

12.Why Create or Replace and not Drop and recreate procedures ?

So that Grants are not dropped.

13.Can you pass parameters in packages ? How ?

Yes. You can pass parameters to procedures or functions in a package.

14.What are the parts of a database trigger ?

The parts of a trigger are:
A triggering event or statement
A trigger restriction
A trigger action

15.What are the various types of database triggers ?

There are 12 types of triggers, they are combination of :
Insert, Delete and Update Triggers.
Before and After Triggers.
Row and Statement Triggers.
(3*2*2=12)

16.What is the advantage of a stored procedure over a database trigger ?

We have control over the firing of a stored procedure but we have no control over
the firing of a trigger.

17.What is the maximum no. of statements that can be specified in a trigger

statement ?
One.

18.Can views be specified in a trigger statement ?

No

19.What are the values of :new and :old in Insert/Delete/Update Triggers ?

INSERT : new = new value, old = NULL
DELETE : new = NULL, old = old value
UPDATE : new = new value, old = old value

20.What are cascading triggers? What is the maximum no of cascading

triggers at a time?
When a statement in a trigger body causes another trigger to be fired, the
triggers are said to be cascading. Max = 32.

21.What are mutating triggers ?

A trigger giving a SELECT on the table on which the trigger is written.

22.What are constraining triggers ?

A trigger giving an Insert/Updat e on a table having referential integrity constraint
on the triggering table.

23.Describe Oracle database's physical and logical structure ?

Physical : Data files, Redo Log files, Control file.
Logical : Tables, Views, Tablespaces, etc.

24.Can you increase the size of a tablespace ? How ?

Yes, by adding datafiles to it.

25.Can you increase the size of datafiles ? How ?

No (for Oracle 7.0)
Yes (for Oracle 7.3 by using the Resize clause ----- Confirm !!).

26.What is the use of Control files ?

Contains pointers to locations of various data files, redo log files, etc.

27.What is the use of Data Dictionary ?

Used by Oracle to store information about various physical and logical Oracle
structures e.g. Tables, Tablespaces, datafiles, etc

28.What are the advantages of clusters ?

Access time reduced for joins.

29.What are the disadvantages of clusters ?

The time for Insert increases.

30.Can Long/Long RAW be clustered ?

No.

31.Can null keys be entered in cluster index, normal index ?

Yes.
32.Can Check constraint be used for self referential integrity ? How ?
Yes. In the CHECK condition for a column of a table, we can reference some
other column of the same table and thus enforce self referential integrity.

33.What are the min. extents allocated to a rollback extent ?

Two

34.What are the states of a rollback segment ? What is the difference between
partly available and needs recovery ?
The various states of a rollback segment are :
ONLINE, OFFLINE, PARTLY AVAILABLE, NEEDS RECOVERY and INVALID.
35.What is the difference between unique key and primary key ?
Unique key can be null; Primary key cannot be null.
36.An insert statement followed by a create table statement followed by
rollback ? Will the rows be inserted ?
No.
37.Can you define multiple savepoints ?
Yes.
38.Can you Rollback to any savepoint ?
Yes.
40.What is the maximum no. of columns a table can have ?
254.
41.What is the significance of the & and && operators in PL SQL ?
The & operator means that the PL SQL block requires user input for a variable.
The && operator means that the value of this variable should be the same as
inputted by the user previously for this same variable.
If a transaction is very large, and the rollback segment is not able to hold the
rollback information, then will the transaction span across different rollback
segments or will it terminate ?
It will terminate (Please check ).
42.Can you pass a parameter to a cursor ?
Explicit cursors can take parameters, as the example below shows. A cursor
parameter can appear in a query wherever a constant can appear.
CURSOR c1 (median IN NUMBER) IS
SELECT job, ename FROM emp WHERE sal > median;
43.What are the various types of RollBack Segments ?
Public Available to all instances
Private Available to specific instance
44.Can you use %RowCount as a parameter to a cursor ?
Yes
45.Is the query below allowed :
Select sal, ename Into x From emp Where ename = 'KING'
(Where x is a record of Number(4) and Char(15))
Yes
46.Is the assignment given below allowed :
ABC = PQR (Where ABC and PQR are records)
Yes
47.Is this for loop allowed :
For x in &Start..&End Loop
Yes
48.How many rows will the following SQL return :
Select * from emp Where rownum < 10;
9 rows
49.How many rows will the following SQL return :
Select * from emp Where rownum = 10;
No rows
50.Which symbol preceeds the path to the table in the remote database ?
@
51.Are views automatically updated when base tables are updated ?
Yes
52.Can a trigger written for a view ?
No
53.If all the values from a cursor have been fetched and another fetch is issued,
the output will be : error, last record or first record ?
Last Record
54.A table has the following data : [[5, Null, 10]]. What will the average function
return ?
7.5
55.Is Sysdate a system variable or a system function?
System Function
56.Consider a sequence whose currval is 1 and gets incremented by 1 by using
the nextval reference we get the next number 2. Suppose at this point we issue
an rollback and again issue a nextval. What will the output be ?
3
56.Definition of relational DataBase by Dr. Codd (IBM)?
A Relational Database is a database where all data visible to the user is
organized strictly as tables of data values and where all database operations
work on these tables.
57.What is Multi Threaded Server (MTA) ?
In a Single Threaded Architecture (or a dedicated server configuration) the
database manager creates a separate process for each database user. But in
MTA the database manager can assign multiple users (multiple user processes)
to a single dispatcher (server process), a controlling process that queues request
for work thus reducing the databases memory requirement and resources.
58.Which are initial RDBMS, Hierarchical & N/w database ?
RDBMS - R system
Hierarchical - IMS
N/W - DBTG
59.Difference between Oracle 6 and Oracle 7
ORACLE 7 ORACLE 6
Cost based optimizer ? Rule based optimizer
Shared SQL Area ? SQL area allocated for each user
Multi Threaded Server ? Single Threaded Server
Hash Clusters ? Only B-Tree indexing
Roll back Size Adjustment ? No provision
Truncate command ? No provision
Database Integrity
Constraints ? Provision at Application Level
Stored procedures, functions
packages & triggers ? No provision
Resource profile limit. It
prevents user from running
away with system resources ? No provision
Distributed Database ? Distributed Query
Table replication & snapshots? No provision
Client/Server Tech. ? No provision
60.What is Functional Dependency
Given a relation R, attribute Y of R is functionally dependent on attribute X of R if
and only if each X-value has associated with it precisely one -Y value in R
61.What is Auditing ?
The database has the ability to audit all actions that take place within it.
a) Login attempts, b) Object Accesss, c) Database Action
Result of Greatest(1,NULL) or Least(1,NULL)
NULL
62.While designing in client/server what are the 2 imp. things to be considered ?
Network Overhead (traffic), Speed and Load of client server
63.What are the disadvantages of SQL ?
Disadvantages of SQL are :
? Cannot drop a field
? Cannot rename a field
? Cannot manage memory
? Procedural Language option not provided
? Index on view or index on index not provided
? View updation problem

64.When to create indexes ?

To be created when table is queried for less than 2% or 4% to 25% of the table
rows.
65.How can you avoid indexes ?
TO make index access path unavailable
? Use FULL hint to optimizer for full table scan
? Use INDEX or AND-EQUAL hint to optimizer to use one index or set to
indexes instead of another.
? Use an expression in the Where Clause of the SQL.
66.What is the result of the following SQL :
Select 1 from dual
UNION
Select 'A' from dual;
Error
67.Can database trigger written on synonym of a table and if it can be then what
would be the effect if original table is accessed.
Yes, database trigger would fire.
68.Can you alter synonym of view or view ?
No
69.Can you create index on view
No.
70.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.
71.What is the difference between alias and synonym ?
Alias is temporary and used with one query. Synonym is permanent and not used
as alias.
72.What is the effect of synonym and table name used in same Select
statement ?
Valid
73.What's the length of SQL integer ?
32 bit length
74.What is the difference between foreign key and reference key ?
Foreign key is the key i.e. attribute which refers to another table primary key.
Reference key is the primary key of table referred by another table.
75.Can dual table be deleted, dropped or altered or updated or inserted ?
Yes
76.If content of dual is updated to some value computation takes place or not ?
Yes
77.If any other table same as dual is created would it act similar to dual?
Yes
78.For which relational operators in where clause, index is not used ?
<> , like '% ...' is NOT functions, field +constant, field || ''
79.Assume that there are multiple databases running on one machine. How can
you switch from one to another ?
Changing the ORACLE_SID
80.What are the advantages of Oracle ?
Portability : Oracle is ported to more platforms than any of its competitors,
running on more than 100 hardware platforms and 20 networking protocols.
Market Presence : Oracle is by far the largest RDBMS vendor and spends more
on R & D than most of its competitors earn in total revenue. This market clout
means that you are unlikely to be left in the lurch by Oracle and there are always
lots of third party interfaces available.
Backup and Recovery : Oracle provides industrial strength support for on-line
backup and recovery and good software fault tolerence to disk failure. You can
also do point-in-time recovery.
Performance : Speed of a 'tuned' Oracle Database and application is quite good,
even with large databases. Oracle can manage > 100GB databases.
Multiple database support : Oracle has a superior ability to manage multiple
databases within the same transaction using a two-phase commit protocol.
81.What is a forward declaration ? What is its use ?
PL/SQL requires that you declare an identifier before using it. Therefore, you
must declare a subprogram before calling it. This declaration at the start of a
subprogram is called forward declaration. A forward declaration consists of a
subprogram specification terminated by a semicolon.
82.What are actual and formal parameters ?
Actual Parameters : Subprograms pass information using parameters. The
variables or expressions referenced in the parameter list of a subprogram call are
actual parameters. For example, the following procedure call lists two actual
parameters named emp_num and amount:
Eg. raise_salary(emp_num, amount);

Formal Parameters : The variables declared in a subprogram specification and

referenced in the subprogram body are formal parameters. For example, the
following procedure declares two formal parameters named emp_id and
increase:
Eg. PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS
current_salary REAL;
83.What are the types of Notation ?
Position, Named, Mixed and Restrictions.
84.What all important parameters of the init.ora are supposed to be increased if
you want to increase the SGA size ?
In our case, db_block_buffers was changed from 60 to 1000 (std values are 60,
550 & 3500) shared_pool_size was changed from 3.5MB to 9MB (std values are
3.5, 5 & 9MB) open_cursors was changed from 200 to 300 (std values are 200 &
300) db_block_size was changed from 2048 (2K) to 4096 (4K) {at the time of
database creation}.
The initial SGA was around 4MB when the server RAM was 32MB and The new
SGA was around 13MB when the server RAM was increased to 128MB.
85.If I have an execute privilege on a procedure in another users schema, can I
execute his procedure even though I do not have privileges on the tables within
the procedure ?
Yes
86.What are various types of joins ?
Equijoins, Non-equijoins, self join, outer join
87.What is a package cursor ?
A package cursor is a cursor which you declare in the package specification
without an SQL statement. The SQL statement for the cursor is attached
dynamically at runtime from calling procedures.
88.If you insert a row in a table, then create another table and then say Rollback.
In this case will the row be inserted ?
Yes. Because Create table is a DDL which commits automatically as soon as it is
executed. The DDL commits the transaction even if the create statement fails
internally (eg table already exists error) and not syntactically.
89.What are the various types of queries ?
Normal Queries
Sub Queries
Co-related queries
Nested queries
Compound queries
90.What is a transaction ?
A transaction is a set of SQL statements between any two COMMIT and
ROLLBACK statements.

91.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.
92.Which of the following is not a schema object : Indexes, tables, public
synonyms, triggers and packages ?
Public synonyms
93.What is the difference between a view and a snapshot ?
94.What is PL/SQL?
PL/SQL is Oracle's Procedural Language extension to SQL. The language
includes object oriented programming techniques such as encapsulation,
function overloading, information hiding (all but inheritance), and so, brings state-
of-the-art programming to the Oracle database server and a variety of Oracle
tools.
95.Is there a PL/SQL Engine in SQL*Plus?
No. Unlike Oracle Forms, SQL*Plus does not have a PL/SQL engine. Thus, all
your PL/SQL are send directly to the database engine for execution. This makes
it much more efficient as SQL statements are not stripped off and send to the
database individually.
96.Is there a limit on the size of a PL/SQL block?
Currently, the maximum parsed/compiled size of a PL/SQL block is 64K and the
maximum code size is 100K. You can run the following select statement to query
the size of an existing package or procedure.
SQL> select * from dba_object_size where name = 'procedure_name'
97.Can one read/write files from PL/SQL?
Included in Oracle 7.3 is a UTL_FILE package that can read and write files. The
directory you intend writing to has to be in your INIT.ORA file (see
UTL_FILE_DIR=... parameter). Before Oracle 7.3 the only means of writing a file
was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/home/oracle/tmp', 'myoutput','W');
UTL_FILE.PUTF(fileHandler, 'Value of func1 is %sn', func1(1));
UTL_FILE.FCLOSE(fileHandler);
END;
98.How can I protect my PL/SQL source code?
PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL
programs to protect the source code. This is done via a standalone utility that
transforms the PL/SQL source code into portable binary object code (somewhat
larger than the original). This way you can distribute software without having to
worry about exposing your proprietary algorithms and methods. SQL*Plus and
SQL*DBA will still understand and know how to execute such scripts. Just be
careful, there is no "decode" command available.
The syntax is:
wrap iname=myscript.sql oname=xxxx.yyy
99.Can one use dynamic SQL within PL/SQL? OR Can you use a DDL in a
procedure ? How ?
From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic
SQL statements.
Eg: CREATE OR REPLACE PROCEDURE DYNSQL
AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur,'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;
1. What is Referential Integrity rule?
Differentiate between
2. Delete & Truncate command.
3. Implicit Cursor & Explicit Cursor.
4. Ref. key & Foreign key.
5. Where & Having Clause.
6. What are various kinds of Integrity Constraints in Oracle?
7. What are various kind of joins?
8. What is Raise_Application_Error?
9. What are various kinds of exceptions in Oracle?
10. Normal Forms

Oracle Notes :

Oracle 8i -
It is a DB of internet computing , It changes the of information managed and
accessed to meet the demandof internet age.
-- Significant new feature for OLTP(Online trans Processing) and data ware
housing Appl.
-- To mange all types of datain web site.
-- iFS Internet file Syatem
-- interMedia to manage and access multimedia data,audio,video
-- Support to java(to install JVM on server)
-- Security enhancement(authentication and authorization,centralizing user
management)

Oracle 8(ORDBMS) -
-Parrallel enhancement ,faster connection
-Table partitioning , Connection inc to 30000 ,Table column upto 1000
-DB size inc from few tera byte to 10 tera. , Data file inc 65,533
-Support MTS,provides LOB

Oracle Start -
1. Oracle instance start -Allocates SGA and start BAckground processes.
2. Mount Oracle DB-Method of Associating DB with previous started instance
3.Opening DB-To make available.

Normalization -
It's a technique thr. which we can design the DB.
During normalization dependancies can be identified which can cause pbs during
deletion & updation .It is used in simplifing the structure of table.
1NF-Unnorma;ised data transfer to normalised form.
2NF-Functional dependancies can be find out & decompose the table without
loss of data.
3NF-Transist dependancies ,Every non key attrbute is functionally dependant on
just PK.
4NF(BCNF)-The relation which has multiple candidate keys ,then we have to go
for BCNF.
Denormalization-
At the same time when information is required from more than one table at faster
rate then it is wiser to add some sort of dependancies.

Rooling Forward -To reapply to Data file to all changes that are recorded in Redo
log file due to which datafile contains commited & uncommited dat.
Forward Declaration-To declare variable and procedures before using it.
2- Tier Arch. Disadv-When Business Rule changes.
PL/SQL Record-To represent more than one row at time.
PL/SQL Table -To define single variable comprises several data element.
To delete define one more empty table and assign it.
Tablespace -
Profile-To control system resources ,memory ,diskspace and CPU time.
We can find rows effected by %rowcount.
Data Binding-Dividing the cursor in appl as per select stamt.
Trancate -Faster than delete ,doesn't fire ny DB trigger ,Allocate space ,No
roolback.
Defered Integrity constraints-When we refere PK in the same table where we
defined .
Cascading trigger-
Temporary Table-Delete operation table.
Log Table-to store information abt error.
Coordinity-
Err Trap -To trap error use SQLERRM,SQLCODE
Modularity-PL/SQL allows to create program module to improve software
reliability and to hide complexity
Positional and Named Notation -
The actual arguments are associaed with formal arguments by position k/s
Positional Notation.It's commonly used.
A Trigger doesn't accept argument & have same name as table or procedure as it
exist in seperate namespace.
How we ref FK in Sql -Join Condition.
Security/Lock-
Shared/exclusive -When 2 transaction wants to read/write from db at the same
time.
Dead- 1trans updates emp and dep
2 trans update dep and emp
TO add a not null column to a table which has already some records -
Alter table a
Add(b number default 1 not null)
Sequence- Start with,increment by,Cache/No cache,Order/No order,Max,Min
ER Dia.- Entity Relation Dia.
Set Transaction -To set a current transaction online offline
Oracle err-
ORA-06500 stiorage err
ORA-00923 from keyword not found
ORA-06501program err
ORA-00904 Invalid Col
ORA-00001Uk violated.
Dynamic Sql -Which uses late binding
File I/O-To read and write dat to and from text file thr. Oracle procedure.
Joins-Equi,Non EQui,Self,inner joins,outer joins
Index-16 col per table.
Parsing-Syntax checking.
Optimization-Use of index (HINT)
Corelated Subquery -Which fires only once/ per row for entire stmt.
Simple Query--Which fires everytime for entire stmt
Packages- Encapsulation,Overloading,improve performance as I/O reduces.
PL/SQL Signature Method- To determine when remote dependant object get
invalid.
Object Previledge - On a particular object- I/U/D/Exec
System Previledge -Entire collection object -C/A/D
SGA Comprises -Data Buffer, Redo Log Buffer,Shared pool Buffer.
Shared Pool - Req to process unique SQL stmt submitted to DB.
It contains information such as parse tree and execusion plan .
PGA -A memry buffer that contains data and control information for a server
process.
Dedicated server - Handles request. for single user.
Multithresd Server-Handles request. for multiple user.
Background process -DBWR,LGWR,PMON,SMON,CKPT
DBWR-Writes modified data blocks from DB buffer to data file.
LGWR-
CKPT-Responsible to check DBWR and update control file to indicate most
recent CKPT.
SMON-Instance recovery at start up,Clean Temporary. Segment.
PMON-Responsible for process recovery and user process fails,Cleaning up
cache ,freeing resources which was using process.
Segment-Data/Index/Rollback/Temp
Data Dictionary -V$SESSION, information abt integrity constraints,space
allocated for schema object.
USER_TAB_COLUMNS gives you a list of tables as per Column.
EOD Procedure-

Mutating/Constraining Err/Table
Diff of where and group by
Connect,Allocate.Analyse Command.
Queries--
1. 3rd Max
select distinct sal
from emp a
where 3=(select count(distinct sal)
from emp b
where a.sal=<b.sal)
2. Delete Duplicate rows
Delete Emp
where rowid not in(select max(rowid)
from emp
group by emp_no)
3. First 5 Max No
select sal
from (select abs(-sal) sal
from emp
group by -sal)
where rownum<6
Views--
-No Aggr function,group by,having
-U/D without PK but not Insert.
-Join -No DML
-No join-DML
Index -are used for row selection in where and order by only if indexing on
column
You can launch the DBA Studio or the individual tools directly from the Windows
NT Start menu. Or, you can use the following syntax to launch them from a
command line prompt:

oemapp tool_name

where tool_name may be dbastudio, instance, security, storage, schema, or

worksheet, if installed.

DBMS_ALERT is a Transaction Processing Package while DBMS_PIPE is an

Application Development package

Developed By Satish Shrikhande

DBA?

If to_date(sysdate,'DAY')='Tuesday' then ..
Buffer Cache-To improve data block recently used by user in order to improve the
performance.
Ordinality-Emp, Expences-Emp may expense sheet and Expense sheet has only
one Emp. This fact k/s Referred Ordinality.
Three Steps in creating DB.--
-Creating physical location for data in tables and indexes to be stored in DB.
-To create the file that still store log entries.
-To create logical structure of data dictionary.
This is accomplished by create DB
1. Back up existing DB.
2.Create or Edit the init.ora file
3.Varify the instance name
4. Start Application management DB tool.
5.start instance
6.Create and Backup the new DB.
Control file -250K
Oracle Administration Assistant for W-NT is a DB management tool that enables
to create DB administartor, operator, Users and role. To manage Oracle DB
services, DB start up, shut down, Edit registry parameter setting, views oracle
process information.
Database Configuration Assistant -To create DB
Oracle environment-
OLTP-Many users can read and update, hight response time.
DSS-Read only.
Hybrid-both OLTP & DSS App. are running with this App.
Init.ora-is a parameter file like DB_NAME, CONTROL_FILE, DB_BLOCK _SIZE
RowID-BlockIDRowIdDatafileId
Cluster Segment-To support use of cluster on the DB.
Hash Cluster-By placing data in close proximity k/s Hashing.
Optimization-
Decides line of execution of query.
First apply condition and then make Cartesian product. The cost can reduce by
reducing no of rows.
Oracle ways for optimization-
-Evaluation of expression and condition amt>500/100--amt>5
Like convert to equal
IN - OR condition
Any -OR
Between/ALL -AND
NOT-Avoid
Transitivity-where a.id=b.id and a.id=1 use a.id=1 and b.id =1
Merging views
Index column be in order by clause.

Bitmap Index- If the column has very few distinct entries

We have to specify in init.ora Rate, Cost Choose mode based Approach
-Avoid full table scan.
-Access by Rowid
-No function on Index column as it prevents the optimization.
-Avoid IN, NOT and LIKE operator.
-Column in where clause should be indexed.
DATABASE-
Profile -To control system resources like memory, diskspace, and CPU time.
Role -Collection of privileges.
Type of segment- Rollback, Temp, Data, Index
Snapshot-It's a read only table, to improve efficiency of query, which referred
remote db, therefore reduce remote traffic.
DB trigger-is a PL/SQL block that are associated with given table.
Diff bet Trigger and Procedure-
-Trigger need not required to be call (Implicitly fire on event)
-No TCL used
-Proc/fun can be used in trigger
-No use of Long raw,LOB,LONG
-Procedure is prefered over trigger as proc stored in compile form as trigg
p_code stores.
TO check time nbetwen 8 am and 6 pm.
Create or replace trigger ptpt
before insert on batch
for each row
declare
A varchar2 (20);
begin
Select substr (to_char (sysdate,'HH: MI: SSSS?) 1,2) into a from dual;

If (a between '08' and ?18?) then

Raise_application_error (-20001,'Invalid Time');
End if;
End;
Snapshot too old-We have to refresh the snapshot
Alter snapshot as
Select * from batch@dmaster.link
Refresh after seven days.
We can reduce network traffic-
-By using snapshot
-By storing related table in same tablespace
-By avoiding Row chain.
Oracle DB uses three types of file structure.
Data files-store actual data for tablespace, which is a logical unit of storage.
Every tablespace has one or more data file to store actual data for tables,
indexes, and clusters. Data is read and write to data file as needed.
Redo log file-Two or more redo log file make up a logical redo log, which is used
to recover modifications that have not been written to data files in event of power
outage.
Control file-Used at start up to identify the DB and determine which redo log file
and data file are created.
1 data file, 1 control file, 2 redo log file.
SET TRANSACTION-We use set transaction statement to login a read only or
read-write or to assign the current transaction to specified rollback segment.
Where date=sysdate-daily sale
>sysdate-7 weekly sale
>sysdate-30 monthly sale.
A function must contain atleast one return value else PL/SQL raises predefined
exception program_error.
Actual parameter- when call
Formal parameter
Parametric Cursor - The cursor in which we can pass value when it is being
opened
Sql Stmt Execu-
-Reserves an area in memory called Private Sql Area.
-Populate this area with app. data.
-Process data in memory area.
-Free the, memory area when exec is complete.
Active set- A set of rows return by a mult-row query.
Export-Putting data of tables in file, which can be, handles by OS.
Auditing-
is used for noting down user's activity and statistics abt the operations in data
objects. The auditing are
1-Stmt
2-Preveledge
3-Object
1-It is done to audit stmt activity .The auditing information abt. date & time of
information, nature of operation is stored in table AUD$ which is used by user
sys.
Audit select on itemmaster;
Then app. auditing is done and stored in table .
-To record the usage of privilege
-To record the activity on object.
Nature of Auditing-
Auditing is done on
-Per session basis-one record is generated.
Per statement basis per session/stmt
Audit any allows user to audit any schema object in the DB.
Table partitioning-
Table partitioning divides table data between two or more tablespaces and
physical data file on separate disk.
We can use it to improve transaction throughout and certain type of queries for
large tables.
Restriction-
-A table that is a part of cluster can't be partioned.
-A table can be partitioned based on ranges column values only.
-Attribute of partitioned table can't include long, long raw or any lob data type.
-Bitmap indexes can't be defined on partioned tables.
We add partition using ALTER TABLE OR
Create table aa (
a date,
B number
C varchar2 (10))
partion by range(a,b)
(partition pa1 values less than ('01-jan-99', 2) tablespace tsp1,
-----------------------------------);
Accessing partition table-
Select * from aa partion(pa1);
Drop partion
-Alter table AA
drop partion pa1;

SQL Language Extension-

Oracle * provide new built-in datatype, object datatypes, nested tables, and a no
of other features that require new DDL extension.
VARRAY
REF
LOBS
Create table AA(a N (10)
B date,
C varchar2 (10));

Create type aa1 as varray (5) of number (5);

The UTLBSTAT and UTLESTAT script to get general overview of database 's
performance over a certain period of time.
UTLBSTAT creates table and views containing cumulative database performance
summary information at the time when the script runs .All the objects create by
UTLBSTAT contain word login.
Utlbstat.sql

UTLESTAT creates table and views containing cumulative database performance

summary information at the time when the script runs .All the objects create by
UTLESTAT contain word end.
UTLESTAT spools the results of these SQL statements to a file called
REPORT.TXT
Utlestat.sql

Determine the shared Pool Performance.

The shared pool is one of the memory structures in SGA .It is comprised of the
data dictionary and the library cache. Check v$sgastat
The data dictionary cache buffers data dictionary objects that contain data about
tables, indexes, users and all other objects.
The Library Cache/SQL Cache buffers previously executed queries, so that they
need not be reloaded and reparsed if user calls them again.

Otherwise if the information is not in the buffer then oracle must get it from disk.

The V$LIBRAY CACHE View stores performance data for library cache and
V$ROWCACHE view stores performance data for the data dictionary cache.

Sometime we may have to increase the value of initialization parameter

SHARED_POOL_SIZE.
To improve the performance .

Redo Log --

Oracle 8 stores all changes to the database, even uncommitted changes, in the
redo log files.
LGWR writes .

Alter database archievelog

Edit the parameter initialization file.

Log_archieve_start =true -turn it on
Log_archieve_dest=c:/oracle/ora81/archieve -location
log_archieve_format="ARCH%S.LOG" - name format for archieve file .
%S for log sequence number .
By querying the V$SESSION view , we can determine who is logged on ,as well
as information such as the time of logon .

Kill a session - ALTER system kill session '&sid,&serial'

Select Sid,serial#,status from V$session where username='name';

Unbalanced Index ?
if we do have lot on index on a table and we are doing I/U/D frequently then there
is a problem of disk contention . To check this problem sees the BLEVEL value in
DBA_INDEXES and if it is 1,2,3,4 then it?s ok else rebuild the index .

Alter index satish.a_satish rebuild unrecoverable ;

Comments on table and columns

--For documentation purpose .

Comment table a is ?table a? ; Retrieve comment from user_tab_comment
Comment column a. a is ?column a?; user_col_comments

Detect the objects close to maximum extent

Check in dba_seqment
Detect row chaining and row migration in tables
Row migration occurs when a database block doesn?t contain enough free
space to accommodate an update statement .In that case server moves the row
to another block and maintains a pointer to to new block in the row?s original
block .when pctfree is 0
Row chaining in contrast , occurs when no single db block is larger enough to
accommodate a particular row . this is common when table contain several large
data types. It will reside in multiple database blocks .
An unpleasant side effect of both chaining and migration is that the oracle *
server must read more than one db block to read a single row . solution ? move
rows to a temp table and then delete rows from original table and then insert it
from temp table .

Execute utlchain.sql

Get information from CHAINED_ROWS or V$SYSSTAT

Data Base Administration

51. What is a database instance? Explain.

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.

The memory structure that is used to store the most queried data from database.
This helps up to improve database performance by decreasing the amount of I/O
performed against data file.

52. What is Parallel Server?

Multiple instances accessing the same database (only in multi-CPU

environments)

53. What is a schema?

The set of objects owned by user account is called the schema.

54. What is an index? How it is implemented in Oracle database?

An index is a database structure used by the server to have direct access of a

row in a table. An index is automatically created when a unique of primary key
constraint clause is specified in create table command

55. What are clusters?

Group of tables physically stored together because they share common columns
and are often used together is called cluster.

56. What is a cluster key?

The related columns of the tables are called the cluster key. The cluster key is
indexed using a cluster index and its value is stored only once for multiple tables
in the cluster.

57. What are the basic element of base configuration of an Oracle

database?

It consists of
one or more data files.
one or more control files.
two or more redo log files.
The Database contains
multiple users/schemas
one or more rollback segments
one or more tablespaces
 Data dictionary tables
User objects (table,indexes,views etc.,)
The server that access the database consists of
SGA (Database buffer, Dictionary Cache Buffers, Redo log buffers, Shared
SQL pool)
SMON (System MONito)
PMON (Process MONitor)
LGWR (LoG Write)
DBWR (Data Base Write)
ARCH (ARCHiver)
CKPT (Check Point)
RECO
Dispatcher
User Process with associated PGS

58. What is a deadlock? Explain.

Two processes waiting to update the rows of a table, which are locked by other
processes then deadlock arises.

In a database environment this will often happen because of not issuing the
proper row lock commands. Poor design of front-end application may cause this
situation and the performance of server will reduce drastically.

These locks will be released automatically when a commit/rollback operation

performed or any one of this processes being killed externally.

Memory Management

59. What is SGA?

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

60. 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.
61. What is mean by Program Global Area (PGA)?

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

62. What is a data segment?

Data segment are the physical areas within a database block in which the data
associated with tables and clusters are stored.

63. What are the factors causing the reparsing of SQL statements in SGA?

Due to insufficient shared pool size.

Monitor the ratio of the reloads takes place while executing SQL statements. If
the ratio is greater than 1 then increase the SHARED_POOL_SIZE.

Database Logical & Physical Architecture

64. 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 such as tables, indexes and
clusters DB_BLOCK_BUFFERS parameter in INIT.ORA decides the size.

65. What is dictionary cache?

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

dictionary table.

66. What is meant by recursive hints?

Number of times processes repeatedly query the dictionary table is called

recursive hints. It is due to the data dictionary cache is too small. By increasing
the SHARED_POOL_SIZE parameter we can optimize the size of data dictionary
cache.

67. What is redo log buffer?

Changes made to the records are written to the on-line redo log files. So that
they can be used in roll forward operations during database recoveries. Before
writing them into the redo log files, they will first brought to redo log buffers in
SGA and LGWR will write into files frequently. LOG_BUFFER parameter will
decide the size.

68. How will you swap objects into a different table space for an existing
database?

- Export the user

- Perform import using the command imp system/manager file=export.dmp
indexfile=newrite.sql. This will create all definitions into newfile.sql.
- Drop necessary objects.
- Run the script newfile.sql after altering the tablespaces.
- Import from the backup for the necessary objects.

69. List the Optional Flexible Architecture (OFA) of Oracle database? How
can we organize the tablespaces in Oracle database to have maximum
performance?

SYSTEM - Data dictionary tables.

DATA - Standard operational tables.
DATA2- Static tables used for standard operations
INDEXES - Indexes for Standard operational tables.
INDEXES1 - Indexes of static tables used for standard operations.
TOOLS - Tools table.
TOOLS1 - Indexes for tools table.
RBS - Standard Operations Rollback Segments,
RBS1,RBS2 - Additional/Special Rollback segments.
TEMP - Temporary purpose tablespace
TEMP_USER - Temporary tablespace for users.
USERS - User tablespace.

70. How will you force database to use particular rollback segment?

SET TRANSACTION USE ROLLBACK SEGMENT rbs_name.

71. What is meant by free extent?

A free extent is a collection of continuous free blocks in tablespace. When a

segment is dropped its extents are reallocated and are marked as free.

72.Which parameter in Storage clause will reduce number of rows per

block?

PCTFREE parameter

Row size also reduces no of rows per block.

73. What is the significance of having storage clause?

We can plan the storage for a table as how much initial extents are required, how
much can be extended next, how much % should leave free for managing row
updating, etc.,

74. How does Space allocation table place within a block?

Each block contains entries as follows

Fixed block header
Variable block header
Row Header, row date (multiple rows may exists)
PCTEREE (% of free space for row updating in future)

75. What is the role of PCTFREE parameter is storage clause?

This is used to reserve certain amount of space in a block for expansion of rows.

76. What is the OPTIMAL parameter?

It is used to set the optimal length of a rollback segment.

77. What is the functionality of SYSTEM table space?

To manage the database level transactions such as modifications of the data

dictionary table that record information about the free space usage.

78. How will you create multiple rollback segments in a database?

- Create a database, which implicitly creates a SYSTEM rollback segment in a

SYSTEM tablespace.

- Create a second rollback segment name R0 in the SYSTEM tablespace.

- Make new rollback segment available (after shutdown, modify init.ora file and
start database)

- Create other tablespaces (RBS) for rollback segments.

- Deactivate rollback segment R0 and activate the newly created rollback

segments.

79. How the space utilization takes place within rollback segments?
It will try to fit the transaction in a cyclic fashion to all existing extents. Once it
found an extent is in use then it forced to acquire a new extent (number of
extents is based on the optimal size)

80. Why query fails sometimes?

Rollback segment dynamically extent to handle larger transactions entry loads.

A single transaction may wipeout all available free space in the rollback segment
tablespace. This prevents other user using rollback segments.

81. How will you monitor the space allocation?

By querying DBA_SEGMENT table/view

82. How will you monitor rollback segment status?

Querying the DBA_ROLLBACK_SEGS view

IN USE - Rollback Segment is on-line.

AVAILABLE - Rollback Segment available but not on-line.
OFF-LINE - Rollback Segment off-line
INVALID - Rollback Segment Dropped.
NEEDS RECOVERY - Contains data but need recovery or corrupted.
PARTLY AVAILABLE - Contains data from an unresolved transaction involving a
distributed database.

83. List the sequence of events when a large transaction that exceeds
beyond its optimal value when an entry wraps and causes the rollback
segment to expand into another extend.

Transaction Begins.

An entry is made in the RES header for new transactions entry

Transaction acquires blocks in an extent of RBS

The entry attempts to wrap into second extent. None is available, so that the
RBS must extent.

The RBS checks to see if it is part of its OPTIMAL size.

RBS chooses its oldest inactive segment.
Oldest inactive segment is eliminated.
RBS extents
The data dictionary tables for space management are updated.
Transaction Completes.
84. How can we plan storage for very large tables?

Limit the number of extents in the table

Separate table from its indexes.
Allocate sufficient temporary storage.

85. How will you estimate the space required by a non-clustered tables?

Calculate the total header size

Calculate the available data space per data block
Calculate the combined column lengths of the average row
Calculate the total average row size.
Calculate the average number rows that can fit in a block
Calculate the number of blocks and bytes required for the table.

After arriving the calculation, add 10 % additional space to calculate the initial
extent size for a working table.

86. It is possible to use raw devices as data files and what are the
advantages over file system files?

Yes.

The advantages over file system files are that I/O will be improved because
Oracle is bye-passing the kernel which writing into disk. Disk corruption will be
very less.

87. What is a Control file?

Database's overall physical architecture is maintained in a file called control file.

It will be used to maintain internal consistency and guide recovery operations.
Multiple copies of control files are advisable.

88. How to implement the multiple control files for an existing database?

Shutdown the database

Copy one of the existing controlfile to new location
Edit Config ora file by adding new control filename
Restart the database.

89. What is redo log file mirroring? How can be achieved?

Process of having a copy of redo log files is called mirroring.

This can be achieved by creating group of log files together, so that LGWR will
automatically writes them to all the members of the current on-line redo log
group. If any one group fails then database automatically switch over to next
group. It degrades performance.

90. What is advantage of having disk shadowing / mirroring?

Shadow set of disks save as a backup in the event of disk failure. In most
operating systems if any disk failure occurs it automatically switchover to place of
failed disk.

Improved performance because most OS support volume shadowing can direct

file I/O request to use the shadow set of files instead of the main set of files. This
reduces I/O load on the main set of disks.

91. What is use of rollback segments in Oracle database?

They allow the database to maintain read consistency between multiple

transactions.

92. What is a rollback segment entry?

It is the set of before image data blocks that contain rows that are modified by a
transaction.
Each rollback segment entry must be completed within one rollback segment.

A single rollback segment can have multiple rollback segment entries.

93. What is hit ratio?

It is a measure of well the data cache buffer is handling requests for data.

Hit Ratio = (Logical Reads - Physical Reads - Hits Misses)/ Logical Reads.

94. When will be a segment released?

When Segment is dropped.

When Shrink (RBS only)
When truncated (TRUNCATE used with drop storage option)

95. What are disadvantages of having raw devices?

We should depend on export/import utility for backup/recovery (fully reliable)

The tar command cannot be used for physical file backup, instead we can use dd
command, which is less flexible and has limited recoveries.
96. List the factors that can affect the accuracy of the estimations?

- The space used transaction entries and deleted records, does not become free
immediately after completion due to delayed cleanout.

- Trailing nulls and length bytes are not stored.

- Inserts of, updates to and deletes of rows as well as columns larger than a
single data block, can cause fragmentation a chained row pieces.

Database Security & Administration

97. What is user Account in Oracle database?

A user account is not a physical structure in database but it is having important

relationship to the objects in the database and will be having certain privileges.

98. How will you enforce security using stored procedures?

Don't grant user access directly to tables within the application.

Instead grant the ability to access the procedures that access the tables.

When procedure executed it will execute the privilege of procedures owner.

Users cannot access tables except via the procedure.

99. What are the dictionary tables used to monitor a database space?

DBA_FREE_SPACE
DBA_SEGMENTS
DBA_DATA_FILES.

SQL*Plus Statements

100. What are the types of SQL statement?

Data Definition Language: CREATE, ALTER, DROP, TRUNCATE, REVOKE, NO
AUDIT & COMMIT.
Data Manipulation Language: INSERT, UPDATE, DELETE, LOCK TABLE,
EXPLAIN PLAN & SELECT.
Transactional Control: COMMIT & ROLLBACK
Session Control: ALTERSESSION & SET ROLE
System Control: ALTER SYSTEM.

101. What is a transaction?

Transaction is logical unit between two commits and commit and rollback.

102. What is difference between TRUNCATE & DELETE?

TRUNCATE commits after deleting entire table i.e., cannot be rolled back.
Database triggers do not fire on TRUNCATE

DELETE allows the filtered deletion. Deleted records can be rolled back or
committed.
Database triggers fire on DELETE.

103. What is a join? Explain the different types of joins?

Join is a query, which retrieves related columns or rows from multiple tables.

Self Join - Joining the table with itself.

Equi Join - Joining two tables by equating two common columns.
Non-Equi Join - Joining two tables by equating two common columns.
Outer Join - Joining two tables in such a way that query can also retrieve rows
that do not have corresponding join value in the other table.

104. What is the sub-query?

Sub-query is a query whose return values are used in filtering conditions of the
main query.

105. What is correlated sub-query?

Correlated sub-query is a sub-query, which has reference to the main query.

106. Explain CONNECT BY PRIOR?

Retrieves rows in hierarchical order eg.

select empno, ename from emp where.

107. Difference between SUBSTR and INSTR?

INSTR (String1, String2 (n, (m)),

INSTR returns the position of the m-th occurrence of the string 2 in string1. The
search begins from nth position of string1.

SUBSTR (String1 n, m)
SUBSTR returns a character string of size m in string1, starting from n-th position
of string1.

108. Explain UNION, MINUS, UNION ALL and INTERSECT?

INTERSECT - returns all distinct rows selected by both queries.

MINUS - returns all distinct rows selected by the first query but not by the
second.
UNION - returns all distinct rows selected by either query
UNION ALL - returns all rows selected by either query, including all duplicates.

109. What is ROWID?

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

long, blockno, rownumber are the components of ROWID.

110. What is the fastest way of accessing a row in a table?

Using ROWID.
CONSTRAINTS

111. What is an integrity constraint?

Integrity constraint is a rule that restricts values to a column in a table.

112. What is referential integrity constraint?

Maintaining data integrity through a set of rules that restrict the values of one or
more columns of the tables based on the values of primary key or unique key of
the referenced table.

113. 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.

114. 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.

115. What are the data types allowed in a table?

CHAR, VARCHAR2, NUMBER, DATE, RAW, LONG and LONG RAW.

116. What is difference between CHAR and VARCHAR2? What is the

maximum SIZE allowed for each type?

CHAR pads blank spaces to the maximum length.

VARCHAR2 does not pad blank spaces.
For CHAR the maximum length is 255 and 2000 for VARCHAR2.

117. How many LONG columns are allowed in a table? Is it possible to use
LONG columns in WHERE clause or ORDER BY?

Only one LONG column is allowed. It is not possible to use LONG column in
WHERE or ORDER BY clause.

118. What are the pre-requisites to modify datatype of a column and to add
a column with NOT NULL constraint?

- To modify the datatype of a column the column must be empty.

- To add a column with NOT NULL constrain, the table must be empty.

119. Where the integrity constraints are stored in data dictionary?

The integrity constraints are stored in USER_CONSTRAINTS.

120. How will you activate/deactivate integrity constraints?

The integrity constraints can be enabled or disabled by ALTER TABLE ENABLE

CONSTRAINT / DISABLE CONSTRAINT.

121. If unique key constraint on DATE column is created, will it validate the
rows that are inserted with SYSDATE?

It won't, Because SYSDATE format contains time attached with it.

122. What is a database link?

Database link is a named path through which a remote database can be

accessed.
123. How to access the current value and next value from a sequence? Is it
possible to access the current value in a session before accessing next
value?

Sequence name CURRVAL, sequence name NEXTVAL. It is not possible. Only if

you access next value in the session, current value can be accessed.

124. What is CYCLE/NO CYCLE in a Sequence?

CYCLE specifies that the sequence continue to generate values after reaching
either maximum or minimum value. After pan-ascending sequence reaches its
maximum value, it generates its minimum value. After a descending sequence
reaches its minimum, it generates its maximum.

NO CYCLE specifies that the sequence cannot generate more values after
reaching its maximum or minimum value.

125. What are the advantages of VIEW?

- To protect some of the columns of a table from other users.

- To hide complexity of a query.
- To hide complexity of calculations.

126. Can a view be updated/inserted/deleted? If Yes - under what

conditions?

A View can be updated/deleted/inserted if it has only one base table if the view is
based on columns from one or more tables then insert, update and delete is not
possible.

127. If a view on a single base table is manipulated will the changes be

reflected on the base table?

If changes are made to the tables and these tables are the base tables of a view,
then the changes will be reference on the view.
Oracle Interview Questions and Answers : SQL
1. To see current user name
Sql> show user;
2. Change SQL prompt name
SQL> set sqlprompt “Manimara > “
Manimara >
Manimara >
3. Switch to DOS prompt
SQL> host
4. How do I eliminate the duplicate rows ?
SQL> delete from table_name where rowid not in (select max(rowid) from table
group by duplicate_values_field_name);
or
SQL> delete duplicate_values_field_name dv from table_name ta where rowid
<(select min(rowid) from table_name tb where ta.dv=tb.dv);
Example.
Table Emp
Empno Ename
101 Scott
102 Jiyo
103 Millor
104 Jiyo
105 Smith
delete ename from emp a where rowid < ( select min(rowid) from emp b where
a.ename = b.ename);
The output like,
Empno Ename
101 Scott
102 Millor
103 Jiyo
104 Smith
5. How do I display row number with records?
To achive this use rownum pseudocolumn with query, like SQL> SQL> select
rownum, ename from emp;
Output:
1 Scott
2 Millor
3 Jiyo
4 Smith
6. Display the records between two range
select rownum, empno, ename from emp where rowid in
(select rowid from emp where rownum <=&upto
minus
select rowid from emp where rownum<&Start);
Enter value for upto: 10
Enter value for Start: 7
 ROWNUM EMPNO ENAME
--------- --------- ----------
1 7782 CLARK
2 7788 SCOTT
3 7839 KING
4 7844 TURNER
7. I know the nvl function only allows the same data type(ie. number or
char or date Nvl(comm, 0)), if commission is null then the text “Not
Applicable” want to display, instead of blank space. How do I write the
query?
SQL> select nvl(to_char(comm.),'NA') from emp;
Output :
NVL(TO_CHAR(COMM),'NA')
-----------------------
NA
300
500
NA
1400
NA
NA
8. Oracle cursor : Implicit & Explicit cursors
Oracle uses work areas called private SQL areas to create SQL statements.
PL/SQL construct to identify each and every work are used, is called as Cursor.
For SQL queries returning a single row, PL/SQL declares all implicit cursors.
For queries that returning more than one row, the cursor needs to be explicitly
declared.
9. Explicit Cursor attributes
There are four cursor attributes used in Oracle
cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT,
cursor_name%ISOPEN
10. Implicit Cursor attributes
Same as explicit cursor but prefixed by the word SQL
SQL%Found, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN
Tips : 1. Here SQL%ISOPEN is false, because oracle automatically closed the
implicit cursor after executing SQL statements.
: 2. All are Boolean attributes.
11. Find out nth highest salary from emp table
SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT
(DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
Enter value for n: 2
SAL
---------
3700
12. To view installed Oracle version information
SQL> select banner from v$version;
13. Display the number value in Words
SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))
from emp;
the output like,
SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))
--------- -----------------------------------------------------
800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty
If you want to add some text like,
Rs. Three Thousand only.
SQL> select sal "Salary ",
(' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| ' only.'))
"Sal in Words" from emp
/
Salary Sal in Words
------- ------------------------------------------------------
800 Rs. Eight Hundred only.
1600 Rs. One Thousand Six Hundred only.
1250 Rs. One Thousand Two Hundred Fifty only.
14. Display Odd/ Even number of records
Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
1
3
5
Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
2
4
6
15. Which date function returns number value?
months_between
16. Any three PL/SQL Exceptions?
Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others
17. What are PL/SQL Cursor Exceptions?
Cursor_Already_Open, Invalid_Cursor
18. Other way to replace query result null value with a text
SQL> Set NULL ‘N/A’
to reset SQL> Set NULL ‘’
19. What are the more common pseudo-columns?
SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM
20. What is the output of SIGN function?
1 for positive value,
0 for Zero,
-1 for Negative value.
21. What is the maximum number of triggers, can apply to a single table?
12 triggers.
PL/SQL interview qiuestions Database
1. Which of the following statements is true about implicit cursors?
1. Implicit cursors are used for SQL statements that are not named.
2. Developers should use implicit cursors with great care.
3. Implicit cursors are used in cursor for loops to handle data
processing.
4. Implicit cursors are no longer a feature in Oracle.
2. Which of the following is not a feature of a cursor FOR loop?
1. Record type declaration.
2. Opening and parsing of SQL statements.
3. Fetches records from cursor.
4. Requires exit condition to be defined.
3. A developer would like to use referential datatype declaration on a
variable. The variable name is EMPLOYEE_LASTNAME, and the
corresponding table and column is EMPLOYEE, and LNAME,
respectively. How would the developer define this variable using
referential datatypes?
1. Use employee.lname%type.
2. Use employee.lname%rowtype.
3. Look up datatype for EMPLOYEE column on LASTNAME table and
use that.
4. Declare it to be type LONG.
4. Which three of the following are implicit cursor attributes?
1. %found
2. %too_many_rows
3. %notfound
4. %rowcount
5. %rowtype
5. If left out, which of the following would cause an infinite loop to
occur in a simple loop?
1. LOOP
2. END LOOP
3. IF-THEN
4. EXIT
6. Which line in the following statement will produce an error?
1. cursor action_cursor is
2. select name, rate, action
3. into action_record
4. from action_table;
5. There are no errors in this statement.
7. The command used to open a CURSOR FOR loop is
1. open
2. fetch
3. parse
4. None, cursor for loops handle cursor opening implicitly.
8. What happens when rows are found using a FETCH statement
 1. It causes the cursor to close
2. It causes the cursor to open
3. It loads the current row values into variables
4. It creates the variables to hold the current row values
9. Read the following code:
10. CREATE OR REPLACE PROCEDURE find_cpt
11. (v_movie_id {Argument Mode} NUMBER, v_cost_per_ticket
{argument mode} NUMBER)
12. IS
13. BEGIN
14. IF v_cost_per_ticket > 8.5 THEN
15. SELECT cost_per_ticket
16. INTO v_cost_per_ticket
17. FROM gross_receipt
18. WHERE movie_id = v_movie_id;
19. END IF;
20. END;
Which mode should be used for V_COST_PER_TICKET?
1. IN
2. OUT
3. RETURN
4. IN OUT
21. Read the following code:
22. CREATE OR REPLACE TRIGGER update_show_gross
23. {trigger information}
24. BEGIN
25. {additional code}
26. END;
The trigger code should only execute when the column,
COST_PER_TICKET, is greater than $3. Which trigger information will
you add?
1. WHEN (new.cost_per_ticket > 3.75)
2. WHEN (:new.cost_per_ticket > 3.75
3. WHERE (new.cost_per_ticket > 3.75)
4. WHERE (:new.cost_per_ticket > 3.75)
27. What is the maximum number of handlers processed before the
PL/SQL block is exited when an exception occurs?
1. Only one
2. All that apply
3. All referenced
4. None
28. For which trigger timing can you reference the NEW and OLD
qualifiers?
1. Statement and Row
2. Statement only
3. Row only
 4. Oracle Forms trigger
29. Read the following code:
30. CREATE OR REPLACE FUNCTION get_budget(v_studio_id IN
NUMBER)
RETURN number IS

v_yearly_budget NUMBER;

BEGIN
SELECT yearly_budget
INTO v_yearly_budget
FROM studio
WHERE id = v_studio_id;

RETURN v_yearly_budget;
END;
Which set of statements will successfully invoke this function within
SQL*Plus?
1. VARIABLE g_yearly_budget NUMBER
EXECUTE g_yearly_budget := GET_BUDGET(11);
2. VARIABLE g_yearly_budget NUMBER
EXECUTE :g_yearly_budget := GET_BUDGET(11);
3. VARIABLE :g_yearly_budget NUMBER
EXECUTE :g_yearly_budget := GET_BUDGET(11);
4. VARIABLE g_yearly_budget NUMBER
:g_yearly_budget := GET_BUDGET(11);
31. CREATE OR REPLACE PROCEDURE update_theater
32. (v_name IN VARCHAR v_theater_id IN NUMBER) IS
33. BEGIN
34. UPDATE theater
35. SET name = v_name
36. WHERE id = v_theater_id;
37. END update_theater;
38. When invoking this procedure, you encounter the error:
ORA-000: Unique constraint(SCOTT.THEATER_NAME_UK)
violated.
How should you modify the function to handle this error?
1. An user defined exception must be declared and associated with
the error code and handled in the EXCEPTION section.
2. Handle the error in EXCEPTION section by referencing the error
code directly.
3. Handle the error in the EXCEPTION section by referencing the
UNIQUE_ERROR predefined exception.
 4. Check for success by checking the value of SQL%FOUND
immediately after the UPDATE statement.
39. Read the following code:
40. CREATE OR REPLACE PROCEDURE calculate_budget IS
41. v_budget studio.yearly_budget%TYPE;
42. BEGIN
43. v_budget := get_budget(11);
44. IF v_budget < 30000
45. THEN
46. set_budget(11,30000000);
47. END IF;
48. END;
You are about to add an argument to CALCULATE_BUDGET. What
effect will this have?
1. The GET_BUDGET function will be marked invalid and must be
recompiled before the next execution.
2. The SET_BUDGET function will be marked invalid and must be
recompiled before the next execution.
3. Only the CALCULATE_BUDGET procedure needs to be
recompiled.
4. All three procedures are marked invalid and must be recompiled.
49. Which procedure can be used to create a customized error
message?
1. RAISE_ERROR
2. SQLERRM
3. RAISE_APPLICATION_ERROR
4. RAISE_SERVER_ERROR
50. The CHECK_THEATER trigger of the THEATER table has been
disabled. Which command can you issue to enable this trigger?
1. ALTER TRIGGER check_theater ENABLE;
2. ENABLE TRIGGER check_theater;
3. ALTER TABLE check_theater ENABLE check_theater;
4. ENABLE check_theater;
51. Examine this database trigger
52. CREATE OR REPLACE TRIGGER prevent_gross_modification
53. {additional trigger information}
54. BEGIN
55. IF TO_CHAR(sysdate, DY) = MON
56. THEN
57. RAISE_APPLICATION_ERROR(-20000,Gross receipts cannot
be deleted on Monday);
58. END IF;
59. END;
This trigger must fire before each DELETE of the GROSS_RECEIPT
table. It should fire only once for the entire DELETE statement. What
additional information must you add?
 1. BEFORE DELETE ON gross_receipt
2. AFTER DELETE ON gross_receipt
3. BEFORE (gross_receipt DELETE)
4. FOR EACH ROW DELETED FROM gross_receipt
60. Examine this function:
61. CREATE OR REPLACE FUNCTION set_budget
62. (v_studio_id IN NUMBER, v_new_budget IN NUMBER) IS
63. BEGIN
64. UPDATE studio
65. SET yearly_budget = v_new_budget
WHERE id = v_studio_id;

IF SQL%FOUND THEN
RETURN TRUEl;
ELSE
RETURN FALSE;
END IF;

COMMIT;
END;
Which code must be added to successfully compile this function?
1. Add RETURN right before the IS keyword.
2. Add RETURN number right before the IS keyword.
3. Add RETURN boolean right after the IS keyword.
4. Add RETURN boolean right before the IS keyword.
66. Under which circumstance must you recompile the package body
after recompiling the package specification?
1. Altering the argument list of one of the package constructs
2. Any change made to one of the package constructs
3. Any SQL statement change made to one of the package constructs
4. Removing a local variable from the DECLARE section of one of the
package constructs
67. Procedure and Functions are explicitly executed. This is different
from a database trigger. When is a database trigger executed?
1. When the transaction is committed
2. During the data manipulation statement
3. When an Oracle supplied package references the trigger
4. During a data manipulation statement and when the transaction is
committed
68. Which Oracle supplied package can you use to output values and
messages from database triggers, stored procedures and functions
within SQL*Plus?
1. DBMS_DISPLAY
2. DBMS_OUTPUT
 3. DBMS_LIST
4. DBMS_DESCRIBE
69. What occurs if a procedure or function terminates with failure
without being handled?
1. Any DML statements issued by the construct are still pending and
can be committed or rolled back.
2. Any DML statements issued by the construct are committed
3. Unless a GOTO statement is used to continue processing within the
BEGIN section, the construct terminates.
4. The construct rolls back any DML statements issued and returns
the unhandled exception to the calling environment.
70. Examine this code
71. BEGIN
72. theater_pck.v_total_seats_sold_overall :=
theater_pck.get_total_for_year;
73. END;
For this code to be successful, what must be true?
1. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the
GET_TOTAL_FOR_YEAR function must exist only in the body of
the THEATER_PCK package.
2. Only the GET_TOTAL_FOR_YEAR variable must exist in the
specification of the THEATER_PCK package.
3. Only the V_TOTAL_SEATS_SOLD_OVERALL variable must exist
in the specification of the THEATER_PCK package.
4. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the
GET_TOTAL_FOR_YEAR function must exist in the specification of
the THEATER_PCK package.
74. A stored function must return a value based on conditions that are
determined at runtime. Therefore, the SELECT statement cannot be
hard-coded and must be created dynamically when the function is
executed. Which Oracle supplied package will enable this feature?
1. DBMS_DDL
2. DBMS_DML
3. DBMS_SYN
4. DBMS_SQL
Database management interview questions Database
1. What is a Cartesian product? What causes it?
Expected answer:
A Cartesian product is the result of an unrestricted join of two or more tables. The
result set of a three table Cartesian product will have x * y * z number of rows
where x, y, z correspond to the number of rows in each table involved in the join.
It is causes by specifying a table in the FROM clause without joining it to another
table.
2. What is an advantage to using a stored procedure as opposed to passing
an SQL query from an application.
Expected answer:
A stored procedure is pre-loaded in memory for faster execution. It allows the
DBMS control of permissions for security purposes. It also eliminates the need to
recompile components when minor changes occur to the database.
3. What is the difference of a LEFT JOIN and an INNER JOIN statement?
Expected answer:
A LEFT JOIN will take ALL values from the first declared table and matching
values from the second declared table based on the column the join has been
declared on. An INNER JOIN will take only matching values from both tables
4. When a query is sent to the database and an index is not being used,
what type of execution is taking place?
Expected answer:
A table scan.
5. What are the pros and cons of using triggers?
Expected answer:
A trigger is one or more statements of SQL that are being executed in event of
data modification in a table to which the trigger belongs.
Triggers enhance the security, efficiency, and standardization of databases.
Triggers can be beneficial when used:
– to check or modify values before they are actually updated or inserted in the
database. This is useful if you need to transform data from the way the user sees
it to some internal database format.
– to run other non-database operations coded in user-defined functions
– to update data in other tables. This is useful for maintaining relationships
between data or in keeping audit trail information.
– to check against other data in the table or in other tables. This is useful to
ensure data integrity when referential integrity constraints aren’t appropriate, or
when table check constraints limit checking to the current table only.
1. 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. The benefit of taking a hot backup is that the database is still available for
use while the backup is occurring and you can recover the database to any point
in time. The benefit of taking a cold backup is that it is typically easier to
administer the backup and recovery process. In addition, since you are taking
cold backups the database does not require being in archive log mode and thus
there will be a slight performance gain as the database is not cutting archive logs
to disk.

2. You have just had to restore from backup and do not have any control files.
How would you go about bringing up this database?

I would create a text based backup control file, stipulating where on disk all the
data files where and then issue the recover command with the using backup
control file clause.

3. How do you switch from an init.ora file to a spfile?

Issue the create spfile from pfile command.

4. Explain the difference between a data block, an extent and a segment.

A data block is the smallest unit of logical storage for a database object. As
objects grow they take chunks of additional storage that are composed of
contiguous data blocks. These groupings of contiguous data blocks are called
extents. All the extents that an object takes when grouped together are
considered the segment of the database object.

5. Give two examples of how you might determine the structure of the table
DEPT.

Use the describe command or use the dbms_metadata.get_ddl package.

6. Where would you look for errors from the database engine?

In the alert log.

7. Compare and contrast TRUNCATE and DELETE for a table.

Both the truncate and delete command have the desired outcome of getting rid of
all the rows in a table. The difference between the two is that the truncate
command is a DDL operation and just moves the high water mark and produces
a now rollback. The delete command, on the other hand, is a DML operation,
which will produce a rollback and thus take longer to complete.

8. Give the reasoning behind using an index.

Faster access to data blocks in a table.

9. Give the two types of tables involved in producing a star schema and the
type of data they hold.

Fact tables and dimension tables. A fact table contains measurements while
dimension tables will contain data that will help describe the fact tables.

10. . What type of index should you use on a fact table?

A Bitmap index.

11. Give two examples of referential integrity constraints.

A primary key and a foreign key.

12. A table is classified as a parent table and you want to drop and re-create it.
How would you do this without affecting the children tables?

Disable the foreign key constraint to the parent, drop the table, re-create the
table, enable the foreign key constraint.

13. Explain the difference between ARCHIVELOG mode and NOARCHIVELOG

mode and the benefits and disadvantages to each.

ARCHIVELOG mode is a mode that you can put the database in for creating a
backup of all transactions that have occurred in the database so that you can
recover to any point in time. NOARCHIVELOG mode is basically the absence of
ARCHIVELOG mode and has the disadvantage of not being able to recover to
any point in time. NOARCHIVELOG mode does have the advantage of not
having to write transactions to an archive log and thus increases the performance
of the database slightly.

14. What command would you use to create a backup control file?

Alter database backup control file to trace.

15. Give the stages of instance startup to a usable state where normal users may
access it.

STARTUP NOMOUNT - Instance startup

STARTUP MOUNT - The database is mounted

STARTUP OPEN - The database is opened

16. What column differentiates the V$ views to the GV$ views and how?

The INST_ID column which indicates the instance in a RAC environment the
information came from.

17. How would you go about generating an EXPLAIN plan?

Create a plan table with utlxplan.sql.

Use the explain plan set statement_id = 'tst1' into plan_table for a SQL statement

Look at the explain plan with utlxplp.sql or utlxpls.sql

18. How would you go about increasing the buffer cache hit ratio?

Use the buffer cache advisory over a given workload and then query the
v$db_cache_advice table. If a change was necessary then I would use the alter
system set db_cache_size command.

19. Explain an ORA-01555

You get this error when you get a snapshot too old within rollback. It can usually
be solved by increasing the undo retention or increasing the size of rollbacks.
You should also look at the logic involved in the application getting the error
message.

20. Explain the difference between $ORACLE_HOME and $ORACLE_BASE.

ORACLE_BASE is the root directory for oracle. ORACLE_HOME located

beneath ORACLE_BASE is where the oracle products reside.

21. How would you determine the time zone under which a database was
operating?

select DBTIMEZONE from dual;

22. Explain the use of setting GLOBAL_NAMES equal to TRUE.

Setting GLOBAL_NAMES dictates how you might connect to a database. This
variable is either TRUE or FALSE and if it is set to TRUE it enforces database
links to have the same name as the remote database to which they are linking.

23. What command would you use to encrypt a PL/SQL application?

WRAP

24. 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.

25. Explain the use of table functions.

Table functions are designed to return a set of rows through PL/SQL logic but are
intended to be used as a normal table or view in a SQL statement. They are also
used to pipeline information in an ETL process.

26. Name three advisory statistics you can collect.

Buffer Cache Advice, Segment Level Statistics, & Timed Statistics

27. Where in the Oracle directory tree structure are audit traces placed?

In unix $ORACLE_HOME/rdbms/audit, in Windows the event viewer

28. Explain materialized views and how they are used.

Materialized views are objects that are reduced sets of information that have
been summarized, grouped, or aggregated from base tables. They are typically
used in data warehouse or decision support systems.

29. When a user process fails, what background process cleans up after it?

PMON

30. What background process refreshes materialized views?

The Job Queue Processes.

31. How would you determine what sessions are connected and what resources
they are waiting for?

Use of V$SESSION and V$SESSION_WAIT

32. 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.

33. How would you force a log switch?

ALTER SYSTEM SWITCH LOGFILE;

34. Give two methods you could use to determine what DDL changes have been
made.

You could use Logminer or Streams

35. What does coalescing a tablespace do?

Coalescing is only valid for dictionary-managed tablespaces and de-fragments

space by combining neighboring free extents into large single extents.

36. What is the difference between a TEMPORARY tablespace and a

PERMANENT tablespace?

A temporary tablespace is used for temporary objects such as sort structures

while permanent tablespaces are used to store those objects meant to be used
as the true objects of the database.

37. Name a tablespace automatically created when you create a database.

The SYSTEM tablespace.

38. When creating a user, what permissions must you grant to allow them to
connect to the database?

Grant the CONNECT to the user.

39. How do you add a data file to a tablespace?

ALTER TABLESPACE <tablespace_name> ADD DATAFILE <datafile_name>

SIZE <size>
40. How do you resize a data file?

ALTER DATABASE DATAFILE <datafile_name> RESIZE <new_size>;

41. What view would you use to look at the size of a data file?

DBA_DATA_FILES

42. What view would you use to determine free space in a tablespace?

DBA_FREE_SPACE

43. How would you determine who has added a row to a table?

Turn on fine grain auditing for the table.

44. How can you rebuild an index?

ALTER INDEX <index_name> REBUILD;

45. Explain what partitioning is and what its benefit is.

Partitioning is a method of taking large tables and indexes and splitting them into
smaller, more manageable pieces.

46. You have just compiled a PL/SQL package but got errors, how would you
view the errors?

SHOW ERRORS

47. How can you gather statistics on a table?

The ANALYZE command.

48. How can you enable a trace for a session?

Use the DBMS_SESSION.SET_SQL_TRACE or

Use ALTER SESSION SET SQL_TRACE = TRUE;

49. What is the difference between the SQL*Loader and IMPORT utilities?

These two Oracle utilities are used for loading data into the database. The
difference is that the import utility relies on the data being produced by another
Oracle utility EXPORT while the SQL*Loader utility allows data to be loaded that
has been produced by other utilities from different data sources just so long as it
conforms to ASCII formatted or delimited files.

50. Name two files used for network connection to a database.

TNSNAMES.ORA and SQLNET.ORA

Technical - UNIX

Every DBA should know something about the operating system that the database
will be running on. The questions here are related to UNIX but you should equally
be able to answer questions related to common Windows environments.

1. How do you list the files in an UNIX directory while also showing hidden files?

ls -ltra

2. How do you execute a UNIX command in the background?

Use the "&"

3. What UNIX command will control the default file permissions when files are
created?

Umask

4. Explain the read, write, and execute permissions on a UNIX directory.

Read allows you to see and list the directory contents.

Write allows you to create, edit and delete files and subdirectories in the
directory.

Execute gives you the previous read/write permissions plus allows you to change
into the directory and execute programs or shells from the directory.

5. the difference between a soft link and a hard link?

A symbolic (soft) linked file and the targeted file can be located on the same or
different file system while for a hard link they must be located on the same file
system.

6. Give the command to display space usage on the UNIX file system.

df -lk
7. Explain iostat, vmstat and netstat.

Iostat reports on terminal, disk and tape I/O activity.

Vmstat reports on virtual memory statistics for processes, disk, tape and CPU
activity.

Netstat reports on the contents of network data structures.

8. How would you change all occurrences of a value using VI?

Use :%s/<old>/<new>/g

9. Give two UNIX kernel parameters that effect an Oracle install

SHMMAX & SHMMNI

10. Briefly, how do you install Oracle software on UNIX.

Basically, set up disks, kernel parameters, and run orainst.

1. To see current user name

Sql> show user;

2. Change SQL prompt name

SQL> set sqlprompt “Manimara > “
Manimara >
Manimara >

3. Switch to DOS prompt

SQL> host

4. How do I eliminate the duplicate rows ?

SQL> delete from table_name where rowid not in (select max(rowid) from table
group by duplicate_values_field_name);
or
SQL> delete duplicate_values_field_name dv from table_name ta where rowid
<(select min(rowid) from table_name tb where ta.dv=tb.dv);
Example.
Table Emp
Empno Ename
101 Scott
102 Jiyo
103 Millor
104 Jiyo
105 Smith
delete ename from emp a where rowid < ( select min(rowid) from emp b where
a.ename = b.ename);
The output like,
Empno Ename
101 Scott
102 Millor
103 Jiyo
104 Smith

5. How do I display row number with records?

To achive this use rownum pseudocolumn with query, like SQL> SQL> select
rownum, ename from emp;
Output:
1 Scott
2 Millor
3 Jiyo
4 Smith

6. Display the records between two range

select rownum, empno, ename from emp where rowid in
(select rowid from emp where rownum <=&upto
minus
select rowid from emp where rownum<&Start);
Enter value for upto: 10
Enter value for Start: 7

ROWNUM EMPNO ENAME

--------- --------- ----------
1 7782 CLARK
2 7788 SCOTT
3 7839 KING
4 7844 TURNER

7. I know the nvl function only allows the same data type(ie. number or char or
date Nvl(comm, 0)), if commission is null then the text “Not Applicable” want to
display, instead of blank space. How do I write the query?

SQL> select nvl(to_char(comm.),'NA') from emp;

Output :

NVL(TO_CHAR(COMM),'NA')
-----------------------
NA
300
500
NA
1400
NA
NA

8. Oracle cursor : Implicit & Explicit cursors

Oracle uses work areas called private SQL areas to create SQL statements.
PL/SQL construct to identify each and every work are used, is called as Cursor.
For SQL queries returning a single row, PL/SQL declares all implicit cursors.
For queries that returning more than one row, the cursor needs to be explicitly
declared.

9. Explicit Cursor attributes

There are four cursor attributes used in Oracle
cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT,
cursor_name%ISOPEN

10. Implicit Cursor attributes

Same as explicit cursor but prefixed by the word SQL

SQL%Found, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN

Tips : 1. Here SQL%ISOPEN is false, because oracle automatically closed the

implicit cursor after executing SQL statements.
: 2. All are Boolean attributes.

11. Find out nth highest salary from emp table

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT
(DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);

Enter value for n: 2

SAL
---------
3700

12. To view installed Oracle version information

SQL> select banner from v$version;

13. Display the number value in Words

SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))
from emp;
the output like,
 SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))
--------- -----------------------------------------------------
800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty
If you want to add some text like,
Rs. Three Thousand only.
SQL> select sal "Salary ",
(' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| ' only.'))
"Sal in Words" from emp
/
Salary Sal in Words
------- ------------------------------------------------------
800 Rs. Eight Hundred only.
1600 Rs. One Thousand Six Hundred only.
1250 Rs. One Thousand Two Hundred Fifty only.

14. Display Odd/ Even number of records

Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
1
3
5
Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
2
4
6

15. Which date function returns number value?

months_between

16. Any three PL/SQL Exceptions?

Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others

17. What are PL/SQL Cursor Exceptions?

Cursor_Already_Open, Invalid_Cursor

18. Other way to replace query result null value with a text
SQL> Set NULL ‘N/A’
to reset SQL> Set NULL ‘’

19. What are the more common pseudo-columns?

SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM
20. What is the output of SIGN function?
1 for positive value,
0 for Zero,
-1 for Negative value.

21. What is the maximum number of triggers, can apply to a single table?
12 triggers.

Interview Questions for Oracle, DBA, Developer Candidates

PL/SQL Questions:

1. Describe the difference between a procedure, function and anonymous pl/sql

block.

Level: Low

Expected answer : Candidate should mention use of DECLARE statement, a

function must return a value while a procedure doesn?t have to.

2. What is a mutating table error and how can you get around it?

Level: Intermediate

Expected answer: This happens with triggers. It occurs because the trigger is
trying to update a row it is currently using. The usual fix involves either use of
views or temporary tables so the database is selecting from one while updating
the other.

3. Describe the use of %ROWTYPE and %TYPE in PL/SQL

Level: Low

Expected answer: %ROWTYPE allows you to associate a variable with an entire

table row. The %TYPE associates a variable with a single column type.

4. What packages (if any) has Oracle provided for use by developers?

Level: Intermediate to high

Expected answer: Oracle provides the DBMS_ series of packages. There are
many which developers should be aware of such as DBMS_SQL, DBMS_PIPE,
DBMS_TRANSACTION, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT,
DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE. If they can mention a few
of these and describe how they used them, even better. If they include the SQL
routines provided by Oracle, great, but not really what was asked.

5. Describe the use of PL/SQL tables

Level: Intermediate

Expected answer: PL/SQL tables are scalar arrays that can be referenced by a
binary integer. They can be used to hold values for use in later queries or
calculations. In Oracle 8 they will be able to be of the %ROWTYPE designation,
or RECORD.

6. When is a declare statement needed ?

Level: Low

The DECLARE statement is used in PL/SQL anonymous blocks such as with

stand alone, non-stored PL/SQL procedures. It must come first in a PL/SQL
stand alone file if it is used.

7. In what order should a open/fetch/loop set of commands in a PL/SQL block be

implemented if you use the %NOTFOUND cursor variable in the exit when
statement? Why?

Level: Intermediate

Expected answer: OPEN then FETCH then LOOP followed by the exit when. If
not specified in this order will result in the final return being done twice because
of the way the %NOTFOUND is handled by PL/SQL.

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

Level: Intermediate

Expected answer: SQLCODE returns the value of the error number for the last
error encountered. The SQLERRM returns the actual error message for the last
error encountered. They can be used in exception handling to report, or, store in
an error log table, the error that occurred in the code. These are especially useful
for the WHEN OTHERS exception.

9. How can you find within a PL/SQL block, if a cursor is open?

Level: Low
Expected answer: Use the %ISOPEN cursor status variable.

10. How can you generate debugging output from PL/SQL?

Level:Intermediate to high

Expected answer: Use the DBMS_OUTPUT package. Another possible method

is to just use the SHOW ERROR command, but this only shows errors. The
DBMS_OUTPUT package can be used to show intermediate results from loops
and the status of variables as the procedure is executed. The new package
UTL_FILE can also be used.

11. What are the types of triggers?

Level:Intermediate to high

Expected Answer: There are 12 types of triggers in PL/SQL that consist of

combinations of the BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE,
DELETE and ALL key words:

BEFORE ALL ROW INSERT

AFTER ALL ROW INSERT

BEFORE INSERT

AFTER INSERT etc.

DBA:

1. Give one method for transferring a table from one schema to another:

Level:Intermediate

Expected Answer: There are several possible methods, export-import, CREATE

TABLE... AS SELECT, or COPY.

2. What is the purpose of the IMPORT option IGNORE? What is it?s default
setting?

Level: Low

Expected Answer: The IMPORT IGNORE option tells import to ignore "already
exists" errors. If it is not specified the tables that already exist will be skipped. If it
is specified, the error is ignored and the tables data will be inserted. The default
value is N.
3. You have a rollback segment in a version 7.2 database that has expanded
beyond optimal, how can it be restored to optimal?

Level: Low

Expected answer: Use the ALTER TABLESPACE ..... SHRINK command.

4. If the DEFAULT and TEMPORARY tablespace clauses are left out of a

CREATE USER command what happens? Is this bad or good? Why?

Level: Low

Expected answer: The user is assigned the SYSTEM tablespace as a default

and temporary tablespace. This is bad because it causes user objects and
temporary segments to be placed into the SYSTEM tablespace resulting in
fragmentation and improper table placement (only data dictionary objects and the
system rollback segment should be in SYSTEM).

5. What are some of the Oracle provided packages that DBAs should be aware
of?

Level: Intermediate to High

Expected answer: Oracle provides a number of packages in the form of the

DBMS_ packages owned by the SYS user. The packages used by DBAs may
include: DBMS_SHARED_POOL, DBMS_UTILITY, DBMS_SQL, DBMS_DDL,
DBMS_SESSION, DBMS_OUTPUT and DBMS_SNAPSHOT. They may also try
to answer with the UTL*.SQL or CAT*.SQL series of SQL procedures. These can
be viewed as extra credit but aren?t part of the answer.

6. What happens if the constraint name is left out of a constraint clause?

Level: Low

Expected answer: The Oracle system will use the default name of SYS_Cxxxx
where xxxx is a system generated number. This is bad since it makes tracking
which table the constraint belongs to or what the constraint does harder.

7. What happens if a tablespace clause is left off of a primary key constraint

clause?

Level: Low

Expected answer: This results in the index that is automatically generated being
placed in then users default tablespace. Since this will usually be the same
tablespace as the table is being created in, this can cause serious performance
problems.

8. What is the proper method for disabling and re-enabling a primary key
constraint?

Level: Intermediate

Expected answer: You use the ALTER TABLE command for both. However, for
the enable clause you must specify the USING INDEX and TABLESPACE clause
for primary keys.

9. What happens if a primary key constraint is disabled and then enabled without
fully specifying the index clause?

Level: Intermediate

Expected answer: The index is created in the user?s default tablespace and all
sizing information is lost. Oracle doesn?t store this information as a part of the
constraint definition, but only as part of the index definition, when the constraint
was disabled the index was dropped and the information is gone.

10. (On UNIX) When should more than one DB writer process be used? How
many should be used?

Level: High

Expected answer: If the UNIX system being used is capable of asynchronous IO

then only one is required, if the system is not capable of asynchronous IO then
up to twice the number of disks used by Oracle number of DB writers should be
specified by use of the db_writers initialization parameter.

11. You are using hot backup without being in archivelog mode, can you recover
in the event of a failure? Why or why not?

Level: High

Expected answer: You can?t use hot backup without being in archivelog mode.
So no, you couldn?t recover.

12. What causes the "snapshot too old" error? How can this be prevented or
mitigated?

Level: Intermediate
Expected answer: This is caused by large or long running transactions that have
either wrapped onto their own rollback space or have had another transaction
write on part of their rollback space. This can be prevented or mitigated by
breaking the transaction into a set of smaller transactions or increasing the size
of the rollback segments and their extents.

13. How can you tell if a database object is invalid?

Level: Low

Expected answer: By checking the status column of the DBA_, ALL_ or

USER_OBJECTS views, depending upon whether you own or only have
permission on the view or are using a DBA account.

14. A user is getting an ORA-00942 error yet you know you have granted them
permission on the table, what else should you check?

Level: Low

Expected answer: You need to check that the user has specified the full name of
the object (select empid from scott.emp; instead of select empid from emp;) or
has a synonym that points to the object (create synonym emp for scott.emp;)

15. A developer is trying to create a view and the database won?t let him. He has
the "DEVELOPER" role which has the "CREATE VIEW" system privilege and
SELECT grants on the tables he is using, what is the problem?

Level: Intermediate

Expected answer: You need to verify the developer has direct grants on all tables
used in the view. You can?t create a stored object with grants given through
views.

16. If you have an example table, what is the best way to get sizing data for the
production table implementation?

Level: Intermediate

Expected answer: The best way is to analyze the table and then use the data
provided in the DBA_TABLES view to get the average row length and other
pertinent data for the calculation. The quick and dirty way is to look at the number
of blocks the table is actually using and ratio the number of rows in the table to its
number of blocks against the number of expected rows.

17. How can you find out how many users are currently logged into the
database? How can you find their operating system id?
Level: high

Expected answer: There are several ways. One is to look at the v$session or
v$process views. Another way is to check the current_logins parameter in the
v$sysstat view. Another if you are on UNIX is to do a "ps -ef|grep oracle|wc -l?
command, but this only works against a single instance installation.

18. A user selects from a sequence and gets back two values, his select is:

SELECT pk_seq.nextval FROM dual;

What is the problem?

Level: Intermediate

Expected answer: Somehow two values have been inserted into the dual table.
This table is a single row, single column table that should only have one value in
it.

19. How can you determine if an index needs to be dropped and rebuilt?

Level: Intermediate

Expected answer: Run the ANALYZE INDEX command on the index to validate
its structure and then calculate the ratio of
LF_BLK_LEN/LF_BLK_LEN+BR_BLK_LEN and if it isn?t near 1.0 (i.e. greater
than 0.7 or so) then the index should be rebuilt. Or if the ratio

BR_BLK_LEN/ LF_BLK_LEN+BR_BLK_LEN is nearing 0.3.

SQL/ SQLPlus

1. How can variables be passed to a SQL routine?

Level: Low

Expected answer: By use of the & symbol. For passing in variables the numbers
1-8 can be used (&1, &2,...,&8) to pass the values after the command into the
SQLPLUS session. To be prompted for a specific variable, place the
ampersanded variable in the code itself:

"select * from dba_tables where owner=&owner_name;" . Use of double

ampersands tells SQLPLUS to resubstitute the value for each subsequent use of
the variable, a single ampersand will cause a reprompt for the value unless an
ACCEPT statement is used to get the value from the user.
2. You want to include a carriage return/linefeed in your output from a SQL script,
how can you do this?

Level: Intermediate to high

Expected answer: The best method is to use the CHR() function (CHR(10) is a
return/linefeed) and the concatenation function "||". Another method, although it is
hard to document and isn?t always portable is to use the return/linefeed as a part
of a quoted string.

3. How can you call a PL/SQL procedure from SQL?

Level: Intermediate

Expected answer: By use of the EXECUTE (short form EXEC) command.

4. How do you execute a host operating system command from within SQL?

Level: Low

Expected answer: By use of the exclamation point "!" (in UNIX and some other
OS) or the HOST (HO) command.

5. You want to use SQL to build SQL, what is this called and give an example

Level: Intermediate to high

Expected answer: This is called dynamic SQL. An example would be:

set lines 90 pages 0 termout off feedback off verify off

spool drop_all.sql

select ?drop user ?||username||? cascade;? from dba_users

where username not in ("SYS?,?SYSTEM?);

spool off

Essentially you are looking to see that they know to include a command (in this
case DROP USER...CASCADE;) and that you need to concatenate using the ?||?
the values selected from the database.

6. What SQLPlus command is used to format output from a select?

Level: low
Expected answer: This is best done with the COLUMN command.

7. You want to group the following set of select returns, what can you group on?

Max(sum_of_cost), min(sum_of_cost), count(item_no), item_no

Level: Intermediate

Expected answer: The only column that can be grouped on is the "item_no"
column, the rest have aggregate functions associated with them.

8. What special Oracle feature allows you to specify how the cost based system
treats a SQL statement?

Level: Intermediate to high

Expected answer: The COST based system allows the use of HINTs to control
the optimizer path selection. If they can give some example hints such as FIRST
ROWS, ALL ROWS, USING INDEX, STAR, even better.

9. You want to determine the location of identical rows in a table before

attempting to place a unique index on the table, how can this be done?

Level: High

Expected answer: Oracle tables always have one guaranteed unique column, the
rowid column. If you use a min/max function against your rowid and then select
against the proposed primary key you can squeeze out the rowids of the
duplicate rows pretty quick. For example:

select rowid from emp e

where e.rowid > (select min(x.rowid)

from emp x

where x.emp_no = e.emp_no);

In the situation where multiple columns make up the proposed key, they must all
be used in the where clause.

10. What is a Cartesian product?

Level: Low
Expected answer: A Cartesian product is the result of an unrestricted join of two
or more tables. The result set of a three table Cartesian product will have x * y * z
number of rows where x, y, z correspond to the number of rows in each table
involved in the join.

11. You are joining a local and a remote table, the network manager complains
about the traffic involved, how can you reduce the network traffic?

Level: High

Expected answer: Push the processing of the remote data to the remote instance
by using a view to pre-select the information for the join. This will result in only
the data required for the join being sent across.

12. What is the default ordering of an ORDER BY clause in a SELECT

statement?

Level: Low

Expected answer: Ascending

13. What is tkprof and how is it used?

Level: Intermediate to high

Expected answer: The tkprof tool is a tuning tool used to determine cpu and
execution times for SQL statements. You use it by first setting timed_statistics to
true in the initialization file and then turning on tracing for either the entire
database via the sql_trace parameter or for the session using the ALTER
SESSION command. Once the trace file is generated you run the tkprof tool
against the trace file and then look at the output from the tkprof tool. This can
also be used to generate explain plan output.

14. What is explain plan and how is it used?

Level: Intermediate to high

Expected answer: The EXPLAIN PLAN command is a tool to tune SQL

statements. To use it you must have an explain_table generated in the user you
are running the explain plan for. This is created using the utlxplan.sql script.
Once the explain plan table exists you run the explain plan command giving as
its argument the SQL statement to be explained. The explain_plan table is then
queried to see the execution plan of the statement. Explain plans can also be run
using tkprof.

15. How do you set the number of lines on a page of output? The width?
Level: Low

Expected answer: The SET command in SQLPLUS is used to control the number
of lines generated per page and the width of those lines, for example SET
PAGESIZE 60 LINESIZE 80 will generate reports that are 60 lines long with a
line width of 80 characters. The PAGESIZE and LINESIZE options can be
shortened to PAGES and LINES.

16. How do you prevent output from coming to the screen?

Level: Low

Expected answer: The SET option TERMOUT controls output to the screen.
Setting TERMOUT OFF turns off screen output. This option can be shortened to
TERM.

17. How do you prevent Oracle from giving you informational messages during
and after a SQL statement execution?

Level: Low

Expected answer: The SET options FEEDBACK and VERIFY can be set to OFF.

18. How do you generate file output from SQL?

Level: Low

Expected answer: By use of the SPOOL command

Tuning Questions:

1. A tablespace has a table with 30 extents in it. Is this bad? Why or why not.

Level: Intermediate

Expected answer: Multiple extents in and of themselves aren?t bad. However if

you also have chained rows this can hurt performance.

2. How do you set up tablespaces during an Oracle installation?

Level: Low

Expected answer: You should always attempt to use the Oracle Flexible
Architecture standard or another partitioning scheme to ensure proper separation
of SYSTEM, ROLLBACK, REDO LOG, DATA, TEMPORARY and INDEX
segments.
3. You see multiple fragments in the SYSTEM tablespace, what should you
check first?

Level: Low

Expected answer: Ensure that users don?t have the SYSTEM tablespace as
their TEMPORARY or DEFAULT tablespace assignment by checking the
DBA_USERS view.

4. What are some indications that you need to increase the

SHARED_POOL_SIZE parameter?

Level: Intermediate

Expected answer: Poor data dictionary or library cache hit ratios, getting error
ORA-04031. Another indication is steadily decreasing performance with all other
tuning parameters the same.

5. What is the general guideline for sizing db_block_size and

db_multi_block_read for an application that does many full table scans?

Level: High

Expected answer: Oracle almost always reads in 64k chunks. The two should
have a product equal to 64 or a multiple of 64.

6. What is the fastest query method for a table?

Level: Intermediate

Expected answer: Fetch by rowid

7. Explain the use of TKPROF? What initialization parameter should be turned on

to get full TKPROF output?

Level: High

Expected answer: The tkprof tool is a tuning tool used to determine cpu and
execution times for SQL statements. You use it by first setting timed_statistics to
true in the initialization file and then turning on tracing for either the entire
database via the sql_trace parameter or for the session using the ALTER
SESSION command. Once the trace file is generated you run the tkprof tool
against the trace file and then look at the output from the tkprof tool. This can
also be used to generate explain plan output.
8. When looking at v$sysstat you see that sorts (disk) is high. Is this bad or
good? If bad -How do you correct it?

Level: Intermediate

Expected answer: If you get excessive disk sorts this is bad. This indicates you
need to tune the sort area parameters in the initialization files. The major sort are
parameter is the SORT_AREA_SIZe parameter.

9. When should you increase copy latches? What parameters control copy
latches?

Level: high

Expected answer: When you get excessive contention for the copy latches as
shown by the "redo copy" latch hit ratio. You can increase copy latches via the
initialization parameter LOG_SIMULTANEOUS_COPIES to twice the number of
CPUs on your system.

10. Where can you get a list of all initialization parameters for your instance?
How about an indication if they are default settings or have been changed?

Level: Low

Expected answer: You can look in the init.ora file for an indication of manually set
parameters. For all parameters, their value and whether or not the current value
is the default value, look in the v$parameter view.

11. Describe hit ratio as it pertains to the database buffers. What is the difference
between instantaneous and cumulative hit ratio and which should be used for
tuning?

Level: Intermediate

Expected answer: The hit ratio is a measure of how many times the database
was able to read a value from the buffers verses how many times it had to re-
read a data value from the disks. A value greater than 80-90% is good, less could
indicate problems. If you simply take the ratio of existing parameters this will be a
cumulative value since the database started. If you do a comparison between
pairs of readings based on some arbitrary time span, this is the instantaneous
ratio for that time span. Generally speaking an instantaneous reading gives more
valuable data since it will tell you what your instance is doing for the time it was
generated over.

12. Discuss row chaining, how does it happen? How can you reduce it? How do
you correct it?
Level: high

Expected answer: Row chaining occurs when a VARCHAR2 value is updated

and the length of the new value is longer than the old value and won?t fit in the
remaining block space. This results in the row chaining to another block. It can
be reduced by setting the storage parameters on the table to appropriate values.
It can be corrected by export and import of the effected table.

13. When looking at the estat events report you see that you are getting busy
buffer waits. Is this bad? How can you find what is causing it?

Level: high

Expected answer: Buffer busy waits could indicate contention in redo, rollback or
data blocks. You need to check the v$waitstat view to see what areas are
causing the problem. The value of the "count" column tells where the problem is,
the "class" column tells you with what. UNDO is rollback segments, DATA is data
base buffers.

14. If you see contention for library caches how can you fix it?

Level: Intermediate

Expected answer: Increase the size of the shared pool.

15. If you see statistics that deal with "undo" what are they really talking about?

Level: Intermediate

Expected answer: Rollback segments and associated structures.

16. If a tablespace has a default pctincrease of zero what will this cause (in
relationship to the smon process)?

Level: High

Expected answer: The SMON process won?t automatically coalesce its free
space fragments.

17. If a tablespace shows excessive fragmentation what are some methods to

defragment the tablespace? (7.1,7.2 and 7.3 only)

Level: High

Expected answer: In Oracle 7.0 to 7.2 The use of the 'alter session set events
'immediate trace name coalesce level ts#';? command is the easiest way to
defragment contiguous free space fragmentation. The ts# parameter
corresponds to the ts# value found in the ts$ SYS table. In version 7.3 the ?alter
tablespace coalesce;? is best. If the free space isn?t contiguous then export,
drop and import of the tablespace contents may be the only way to reclaim non-
contiguous free space.

18. How can you tell if a tablespace has excessive fragmentation?

Level: Intermediate

If a select against the dba_free_space table shows that the count of a

tablespaces extents is greater than the count of its data files, then it is
fragmented.

19. You see the following on a status report:

redo log space requests 23

redo log space wait time 0

Is this something to worry about? What if redo log space wait time is high? How
can you fix this?

Level: Intermediate

Expected answer: Since the wait time is zero, no. If the wait time was high it
might indicate a need for more or larger redo logs.

20. What can cause a high value for recursive calls? How can this be fixed?

Level: High

Expected answer: A high value for recursive calls is cause by improper cursor
usage, excessive dynamic space management actions, and or excessive
statement re-parses. You need to determine the cause and correct it By either
relinking applications to hold cursors, use proper space management techniques
(proper storage and sizing) or ensure repeat queries are placed in packages for
proper reuse.

21. If you see a pin hit ratio of less than 0.8 in the estat library cache report is this
a problem? If so, how do you fix it?

Level: Intermediate

Expected answer: This indicate that the shared pool may be too small. Increase
the shared pool size.
22. If you see the value for reloads is high in the estat library cache report is this
a matter for concern?

Level: Intermediate

Expected answer: Yes, you should strive for zero reloads if possible. If you see
excessive reloads then increase the size of the shared pool.

23. You look at the dba_rollback_segs view and see that there is a large number
of shrinks and they are of relatively small size, is this a problem? How can it be
fixed if it is a problem?

Level: High

Expected answer: A large number of small shrinks indicates a need to increase

the size of the rollback segment extents. Ideally you should have no shrinks or a
small number of large shrinks. To fix this just increase the size of the extents and
adjust optimal accordingly.

24. You look at the dba_rollback_segs view and see that you have a large
number of wraps is this a problem?

Level: High

Expected answer: A large number of wraps indicates that your extent size for
your rollback segments are probably too small. Increase the size of your extents
to reduce the number of wraps. You can look at the average transaction size in
the same view to get the information on transaction size.

25. In a system with an average of 40 concurrent users you get the following
from a query on rollback extents:

ROLLBACK CUR EXTENTS

--------------------- --------------------------

R01 11

R02 8

R03 12

R04 9

SYSTEM 4
You have room for each to grow by 20 more extents each. Is there a problem?
Should you take any action?

Level: Intermediate

Expected answer: No there is not a problem. You have 40 extents showing and
an average of 40 concurrent users. Since there is plenty of room to grow no
action is needed.

26. You see multiple extents in the temporary tablespace. Is this a problem?

Level: Intermediate

Expected answer: As long as they are all the same size this isn?t a problem. In
fact, it can even improve performance since Oracle won?t have to create a new
extent when a user needs one.

Installation/Configuration

1. Define OFA.

Level: Low

Expected answer: OFA stands for Optimal Flexible Architecture. It is a method of

placing directories and files in an Oracle system so that you get the maximum
flexibility for future tuning and file placement.

2. How do you set up your tablespace on installation?

Level: Low

Expected answer: The answer here should show an understanding of separation

of redo and rollback, data and indexes and isolation os SYSTEM tables from
other tables. An example would be to specify that at least 7 disks should be used
for an Oracle installation so that you can place SYSTEM tablespace on one, redo
logs on two (mirrored redo logs) the TEMPORARY tablespace on another,
ROLLBACK tablespace on another and still have two for DATA and INDEXES.
They should indicate how they will handle archive logs and exports as well. As
long as they have a logical plan for combining or further separation more or less
disks can be specified.

3. What should be done prior to installing Oracle (for the OS and the disks)?

Level: Low
Expected Answer: adjust kernel parameters or OS tuning parameters in
accordance with installation guide. Be sure enough contiguous disk space is
available.

4. You have installed Oracle and you are now setting up the actual instance. You
have been waiting an hour for the initialization script to finish, what should you
check first to determine if there is a problem?

Level: Intermediate to high

Expected Answer: Check to make sure that the archiver isn?t stuck. If archive
logging is turned on during install a large number of logs will be created. This can
fill up your archive log destination causing Oracle to stop to wait for more space.

5. When configuring SQLNET on the server what files must be set up?

Level: Intermediate

Expected answer: INITIALIZATION file, TNSNAMES.ORA file, SQLNET.ORA file

6. When configuring SQLNET on the client what files need to be set up?

Level: Intermediate

Expected answer: SQLNET.ORA, TNSNAMES.ORA

7. What must be installed with ODBC on the client in order for it to work with
Oracle?

Level: Intermediate

Expected answer: SQLNET and PROTOCOL (for example: TCPIP adapter)

layers of the transport programs.

8. You have just started a new instance with a large SGA on a busy existing
server. Performance is terrible, what should you check for?

Level: Intermediate

Expected answer: The first thing to check with a large SGA is that it isn?t being
swapped out.

9. What OS user should be used for the first part of an Oracle installation (on
UNIX)?

Level: low
Expected answer: You must use root first.

10. When should the default values for Oracle initialization parameters be used
as is?

Level: Low

Expected answer: Never

11. How many control files should you have? Where should they be located?

Level: Low

Expected answer: At least 2 on separate disk spindles. Be sure they say on

separate disks, not just file systems.

12. How many redo logs should you have and how should they be configured for
maximum recoverability?

Level: Intermediate

Expected answer: You should have at least three groups of two redo logs with
the two logs each on a separate disk spindle (mirrored by Oracle). The redo logs
should not be on raw devices on UNIX if it can be avoided.

13. You have a simple application with no "hot" tables (i.e. uniform IO and access
requirements). How many disks should you have assuming standard layout for
SYSTEM, USER, TEMP and ROLLBACK tablespaces?

Expected answer: At least 7, see disk configuration answer above.

Data Modeler:

1. Describe third normal form?

Level: Low

Expected answer: Something like: In third normal form all attributes in an entity
are related to the primary key and only to the primary key

2. Is the following statement true or false:

"All relational databases must be in third normal form"

Why or why not?

Level: Intermediate

Expected answer: False. While 3NF is good for logical design most databases, if
they have more than just a few tables, will not perform well using full 3NF.
Usually some entities will be denormalized in the logical to physical transfer
process.

3. What is an ERD?

Level: Low

Expected answer: An ERD is an Entity-Relationship-Diagram. It is used to show

the entities and relationships for a database logical model.

4. Why are recursive relationships bad? How do you resolve them?

Level: Intermediate

A recursive relationship (one where a table relates to itself) is bad when it is a

hard relationship (i.e. neither side is a "may" both are "must") as this can result in
it not being possible to put in a top or perhaps a bottom of the table (for example
in the EMPLOYEE table you couldn?t put in the PRESIDENT of the company
because he has no boss, or the junior janitor because he has no subordinates).
These type of relationships are usually resolved by adding a small intersection
entity.

5. What does a hard one-to-one relationship mean (one where the relationship
on both ends is "must")?

Level: Low to intermediate

Expected answer: This means the two entities should probably be made into one
entity.

6. How should a many-to-many relationship be handled?

Level: Intermediate

Expected answer: By adding an intersection entity table

7. What is an artificial (derived) primary key? When should an artificial (or

derived) primary key be used?

Level: Intermediate
Expected answer: A derived key comes from a sequence. Usually it is used when
a concatenated key becomes too cumbersome to use as a foreign key.

8. When should you consider denormalization?

Level: Intermediate

Expected answer: Whenever performance analysis indicates it would be

beneficial to do so without compromising data integrity.

UNIX:

1. How can you determine the space left in a file system?

Level: Low

Expected answer: There are several commands to do this: du, df, or bdf

2. How can you determine the number of SQLNET users logged in to the UNIX
system?

Level: Intermediate

Expected answer: SQLNET users will show up with a process unique name that
begins with oracle, if you do a ps -ef|grep oracle|wc -l you can get a count of the
number of users.

3. What command is used to type files to the screen?

Level: Low

Expected answer: cat, more, pg

4. What command is used to remove a file?

Level: Low

Expected answer: rm

5. Can you remove an open file under UNIX?

Level: Low

Expected answer: yes

6. How do you create a decision tree in a shell script?

Level: intermediate

Expected answer: depending on shell, usually a case-esac or an if-endif or fi

structure

7. What is the purpose of the grep command?

Level: Low

Expected answer: grep is a string search command that parses the specified
string from the specified file or files

8. The system has a program that always includes the word nocomp in its name,
how can you determine the number of processes that are using this program?

Level: intermediate

Expected answer: ps -ef|grep *nocomp*|wc -l

9. What is an inode?

Level: Intermediate

Expected answer: an inode is a file status indicator. It is stored in both disk and
memory and tracts file status. There is one inode for each file on the system.

10. The system administrator tells you that the system hasn?t been rebooted in 6
months, should he be proud of this?

Level: High

Expected answer: Maybe. Some UNIX systems don?t clean up well after
themselves. Inode problems and dead user processes can accumulate causing
possible performance and corruption problems. Most UNIX systems should have
a scheduled periodic reboot so file systems can be checked and cleaned and
dead or zombie processes cleared out.

11. What is redirection and how is it used?

Level: Intermediate

Expected answer: redirection is the process by which input or output to or from a

process is redirected to another process. This can be done using the pipe symbol
"|", the greater than symbol ">" or the "tee" command. This is one of the
strengths of UNIX allowing the output from one command to be redirected
directly into the input of another command.
12. How can you find dead processes?

Level: Intermediate

Expected answer: ps -ef|grep zombie -- or -- who -d depending on the system.

13. How can you find all the processes on your system?

Level: Low

Expected answer: Use the ps command

14. How can you find your id on a system?

Level: Low

Expected answer: Use the "who am i" command.

15. What is the finger command?

Level: Low

Expected answer: The finger command uses data in the passwd file to give
information on system users.

16. What is the easiest method to create a file on UNIX?

Level: Low

Expected answer: Use the touch command

17. What does >> do?

Level: Intermediate

Expected answer: The ">>" redirection symbol appends the output from the
command specified into the file specified. The file must already have been
created.

18. If you aren?t sure what command does a particular UNIX function what is the
best way to determine the command?

Expected answer: The UNIX man -k command will search the man pages for the
value specified. Review the results from the command to find the command of
interest.
Oracle Troubleshooting:

1. How can you determine if an Oracle instance is up from the operating system
level?

Level: Low

Expected answer: There are several base Oracle processes that will be running
on multi-user operating systems, these will be smon, pmon, dbwr and lgwr. Any
answer that has them using their operating system process showing feature to
check for these is acceptable. For example, on UNIX a ps -ef|grep dbwr will show
what instances are up.

2. Users from the PC clients are getting messages indicating :

Level: Low

ORA-06114: (Cnct err, can't get err txt. See Servr Msgs & Codes Manual)

What could the problem be?

Expected answer: The instance name is probably incorrect in their connection

string.

3. Users from the PC clients are getting the following error stack:

Level: Low

ERROR: ORA-01034: ORACLE not available

ORA-07318: smsget: open error when opening sgadef.dbf file.

HP-UX Error: 2: No such file or directory

What is the probable cause?

Expected answer: The Oracle instance is shutdown that they are trying to
access, restart the instance.

4. How can you determine if the SQLNET process is running for SQLNET V1?
How about V2?

Level: Low

Expected answer: For SQLNET V1 check for the existence of the orasrv process.
You can use the command "tcpctl status" to get a full status of the V1 TCPIP
server, other protocols have similar command formats. For SQLNET V2 check for
the presence of the LISTENER process(s) or you can issue the command "lsnrctl
status".

5. What file will give you Oracle instance status information? Where is it located?

Level: Low

Expected answer: The alert.ora log. It is located in the directory specified by the
background_dump_dest parameter in the v$parameter table.

6. Users aren?t being allowed on the system. The following message is received:

Level: Intermediate

ORA-00257 archiver is stuck. Connect internal only, until freed

What is the problem?

Expected answer: The archive destination is probably full, backup the archive
logs and remove them and the archiver will re-start.

7. Where would you look to find out if a redo log was corrupted assuming you are
using Oracle mirrored redo logs?

Level: Intermediate

Expected answer: There is no message that comes to the SQLDBA or SRVMGR

programs during startup in this situation, you must check the alert.log file for this
information.

8. You attempt to add a datafile and get:

Level: Intermediate

ORA-01118: cannot add anymore datafiles: limit of 40 exceeded

What is the problem and how can you fix it?

Expected answer: When the database was created the db_files parameter in the
initialization file was set to 40. You can shutdown and reset this to a higher value,
up to the value of MAX_DATAFILES as specified at database creation. If the
MAX_DATAFILES is set to low, you will have to rebuild the control file to increase
it before proceeding.
9. You look at your fragmentation report and see that smon hasn?t coalesced any
of you tablespaces, even though you know several have large chunks of
contiguous free extents. What is the problem?

Level: High

Expected answer: Check the dba_tablespaces view for the value of pct_increase
for the tablespaces. If pct_increase is zero, smon will not coalesce their free
space.

10. Your users get the following error:

Level: Intermediate

ORA-00055 maximum number of DML locks exceeded

What is the problem and how do you fix it?

Expected answer: The number of DML Locks is set by the initialization parameter
DML_LOCKS. If this value is set to low (which it is by default) you will get this
error. Increase the value of DML_LOCKS. If you are sure that this is just a
temporary problem, you can have them wait and then try again later and the error
should clear.

11. You get a call from you backup DBA while you are on vacation. He has
corrupted all of the control files while playing with the ALTER DATABASE
BACKUP CONTROLFILE command. What do you do?

Level: High

Expected answer: As long as all datafiles are safe and he was successful with
the BACKUP controlfile command you can do the following:

CONNECT INTERNAL

STARTUP MOUNT

(Take any read-only tablespaces offline before next step ALTER DATABASE
DATAFILE .... OFFLINE;)

RECOVER DATABASE USING BACKUP CONTROLFILE

ALTER DATABASE OPEN RESETLOGS;

(bring read-only tablespaces back online)

Shutdown and backup the system, then restart

If they have a recent output file from the ALTER DATABASE BACKUP CONTROL
FILE TO TRACE; command, they can use that to recover as well.

If no backup of the control file is available then the following will be required:

CONNECT INTERNAL

STARTUP NOMOUNT

CREATE CONTROL FILE .....;

However, they will need to know all of the datafiles, logfiles, and settings for
MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES for
the database to use the command.

1. Which of the following statements is true about implicit cursors?

1. Implicit cursors are used for SQL statements that are not named.
2. Developers should use implicit cursors with great care.
3. Implicit cursors are used in cursor for loops to handle data
processing.
4. Implicit cursors are no longer a feature in Oracle.
2. Which of the following is not a feature of a cursor FOR loop?
1. Record type declaration.
2. Opening and parsing of SQL statements.
3. Fetches records from cursor.
4. Requires exit condition to be defined.
3. A developer would like to use referential datatype declaration on a
variable. The variable name is EMPLOYEE_LASTNAME, and the
corresponding table and column is EMPLOYEE, and LNAME, respectively.
How would the developer define this variable using referential datatypes?
1. Use employee.lname%type.
2. Use employee.lname%rowtype.
3. Look up datatype for EMPLOYEE column on LASTNAME table and
use that.
4. Declare it to be type LONG.
4. Which three of the following are implicit cursor attributes?
1. %found
2. %too_many_rows
3. %notfound
4. %rowcount
 5. %rowtype
5. If left out, which of the following would cause an infinite loop to occur in a
simple loop?
1. LOOP
2. END LOOP
3. IF-THEN
4. EXIT
6. Which line in the following statement will produce an error?
1. cursor action_cursor is
2. select name, rate, action
3. into action_record
4. from action_table;
5. There are no errors in this statement.
7. The command used to open a CURSOR FOR loop is
1. open
2. fetch
3. parse
4. None, cursor for loops handle cursor opening implicitly.
8. What happens when rows are found using a FETCH statement
1. It causes the cursor to close
2. It causes the cursor to open
3. It loads the current row values into variables
4. It creates the variables to hold the current row values
9. Read the following code:
10. CREATE OR REPLACE PROCEDURE find_cpt
11. (v_movie_id {Argument Mode} NUMBER, v_cost_per_ticket {argument
mode} NUMBER)
12. IS
13. BEGIN
14. IF v_cost_per_ticket > 8.5 THEN
15. SELECT cost_per_ticket
16. INTO v_cost_per_ticket
17. FROM gross_receipt
18. WHERE movie_id = v_movie_id;
19. END IF;
20. END;

Which mode should be used for V_COST_PER_TICKET?

1. IN
2. OUT
3. RETURN
4. IN OUT
21. Read the following code:
22. CREATE OR REPLACE TRIGGER update_show_gross
23. {trigger information}
24. BEGIN
25. {additional code}
26. END;

The trigger code should only execute when the column,

COST_PER_TICKET, is greater than $3. Which trigger information will you
add?

1. WHEN (new.cost_per_ticket > 3.75)

2. WHEN (:new.cost_per_ticket > 3.75
3. WHERE (new.cost_per_ticket > 3.75)
4. WHERE (:new.cost_per_ticket > 3.75)
27. What is the maximum number of handlers processed before the PL/SQL
block is exited when an exception occurs?
1. Only one
2. All that apply
3. All referenced
4. None
28. For which trigger timing can you reference the NEW and OLD qualifiers?
1. Statement and Row
2. Statement only
3. Row only
4. Oracle Forms trigger
29. Read the following code:
30. CREATE OR REPLACE FUNCTION get_budget(v_studio_id IN
NUMBER)
RETURN number IS

v_yearly_budget NUMBER;

BEGIN

SELECT yearly_budget

INTO v_yearly_budget

FROM studio

WHERE id = v_studio_id;

RETURN v_yearly_budget;

END;
 Which set of statements will successfully invoke this function within
SQL*Plus?

1. VARIABLE g_yearly_budget NUMBER

EXECUTE g_yearly_budget := GET_BUDGET(11);
2. VARIABLE g_yearly_budget NUMBER
EXECUTE :g_yearly_budget := GET_BUDGET(11);
3. VARIABLE :g_yearly_budget NUMBER
EXECUTE :g_yearly_budget := GET_BUDGET(11);
4. VARIABLE g_yearly_budget NUMBER
:g_yearly_budget := GET_BUDGET(11);
31. CREATE OR REPLACE PROCEDURE update_theater
32. (v_name IN VARCHAR v_theater_id IN NUMBER) IS
33. BEGIN
34. UPDATE theater
35. SET name = v_name
36. WHERE id = v_theater_id;
37. END update_theater;
38.

When invoking this procedure, you encounter the error:

ORA-000: Unique constraint(SCOTT.THEATER_NAME_UK) violated.

How should you modify the function to handle this error?

1. An user defined exception must be declared and associated with

the error code and handled in the EXCEPTION section.
2. Handle the error in EXCEPTION section by referencing the error
code directly.
3. Handle the error in the EXCEPTION section by referencing the
UNIQUE_ERROR predefined exception.
4. Check for success by checking the value of SQL%FOUND
immediately after the UPDATE statement.
39. Read the following code:
40. CREATE OR REPLACE PROCEDURE calculate_budget IS
41. v_budget studio.yearly_budget%TYPE;
42. BEGIN
43. v_budget := get_budget(11);
44. IF v_budget < 30000
45. THEN
46. set_budget(11,30000000);
47. END IF;
48. END;
 You are about to add an argument to CALCULATE_BUDGET. What effect
will this have?

1. The GET_BUDGET function will be marked invalid and must be

recompiled before the next execution.
2. The SET_BUDGET function will be marked invalid and must be
recompiled before the next execution.
3. Only the CALCULATE_BUDGET procedure needs to be
recompiled.
4. All three procedures are marked invalid and must be recompiled.
49. Which procedure can be used to create a customized error message?
1. RAISE_ERROR
2. SQLERRM
3. RAISE_APPLICATION_ERROR
4. RAISE_SERVER_ERROR
50. The CHECK_THEATER trigger of the THEATER table has been disabled.
Which command can you issue to enable this trigger?
1. ALTER TRIGGER check_theater ENABLE;
2. ENABLE TRIGGER check_theater;
3. ALTER TABLE check_theater ENABLE check_theater;
4. ENABLE check_theater;
51. Examine this database trigger
52. CREATE OR REPLACE TRIGGER prevent_gross_modification
53. {additional trigger information}
54. BEGIN
55. IF TO_CHAR(sysdate, DY) = MON
56. THEN
57. RAISE_APPLICATION_ERROR(-20000,Gross receipts cannot be
deleted on Monday);
58. END IF;
59. END;

This trigger must fire before each DELETE of the GROSS_RECEIPT

table. It should fire only once for the entire DELETE statement. What
additional information must you add?

1. BEFORE DELETE ON gross_receipt

2. AFTER DELETE ON gross_receipt
3. BEFORE (gross_receipt DELETE)
4. FOR EACH ROW DELETED FROM gross_receipt
60. Examine this function:
61. CREATE OR REPLACE FUNCTION set_budget
62. (v_studio_id IN NUMBER, v_new_budget IN NUMBER) IS
63. BEGIN
64. UPDATE studio
65. SET yearly_budget = v_new_budget
WHERE id = v_studio_id;

IF SQL%FOUND THEN

RETURN TRUEl;

ELSE

RETURN FALSE;

END IF;

COMMIT;

END;

Which code must be added to successfully compile this function?

1. Add RETURN right before the IS keyword.

2. Add RETURN number right before the IS keyword.
3. Add RETURN boolean right after the IS keyword.
4. Add RETURN boolean right before the IS keyword.
66. Under which circumstance must you recompile the package body after
recompiling the package specification?
1. Altering the argument list of one of the package constructs
2. Any change made to one of the package constructs
3. Any SQL statement change made to one of the package constructs
4. Removing a local variable from the DECLARE section of one of the
package constructs
67. Procedure and Functions are explicitly executed. This is different from a
database trigger. When is a database trigger executed?
1. When the transaction is committed
2. During the data manipulation statement
3. When an Oracle supplied package references the trigger
 4. During a data manipulation statement and when the transaction is
committed
68. Which Oracle supplied package can you use to output values and
messages from database triggers, stored procedures and functions within
SQL*Plus?
1. DBMS_DISPLAY
2. DBMS_OUTPUT
3. DBMS_LIST
4. DBMS_DESCRIBE
69. What occurs if a procedure or function terminates with failure without
being handled?
1. Any DML statements issued by the construct are still pending and
can be committed or rolled back.
2. Any DML statements issued by the construct are committed
3. Unless a GOTO statement is used to continue processing within the
BEGIN section, the construct terminates.
4. The construct rolls back any DML statements issued and returns
the unhandled exception to the calling environment.
70. Examine this code
71. BEGIN
72. theater_pck.v_total_seats_sold_overall :=
theater_pck.get_total_for_year;
73. END;

For this code to be successful, what must be true?

1. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the

GET_TOTAL_FOR_YEAR function must exist only in the body of
the THEATER_PCK package.
2. Only the GET_TOTAL_FOR_YEAR variable must exist in the
specification of the THEATER_PCK package.
3. Only the V_TOTAL_SEATS_SOLD_OVERALL variable must exist
in the specification of the THEATER_PCK package.
4. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the
GET_TOTAL_FOR_YEAR function must exist in the specification of
the THEATER_PCK package.
74. A stored function must return a value based on conditions that are
determined at runtime. Therefore, the SELECT statement cannot be hard-
coded and must be created dynamically when the function is executed.
Which Oracle supplied package will enable this feature?
1. DBMS_DDL
2. DBMS_DML
3. DBMS_SYN
4. DBMS_SQL
 SNAPSHOT TOO OLD ERROR

This article( taken from Oracle website ) will discuss the circumstances under
which a query can return the
Oracle error ORA-01555 "snapshot too old (rollback segment too small)". The
article will then proceed to discuss actions that can be taken to avoid the
error and finally will provide some simple PL/SQL scripts that illustrate the
issues discussed.

Terminology
~~~~~~~~~~~

It is assumed that the reader is familiar with standard Oracle terminology such
as 'rollback segment' and 'SCN'. If not, the reader should first read the Oracle
Server Concepts manual and related Oracle documentation.

In addition to this, two key concepts are briefly covered below which help in
the understanding of ORA-01555:

1. READ CONSISTENCY:
====================

This is documented in the Oracle Server Concepts manual and so will not be
discussed further. However, for the purposes of this article this should be read
and understood if not understood already.

Oracle Server has the ability to have multi-version read consistency which is
invaluable to you because it guarantees that you are seeing a consistent view of
the data (no 'dirty reads').

2. DELAYED BLOCK CLEANOUT:

==========================

This is best illustrated with an example: Consider a transaction that updates a

million row table. This obviously visits a large number of database blocks to
make the change to the data. When the user commits the transaction Oracle
does
NOT go back and revisit these blocks to make the change permanent. It is left
for the next transaction that visits any block affected by the update to 'tidy
up' the block (hence the term 'delayed block cleanout').

Whenever Oracle changes a database block (index, table, cluster) it stores a

pointer in the header of the data block which identifies the rollback segment
used to hold the rollback information for the changes made by the transaction.
(This is required if the user later elects to not commit the changes and wishes
to 'undo' the changes made.)

Upon commit, the database simply marks the relevant rollback segment header
entry as committed. Now, when one of the changed blocks is revisited Oracle
examines the header of the data block which indicates that it has been changed
at some point. The database needs to confirm whether the change has been
committed or whether it is currently uncommitted. To do this, Oracle determines
the rollback segment used for the previous transaction (from the block's header)
and then determines whether the rollback header indicates whether it has been
committed or not.

If it is found that the block is committed then the header of the data block is
updated so that subsequent accesses to the block do not incur this processing.

This behaviour is illustrated in a very simplified way below. Here we walk

through the stages involved in updating a data block.

STAGE 1 - No changes made

Description: This is the starting point. At the top of the

data block we have an area used to link active
transactions to a rollback
segment (the 'tx' part), and the rollback segment
header has a table that stores information upon
all the latest transactions
that have used that rollback segment.

In our example, we have two active transaction

slots (01 and 02)
and the next free slot is slot 03. (Since we are
free to overwrite committed transactions.)

Data Block 500 Rollback Segment Header 5

+----+--------------+ +----------------------+---------+
| tx | None | | transaction entry 01 |ACTIVE |
+----+--------------+ | transaction entry 02 |ACTIVE |
| row 1 | | transaction entry 03 |COMMITTED|
| row 2 | | transaction entry 04 |COMMITTED|
| ... .. | | ... ... .. | ... |
| row n | | transaction entry nn |COMMITTED|
+-------------------+ +--------------------------------+

STAGE 2 - Row 2 is updated

Description: We have now updated row 2 of block 500. Note that

 the data block header is updated to point to the
rollback segment 5, transaction
slot 3 (5.3) and that it is marked uncommitted
(Active).

Data Block 500 Rollback Segment Header 5

+----+--------------+ +----------------------+---------+
| tx |5.3uncommitted|-+ | transaction entry 01 |ACTIVE |
+----+--------------+ | | transaction entry 02 |ACTIVE |
| row 1 | +-->| transaction entry 03 |ACTIVE |
| row 2 *changed* | | transaction entry 04 |COMMITTED|
| ... .. | | ... ... .. | ... |
| row n | | transaction entry nn |COMMITTED|
+------------------+ +--------------------------------+

STAGE 3 - The user issues a commit

Description: Next the user hits commit. Note that all that
this does is it
updates the rollback segment header's
corresponding transaction
slot as committed. It does *nothing* to the data
block.

Data Block 500 Rollback Segment Header 5

+----+--------------+ +----------------------+---------+
| tx |5.3uncommitted|--+ | transaction entry 01 |ACTIVE |
+----+--------------+ | | transaction entry 02 |ACTIVE |
| row 1 | +--->| transaction entry 03 |COMMITTED|
| row 2 *changed* | | transaction entry 04 |COMMITTED|
| ... .. | | ... ... .. | ... |
| row n | | transaction entry nn |COMMITTED|
+------------------+ +--------------------------------+

STAGE 4 - Another user selects data block 500

Description: Some time later another user (or the same user)
revisits data block 500. We can see that there
is an uncommitted change in the
data block according to the data block's header.

Oracle then uses the data block header to look up

the corresponding rollback segment transaction
table slot, sees that it has been committed, and
changes data block 500 to reflect the
true state of the datablock. (i.e. it performs
 delayed cleanout).

Data Block 500 Rollback Segment Header 5

+----+--------------+ +----------------------+---------+
| tx | None | | transaction entry 01 |ACTIVE |
+----+--------------+ | transaction entry 02 |ACTIVE |
| row 1 | | transaction entry 03 |COMMITTED|
| row 2 | | transaction entry 04 |COMMITTED|
| ... .. | | ... ... .. | ... |
| row n | | transaction entry nn |COMMITTED|
+------------------+ +--------------------------------+

ORA-01555 Explanation
~~~~~~~~~~~~~~~~~~~~~

There are two fundamental causes of the error ORA-01555 that are a result of
Oracle trying to attain a 'read consistent' image. These are :

o The rollback information itself is overwritten so that Oracle is unable to

rollback the (committed) transaction entries to attain a sufficiently old enough
version of the block.

o The transaction slot in the rollback segment's transaction table (stored in

the rollback segment's header) is overwritten, and Oracle cannot rollback the
transaction header sufficiently to derive the original rollback segment
transaction slot.

Both of these situations are discussed below with the series of steps that cause
the ORA-01555. In the steps, reference is made to 'QENV'. 'QENV' is short for
'Query Environment', which can be thought of as the environment that existed
when a query is first started and to which Oracle is trying to attain a read
consistent image. Associated with this environment is the SCN
(System Change Number) at that time and hence, QENV 50 is the query
environment
with SCN 50.

CASE 1 - ROLLBACK OVERWRITTEN

This breaks down into two cases: another session overwriting the rollback that
the current session requires or the case where the current session overwrites
the rollback information that it requires. The latter is discussed in this
article because this is usually the harder one to understand.

Steps:
 1. Session 1 starts query at time T1 and QENV 50

2. Session 1 selects block B1 during this query

3. Session 1 updates the block at SCN 51

4. Session 1 does some other work that generates rollback information.

5. Session 1 commits the changes made in steps '3' and '4'.

(Now other transactions are free to overwrite this rollback information)

6. Session 1 revisits the same block B1 (perhaps for a different row).

Now, Oracle can see from the block's header that it has been changed and
it is later than the required QENV (which was 50). Therefore we need to get an
image of the block as of this QENV.

If an old enough version of the block can be found in the buffer cache
then we will use this, otherwise we need to rollback the current block to
generate another version of the block as at the required QENV.

It is under this condition that Oracle may not be able to get the
required rollback information because Session 1's changes have generated
rollback information that has overwritten it and returns the ORA-1555 error.

CASE 2 - ROLLBACK TRANSACTION SLOT OVERWRITTEN

1. Session 1 starts query at time T1 and QENV 50

2. Session 1 selects block B1 during this query

3. Session 1 updates the block at SCN 51

4. Session 1 commits the changes

(Now other transactions are free to overwrite this rollback information)

5. A session (Session 1, another session or a number of other sessions) then

use the same rollback segment for a series of committed transactions.

These transactions each consume a slot in the rollback segment

transaction table such that it eventually wraps around (the slots are written to
in a circular fashion) and overwrites all the slots. Note that Oracle is free to
reuse these slots since all transactions are committed.

6. Session 1's query then visits a block that has been changed since the
initial QENV was established. Oracle therefore needs to derive an image of the
block as at that point in time.

Next Oracle attempts to lookup the rollback segment header's transaction

slot pointed to by the top of the data block. It then realises that this has
been overwritten and attempts to rollback the changes made to the rollback
segment header to get the original transaction slot entry.

If it cannot rollback the rollback segment transaction table sufficiently

it will return ORA-1555 since Oracle can no longer derive the required version
of the data block.

It is also possible to encounter a variant of the transaction slot being

overwritten when using block cleanout. This is briefly described below :

Session 1 starts a query at QENV 50. After this another process updates the
blocks that Session 1 will require. When Session 1 encounters these blocks it
determines that the blocks have changed and have not yet been cleaned out (via
delayed block cleanout). Session 1 must determine whether the rows in the
block
existed at QENV 50, were subsequently changed,

In order to do this, Oracle must look at the relevant rollback segment

transaction table slot to determine the committed SCN. If this SCN is after the
QENV then Oracle must try to construct an older version of the block and if it
is before then the block just needs clean out to be good enough for the QENV.

If the transaction slot has been overwritten and the transaction table cannot
be rolled back to a sufficiently old enough version then Oracle cannot derive
the block image and will return ORA-1555.

(Note: Normally Oracle can use an algorithm for determining a block's SCN
during block cleanout even when the rollback segment slot has been overwritten.
But in this case Oracle cannot guarantee that the version of the block has not
changed since the start of the query).

Solutions
~~~~~~~~~

This section lists some of the solutions that can be used to avoid the ORA-01555
problems discussed in this article. It addresses the cases where rollback
segment information is overwritten by the same session and when the rollback
segment transaction table entry is overwritten.

It is worth highlighting that if a single session experiences the ORA-01555 and

it is not one of the special cases listed at the end of this article, then the
session must be using an Oracle extension whereby fetches across commits are
tolerated. This does not follow the ANSI model and in the rare cases where
ORA-01555 is returned one of the solutions below must be used.

CASE 1 - ROLLBACK OVERWRITTEN

1. Increase size of rollback segment which will reduce the likelihood of

overwriting rollback information that is needed.

2. Reduce the number of commits (same reason as 1).

3. Run the processing against a range of data rather than the whole table.
(Same reason as 1).

4. Add additional rollback segments. This will allow the updates etc. to be
spread across more rollback segments thereby reducing the chances of
overwriting
required rollback information.

5. If fetching across commits, the code can be changed so that this is not
done.

6. Ensure that the outer select does not revisit the same block at different
times during the processing. This can be achieved by :

- Using a full table scan rather than an index lookup

- Introducing a dummy sort so that we retrieve all the data, sort it and
then sequentially visit these data blocks.

CASE 2 - ROLLBACK TRANSACTION SLOT OVERWRITTEN

1. Use any of the methods outlined above except for '6'. This will allow
transactions to spread their work across multiple rollback segments therefore
reducing the likelihood or rollback segment transaction table slots being
consumed.

2. If it is suspected that the block cleanout variant is the cause, then force
block cleanout to occur prior to the transaction that returns the ORA-1555. This
can be achieved by issuing the following in SQL*Plus, SQL*DBA or Server
Manager
:

alter session set optimizer_goal = rule;

select count(*) from table_name;

If indexes are being accessed then the problem may be an index block and
clean out can be forced by ensuring that all the index is traversed. Eg, if the
index is on a numeric column with a minimum value of 25 then the following
query
will force cleanout of the index :

select index_column from table_name where index_column > 24;

Examples
~~~~~~~~

Listed below are some PL/SQL examples that can be used to illustrate the
ORA-1555 cases given above. Before these PL/SQL examples will return this
error
the database must be configured as follows :

o Use a small buffer cache (db_block_buffers).

REASON: You do not want the session executing the script to be able to find
old versions of the block in the buffer cache which can be used to satisfy a
block visit without requiring the rollback information.

o Use one rollback segment other than SYSTEM.

REASON: You need to ensure that the work being done is generating rollback
information that will overwrite the rollback information required.

o Ensure that the rollback segment is small.

REASON: See the reason for using one rollback segment.

ROLLBACK OVERWRITTEN

rem * 1555_a.sql -
rem * Example of getting ora-1555 "Snapshot too old" by
rem * session overwriting the rollback information required
rem * by the same session.

drop table bigemp;

create table bigemp (a number, b varchar2(30), done char(1));

drop table dummy1;

create table dummy1 (a varchar2(200));

rem * Populate the example tables.

begin
for i in 1..4000 loop
 insert into bigemp values (mod(i,20), to_char(i), 'N');
if mod(i,100) = 0 then
insert into dummy1 values ('ssssssssssss');
commit;
end if;
end loop;
commit;
end;
/

rem * Ensure that table is 'cleaned out'.

select count(*) from bigemp;

declare
-- Must use a predicate so that we revisit a changed block at a different
-- time.

-- If another tx is updating the table then we may not need the predicate
cursor c1 is select rowid, bigemp.* from bigemp where a < 20;

begin
for c1rec in c1 loop

update dummy1 set a = 'aaaaaaaa';

update dummy1 set a = 'bbbbbbbb';
update dummy1 set a = 'cccccccc';
update bigemp set done='Y' where c1rec.rowid = rowid;
commit;
end loop;
end;
/

ROLLBACK TRANSACTION SLOT OVERWRITTEN

rem * 1555_b.sql - Example of getting ora-1555 "Snapshot too old" by

rem * overwriting the transaction slot in the rollback
rem * segment header. This just uses one session.

drop table bigemp;

create table bigemp (a number, b varchar2(30), done char(1));

rem * Populate demo table.

begin
for i in 1..200 loop
insert into bigemp values (mod(i,20), to_char(i), 'N');
if mod(i,100) = 0 then
 commit;
end if;
end loop;
commit;
end;
/

drop table mydual;

create table mydual (a number);
insert into mydual values (1);
commit;

rem * Cleanout demo table.

select count(*) from bigemp;

declare

cursor c1 is select * from bigemp;

begin

-- The following update is required to illustrate the problem if block

-- cleanout has been done on 'bigemp'. If the cleanout (above) is commented
-- out then the update and commit statements can be commented and the
-- script will fail with ORA-1555 for the block cleanout variant.
update bigemp set b = 'aaaaa';
commit;

for c1rec in c1 loop

for i in 1..20 loop
update mydual set a=a;
commit;
end loop;
end loop;
end;
/

Special Cases
~~~~~~~~~~~~~

There are other special cases that may result in an ORA-01555. These are given
below but are rare and so not discussed in this article :

o Trusted Oracle can return this if configured in OS MAC mode. Decreasing

LOG_CHECKPOINT_INTERVAL on the secondary database may overcome the
problem.
 o If a query visits a data block that has been changed by using the Oracle
discrete transaction facility then it will return ORA-01555.

o It is feasible that a rollback segment created with the OPTIMAL clause

maycause a query to return ORA-01555 if it has shrunk during the life of the
query causing rollback segment information required to generate consistent read
versions of blocks to be lost.

Summary
~~~~~~~

This article has discussed the reasons behind the error ORA-01555 "Snapshot
too
old", has provided a list of possible methods to avoid the error when it is
encountered, and has provided simple PL/SQL scripts that illustrate the cases
discussed.