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

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?

Question Posted / sruti

2 Answers
5041 Views
I also Faced
E-Mail Answers

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

create or replace procedure search_matter(empno varchar2) as sql_stmt varchar2(200); stmt varchar2(200); v_table_name varchar2(200); val_pres number; inp_value varchar2(200); type obj_typ is table of cols.column_name%type index by binary_integer; type all_col is table of varchar2(100) index by binary_integer; typ_obj_typ obj_typ; typ_all_col all_col; begin select object_name bulk collect into typ_obj_typ from user_tables,user_objects where table_name = object_name AND object_type = 'TABLE'; select empno into inp_value from dual; dbms_output.put_line('inp value : '||inp_value); for i in typ_obj_typ.first..typ_obj_typ.last loop v_table_name := NULL; v_table_name := typ_obj_typ(i); dbms_output.put_line(i||':'||typ_obj_typ(i)); dbms_output.put_line('....................'); sql_stmt := 'select column_name from cols where table_name = :1 and data_type in (''CHAR'', ''VARCHAR2'', ''NCHAR'', ''NVARCHAR2'',''NUMBER'')'; EXECUTE IMMEDIATE sql_stmt bulk collect into typ_all_col using typ_obj_typ(i); for inside in typ_all_col.first..typ_all_col.last loop dbms_output.put_line('sql stmt: '||sql_stmt); dbms_output.put_line('column name: '||typ_all_col(inside)||'table name: '||typ_obj_typ(i)); stmt := 'select count(*) from ||typ_obj_typ(i)||'; EXECUTE_IMMEDIATE stmt into val_pres ; if val_pres = 1 then dbms_output.put_line('value present col name: '||typ_all_col(inside)||'table name :'||typ_obj_typ(i)); end if; end loop; dbms_output.put_line('....................'); end loop; exception when others then dbms_output.put_line('sql code '||sqlcode||'Table name: '||v_table_name); dbms_output.put_line('sql message '||sqlerrm); end; Compile-time I am getting below error, Plz help to resolve. LINE/COL ERROR -------- ----------------------------------------------------------------- 47/23 PLS-00103: Encountered the symbol "STMT" when expecting one of the following: := . ( @ % ;

0 Answers

What is a function in oracle pl sql?

0 Answers

IF EMP HAS 2 ROWS,DEPT HAS 4 ROWS.WHATS THE RESULT OF SELECT * FROM EMP,DEPT;

1 Answers

What does subquery mean in sql?

0 Answers

Does pdo prevent sql injection?

0 Answers

What is sql key?

0 Answers

what is a field in a database ? : Sql dba