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?
V$MEMORY_TARGET_ADVICE
This process checks for scheduled tasks within the database.
This is a library of ready to go SQL Statements.

2. how do you turn on or off auditing?
AUDIT - NOAUDIT
This is a logical structure that represents objects in the database that require physical storage
You should switch these every 15-30 minutes
This is a library of ready to go SQL Statements.

3. What is the minimum number of redo log groups you must have?
The parameter file is read at what point during database startup?
Audit create table;
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;
You must have at least 2 of the log file groups.

4. What is a buffer cache hit?
If you have a full backup and copies of these you can recover from almost any type of failure.
Whenever successful - whenever not successful - by access - by session
Data requested by user is found in the database buffer cache. No need to read from disk.
This process resolves deadlock situations

5. How do you assign a password profile to a user
This process takes dirty blocks from dirty list and writes them to disk.
Alter user HR profile report_writer;
ORACLE's work area for executing SQL
What is OPS$

6. How many datafiles does a tablespace organize?
This logical structure organizes one or more data files.
This lets you encrypt data in your database files
This process copies filled redo logs to the archived redo storage area. If you aren't in archive mode - this process shuts down.
Allows a user to grant an object privilege to another user.

7. When does the large pool get cleaned out?
This is a modification applied to something; executing DML generates these applied to data.
This is the first part of a data file.
These files are stored in ORACLE_HOME: $ORACLE_HOME/dbs - %ORACLE_HOMEdatabase
This pool does not use LRU and will fill up.

8. What kinds of server processes are there?
This process can be dedicated or shared
This process takes dirty blocks from dirty list and writes them 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.
You should switch these every 15-30 minutes

9. Where does auditing data go by default
This contains information used for private or session-related information that individual users need
What goes into the table SYS.AUD$
This can only mount to one database
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.

10. What are Archive log files?
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.
Block in the database buffer cache does not match what is on disk.
These are copies of redo log files
This is the logical structure that makes up segments.

11. What is the TEMP tablespace used for?
This tablespace is used for temporary storage.
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.
These files contain information about how your running database is configured.
This grants users access to the database.

12. What does process SMON do
This can only mount to one database
This process is primarily responsible for instance recovery. Applies redo information. Cleans and releases temporary space
This process takes dirty blocks from dirty list and writes them to disk.
V$MEMORY_TARGET_ADVICE

13. What is the SQL for creating a password profile
These files are stored in ORACLE_HOME: $ORACLE_HOME/dbs - %ORACLE_HOMEdatabase
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;
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.

14. What SQL revokes user access to the database?
Revoke create session from <user>;
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;
This is written in PL/SQL and examines passwords when they're chosen and accepts or rejects them based on criteria.
LGWR starts writing to the next group - A Database checkpoint occurs - The DBWR writes dirty blocks our of the buffer cascade

15. What uses the large pool?
This process checks for scheduled tasks within the database.
Grant create session to <user> with admin option;
These use which pool? Oracle Recovery Manager - Oracle Shared Server - Parallel processing - I/O-related server processes.
These are usually configured 3 groups with 2 mirrored members in each.

16. What is the SYSTEM tablespace used for?
Create user OPS$REPORTS identified externally;
This tablespace stores the core database objects that are used for running the database itself
Tables - Indexes - Materialized Views - Partitions
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.

17. What happens if you are missing an archive log?
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.
You cannot restore a database with a complete collection of these from your last full backup.
The logwriter (LGWR) writes to these files.
This lets you encrypt data in your database files

18. What does process CJQ0 do?
This file should be named init<dbname>.ora
Each concurrent database request gets a server process.
This process checks for scheduled tasks within the database.
This process runs the job given to it by the CJQ0 process

19. What do control files contain?
These use which pool? Oracle Recovery Manager - Oracle Shared Server - Parallel processing - I/O-related server processes.
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.
This process resolves deadlock situations
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.

20. What SQL lets you see where SYSDBA connections audits are stored?
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.
Large Pool - Java Pool - Streams Pool
Show parameter audit_file_dest;
These files contain information about how your running database is configured.

21. What does process LGWR do?
This tablespace stored objects that are auxiliary and not specifically tied to the core features of the database
This process flushes the redo log buffer. Writes redo entries to disk and signals a completion.
This pool does not use LRU and will fill up.
The tablespace is what level of logical organization?

22. What is Virtual Private Database?
Row level security is this kind of database.
You cannot restore a database with a complete collection of these from your last full backup.
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 forces a user to follow guidelines when creating or changing passwords.

23. What is the library cache?
This is a library of ready to go SQL Statements.
This is a modification applied to something; executing DML generates these applied to data.
This is the logical structure that makes up segments.
This lets you encrypt data in your database files

24. What is a password verify function

25. What SQL gives a user the ability to grant system privileges to another user?
This controls what a user can do in the database. Primarily applies to adding or changing structures in the database.
Allows a user to grant a system privilege to another user.
Grant create session to <user> with admin option;
This is a modification applied to something; executing DML generates these applied to data.

26. Which SGA Structures are optional
Large Pool - Java Pool - Streams Pool
SYSTEM - SYSAUX - UNDO - TEMP
This processs provides a time reference within the database.
The first extent is created when?

27. 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.
Database shutdown - full or partial checkpoint - recovery time threshold - free block is needed - some DDL commands - every three seconds
This is a special type of database file holding only information for sorts or temporary tables.
This process resolves deadlock situations

28. What are redo log files?
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.
These files store information from the log buffer.
Grant create session to <user>;
SELECT statement information are not stored in these files?

29. What triggers a block write and therefore a dirty block?
This is the physical component or the files
Database shutdown - full or partial checkpoint - recovery time threshold - free block is needed - some DDL commands - every three seconds
This cache is allocated at instance startup
DBA_PRIV_AUDIT_OPTS is a view that does what?

30. What does the CREATE SESSION privilege do?
This grants users access to the database.
This logical structure organizes one or more data files.
This is a logical structure that represents objects in the database that require physical storage
Database Buffer Cache - Log Buffer - Shared Pool

31. What is the dictionary cache?

32. What actions are always audited
This logical structure is the minimum unit that Oracle will read or write at any given time.
When are connections with SYSDBA privilege audited?
AUDIT - NOAUDIT
Database shutdown - full or partial checkpoint - recovery time threshold - free block is needed - some DDL commands - every three seconds

33. What do archive log and redo logs allow you to do?
What goes into the table SYS.AUD$
If you have a full backup and copies of these you can recover from almost any type of failure.
This is written in PL/SQL and examines passwords when they're chosen and accepts or rejects them based on criteria.
These connections always get a dedicated server process.

34. What is an Extent?
...
This is the logical structure that makes up segments.
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.
The first extent is created when?

35. What options does a password profile allow you to have?
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.
You should switch these every 15-30 minutes
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.

36. What is the Redo Log Buffer
ORACLE's work area for executing SQL
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.
This process checks for scheduled tasks within the database.
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.

37. What are the PFILE and SPFILE?
You must have at least 2 of the log file groups.
This logical structure organizes one or more data files.
What logical structure do data files correspond to?
These files contain information about how your running database is configured.

38. What are the 3 buffer cache states?
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.
Free - Pinned - Dirty are what?
These are usually configured 3 groups with 2 mirrored members in each.
This logical structure is the minimum unit that Oracle will read or write at any given time.

39. What is an OS Block?
This controls what a user can do in the database. Primarily applies to adding or changing structures in the database.
This cleans up failed processes by releasing resources and rolling back uncomitted data.
Memory used by sessions connecting to the database are not controlled by this parameter.
This physical structure is the formatted size of the minimum unit of storage on the device

40. What view lets you look at audits of objects?
The logwriter (LGWR) writes to these files.
Background process that write database buffer cache to disk
V$MEMORY_TARGET_ADVICE
DBA_OBJ_AUDIT_OPTS is a view that does what?

41. What does process PMON do?
This process can be dedicated or shared
Background process that write database buffer cache to disk
This tablespace is used for temporary storage.
This cleans up failed processes by releasing resources and rolling back uncomitted data.

42. What is stored in the block header
This contains information used for private or session-related information that individual users need
This pool does not use LRU and will fill up.
This contains the information used for backup and recovery - free space - file status details.
This is the first part of a data file.

43. What SQL allows a user access to the database?
Allows you to group privileges for ease of management
This is a special type of database file holding only information for sorts or temporary tables.
Grant SELECT on <table> to <user> WITH GRANT OPTION;
Grant create session to <user>;

44. What is a tempfile?
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE
What goes into the table SYS.AUD$
This is a special type of database file holding only information for sorts or temporary tables.
This contains information used for private or session-related information that individual users need

45. How many databases can an instance mount to?
Show parameter audit_file_dest;
This can only mount to one database
This cleans up failed processes by releasing resources and rolling back uncomitted data.
Allows you to group privileges for ease of management

46. What does process CKPT do?
Grant create session to <user> with admin option;
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 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.

47. What does process J000 do?
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
What logical structure do data files correspond to?
The parameter file is read at what point during database startup?

48. What is a database block?
This is the minimum amount of storage that Oracle reads or writes.
ORACLE's work area for executing SQL
This cleans up failed processes by releasing resources and rolling back uncomitted data.
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 options can you add to audits?
Whenever successful - whenever not successful - by access - by session
AUDIT_TRAIL is set to DB
This process is primarily responsible for instance recovery. Applies redo information. Cleans and releases temporary space
Large Pool - Java Pool - Streams Pool

50. What does process ARCn do?