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 SQL allows a user access to the database?
With this server process each user process gets its own server process. Server process waits on you. Most responsive.
This process copies filled redo logs to the archived redo storage area. If you aren't in archive mode - this process shuts down.
Grant create session to <user>;
ORACLE's work area for executing SQL

2. What kind of objects can be in a segment
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.
Tables - Indexes - Materialized Views - Partitions
Users - Objects - System Privileges - Combination are things you can?
Allows a user to grant an object privilege to another user.

3. What does process PMON do?
This is a collection of information about you and the database's objects.
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 cleans up failed processes by releasing resources and rolling back uncomitted data.
This relieves the shared pool of sometimes-transient memory requirements.

4. When is database buffer cache allocated?
SYSTEM - SYSAUX - UNDO - TEMP
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.
Tables - Indexes - Materialized Views - Partitions
This cache is allocated at instance startup

5. What SQL gives a user the ability to grant system privileges to another user?
Audit create table;
Grant create session to <user> with admin option;
This relieves the shared pool of sometimes-transient memory requirements.
This process is primarily responsible for instance recovery. Applies redo information. Cleans and releases temporary space

6. What kind of server process does a DBA connection get?
This process can be dedicated or shared
These connections always get a dedicated server process.
If you have a full backup and copies of these you can recover from almost any type of failure.
This can only mount to one database

7. What is an instance?
Large Pool - Java Pool - Streams Pool
This file should be named spfile<dbname>.ora
This is a combination of memory and processes that are part of a running installation
This can only mount to one database

8. What is stored in the block header
DBA_OBJ_AUDIT_OPTS is a view that does what?
This grants users access to the database.
This contains the information used for backup and recovery - free space - file status details.
What is OPS$

9. What is the highest privileged role?
Tables - Indexes - Materialized Views - Partitions
This forces a user to follow guidelines when creating or changing passwords.
This relieves the shared pool of sometimes-transient memory requirements.
SYSDBA is this kind of role.

10. How often should you switch log files
The logwriter (LGWR) writes to these files.
V$TEMPFILE and V$DATAFILE contain this information?
These files contain information about how your running database is configured.
You should switch these every 15-30 minutes

11. What is the Log Buffer
This is a collection of information about you and the database's objects.
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.
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 contain information about how your running database is configured.

12. Which SGA Structures are required
This pool does not use LRU and will fill up.
Database Buffer Cache - Log Buffer - Shared Pool
Data blocks copied into database buffer cache - relevant rows copied into user session PGA - blocks stay in cache until buffer needed for something else.
You cannot restore a database with a complete collection of these from your last full backup.

13. What view lets you look at audits of system privileges
This processs provides a time reference within the database.
DBA_STMT_AUDIT_OPTS is a view that does what?
Allows you to group privileges for ease of management
This logical structure is the minimum unit that Oracle will read or write at any given time.

14. What is the SYSTEM tablespace used for?
This tablespace stores the core database objects that are used for running the database itself
Whenever successful - whenever not successful - by access - by session
V$MEMORY_TARGET_ADVICE
This process takes dirty blocks from dirty list and writes them to disk.

15. What options does a password profile allow you to have?
Oracle background processes and server processes share this.
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.
These files contain information about how your running database is configured.
This process runs the job given to it by the CJQ0 process

16. What does process J000 do?
Database shutdown - full or partial checkpoint - recovery time threshold - free block is needed - some DDL commands - every three seconds
This process runs the job given to it by the CJQ0 process
Data requested by user is found in the database buffer cache. No need to read from disk.
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.

17. What kinds of server processes are there?
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;
The parameter file is read at what point during database startup?
Large Pool - Java Pool - Streams Pool
This process can be dedicated or shared

18. How does a dedicated server process work?
With this server process each user process gets its own server process. Server process waits on you. Most responsive.
This relieves the shared pool of sometimes-transient memory requirements.
Large Pool - Java Pool - Streams Pool
This tablespace stores the rollback or undo segments used for transaction recovery

19. What kinds of objects are not segments -but are pieces of code in the SYSTEM tablespace.
Allows a user to grant an object privilege to another user.
AUDIT - NOAUDIT
Views - Procedures - Synonyms - Sequences
This pool does not use LRU and will fill up.

20. What is stored in the library cache
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.
Grant create session to <user> with admin option;
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.
This process resolves deadlock situations

21. What options do you have for auditing?
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.
Users - Objects - System Privileges - Combination are things you can?
Oracle background processes and server processes share this.

22. What is the PL/SQL function result cache?
These are copies of redo log files
Memory used by sessions connecting to the database are not controlled by this parameter.
You must have at least 2 of the log file groups.
This stores the results of a computation.

23. What does the server process do?
Grant create session to <user> with admin option;
These all start with 'ora_'
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.
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.

24. What physical structure does a tablespace correspond to?
What logical structure do data files correspond to?
Database shutdown - full or partial checkpoint - recovery time threshold - free block is needed - some DDL commands - every three seconds
This is a logical structure that represents objects in the database that require physical storage
This contains the information used for backup and recovery - free space - file status details.

25. What does process SMON do
Audit create table;
This process is primarily responsible for instance recovery. Applies redo information. Cleans and releases temporary space
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.
V$MEMORY_TARGET_ADVICE

26. Which SGA Structures are optional
Show parameter audit_file_dest;
Views - Procedures - Synonyms - Sequences
Large Pool - Java Pool - Streams Pool
This is a modification applied to something; executing DML generates these applied to data.

27. How do you create an OS-Authenticated user?
You should switch these every 15-30 minutes
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.
Create user OPS$REPORTS identified externally;
This tablespace stores the core database objects that are used for running the database itself

28. Which process writes to the Redo Log Files?
The logwriter (LGWR) writes to these files.
Views - Procedures - Synonyms - Sequences
The first extent is created when?
What is OPS$

29. What characters do all background processes start with?

30. What do archive log and redo logs allow you to do?
This process is primarily responsible for instance recovery. Applies redo information. Cleans and releases temporary space
If you have a full backup and copies of these you can recover from almost any type of failure.
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.
Large Pool - Java Pool - Streams Pool

31. What does process LGWR do?
Recognizes a user as logged into the OS and waives the password requirement.
This process flushes the redo log buffer. Writes redo entries to disk and signals a completion.
This is a library of ready to go SQL Statements.
Each concurrent database request gets a server process.

32. What are the 8 object priviliges?
Allows a user to grant an object privilege to another user.
Memory Size - Database and Instance Name - Archiving parameters - Processes - Over 1900 other parameters
This process checks for scheduled tasks within the database.
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE

33. What prefix must precede the OS username for an OS-Authenticated user?
This tablespace stored objects that are auxiliary and not specifically tied to the core features of the database
What is OPS$
Background process that write database buffer cache to disk
This contains information used for private or session-related information that individual users need

34. How does a shared server architecture work?
You cannot restore a database with a complete collection of these from your last full backup.
These files are stored in ORACLE_HOME: $ORACLE_HOME/dbs - %ORACLE_HOMEdatabase
Each concurrent database request gets a server process.
This forces a user to follow guidelines when creating or changing passwords.

35. What is the SQL to grant an object privilege?
The first extent is created when?
This is the concept that adding and removing privileges from a role immediately affect all users who have the role.
Grant SELECT on <table> to <user>;
This is a modification applied to something; executing DML generates these applied to data.

36. What naming convention should you use for your PFILE
You should switch these every 15-30 minutes
This file should be named init<dbname>.ora
This cleans up failed processes by releasing resources and rolling back uncomitted data.
The parameter file is read at what point during database startup?

37. What is a database
Audit create table;
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.
What goes into the table SYS.AUD$
This is the physical component or the files

38. 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
This is a logical structure that represents objects in the database that require physical storage
The logwriter (LGWR) writes to these files.
Memory Size - Database and Instance Name - Archiving parameters - Processes - Over 1900 other parameters

39. What is the first level of logical organization of your physical storage?
You should switch these every 15-30 minutes
The tablespace is what level of logical organization?
Free - Pinned - Dirty are what?
Background process that write database buffer cache to disk

40. What does process CJQ0 do?
This process flushes the redo log buffer. Writes redo entries to disk and signals a completion.
DBA_STMT_AUDIT_OPTS is a view that does what?
This contains the information used for backup and recovery - free space - file status details.
This process checks for scheduled tasks within the database.

41. What is the UNDO tablespace used for?
Recognizes a user as logged into the OS and waives the password requirement.
This tablespace stores the rollback or undo segments used for transaction recovery
This is a combination of memory and processes that are part of a running installation
Allows you to group privileges for ease of management

42. What does process VKTM do?
The tablespace is what level of logical organization?
Grant create session to <user>;
SYSDBA is this kind of role.
This processs provides a time reference within the database.

43. What SQL gives a user the ability to grant object privileges to another user?
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 logical structure that represents objects in the database that require physical storage
Grant SELECT on <table> to <user> WITH GRANT OPTION;
If you have a full backup and copies of these you can recover from almost any type of failure.

44. What is the library cache?
Revoke create session from <user>;
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 grants users access to the database.
This is a library of ready to go SQL Statements.

45. What options can you add to audits?
The parameter file is read at what point during database startup?
You must have at least 2 of the log file groups.
...
Whenever successful - whenever not successful - by access - by session

46. What is a change vector?
This process resolves deadlock situations
Tables - Indexes - Materialized Views - Partitions
This tablespace is used for temporary storage.
This is a modification applied to something; executing DML generates these applied to data.

47. What view lets you look at audits of privileges
This cache is allocated at instance startup
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 pool does not use LRU and will fill up.
DBA_PRIV_AUDIT_OPTS is a view that does what?

48. Where does auditing data go by default
Database Buffer Cache - Log Buffer - Shared Pool
This process takes dirty blocks from dirty list and writes them to disk.
This logical structure is the minimum unit that Oracle will read or write at any given time.
What goes into the table SYS.AUD$

49. What does the CREATE SESSION privilege do?
This grants users access to the database.
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE
What logical structure do data files correspond to?
These are the PFILE and the SPFILE

50. What is a tempfile?
This is the minimum amount of storage that Oracle reads or writes.
This relieves the shared pool of sometimes-transient memory requirements.
AUDIT - NOAUDIT
This is a special type of database file holding only information for sorts or temporary tables.