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 is Operating System Authentication?
Recognizes a user as logged into the OS and waives the password requirement.
Alter user HR profile report_writer;
Allows a user to grant an object privilege to another user.
This lets you encrypt data in your database files

2. What prefix must precede the OS username for an OS-Authenticated user?
Memory used by sessions connecting to the database are not controlled by this parameter.
LGWR starts writing to the next group - A Database checkpoint occurs - The DBWR writes dirty blocks our of the buffer cascade
Each concurrent database request gets a server process.
What is OPS$

3. What is the Program Global Area -- PGA?
This stores the results of a computation.
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.
This contains information used for private or session-related information that individual users need
This is a library of ready to go SQL Statements.

4. What does the CREATE SESSION privilege do?
This is the physical component or the files
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;
This grants users access to the database.
SELECT statement information are not stored in these files?

5. How does select use Database Buffer Cache
This pool does not use LRU and will fill up.
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 is the logical structure that makes up segments.
This is the first part of a data file.

6. Is Auditing turned on by default?
AUDIT_TRAIL is set to DB
This controls what a user can do in the database. Primarily applies to adding or changing structures in the database.
SYSTEM - SYSAUX - UNDO - TEMP
DBA_OBJ_AUDIT_OPTS is a view that does what?

7. What actions are always audited
SYSDBA is this kind of role.
Grant SELECT on <table> to <user> WITH GRANT OPTION;
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;
When are connections with SYSDBA privilege audited?

8. What is an object privilege?
Data requested by user is found in the database buffer cache. No need to read from disk.
This process takes dirty blocks from dirty list and writes them to disk.
This controls how a user can access actual data in the database. Primarily applies to rows in a table or a view.
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;

9. What does the server process do?
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.
Create user OPS$REPORTS identified externally;
Audit create table;
Memory used by sessions connecting to the database are not controlled by this parameter.

10. What views contain information about your DB Files?
This is the logical structure that makes up segments.
V$TEMPFILE and V$DATAFILE contain this information?
Users - Objects - System Privileges - Combination are things you can?
The logwriter (LGWR) writes to these files.

11. What are the 3 processes that are part of an instance?
This process flushes the redo log buffer. Writes redo entries to disk and signals a completion.
Background processes - Server Processes - User Processes are part of what?
Alter user HR profile report_writer;
The first extent is created when?

12. What kinds of blocks do data files have?
These files contain information about how your running database is configured.
These are the PFILE and the SPFILE
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.
You should switch these every 15-30 minutes

13. What does process CKPT do?
These are the PFILE and the SPFILE
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 process runs the job given to it by the CJQ0 process
SYSTEM - SYSAUX - UNDO - TEMP

14. What happens during a log switch operation? What causes the following: LGWR finishes writing to the current group
These connections always get a dedicated server process.
Each concurrent database request gets a server process.
This is a modification applied to something; executing DML generates these applied to data.
LGWR starts writing to the next group - A Database checkpoint occurs - The DBWR writes dirty blocks our of the buffer cascade

15. Which SGA Structures are optional
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.
AUDIT_TRAIL is set to DB
Large Pool - Java Pool - Streams Pool
Allows you to group privileges for ease of management

16. What is Advanced Security Option
This lets you encrypt data in your database files
Large Pool - Java Pool - Streams Pool
These are usually configured 3 groups with 2 mirrored members in each.
This process can be dedicated or shared

17. When does the large pool get cleaned out?
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE
This pool does not use LRU and will fill up.
You cannot restore a database with a complete collection of these from your last full backup.
These files contain information about how your running database is configured.

18. How often should you switch log files
These all start with 'ora_'
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.
This grants users access to the database.
You should switch these every 15-30 minutes

19. What is the database writer process?
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.
Background process that write database buffer cache to disk
This process resolves deadlock situations
Memory used by sessions connecting to the database are not controlled by this parameter.

20. What does process LGWR do?
This process flushes the redo log buffer. Writes redo entries to disk and signals a completion.
Each concurrent database request gets a server process.
This processs provides a time reference within the database.
DBA_PRIV_AUDIT_OPTS is a view that does what?

21. What physical structure does a tablespace correspond to?
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;
What logical structure do data files correspond to?
This process flushes the redo log buffer. Writes redo entries to disk and signals a completion.

22. What is the library cache?
This is a library of ready to go SQL Statements.
This is the physical component or the files
This cleans up failed processes by releasing resources and rolling back uncomitted data.
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.

23. What naming convention should you use for your SPFILE?
This process runs the job given to it by the CJQ0 process
This file should be named spfile<dbname>.ora
This is the minimum amount of storage that Oracle reads or writes.
This forces a user to follow guidelines when creating or changing passwords.

24. What does the WITH ADMIN OPTION do?
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;
Allows a user to grant a system privilege to another user.
This is written in PL/SQL and examines passwords when they're chosen and accepts or rejects them based on criteria.
This process takes dirty blocks from dirty list and writes them to disk.

25. What is a database block?
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.
This is the minimum amount of storage that Oracle reads or writes.
SYSTEM - SYSAUX - UNDO - TEMP
ORACLE's work area for executing SQL

26. How do you create an OS-Authenticated user?
Create user OPS$REPORTS identified externally;
What is OPS$
These all start with 'ora_'
DBA_PRIV_AUDIT_OPTS is a view that does what?

27. The SGA is shared by what processes?
This process runs the job given to it by the CJQ0 process
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;
Oracle background processes and server processes share this.
You must have at least 2 of the log file groups.

28. What is the large pool?
This file should be named spfile<dbname>.ora
This pool does not use LRU and will fill up.
This relieves the shared pool of sometimes-transient memory requirements.
This tablespace stores the core database objects that are used for running the database itself

29. What SQL gives a user the ability to grant object privileges to another user?
Grant SELECT on <table> to <user> WITH GRANT OPTION;
This file should be named init<dbname>.ora
Whenever successful - whenever not successful - by access - by session
Background processes - Server Processes - User Processes are part of what?

30. What is a change vector?
SYSDBA is this kind of role.
This is a modification applied to something; executing DML generates these applied to data.
These connections always get a dedicated server process.
Tables - Indexes - Materialized Views - Partitions

31. How do you assign a password profile to a user
This file should be named init<dbname>.ora
DBA_PRIV_AUDIT_OPTS is a view that does what?
Alter user HR profile report_writer;
This is the minimum amount of storage that Oracle reads or writes.

32. What is stored in the library cache
Block in the database buffer cache does not match what is on disk.
Revoke create session from <user>;
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.
This tablespace stores the core database objects that are used for running the database itself

33. What triggers a block write and therefore a dirty block?
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.
Oracle background processes and server processes share this.
Database shutdown - full or partial checkpoint - recovery time threshold - free block is needed - some DDL commands - every three seconds
This forces a user to follow guidelines when creating or changing passwords.

34. What can you configure in the parameter files?
Grant SELECT on <table> to <user> WITH GRANT OPTION;
This contains information used for private or session-related information that individual users need
Memory Size - Database and Instance Name - Archiving parameters - Processes - Over 1900 other parameters
Recognizes a user as logged into the OS and waives the password requirement.

35. How many databases can an instance mount to?
This can only mount to one database
This processs provides a time reference within the database.
Database Buffer Cache - Log Buffer - Shared Pool
This grants users access to the database.

36. List the kinds of files used to run an Oracle Instance?
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.
This logical structure is the minimum unit that Oracle will read or write at any given time.
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files
Grant create session to <user>;

37. What SQL allows you to create role and grant privileges to a user
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;
This controls how a user can access actual data in the database. Primarily applies to rows in a table or a view.
This process is primarily responsible for instance recovery. Applies redo information. Cleans and releases temporary space
The parameter file is read at what point during database startup?

38. What are the PFILE and SPFILE?
These files contain information about how your running database is configured.
Allows a user to grant an object privilege to another user.
This tablespace is used for temporary storage.
What is OPS$

39. What kinds of objects are not segments -but are pieces of code in the SYSTEM tablespace.
Views - Procedures - Synonyms - Sequences
These all start with 'ora_'
Each concurrent database request gets a server process.
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.

40. What do control files contain?
This is a library of ready to go SQL Statements.
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 logwriter (LGWR) writes to these files.
Whenever successful - whenever not successful - by access - by session

41. What is a buffer cache hit?
DBA_PRIV_AUDIT_OPTS is a view that does what?
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;
Data requested by user is found in the database buffer cache. No need to read from disk.
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.

42. What is the TEMP tablespace used for?
This process copies filled redo logs to the archived redo storage area. If you aren't in archive mode - this process shuts down.
This tablespace is used for temporary storage.
LGWR starts writing to the next group - A Database checkpoint occurs - The DBWR writes dirty blocks our of the buffer cascade
This is a special type of database file holding only information for sorts or temporary tables.

43. What SQL lets you see where SYSDBA connections audits are stored?
V$TEMPFILE and V$DATAFILE contain this information?
SYSTEM - SYSAUX - UNDO - TEMP
Show parameter audit_file_dest;
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files

44. What is the PL/SQL function result cache?
These are usually configured 3 groups with 2 mirrored members in each.
Large Pool - Java Pool - Streams Pool
This stores the results of a computation.
This is a special type of database file holding only information for sorts or temporary tables.

45. What are redo log files?
These files store information from the log buffer.
V$TEMPFILE and V$DATAFILE contain this information?
V$MEMORY_TARGET_ADVICE
This is the logical structure that makes up segments.

46. What is the first level of logical organization of your physical storage?
This tablespace stored objects that are auxiliary and not specifically tied to the core features of the database
Audit create table;
The tablespace is what level of logical organization?
This processs provides a time reference within the database.

47. How many datafiles does a tablespace organize?
What is OPS$
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.
This logical structure organizes one or more data files.
SYSDBA is this kind of role.

48. How should log files be configured?
These are usually configured 3 groups with 2 mirrored members in each.
This process takes dirty blocks from dirty list and writes them to disk.
Block in the database buffer cache does not match what is on disk.
LGWR starts writing to the next group - A Database checkpoint occurs - The DBWR writes dirty blocks our of the buffer cascade

49. What is a system privilege?
This controls what a user can do in the database. Primarily applies to adding or changing structures in the database.
You must have at least 2 of the log file groups.
These are the PFILE and the SPFILE
ORACLE's work area for executing SQL

50. What does parameter MEMORY_TARGET do?
Grant create session to <user>;
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.
This is written in PL/SQL and examines passwords when they're chosen and accepts or rejects them based on criteria.
This process resolves deadlock situations