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 a password profile
This is a logical structure that represents objects in the database that require physical storage
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.
Grant create session to <user> with admin option;
This forces a user to follow guidelines when creating or changing passwords.

2. When is database buffer cache allocated?
This process can be dedicated or shared
This grants users access to the database.
Recognizes a user as logged into the OS and waives the password requirement.
This cache is allocated at instance startup

3. 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.
Whenever successful - whenever not successful - by access - by session
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;
This contains information used for private or session-related information that individual users need

4. What are the PFILE and SPFILE?
This pool does not use LRU and will fill up.
These connections always get a dedicated server process.
This file should be named init<dbname>.ora
These files contain information about how your running database is configured.

5. What is the highest privileged role?
Oracle background processes and server processes share this.
This lets you encrypt data in your database files
SYSDBA is this kind of role.
This relieves the shared pool of sometimes-transient memory requirements.

6. What is the SYSTEM tablespace used for?
This tablespace stores the core database objects that are used for running the database itself
If you have a full backup and copies of these you can recover from almost any type of failure.
Tables - Indexes - Materialized Views - Partitions
Allows you to group privileges for ease of management

7. How does select use Database Buffer Cache
This relieves the shared pool of sometimes-transient memory requirements.
Oracle background processes and server processes share this.
The tablespace is what level of logical organization?
Data blocks copied into database buffer cache - relevant rows copied into user session PGA - blocks stay in cache until buffer needed for something else.

8. What does parameter MEMORY_TARGET do?
Free - Pinned - Dirty are what?
Memory used by sessions connecting to the database are not controlled by this parameter.
Allows a user to grant an object privilege to another user.
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.

9. What is the SQL to grant an object privilege?
Grant SELECT on <table> to <user>;
This process copies filled redo logs to the archived redo storage area. If you aren't in archive mode - this process shuts down.
SELECT statement information are not stored in these files?
This process checks for scheduled tasks within the database.

10. What SQL allows a user access to the database?
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;
Data requested by user is found in the database buffer cache. No need to read from disk.
Grant create session to <user>;
What goes into the table SYS.AUD$

11. What kinds of blocks do data files have?
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.
This logical structure is the minimum unit that Oracle will read or write at any given time.
Create user OPS$REPORTS identified externally;
What logical structure do data files correspond to?

12. When does the large pool get cleaned out?
Audit create table;
This process flushes the redo log buffer. Writes redo entries to disk and signals a completion.
This process resolves deadlock situations
This pool does not use LRU and will fill up.

13. What does process DIA0 do?
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.
This is the physical component or the files
This process resolves deadlock situations
The tablespace is what level of logical organization?

14. Which SGA Structures are optional
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;
What logical structure do data files correspond to?
Large Pool - Java Pool - Streams Pool
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.

15. How do you assign a password profile to a user
Alter user HR profile report_writer;
Grant create session to <user>;
This contains information used for private or session-related information that individual users need
This lets you encrypt data in your database files

16. How should log files be configured?
This process runs the job given to it by the CJQ0 process
This file should be named spfile<dbname>.ora
DBA_STMT_AUDIT_OPTS is a view that does what?
These are usually configured 3 groups with 2 mirrored members in each.

17. What do archive log and redo logs allow you to do?
This lets you encrypt data in your database 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.
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.
If you have a full backup and copies of these you can recover from almost any type of failure.

18. What is the dictionary cache?

Warning: Invalid argument supplied for foreach() in /var/www/html/basicversity.com/show_quiz.php on line 183

19. What is the PL/SQL function result cache?
This lets you encrypt data in your database files
This is the physical component or the files
These files contain information about how your running database is configured.
This stores the results of a computation.

20. What is an instance?
This file should be named spfile<dbname>.ora
This is a combination of memory and processes that are part of a running installation
This is a library of ready to go SQL Statements.
This pool does not use LRU and will fill up.

21. What is a buffer cache hit?
This stores the results of a computation.
Memory used by sessions connecting to the database are not controlled by this parameter.
Data requested by user is found in the database buffer cache. No need to read from disk.
This lets you encrypt data in your database files

22. What is a database
You should switch these every 15-30 minutes
The tablespace is what level of logical organization?
These connections always get a dedicated server process.
This is the physical component or the files

23. Which process writes to the Redo Log Files?
This pool does not use LRU and will fill up.
Large Pool - Java Pool - Streams Pool
The logwriter (LGWR) writes to these files.
Memory used by sessions connecting to the database are not controlled by this parameter.

24. What is the first level of logical organization of your physical storage?
Row level security is this kind of database.
SYSTEM - SYSAUX - UNDO - TEMP
The tablespace is what level of logical organization?
These connections always get a dedicated server process.

25. What is stored in the library cache
This tablespace is used for temporary storage.
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.
Users - Objects - System Privileges - Combination are things you can?
What logical structure do data files correspond to?

26. What SQL revokes user access to the database?
Data blocks copied into database buffer cache - relevant rows copied into user session PGA - blocks stay in cache until buffer needed for something else.
You cannot restore a database with a complete collection of these from your last full backup.
Revoke create session from <user>;
V$TEMPFILE and V$DATAFILE contain this information?

27. What view lets you look at audits of objects?
The first extent is created when?
Oracle background processes and server processes share this.
DBA_OBJ_AUDIT_OPTS is a view that does what?
This contains the information used for backup and recovery - free space - file status details.

28. What tablespaces should every database have?
Alter user HR profile report_writer;
This tablespace stores the core database objects that are used for running the database itself
SYSTEM - SYSAUX - UNDO - TEMP
This process checks for scheduled tasks within the database.

29. What does the Program Global Area (PGA) contain?
Each concurrent database request gets a server process.
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.
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
What goes into the table SYS.AUD$

30. What happens if you are missing an archive log?
Show parameter audit_file_dest;
You cannot restore a database with a complete collection of these from your last full backup.
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.
This process checks for scheduled tasks within the database.

31. What naming convention should you use for your SPFILE?
This process takes dirty blocks from dirty list and writes them to disk.
This file should be named spfile<dbname>.ora
What is OPS$
LGWR starts writing to the next group - A Database checkpoint occurs - The DBWR writes dirty blocks our of the buffer cascade

32. What SQL lets you see where SYSDBA connections audits are stored?
Grant SELECT on <table> to <user> WITH GRANT OPTION;
Show parameter audit_file_dest;
This is a combination of memory and processes that are part of a running installation
AUDIT_TRAIL is set to DB

33. What is dynamic privilege management?
This is the concept that adding and removing privileges from a role immediately affect all users who have the role.
These use which pool? Oracle Recovery Manager - Oracle Shared Server - Parallel processing - I/O-related server processes.
This contains the information used for backup and recovery - free space - file status details.
The logwriter (LGWR) writes to these files.

34. What uses the large pool?
This controls what a user can do in the database. Primarily applies to adding or changing structures in the database.
This processs provides a time reference within the database.
Create user OPS$REPORTS identified externally;
These use which pool? Oracle Recovery Manager - Oracle Shared Server - Parallel processing - I/O-related server processes.

35. How do you create an OS-Authenticated user?
This process resolves deadlock situations
This process runs the job given to it by the CJQ0 process
Create user OPS$REPORTS identified externally;
Audit create table;

36. This logical structure gets created when you first create an object such as a table?
This is the logical structure that makes up segments.
This is a logical structure that represents objects in the database that require physical storage
Revoke create session from <user>;
The first extent is created when?

37. List the kinds of files used to run an Oracle Instance?
This cleans up failed processes by releasing resources and rolling back uncomitted data.
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files
This controls how a user can access actual data in the database. Primarily applies to rows in a table or a view.
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.

38. When is the redo log buffer flushed?
DBA_OBJ_AUDIT_OPTS is a view that does what?
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.
The first extent is created when?
You cannot restore a database with a complete collection of these from your last full backup.

39. What is a system privilege?
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 is a modification applied to something; executing DML generates these applied to data.
Allows you to group privileges for ease of management

40. What is the TEMP tablespace used for?
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE
This tablespace is used for temporary storage.
This is a modification applied to something; executing DML generates these applied to data.
This forces a user to follow guidelines when creating or changing passwords.

41. What are the 3 buffer cache states?
This process flushes the redo log buffer. Writes redo entries to disk and signals a completion.
Alter user HR profile report_writer;
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.
Free - Pinned - Dirty are what?

42. What does the server process do?
Block in the database buffer cache does not match what is on disk.
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 controls what a user can do in the database. Primarily applies to adding or changing structures in the database.
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.

43. The SGA is shared by what processes?
Oracle background processes and server processes share this.
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.
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.
The tablespace is what level of logical organization?

44. What is a database block?
This pool does not use LRU and will fill up.
This is the minimum amount of storage that Oracle reads or writes.
This is the physical component or the files
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;

45. What does process J000 do?
Memory Size - Database and Instance Name - Archiving parameters - Processes - Over 1900 other parameters
ORACLE's work area for executing SQL
This process runs the job given to it by the CJQ0 process
DBA_OBJ_AUDIT_OPTS is a view that does what?

46. How many databases can an instance mount to?
These files store information from the log buffer.
What logical structure do data files correspond to?
This process resolves deadlock situations
This can only mount to one database

47. What physical structure does a tablespace correspond to?
What logical structure do data files correspond to?
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.
You cannot restore a database with a complete collection of these from your last full backup.

48. What is the first file read when a database starts?
Background processes - Server Processes - User Processes are part of what?
You must have at least 2 of the log file groups.
Each concurrent database request gets a server process.
The parameter file is read at what point during database startup?

49. What is Virtual Private Database?
This is the logical structure that makes up segments.
Row level security is this kind of database.
You should switch these every 15-30 minutes
Revoke create session from <user>;

50. What does process CKPT do?
This is the logical structure that makes up segments.
These are copies of redo log files
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 pool does not use LRU and will fill up.