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 parameter MEMORY_TARGET do?
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.
Whenever successful - whenever not successful - by access - by session
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.
This is the physical component or the files

2. What is the SYSTEM tablespace used for?
This is the minimum amount of storage that Oracle reads or writes.
This tablespace stores the core database objects that are used for running the database itself
This is the logical structure that makes up segments.
Revoke create session from <user>;

3. What options can you add to audits?
This cleans up failed processes by releasing resources and rolling back uncomitted data.
Whenever successful - whenever not successful - by access - by session
Large Pool - Java Pool - Streams Pool
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE

4. When is the redo log buffer flushed?
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.
V$TEMPFILE and V$DATAFILE contain this information?
These are the PFILE and the SPFILE
These use which pool? Oracle Recovery Manager - Oracle Shared Server - Parallel processing - I/O-related server processes.

5. Which process writes to the Redo Log Files?
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE
Grant SELECT on <table> to <user>;
These files are stored in ORACLE_HOME: $ORACLE_HOME/dbs - %ORACLE_HOMEdatabase
The logwriter (LGWR) writes to these files.

6. What kinds of blocks do data files have?
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.
Grant SELECT on <table> to <user> WITH GRANT OPTION;
Memory Size - Database and Instance Name - Archiving parameters - Processes - Over 1900 other parameters
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.

7. What is the Log Buffer
This file should be named init<dbname>.ora
Row level security is this kind of database.
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 small - short-term staging area for change vectors before they are written to redo logs on disk.

8. What is the first file read when a database starts?
This tablespace stored objects that are auxiliary and not specifically tied to the core features of the database
Database Buffer Cache - Log Buffer - Shared Pool
This controls what a user can do in the database. Primarily applies to adding or changing structures in the database.
The parameter file is read at what point during database startup?

9. What is the PL/SQL function result cache?
Audit create table;
This stores the results of a computation.
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.
Alter user HR profile report_writer;

10. What does process PMON do?
This cleans up failed processes by releasing resources and rolling back uncomitted data.
Each concurrent database request gets a server process.
DBA_PRIV_AUDIT_OPTS is a view that does what?
ORACLE's work area for executing SQL

11. What is a password verify function

12. What is a segment
This is a logical structure that represents objects in the database that require physical storage
These connections always get a dedicated server process.
This stores the results of a computation.
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.

13. What is the library cache?
This is a library of ready to go SQL Statements.
This process is primarily responsible for instance recovery. Applies redo information. Cleans and releases temporary space
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 resolves deadlock situations

14. What physical structure does a tablespace correspond to?
This cache is allocated at instance startup
This lets you encrypt data in your database files
Database Buffer Cache - Log Buffer - Shared Pool
What logical structure do data files correspond to?

15. What is an object privilege?
This logical structure organizes one or more data files.
This is the logical structure that makes up segments.
This controls how a user can access actual data in the database. Primarily applies to rows in a table or a view.
Memory used by sessions connecting to the database are not controlled by this parameter.

16. What is stored in the library cache
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE
This file should be named spfile<dbname>.ora
This tablespace stores the rollback or undo segments used for transaction recovery

17. Which SGA Structures are required
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.
SYSTEM - SYSAUX - UNDO - TEMP
Create user OPS$REPORTS identified externally;
Database Buffer Cache - Log Buffer - Shared Pool

18. What is the SQL to grant an object privilege?
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;
This forces a user to follow guidelines when creating or changing passwords.
These files contain information about how your running database is configured.
Grant SELECT on <table> to <user>;

19. What kind of objects can be in a segment
This process takes dirty blocks from dirty list and writes them to disk.
Row level security is this kind of database.
Tables - Indexes - Materialized Views - Partitions
...

20. What is the large pool?
Grant create session to <user>;
This relieves the shared pool of sometimes-transient memory requirements.
Users - Objects - System Privileges - Combination are things you can?
This is the first part of a data file.

21. What actions are always audited
This file should be named init<dbname>.ora
The logwriter (LGWR) writes to these files.
Data blocks copied into database buffer cache - relevant rows copied into user session PGA - blocks stay in cache until buffer needed for something else.
When are connections with SYSDBA privilege audited?

22. What is the Database Buffer Cache

23. What is a dirty buffer?
DBA_PRIV_AUDIT_OPTS is a view that does what?
This logical structure organizes one or more data files.
Alter user HR profile report_writer;
Block in the database buffer cache does not match what is on disk.

24. What is the UNDO tablespace used for?
This tablespace stores the rollback or undo segments used for transaction recovery
What logical structure do data files correspond to?
Allows you to group privileges for ease of management
DBA_PRIV_AUDIT_OPTS is a view that does what?

25. What are the 8 object priviliges?
This is the concept that adding and removing privileges from a role immediately affect all users who have the role.
When are connections with SYSDBA privilege audited?
This stores the results of a computation.
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE

26. What does the server process do?
This is the physical component or the files
This process copies filled redo logs to the archived redo storage area. If you aren't in archive mode - this process shuts down.
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 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. What is a buffer cache hit?
These are copies of redo log files
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.
These files contain information about how your running database is configured.
Data requested by user is found in the database buffer cache. No need to read from disk.

28. What triggers a block write and therefore a dirty block?
SELECT statement information are not stored in these files?
Database shutdown - full or partial checkpoint - recovery time threshold - free block is needed - some DDL commands - every three seconds
This tablespace is used for temporary storage.
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.

29. What is the SYSAUX tablespace used for?
This process takes dirty blocks from dirty list and writes them to disk.
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 flushes the redo log buffer. Writes redo entries to disk and signals a completion.
This tablespace stored objects that are auxiliary and not specifically tied to the core features of the database

30. How does select use Database Buffer Cache
DBA_STMT_AUDIT_OPTS is a view that does what?
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.
You cannot restore a database with a complete collection of these from your last full backup.

31. What does the WITH GRANT OPTION do?
This process is primarily responsible for instance recovery. Applies redo information. Cleans and releases temporary space
Allows a user to grant an object privilege to another user.
Recognizes a user as logged into the OS and waives the password requirement.
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files

32. What can you configure in the parameter files?
Memory Size - Database and Instance Name - Archiving parameters - Processes - Over 1900 other parameters
What logical structure do data files correspond to?
This tablespace is used for temporary storage.
This is a logical structure that represents objects in the database that require physical storage

33. What view lets you look at audits of objects?
This process is primarily responsible for instance recovery. Applies redo information. Cleans and releases temporary space
These are the PFILE and the SPFILE
SYSTEM - SYSAUX - UNDO - TEMP
DBA_OBJ_AUDIT_OPTS is a view that does what?

34. What tablespaces should every database have?
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files
SYSTEM - SYSAUX - UNDO - TEMP
This physical structure is the formatted size of the minimum unit of storage on the device
Oracle background processes and server processes share this.

35. What does process DIA0 do?
Memory used by sessions connecting to the database are not controlled by this parameter.
...
This process runs the job given to it by the CJQ0 process
This process resolves deadlock situations

36. What is the TEMP tablespace used for?
This tablespace is used for temporary storage.
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.
Allows a user to grant an object privilege to another user.

37. What does a database role do?
Allows you to group privileges for ease of management
This is the first part of a data file.
This is written in PL/SQL and examines passwords when they're chosen and accepts or rejects them based on criteria.
This is a combination of memory and processes that are part of a running installation

38. What is the Program Global Area -- PGA?
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.
The logwriter (LGWR) writes to these files.
This contains information used for private or session-related information that individual users need

39. How do you create an OS-Authenticated 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.
These are the PFILE and the SPFILE
Create user OPS$REPORTS identified externally;
Grant SELECT on <table> to <user>;

40. How should log files be configured?
This process runs the job given to it by the CJQ0 process
This is written in PL/SQL and examines passwords when they're chosen and accepts or rejects them based on criteria.
These are usually configured 3 groups with 2 mirrored members in each.
AUDIT_TRAIL is set to DB

41. List the kinds of files used to run an Oracle Instance?
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files
Memory Size - Database and Instance Name - Archiving parameters - Processes - Over 1900 other parameters
The logwriter (LGWR) writes to these files.
Grant create session to <user>;

42. What is the block header?
This is the first part of a data file.
...
V$MEMORY_TARGET_ADVICE
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;

43. What is the Redo Log Buffer
This process is primarily responsible for instance recovery. Applies redo information. Cleans and releases temporary space
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.
These files store information from the log buffer.

44. What is a tempfile?
Whenever successful - whenever not successful - by access - by session
This is a special type of database file holding only information for sorts or temporary tables.
What is OPS$
AUDIT_TRAIL is set to DB

45. Is Auditing turned on by default?
These are the PFILE and the SPFILE
Data blocks copied into database buffer cache - relevant rows copied into user session PGA - blocks stay in cache until buffer needed for something else.
AUDIT_TRAIL is set to DB
This process checks for scheduled tasks within the database.

46. What is an Oracle Block?
This logical structure is the minimum unit that Oracle will read or write at any given time.
V$TEMPFILE and V$DATAFILE contain this information?
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files
Data blocks copied into database buffer cache - relevant rows copied into user session PGA - blocks stay in cache until buffer needed for something else.

47. What views contain information about your DB Files?
These files store information from the log buffer.
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;
V$TEMPFILE and V$DATAFILE contain this information?
Grant create session to <user> with admin option;

48. How does updating use Database Buffer Cache
The tablespace is what level of logical organization?
This process is primarily responsible for instance recovery. Applies redo information. Cleans and releases temporary space
This stores the results of a computation.
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.

49. How many databases can an instance mount to?
These files are stored in ORACLE_HOME: $ORACLE_HOME/dbs - %ORACLE_HOMEdatabase
Each concurrent database request gets a server process.
This can only mount to one database
Grant create session to <user>;

50. What is an instance?
This is a combination of memory and processes that are part of a running installation
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;
Large Pool - Java Pool - Streams Pool
These are usually configured 3 groups with 2 mirrored members in each.