Thursday 26 May 2016

Oracle DBA Interview Questions - 6





 




1. What are the different types of PL/SQL program units that can be defined and stored in ORACLE database?
   Procedures and Functions, Packages and Database Triggers.

2. What is a Procedure?
    A Procedure consist of a set of SQL and PL/SQL statements that are grouped together as a unit to solve     a specific problem or perform a set of related tasks.

3. What is difference between Procedures and Functions?
     A Function returns a value to the caller where as a Procedure does not.

4. What is a Package?
    A Package is a collection of related procedures, functions, variables and other package constructs together as a unit in the database.

5. What are the advantages of having a Package?
    Increased functionality (for example, global package variables can be declared and used by any procedure in the package) and performance (for example all objects of the package are parsed compiled, and loaded into memory once)

6. What is Database Trigger?
    A Database Trigger is procedure (set of SQL and PL/SQL statements) that is automatically executed as a result of an insert in, update to, or delete from a table.

7. What are the uses of Database Trigger?
    Database triggers can be used to automatic data generation, audit data modifications, enforce complex Integrity constraints, and customize complex security authorizations.

8. What are the differences between Database Trigger and Integrity constraints?
    A declarative integrity constraint is a statement about the database that is always true. A constraint applies to existing data in the table and any statement that manipulates the table.
A trigger does not apply to data loaded before the definition of the trigger, therefore, it does not guarantee all data in a table conforms to the rules established by an associated trigger.
A trigger can be used to enforce transitional constraints where as a declarative integrity constraint cannot be used.

9. What are Roles ?
   Roles are named groups of related privileges that are granted to users or other roles.

10. What are the uses of Roles ?
       REDUCED GRANTING OF PRIVILEGES - Rather than explicitly granting the same set of privileges          to many users a database administrator can grant the privileges for a group of related users granted to a        role and then grant only the role to each member of the group.
      DYNAMIC PRIVILEGE MANAGEMENT - When the privileges of a group must change, only the       privileges of the role need to be modified. The security domains of all users granted the group's role automatically reflect the changes made to the role.
    SELECTIVE AVAILABILITY OF PRIVILEGES - The roles granted to a user can be selectively enable (available for use) or disabled (not available for use). This allows specific control of a user's privileges in any given situation.
   APPLICATION AWARENESS - A database application can be designed to automatically enable and disable selective roles when a user attempts to use the application.

11. How to prevent unauthorized use of privileges granted to a Role ?
       By creating a Role with a password.

12. What is default table space ?
      The Tablespace to contain schema objects created without specifying a tablespace name.

13. What is Tablespace Quota ?
       The collective amount of disk space available to the objects in a schema on a particular tablespace.

14. What is a profile ?
       Each database user is assigned a Profile that specifies limitations on various system resources available to the user.

15. What are the system resources that can be controlled through Profile ?
      The number of concurrent sessions the user can establish the CPU processing time available to the user's       session the CPU processing time available to a single call to ORACLE made by a SQL statement the           amount of logical I/O available to the user's session the amount of logical I/O available to a single call to        ORACLE made by a SQL statement the allowed amount of idle time for the user's session the allowed         amount of connect time for the user's session.

16. What is Auditing ?
       Monitoring of user access to aid in the investigation of database use.

17. What are the different Levels of Auditing ?
       Statement Auditing, Privilege Auditing and Object Auditing.

18. What is Statement Auditing ?
      Statement auditing is the auditing of the powerful system privileges without regard to specifically named objects.

19. What is Privilege Auditing ?
      Privilege auditing is the auditing of the use of powerful system privileges without regard to specifically             named objects.

20. What is Object Auditing ?
     Object auditing is the auditing of accesses to specific schema objects without regard to user.


Oracle DBA Interview Questions - 1









0 comments:

Post a Comment