Test your basic knowledge |

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. What does process DIA0 do?
What logical structure do data files correspond to?
Database Buffer Cache - Log Buffer - Shared Pool
This process resolves deadlock situations
This process takes dirty blocks from dirty list and writes them to disk.

2. What is the dictionary cache?

3. What triggers a block write and therefore a dirty block?
Grant SELECT on <table> to <user> WITH GRANT OPTION;
Database shutdown - full or partial checkpoint - recovery time threshold - free block is needed - some DDL commands - every three seconds
This is a combination of memory and processes that are part of a running installation
Background process that write database buffer cache to disk

4. What are the 8 object priviliges?
This controls how a user can access actual data in the database. Primarily applies to rows in a table or a view.
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE
This tablespace stores the rollback or undo segments used for transaction recovery
This tablespace stored objects that are auxiliary and not specifically tied to the core features of the database

5. How does updating use Database Buffer Cache
This controls how a user can access actual data in the database. Primarily applies to rows in a table or a view.
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 is a combination of memory and processes that are part of a running installation
Background process that write database buffer cache to disk

6. What is the UNDO tablespace used for?
V$MEMORY_TARGET_ADVICE
This contains information used for private or session-related information that individual users need
This process does anything the user requests of it. It is responsible for reading blocks into the buffer cache. It changes the blocks if requested. It can create objects.
This tablespace stores the rollback or undo segments used for transaction recovery

7. Which SGA Structures are optional
Allows a user to grant a system privilege to another user.
Recognizes a user as logged into the OS and waives the password requirement.
Large Pool - Java Pool - Streams Pool
V$TEMPFILE and V$DATAFILE contain this information?

8. What is an object privilege?
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 controls how a user can access actual data in the database. Primarily applies to rows in a table or a view.
Revoke create session from <user>;
This grants users access to the database.

9. What SQL Would let you audit people creating tables?
Users - Objects - System Privileges - Combination are things you can?
The first extent is created when?
This process copies filled redo logs to the archived redo storage area. If you aren't in archive mode - this process shuts down.
Audit create table;

10. How do you assign a password profile to a user
This is the concept that adding and removing privileges from a role immediately affect all users who have the role.
Alter user HR profile report_writer;
LGWR starts writing to the next group - A Database checkpoint occurs - The DBWR writes dirty blocks our of the buffer cascade
Memory used by sessions connecting to the database are not controlled by this parameter.

11. What options does a password profile allow you to have?
DBA_PRIV_AUDIT_OPTS is a view that does what?
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.
These use which pool? Oracle Recovery Manager - Oracle Shared Server - Parallel processing - I/O-related server processes.
If you have a full backup and copies of these you can recover from almost any type of failure.

12. What is the database writer process?
These are the PFILE and the SPFILE
Database Buffer Cache - Log Buffer - Shared Pool
This contains information used for private or session-related information that individual users need
Background process that write database buffer cache to disk

13. What SQL gives a user the ability to grant object privileges to another user?
This logical structure organizes one or more data files.
These files contain information about how your running database is configured.
Grant SELECT on <table> to <user> WITH GRANT OPTION;
Users - Objects - System Privileges - Combination are things you can?

14. What options do you have for auditing?
This is written in PL/SQL and examines passwords when they're chosen and accepts or rejects them based on criteria.
With this server process each user process gets its own server process. Server process waits on you. Most responsive.
SYSDBA is this kind of role.
Users - Objects - System Privileges - Combination are things you can?

15. What does process LGWR do?
What logical structure do data files correspond to?
This process flushes the redo log buffer. Writes redo entries to disk and signals a completion.
V$MEMORY_TARGET_ADVICE
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE

16. What does the Program Global Area (PGA) contain?
This process resolves deadlock situations
Grant SELECT on <table> to <user>;
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 is the logical structure that makes up segments.

17. What does the CREATE SESSION privilege do?
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;
Database shutdown - full or partial checkpoint - recovery time threshold - free block is needed - some DDL commands - every three seconds
This grants users access to the database.
This cache is allocated at instance startup

18. What is a buffer cache hit?
Data requested by user is found in the database buffer cache. No need to read from disk.
Memory used by sessions connecting to the database are not controlled by this parameter.
These are copies of redo log files
Free - Pinned - Dirty are what?

19. When does the large pool get cleaned out?
This process takes dirty blocks from dirty list and writes them to disk.
Allows you to group privileges for ease of management
This logical structure is the minimum unit that Oracle will read or write at any given time.
This pool does not use LRU and will fill up.

20. What SQL lets you see where SYSDBA connections audits are stored?
Users - Objects - System Privileges - Combination are things you can?
This cleans up failed processes by releasing resources and rolling back uncomitted data.
This process is primarily responsible for instance recovery. Applies redo information. Cleans and releases temporary space
Show parameter audit_file_dest;

21. What is Operating System Authentication?
With this server process each user process gets its own server process. Server process waits on you. Most responsive.
Show parameter audit_file_dest;
Recognizes a user as logged into the OS and waives the password requirement.
Data blocks copied into database buffer cache - relevant rows copied into user session PGA - blocks stay in cache until buffer needed for something else.

22. What is the Log Buffer
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.
Grant create session to <user>;
These are usually configured 3 groups with 2 mirrored members in each.
This physical structure is the formatted size of the minimum unit of storage on the device

23. What is a segment
These connections always get a dedicated server process.
This is a logical structure that represents objects in the database that require physical storage
This pool does not use LRU and will fill up.
Free - Pinned - Dirty are what?

24. What is a dirty buffer?
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;
Create user OPS$REPORTS identified externally;
Block in the database buffer cache does not match what is on disk.

25. What is audited by default in Oracle 11g?
...
V$TEMPFILE and V$DATAFILE contain this information?
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.
The tablespace is what level of logical organization?

26. What is the first level of logical organization of your physical storage?
This process does anything the user requests of it. It is responsible for reading blocks into the buffer cache. It changes the blocks if requested. It can create objects.
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.
When are connections with SYSDBA privilege audited?
The tablespace is what level of logical organization?

27. What is the SYSAUX tablespace used for?
This tablespace stored objects that are auxiliary and not specifically tied to the core features of the database
SYSTEM - SYSAUX - UNDO - TEMP
Oracle background processes and server processes share this.
You should switch these every 15-30 minutes

28. What does process CJQ0 do?
This pool does not use LRU and will fill up.
This process checks for scheduled tasks within the database.
This grants users access to the database.
Grant SELECT on <table> to <user> WITH GRANT OPTION;

29. What can you configure in the parameter files?
This is a combination of memory and processes that are part of a running installation
This file should be named spfile<dbname>.ora
Memory Size - Database and Instance Name - Archiving parameters - Processes - Over 1900 other parameters
LGWR starts writing to the next group - A Database checkpoint occurs - The DBWR writes dirty blocks our of the buffer cascade

30. Which process writes to the Redo Log Files?
The logwriter (LGWR) writes to these files.
Tables - Indexes - Materialized Views - Partitions
Alter user HR profile report_writer;
This is the physical component or the files

31. What is a database
Grant SELECT on <table> to <user>;
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.
This is the physical component or the files
This logical structure is the minimum unit that Oracle will read or write at any given time.

32. What is the Redo Log Buffer
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.
These are copies of redo log files
This tablespace stores the rollback or undo segments used for transaction recovery
Memory used by sessions connecting to the database are not controlled by this parameter.

33. What options can you add to audits?
This controls how a user can access actual data in the database. Primarily applies to rows in a table or a view.
This pool does not use LRU and will fill up.
Background process that write database buffer cache to disk
Whenever successful - whenever not successful - by access - by session

34. What do archive log and redo logs allow you to do?
Create user OPS$REPORTS identified externally;
...
This tablespace is used for temporary storage.
If you have a full backup and copies of these you can recover from almost any type of failure.

35. What is the minimum number of redo log groups you must have?
You must have at least 2 of the log file groups.
This is the logical structure that makes up segments.
This logical structure organizes one or more data files.
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;

36. What view lets you look at audits of system privileges
This process takes dirty blocks from dirty list and writes them to disk.
DBA_STMT_AUDIT_OPTS is a view that does what?
Database Buffer Cache - Log Buffer - Shared Pool
This process runs the job given to it by the CJQ0 process

37. how do you turn on or off auditing?
This grants users access to the database.
Allows a user to grant a system privilege to another user.
AUDIT - NOAUDIT
This cleans up failed processes by releasing resources and rolling back uncomitted data.

38. What does a database role do?
Large Pool - Java Pool - Streams Pool
...
Allows you to group privileges for ease of management
When are connections with SYSDBA privilege audited?

39. What is a database block?
The parameter file is read at what point during database startup?
This is the minimum amount of storage that Oracle reads or writes.
Revoke create session from <user>;
Each concurrent database request gets a server process.

40. Which view helps you size memory properly
V$MEMORY_TARGET_ADVICE
This cleans up failed processes by releasing resources and rolling back uncomitted data.
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;
This process does anything the user requests of it. It is responsible for reading blocks into the buffer cache. It changes the blocks if requested. It can create objects.

41. What happens if you are missing an archive log?
SYSDBA is this kind of role.
You cannot restore a database with a complete collection of these from your last full backup.
Database shutdown - full or partial checkpoint - recovery time threshold - free block is needed - some DDL commands - every three seconds
This is a collection of information about you and the database's objects.

42. When is the redo log buffer flushed?
This is the logical structure that makes up segments.
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.
These connections always get a dedicated server process.
Each concurrent database request gets a server process.

43. What does process DBWn do?
What goes into the table SYS.AUD$
Oracle background processes and server processes share this.
This process takes dirty blocks from dirty list and writes them to disk.
Allows you to group privileges for ease of management

44. What does the WITH GRANT OPTION do?
This logical structure organizes one or more data files.
Allows a user to grant an object privilege to another user.
AUDIT_TRAIL is set to DB
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.

45. What is a system privilege?
These are usually configured 3 groups with 2 mirrored members in each.
This controls what a user can do in the database. Primarily applies to adding or changing structures in the database.
Data requested by user is found in the database buffer cache. No need to read from disk.
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.

46. What is the SQL for creating a password profile
DBA_OBJ_AUDIT_OPTS is a view that does what?
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE
Allows a user to grant a system privilege to another user.

47. What uses the large pool?
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.
These use which pool? Oracle Recovery Manager - Oracle Shared Server - Parallel processing - I/O-related server processes.
This is a modification applied to something; executing DML generates these applied to data.
This stores the results of a computation.

48. What is stored in the block header
This contains the information used for backup and recovery - free space - file status details.
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.
These are usually configured 3 groups with 2 mirrored members in each.
Users - Objects - System Privileges - Combination are things you can?

49. What do control files contain?
Allows a user to grant an object privilege to another user.
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.
The parameter file is read at what point during database startup?
SYSTEM - SYSAUX - UNDO - TEMP

50. How often should you switch log files
You should switch these every 15-30 minutes
Database Buffer Cache - Log Buffer - Shared Pool
This process resolves deadlock situations
Views - Procedures - Synonyms - Sequences