Oracle Interview Questions

. How many memory layers are in the shared pool?
2. How do you find out from the RMAN catalog if a particular archive log has been backed-up?
3. How can you tell how much space is left on a given file system and how much space each of the file system's subdirectories take-up?
4. Define the SGA and:• How you would configure SGA for a mid-sized OLTP environment?• What is involved in tuning the SGA?
5. What is the cache hit ratio, what impact does it have on performance of an Oracle database and what is involved in tuning it?
6. Other than making use of the statspack utility, what would you check when you are monitoring or running a health check on an Oracle 8i or 9i database?
7. How do you tell what your machine name is and what is its IP address?
8. How would you go about verifying the network name that the local_listener is currently using?
9. You have 4 instances running on the same UNIX box. How can you determine which shared memory and semaphores are associated with which instance?
10. What view(s) do you use to associate a user's SQLPLUS session with his o/s process?
11. What is the recommended interval at which to run statspack snapshots, and why?
12. What spfile/init.ora file parameter exists to force the CBO to make the execution path of a given statement use an index, even if the index scan may appear to be calculated as more costly?
13. Assuming today is Monday, how would you use the DBMS_JOB package to schedule the execution of a given procedure owned by SCOTT to start Wednesday at 9AM and to run subsequently every other day at2AM.
14. How would you edit your CRONTAB to schedule the running of /test/test.sh to run every other day at 2PM?
15. What do the 9i dbms_standard.sql_txt() anddbms_standard.sql_text() procedures do?
16. In which dictionary table or view would you look to determine at which time a snapshot or MVIEW last successfully refreshed?
17. How would you best determine why your MVIEW couldn't FAST REFRESH?
18. How does propagation differ between Advanced Replication and Snapshot Replication (read-only)?
19. Which dictionary view(s) would you first look at tounderstand or get a high-level idea of a given Advanced Replication environment?
20. How would you begin to troubleshoot an ORA-3113 error?
21. Which dictionary tables and/or views would you look at to diagnose a locking issue?
22. An automatic job running via DBMS_JOB has failed. Knowing only that "it's failed", how do you approach troubleshooting this issue?
23. How would you extract DDL of a table without using a GUI tool?
24. You're getting high "busy buffer waits" - how can you find what's causing it?
25. What query tells you how much space a tablespace named "test" is taking up, and how much space is remaining?
26. Database is hung. Old and new user connections alike hang on impact. What do you do? Your SYS SQLPLUS session IS able to connect.
29. How do you increase the OS limitation for open files (LINUX and/or Solaris)?
30. Provide an example of a shell script which logs into SQLPLUS as SYS, determines the current date, changes the date format to include minutes & seconds, issues a drop table command, displays the date again, and finally exits.
31. Explain how you would restore a database using RMAN to Point in Time?
32. How does Oracle guarantee data integrity of data changes?
33. Which environment variables are absolutely critical in order to run the OUI?
34. What SQL query from v$session can you run to show how many sessions are logged in as a particular user account?
35. Why does Oracle not permit the use of PCTUSED with indexes?
36. What would you use to improve performance on an insert statement that places millions of rows into that table?
37. What would you do with an "in-doubt" distributed transaction?
38. What are the commands you'd issue to show the explain plan for "select * from dual"?
39. In what script is "snap$" created? In what script isthe "scott/tiger" schema created?
40. If you're unsure in which script a sys or system-owned object is created, but you know it's in a script from a specific directory, what UNIX command from that directory structure can you run to find your answer?
41. How would you configure your networking files to connect to a database by the name of DSS which resides in domain icallinc.com?
42. You create a private database link <> and uponconnection, fails with: ORA-2085: <> connects to <>. What is the problem? How would you go about resolving this error?
43. I have my backup RMAN script called "backup_rman.sh". I am on the target database. My catalog username/password is rman/rman. My catalog db is called rman. How would you run this shell script from the O/S such that it would run as a background process?
44. Explain the concept of the DUAL table.
45. What are the ways tablespaces can be managed and how do they differ?
46. From the database level, how can you tell under which time zone a database is operating?
47. What's the benefit of "dbms_stats" over "analyze"?
48. Typically, where is the conventional directory structure chosen for Oracle binaries to reside?
49. You have found corruption in a tablespace that contains static tables that are part of a database that is in NOARCHIVE log mode. How would you restore the tablespace without losing new data in the other tablespaces?
50. How do you recover a datafile that has not been physically been backed up since its creation and has been deleted. Provide syntax example.
1. What are the different types of joins?
2. Explain normalization with examples.
3. What cursor type do you use to retrieve multiple record sets?
4. Difference between a "where" clause and a "having" clause
5. What is the difference between "procedure" and "function"?
6. How will you copy the structure of a table without copying the data?
7. How to find out the database name from SQL*PLUScommand prompt?
8. Takeoffs with having indexes
9. Talk about "Exception Handling" in PL/SQL?
10. What is the difference between "NULL in C" and "NULL in Oracle?"
11. What is Pro*C? What is OCI?
12. Give some examples of Analytical functions.
13. What is the difference between "translate" and "replace"?
14. What is DYNAMIC SQL method 4?
15. How to remove duplicate records from a table?
16. What is the use of analyzing the tables?
17. How to run SQL script from a Unix Shell?
18. What is a "transaction"? Why are they necessary?
19. Explain Normalization and Denormalization with examples.
20. When do you get constraint violation? What are the types of constraints?
21. How to convert RAW data type into TEXT?
22. Difference - Primary Key and Aggregate Key
23. How functional dependency is related to database table design?
24. What is a "trigger"?
25. Why can a "group by" or "order by" clause be expensive to process?
26. What are "HINTS"? What is "index covering" of a query?
27. What is a VIEW? How to get script for a view?
28. What are the Large object types supported by Oracle?
29. What is SQL*Loader?
30. Difference between "VARCHAR" and "VARCHAR2" data types.
31. What is the difference among "dropping a table", "truncating a table" and "deleting all records" from a table?
32. Difference between "ORACLE" and "MICROSOFT ACCESS" databases.
33. How to create a database link?
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.
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 -efgrep oraclewc -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.

ORACLE Interview Questions

1.What is an oracle instance?
2.What is a view?
3.What is referential integrity?
4.Name the data dictionary that stores user-defined
constraints?
5.What is a collection of privileges?
6.What is a snapshot?
7.What is a synonym?
8.What is a cursor?
9.What is a sequence?
10.What is a trigger?
11.What is an exception?
12.What is a partition of table?
13.What are pseudo-columns in SQL? Give examples?
14.What are the Data Control statements?
15.What is a schema?
16.What is a type?
17.What is a data model?
18.What is a relation?
19.Advantages of redo log files?
20.What is an Archiver?
21.What is a database buffer cache?
22.What are the 5 background processes in Oracle?
23.%type and %rowtype are attributes for?
24.What are the 2 steps in a two-phase commit?
25.What is a union, intersect, minus?
26.What is a join, explain the types of joins?
27.What is a co-related sub-query?
28.ODBC stands for?
29.Data-type used to work with integers is?
30.Describe data models?
31.Describe the Normalization principles?
32.What are the types of Normalization?
33.What is de-normalization?
SQL SERVER:-
34.What is normalization?
Ans: Well a relational database is basically composed
of tables that contain related data. So the Process of
organizing this data into tables is actually referred
to as normalization.
35.What is a Stored Procedure?
Ans: Its nothing but a set of T-SQL statements
combined to perform a single task of several tasks.
Its basically like a Macro so when you invoke the
Stored procedure, you actually run a set of
statements.
36.Can you give an example of Stored Procedure?
Ans: sp_helpdb , sp_who2, sp_renamedb are a set of
system defined stored procedures. We can also have
user defined stored procedures which can be called in
similar wayWhat is a trigger?
Ans: Triggers are basically used to implement business
rules. Triggers is also similar to stored procedures.
The difference is that it can be activated when data
is added or edited or deleted from a table in a
database.
37.What is a view?
Ans: If we have several tables in a db and we want to
view only specific columns from specific tables we can
go for views. It would also suffice the needs of
security some times allowing specfic users to see only
specific columns based on the permission that we can
configure on the view. Views also reduce the effort
that is required for writing queries to access
specific columns every time.
38.What is an Index?
Ans: When queries are run against a db, an index on
that db basically helps in the way the data is sorted
to process the query for faster and data retrievals
are much faster when we have an index.
39.What are the types of indexes available with SQL
Server?
Ans: There are basically two types of indexes that we
use with the SQL Server. Clustered and the
Non-Clustered.
40.What is the basic difference between clustered and
a non-clustered index?
Ans: The difference is that, Clustered index is unique
for any given table and we can have only one clustered
index on a table. The leaf level of a clustered index
is the actual data and the data is resorted in case of
clustered index. Whereas in case of non-clustered
index the leaf level is actually a pointer to the data
in rows so we can have as many non-clustered indexes
as we can on the db.
41.What are cursors?
Ans: Well cursors help us to do an operation on a set
of data that we retreive by commands such as Select
columns from table. For example : If we have duplicate
records in a table we can remove it by declaring a
cursor which would check the records during retreival
one by one and remove rows which have duplicate
values.
42.When do we use the UPDATE_STATISTICS command?
Ans: This command is basically used when we do a large
processing of data. If we do a large amount of
deletions any modification or Bulk Copy into the
tables, we need to basically update the indexes to
take these changes into account. UPDATE_STATISTICS
updates the indexes on these tables accordingly.
43.SQL Server runs on which TCP/IP port?
Ans: SQL Server runs on port 1433 but we can also
change it for better security.
44.From where can you change the default port?
Ans: From the Network Utility TCP/IP properties -->
Port number.both on client and the server.
45.Can you tell me the difference between DELETE &
TRUNCATE commands?
Ans: Delete command removes the rows from a table
based on the condition that we provide with a WHERE
clause. Truncate will actually remove all the rows
from a table and there will be no data in the table
after we run the truncate command.
46.Can we use Truncate command on a table which is
referenced by FOREIGN KEY?
Ans: No. We cannot use Truncate command on a table
with Foreign Key because of referential integrity.
47.What is the use of DBCC commands?
Ans: DBCC stands for database consistency checker. We
use these commands to check the consistency of the
databases, i.e., maintenance, validation task and
status checks.
48.Can you give me some DBCC command options?(Database
consistency check)
Ans: DBCC CHECKDB - Ensures that tables in the db and
the indexes are correctly linked.and DBCC CHECKALLOC -
To check that all pages in a db are correctly
allocated. DBCC SQLPERF - It gives report on current
usage of transaction log in percentage. DBCC
CHECKFILEGROUP - Checks all tables file group for any
damage.
49.What command do we use to rename a db?
Ans: sp_renamedb 'oldname' , 'newname'
50.Well sometimes sp_reanmedb may not work you know
because if some one is using the db it will not accept
this command so what do you think you can do in such
cases?
Ans: In such cases we can first bring to db to single
user using sp_dboptions and then we can rename that db
and then we can rerun the sp_dboptions command to
remove the single user mode.
51.What is the difference between a HAVING CLAUSE and
a WHERE CLAUSE?
Ans: Having Clause is basically used only with the
GROUP BY function in a query. WHERE Clause is applied
to each row before they are part of the GROUP BY
function in a query.
52.What do you mean by COLLATION?
Ans: Collation is basically the sort order. There are
three types of sort order Dictionary case sensitive,
Dictonary - case insensitive and Binary.
53.What is a Join in SQL Server?
Ans: Join actually puts data from two or more tables
into a single result set.
54.Can you explain the types of Joins that we can have
with Sql Server?
Ans: There are three types of joins: Inner Join, Outer
Join, Cross Join
55.When do you use SQL Profiler?
Ans: SQL Profiler utility allows us to basically track
connections to the SQL Server and also determine
activities such as which SQL Scripts are running,
failed jobs etc..
56.What is a Linked Server?
Ans: Linked Servers is a concept in SQL Server by
which we can add other SQL Server to a Group and query
both the SQL Server dbs using T-SQL Statements.
57.Can you link only other SQL Servers or any database
servers such as Oracle etc..
Ans: We can link any server provided we have the
OLE-DB provider from Microsoft to allow a link. For
Oracle we have a OLE-DB provider for oracle that
microsoft provides to add it as a linked server to the
sql server group.
58.Which stored procedure will you be running to add a
linked server?
Ans: sp_addlinkedserver, sp_addlinkedsrvlogin
59.What are the OS services that the SQL Server
installation adds?
Ans: MS SQL SERVER SERVICE, SQL AGENT SERVICE, DTC
(Distribution transac co-ordinator)
60.Can you explain the role of each service?
Ans: SQL SERVER - is for running the databases
SQL AGENT - is for automation such as Jobs, DB
Maintanance, Backups
DTC - Is for linking and connecting to other SQL
Servers
61.How do you troubleshoot SQL Server if its running
very slow?
Ans: First check the processor and memory usage to see
that processor is not above 80% utilization and memory
not above 40-45% utilization then check the disk
utilization using Performance Monitor, Secondly, use
SQL Profiler to check for the users and current SQL
activities and jobs running which might be a problem.
Third would be to run UPDATE_STATISTICS command to
update the indexes
62.Lets say due to N/W or Security issues client is
not able to connect to server or vice versa. How do
you troubleshoot?
Ans: First I will look to ensure that port settings
are proper on server and client Network utility for
connections. ODBC is properly configured at client end
for connection ------Makepipe & readpipe are utilities
to check for connection. Makepipe is run on Server and
readpipe on client to check for any connection issues.
63.What are the authentication modes in SQL Server?
Ans: Windows mode and mixed mode (SQL & Windows).
64.Where do you think the users names and passwords
will be stored in sql server?
Ans: They get stored in master db in the sysxlogins
table.
65.What is log shipping? Can we do logshipping with
SQL Server 7.0
Ans: Logshipping is a new feature of SQL Server 2000.
We should have two SQL Server - Enterprise Editions.
From Enterprise Manager we can configure the
logshipping. In logshipping the transactional log file
from one server is automatically updated into the
backup database on the other server. If one server
fails, the other server will have the same db and we
can use this as the DR (disaster recovery) plan.
66.Let us say the SQL Server crashed and you are
rebuilding the databases including the master database
what procedure to you follow?
Ans: For restoring the master db we have to stop the
SQL Server first and then from command line we can
type SQLSERVER –m which will basically bring it into
the maintenance mode after which we can restore the
master db.
67.Let us say master db itself has no backup. Now you
have to rebuild the db so what kind of action do you
take?
Ans: (I am not sure- but I think we have a command to
do it).
68.What is BCP? When do we use it?
Ans:BulkCopy is a tool used to copy huge amount of
data from tables and views. But it won't copy the
structures of the same.
69.What should we do to copy the tables, schema and
views from one SQL Server to another?
Ans:We have to write some DTS packages for it.

70.What are the different types of joins and what dies
each do?
71.What are the four main query statements?
72.What is a sub-query? When would you use one?
73.What is a NOLOCK?
74.What are three SQL keywords used to change or set
someone’s permissions?
75.What is the difference between HAVING clause and
the WHERE clause?
76.What is referential integrity? What are the
advantages of it?
77.What is database normalization?
78.Which command using Query Analyzer will give you
the version of SQL server and operating system?
79.Using query analyzer, name 3 ways you can get an
accurate count of the number of records in a table?
80.What is the purpose of using COLLATE in a query?
81.What is a trigger?
82.What is one of the first things you would do to
increase performance of a query? Example “ a query ran
yesterday took 30 seconds, today it takes 6 minutes”
83.What is an execution plan? When would you use it?
How would you view the execution plan?
84.What is the STUFF function and how does it differ
from the REPLACE function?
85.What does it mean to have quoted_identifier on?
What are the implications of having it off?
86.What are the different types of replication? How
are they used?
87.What is the difference between a local and a global
variable?
88.What is the difference between a Local temporary
table and a Global temporary table? How is each one
used?
89.What are cursors? Name four types of cursors and
when each one would be applied?
90.What is the purpose of UPDATE STATISTICS?
91.How do you use DBCC statements to monitor various
aspects of a SQL server installation?
92.How do you load large data to the SQL server
database?
93.How do you check the performance of a query and how
do you optimize it?
94.How do SQL server 2000 and XML linked? Can XML be
used to access data?
95.What is SQL server agent?
96.What is referential integrity and how is it
achieved?
97.What is indexing?
98.What is normalization and what are the different
forms of normalizations?
99.Difference between server.transfer and
server.execute method?
100.What id de-normalization and when do you do it?
101.What is better- 2nd Normal form or 3rd normal
form? Why?
102.Can we rewrite subqueries into simple select
statements or with joins? Example?
103.What is a function? Give some example?
104.What is a stored procedure?
105.Difference between Function and Procedure-in
general?
106.Difference between Function and Stored Procedure?
107.Can a stored procedure call another stored
procedure. If yes what level and can it be controlled?
108.Can a stored procedure call itself(recursive). If
yes what level and can it be controlled.?
109.How do you find the number of rows in a table?
110.Difference between Cluster and Non-cluster index?
111.What is a table called, if it does not have
neither Cluster nor Non-cluster Index?
112.Explain DBMS, RDBMS?
113.Explain basic SQL queries with SELECT from where
Order By, Group By-Having?
114.Explain the basic concepts of SQL server
architecture?
115.Explain couple pf features of SQL server
2000(Scalability, Availability, Integration with
internet, etc.)?
116.Explain fundamentals of Data ware housing & OLAP?
117.Explain the new features of SQL server 2000?
118.How do we upgrade from SQL Server 6.5 to 7.0 and
7.0 to 2000?
119.What is data integrity? Explain constraints?
120.Explain some DBCC commands?
121.Explain sp_configure commands, set commands?
122.Explain what are db_options used for?
123.What is the basic functions for master, msdb,
tempdb databases?
124.What is a job?
125.What are tasks?
126.What are primary keys and foreign keys?
127.How would you Update the rows which are divisible
by 10, given a set of numbers in column?
128.If a stored procedure is taking a table data type,
how it looks?
129.How m-m relationships are implemented?
130.How do you know which index a table is using?
131.How will oyu test the stored procedure taking two
parameters namely first name and last name returning
full name?
132.How do you find the error, how can you know the
number of rows effected by last SQL statement?
133.How can you get @@error and @@rowcount at the same
time?
134.What are sub-queries? Give example? In which case
sub-queries are not feasible?
135.What are the type of joins? When do we use Outer
and Self joins?
136.Which virtual table does a trigger use?
137.How do you measure the performance of a stored
procedure?
138.Questions regarding Raiseerror?
139.Questions on identity?
140.If there is failure during updation of certain
rows, what will be the state?