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 are Archive log files?
If you have a full backup and copies of these you can recover from almost any type of failure.
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.
These are copies of redo log files

2. What does the Program Global Area (PGA) contain?
These use which pool? Oracle Recovery Manager - Oracle Shared Server - Parallel processing - I/O-related server processes.
Create user OPS$REPORTS identified externally;
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 physical structure is the formatted size of the minimum unit of storage on the device

3. What is a buffer cache hit?
Data requested by user is found in the database buffer cache. No need to read from disk.
Allows a user to grant a system privilege to another user.
You cannot restore a database with a complete collection of these from your last full backup.
SELECT statement information are not stored in these files?

4. What is a change vector?
This is a modification applied to something; executing DML generates these applied to data.
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.
You must have at least 2 of the log file groups.
These use which pool? Oracle Recovery Manager - Oracle Shared Server - Parallel processing - I/O-related server processes.

5. What SQL allows you to create role and grant privileges to a user
This is written in PL/SQL and examines passwords when they're chosen and accepts or rejects them based on criteria.
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;
What goes into the table SYS.AUD$
This is the concept that adding and removing privileges from a role immediately affect all users who have the role.

6. What does process J000 do?
This process runs the job given to it by the CJQ0 process
SYSDBA is this kind of role.
This physical structure is the formatted size of the minimum unit of storage on the device
These all start with 'ora_'

7. What happens during a log switch operation? What causes the following: LGWR finishes writing to the current group
Whenever successful - whenever not successful - by access - by session
V$MEMORY_TARGET_ADVICE
This is the physical component or the files
LGWR starts writing to the next group - A Database checkpoint occurs - The DBWR writes dirty blocks our of the buffer cascade

8. What does process PMON do?
ORACLE's work area for executing SQL
AUDIT - NOAUDIT
This cleans up failed processes by releasing resources and rolling back uncomitted data.
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.

9. What is stored in the library cache
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.
Background process that write database buffer cache to disk
Tables - Indexes - Materialized Views - Partitions

10. What does parameter MEMORY_TARGET do?
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.
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?

11. What physical structure does a tablespace correspond to?
This process can be dedicated or shared
What logical structure do data files correspond to?
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.
Grant create session to <user> with admin option;

12. List the kinds of files used to run an Oracle Instance?
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files
Allows a user to grant an object privilege to another user.
Whenever successful - whenever not successful - by access - by session
Views - Procedures - Synonyms - Sequences

13. What does process VKTM do?
This logical structure is the minimum unit that Oracle will read or write at any given time.
These files store information from the log buffer.
DBA_OBJ_AUDIT_OPTS is a view that does what?
This processs provides a time reference within the database.

14. What do control files contain?
Memory used by sessions connecting to the database are not controlled by this parameter.
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.
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.

15. What are the 8 object priviliges?
Allows a user to grant a system privilege to another user.
If you have a full backup and copies of these you can recover from almost any type of failure.
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE
Allows a user to grant an object privilege to another user.

16. What is an instance?
This stores the results of a computation.
This is a combination of memory and processes that are part of a running installation
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.

17. What is the minimum number of redo log groups you must have?
DBA_OBJ_AUDIT_OPTS is a view that does what?
This forces a user to follow guidelines when creating or changing passwords.
These are usually configured 3 groups with 2 mirrored members in each.
You must have at least 2 of the log file groups.

18. What characters do all background processes start with?

19. What is Advanced Security Option
When are connections with SYSDBA privilege audited?
What logical structure do data files correspond to?
Grant SELECT on <table> to <user>;
This lets you encrypt data in your database files

20. How does a shared server architecture work?
Allows a user to grant an object privilege to another user.
This processs provides a time reference within the database.
This is a combination of memory and processes that are part of a running installation
Each concurrent database request gets a server process.

21. What is a segment
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;
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
V$TEMPFILE and V$DATAFILE contain this information?

22. What happens if you are missing an archive log?
You cannot restore a database with a complete collection of these from your last full backup.
This controls how a user can access actual data in the database. Primarily applies to rows in a table or a view.
Revoke create session from <user>;
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.

23. What is a database
This tablespace stores the rollback or undo segments used for transaction recovery
This is the physical component or the files
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;

24. What uses the large pool?
These use which pool? Oracle Recovery Manager - Oracle Shared Server - Parallel processing - I/O-related server processes.
Alter user HR profile report_writer;
This is written in PL/SQL and examines passwords when they're chosen and accepts or rejects them based on criteria.
AUDIT_TRAIL is set to DB

25. What is the Log Buffer
This can only mount to one database
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.
Background processes - Server Processes - User Processes are part of what?
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.

26. How do you assign a password profile to a user
This logical structure is the minimum unit that Oracle will read or write at any given time.
This contains the information used for backup and recovery - free space - file status details.
This tablespace is used for temporary storage.
Alter user HR profile report_writer;

27. What view lets you look at audits of objects?
This process takes dirty blocks from dirty list and writes them to disk.
DBA_OBJ_AUDIT_OPTS is a view that does what?
This is the minimum amount of storage that Oracle reads or writes.
This pool does not use LRU and will fill up.

28. Where are the PFILE and SPFILE stored?
These files are stored in ORACLE_HOME: $ORACLE_HOME/dbs - %ORACLE_HOMEdatabase
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 stores the results of a computation.
This is written in PL/SQL and examines passwords when they're chosen and accepts or rejects them based on criteria.

29. What are the names of the parameter files?
These are the PFILE and the SPFILE
This process resolves deadlock situations
This tablespace stores the core database objects that are used for running the database itself
SELECT statement information are not stored in these files?

30. What does process CKPT do?
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 are usually configured 3 groups with 2 mirrored members in each.
Users - Objects - System Privileges - Combination are things you can?
This process copies filled redo logs to the archived redo storage area. If you aren't in archive mode - this process shuts down.

31. What is the block header?
DBA_STMT_AUDIT_OPTS is a view that does what?
This is a collection of information about you and the database's objects.
This lets you encrypt data in your database files
This is the first part of a data file.

32. What is Operating System Authentication?
Oracle background processes and server processes share this.
You should switch these every 15-30 minutes
Background processes - Server Processes - User Processes are part of what?
Recognizes a user as logged into the OS and waives the password requirement.

33. What is an OS Block?
This physical structure is the formatted size of the minimum unit of storage on the device
These are the PFILE and the SPFILE
Tables - Indexes - Materialized Views - Partitions
This forces a user to follow guidelines when creating or changing passwords.

34. What naming convention should you use for your PFILE
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 logical structure is the minimum unit that Oracle will read or write at any given time.
This file should be named init<dbname>.ora
SYSTEM - SYSAUX - UNDO - TEMP

35. What is audited by default in Oracle 11g?
This is a modification applied to something; executing DML generates these applied to data.
This pool does not use LRU and will fill up.
This controls how a user can access actual data in the database. Primarily applies to rows in a table or a view.
...

36. What SQL revokes user access to the database?
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files
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.
Revoke create session from <user>;
This tablespace stored objects that are auxiliary and not specifically tied to the core features of the database

37. When is the redo log buffer flushed?
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;
You must have at least 2 of the log file groups.
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.
Block in the database buffer cache does not match what is on disk.

38. What kinds of server processes are there?
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.
When are connections with SYSDBA privilege audited?
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.

39. What is the PL/SQL function result cache?
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 file should be named spfile<dbname>.ora
This stores the results of a computation.
AUDIT - NOAUDIT

40. What does the WITH GRANT OPTION do?
Grant SELECT on <table> to <user> WITH GRANT OPTION;
This forces a user to follow guidelines when creating or changing passwords.
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.
Allows a user to grant an object privilege to another user.

41. What is a database block?
Memory used by sessions connecting to the database are not controlled by this parameter.
This is the minimum amount of storage that Oracle reads or writes.
Create user OPS$REPORTS identified externally;
Free - Pinned - Dirty are what?

42. 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.
This is a library of ready to go SQL Statements.
Show parameter audit_file_dest;
Allows a user to grant a system privilege to another user.

43. What triggers a block write and therefore a dirty block?
This logical structure is the minimum unit that Oracle will read or write at any given time.
You must have at least 2 of the log file groups.
Database shutdown - full or partial checkpoint - recovery time threshold - free block is needed - some DDL commands - every three seconds
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.

44. Which view helps you size memory properly
Grant create session to <user>;
This controls what a user can do in the database. Primarily applies to adding or changing structures in the database.
V$MEMORY_TARGET_ADVICE
...

45. 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.
Free - Pinned - Dirty are what?
This controls what a user can do in the database. Primarily applies to adding or changing structures in the database.
This physical structure is the formatted size of the minimum unit of storage on the device

46. What is the Program Global Area -- PGA?
This contains information used for private or session-related information that individual users need
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.
AUDIT - NOAUDIT

47. What views contain information about your DB Files?
These files store information from the log buffer.
V$TEMPFILE and V$DATAFILE contain this information?
DBA_PRIV_AUDIT_OPTS is a view that does what?
These are copies of redo log files

48. What does parameter MEMORY_TARGET not control?
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.
Memory used by sessions connecting to the database are not controlled by this parameter.
These all start with 'ora_'
These files contain information about how your running database is configured.

49. What is a system privilege?
This process checks for scheduled tasks within the database.
This is the minimum amount of storage that Oracle reads or writes.
This controls what a user can do in the database. Primarily applies to adding or changing structures in the database.
This is written in PL/SQL and examines passwords when they're chosen and accepts or rejects them based on criteria.

50. What is the large pool?
Large Pool - Java Pool - Streams Pool
Views - Procedures - Synonyms - Sequences
This relieves the shared pool of sometimes-transient memory requirements.
The tablespace is what level of logical organization?