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 VKTM do?
Recognizes a user as logged into the OS and waives the password requirement.
The tablespace is what level of logical organization?
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE
This processs provides a time reference within the database.

2. What does process PMON do?
This tablespace stores the rollback or undo segments used for transaction recovery
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.
This cleans up failed processes by releasing resources and rolling back uncomitted data.
These files are stored in ORACLE_HOME: $ORACLE_HOME/dbs - %ORACLE_HOMEdatabase

3. What is a database block?
This is the minimum amount of storage that Oracle reads or writes.
Revoke create session from <user>;
This file should be named init<dbname>.ora
Create user OPS$REPORTS identified externally;

4. What does process LGWR do?
Tables - Indexes - Materialized Views - Partitions
If you have a full backup and copies of these you can recover from almost any type of failure.
This process resolves deadlock situations
This process flushes the redo log buffer. Writes redo entries to disk and signals a completion.

5. How does select use Database Buffer Cache
The logwriter (LGWR) writes to these files.
This process resolves deadlock situations
Data blocks copied into database buffer cache - relevant rows copied into user session PGA - blocks stay in cache until buffer needed for something else.
Block in the database buffer cache does not match what is on disk.

6. What does process CJQ0 do?
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.
This cache is allocated at instance startup
This process checks for scheduled tasks within the database.
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;

7. What view lets you look at audits of privileges
Show parameter audit_file_dest;
If you have a full backup and copies of these you can recover from almost any type of failure.
This process can be dedicated or shared
DBA_PRIV_AUDIT_OPTS is a view that does what?

8. What naming convention should you use for your SPFILE?
This cache is allocated at instance startup
This can only mount to one database
This file should be named spfile<dbname>.ora
These all start with 'ora_'

9. What does the CREATE SESSION privilege do?
This grants users access to the database.
This physical structure is the formatted size of the minimum unit of storage on the device
This controls what a user can do in the database. Primarily applies to adding or changing structures in the database.
Free - Pinned - Dirty are what?

10. What SQL gives a user the ability to grant system privileges to another user?
Grant create session to <user> with admin option;
Allows a user to grant an object privilege to another user.
This process resolves deadlock situations
What goes into the table SYS.AUD$

11. What does process DIA0 do?
This is the first part of a data file.
DBA_STMT_AUDIT_OPTS is a view that does what?
This processs provides a time reference within the database.
This process resolves deadlock situations

12. This logical structure gets created when you first create an object such as a table?
You must have at least 2 of the log file groups.
Allows you to group privileges for ease of management
These use which pool? Oracle Recovery Manager - Oracle Shared Server - Parallel processing - I/O-related server processes.
The first extent is created when?

13. What tablespaces should every database have?
Whenever successful - whenever not successful - by access - by session
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 first part of a data file.
SYSTEM - SYSAUX - UNDO - TEMP

14. List the kinds of files used to run an Oracle Instance?
SELECT statement information are not stored in these files?
This cleans up failed processes by releasing resources and rolling back uncomitted data.
This is a modification applied to something; executing DML generates these applied to data.
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files

15. What options does a password profile allow you to have?
You must have at least 2 of the log file groups.
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.
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.
DBA_STMT_AUDIT_OPTS is a view that does what?

16. What is the Database Buffer Cache

17. What are the 3 buffer cache states?
Oracle background processes and server processes share this.
Free - Pinned - Dirty are what?
What is OPS$
When are connections with SYSDBA privilege audited?

18. How do you assign a password profile to a user
You must have at least 2 of the log file groups.
Alter user HR profile report_writer;
Views - Procedures - Synonyms - Sequences
This processs provides a time reference within the database.

19. What does the server process do?
AUDIT_TRAIL is set to DB
Database shutdown - full or partial checkpoint - recovery time threshold - free block is needed - some DDL commands - every three seconds
Database Buffer Cache - Log Buffer - Shared Pool
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.

20. What is the first file read when a database starts?
This process resolves deadlock situations
The parameter file is read at what point during database startup?
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 connections always get a dedicated server process.

21. What are Archive log files?
The tablespace is what level of logical organization?
V$TEMPFILE and V$DATAFILE contain this information?
Grant create session to <user>;
These are copies of redo log files

22. What do control files contain?
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 grants users access to the database.
Oracle background processes and server processes share this.
This can only mount to one database

23. What is the database writer process?
This processs provides a time reference within the database.
Background process that write database buffer cache to disk
Grant SELECT on <table> to <user>;
This pool does not use LRU and will fill up.

24. What options do you have for auditing?
Tables - Indexes - Materialized Views - Partitions
This logical structure is the minimum unit that Oracle will read or write at any given time.
Users - Objects - System Privileges - Combination are things you can?
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;

25. What does process CKPT do?
You cannot restore a database with a complete collection of these from your last full backup.
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.
Database shutdown - full or partial checkpoint - recovery time threshold - free block is needed - some DDL commands - every three seconds
Row level security is this kind of database.

26. What view lets you look at audits of system privileges
This process checks for scheduled tasks within the database.
This is a special type of database file holding only information for sorts or temporary tables.
DBA_STMT_AUDIT_OPTS is a view that does what?
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.

27. Which SGA Structures are required
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
Database Buffer Cache - Log Buffer - Shared Pool
This pool does not use LRU and will fill up.
This lets you encrypt data in your database files

28. What is the SQL for creating a password profile
The tablespace is what level of logical organization?
You cannot restore a database with a complete collection of these from your last full backup.
AUDIT_TRAIL is set to DB
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;

29. What happens if you are missing an archive log?
LGWR starts writing to the next group - A Database checkpoint occurs - The DBWR writes dirty blocks our of the buffer cascade
This process copies filled redo logs to the archived redo storage area. If you aren't in archive mode - this process shuts down.
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.
You cannot restore a database with a complete collection of these from your last full backup.

30. What is the SYSTEM tablespace used for?
This contains information used for private or session-related information that individual users need
This tablespace stores the core database objects that are used for running the database itself
This process flushes the redo log buffer. Writes redo entries to disk and signals a completion.
With this server process each user process gets its own server process. Server process waits on you. Most responsive.

31. What are redo log files?
These files store information from the log buffer.
Database shutdown - full or partial checkpoint - recovery time threshold - free block is needed - some DDL commands - every three seconds
This lets you encrypt data in your database files
Block in the database buffer cache does not match what is on disk.

32. What is Virtual Private Database?
If you have a full backup and copies of these you can recover from almost any type of failure.
Row level security is this kind of database.
Free - Pinned - Dirty are what?
This process flushes the redo log buffer. Writes redo entries to disk and signals a completion.

33. What prefix must precede the OS username for an OS-Authenticated user?
This cleans up failed processes by releasing resources and rolling back uncomitted data.
What is OPS$
This file should be named init<dbname>.ora
Whenever successful - whenever not successful - by access - by session

34. What is Operating System Authentication?
This forces a user to follow guidelines when creating or changing passwords.
This logical structure is the minimum unit that Oracle will read or write at any given time.
Recognizes a user as logged into the OS and waives the password requirement.
Grant SELECT on <table> to <user>;

35. What is an Extent?
This is a logical structure that represents objects in the database that require physical storage
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;
This is the logical structure that makes up segments.

36. What is a segment
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
LGWR starts writing to the next group - A Database checkpoint occurs - The DBWR writes dirty blocks our of the buffer cascade
This process resolves deadlock situations
This is a logical structure that represents objects in the database that require physical storage

37. What is a password verify function

38. When is database buffer cache allocated?
This process is primarily responsible for instance recovery. Applies redo information. Cleans and releases temporary space
This cache is allocated at instance startup
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.
Tables - Indexes - Materialized Views - Partitions

39. What is audited by default in Oracle 11g?
When are connections with SYSDBA privilege audited?
This contains information used for private or session-related information that individual users need
This lets you encrypt data in your database files
...

40. How many datafiles does a tablespace organize?
DBA_STMT_AUDIT_OPTS is a view that does what?
This is the logical structure that makes up segments.
Oracle background processes and server processes share this.
This logical structure organizes one or more data files.

41. What is the minimum number of redo log groups you must have?
These files contain information about how your running database is configured.
Create user OPS$REPORTS identified externally;
You must have at least 2 of the log file groups.
This logical structure organizes one or more data files.

42. What is the first level of logical organization of your physical storage?
Block in the database buffer cache does not match what is on disk.
This logical structure organizes one or more data files.
The tablespace is what level of logical organization?
Oracle background processes and server processes share this.

43. What naming convention should you use for your PFILE
This file should be named init<dbname>.ora
Show parameter audit_file_dest;
Oracle background processes and server processes share this.
This processs provides a time reference within the database.

44. What does process DBWn do?
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files
This process takes dirty blocks from dirty list and writes them to disk.
This file should be named init<dbname>.ora
What logical structure do data files correspond to?

45. What options can you add to audits?
This contains information used for private or session-related information that individual users need
These files contain information about how your running database is configured.
Whenever successful - whenever not successful - by access - by session
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

46. What SQL lets you see where SYSDBA connections audits are stored?
Show parameter audit_file_dest;
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.
Memory used by sessions connecting to the database are not controlled by this parameter.
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.

47. What are the PFILE and SPFILE?
DBA_STMT_AUDIT_OPTS is a view that does what?
This tablespace stores the rollback or undo segments used for transaction recovery
What goes into the table SYS.AUD$
These files contain information about how your running database is configured.

48. What is the Redo Log Buffer
This cache is allocated at instance startup
This tablespace stores the core database objects that are used for running the database itself
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.
This tablespace is used for temporary storage.

49. When does the large pool get cleaned out?
This pool does not use LRU and will fill up.
Oracle background processes and server processes share this.
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;
These use which pool? Oracle Recovery Manager - Oracle Shared Server - Parallel processing - I/O-related server processes.

50. What is dynamic privilege management?
What goes into the table SYS.AUD$
This is the concept that adding and removing privileges from a role immediately affect all users who have the role.
You cannot restore a database with a complete collection of these from your last full backup.
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.