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 is dynamic privilege management?
These files contain information about how your running database is configured.
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.
This tablespace stores the rollback or undo segments used for transaction recovery
This is the concept that adding and removing privileges from a role immediately affect all users who have the role.

2. 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.
Database shutdown - full or partial checkpoint - recovery time threshold - free block is needed - some DDL commands - every three seconds
This is a collection of information about you and the database's objects.
Free - Pinned - Dirty are what?

3. What does the Program Global Area (PGA) contain?
This process checks for scheduled tasks within the database.
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 Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files
This tablespace stores the core database objects that are used for running the database itself

4. What views contain information about your DB Files?
This contains information used for private or session-related information that individual users need
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.
V$TEMPFILE and V$DATAFILE contain this information?
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.

5. Where are the PFILE and SPFILE stored?
Memory Size - Database and Instance Name - Archiving parameters - Processes - Over 1900 other parameters
With this server process each user process gets its own server process. Server process waits on you. Most responsive.
This is a library of ready to go SQL Statements.
These files are stored in ORACLE_HOME: $ORACLE_HOME/dbs - %ORACLE_HOMEdatabase

6. What is the dictionary cache?

7. What is the SQL for creating a password profile
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;
DBA_STMT_AUDIT_OPTS is a view that does what?
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
Large Pool - Java Pool - Streams Pool

8. What do control files contain?
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 records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.
The logwriter (LGWR) writes to these files.
DBA_STMT_AUDIT_OPTS is a view that does what?

9. What kind of server process does a DBA connection get?
These connections always get a dedicated server process.
This is written in PL/SQL and examines passwords when they're chosen and accepts or rejects them based on criteria.
This contains the information used for backup and recovery - free space - file status details.
Show parameter audit_file_dest;

10. What are the names of the parameter files?
This file should be named spfile<dbname>.ora
These are the PFILE and the SPFILE
This logical structure organizes one or more data files.
Grant SELECT on <table> to <user>;

11. This logical structure gets created when you first create an object such as a table?
The first extent is created when?
This contains the information used for backup and recovery - free space - file status details.
Block in the database buffer cache does not match what is on disk.
You should switch these every 15-30 minutes

12. What is a database block?
These all start with 'ora_'
This is the minimum amount of storage that Oracle reads or writes.
This cache is allocated at instance startup
These connections always get a dedicated server process.

13. What does the CREATE SESSION privilege do?
Tables - Indexes - Materialized Views - Partitions
This grants users access to the database.
Data blocks copied into database buffer cache - relevant rows copied into user session PGA - blocks stay in cache until buffer needed for something else.
These are usually configured 3 groups with 2 mirrored members in each.

14. What is the block header?
This is the first part of a data file.
AUDIT - NOAUDIT
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files
Data blocks copied into database buffer cache - relevant rows copied into user session PGA - blocks stay in cache until buffer needed for something else.

15. What triggers a block write and therefore a dirty block?
Grant SELECT on <table> to <user>;
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.
Alter user HR profile report_writer;

16. What is the Redo Log Buffer
When are connections with SYSDBA privilege audited?
Allows you to group privileges for ease of management
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.
This process flushes the redo log buffer. Writes redo entries to disk and signals a completion.

17. What is the first file read when a database starts?
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;
Grant SELECT on <table> to <user> WITH GRANT OPTION;
The parameter file is read at what point during database startup?
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files

18. What is Advanced Security Option
DBA_STMT_AUDIT_OPTS is a view that does what?
This lets you encrypt data in your database files
What is OPS$
You should switch these every 15-30 minutes

19. What are Archive log files?
V$MEMORY_TARGET_ADVICE
These are copies of redo log files
These files contain information about how your running database is configured.
This tablespace stores the core database objects that are used for running the database itself

20. What SQL allows you to create role and grant privileges to a user
Grant SELECT on <table> to <user>;
Users - Objects - System Privileges - Combination are things you can?
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;
Recognizes a user as logged into the OS and waives the password requirement.

21. Which SGA Structures are optional
Large Pool - Java Pool - Streams Pool
These are the PFILE and the SPFILE
Block in the database buffer cache does not match what is on disk.
This contains information used for private or session-related information that individual users need

22. What is a password profile
This is a logical structure that represents objects in the database that require physical storage
This forces a user to follow guidelines when creating or changing passwords.
These are copies of redo log files
This process takes dirty blocks from dirty list and writes them to disk.

23. What is the SYSTEM tablespace used for?
This file should be named init<dbname>.ora
This tablespace stores the core database objects that are used for running the database itself
Tables - Indexes - Materialized Views - Partitions
Allows you to group privileges for ease of management

24. What do archive log and redo logs allow you to do?
This is the concept that adding and removing privileges from a role immediately affect all users who have the role.
If you have a full backup and copies of these you can recover from almost any type of failure.
These are usually configured 3 groups with 2 mirrored members in each.
SELECT statement information are not stored in these files?

25. What SQL lets you see where SYSDBA connections audits are stored?
Show parameter audit_file_dest;
This process resolves deadlock situations
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.
You must have at least 2 of the log file groups.

26. What is a segment
V$TEMPFILE and V$DATAFILE contain this information?
The logwriter (LGWR) writes to these files.
This is a logical structure that represents objects in the database that require physical storage
This is written in PL/SQL and examines passwords when they're chosen and accepts or rejects them based on criteria.

27. What does parameter MEMORY_TARGET do?
These connections always get a dedicated server process.
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.
Memory used by sessions connecting to the database are not controlled by this parameter.

28. What is an instance?
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?
This is a combination of memory and processes that are part of a running installation
Allows a user to grant an object privilege to another user.

29. What is stored in the library cache
This is a library of ready to go SQL Statements.
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.
These all start with 'ora_'

30. What does process CJQ0 do?
This process resolves deadlock situations
This process checks for scheduled tasks within the database.
AUDIT - NOAUDIT
Show parameter audit_file_dest;

31. What is a system privilege?
Database Buffer Cache - Log Buffer - Shared Pool
Grant SELECT on <table> to <user> WITH GRANT OPTION;
With this server process each user process gets its own server process. Server process waits on you. Most responsive.
This controls what a user can do in the database. Primarily applies to adding or changing structures in the database.

32. What does a database role do?
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE
Allows you to group privileges for ease of management
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 controls how a user can access actual data in the database. Primarily applies to rows in a table or a view.

33. What are redo log files?
These files store information from the log buffer.
This process is primarily responsible for instance recovery. Applies redo information. Cleans and releases temporary space
When are connections with SYSDBA privilege audited?
ORACLE's work area for executing SQL

34. How does select use Database Buffer Cache
Recognizes a user as logged into the OS and waives the password requirement.
This cache is allocated at instance startup
This process is primarily responsible for instance recovery. Applies redo information. Cleans and releases temporary space
Data blocks copied into database buffer cache - relevant rows copied into user session PGA - blocks stay in cache until buffer needed for something else.

35. When is the redo log buffer flushed?
These files contain information about how your running database is configured.
Grant SELECT on <table> to <user> WITH GRANT OPTION;
...
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.

36. How does a shared server architecture work?
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.
Grant SELECT on <table> to <user>;
Audit create table;
Each concurrent database request gets a server process.

37. What SQL allows a user access to the database?
This logical structure is the minimum unit that Oracle will read or write at any given time.
Grant create session to <user>;
V$TEMPFILE and V$DATAFILE contain this information?
Row level security is this kind of database.

38. What kinds of blocks do data files have?
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.
This process runs the job given to it by the CJQ0 process
The first extent is created when?
ORACLE's work area for executing SQL

39. What does process ARCn do?

40. What is Virtual Private Database?
This processs provides a time reference within the database.
This is the physical component or the files
Row level security is this kind of database.
What is OPS$

41. What does process J000 do?
Grant create session to <user>;
Row level security is this kind of database.
This process runs the job given to it by the CJQ0 process
This controls what a user can do in the database. Primarily applies to adding or changing structures in the database.

42. Which SGA Structures are required
ORACLE's work area for executing SQL
These are copies of redo log files
Database Buffer Cache - Log Buffer - Shared Pool
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;

43. What does process LGWR do?
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.
This process flushes the redo log buffer. Writes redo entries to disk and signals a completion.
This is a modification applied to something; executing DML generates these applied to data.

44. When is database buffer cache allocated?
This file should be named init<dbname>.ora
This cache is allocated at instance startup
The parameter file is read at what point during database startup?
AUDIT - NOAUDIT

45. What options can you add to audits?
Whenever successful - whenever not successful - by access - by session
AUDIT_TRAIL is set to DB
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.
This is a modification applied to something; executing DML generates these applied to data.

46. What is the library cache?
Grant SELECT on <table> to <user> WITH GRANT OPTION;
Row level security is this kind of database.
This is a library of ready to go SQL Statements.
This is written in PL/SQL and examines passwords when they're chosen and accepts or rejects them based on criteria.

47. What options do you have for auditing?
These files store information from the log buffer.
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 all start with 'ora_'
Users - Objects - System Privileges - Combination are things you can?

48. What happens during a log switch operation? What causes the following: LGWR finishes writing to the current group
This is a collection of information about you and the database's objects.
This is written in PL/SQL and examines passwords when they're chosen and accepts or rejects them based on criteria.
Allows a user to grant an object privilege to another user.
LGWR starts writing to the next group - A Database checkpoint occurs - The DBWR writes dirty blocks our of the buffer cascade

49. Where does auditing data go by default
What is OPS$
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE
What goes into the table SYS.AUD$
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files

50. What is the UNDO tablespace used for?
This tablespace stores the rollback or undo segments used for transaction recovery
This process takes dirty blocks from dirty list and writes them to disk.
What logical structure do data files correspond to?
What is OPS$