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 the SYSAUX tablespace used for?
Memory Size - Database and Instance Name - Archiving parameters - Processes - Over 1900 other parameters
This tablespace stored objects that are auxiliary and not specifically tied to the core features of the database
Grant create session to <user> with admin option;
These files are stored in ORACLE_HOME: $ORACLE_HOME/dbs - %ORACLE_HOMEdatabase

2. What SQL allows a user access to the database?
Memory Size - Database and Instance Name - Archiving parameters - Processes - Over 1900 other parameters
This process checks for scheduled tasks within the database.
CREATE PROFILE xxxxx LIMIT FAILED_LOGIN_ATTEMPTS 3;
Grant create session to <user>;

3. Which SGA Structures are required
This tablespace stored objects that are auxiliary and not specifically tied to the core features of the database
This tablespace stores the rollback or undo segments used for transaction recovery
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.
Database Buffer Cache - Log Buffer - Shared Pool

4. What is Virtual Private Database?
Memory used by sessions connecting to the database are not controlled by this parameter.
This cache is allocated at instance startup
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.
Row level security is this kind of database.

5. What kinds of objects are not segments -but are pieces of code in the SYSTEM tablespace.
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.
Views - Procedures - Synonyms - Sequences
When are connections with SYSDBA privilege audited?
This processs provides a time reference within the database.

6. Which SGA Structures are optional
Data blocks copied into database buffer cache - relevant rows copied into user session PGA - blocks stay in cache until buffer needed for something else.
LGWR starts writing to the next group - A Database checkpoint occurs - The DBWR writes dirty blocks our of the buffer cascade
Large Pool - Java Pool - Streams Pool
Show parameter audit_file_dest;

7. What does process SMON do
Recognizes a user as logged into the OS and waives the password requirement.
Create user OPS$REPORTS identified externally;
This tablespace stored objects that are auxiliary and not specifically tied to the core features of the database
This process is primarily responsible for instance recovery. Applies redo information. Cleans and releases temporary space

8. What is a change vector?
Free - Pinned - Dirty are what?
Create user OPS$REPORTS identified externally;
This is a modification applied to something; executing DML generates these applied to data.
These use which pool? Oracle Recovery Manager - Oracle Shared Server - Parallel processing - I/O-related server processes.

9. What options can you add to audits?
Row level security is this kind of database.
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
This tablespace stores the rollback or undo segments used for transaction recovery

10. What is the minimum number of redo log groups you must have?
This is the logical structure that makes up segments.
Audit create table;
You must have at least 2 of the log file groups.
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

11. What is stored in the block header
This contains the information used for backup and recovery - free space - file status details.
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.
This lets you encrypt data in your database files
Tables - Indexes - Materialized Views - Partitions

12. What does the WITH GRANT OPTION do?
Allows a user to grant an object privilege to another user.
This file should be named init<dbname>.ora
These connections always get a dedicated server process.
This controls what a user can do in the database. Primarily applies to adding or changing structures in the database.

13. How does a shared server architecture work?
This is the first part of a data file.
Allows a user to grant an object privilege to another user.
Each concurrent database request gets a server process.
You should switch these every 15-30 minutes

14. What do control files contain?
What logical structure do data files correspond to?
This logical structure organizes one or more data files.
You must have at least 2 of the log file groups.
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.

15. What is a dirty buffer?
This is a modification applied to something; executing DML generates these applied to data.
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.
Block in the database buffer cache does not match what is on disk.

16. How does a dedicated server process work?
This is the logical structure that makes up segments.
With this server process each user process gets its own server process. Server process waits on you. Most responsive.
These connections always get a dedicated server process.
Show parameter audit_file_dest;

17. What is the first file read when a database starts?
This is a collection of information about you and the database's objects.
The parameter file is read at what point during database startup?
Create user OPS$REPORTS identified externally;
Database shutdown - full or partial checkpoint - recovery time threshold - free block is needed - some DDL commands - every three seconds

18. What is a buffer cache hit?
Data requested by user is found in the database buffer cache. No need to read from disk.
Create user OPS$REPORTS identified externally;
This process flushes the redo log buffer. Writes redo entries to disk and signals a completion.
Allows a user to grant an object privilege to another user.

19. What options do you have for auditing?
Memory used by sessions connecting to the database are not controlled by this parameter.
This process can be dedicated or shared
Users - Objects - System Privileges - Combination are things you can?
Audit create table;

20. What is dynamic privilege management?
This process resolves deadlock situations
What logical structure do data files correspond to?
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.

21. What does process ARCn do?

22. What kind of server process does a DBA connection get?
These all start with 'ora_'
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.
These files are stored in ORACLE_HOME: $ORACLE_HOME/dbs - %ORACLE_HOMEdatabase
These connections always get a dedicated server process.

23. What is Operating System Authentication?
This tablespace stores the rollback or undo segments used for transaction recovery
This file should be named spfile<dbname>.ora
Recognizes a user as logged into the OS and waives the password requirement.
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.

24. What is stored in the library cache
The tablespace is what level of logical organization?
Show parameter audit_file_dest;
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.
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.

25. How does select use Database Buffer Cache
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 tablespace stores the core database objects that are used for running the database itself
SYSTEM - SYSAUX - UNDO - TEMP
This process runs the job given to it by the CJQ0 process

26. What is the Database Buffer Cache

27. What characters do all background processes start with?

28. What actions are always audited
When are connections with SYSDBA privilege audited?
This pool does not use LRU and will fill up.
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.
This process copies filled redo logs to the archived redo storage area. If you aren't in archive mode - this process shuts down.

29. What is the PL/SQL function result cache?
This relieves the shared pool of sometimes-transient memory requirements.
This stores the results of a computation.
This process flushes the redo log buffer. Writes redo entries to disk and signals a completion.
This process can be dedicated or shared

30. What tablespaces should every database have?
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files
This stores the results of a computation.
This is a small - short-term staging area for change vectors before they are written to redo logs on disk.
SYSTEM - SYSAUX - UNDO - TEMP

31. What is a system privilege?
This controls what a user can do in the database. Primarily applies to adding or changing structures in the database.
The logwriter (LGWR) writes to these files.
This grants users access to the database.
This physical structure is the formatted size of the minimum unit of storage on the device

32. What does the server process do?
You must have at least 2 of the log file groups.
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;
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.
Memory used by sessions connecting to the database are not controlled by this parameter.

33. What naming convention should you use for your PFILE
This file should be named spfile<dbname>.ora
This file should be named init<dbname>.ora
This is the minimum amount of storage that Oracle reads or writes.
Alter user HR profile report_writer;

34. What is the block header?
This logical structure organizes one or more data files.
This is the minimum amount of storage that Oracle reads or writes.
These all start with 'ora_'
This is the first part of a data file.

35. What prefix must precede the OS username for an OS-Authenticated user?
What is OPS$
This logical structure is the minimum unit that Oracle will read or write at any given time.
These connections always get a dedicated server process.
Alter user HR profile report_writer;

36. What does the WITH ADMIN OPTION do?
This tablespace is used for temporary storage.
These files store information from the log buffer.
These use which pool? Oracle Recovery Manager - Oracle Shared Server - Parallel processing - I/O-related server processes.
Allows a user to grant a system privilege to another user.

37. What does process VKTM do?
What logical structure do data files correspond to?
Background process that write database buffer cache to disk
Create user OPS$REPORTS identified externally;
This processs provides a time reference within the database.

38. What does process PMON do?
This cleans up failed processes by releasing resources and rolling back uncomitted data.
Alter user HR profile report_writer;
This is a modification applied to something; executing DML generates these applied to data.
DBA_OBJ_AUDIT_OPTS is a view that does what?

39. What uses the large pool?
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.
These use which pool? Oracle Recovery Manager - Oracle Shared Server - Parallel processing - I/O-related server processes.
This processs provides a time reference within the database.
This process takes dirty blocks from dirty list and writes them to disk.

40. What is the highest privileged role?
SYSDBA is this kind of role.
DBA_PRIV_AUDIT_OPTS is a view that does what?
This file should be named spfile<dbname>.ora
Row level security is this kind of database.

41. What is the SYSTEM tablespace used for?
Database Buffer Cache - Log Buffer - Shared Pool
This tablespace stores the core database objects that are used for running the database itself
DBA_PRIV_AUDIT_OPTS is a view that does what?
This relieves the shared pool of sometimes-transient memory requirements.

42. What are the 8 object priviliges?
This forces a user to follow guidelines when creating or changing passwords.
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE
LGWR starts writing to the next group - A Database checkpoint occurs - The DBWR writes dirty blocks our of the buffer cascade
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.

43. When does the large pool get cleaned out?
This pool does not use LRU and will fill up.
These are the PFILE and the SPFILE
...
This is a collection of information about you and the database's objects.

44. What SQL Would let you audit people creating tables?
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.
The parameter file is read at what point during database startup?
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.
Audit create table;

45. What does process DBWn do?
This process takes dirty blocks from dirty list and writes them to disk.
This contains information used for private or session-related information that individual users need
This process copies filled redo logs to the archived redo storage area. If you aren't in archive mode - this process shuts down.
Grant SELECT on <table> to <user> WITH GRANT OPTION;

46. How do you create an OS-Authenticated user?
What is OPS$
ORACLE's work area for executing SQL
Create user OPS$REPORTS identified externally;
Show parameter audit_file_dest;

47. What is a password profile
This file should be named spfile<dbname>.ora
SYSDBA is this kind of role.
This forces a user to follow guidelines when creating or changing passwords.
This process resolves deadlock situations

48. What SQL gives a user the ability to grant object privileges to another user?
These are copies of redo log files
Grant SELECT on <table> to <user> WITH GRANT OPTION;
Row level security is this kind of database.
Background process that write database buffer cache to disk

49. Which view helps you size memory properly
This stores the results of a computation.
AUDIT - NOAUDIT
V$MEMORY_TARGET_ADVICE
Allows a user to grant an object privilege to another user.

50. What kinds of blocks do data files have?
This process copies filled redo logs to the archived redo storage area. If you aren't in archive mode - this process shuts down.
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.
Show parameter audit_file_dest;
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.