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 elements 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 MONitor)
PMON (Process MONitor)
LGWR R (LoG Write)
DBWR (Data Base Writer)
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.
For More -- Please click HERE
