1. Explain briefly about Oracle database architecture
Ans. Oracle
database architecture is a combination of instance and database. Instance is a
combination of memory structures and background processes which helps in reading
and writing the data to/from the database.
2. Which background process is used during user
connectivity?
Ans. PMON
3. What are base tables? When and how they will get
created?
Ans. Base tables
are dictionary information of the database. They will be created at the time of
database creation using SQL.BSQ script.
4. What are different views DBA uses? Which script will
create them?
Ans. We use data
dictionary views to look into permanent information about the database and
dynamic performance views to get ongoing actions in the database. Both the
views will be created after database creation using catalog.sql script.
5. Why to execute catproc.sql script?
Ans. It will
create necessary packages and procedures which DBA use for certain actions.
6. Explain the phases of SQL execution
Ans. SQL execution
contains 2 phases.
i. Parsing – in which syntax checking, semantic checking and dividing
the statement into literals will be done
ii. Execution – in which parsed statement will get converted into
ASCII format and will be executed
iii. Fetch – in which data will be fetched either from database buffer
cache or database.
7. What is mean by semantic checking? Which component
helps in that?
Ans. Semantic
checking means checking for the privileges for the user or in other words
authorizing the user. Base tables or dictionary will help in doing this.
8. What is server process?
Ans. It is a
process created to help the user process either in reading/writing the data in
the database.
9. What is the difference between physical and logical
read?
Ans. If we fetch
data from database buffer cache, then it’s called logical read. If we fetch it
from database, it’s called physical read as it includes an I/O operation.
10. Why to maintain a copy of data in database buffer
cache?
Ans. When the same
query is ran by same or different user, data can be picked from buffer cache
thus avoiding I/O and improving performance.
11. Why server process will not start searching from MRU
end?
Ans. If server
process starts searching at MRU end, there is a chance that data may get flush
from buffer cache by the ime it reaches LRU end. In that case again to fetch
data, we need to do an I/O which is costly. So oracle designed its architecture
that server process will search only from LRU end.
12. What are the logical structures of the database?
Ans. Tablespace,
segment, extent and oracle data block are logical structures.
13. What are the 4 mandatory tablespaces req to run the
database?
Ans. SYSTEM,
SYSAUX, TEMP and UNDO.
14. Can I have a database without SYSAUX tablespace in
10g?
Ans. Yes, but load
will be more on system tablespace.
15. If we have physical structures, why do we need
logical structures?
Ans. Logical
structures are defined to provide the easiness in maintenance
16. Explain the difference between a block, extent and
segment?
Ans. Block is a
basic storage unit whereas extent is a small memory area allocated to a
table and segment is an object which
occupies space.
17. What are redo entries? When and where they will be
created?
Ans. A single
atomic chance happened to the database is called redo entry. They will be
created when we run any DML or DDL commands or when any changes are done in the
database. They will be created in PGA.
18. What is different status for a block in database
buffer cache?
Ans. UNUSED, FREE,
PINNED and DIRTY.
19. What is write-ahead protocol?
Ans. LGWR writing
before DBWR is called write-ahead protocol.
20. Will there be any undo generated in case of DDL
statement processing? If so why?
Ans. Yes. Reason
is even tough it is a DDL externally, it will be DML to base tables and to
rollback them always oracle requires undo.
21. What is PGA?
Ans. PGA is a
memory area which is used to store user’s exclusive information like session
information and helps in executing a query.
22. What is the difference between memory allocation in
8i and 9i for PGA?
Ans. In 8i, we
need to set different individual parameters where as in 9i setting only one
parameter PGA_AGGREGATE_TARGET would be enough
23. Which is correct? Sorting will take place in PGA or
temp tablespace?
Ans. Both. If the
data which to be sorted is more than sort area size of PGA, then temp
tablespace will be used.
24. How you will define INSTANCE and DATABASE?
Ans. INSTANCE is a
combination of memory structures and background processes which helps in
reading/writing the data. DATABASE is mix of physical and logical structures
which helps in storing user data.
24. What is SGA?
Ans. SGA is a combination of different memory structures which helps
in several actions in the database.
Comments
Post a Comment