Oracle in World: 11/28/10

Monday, November 29, 2010

Oracle DBA Architecture FAQ Exercises

1. __ contain transaction records of all database changes. (Points: 2)
a)Parameter files
b)Redo logs
c)Archive logs
d)Control files

2. TCO 3 - Checkpoints can be made to recover up to a specific time or __. (Points: 2)
a)change number
b)stamp
c)rollback ID
d)Log Sequence Number

3. TCO 3 - __ means making redundant copies of files to guarantee that if the original file is damaged, another can take its place immediately. (Points: 2)
a)Splitting
b)Replicating
c)Multiplexing
d)Rollbacking

4. TCO 3 - The __ clause of the CREATE CONTROLFILE command tells Oracle 10g to overwrite any existing control files it finds. (Points: 2)
a)MAX
b)SET DATABASE
c)REUSE
d)NORESETLOGS

5. There are __ dynamic performance views that display the high-level contents of the control file. (Points: 2)
a)two
b)three
c)four
d)five

6. __ lists current checkpoint numbers and control file sequence numbers. (Points: 2)
a)V$CONTROLFILE
b)V$CONTROLFILE_RECORD_SECTION
c)V$PARAMETER
d)V$DATABASE

7. __ shows data held in the record section. (Points: 2)
a)V$CONTROLFILE
b)V$CONTROLFILE_RECORD_SECTION
c)V$PARAMETER
d)V$DATABASE

8. The __ control file view contains copies of recycled redo log files. (Points: 2)
a)V$ARCHIVED_LOG
b)V$DATAFILE
c)V$LOG
d)V$LOGFILE

9. What is the purpose of grid computing? How can this be achieved?

Sunday, November 28, 2010

Oracle DBA Security FAQ Exercises

1. TCO 2 - When choosing the DBA authentication method, you can choose between __ and password file authentication. (Points: 2)
a)encrypted
b)hashed
c)shadow
d)operating system

2. TCO 2 - If you install Oracle 10g Enterprise Edition on Windows 2000, the minimum requirements for memory are __ megabytes of RAM. (Points: 2)
a)256
b)512
c)1024
d)2048

3. TCO 2 - If you install Oracle 10g Enterprise Edition on Windows 2000, the recommended requirements for virtual memory are __ megabytes of RAM. (Points: 2)
a)256
b)512
c)1024
d)2048

4. TCO 2 - To implement user-managed redo log files, you should use the __ clause in the CREATE DATABASE command itself. (Points: 2)
a)LF
b)LOG
c)LOGF
d)LOGFILES

5. TCO 2 - There are well over __ initialization parameters. (Points: 2)
a)50
b)200
c)500
d)1000

6. TCO 2 - The DBCA tool executes the __ for you, in order to configure the new database's Named Service. (Points: 2)
a)SQL*Plus
b)Net Services
c)Net Manager
d)Universal Installer

7. TCO 2 - The CREATE DATABASE __ option is used to set the maximum number of redo log groups. (Points: 2)
a)MAXLOGFILES
b)MAXLOGMEMBERS
c)MAXINSTANCES
d)MAXDATAFILES

8. Briefly discuss the advantages of using Oracle Managed Files.

9. Briefly discuss how Operating System authentication works.

Oracle Database PL/SQL FAQ Exercises

1. What is portability in oracle?

2. What is the mechanism to trap an error that occurs in processing?
a)EXCEPTION
b)UPDATE
c)Exception handler
d)GOTO

3. Which of the following refers to a condition where there is no WHEN clause in the CASE statement?
a)CASE_NOT_FOUND
b)ZERO_DIVIDE
c)DUP_VAL_ON_INDEX
d)NO_DATA_FOUND

4. A(n) __ is one that a developer explicitly raises in the block to enforce a business rule. (Points: 4)
a)predefined exception
b)exception
c)user-defined exception
d)error

5. The default timing of a trigger is __. (Points: 4)
a)statement level
b)system level
c)row level
d)header level

6. Which of the following statements recompiles the trigger named sales_trg? (Points: 4)
a)ENABLE TRIGGER sales_trg COMPILE;
b)DROP TRIGGER sales_trg COMPILE;
c)COMPILE TRIGGER sales_trg COMPILE;
d)ALTER TRIGGER sales_trg COMPILE;

7. The CREATE TRIGGER statement includes an optional __ clause that can check a condition to determine if the trigger should be executed. (Points: 4)
a)WHEN
b)WHERE
c)FROM
d)UPDATE

8. A(n) __ can store and handle multiple values of different data types as one unit. (Points: 4)
a)composite data type
b)collection
c)record
d)block

9. Which of the following clauses indicate the variables that are to hold the values that are retrieved from the database? (Points: 4)
a)SELECT
b)INTO
c)BEGIN
d)WHERE

10. Which of the following statements is true? (Points: 4)
a)After IN OUT parameters are included in functions, the function can no longer be used in SQL statements.
b)After OUT parameters are included in functions, the function can no longer be used in SQL statements.
c)After INOUT parameters are included in functions, the function can no longer be used in SQL statements.
d)After IN parameters are included in functions, the function can no longer be used in SQL statements.

11. Functions used in SQL statements must meet all of the following requirements, except __. (Points: 4)
a)Must be a stored database object
b)Can use only OUT parameters
c)Return data types must be a database data type
d)Cannot issue ALTER SESSION or ALTER SYSTEM commands

12. When compiling objects that call packaged program units, which of the following statements is true? (Points: 4)
a)Restriction violations are not discovered until runtime.
b)Only the program body is used for verification.
c)Because only the package body information is used, the PL/SQL compiler cannot determine if the restrictions on functions within SQL statements are being respected.
d)Restriction violations are discovered at compile time.

13. The __ directive needs to be included in the package specification to clearly state the purity levels applicable to the function. (Points: 4)
a)PRAGMA REFERENCES
b)PRAGMA RESTRICT
c)PRAGMA RESTRICT_REFERENCES
d)RESTRICT_REFERENCES

14. The term __ refers to program code that resides on the user machine. (Points: 4)
a)client-side
b)client/server
c)two-tier
d)server-side

15. To reference a host variable in PL/SQL, a preceding __ must be used with the host variable name. (Points: 4)
a)colon
b):=
c)=>
d)semicolon

16. Which of the following is an acceptable error number for the RAISE_APPLICATION_ERROR function? (Points: 4)
a)-21,000
b)-20,002
c)-19,000
d)-18,000

17. Microsoft's alternative to Java is __. (Points: 4)
a)Active X
b)JavaScript
c)VBScript
d)Visual C++

18. A server-side extension __. (Points: 4)
a)provides its services to the Web server in a way that is totally transparent to the client browser
b)must be downloaded and installed in the client computer
c)is only available for servers running Windows 2000 or greater
d)is not required to access a database over the Internet

19. Prior to which version of Oracle did the UTL_FILE package have to be explicitly compiled into the database as it was not part of the installation? (Points: 4)
a)10g
b)9i
c)8i
d)8

20. Briefly discuss the purpose of control structures

21. What is the purpose of the BEGIN section of a PL/SQL block

22. Briefly explain the difference between an Oracle error and a user-defined exception and how each is handled?

23. What is the difference between an Oracle error and a user-defined exception and how each is handled?

24. Discuss two situations in which parameter modifications are not viewed as signature modifications and, therefore, do not prompt recompilation.

Oracle Tablespace FAQ Exercises

1. A __-managed tablespace stores the details about its free space and other information inside the data dictionary tables, in the SYSTEM tablespace. (Points: 2)
a)locally
b)dictionary
c)remotely
d)distributively

2. TCO 8 - Before creating a tablespace with a nonstandard block size, you are required to create a(n) __ with a matching block size. (Points: 2)
a)cache space
b)schema object
c)segment
d)extent

3. TCO 8 - __ is the default status for a new tablespace. (Points: 2)
a)TEMPORARY
b)IMMEDIATE
c)ONLINE
d)OFFLINE

4. A(n) __ contains a copy of a primary key value from a parent table. (Points: 2)
a)clone key
b)foreign key
c)constraint
d)index

5. The __ view contains information on data extents in all tablespaces. (Points: 2)
a)DBA_FREE_SPACE
b)DBA_EXTENTS
c)DBA_USERS
d)DBA_TEMP_FILES

6. The __ view contains information on free extents within all tablespaces. (Points: 2)
a)DBA_FREE_SPACE
b)DBA_EXTENTS
c)DBA_USERS
d)DBA_TEMP_FILES

7. Trace files can be found in both the bdump and __ directories. (Points: 2)
a)bdump
b)gdump
c)cdump
d)udump

8. Oracle recommends that a series of tablespaces be created to "house" the different types of segments (objects) that the database will need to deal with to avoid what type of potential problem. (Points: 2)
a)Lack of storage space on the hard drive
b)Major I/O bottlenecks with the reading and writing of different data, indexes, and undo data to a single data file.
c)Conflicts in naming conventions.
d)The DBA not being able to monitor and administer storage for a single datafile.

9. Why would you want to create a special tablespace with its own data block size?

Oracle Backup and Recovery FAQ Exercises

1. __ is the process of rebuilding a database after some part of a database has been lost. (Points: 2)
a)Backup
b)Restoration
c)Recovery
d)Rollback

2. Oracle backup and recovery is actually broken up into __ separate actions. (Points: 2)
a)three
b)four
c)five
d)six

3. The redo logs and __ logs consist of records of all transactions made to a database. (Points: 2)
a)backup
b)recovery
c)undo
d)archive

4. __ logs are copies of old redo log files, copied just before redo log files are reused. (Points: 2)
a)Archive
b)Recovery
c)Control
d)Undo

5. A(n) __ backup or recovery requires a database to be completely shut down for the backup, and sometimes the recovery process as well. (Points: 2)
a)cold
b)hot
c)automated
d)manual

6. __ failure can be due to an administrator-induced problem, such as repetitive use of the SHUTDOWN ABORT command, or pulling the power plug out of the wall. (Points: 2)
a)Media
b)User
c)Application
d)Oracle Database-Induced

7. A __ backup is the same as a snapshot. The only difference is that the export utility performs a logical backup, not a physical backup. (Points: 2)
a)checkpoint
b)manual
c)tablespace
d)consistent

8. A __ backup creates both DDL commands and DML commands, to re-create a database object (it uses the database SQL engine). (Points: 2)
a)tablespace
b)logical
c)physical
d)consistent

9. When managing a database, how can you plan for potential disaster and recovery?

Oracle Database and SQL FAQ Exercises

1. __ allows automated creation and dropping of underlying datafiles, in addition to automated management of both redo log files and control files. (Points: 3)
a)OMF
b)OFA
c)Metadata
d)The data dictionary

2. The DBCA tool executes the __ for you, in order to configure the new database's Named Service. (Points: 3)
a)SQL*Plus
b)Net Services
c)Net Manager
d)Universal Installer

3. The __ Connection Mode can help when there are more than 1000 concurrent users. (Points: 3)
a)Single Server
b)Dedicated Server
c)Multiple
d)Shared Server

4. In the parameter file, comment lines are marked with a leading __ sign; blank lines are ignored. (Points: 3)
a)*
b)!
c)#
d)@

5. There are well over __ initialization parameters. (Points: 3)
a)50
b)200
c)500
d)1000

6. A(n) __ database is used to service Internet applications. (Points: 3)
a)Custom
b)Data Warehouse
c)General Purpose
d)OLTP

7. Oracle executable files are stored in a directory tree referred to as __. (Points: 3)
a)BASE
b)ORACLE_BASE
c)HOME
d)ORACLE_HOME

8. The __ tool allows you to create and run queries, add rows, modify data, and write reports using SQL. (Points: 3)
a)PL/SQL
b)SQL*Plus
c)Forms
d)SQLJ

9. The __ engine allows you to store, parse, and execute Java applets, servlets, and stored procedures within the database. (Points: 3)
a)JVM
b)JDeveloper
c)JBoss
d)SQLJ

10. The __ engine included with the Oracle software provides a specialized programming language you can use for creating triggers, packages, and procedures. (Points: 3)
a)ODBC
b)SQL
c)PgSQL
d)PL/SQL

11. There are __ password settings that you can set within a profile. (Points: 3)
a)five
b)six
c)seven
d)eight

12. Removing users requires the __ system privilege, which the SYSTEM user has. (Points: 3)
a)DROP USER
b)DROP
c)REMOVE
d)DBA

13. Locking a user with the __ clause (of the CREATE USER or ALTER USER commands) completely locks this user out of the database. (Points: 3)
a)LOCK
b)ACCOUNT LOCK
c)BLOCK
d)ACCOUNT BLOCK

14. A(n) __ background process is a process that copies a completed online redo log file to an archived redo log file. (Points: 3)
a)LGWR
b)log
c)ARCn
d)CKPT

15. The __ control file view contains copies of recycled redo log files. (Points: 3)
a)V$ARCHIVED_LOG
b)V$DATAFILE
c)V$LOG
d)V$LOGFILE

16. __ shows data held in the record section. (Points: 3)
a)V$CONTROLFILE
b)V$CONTROLFILE_RECORD_SECTION
c)V$PARAMETER
d)V$DATABASE

17. __ lists the names of the control files in use by the database. (Points: 3)
a)V$CONTROLFILE
b)V$CONTROLFILE_RECORD_SECTION
c)V$PARAMETER
d)V$DATABASE

18. There are __ dynamic performance views that display the high-level contents of the control file. (Points: 3)
a)two
b)three
c)four
d)five

19. The log writer process (____) updates the control file records whenever a new log sequence number is started. (Points: 3)
a)WRITER
b)LGWR
c)LWRITER
d)LOGW

20. The __ clause of the CREATE CONTROLFILE command tells Oracle 10g to overwrite any existing control files it finds. (Points: 3)
a)MAX
b)SET DATABASE
c)REUSE
d)NORESETLOGS

21. The __ data dictionary view contains the definition of a constraint. (Points: 3)
a)ALL_CONS_DEF
b)ALL_DEF_CONS
c)ALL_CONSTRAINTS
d)ALL_CONS_COLUMNS

22. If you omit the __ parameter, you cannot delete a row from the parent table, if it has related children in other tables. (Points: 3)
a)ON DELETE
b)CASCADE
c)DEFER
d)SET NULL

23. When using transparent table encryption, a random string called a __ string is added to the column in clear text prior to encryption. (Points: 3)
a)RANDOM
b)RAND
c)SALT
d)DUMMY

24. The approach to security with roles, and particularly the CONNECT role, is known as a policy of __. (Points: 3)
a)maximum privilege
b)most privilege
c)minimum privilege
d)least privilege

25. The __ object privilege allows you to create FOREIGN KEY constraints that reference the table. (Points: 3)
a)REFERENCES
b)FOREIGN KEY
c)CONSTRAINT
d)INDEX

26. There are over __ system privileges in the Oracle 10g database. (Points: 3)
a)10
b)100
c)1000
d)10000

27. A(n) __ privilege gives a user the ability to perform certain tasks on specific tables or other objects that are owned by a schema. (Points: 3)
a)system
b)role
c)object
d)restricted

28. You should click on the __ tab of a user's property sheet (available in the Security Management section of the Enterprise Manager console) to change the user's password, default tablespace, account status, and profile, without writing any code. (Points: 3)
a)General
b)Role
c)System
d)Quota

29. You should click on the __ tab of a user's property sheet (available in the Security Management section of the Enterprise Manager console) to see a list of the quotas for each tablespace. (Points: 3)
a)General
b)Role
c)System
d)Quota

30. There are __ resources you can limit with a profile. (Points: 3)
a)six
b)seven
c)eight
d)nine

31. The DROP option is required if you take a datafile offline when your database is in __ mode. (Points: 3)
a)NOARCHIVELOG
b)NOLOGGING
c)TEMPORARY
d)IMMEDIATE

32. __ is the default status for a new tablespace. (Points: 3)
a)TEMPORARY
b)IMMEDIATE
c)ONLINE
d)OFFLINE

33. A type of hot backup is a __ backup, for which you switch a tablespace into a special mode called backup mode. (Points: 3)
a)tablespace
b)logical
c)physical
d)consistent

34. Flashback technology relies generally on a combination of undo data, and __. (Points: 3)
a)archive logs
b)redo logs
c)the recycle bin
d)undo logs

35. A(n) __ standby database can potentially pass log entries to a standby database server, log entry by log entry. In other words, each transaction is applied to the standby immediately after application to the primary database. (Points: 3)
a)dynamic
b)online
c)logical
d)physical

36. __ failure is much more likely than any other failure situation, such as dropping a table that should not be dropped. (Points: 3)
a)Media
b)User and Application
c)Backup
d)Oracle Database-Induced

37. __ failure is rare because of many modern striping and mirroring utilities using specialized hardware such as RAID arrays. (Points: 3)
a)Media
b)User
c)Application
d)Oracle Database-Induced

38. A(n) __ backup or recovery requires a database to be completely shut down for the backup, and sometimes the recovery process as well. (Points: 3)
a)cold
b)hot
c)automated
d)manual

39. Sequence values can be accessed using the __ and NEXTVAL pseudocolumns. (Points: 3)
a)PREVVAL
b)FIRSTVAL
c)CURRVAL
d)UPVAL

40. PRIMARY KEY column(s) is/are usually placed __ the table. (Points: 3)
a)at the beginning of
b)at the end of
c)anywhere in
d)randomly in

41. As a DBA you would use the ____ to install Oracle software, or change an existing installation. (Points: 3)
a)Log Miner
b)Schema Manager
c)Universal Installer
d)Instance Manager

42. A __ is a physical file on the computer. (Points: 3)
a)metadata
b)table
c)datafile
d)tablespace

43. The utility __ reads trace files and loads the data into a readable file or even loads it into a database table so you can query the results. (Points: 3)
a)TRACERT
b)TKPROF
c)LGTR
d)LGWR

44. The __ directory in ORACLE_BASE/admin contains background dump trace files, including the alert log and background process trace files. (Points: 3)
a)bdump
b)gdump
c)cdump
d)udump

45. Undo data is made up of __ blocks. (Points: 3)
a)file
b)contiguous
c)data
d)undo

46. A type of hot backup is a __ backup, for which you switch a tablespace into a special mode called backup mode. (Points: 3)
a)tablespace
b)logical
c)physical
d)consistent

47. Flashback technology relies generally on a combination of undo data, and __. (Points: 3)
a)archive logs
b)redo logs
c)the recycle bin
d)undo logs

48. Briefly discuss how the listener deals with queries that are written against the database tables.

49. What is the Oracle10g Personal Edition? What is it usually used for?

50. Briefly discuss how password file authentication works.

51. Briefly discuss the difference between ASM and raw device storage mechanisms?

52. Analyze the two differences between a dedicated server and a shared server.

53. Discuss at least four of the nine types of indexes that exist in Oracle.

54. Discuss the two components of a row?

55. Discuss the different types of logical structures.

56. Discuss the various statuses that a redo log group can have.

57. Why would you want to drop redo log members?

58. As a DBA, what can you do if the user STUDENTA forgets his/her password?

59. What are the advantages of roles?

60. What is a bitmap index?

61. What are the primary advantages and disadvantages of an index-organized table?

62. Discuss how a hot backup works.

63. How fast does data change in an OLTP database and in a data warehouse? How does this affect recoverability?

64. What are rollback segments?

65. You are a junior DBA who has been asked to develop some queries to explore certain aspects of the Data Dictionary. Briefly discuss how can you execute SQL *Plus and SQL *Plus Worksheet to accomplish these tasks. (Points: 5)

Oracle Trigger FAQ Exercises

Just for your practice I am sending some multiple questions/answers so that you can practice your trigger knowledge. However, if you need the solution of this exercise you can contact arjuiut at gmail.com.
1. TCO 3 - All of the following would cause a trigger to fire, except ____. (Points: 1)
        BEFORE
        INSERT
        DELETE
        UPDATE

2. TCO 3 -
CURSOR basketitem_curIS
SELECT idproduct, quantity, option1
FROM bb_basketitem
WHERE idbasket = :NEW.idbasket;
In the code fragment above, which of the following represents the correlation identifier? (Points: 1)
        idproduct
        bb_basketitem
        quantity
        :NEW.idbasket

3. TCO 3 - ____ level triggers fire for each row affected by a DML event. (Points: 1)
        Statement
        System
        Row
        Instead-Of

4. TCO 3 - The values checked in the WHEN clause of a trigger must reference ____. (Points: 1)
        conditional predicates
        PL/SQL code
        DML events
        correlation identifiers

5. TCO 3 - Which of the following statements can be used to disable or enable a specific trigger? (Points: 1)
        ANALYZE TRIGGER trigger_name DISABLE|ENABLE;
        ALTER TRIGGER trigger_name DISABLE|ENABLE;
        AUDIT TRIGGER trigger_name DISABLE|ENABLE;
        GRANT TRIGGER trigger_name DISABLE|ENABLE;

6. TCO 3 - A(n) ____ trigger fires whenever the event occurs, regardless of the schema in which it occurs. (Points: 1)
        row level
        system level
        Instead-Of
        database level

7. TCO 3 - Which of the following statements recompiles the trigger named sales_trg? (Points: 1)
        ENABLE TRIGGER sales_trg COMPILE;
        DROP TRIGGER sales_trg COMPILE;
        COMPILE TRIGGER sales_trg COMPILE;
        ALTER TRIGGER sales_trg COMPILE;

8. TCO 3 -
CREATE [OR REPLACE] TRIGGER trigger_name [BEFORE, AFTER]
[List of DDL or Database System Events] [ON DATABASE |
SCHEMA] Trigger body;
The syntax above represents that of a(n) ____ trigger. (Points: 1)
        statement
        row level
        system
        Instead-Of

9. TCO 3 - How can triggers be applied to accomplish referential integrity? (Points: 2)

Oracle in World

Most Visited Recently

Categories

Popular Posts

Which OS you like best for your oracle

Wednesday, December 1, 2010

Oracle SQL Language FAQ Exercises