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 process DIA0 do?
These connections always get a dedicated server process.
Allows a user to grant an object privilege to another user.
This process resolves deadlock situations
This grants users access to the database.

2. What is dynamic privilege management?
V$TEMPFILE and V$DATAFILE contain this information?
This is the concept that adding and removing privileges from a role immediately affect all users who have the role.
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE
This pool does not use LRU and will fill up.

3. What does the Program Global Area (PGA) contain?
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
You should switch these every 15-30 minutes
Grant create session to <user>;
V$MEMORY_TARGET_ADVICE

4. What kind of server process does a DBA connection get?
These connections always get a dedicated server process.
This process flushes the redo log buffer. Writes redo entries to disk and signals a completion.
This logical structure organizes one or more data files.
Oracle background processes and server processes share this.

5. What is the SYSAUX tablespace used for?
Grant create session to <user> with admin option;
This is the first part of a data file.
V$TEMPFILE and V$DATAFILE contain this information?
This tablespace stored objects that are auxiliary and not specifically tied to the core features of the database

6. How many datafiles does a tablespace organize?
Database Buffer Cache - Log Buffer - Shared Pool
This logical structure organizes one or more data files.
Whenever successful - whenever not successful - by access - by session
Background processes - Server Processes - User Processes are part of what?

7. What views contain information about your DB Files?
V$TEMPFILE and V$DATAFILE contain this information?
This process can be dedicated or shared
V$MEMORY_TARGET_ADVICE
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;

8. What information is NOT stored in redo logs
This physical structure is the formatted size of the minimum unit of storage on the device
SELECT statement information are not stored in these files?
You must have at least 2 of the log file groups.
AUDIT - NOAUDIT

9. What is a segment
This stores the results of a computation.
This process flushes the redo log buffer. Writes redo entries to disk and signals a completion.
This is a logical structure that represents objects in the database that require physical storage
...

10. What is an instance?
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;
This forces a user to follow guidelines when creating or changing passwords.
This is a combination of memory and processes that are part of a running installation
Data requested by user is found in the database buffer cache. No need to read from disk.

11. What is the first file read when a database starts?
AUDIT - NOAUDIT
This tablespace stores the rollback or undo segments used for transaction recovery
Allows a user to grant a system privilege to another user.
The parameter file is read at what point during database startup?

12. What is stored in the block header
This process runs the job given to it by the CJQ0 process
Create user OPS$REPORTS identified externally;
This is a logical structure that represents objects in the database that require physical storage
This contains the information used for backup and recovery - free space - file status details.

13. What options does a password profile allow you to have?
This tablespace stored objects that are auxiliary and not specifically tied to the core features of the database
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?
Grant SELECT on <table> to <user>;

14. What does parameter MEMORY_TARGET not control?
This is the logical structure that makes up segments.
Block in the database buffer cache does not match what is on disk.
These all start with 'ora_'
Memory used by sessions connecting to the database are not controlled by this parameter.

15. The SGA is shared by what processes?
ORACLE's work area for executing SQL
These are the PFILE and the SPFILE
Free - Pinned - Dirty are what?
Oracle background processes and server processes share this.

16. What view lets you look at audits of privileges
This controls how a user can access actual data in the database. Primarily applies to rows in a table or a view.
Each concurrent database request gets a server process.
DBA_STMT_AUDIT_OPTS is a view that does what?
DBA_PRIV_AUDIT_OPTS is a view that does what?

17. What is the UNDO tablespace used for?
Background processes - Server Processes - User Processes are part of what?
...
This tablespace stores the rollback or undo segments used for transaction recovery
Allows you to group privileges for ease of management

18. What is a password profile
Views - Procedures - Synonyms - Sequences
This forces a user to follow guidelines when creating or changing passwords.
DBA_OBJ_AUDIT_OPTS is a view that does what?
AUDIT - NOAUDIT

19. What does process LGWR do?
This process flushes the redo log buffer. Writes redo entries to disk and signals a completion.
Memory used by sessions connecting to the database are not controlled by this parameter.
This process resolves deadlock situations
V$TEMPFILE and V$DATAFILE contain this information?

20. What is a dirty buffer?
You should switch these every 15-30 minutes
This process checks for scheduled tasks within the database.
Block in the database buffer cache does not match what is on disk.
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.

21. List the kinds of files used to run an Oracle Instance?
Grant SELECT on <table> to <user>;
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.
This process takes dirty blocks from dirty list and writes them to disk.
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files

22. Is Auditing turned on by default?
What logical structure do data files correspond to?
...
Grant create session to <user> with admin option;
AUDIT_TRAIL is set to DB

23. What do archive log and redo logs allow you to do?
This contains the information used for backup and recovery - free space - file status details.
This process can be dedicated or shared
If you have a full backup and copies of these you can recover from almost any type of failure.
This is a combination of memory and processes that are part of a running installation

24. What are the 3 processes that are part of an instance?
Oracle background processes and server processes share this.
Allows a user to grant an object privilege to another user.
Background processes - Server Processes - User Processes are part of what?
This logical structure is the minimum unit that Oracle will read or write at any given time.

25. What is the Database Buffer Cache

26. What are the 8 object priviliges?
This logical structure is the minimum unit that Oracle will read or write at any given time.
This process checks for scheduled tasks within the database.
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE
This controls how a user can access actual data in the database. Primarily applies to rows in a table or a view.

27. Which process writes to the Redo Log Files?
This can only mount to one database
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;
This is the logical structure that makes up segments.
The logwriter (LGWR) writes to these files.

28. What naming convention should you use for your PFILE
This file should be named init<dbname>.ora
This tablespace stores the core database objects that are used for running the database itself
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.
...

29. What SQL revokes user access to the database?
Alter user HR profile report_writer;
Revoke create session from <user>;
V$MEMORY_TARGET_ADVICE
Audit create table;

30. What is the large pool?
This relieves the shared pool of sometimes-transient memory requirements.
Allows a user to grant a system privilege to another user.
DBA_PRIV_AUDIT_OPTS is a view that does what?
This process resolves deadlock situations

31. What prefix must precede the OS username for an OS-Authenticated user?
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files
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 process copies filled redo logs to the archived redo storage area. If you aren't in archive mode - this process shuts down.
What is OPS$

32. What is a password verify function

33. How often should you switch log files
This tablespace stored objects that are auxiliary and not specifically tied to the core features of the database
If you have a full backup and copies of these you can recover from almost any type of failure.
You should switch these every 15-30 minutes
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.

34. 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.
Allows a user to grant an object privilege to another user.
Whenever successful - whenever not successful - by access - by session
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.

35. 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.
This is the logical structure that makes up segments.
Show parameter audit_file_dest;
Audit create table;

36. How does updating use Database Buffer Cache
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.
DBA_PRIV_AUDIT_OPTS is a view that does what?
Create user OPS$REPORTS identified externally;
This is a logical structure that represents objects in the database that require physical storage

37. What SQL gives a user the ability to grant object privileges to another user?
Database Buffer Cache - Log Buffer - Shared Pool
Background process that write database buffer cache to disk
Grant SELECT on <table> to <user> WITH GRANT OPTION;
This can only mount to one database

38. What are the names of the parameter files?
Oracle background processes and server processes share this.
These files contain information about how your running database is configured.
These are the PFILE and the SPFILE
This processs provides a time reference within the database.

39. What does the CREATE SESSION privilege do?
Block in the database buffer cache does not match what is on disk.
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.
This grants users access to the database.
This relieves the shared pool of sometimes-transient memory requirements.

40. What does the WITH GRANT OPTION do?
With this server process each user process gets its own server process. Server process waits on you. Most responsive.
Allows a user to grant an object privilege to another user.
This tablespace stores the core database objects that are used for running the database itself
SELECT statement information are not stored in these files?

41. What kind of objects can be in a segment
This process resolves deadlock situations
Tables - Indexes - Materialized Views - Partitions
DBA_OBJ_AUDIT_OPTS is a view that does what?
When are connections with SYSDBA privilege audited?

42. What does process ARCn do?

43. When is database buffer cache allocated?
This cache is allocated at instance startup
Oracle background processes and server processes share this.
Large Pool - Java Pool - Streams Pool
This file should be named init<dbname>.ora

44. What actions are always audited
These are the PFILE and the SPFILE
Audit create table;
When are connections with SYSDBA privilege audited?
With this server process each user process gets its own server process. Server process waits on you. Most responsive.

45. What is the SYSTEM tablespace used for?
This tablespace stores the core database objects that are used for running the database itself
You should switch these every 15-30 minutes
This is a library of ready to go SQL Statements.
These are usually configured 3 groups with 2 mirrored members in each.

46. What is an Oracle Block?
Users - Objects - System Privileges - Combination are things you can?
These all start with 'ora_'
This logical structure is the minimum unit that Oracle will read or write at any given time.
These use which pool? Oracle Recovery Manager - Oracle Shared Server - Parallel processing - I/O-related server processes.

47. What happens if you are missing an archive log?
Revoke create session from <user>;
This is the concept that adding and removing privileges from a role immediately affect all users who have the role.
Block in the database buffer cache does not match what is on disk.
You cannot restore a database with a complete collection of these from your last full backup.

48. What are the PFILE and SPFILE?
These use which pool? Oracle Recovery Manager - Oracle Shared Server - Parallel processing - I/O-related server processes.
This controls what a user can do in the database. Primarily applies to adding or changing structures in the database.
These files contain information about how your running database is configured.
These are copies of redo log files

49. What kinds of objects are not segments -but are pieces of code in the SYSTEM tablespace.
This process takes dirty blocks from dirty list and writes them to disk.
This logical structure organizes one or more data files.
Memory used by sessions connecting to the database are not controlled by this parameter.
Views - Procedures - Synonyms - Sequences

50. What does parameter MEMORY_TARGET do?
This cache is allocated at instance startup
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.
What is OPS$
Data requested by user is found in the database buffer cache. No need to read from disk.