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?
Data requested by user is found in the database buffer cache. No need to read from disk.
DBA_PRIV_AUDIT_OPTS is a view that does what?
Create user OPS$REPORTS identified externally;
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE

2. What is the large pool?
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.
Row level security is this kind of database.
This relieves the shared pool of sometimes-transient memory requirements.
DBA_OBJ_AUDIT_OPTS is a view that does what?

3. What is the Program Global Area -- PGA?
This is the logical structure that makes up segments.
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files
AUDIT_TRAIL is set to DB
This contains information used for private or session-related information that individual users need

4. What is a password verify function

5. List the kinds of files used to run an Oracle Instance?
When are connections with SYSDBA privilege audited?
These are usually configured 3 groups with 2 mirrored members in each.
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files
This cache is allocated at instance startup

6. What can you configure in the parameter files?
Memory Size - Database and Instance Name - Archiving parameters - Processes - Over 1900 other parameters
Revoke create session from <user>;
This contains the information used for backup and recovery - free space - file status details.
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.

7. What does the server process do?
You cannot restore a database with a complete collection of these from your last full backup.
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.
What goes into the table SYS.AUD$
This is the first part of a data file.

8. how do you turn on or off auditing?
AUDIT - NOAUDIT
ORACLE's work area for executing SQL
The tablespace is what level of logical organization?
This process runs the job given to it by the CJQ0 process

9. What is the Log Buffer
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.
This is written in PL/SQL and examines passwords when they're chosen and accepts or rejects them based on criteria.
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.
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.

10. What naming convention should you use for your SPFILE?
This file should be named spfile<dbname>.ora
These are usually configured 3 groups with 2 mirrored members in each.
SELECT statement information are not stored in these files?
This process checks for scheduled tasks within the database.

11. How does select use Database Buffer Cache
This is the minimum amount of storage that Oracle reads or writes.
V$MEMORY_TARGET_ADVICE
This process can be dedicated or shared
Data blocks copied into database buffer cache - relevant rows copied into user session PGA - blocks stay in cache until buffer needed for something else.

12. What are redo log files?
These files store information from the log buffer.
This file should be named spfile<dbname>.ora
This process checks for scheduled tasks within the database.
Create user OPS$REPORTS identified externally;

13. What is Virtual Private Database?
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.
Database Buffer Cache - Log Buffer - Shared Pool
Row level security is this kind of database.
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.

14. What views contain information about your DB Files?
V$TEMPFILE and V$DATAFILE contain this information?
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
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.
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. What does process J000 do?
Memory Size - Database and Instance Name - Archiving parameters - Processes - Over 1900 other parameters
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.
This can only mount to one database
This process runs the job given to it by the CJQ0 process

16. What is the Database Buffer Cache

17. What SQL gives a user the ability to grant object privileges to another user?
Grant SELECT on <table> to <user> WITH GRANT OPTION;
When are connections with SYSDBA privilege audited?
This process resolves deadlock situations
This process is primarily responsible for instance recovery. Applies redo information. Cleans and releases temporary space

18. What is the TEMP tablespace used for?
This process flushes the redo log buffer. Writes redo entries to disk and signals a completion.
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.
This tablespace is used for temporary storage.
This grants users access to the database.

19. How do you assign a password profile to a user
Alter user HR profile report_writer;
Row level security is this kind of database.
Revoke create session from <user>;
This tablespace stores the rollback or undo segments used for transaction recovery

20. What is the Redo Log Buffer
Revoke create session from <user>;
Large Pool - Java Pool - Streams Pool
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.
Database shutdown - full or partial checkpoint - recovery time threshold - free block is needed - some DDL commands - every three seconds

21. Which view helps you size memory properly
This contains the information used for backup and recovery - free space - file status details.
V$MEMORY_TARGET_ADVICE
This cache is allocated at instance startup
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.

22. What is a database block?
Recognizes a user as logged into the OS and waives the password requirement.
This is the minimum amount of storage that Oracle reads or writes.
Grant SELECT on <table> to <user>;
DBA_STMT_AUDIT_OPTS is a view that does what?

23. Is Auditing turned on by default?
With this server process each user process gets its own server process. Server process waits on you. Most responsive.
SYSDBA is this kind of role.
These are usually configured 3 groups with 2 mirrored members in each.
AUDIT_TRAIL is set to DB

24. What view lets you look at audits of objects?
Memory used by sessions connecting to the database are not controlled by this parameter.
What logical structure do data files correspond to?
Grant SELECT on <table> to <user>;
DBA_OBJ_AUDIT_OPTS is a view that does what?

25. How does a shared server architecture work?
Free - Pinned - Dirty are what?
Each concurrent database request gets a server process.
Grant SELECT on <table> to <user> WITH GRANT OPTION;
This is a combination of memory and processes that are part of a running installation

26. What does parameter MEMORY_TARGET not control?
Views - Procedures - Synonyms - Sequences
Memory used by sessions connecting to the database are not controlled by this parameter.
What goes into the table SYS.AUD$
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

27. What does parameter MEMORY_TARGET do?
This contains information used for private or session-related information that individual users need
Memory Size - Database and Instance Name - Archiving parameters - Processes - Over 1900 other parameters
These use which pool? Oracle Recovery Manager - Oracle Shared Server - Parallel processing - I/O-related server processes.
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.

28. What SQL lets you see where SYSDBA connections audits are stored?
Show parameter audit_file_dest;
Allows a user to grant an object privilege to another user.
This is written in PL/SQL and examines passwords when they're chosen and accepts or rejects them based on criteria.
This process checks for scheduled tasks within the database.

29. What is a segment
This is a collection of information about you and the database's objects.
Background processes - Server Processes - User Processes are part of what?
This is a logical structure that represents objects in the database that require physical storage
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.

30. What information is NOT stored in redo logs
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.
SELECT statement information are not stored in these 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.

31. The SGA is shared by what processes?
Background process that write database buffer cache to disk
Oracle background processes and server processes share this.
Recognizes a user as logged into the OS and waives the password requirement.
Grant create session to <user> with admin option;

32. What is the SQL to grant an object privilege?
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.
The tablespace is what level of logical organization?
Grant SELECT on <table> to <user>;
This cleans up failed processes by releasing resources and rolling back uncomitted data.

33. Which SGA Structures are optional
Large Pool - Java Pool - Streams Pool
Recognizes a user as logged into the OS and waives the password requirement.
This is the minimum amount of storage that Oracle reads or writes.
This relieves the shared pool of sometimes-transient memory requirements.

34. When is database buffer cache allocated?
This process flushes the redo log buffer. Writes redo entries to disk and signals a completion.
This cache is allocated at instance startup
Grant create session to <user> with admin option;
Allows you to group privileges for ease of management

35. How many databases can an instance mount to?
This pool does not use LRU and will fill up.
AUDIT_TRAIL is set to DB
This can only mount to one database
Show parameter audit_file_dest;

36. What is Advanced Security Option
Grant SELECT on <table> to <user> WITH GRANT OPTION;
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
SYSDBA is this kind of role.

37. 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.
This contains the information used for backup and recovery - free space - file status details.
...
This tablespace stores the rollback or undo segments used for transaction recovery

38. What does process DIA0 do?
DBA_OBJ_AUDIT_OPTS is a view that does what?
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.
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.
This process resolves deadlock situations

39. 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
This tablespace stored objects that are auxiliary and not specifically tied to the core features of the database
This tablespace stores the core database objects that are used for running the database itself
This physical structure is the formatted size of the minimum unit of storage on the device

40. What is the SQL for creating a password profile
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;
Whenever successful - whenever not successful - by access - by session
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.
This cleans up failed processes by releasing resources and rolling back uncomitted data.

41. What actions are always audited
This is a library of ready to go SQL Statements.
When are connections with SYSDBA privilege audited?
Alter user HR profile report_writer;
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.

42. What is the library cache?
SELECT statement information are not stored in these files?
Audit create table;
This is a library of ready to go SQL Statements.
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.

43. What SQL revokes user access to the database?
Revoke create session from <user>;
SYSDBA is this kind of role.
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.

44. What is audited by default in Oracle 11g?
Free - Pinned - Dirty are what?
...
AUDIT_TRAIL is set to DB
DBA_OBJ_AUDIT_OPTS is a view that does what?

45. What does process SMON 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.
DBA_PRIV_AUDIT_OPTS is a view that does what?
Data requested by user is found in the database buffer cache. No need to read from disk.
This process is primarily responsible for instance recovery. Applies redo information. Cleans and releases temporary space

46. What is the database writer process?
Background process that write database buffer cache 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.
Views - Procedures - Synonyms - Sequences
You cannot restore a database with a complete collection of these from your last full backup.

47. What kinds of server processes are there?
Show parameter audit_file_dest;
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.
This process can be dedicated or shared
Background processes - Server Processes - User Processes are part of what?

48. What does the CREATE SESSION privilege do?
This pool does not use LRU and will fill up.
Views - Procedures - Synonyms - Sequences
This grants users access to the database.
This file should be named spfile<dbname>.ora

49. What is the block header?
This is the first part of a data file.
This process takes dirty blocks from dirty list and writes them to disk.
Memory Size - Database and Instance Name - Archiving parameters - Processes - Over 1900 other parameters
Allows a user to grant a system privilege to another user.

50. What prefix must precede the OS username for an OS-Authenticated user?
Tables - Indexes - Materialized Views - Partitions
These connections always get a dedicated server process.
This is a special type of database file holding only information for sorts or temporary tables.
What is OPS$