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. How do you create an OS-Authenticated user?
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.
Each concurrent database request gets a server process.
Create user OPS$REPORTS identified externally;
This cache is allocated at instance startup

2. How does select use Database Buffer Cache
This process runs the job given to it by the CJQ0 process
Data blocks copied into database buffer cache - relevant rows copied into user session PGA - blocks stay in cache until buffer needed for something else.
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 flushes the redo log buffer. Writes redo entries to disk and signals a completion.

3. What does the WITH ADMIN OPTION do?
These files store information from the log buffer.
Allows a user to grant a system privilege to another user.
Grant SELECT on <table> to <user> WITH GRANT OPTION;
This is the logical structure that makes up segments.

4. What does the CREATE SESSION privilege do?
This grants users access to the database.
Grant create session to <user>;
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.
This cache is allocated at instance startup

5. What SQL Would let you audit people creating tables?
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_PRIV_AUDIT_OPTS is a view that does what?
This tablespace is used for temporary storage.
Audit create table;

6. What happens during a log switch operation? What causes the following: LGWR finishes writing to the current group
This process runs the job given to it by the CJQ0 process
LGWR starts writing to the next group - A Database checkpoint occurs - The DBWR writes dirty blocks our of the buffer cascade
...
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.

7. What information is NOT stored in redo logs
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;
SELECT statement information are not stored in these files?
Allows a user to grant an object privilege to another user.
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

8. What is the SQL to grant an object privilege?
This stores the results of a computation.
This cache is allocated at instance startup
These files store information from the log buffer.
Grant SELECT on <table> to <user>;

9. What are the 3 processes that are part of an instance?
AUDIT - NOAUDIT
Background processes - Server Processes - User Processes are part of what?
The logwriter (LGWR) writes to these files.
This is the minimum amount of storage that Oracle reads or writes.

10. What is the minimum number of redo log groups you must have?
What logical structure do data files correspond to?
This tablespace stored objects that are auxiliary and not specifically tied to the core features of the database
You must have at least 2 of the log file groups.
This is a modification applied to something; executing DML generates these applied to data.

11. What kinds of server processes are there?
This process can be dedicated or shared
This file should be named spfile<dbname>.ora
This cleans up failed processes by releasing resources and rolling back uncomitted data.
These files store information from the log buffer.

12. What is a change vector?
Oracle background processes and server processes share this.
This process runs the job given to it by the CJQ0 process
This tablespace stores the core database objects that are used for running the database itself
This is a modification applied to something; executing DML generates these applied to data.

13. What happens if you are missing an archive log?
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE
ORACLE's work area for executing SQL
You cannot restore a database with a complete collection of these from your last full backup.
This forces a user to follow guidelines when creating or changing passwords.

14. What options can you add to audits?
This tablespace is used for temporary storage.
Whenever successful - whenever not successful - by access - by session
SYSDBA is this kind of role.
This is the concept that adding and removing privileges from a role immediately affect all users who have the role.

15. How often should you switch log files
This stores the results of a computation.
SYSTEM - SYSAUX - UNDO - TEMP
This process can be dedicated or shared
You should switch these every 15-30 minutes

16. Which process writes to the Redo Log Files?
The logwriter (LGWR) writes to these files.
If you have a full backup and copies of these you can recover from almost any type of failure.
This is a library of ready to go SQL Statements.
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.

17. List the kinds of files used to run an Oracle Instance?
Grant create session to <user>;
What goes into the table SYS.AUD$
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files
This process copies filled redo logs to the archived redo storage area. If you aren't in archive mode - this process shuts down.

18. What uses the large pool?
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.
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE
These use which pool? Oracle Recovery Manager - Oracle Shared Server - Parallel processing - I/O-related server processes.

19. What is a tempfile?
This is the logical structure that makes up segments.
These all start with 'ora_'
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 special type of database file holding only information for sorts or temporary tables.

20. What physical structure does a tablespace correspond to?
These files store information from the log buffer.
This process copies filled redo logs to the archived redo storage area. If you aren't in archive mode - this process shuts down.
This is a library of ready to go SQL Statements.
What logical structure do data files correspond to?

21. What is the Database Buffer Cache

22. What are redo log files?
This logical structure organizes one or more data files.
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files
These files store information from the log buffer.
Row level security is this kind of database.

23. What SQL revokes user access to the database?
This is the physical component or the files
Revoke create session from <user>;
These all start with 'ora_'
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

24. What does the Program Global Area (PGA) contain?
This file should be named spfile<dbname>.ora
Recognizes a user as logged into the OS and waives the password requirement.
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
SELECT statement information are not stored in these files?

25. What is an Extent?
SYSTEM - SYSAUX - UNDO - TEMP
Recognizes a user as logged into the OS and waives the password requirement.
This is the logical structure that makes up segments.
When are connections with SYSDBA privilege audited?

26. What is an OS Block?
This file should be named init<dbname>.ora
This logical structure is the minimum unit that Oracle will read or write at any given time.
What goes into the table SYS.AUD$
This physical structure is the formatted size of the minimum unit of storage on the device

27. The SGA is shared by what processes?
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE
Oracle background processes and server processes share this.
This cleans up failed processes by releasing resources and rolling back uncomitted data.
Alter user HR profile report_writer;

28. What is the PL/SQL function result cache?
This stores the results of a computation.
This is the physical component or the files
Oracle background processes and server processes share this.
These files contain information about how your running database is configured.

29. What views contain information about your DB Files?
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;
V$TEMPFILE and V$DATAFILE contain this information?
Row level security is this kind of database.
This process is primarily responsible for instance recovery. Applies redo information. Cleans and releases temporary space

30. What is the large pool?
This forces a user to follow guidelines when creating or changing passwords.
When are connections with SYSDBA privilege audited?
This relieves the shared pool of sometimes-transient memory requirements.
SELECT statement information are not stored in these files?

31. What is Operating System Authentication?
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.
The tablespace is what level of logical organization?
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;
Recognizes a user as logged into the OS and waives the password requirement.

32. What SQL allows a user access to the database?
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;
What is OPS$
Oracle background processes and server processes share this.
Grant create session to <user>;

33. What does process J000 do?
This processs provides a time reference within the database.
This process runs the job given to it by the CJQ0 process
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.
Grant SELECT on <table> to <user>;

34. What is a segment
This is a logical structure that represents objects in the database that require physical storage
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.
Oracle background processes and server processes share this.
The parameter file is read at what point during database startup?

35. What is the library cache?
This is written in PL/SQL and examines passwords when they're chosen and accepts or rejects them based on criteria.
This stores the results of a computation.
This physical structure is the formatted size of the minimum unit of storage on the device
This is a library of ready to go SQL Statements.

36. What is the highest privileged role?
DBA_STMT_AUDIT_OPTS is a view that does what?
This contains information used for private or session-related information that individual users need
SYSDBA is this kind of role.
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.

37. What kind of objects can be in a segment
This relieves the shared pool of sometimes-transient memory requirements.
Tables - Indexes - Materialized Views - Partitions
This controls how a user can access actual data in the database. Primarily applies to rows in a table or a view.
Database shutdown - full or partial checkpoint - recovery time threshold - free block is needed - some DDL commands - every three seconds

38. What is an instance?
This is a combination of memory and processes that are part of a running installation
This is a special type of database file holding only information for sorts or temporary tables.
What is OPS$
These are usually configured 3 groups with 2 mirrored members in each.

39. What does process DIA0 do?
Audit create table;
This process checks for scheduled tasks within the database.
These connections always get a dedicated server process.
This process resolves deadlock situations

40. Which view helps you size memory properly
Memory Size - Database and Instance Name - Archiving parameters - Processes - Over 1900 other parameters
You must have at least 2 of the log file groups.
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.
V$MEMORY_TARGET_ADVICE

41. What does the server process do?
AUDIT_TRAIL is set to DB
Background process that write database buffer cache to disk
This process is primarily responsible for instance recovery. Applies redo information. Cleans and releases temporary space
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.

42. What does process CKPT do?
This forces a user to follow guidelines when creating or changing passwords.
These use which pool? Oracle Recovery Manager - Oracle Shared Server - Parallel processing - I/O-related server processes.
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.
Block in the database buffer cache does not match what is on disk.

43. What are Archive log files?
The tablespace is what level of logical organization?
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.
These are copies of redo log files
This is a logical structure that represents objects in the database that require physical storage

44. What does process CJQ0 do?
This process checks for scheduled tasks within the database.
Grant SELECT on <table> to <user>;
Allows a user to grant a system privilege to another user.
When are connections with SYSDBA privilege audited?

45. How should log files be configured?
When are connections with SYSDBA privilege audited?
SELECT statement information are not stored in these files?
These are usually configured 3 groups with 2 mirrored members in each.
LGWR starts writing to the next group - A Database checkpoint occurs - The DBWR writes dirty blocks our of the buffer cascade

46. What prefix must precede the OS username for an OS-Authenticated user?
What is OPS$
This is written in PL/SQL and examines passwords when they're chosen and accepts or rejects them based on criteria.
You cannot restore a database with a complete collection of these from your last full backup.
This contains the information used for backup and recovery - free space - file status details.

47. What is the dictionary cache?

48. What view lets you look at audits of system privileges
Grant SELECT on <table> to <user>;
DBA_STMT_AUDIT_OPTS is a view that does what?
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.
This relieves the shared pool of sometimes-transient memory requirements.

49. What is the first level of logical organization of your physical storage?
What logical structure do data files correspond to?
This is the physical component or the files
The tablespace is what level of logical organization?
This is a logical structure that represents objects in the database that require physical storage

50. What does process LGWR do?
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 is the physical component or the files
If you have a full backup and copies of these you can recover from almost any type of failure.