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 the PL/SQL function result cache?
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 minimum amount of storage that Oracle reads or writes.
This stores the results of a computation.
AUDIT_TRAIL is set to DB

2. What is stored in the library cache
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.
This process flushes the redo log buffer. Writes redo entries to disk and signals a completion.
This tablespace stores the rollback or undo segments used for transaction recovery
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

3. What does process DIA0 do?
This forces a user to follow guidelines when creating or changing passwords.
This process resolves deadlock situations
This relieves the shared pool of sometimes-transient memory requirements.
Allows a user to grant an object privilege to another user.

4. What prefix must precede the OS username for an OS-Authenticated user?
What is OPS$
Background process that write database buffer cache to disk
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.
You must have at least 2 of the log file groups.

5. Which view helps you size memory properly
Grant SELECT on <table> to <user>;
V$MEMORY_TARGET_ADVICE
This controls what a user can do in the database. Primarily applies to adding or changing structures in the database.
Background processes - Server Processes - User Processes are part of what?

6. What does parameter MEMORY_TARGET not control?
Whenever successful - whenever not successful - by access - by session
Memory used by sessions connecting to the database are not controlled by this parameter.
SYSTEM - SYSAUX - UNDO - TEMP
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.

7. What does parameter MEMORY_TARGET do?
These are usually configured 3 groups with 2 mirrored members in each.
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.
Grant SELECT on <table> to <user>;

8. What does process SMON do
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 is primarily responsible for instance recovery. Applies redo information. Cleans and releases temporary space
SELECT statement information are not stored in these files?
Users - Objects - System Privileges - Combination are things you can?

9. Which SGA Structures are optional
This process flushes the redo log buffer. Writes redo entries to disk and signals a completion.
Oracle background processes and server processes share this.
V$TEMPFILE and V$DATAFILE contain this information?
Large Pool - Java Pool - Streams Pool

10. What happens during a log switch operation? What causes the following: LGWR finishes writing to the current group
This is the minimum amount of storage that Oracle reads or writes.
LGWR starts writing to the next group - A Database checkpoint occurs - The DBWR writes dirty blocks our of the buffer cascade
Views - Procedures - Synonyms - Sequences
This grants users access to the database.

11. What SQL revokes user access to the database?
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 relieves the shared pool of sometimes-transient memory requirements.
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.
Revoke create session from <user>;

12. What is an instance?
This is a combination of memory and processes that are part of a running installation
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE
This is the physical component or the files
Tables - Indexes - Materialized Views - Partitions

13. What is Advanced Security Option
Allows you to group privileges for ease of management
This lets you encrypt data in your database files
These all start with 'ora_'
This controls what a user can do in the database. Primarily applies to adding or changing structures in the database.

14. What is the Database Buffer Cache

15. Where does auditing data go by default
This is the first part of a data file.
...
This contains information used for private or session-related information that individual users need
What goes into the table SYS.AUD$

16. What is an object privilege?
This stores the results of a computation.
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 small - short-term staging area for change vectors before they are written to redo logs on disk.
These files are stored in ORACLE_HOME: $ORACLE_HOME/dbs - %ORACLE_HOMEdatabase

17. What SQL gives a user the ability to grant object privileges to another user?
Grant SELECT on <table> to <user> WITH GRANT OPTION;
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE
...
These all start with 'ora_'

18. What is a password profile
This file should be named spfile<dbname>.ora
You must have at least 2 of the log file groups.
This forces a user to follow guidelines when creating or changing passwords.
Free - Pinned - Dirty are what?

19. What are redo log files?
These files store information from the log buffer.
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 physical structure is the formatted size of the minimum unit of storage on the device
This pool does not use LRU and will fill up.

20. Where are the PFILE and SPFILE stored?
Allows you to group privileges for ease of management
This physical structure is the formatted size of the minimum unit of storage on the device
These files are stored in ORACLE_HOME: $ORACLE_HOME/dbs - %ORACLE_HOMEdatabase
This is the physical component or the files

21. What is a database
...
You should switch these every 15-30 minutes
This is the physical component or the files
Alter user HR profile report_writer;

22. What does process J000 do?
The parameter file is read at what point during database startup?
Create user OPS$REPORTS identified externally;
Users - Objects - System Privileges - Combination are things you can?
This process runs the job given to it by the CJQ0 process

23. What does the WITH ADMIN OPTION do?
Allows a user to grant a system privilege to another user.
Grant create session to <user> with admin option;
These are usually configured 3 groups with 2 mirrored members in each.
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;

24. What does process CKPT do?
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.
The first extent is created when?
...
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

25. The SGA is shared by what processes?
This is the concept that adding and removing privileges from a role immediately affect all users who have the role.
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.
Oracle background processes and server processes share this.
Data blocks copied into database buffer cache - relevant rows copied into user session PGA - blocks stay in cache until buffer needed for something else.

26. What kinds of blocks do data files have?
...
DBA_OBJ_AUDIT_OPTS is a view that does what?
This is a modification applied to something; executing DML generates these applied to data.
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.

27. What information is NOT stored in redo logs
This is the first part of a data file.
Background process that write database buffer cache to disk
SELECT statement information are not stored in these files?
This is a modification applied to something; executing DML generates these applied to data.

28. What are the 3 processes that are part of an instance?
This is a logical structure that represents objects in the database that require physical storage
Recognizes a user as logged into the OS and waives the password requirement.
This tablespace stores the core database objects that are used for running the database itself
Background processes - Server Processes - User Processes are part of what?

29. What does process DBWn do?
What logical structure do data files correspond to?
This process takes dirty blocks from dirty list and writes them to disk.
Row level security is this kind of database.
SYSDBA is this kind of role.

30. What is the TEMP tablespace used for?
Create user OPS$REPORTS identified externally;
This tablespace is used for temporary storage.
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.

31. What tablespaces should every database have?
Block in the database buffer cache does not match what is on disk.
This process runs the job given to it by the CJQ0 process
This process can be dedicated or shared
SYSTEM - SYSAUX - UNDO - TEMP

32. How does a dedicated server process work?
This grants users access to the database.
With this server process each user process gets its own server process. Server process waits on you. Most responsive.
Revoke create session from <user>;
DBA_STMT_AUDIT_OPTS is a view that does what?

33. How do you assign a password profile to a user
Grant create session to <user> with admin option;
This is the minimum amount of storage that Oracle reads or writes.
AUDIT_TRAIL is set to DB
Alter user HR profile report_writer;

34. What does the CREATE SESSION privilege do?
Large Pool - Java Pool - Streams Pool
This grants users access to the database.
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.

35. What options do you have for auditing?
AUDIT_TRAIL is set to DB
Revoke create session from <user>;
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.
Users - Objects - System Privileges - Combination are things you can?

36. What is the Log Buffer
These all start with 'ora_'
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.
These are usually configured 3 groups with 2 mirrored members in each.
This grants users access to the database.

37. What is the dictionary cache?

38. How does select use Database Buffer Cache
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;
Large Pool - Java Pool - Streams Pool
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 tablespace stores the rollback or undo segments used for transaction recovery

39. 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.
Background process that write database buffer cache to disk
Allows a user to grant an object privilege to another 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.

40. When is database buffer cache allocated?
This process copies filled redo logs to the archived redo storage area. If you aren't in archive mode - this process shuts down.
This cleans up failed processes by releasing resources and rolling back uncomitted data.
This cache is allocated at instance startup
These use which pool? Oracle Recovery Manager - Oracle Shared Server - Parallel processing - I/O-related server processes.

41. What are the 3 buffer cache states?
Free - Pinned - Dirty are what?
This is the minimum amount of storage that Oracle reads or writes.
This tablespace stored objects that are auxiliary and not specifically tied to the core features of the database
Oracle background processes and server processes share this.

42. What is the Program Global Area -- PGA?
AUDIT_TRAIL is set to DB
This contains information used for private or session-related information that individual users need
DBA_PRIV_AUDIT_OPTS is a view that does what?
This tablespace stored objects that are auxiliary and not specifically tied to the core features of the database

43. What is a password verify function

44. What is a dirty buffer?
...
Block in the database buffer cache does not match what is on disk.
Allows you to group privileges for ease of management
Allows a user to grant a system privilege to another user.

45. Is Auditing turned on by default?
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.
DBA_STMT_AUDIT_OPTS is a view that does what?
AUDIT_TRAIL is set to DB
Alter user HR profile report_writer;

46. What is an Oracle Block?
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 pool does not use LRU and will fill up.
V$MEMORY_TARGET_ADVICE
This logical structure is the minimum unit that Oracle will read or write at any given time.

47. What naming convention should you use for your SPFILE?
This process flushes the redo log buffer. Writes redo entries to disk and signals a completion.
Allows a user to grant a system privilege to another user.
This file should be named spfile<dbname>.ora
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.

48. What view lets you look at audits of privileges
AUDIT_TRAIL is set to DB
DBA_PRIV_AUDIT_OPTS is a view that does what?
Memory Size - Database and Instance Name - Archiving parameters - Processes - Over 1900 other parameters
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.

49. What is the SQL to grant an object privilege?
Grant SELECT on <table> to <user>;
You should switch these every 15-30 minutes
Audit create table;
This pool does not use LRU and will fill up.

50. What characters do all background processes start with?