How would you go about increasing the buffer cache hit
ratio?
0. Explain the difference between a hot backup and a cold
backup and the benefits associated with each


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


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


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


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


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


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


7. Give the reasoning behind using an index.


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


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


10. Give two examples of referential integrity constraints.


11. 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?


12. Explain the difference between ARCHIVELOG mode and
NOARCHIVELOG mode and the benefits and disadvantages to
each.


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


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


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


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

Answers were Sorted based on User's Feedback



How would you go about increasing the buffer cache hit ratio? 0. Explain the difference between a..

Answer / umesh h

1.If Buffer cache hit ratio is less then 85%.
2.Hot backup is online backup(means we can take backup when
database is up and running.ie Alter database begin backup
mode;
Cold backup is offline backup(means taking backup when
database is shutdown gracefully.ie at O/S level using copy
command.)
3.Create controlfile dynamicaly.
4.create spfile from pfile;
startup force;
5.oracle stores data interms of datablocks
group of datablocks is known as extent.
group of extents make a segment.
6.Truncate=delete+commit(as it is a DDL command)
Delete= as it is a DML command we can rollback the
transaction.
7.reason behind using index is to increase query
performance.(most suitable for DWH).
Cont.... in next answer

Is This Answer Correct ?    3 Yes 0 No

How would you go about increasing the buffer cache hit ratio? 0. Explain the difference between a..

Answer / sathish

1)The hot backup basically backups database is it still up
and running and it must be in archive log mode.
Benefit-database available for use while the backup is
occurring and recover the database any point in time.
The cold backup basically backups database it is shutdown
and not required to being in archive log mode.
Benefit- easier to backup and recover abd slight performance
gain the database is not cutting archive logs to disk.

2)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)using spfile from pfile

4)Oracle save the data as datablocks..group of datablocks
know as extent and these extent are segmented
5)Alert log

7)Faster access to data blocks in a table.

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

9)Bitmap Index

10)Primary key and foreign key

11)disable foreign key in parent table.do changes in parent
table then enable foreign key

12)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.

13)Alter database backup control file to trace

14)STARTUP NOMOUNT - Instance startup

STARTUP MOUNT - The database is mounted

STARTUP OPEN - The database is opened

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

16)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

Is This Answer Correct ?    2 Yes 0 No

Post New Answer

More SQL PLSQL Interview Questions

What's the procedure?

0 Answers  


what are the forced views

6 Answers   Hexaware, TCS,


Why does sql need a server?

0 Answers  


how to implement one-to-one, one-to-many and many-to-many relationships while designing tables? : Sql dba

0 Answers  


What is string data type in sql?

0 Answers  






Is subquery faster than join?

0 Answers  


What is output spooling in sql*plus?

0 Answers  


What is the difference between the conventional and direct path loads? : aql loader

0 Answers  


can we create index on index?

7 Answers   iFlex, Symphony,


How run sql*plus commands that are stored in a local file?

0 Answers  


oracle is compiler or interpretter,can any one tell me the answer?

9 Answers   Satyam,


How do you modify a trigger?

0 Answers  


Categories