Oracle 11g

Instructions:

• Answer 50 questions in 15 minutes.
• If you are not ready to take this test, you can study here.
• Match each statement with the correct term.
• Don't refresh. All questions and answers are randomly picked and ordered every time you load a test.

This is a study tool. The 3 wrong answers for each question are randomly chosen from answers to other questions. So, you might find at times the answers obvious, but you will see it re-enforces your understanding as you take the test each time.

1. List the kinds of files used to run an Oracle Instance?
Large Pool - Java Pool - Streams Pool
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.
Whenever successful - whenever not successful - by access - by session
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files


2. What does the Program Global Area (PGA) contain?
You cannot restore a database with a complete collection of these from your last full backup.
These are contained where? Session Memory - login information - session specific information - Private SQL area - Variables that get values during SQL Execution - Work areas for SQL Sort - Hash - bitmap - Cursors
This cleans up failed processes by releasing resources and rolling back uncomitted data.
This is the concept that adding and removing privileges from a role immediately affect all users who have the role.


3. What does the WITH ADMIN OPTION do?
This cache is allocated at instance startup
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.
Allows a user to grant a system privilege to another user.
What logical structure do data files correspond to?


4. What is the library cache?
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.
This controls what a user can do in the database. Primarily applies to adding or changing structures in the database.
This process initiates check points -- dumps all dirty buffers to disk. Also updates the data file headers and the control files with the checkpoint information so SMON knows where to start recovery if system crashes.
This is a library of ready to go SQL Statements.


5. How do you assign a password profile to a user
These files are stored in ORACLE_HOME: $ORACLE_HOME/dbs - %ORACLE_HOMEdatabase
Alter user HR profile report_writer;
This controls how a user can access actual data in the database. Primarily applies to rows in a table or a view.
This is a modification applied to something; executing DML generates these applied to data.


6. What is a segment
Create user OPS$REPORTS identified externally;
This is a logical structure that represents objects in the database that require physical storage
This is the physical component or the files
Names and locations of data files and redo log files - Recovery and backup information - Checkpoint - Archive information - Database name - Log history and current logging information.


7. What is Advanced Security Option
This lets you encrypt data in your database files
This is a library of ready to go SQL Statements.
Block in the database buffer cache does not match what is on disk.
This process resolves deadlock situations


8. When is the redo log buffer flushed?
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.
This controls what a user can do in the database. Primarily applies to adding or changing structures in the database.
This is the first part of a data file.
Tables - Indexes - Materialized Views - Partitions


9. What does process CJQ0 do?
This is a library of ready to go SQL Statements.
The first extent is created when?
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;
This process checks for scheduled tasks within the database.


10. What is the TEMP tablespace used for?
This tablespace is used for temporary storage.
Tables - Indexes - Materialized Views - Partitions
These are usually configured 3 groups with 2 mirrored members in each.
This tablespace stored objects that are auxiliary and not specifically tied to the core features of the database


11. What does parameter MEMORY_TARGET not control?
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.
Revoke create session from <user>;
Memory used by sessions connecting to the database are not controlled by this parameter.


12. What are the PFILE and SPFILE?
Recognizes a user as logged into the OS and waives the password requirement.
Alter user HR profile report_writer;
These files contain information about how your running database is configured.
Allows a user to grant a system privilege to another user.


13. Which process writes to the Redo Log Files?
SYSDBA is this kind of role.
The logwriter (LGWR) writes to these files.
This file should be named spfile<dbname>.ora
This is a special type of database file holding only information for sorts or temporary tables.


14. What is the large pool?
This is the concept that adding and removing privileges from a role immediately affect all users who have the role.
This relieves the shared pool of sometimes-transient memory requirements.
This physical structure is the formatted size of the minimum unit of storage on the device
The tablespace is what level of logical organization?


15. What information is NOT stored in redo logs
Allows you to group privileges for ease of management
SELECT statement information are not stored in these files?
This is the logical structure that makes up segments.
Block in the database buffer cache does not match what is on disk.


16. What is the Program Global Area -- PGA?
Allows you to group privileges for ease of management
What is OPS$
What logical structure do data files correspond to?
This contains information used for private or session-related information that individual users need


17. What are the 3 processes that are part of an instance?
This is a library of ready to go SQL Statements.
This can only mount to one database
These use which pool? Oracle Recovery Manager - Oracle Shared Server - Parallel processing - I/O-related server processes.
Background processes - Server Processes - User Processes are part of what?


18. What is the SQL for creating a password profile
...
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;
SYSTEM - SYSAUX - UNDO - TEMP
Oracle background processes and server processes share this.


19. What does the CREATE SESSION privilege do?
This cache is allocated at instance startup
These are the PFILE and the SPFILE
Data requested by user is found in the database buffer cache. No need to read from disk.
This grants users access to the database.


20. What is the Database Buffer Cache


21. What is the first level of logical organization of your physical storage?
This controls what a user can do in the database. Primarily applies to adding or changing structures in the database.
The tablespace is what level of logical organization?
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.
This process takes dirty blocks from dirty list and writes them to disk.


22. What are the names of the parameter files?
Whenever successful - whenever not successful - by access - by session
This physical structure is the formatted size of the minimum unit of storage on the device
Database shutdown - full or partial checkpoint - recovery time threshold - free block is needed - some DDL commands - every three seconds
These are the PFILE and the SPFILE


23. What is the SYSTEM tablespace used for?
This tablespace stores the core database objects that are used for running the database itself
The first extent is created when?
Revoke create session from <user>;
This tablespace is used for temporary storage.


24. What is the block header?
These files contain information about how your running database is configured.
This is the first part of a data file.
Revoke create session from <user>;
What is OPS$


25. This logical structure gets created when you first create an object such as a table?
What logical structure do data files correspond to?
The first extent is created when?
This is the physical component or the files
Show parameter audit_file_dest;


26. When does the large pool get cleaned out?
This is a combination of memory and processes that are part of a running installation
If you have a full backup and copies of these you can recover from almost any type of failure.
This pool does not use LRU and will fill up.
SYSDBA is this kind of role.


27. What SQL lets you see where SYSDBA connections audits are stored?
Show parameter audit_file_dest;
This is a library of ready to go SQL Statements.
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;
These files store information from the log buffer.


28. What kinds of objects are not segments -but are pieces of code in the SYSTEM tablespace.
The first extent is created when?
Views - Procedures - Synonyms - Sequences
Large Pool - Java Pool - Streams Pool
Show parameter audit_file_dest;


29. What does process CKPT do?
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.
This process initiates check points -- dumps all dirty buffers to disk. Also updates the data file headers and the control files with the checkpoint information so SMON knows where to start recovery if system crashes.
DBA_STMT_AUDIT_OPTS is a view that does what?
What logical structure do data files correspond to?


30. Where does auditing data go by default
Row level security is this kind of database.
Audit create table;
What goes into the table SYS.AUD$
Memory used by sessions connecting to the database are not controlled by this parameter.


31. What characters do all background processes start with?


32. What kind of objects can be in a segment
Free - Pinned - Dirty are what?
This file should be named init<dbname>.ora
This process flushes the redo log buffer. Writes redo entries to disk and signals a completion.
Tables - Indexes - Materialized Views - Partitions


33. What kinds of server processes are there?
This process can be dedicated or shared
Show parameter audit_file_dest;
Memory used by sessions connecting to the database are not controlled by this parameter.
Database Buffer Cache - Log Buffer - Shared Pool


34. How often should you switch log files
Show parameter audit_file_dest;
You should switch these every 15-30 minutes
This tablespace is used for temporary storage.
...


35. What uses the large pool?
V$TEMPFILE and V$DATAFILE contain this information?
These are contained where? Session Memory - login information - session specific information - Private SQL area - Variables that get values during SQL Execution - Work areas for SQL Sort - Hash - bitmap - Cursors
These use which pool? Oracle Recovery Manager - Oracle Shared Server - Parallel processing - I/O-related server processes.
You must have at least 2 of the log file groups.


36. How does select use Database Buffer Cache
Database shutdown - full or partial checkpoint - recovery time threshold - free block is needed - some DDL commands - every three seconds
Names and locations of data files and redo log files - Recovery and backup information - Checkpoint - Archive information - Database name - Log history and current logging information.
This tablespace stores the rollback or undo segments used for transaction recovery
Data blocks copied into database buffer cache - relevant rows copied into user session PGA - blocks stay in cache until buffer needed for something else.


37. What does the WITH GRANT OPTION do?
Allows a user to grant a system privilege to another user.
Allows a user to grant an object privilege to another user.
Alter user HR profile report_writer;
What goes into the table SYS.AUD$


38. Where are the PFILE and SPFILE stored?
This is a logical structure that represents objects in the database that require physical storage
DBA_PRIV_AUDIT_OPTS is a view that does what?
These files are stored in ORACLE_HOME: $ORACLE_HOME/dbs - %ORACLE_HOMEdatabase
This process can be dedicated or shared


39. How many databases can an instance mount to?
This can only mount to one database
You cannot restore a database with a complete collection of these from your last full backup.
...
The tablespace is what level of logical organization?


40. What views contain information about your DB Files?
Memory Size - Database and Instance Name - Archiving parameters - Processes - Over 1900 other parameters
This contains information used for private or session-related information that individual users need
This is a logical structure that represents objects in the database that require physical storage
V$TEMPFILE and V$DATAFILE contain this information?


41. What SQL gives a user the ability to grant system privileges to another user?
Grant create session to <user> with admin option;
Limit number of times a password can be reused - limit amount of time before a password can be reused - limit failed login attempts - password lock time - passwords a life time - password grace time - check password complexity.
Allows a user to grant an object privilege to another user.
This is a collection of information about you and the database's objects.


42. What are Archive log files?
Memory used by sessions connecting to the database are not controlled by this parameter.
This contains the information used for backup and recovery - free space - file status details.
These are copies of redo log files
These connections always get a dedicated server process.


43. What is an Extent?
This is the minimum amount of storage that Oracle reads or writes.
This is the logical structure that makes up segments.
This file should be named init<dbname>.ora
This controls what a user can do in the database. Primarily applies to adding or changing structures in the database.


44. What physical structure does a tablespace correspond to?
What logical structure do data files correspond to?
DBA_PRIV_AUDIT_OPTS is a view that does what?
This pool does not use LRU and will fill up.
Allows a user to grant an object privilege to another user.


45. What actions are always audited
What is OPS$
This can only mount to one database
DBA_STMT_AUDIT_OPTS is a view that does what?
When are connections with SYSDBA privilege audited?


46. What is an object privilege?
This controls how a user can access actual data in the database. Primarily applies to rows in a table or a view.
This controls what a user can do in the database. Primarily applies to adding or changing structures in the database.
You cannot restore a database with a complete collection of these from your last full backup.
This cleans up failed processes by releasing resources and rolling back uncomitted data.


47. What naming convention should you use for your PFILE
These all start with 'ora_'
These are usually configured 3 groups with 2 mirrored members in each.
Alter user HR profile report_writer;
This file should be named init<dbname>.ora


48. What does process DBWn do?
Data blocks copied into database buffer cache - changes applied to the copy in the DBBC - Blocks remain in cache until buffer is needed for something else.
This file should be named init<dbname>.ora
This process takes dirty blocks from dirty list and writes them to disk.
This tablespace is used for temporary storage.


49. What is the PL/SQL function result cache?
This stores the results of a computation.
Data blocks copied into database buffer cache - relevant rows copied into user session PGA - blocks stay in cache until buffer needed for something else.
This process resolves deadlock situations
With this server process each user process gets its own server process. Server process waits on you. Most responsive.


50. What does process PMON do?
This cleans up failed processes by releasing resources and rolling back uncomitted data.
DBA_OBJ_AUDIT_OPTS is a view that does what?
Block in the database buffer cache does not match what is on disk.
These connections always get a dedicated server process.