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. 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
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 create table;
The logwriter (LGWR) writes to these files.

2. How many datafiles does a tablespace organize?
This is the concept that adding and removing privileges from a role immediately affect all users who have the 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.
Each concurrent database request gets a server process.
This logical structure organizes one or more data files.

3. When is database buffer cache allocated?
These are copies of redo log files
This is written in PL/SQL and examines passwords when they're chosen and accepts or rejects them based on criteria.
This tablespace stored objects that are auxiliary and not specifically tied to the core features of the database
This cache is allocated at instance startup

4. What does process J000 do?
This tablespace stores the core database objects that are used for running the database itself
The logwriter (LGWR) writes to these files.
This process runs the job given to it by the CJQ0 process
V$MEMORY_TARGET_ADVICE

5. What is a database block?
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.
Alter user HR profile report_writer;
This is the minimum amount of storage that Oracle reads or writes.
Database shutdown - full or partial checkpoint - recovery time threshold - free block is needed - some DDL commands - every three seconds

6. 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 physical structure is the formatted size of the minimum unit of storage on the device
This is a logical structure that represents objects in the database that require physical storage
This tablespace stores the core database objects that are used for running the database itself

7. What are Archive log files?
SYSTEM - SYSAUX - UNDO - TEMP
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.
These are copies of redo log files
Block in the database buffer cache does not match what is on disk.

8. How does a dedicated server process work?
If you have a full backup and copies of these you can recover from almost any type of failure.
This file should be named spfile<dbname>.ora
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>;

9. What is the Log Buffer
V$MEMORY_TARGET_ADVICE
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.
These all start with 'ora_'
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;

10. What are the PFILE and SPFILE?
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 contain information about how your running database is configured.
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE
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.

11. What is the Redo Log Buffer
Users - Objects - System Privileges - Combination are things you can?
This file should be named spfile<dbname>.ora
This process copies filled redo logs to the archived redo storage area. If you aren't in archive mode - this process shuts down.
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.

12. What does process DIA0 do?
Views - Procedures - Synonyms - Sequences
This is the first part of a data file.
ORACLE's work area for executing SQL
This process resolves deadlock situations

13. What is the TEMP tablespace used for?
This is a modification applied to something; executing DML generates these applied to data.
These files are stored in ORACLE_HOME: $ORACLE_HOME/dbs - %ORACLE_HOMEdatabase
Memory Size - Database and Instance Name - Archiving parameters - Processes - Over 1900 other parameters
This tablespace is used for temporary storage.

14. What does process DBWn do?
This process takes dirty blocks from dirty list and writes them to disk.
Grant create session to <user> with admin option;
This controls how a user can access actual data in the database. Primarily applies to rows in a table or a view.
This grants users access to the database.

15. What does process SMON do
This is the first part of a data file.
This process is primarily responsible for instance recovery. Applies redo information. Cleans and releases temporary space
This cleans up failed processes by releasing resources and rolling back uncomitted data.
Database Buffer Cache - Log Buffer - Shared Pool

16. What prefix must precede the OS username for an OS-Authenticated user?
Grant SELECT on <table> to <user>;
What is OPS$
This process takes dirty blocks from dirty list and writes them to disk.
This cleans up failed processes by releasing resources and rolling back uncomitted data.

17. What do control files contain?
V$TEMPFILE and V$DATAFILE contain this information?
This relieves the shared pool of sometimes-transient memory requirements.
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 tablespace is used for temporary storage.

18. What is dynamic privilege management?
Allows a user to grant a system privilege to another user.
This is the concept that adding and removing privileges from a role immediately affect all users who have the role.
These files store information from the log buffer.
DBA_OBJ_AUDIT_OPTS is a view that does what?

19. When does the large pool get cleaned out?
Tables - Indexes - Materialized Views - Partitions
Grant SELECT on <table> to <user> WITH GRANT OPTION;
This pool does not use LRU and will fill up.
Allows you to group privileges for ease of management

20. Where are the PFILE and SPFILE stored?
SELECT statement information are not stored in these files?
These files are stored in ORACLE_HOME: $ORACLE_HOME/dbs - %ORACLE_HOMEdatabase
SYSTEM - SYSAUX - UNDO - TEMP
You should switch these every 15-30 minutes

21. What does the WITH GRANT OPTION do?
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;
LGWR starts writing to the next group - A Database checkpoint occurs - The DBWR writes dirty blocks our of the buffer cascade
Allows a user to grant an object privilege to another user.
These are the PFILE and the SPFILE

22. What options does a password profile allow you to have?
Alter user HR profile report_writer;
The parameter file is read at what point during database startup?
This cache is allocated at instance startup
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.

23. What is Operating System Authentication?
V$TEMPFILE and V$DATAFILE contain this information?
This cache is allocated at instance startup
Recognizes a user as logged into the OS and waives the password requirement.
Database Buffer Cache - Log Buffer - Shared Pool

24. How do you assign a password profile to a user
Database Buffer Cache - Log Buffer - Shared Pool
This is the logical structure that makes up segments.
Alter user HR profile report_writer;
This physical structure is the formatted size of the minimum unit of storage on the device

25. What is an Extent?
This processs provides a time reference within the database.
This is the logical structure that makes up segments.
This logical structure organizes one or more data files.
You must have at least 2 of the log file groups.

26. What is a database
This is a modification applied to something; executing DML generates these applied to data.
This is the physical component or the files
This stores the results of a computation.
This relieves the shared pool of sometimes-transient memory requirements.

27. What is the PL/SQL function result cache?
This contains the information used for backup and recovery - free space - file status details.
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;
These connections always get a dedicated server process.
This stores the results of a computation.

28. What SQL lets you see where SYSDBA connections audits are stored?
Show parameter audit_file_dest;
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 first extent is created when?
This tablespace is used for temporary storage.

29. What is the UNDO tablespace used for?
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.
AUDIT_TRAIL is set to DB
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;
This tablespace stores the rollback or undo segments used for transaction recovery

30. What is a password profile
This tablespace stores the core database objects that are used for running the database itself
These files contain information about how your running database is configured.
Block in the database buffer cache does not match what is on disk.
This forces a user to follow guidelines when creating or changing passwords.

31. What is Advanced Security Option
Revoke create session from <user>;
What is OPS$
This lets you encrypt data in your database files
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files

32. What is a password verify function

33. What SQL Would let you audit people creating tables?
SYSDBA is this kind of role.
Audit create table;
These are the PFILE and the SPFILE
This relieves the shared pool of sometimes-transient memory requirements.

34. What are the names of the parameter files?
These are the PFILE and the SPFILE
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.
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.

35. What view lets you look at audits of objects?
This tablespace stores the rollback or undo segments used for transaction recovery
SELECT statement information are not stored in these files?
Tables - Indexes - Materialized Views - Partitions
DBA_OBJ_AUDIT_OPTS is a view that does what?

36. What options do you have for auditing?
Users - Objects - System Privileges - Combination are things you can?
This is the logical structure that makes up segments.
The first extent is created when?
LGWR starts writing to the next group - A Database checkpoint occurs - The DBWR writes dirty blocks our of the buffer cascade

37. What SQL revokes user access to the database?
This process resolves deadlock situations
The logwriter (LGWR) writes to these files.
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE
Revoke create session from <user>;

38. What does process LGWR do?
This tablespace is used for temporary storage.
This lets you encrypt data in your database files
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 process flushes the redo log buffer. Writes redo entries to disk and signals a completion.

39. What information is NOT stored in redo logs
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.
You should switch these every 15-30 minutes
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.

40. What is the Program Global Area -- PGA?
This cleans up failed processes by releasing resources and rolling back uncomitted data.
LGWR starts writing to the next group - A Database checkpoint occurs - The DBWR writes dirty blocks our of the buffer cascade
AUDIT_TRAIL is set to DB
This contains information used for private or session-related information that individual users need

41. What does parameter MEMORY_TARGET not control?
Allows you to group privileges for ease of management
Memory used by sessions connecting to the database are not controlled by this parameter.
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.
This relieves the shared pool of sometimes-transient memory requirements.

42. What is a change vector?
This is a logical structure that represents objects in the database that require physical storage
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.
This contains information used for private or session-related information that individual users need

43. Is Auditing turned on by default?
This cache is allocated at instance startup
Allows you to group privileges for ease of management
These use which pool? Oracle Recovery Manager - Oracle Shared Server - Parallel processing - I/O-related server processes.
AUDIT_TRAIL is set to DB

44. What is the dictionary cache?

45. What view lets you look at audits of system privileges
This controls how a user can access actual data in the database. Primarily applies to rows in a table or a view.
Free - Pinned - Dirty are what?
DBA_STMT_AUDIT_OPTS is a view that does what?
Allows a user to grant an object privilege to another user.

46. How do you create an OS-Authenticated user?
This tablespace stores the core database objects that are used for running the database itself
Allows a user to grant a system privilege to another user.
Create user OPS$REPORTS identified externally;
Grant create session to <user>;

47. What kinds of objects are not segments -but are pieces of code in the SYSTEM tablespace.
AUDIT_TRAIL is set to DB
These use which pool? Oracle Recovery Manager - Oracle Shared Server - Parallel processing - I/O-related server processes.
Views - Procedures - Synonyms - Sequences
This logical structure is the minimum unit that Oracle will read or write at any given time.

48. What naming convention should you use for your PFILE
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.
This file should be named init<dbname>.ora
Tables - Indexes - Materialized Views - Partitions
Block in the database buffer cache does not match what is on disk.

49. What is the Database Buffer Cache

50. What does process VKTM do?
What goes into the table SYS.AUD$
This processs provides a time reference within the database.
With this server process each user process gets its own server process. Server process waits on you. Most responsive.
The logwriter (LGWR) writes to these files.