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. Which SGA Structures are optional
Database shutdown - full or partial checkpoint - recovery time threshold - free block is needed - some DDL commands - every three seconds
Large Pool - Java Pool - Streams Pool
This cleans up failed processes by releasing resources and rolling back uncomitted data.
Recognizes a user as logged into the OS and waives the password requirement.

2. What is a database block?
These connections always get a dedicated server process.
This controls what a user can do in the database. Primarily applies to adding or changing structures in the database.
This cache is allocated at instance startup
This is the minimum amount of storage that Oracle reads or writes.

3. What is a buffer cache hit?
Data requested by user is found in the database buffer cache. No need to read from disk.
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.

4. What is stored in the block header
When are connections with SYSDBA privilege audited?
This is the minimum amount of storage that Oracle reads or writes.
Background process that write database buffer cache to disk
This contains the information used for backup and recovery - free space - file status details.

5. How often should you switch log files
You should switch these every 15-30 minutes
This controls what a user can do in the database. Primarily applies to adding or changing structures in the database.
Oracle background processes and server processes share this.
The first extent is created when?

6. What prefix must precede the OS username for an OS-Authenticated user?
Grant create session to <user>;
Users - Objects - System Privileges - Combination are things you can?
Row level security is this kind of database.
What is OPS$

7. What options do you have for auditing?
This is a logical structure that represents objects in the database that require physical storage
These are the PFILE and the SPFILE
This is the first part of a data file.
Users - Objects - System Privileges - Combination are things you can?

8. How many datafiles does a tablespace organize?
This logical structure organizes one or more data files.
This contains information used for private or session-related information that individual users need
These are copies of redo log files
This cache is allocated at instance startup

9. What does a database role do?
Allows you to group privileges for ease of management
This is the physical component or the files
This file should be named init<dbname>.ora
This controls how a user can access actual data in the database. Primarily applies to rows in a table or a view.

10. What can you configure in the parameter files?
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.
This is the physical component or the files
Memory Size - Database and Instance Name - Archiving parameters - Processes - Over 1900 other parameters

11. What kinds of blocks do data files have?
Oracle background processes and server processes share this.
SYSTEM - SYSAUX - UNDO - TEMP
Background processes - Server Processes - User Processes are part of what?
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.

12. What is a tempfile?
Allows a user to grant a system privilege to another user.
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;
Background process that write database buffer cache to disk
This is a special type of database file holding only information for sorts or temporary tables.

13. What is an Extent?
You should switch these every 15-30 minutes
This is the logical structure that makes up segments.
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.
Allows you to group privileges for ease of management

14. What options can you add to audits?
Whenever successful - whenever not successful - by access - by session
This is the first part of a data file.
LGWR starts writing to the next group - A Database checkpoint occurs - The DBWR writes dirty blocks our of the buffer cascade
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.

15. What is the SYSTEM tablespace used for?
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.
Data requested by user is found in the database buffer cache. No need to read from disk.
This tablespace stores the core database objects that are used for running the database itself
Show parameter audit_file_dest;

16. What is an object privilege?
AUDIT - NOAUDIT
This process takes dirty blocks from dirty list and writes them to disk.
This controls how a user can access actual data in the database. Primarily applies to rows in a table or a view.
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.

17. how do you turn on or off auditing?
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE
If you have a full backup and copies of these you can recover from almost any type of failure.
AUDIT - NOAUDIT
Grant create session to <user>;

18. 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.
ORACLE's work area for executing SQL
Users - Objects - System Privileges - Combination are things you can?
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.

19. What is the Log Buffer
This is the logical structure that makes up segments.
This file should be named init<dbname>.ora
This contains the information used for backup and recovery - free space - file status details.
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.

20. What SQL lets you see where SYSDBA connections audits are stored?
These are usually configured 3 groups with 2 mirrored members in each.
V$TEMPFILE and V$DATAFILE contain this information?
Show parameter audit_file_dest;
AUDIT_TRAIL is set to DB

21. What SQL allows a user access to the database?
This processs provides a time reference within the database.
This file should be named init<dbname>.ora
These are copies of redo log files
Grant create session to <user>;

22. What SQL revokes user access to the database?
This relieves the shared pool of sometimes-transient memory requirements.
Grant SELECT on <table> to <user>;
Revoke create session from <user>;
This contains the information used for backup and recovery - free space - file status details.

23. What is the SQL for creating a password profile
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;
This file should be named spfile<dbname>.ora
V$MEMORY_TARGET_ADVICE
This process copies filled redo logs to the archived redo storage area. If you aren't in archive mode - this process shuts down.

24. What characters do all background processes start with?

25. What is the Database Buffer Cache

26. This logical structure gets created when you first create an object such as a table?
This process can be dedicated or shared
The first extent is created when?
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files
This process is primarily responsible for instance recovery. Applies redo information. Cleans and releases temporary space

27. What is a password profile
You cannot restore a database with a complete collection of these from your last full backup.
This is a combination of memory and processes that are part of a running installation
This forces a user to follow guidelines when creating or changing passwords.
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files

28. What do archive log and redo logs allow you to do?
If you have a full backup and copies of these you can recover from almost any type of failure.
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.
When are connections with SYSDBA privilege audited?
What logical structure do data files correspond to?

29. What are the 3 processes that are part of an instance?
V$TEMPFILE and V$DATAFILE contain this information?
This process can be dedicated or shared
This is the first part of a data file.
Background processes - Server Processes - User Processes are part of what?

30. What SQL gives a user the ability to grant object privileges to another user?
These are usually configured 3 groups with 2 mirrored members in each.
This process copies filled redo logs to the archived redo storage area. If you aren't in archive mode - this process shuts down.
Grant SELECT on <table> to <user> WITH GRANT OPTION;
This process can be dedicated or shared

31. How does select use Database Buffer Cache
These all start with 'ora_'
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 - relevant rows copied into user session PGA - blocks stay in cache until buffer needed for something else.
This process runs the job given to it by the CJQ0 process

32. What are the PFILE and SPFILE?
These files contain information about how your running database is configured.
This process checks for scheduled tasks within the database.
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.
Allows a user to grant an object privilege to another user.

33. List the kinds of files used to run an Oracle Instance?
What is OPS$
This physical structure is the formatted size of the minimum unit of storage on the device
Grant SELECT on <table> to <user> WITH GRANT OPTION;
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files

34. What naming convention should you use for your SPFILE?
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;
This file should be named spfile<dbname>.ora
This tablespace stores the core database objects that are used for running the database itself
You must have at least 2 of the log file groups.

35. What does process J000 do?
What is OPS$
This process runs the job given to it by the CJQ0 process
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE
The logwriter (LGWR) writes to these files.

36. What options does a password profile allow you to have?
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.
With this server process each user process gets its own server process. Server process waits on you. Most responsive.
This lets you encrypt data in your database files
ORACLE's work area for executing SQL

37. What is the Redo Log Buffer
Large Pool - Java Pool - Streams Pool
This file should be named init<dbname>.ora
Views - Procedures - Synonyms - Sequences
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.

38. What tablespaces should every database have?
You should switch these every 15-30 minutes
SYSTEM - SYSAUX - UNDO - TEMP
This is a modification applied to something; executing DML generates these applied to data.
This lets you encrypt data in your database files

39. What kinds of server processes are there?
This process resolves deadlock situations
These are copies of redo log files
This process can be dedicated or shared
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.

40. What is the TEMP tablespace used for?
This tablespace is used for temporary storage.
What logical structure do data files correspond to?
This processs provides a time reference within the database.
SYSTEM - SYSAUX - UNDO - TEMP

41. What SQL Would let you audit people creating tables?
This is a combination of memory and processes that are part of a running installation
This stores the results of a computation.
Audit create table;
This process checks for scheduled tasks within the database.

42. What triggers a block write and therefore a dirty block?
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.
This process copies filled redo logs to the archived redo storage area. If you aren't in archive mode - this process shuts down.
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 shutdown - full or partial checkpoint - recovery time threshold - free block is needed - some DDL commands - every three seconds

43. What is a segment
This is a logical structure that represents objects in the database that require physical storage
This tablespace stores the rollback or undo segments used for transaction recovery
This contains information used for private or session-related information that individual users need
Views - Procedures - Synonyms - Sequences

44. What is the library cache?
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 is a library of ready to go SQL Statements.
This process takes dirty blocks from dirty list and writes them to disk.
This relieves the shared pool of sometimes-transient memory requirements.

45. What does process DIA0 do?
This process resolves deadlock situations
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 the first part of a data file.
Tables - Indexes - Materialized Views - Partitions

46. Which process writes to the Redo Log Files?
AUDIT - NOAUDIT
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;
This contains information used for private or session-related information that individual users need
The logwriter (LGWR) writes to these files.

47. What information is NOT stored in redo logs
SELECT statement information are not stored in these files?
This is a special type of database file holding only information for sorts or temporary tables.
This process takes dirty blocks from dirty list and writes them to disk.
Grant SELECT on <table> to <user>;

48. 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
These files store information from the log buffer.
Background processes - Server Processes - User Processes are part of what?
Data blocks copied into database buffer cache - relevant rows copied into user session PGA - blocks stay in cache until buffer needed for something else.

49. What does process CJQ0 do?
This process checks for scheduled tasks within the database.
DBA_PRIV_AUDIT_OPTS is a view that does what?
This tablespace stores the rollback or undo segments used for transaction recovery
What is OPS$

50. What does parameter MEMORY_TARGET do?
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.
Create user OPS$REPORTS identified externally;
V$TEMPFILE and V$DATAFILE contain this information?
This is written in PL/SQL and examines passwords when they're chosen and accepts or rejects them based on criteria.