5 avg. rating (100% score) - 1 votes
Oracle Memory Management is managing the memory of the database. Various methods are available to perform memory management. This will tune the database to optimize the memory. It is Very effective and efficient in terms of managing memory. We can set the memory management to automatic which will handle the Shared global area and program global area automatically. By caching the data, the efficiency of the database increases. Good number of positions is available for the candidates. Good knowledge on Oracle Memory Management will boost your confidence. Follow our Wisdomjobs page for Oracle Memory Management interview questions and answers page to get through your job interview successfully in first attempt. Interview questions are exclusively designed for job seekers to assist them in clearing interviews.
Question 1. What Is The Significance Of Having Storage Clause?
Answer :
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 updations etc.
Question 2. What Is The Functionality Of System Table Space?
Answer :
To manage the database level transactions such as modifications of the data dictionary table that record information about the free space usage.
Question 3. How Does Space Allocation Table Place Within A Block?
Answer :
Each block contains entries as follows:
Question 4. What Is The Role Of Pctfree Parameter Is Storage Clause?
Answer :
This is used to reserve certain amount of space in a block for expansion of rows.
Question 5. What Is The Optimal Parameter?
Answer :
To avoid the space wastage we use OPTIMAL parameter.
Question 6. What Is A Shared Pool?
Answer :
Question 7. What Is Mean By Program Global Area (pga)?
Answer :
It is area in memory that is used by a Single Oracle User Process.
Question 8. What Is A Data Segment?
Answer :
Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.
Question 9. What Are The Factors Causing The Re Parsing Of Sql Statements In Sga?
Answer :
Question 10. What Is Database Buffers?
Answer :
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.
Question 11. What Is Dictionary Cache?
Answer :
Dictionary cache is information about the database objects stored in a data dictionary table.
Question 12. Which Parameter In Storage Clause Will Reduce Number Of Rows Per Block?
Answer :
Question 13. What Is Meant By Free Extent?
Answer :
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.
Question 14. How Will You Force Database To Use Particular Rollback Segment?
Answer :
For particular transaction Alter system set rollback segment 'name';
For database, we can set in pfile. Rollback_segment='name' .
Question 15. How Can We Organize The Tablespaces In Oracle Database To Have Maximum Performance?
Answer :
Store data in tablespaces to avoid disk contension.system tablespace-recursive callsuserdata-users objectsindex tablespace-for indexesrollback segmnets-undo tablespace or manual rollback segmentsplace application specific data in respective tablespaces.Place all these tablespaces in saperate disks.Try to implement raid-0 (striping) for better performance.
Question 16. How Will You Swap Objects Into A Different Table Space For An Existing Database?
Answer :
Question 17. What Is Redo Log Buffer?
Answer :
Changes made to entries 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.
Question 18. What Is Meant By Recursive Hints?
Answer :
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.
Question 19. How Can We Plan Storage For Very Large Tables?
Answer :
Question 20. How Will You Estimate The Space Required By A Non-clustered Tables?
Answer :
After arriving the calculation, add 10 % additional space to calculate the initial extent size for a working table.
Answer :
Yes. It is possible to use raw devices as data files.
The advantages over file system files:
Question 22. What Is A Control File?
Answer :
The Control File is a small binary file necessary for the database to start and operate successfully. Each Control file is associated with only one Oracle database. Before a database is opened, the control file is read to determine if the database is in a valid state to USE.
The Control file is not accessible, the database does not function properly.
Question 23. How Will You Monitor Rollback Segment Status?
Answer :
By using dictionary views called v$rollstat,dba_rollback_segs.
Question 24. How Will You Monitor The Space Allocation?
Answer :
This can be monitored in DB_data_files.
Question 25. Why Query Fails Sometimes?
Answer :
Due to syntax errors.
Question 26. How The Space Utilization Takes Place Within Rollback Segments?
Answer :
By correctly fitting optimal size.
Question 27. How Will You Create Multiple Rollback Segments In A Database?
Answer :
create rollback segment roll1tablespace roll1.
Question 28. What Is A Rollback Segment Entry?
Answer :
When ever changes happened to the database previous change will be there in the rollback segment.
Question 29. What Is Hit Ratio?
Answer :
Hit Ratio is the ratio of shared SQL and PL/SQL items found in the Library Cache versus physical storage.It can also be defined in a mathematical expression as 1 - ((physical reads) / (db block gets + consistent reads)).
Question 30. What Are Disadvantages Of Having Raw Devices?
Answer :
We should depend on export/import utility for backup/recovery (fully reliable) The tar command cannot be used for physical file backup, instead we can use dd command which is less flexible and has limited recoveries.
Question 31. What Is Use Of Rollback Segments In Oracle Database?
Answer :
When a user updated a particular table (for example 100 rows) the old value will be retained in the roll back segments(Oracle 8) and now it is Undo segment (oracle 9i). If the user issue a rollback command the old value will be taken from the rollback segment(that too if undo_retention parameter set properly in the parameter file).
Question 32. What Is Advantage Of Having Disk Shadowing / Mirroring?
Answer :
Shadow set of disks save as a backup in the event of disk failure. In most Operating System if any disk failure occurs it automatically switchover to place of failed disk.
Improved performance because most OS support volume shadowing can direct file I/O request to use the shadow set of files instead of the main set of files. This reduces I/O load on the main set of disks.
Question 33. How Redo Logs Can Be Achieved?
Answer :
LGWR process writes all change vectors from the redo log buffer to online redo log file sequentially.
Question 34. What Is Redo Log File Mirroring?
Answer :
Multiplexing Redo log file called Mirroring. ( Keeping multiple copies in different disks)
Question 35. How To Implement The Multiple Control Files For An Existing Database?
Answer :
Question 36. What Is Sga? How It Is Different From Ver 6.0 And Ver 7.0?
Answer :
The System Global Area in a Oracle database is the area in memory to facilitates 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 SQL pool (ver 7.0 only) area.
Question 37. What Is A Shared Sql Pool?
Answer :
The data dictionary cache is stored in an area in SGA called the Shared SQL Pool. This will allow sharing of parsed SQL statements among concurrent users.
Question 38. List The Factors That Can Affect The Accuracy Of The Estimations?
Answer :
The space used transaction entries and a deleted record does not become free immediately after completion due to delayed cleanout.
Trailing nulls and length bytes are not stored. Inserts of, updates to and deletes of rows as well as columns larger than a single datablock, can cause fragmentation and chained row pieces.
Question 39. What Are The Different Kind Of Export Backups?
Answer :
Question 40. What Is Cold Backup? What Are The Elements Of It?
Answer :
Cold backup is taking backup of all physical files after normal shutdown of database. We need to take.
Question 41. What Is A Logical Backup?
Answer :
Logical backup involves reading a set of database records and writing them into a file. Export utility is used for taking backup and Import utility is used to recover from backup.
Question 42. What Is Hot Backup And How It Can Be Taken?
Answer :
Taking backup of archive log files when database is open. For this the ARCHIVELOG mode should be enabled. The following files need to be backed up. All data files. All Archive log, redo log files. All control files.
Question 43. What Is The Use Of File Option In Exp Command?
Answer :
To give the export file name.
Question 44. What Is The Use Of Grant Option In Exp Command?
Answer :
A flag to indicate whether grants on databse objects will be exported or not. Value is 'Y' or 'N'.
Question 45. What Is The Use Of Indexes Option In Exp Command?
Answer :
A flag to indicate whether indexes on tables will be exported.
Question 46. What Is The Use Of Rows Option In Exp Command?
Answer :
Flag to indicate whether table rows should be exported. If 'N' only DDL statements for the database objects will be created.
Question 47. What Is The Use Of Parfile Option In Exp Command?
Answer :
Name of the parameter file to be passed for export.
Question 48. What Is The Use Of Analyse ( Ver 7) Option In Exp Command?
Answer :
A flag to indicate whether statistical information about the exported objects should be written to export dump file.
Question 49. What Is The Use Of Full Option In Exp Command?
Answer :
A flag to indicate whether full databse export should be performed.
Question 50. What Is The Use Of Owner Option In Exp Command?
Answer :
List of table accounts should be exported.
Question 51. What Is The Use Of Tables Option In Exp Command?
Answer :
List of tables should be exported.
Question 52. What Is The Use Of Record Length Option In Exp Command?
Answer :
Record length in bytes.
Question 53. What Is The Use Of Inctype Option In Exp Command?
Answer :
Type export should be performed COMPLETE,CUMULATIVE,INCREMENTAL
Question 54. What Is The Use Of Record Option In Exp Command?
Answer :
For Incremental exports, the flag indirects whether a record will be stores data dictionary tables recording the export.
Question 55. What Is The Use Of Rows Option In Imp Command?
Answer :
A flag to indicate whether rows should be imported. If this is set to 'N' then only DDL for database objects will be executed.
Question 56. What Is The Use Of Indexes Option In Imp Command?
Answer :
A flag to indicate whether import should import index on tables or not.
Question 57. What Is The Use Of Grant Option In Imp Command?
Answer :
A flag to indicate whether grants on database objects will be imported.
Question 58. What Is The Use Of Show Option In Imp Command?
Answer :
A flag to indicate whether file content should be displayed or not.
Question 59. What Is The Use Of File Option In Imp Command?
Answer :
The name of the file from which import should be performed.
Question 60. What Is Use Of Log (ver 7) Option In Exp Command?
Answer :
The name of the file which log of the export will be written.
Oracle Memory Management Related Tutorials |
|
---|---|
Oracle 9i Tutorial | Oracle DBA Tutorial |
Oracle 11g Tutorial |
Oracle Memory Management Related Practice Tests |
|
---|---|
Oracle 9i Practice Tests | Oracle DBA Practice Tests |
Oracle 11g Practice Tests | Oracle Data Integrator (ODI) Practice Tests |
Oracle MySQL 5.6 Database Administrator Practice Tests | Oracle Exadata Database Practice Tests |
Relational Database Concepts
Sql*plus And Isql*plus Basics
Oracle Database Functions
Restricting, Sorting, And Grouping Data
Using Multiple Tables
Advanced Sql Queries
Logical Consistency
Installing Oracle And Creating A Database
Reporting Techniques
Creating And Maintaining Database Objects
Users And Security
Making Things Run Fast (enough)
Saving Your Stuff (backups)
Troubleshooting
Text Resumes
Visual Resumes
Social Resumes
Resume Quality
IT Resume Samples
MARKETING Resume Samples
HR Resume Samples
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.