1. What is an Oracle Instance?
An instance is the (executed) Oracle software (Oracle processes) and the memory they use (SGA). It is the instance that manipulates the data stored in the database.
2. What information is stored in Control File?
The database name
The timestamp of database creation
The names and locations of associated datafiles and redo log files
Tablespace information
Datafile offline ranges
The log history
Archived log information
Backup set and backup piece information
Backup datafile and redo log information
Datafile copy information
The current log sequence number
3. When you start an Oracle DB which file is accessed first?
Oracle first open and reads the initialization parameter file (init.ora).
4. What is the Job of SMON, PMON processes?
PMON—It cleaned up the failed processes BY
>> Rolling back the transaction
>> Releasing all currently held table or row locks
>> freeing other resource currently held by the user
>>Restart the dead dispatcher
SMON—if the oracle instance fails , any information that in SGA that has not been written to the disk is cost .
>> the background process smon automatic perform instance recovery when the database is reopened.
>>Instance recovery
• Roll forward changes in the redolog
• Open the database for user access
• Roll back the uncommitted transaction also perform
• It combines or coalesces, adjacent area of free space in the datafile .
• Dislocate the temporary segment
5. What is Instance Recovery?
Instance recovery
• Roll forward changes in the redolog
• Open the database for user access
• Roll back the uncommitted transaction also perform
• It combines or coalesces, adjacent area of free space in the datafile .
• Dislocate the temporary segment
6. What is written in Redo Log Files?
# Log writer (LGWR) writes redo log buffer contents Into Redo Log FIles. Log writer does this every three seconds, when the redo log buffer is 1/3 full and immediately before the Database Writer (DBWn) writes its changed buffers into the datafile.
7. How do you control number of Datafiles one can have in an Oracle database?
8. How many Maximum Datafiles can there be in an Oracle Database?
In a database there can have maximum 65533 data files.
9. What is a Tablespace?
A tablespace is a logical group of data files in a database. A database typically contains at least one tablespace, and usually two or more. Within the database, the tablespace plays a role similar to that of a folder on the hard drive of a computer.
Some database programs, such as Oracle, automatically create a tablespace called SYSTEM that contains general information about the structure and contents of the database. A small database can be entirely contained in the SYSTEM tablespace, but in most cases, user datais placed in other tablespaces.
There is no relationship between a tablespace and a schema. Objects in the same schema can be in different tablespaces, and a tablespace can hold objects from different schemas.
10. What is the purpose of Redo Log files?
11. Which default Database roles are created when you create a Database?
12. What is a Checkpoint?
13. Which Process reads data from Datafiles?
14. Which Process writes data in Datafiles?
15. Can you make a Datafile auto extendible. If yes, how?
16. What is a Shared Pool?
17. What is kept in the Database Buffer Cache?
18. How many maximum Redo Logfiles one can have in a Database?
19. What is difference between PFile and SPFile?
20. What is PGA_AGGREGRATE_TARGET parameter?
21. Large Pool is used for what?
22. What is PCT Increase setting?
23. What is PCTFREE and PCTUSED Setting?
24. What is Row Migration and Row Chaining?
25. What is 01555 - Snapshot Too Old error and how do you avoid it?
26. What is a Locally Managed Tablespace?
27. Can you audit SELECT statements?
28. What does DBMS_FGA package do?
29. What is Cost Based Optimization?
30. How often you should collect statistics for a table?
31. How do you collect statistics for a table, schema and Database?
32. Can you make collection of Statistics for tables automatic?
33. On which columns you should create Indexes?
34. What type of Indexes are available in Oracle?
35. What is B-Tree Index?
36. A table is having few rows, should you create indexes on this table?
37. A Column is having many repeated values which type of index you should create on this
column, if you have to?
38. When should you rebuilt indexes?
39. Can you built indexes online?
40. Can you see Execution Plan of a statement.
41. A table is created with the following setting
storage (initial 200k next 200k minextents 2 maxextents 100 pctincrease
40) What will be size of 4th extent?
42. What is DB Buffer Cache Advisor?
43. What is STATSPACK tool?
44. Can you change SHARED_POOL_SIZE online?
45. Can you Redefine a table Online?
46. Can you assign Priority to users?
47. You want users to change their passwords every 2 months. How do you enforce this?
48. How do you delete duplicate rows in a table?
49. What is Automatic Management of Segment Space setting?
50. What is the difference between DELETE and TRUNCATE statements?
51. What is COMPRESS and CONSISTENT setting in EXPORT utility?
52. What is the difference between Direct Path and Convention Path loading?
53. Can you disable and enable Primary key?
54. What is an Index Organized Table?
55. What is a Global Index and Local Index?
56. What is the difference between Range Partitioning and Hash Partitioning?
57. What is difference between Multithreaded/Shared Server and Dedicated Server?
58. Can you import objects from Oracle ver. 9i to 10g?
59. How do you move tables from one tablespace to another tablespace?
60. How do see how much space is used and free in a tablespace?
any mistake is there or u wanna give any suggestion so please write to me ............
An instance is the (executed) Oracle software (Oracle processes) and the memory they use (SGA). It is the instance that manipulates the data stored in the database.
2. What information is stored in Control File?
The database name
The timestamp of database creation
The names and locations of associated datafiles and redo log files
Tablespace information
Datafile offline ranges
The log history
Archived log information
Backup set and backup piece information
Backup datafile and redo log information
Datafile copy information
The current log sequence number
3. When you start an Oracle DB which file is accessed first?
Oracle first open and reads the initialization parameter file (init.ora).
4. What is the Job of SMON, PMON processes?
PMON—It cleaned up the failed processes BY
>> Rolling back the transaction
>> Releasing all currently held table or row locks
>> freeing other resource currently held by the user
>>Restart the dead dispatcher
SMON—if the oracle instance fails , any information that in SGA that has not been written to the disk is cost .
>> the background process smon automatic perform instance recovery when the database is reopened.
>>Instance recovery
• Roll forward changes in the redolog
• Open the database for user access
• Roll back the uncommitted transaction also perform
• It combines or coalesces, adjacent area of free space in the datafile .
• Dislocate the temporary segment
5. What is Instance Recovery?
Instance recovery
• Roll forward changes in the redolog
• Open the database for user access
• Roll back the uncommitted transaction also perform
• It combines or coalesces, adjacent area of free space in the datafile .
• Dislocate the temporary segment
6. What is written in Redo Log Files?
# Log writer (LGWR) writes redo log buffer contents Into Redo Log FIles. Log writer does this every three seconds, when the redo log buffer is 1/3 full and immediately before the Database Writer (DBWn) writes its changed buffers into the datafile.
7. How do you control number of Datafiles one can have in an Oracle database?
8. How many Maximum Datafiles can there be in an Oracle Database?
In a database there can have maximum 65533 data files.
9. What is a Tablespace?
A tablespace is a logical group of data files in a database. A database typically contains at least one tablespace, and usually two or more. Within the database, the tablespace plays a role similar to that of a folder on the hard drive of a computer.
Some database programs, such as Oracle, automatically create a tablespace called SYSTEM that contains general information about the structure and contents of the database. A small database can be entirely contained in the SYSTEM tablespace, but in most cases, user datais placed in other tablespaces.
There is no relationship between a tablespace and a schema. Objects in the same schema can be in different tablespaces, and a tablespace can hold objects from different schemas.
10. What is the purpose of Redo Log files?
11. Which default Database roles are created when you create a Database?
12. What is a Checkpoint?
13. Which Process reads data from Datafiles?
14. Which Process writes data in Datafiles?
15. Can you make a Datafile auto extendible. If yes, how?
16. What is a Shared Pool?
17. What is kept in the Database Buffer Cache?
18. How many maximum Redo Logfiles one can have in a Database?
19. What is difference between PFile and SPFile?
20. What is PGA_AGGREGRATE_TARGET parameter?
21. Large Pool is used for what?
22. What is PCT Increase setting?
23. What is PCTFREE and PCTUSED Setting?
24. What is Row Migration and Row Chaining?
25. What is 01555 - Snapshot Too Old error and how do you avoid it?
26. What is a Locally Managed Tablespace?
27. Can you audit SELECT statements?
28. What does DBMS_FGA package do?
29. What is Cost Based Optimization?
30. How often you should collect statistics for a table?
31. How do you collect statistics for a table, schema and Database?
32. Can you make collection of Statistics for tables automatic?
33. On which columns you should create Indexes?
34. What type of Indexes are available in Oracle?
35. What is B-Tree Index?
36. A table is having few rows, should you create indexes on this table?
37. A Column is having many repeated values which type of index you should create on this
column, if you have to?
38. When should you rebuilt indexes?
39. Can you built indexes online?
40. Can you see Execution Plan of a statement.
41. A table is created with the following setting
storage (initial 200k next 200k minextents 2 maxextents 100 pctincrease
40) What will be size of 4th extent?
42. What is DB Buffer Cache Advisor?
43. What is STATSPACK tool?
44. Can you change SHARED_POOL_SIZE online?
45. Can you Redefine a table Online?
46. Can you assign Priority to users?
47. You want users to change their passwords every 2 months. How do you enforce this?
48. How do you delete duplicate rows in a table?
49. What is Automatic Management of Segment Space setting?
50. What is the difference between DELETE and TRUNCATE statements?
51. What is COMPRESS and CONSISTENT setting in EXPORT utility?
52. What is the difference between Direct Path and Convention Path loading?
53. Can you disable and enable Primary key?
54. What is an Index Organized Table?
55. What is a Global Index and Local Index?
56. What is the difference between Range Partitioning and Hash Partitioning?
57. What is difference between Multithreaded/Shared Server and Dedicated Server?
58. Can you import objects from Oracle ver. 9i to 10g?
59. How do you move tables from one tablespace to another tablespace?
60. How do see how much space is used and free in a tablespace?
any mistake is there or u wanna give any suggestion so please write to me ............