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 does the WITH GRANT OPTION do?
These are copies of redo log files
Allows a user to grant an object privilege to another user.
This controls how a user can access actual data in the database. Primarily applies to rows in a table or a view.
What is OPS$

2. What do control files contain?
Alter user HR profile report_writer;
Database shutdown - full or partial checkpoint - recovery time threshold - free block is needed - some DDL commands - every three seconds
Recognizes a user as logged into the OS and waives the password requirement.
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.

3. Which SGA Structures are optional
Large Pool - Java Pool - Streams Pool
SELECT - INSERT - UPDATE - DELETE - REFERENCES - INDEX - ALTER - EXECUTE
Memory Size - Database and Instance Name - Archiving parameters - Processes - Over 1900 other parameters
AUDIT_TRAIL is set to DB

4. What kind of objects can be in a segment
You should switch these every 15-30 minutes
Tables - Indexes - Materialized Views - Partitions
Alter user HR profile report_writer;
Allows you to group privileges for ease of management

5. What is the first file read when a database starts?
This lets you encrypt data in your database files
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 are usually configured 3 groups with 2 mirrored members in each.
The parameter file is read at what point during database startup?

6. 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.
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.
Audit create table;
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.

7. What is dynamic privilege 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 process can be dedicated or shared
This is the concept that adding and removing privileges from a role immediately affect all users who have the role.
This file should be named init<dbname>.ora

8. 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.
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 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

9. What is the SQL to grant an object privilege?
This process runs the job given to it by the CJQ0 process
Data Files - Control Files - Redo Log Files - Archive Log Files - Server and initialization parameter files
Revoke create session from <user>;
Grant SELECT on <table> to <user>;

10. How does a dedicated server process work?
This is a library of ready to go SQL Statements.
With this server process each user process gets its own server process. Server process waits on you. Most responsive.
This is the physical component or the files
Memory Size - Database and Instance Name - Archiving parameters - Processes - Over 1900 other parameters

11. What does process DIA0 do?
The parameter file is read at what point during database 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 process resolves deadlock situations
This process copies filled redo logs to the archived redo storage area. If you aren't in archive mode - this process shuts down.

12. How does updating use Database Buffer Cache
Database Buffer Cache - Log Buffer - Shared Pool
Alter user HR profile report_writer;
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 connections always get a dedicated server process.

13. What options can you add to audits?
DBA_OBJ_AUDIT_OPTS is a view that does what?
Views - Procedures - Synonyms - Sequences
Whenever successful - whenever not successful - by access - by session
V$MEMORY_TARGET_ADVICE

14. What does process ARCn do?

15. What is the highest privileged role?
This process flushes the redo log buffer. Writes redo entries to disk and signals a completion.
This process checks for scheduled tasks within the database.
This process is primarily responsible for instance recovery. Applies redo information. Cleans and releases temporary space
SYSDBA is this kind of role.

16. What does the server process do?
Create user OPS$REPORTS identified externally;
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.
ORACLE's work area for executing SQL
This is written in PL/SQL and examines passwords when they're chosen and accepts or rejects them based on criteria.

17. How should log files be configured?
These are usually configured 3 groups with 2 mirrored members in each.
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 are the PFILE and the SPFILE
ORACLE's work area for executing SQL

18. The SGA is shared by what processes?
This is the physical component or the files
DBA_OBJ_AUDIT_OPTS is a view that does what?
This process copies filled redo logs to the archived redo storage area. If you aren't in archive mode - this process shuts down.
Oracle background processes and server processes share this.

19. Where does auditing data go by default
These all start with 'ora_'
AUDIT - NOAUDIT
What goes into the table SYS.AUD$
DB Block -- minimum size read into database buffer cache - OS Block - physical block determined when you format hard drive.

20. When is database buffer cache allocated?
This is the physical component or the files
This cache is allocated at instance startup
This process takes dirty blocks from dirty list and writes them to disk.
Large Pool - Java Pool - Streams Pool

21. How often should you switch log files
DBA_PRIV_AUDIT_OPTS is a view that does what?
This tablespace stored objects that are auxiliary and not specifically tied to the core features of the database
You should switch these every 15-30 minutes
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.

22. What is the library cache?
Background processes - Server Processes - User Processes are part of what?
This is a library of ready to go SQL Statements.
Tables - Indexes - Materialized Views - Partitions
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.

23. What are Archive log files?
These are copies of redo log files
Data requested by user is found in the database buffer cache. No need to read from disk.
This parameter is used to automatically size and manage the SGA - PGA - and all memory used by them.
This contains the information used for backup and recovery - free space - file status details.

24. What SQL Would let you audit people creating tables?
These files store information from the log buffer.
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;
Audit create table;

25. What view lets you look at audits of privileges
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.
These use which pool? Oracle Recovery Manager - Oracle Shared Server - Parallel processing - I/O-related server processes.
DBA_PRIV_AUDIT_OPTS is a view that does what?
These are copies of redo log files

26. What is the SYSTEM tablespace used for?
These connections always get a dedicated server process.
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 tablespace stores the core database objects that are used for running the database itself
This lets you encrypt data in your database files

27. What physical structure does a tablespace correspond to?
This is a library of ready to go SQL Statements.
What logical structure do data files correspond to?
Grant create session to <user> with admin option;
Audit create table;

28. What does parameter MEMORY_TARGET not control?
Memory used by sessions connecting to the database are not controlled by this parameter.
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 is a combination of memory and processes that are part of a running installation
This grants users access to the database.

29. What is a database
Views - Procedures - Synonyms - Sequences
If you have a full backup and copies of these you can recover from almost any type of failure.
Grant create session to <user> with admin option;
This is the physical component or the files

30. What is the block header?
This is the first part of a data file.
This relieves the shared pool of sometimes-transient memory requirements.
This is a special type of database file holding only information for sorts or temporary tables.
Data requested by user is found in the database buffer cache. No need to read from disk.

31. What is a segment
This process checks for scheduled tasks within the database.
Parsed SQL - syntax - objects are checked - Oracle permissions - p-code - optimum path or plan.
This is a logical structure that represents objects in the database that require physical storage
Whenever successful - whenever not successful - by access - by session

32. What can you configure in the 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.
Memory Size - Database and Instance Name - Archiving parameters - Processes - Over 1900 other parameters
This cache is allocated at instance startup
This is the minimum amount of storage that Oracle reads or writes.

33. What actions are always audited
Commit - every three seconds - redo log buffer is 1/3 full - just before dirty block is written to disk.
Grant create session to <user> with admin option;
This cleans up failed processes by releasing resources and rolling back uncomitted data.
When are connections with SYSDBA privilege audited?

34. What are redo log files?
These files store information from the log buffer.
This lets you encrypt data in your database files
Views - Procedures - Synonyms - Sequences
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;

35. What options do you have for auditing?
This is the first part of a data file.
Recognizes a user as logged into the OS and waives the password requirement.
This grants users access to the database.
Users - Objects - System Privileges - Combination are things you can?

36. What prefix must precede the OS username for an OS-Authenticated user?
Background processes - Server Processes - User Processes are part of what?
This process resolves deadlock situations
These are copies of redo log files
What is OPS$

37. What information is NOT stored in redo logs
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 are stored in ORACLE_HOME: $ORACLE_HOME/dbs - %ORACLE_HOMEdatabase
DBA_PRIV_AUDIT_OPTS is a view that does what?
SELECT statement information are not stored in these files?

38. What is the minimum number of redo log groups you must have?
You must have at least 2 of the log file groups.
Allows a user to grant a system privilege to another user.
This records every SQL statement that changes data. The redo entry is in the redo log buffer before written to redo log.
These files store information from the log buffer.

39. What happens if you are missing an archive log?
The tablespace is what level of logical organization?
This pool does not use LRU and will fill up.
You cannot restore a database with a complete collection of these from your last full backup.
Data blocks copied into database buffer cache - relevant rows copied into user session PGA - blocks stay in cache until buffer needed for something else.

40. how do you turn on or off auditing?
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.
These are copies of redo log files
AUDIT - NOAUDIT

41. What is the PL/SQL function result cache?
Grant SELECT on <table> to <user> WITH GRANT OPTION;
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.
The first extent is created when?

42. What kinds of objects are not segments -but are pieces of code in the SYSTEM tablespace.
What is OPS$
Views - Procedures - Synonyms - Sequences
This is a special type of database file holding only information for sorts or temporary tables.
These files are stored in ORACLE_HOME: $ORACLE_HOME/dbs - %ORACLE_HOMEdatabase

43. What SQL allows you to create role and grant privileges to a user
Create role <role>; grant <privilege> on <table> to <role>; grant <role> to <user>;
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 is the logical structure that makes up segments.
Free - Pinned - Dirty are what?

44. What kind of server process does a DBA connection get?
This file should be named spfile<dbname>.ora
These connections always get a dedicated server process.
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.
Block in the database buffer cache does not match what is on disk.

45. What is a change vector?
This is a modification applied to something; executing DML generates these applied to data.
This is the concept that adding and removing privileges from a role immediately affect all users who have the role.
Data blocks copied into database buffer cache - relevant rows copied into user session PGA - blocks stay in cache until buffer needed for something else.
The tablespace is what level of logical organization?

46. What does process LGWR do?
This process flushes the redo log buffer. Writes redo entries to disk and signals a completion.
These files are stored in ORACLE_HOME: $ORACLE_HOME/dbs - %ORACLE_HOMEdatabase
This cache is allocated at instance startup
...

47. What naming convention should you use for your SPFILE?
DBA_OBJ_AUDIT_OPTS is a view that does what?
With this server process each user process gets its own server process. Server process waits on you. Most responsive.
This file should be named spfile<dbname>.ora
Audit create table;

48. What view lets you look at audits of objects?
The parameter file is read at what point during database 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.
Data requested by user is found in the database buffer cache. No need to read from disk.
DBA_OBJ_AUDIT_OPTS is a view that does what?

49. What options does a password profile allow you to have?
This tablespace is used for temporary storage.
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 can only mount to one database
These files contain information about how your running database is configured.

50. What is Advanced Security Option
This lets you encrypt data in your database files
If you have a full backup and copies of these you can recover from almost any type of failure.
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.
V$MEMORY_TARGET_ADVICE