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 view lets you look at audits of privileges
DBA_PRIV_AUDIT_OPTS is a view that does what?
DBA_STMT_AUDIT_OPTS is a view that does what?
Allows a user to grant an object privilege to another user.
These are the PFILE and the SPFILE

2. What view lets you look at audits of objects?
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_OBJ_AUDIT_OPTS is a view that does what?
This is written in PL/SQL and examines passwords when they're chosen and accepts or rejects them based on criteria.
...

3. What is Advanced Security Option
Allows a user to grant an object privilege to another user.
This logical structure organizes one or more data files.
These are the PFILE and the SPFILE
This lets you encrypt data in your database files

4. What is the first level of logical organization of your physical storage?
This forces a user to follow guidelines when creating or changing passwords.
This is the concept that adding and removing privileges from a role immediately affect all users who have the role.
The tablespace is what level of logical organization?
Background processes - Server Processes - User Processes are part of what?

5. What does a database role do?
This pool does not use LRU and will fill up.
Free - Pinned - Dirty are what?
This processs provides a time reference within the database.
Allows you to group privileges for ease of management

6. What is an Oracle Block?
These are the PFILE and the SPFILE
This logical structure is the minimum unit that Oracle will read or write at any given time.
This file should be named spfile<dbname>.ora
Revoke create session from <user>;

7. What is the large pool?
This relieves the shared pool of sometimes-transient memory requirements.
LGWR starts writing to the next group - A Database checkpoint occurs - The DBWR writes dirty blocks our of the buffer cascade
This cache is allocated at instance startup
DBA_OBJ_AUDIT_OPTS is a view that does what?

8. What is an instance?
This is a combination of memory and processes that are part of a running installation
Oracle background processes and server processes share this.
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 cleans up failed processes by releasing resources and rolling back uncomitted data.

9. What are the 3 buffer cache states?
Alter user HR profile report_writer;
This file should be named spfile<dbname>.ora
Free - Pinned - Dirty are what?
Large Pool - Java Pool - Streams Pool

10. What does the WITH GRANT OPTION do?
DBA_OBJ_AUDIT_OPTS is a view that does what?
Users - Objects - System Privileges - Combination are things you can?
Block in the database buffer cache does not match what is on disk.
Allows a user to grant an object privilege to another user.

11. What SQL Would let you audit people creating tables?
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.
LGWR starts writing to the next group - A Database checkpoint occurs - The DBWR writes dirty blocks our of the buffer cascade
Audit create table;
These are copies of redo log files

12. What is a password profile
This forces a user to follow guidelines when creating or changing passwords.
SELECT statement information are not stored in these files?
This processs provides a time reference within the database.
This physical structure is the formatted size of the minimum unit of storage on the device

13. What is the Program Global Area -- PGA?
This controls how a user can access actual data in the database. Primarily applies to rows in a table or a view.
This contains information used for private or session-related information that individual users need
Data requested by user is found in the database buffer cache. No need to read from disk.
V$TEMPFILE and V$DATAFILE contain this information?

14. What information is NOT stored in redo logs
This process can be dedicated or shared
SELECT statement information are not stored in these files?
LGWR starts writing to the next group - A Database checkpoint occurs - The DBWR writes dirty blocks our of the buffer cascade
Grant create session to <user> with admin option;

15. Which process writes to the Redo Log Files?
The logwriter (LGWR) writes to these files.
DBA_STMT_AUDIT_OPTS is a view that does what?
This contains the information used for backup and recovery - free space - file status details.
Whenever successful - whenever not successful - by access - by session

16. What is an OS Block?
Large Pool - Java Pool - Streams Pool
These all start with 'ora_'
This physical structure is the formatted size of the minimum unit of storage on the device
This controls how a user can access actual data in the database. Primarily applies to rows in a table or a view.

17. What is stored in the library cache
This is a library of ready to go SQL Statements.
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.
V$MEMORY_TARGET_ADVICE
AUDIT - NOAUDIT

18. What happens if you are missing an archive log?
Background processes - Server Processes - User Processes are part of what?
You cannot restore a database with a complete collection of these from your last full backup.
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.
This pool does not use LRU and will fill up.

19. What is the library cache?
These are the PFILE and the SPFILE
This is a library of ready to go SQL Statements.
With this server process each user process gets its own server process. Server process waits on you. Most responsive.
The parameter file is read at what point during database startup?

20. What is the database writer process?
Audit create table;
You must have at least 2 of the log file groups.
Allows you to group privileges for ease of management
Background process that write database buffer cache to disk

21. What is the SQL to grant an object privilege?
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.
You must have at least 2 of the log file groups.
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;
Grant SELECT on <table> to <user>;

22. What is the block header?
This processs provides a time reference within the database.
Create user OPS$REPORTS identified externally;
This is the first part of a data file.
SELECT statement information are not stored in these files?

23. What is the Redo Log Buffer
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.
This contains the information used for backup and recovery - free space - file status details.
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.
Row level security is this kind of database.

24. What is a database
The tablespace is what level of logical organization?
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.
Memory used by sessions connecting to the database are not controlled by this parameter.

25. What is stored in the block header
This is a modification applied to something; executing DML generates these applied to data.
These are the PFILE and the SPFILE
This contains the information used for backup and recovery - free space - file status details.
This is the logical structure that makes up segments.

26. What SQL allows a user access to the database?
You should switch these every 15-30 minutes
Grant create session to <user>;
SYSTEM - SYSAUX - UNDO - TEMP
Oracle background processes and server processes share this.

27. What is the dictionary cache?

28. What kind of server process does a DBA connection get?
This tablespace is used for temporary storage.
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.
These files are stored in ORACLE_HOME: $ORACLE_HOME/dbs - %ORACLE_HOMEdatabase
These connections always get a dedicated server process.

29. How does select use Database Buffer Cache
This is the concept that adding and removing privileges from a role immediately affect all users who have the role.
Data blocks copied into database buffer cache - relevant rows copied into user session PGA - blocks stay in cache until buffer needed for something else.
Database Buffer Cache - Log Buffer - Shared Pool
These are the PFILE and the SPFILE

30. What is the Log Buffer
Data requested by user is found in the database buffer cache. No need to read from disk.
This file should be named init<dbname>.ora
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.
This is the logical structure that makes up segments.

31. How does updating use Database Buffer Cache
These files contain information about how your running database is configured.
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 contains information used for private or session-related information that individual users need

32. Which SGA Structures are required
This contains the information used for backup and recovery - free space - file status details.
Database Buffer Cache - Log Buffer - Shared Pool
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.
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.

33. What is a buffer cache hit?
This logical structure organizes one or more data files.
Grant SELECT on <table> to <user>;
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE
Data requested by user is found in the database buffer cache. No need to read from disk.

34. What is the SYSAUX tablespace used for?
This stores the results of a computation.
This contains the information used for backup and recovery - free space - file status details.
This tablespace stored objects that are auxiliary and not specifically tied to the core features of the database
Revoke create session from <user>;

35. What are the names of the parameter files?
These are the PFILE and the SPFILE
Background process that write database buffer cache to disk
What logical structure do data files correspond to?
This tablespace stores the rollback or undo segments used for transaction recovery

36. What kinds of server processes are there?
What goes into the table SYS.AUD$
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 can be dedicated or shared
Create user OPS$REPORTS identified externally;

37. What does the server process do?
Block in the database buffer cache does not match what is on disk.
Revoke create session from <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.
With this server process each user process gets its own server process. Server process waits on you. Most responsive.

38. What does process LGWR do?
When are connections with SYSDBA privilege audited?
This process flushes the redo log buffer. Writes redo entries to disk and signals a completion.
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.
The tablespace is what level of logical organization?

39. What is the minimum number of redo log groups you must have?
These use which pool? Oracle Recovery Manager - Oracle Shared Server - Parallel processing - I/O-related server processes.
You must have at least 2 of the log file groups.
Allows a user to grant an object privilege to another user.
Alter user HR profile report_writer;

40. Which view helps you size memory properly
Grant SELECT on <table> to <user>;
V$MEMORY_TARGET_ADVICE
This cache is allocated at instance startup
If you have a full backup and copies of these you can recover from almost any type of failure.

41. What does process DIA0 do?
This process resolves deadlock situations
What logical structure do data files correspond to?
With this server process each user process gets its own server process. Server process waits on you. Most responsive.
Grant SELECT on <table> to <user>;

42. What naming convention should you use for your PFILE
Background process that write database buffer cache to disk
This is the concept that adding and removing privileges from a role immediately affect all users who have the role.
This file should be named init<dbname>.ora
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.

43. What options does a password profile allow you to have?
This is the logical structure that makes up segments.
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.
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.
The logwriter (LGWR) writes to these files.

44. What kind of objects can be in a segment
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.
These connections always get a dedicated server process.
Allows you to group privileges for ease of management
Tables - Indexes - Materialized Views - Partitions

45. What is Virtual Private Database?
Row level security is this kind of database.
This stores the results of a computation.
Database Buffer Cache - Log Buffer - Shared Pool
SYSDBA is this kind of role.

46. What does the WITH ADMIN OPTION do?
This physical structure is the formatted size of the minimum unit of storage on the device
Alter user HR profile report_writer;
Allows a user to grant a system privilege to another user.
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE

47. What is a change vector?
This grants users access to the database.
DBA_STMT_AUDIT_OPTS is a view that does what?
Memory Size - Database and Instance Name - Archiving parameters - Processes - Over 1900 other parameters
This is a modification applied to something; executing DML generates these applied to data.

48. What is the highest privileged 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.
This contains information used for private or session-related information that individual users need
Database Buffer Cache - Log Buffer - Shared Pool
SYSDBA is this kind of role.

49. What characters do all background processes start with?

50. What is a password verify function