Oracle DBA Interview Questions PART 3

 Database Creation

1. What are the steps involved in Database Startup?
  Start an instance, Mount the Database and Open the Database.

2. What are the steps involved in Database Shutdown? Close the Database; Dismount the Database and Shutdown the Instance.

3. What is Restricted Mode of Instance Startup?
 An instance can be started in restricted mode so that when the database is open connections are limited only to those whose user accounts have been granted the RESTRICTED SESSION system privilege.

4. What mode the instance should be to create the database?

No mount

5. While creating database can you specify the size of control files?

      No

6. Which parameter determines the size of SHARED POOL?

SHARED_POOL_SIZE.

7. After mounting a database using command STARTP MOUNT can you open your database in RESTRICTED MODE. Using command “alter database open restrict;”

 No.

8. While creating database can you specify more than one datafile for SYSTEM Tablespace?

 Yes

9. What is a trace file and how is it created?

 Each server and background process can write an associated trace file. When an internal error is detected by a process or user process, it dumps information about the error to its trace. This can be used for tuning the database.

10. How do you get the create syntax of a table or index or function or procedure?         

  Select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

11. Explain about System Table space and sysaux tablespace?

 System table space has all the view relating to oracle. Sysaux tablespace is used for storage of non-system related tables and indexes that traditionally were placed in the System Tablespace. Like RMAN recovery catalog, Automatic workload repository and ultra search.

12. What are the different modes of mounting a Database with the Parallel Server?Exclusive Mode If the first instance that mounts a database does so in exclusive mode, only that Instance can mount the database. Parallel Mode If the first instance that mounts a database is started in parallel mode, other instances that are started in parallel mode can also mount the database.

13. Where alert log is stored? What is the parameter?

 In trace directory /disk2/oradata/prod/diag/rdbms/prod/prod/trace).parameter is diagnostic_dest (oracle 11g)

14. What is a Data Dictionary?The Oracle data dictionary is one of the most important components of the Oracle DBMS.It contains all information about the structures and objects of the database such as tables,columns, users, data files etc. The data stored in the data dictionary are also often called metadata.

15. How many redo logs should you have and how should they be configured for maximum recoverability? You should have at least three groups of two redo logs with the two logs each on a separate disk spindle (mirrored by Oracle). 

16. What is the Max Size of the SID?

 15char

Storage Parameters

1. What is an Extent?An Extent is a specific number of contiguous data blocks, obtained in a single allocation, and used to store a specific type of information.

2. What is a Segment?
It is a space demanding object created by user. A segment is a set of extents allocated for a certain logical structure.

3. What is the package and procedure name to conver dmts to lmts and vice versa?

 Exec dbms_space_admin.tablespace_migrate_from_local(‘TS1’)

 Exec dbms_space_admin.tablespace_migrate_to_local(‘TS1’)

4. What is the value for the storage clause pctincrease when the tablespace extent management is local (uniform)?

0%

5. Can you change the SEGMENT SPACE MANAGEMENT after creation of Tablespace?

No

6. What are the different types of Segments?Data Segment, Index Segment, Rollback Segment and Temporary Segment.

7. How to define Data Block size?
A data block size is specified for each ORACLE database when the database is created. A database users and allocated free database space in ORACLE data blocks. Block size is specified in INIT.ORA file and can't be changed latter.

8. What are the dictionary views used to monitor a database spaces?DBA_FREE_SPACE
DBA_SEGMENTS
DBA_DATA_FILES.
9 What is the difference between Data blocks and Extents?Data blocks are the smallest block for data storage of the oracle. Blocks contain actual data. Extents are made up of data blocks . Extents are continuous available blocks for storing a specific type of information.

10. What is an Index Segment? Each Index has an Index segment that stores all of its data.
11. What is the significance of having storage clause?  We can plan the storage for a table as how much initial extents are required, how much can be extended next, how much % should leave free for managing row updations etc.

12. Which parameter specified in the DEFAULT STORAGE clause of CREATE TABLESPACE cannot be altered after creating the table space?All the default storage parameters defined for the tablespace can be changed using the ALTER TABLESPACE command. When objects are created their INITIAL and MINEXTENS values cannot be changed.

13. What is the extent management of system tablespace when database is created manually, DBCA?

 Dictionary, local

 User Management

1. Can objects of the same Schema reside in different tablespaces?

Yes.

2. Can a Tablespace hold objects from different Schemas?

Yes.

3. Is CONNECT a system privilege or a role? If the answer is 'a role',   what system privileges are assigned to this role by default?

It is a role .create session.

4. Where do you get the information of quotas?

Dba_ts_quotas view, user_ts_quotas view.

5. What is the init parameter to make use of profile?

Resource_limit=true

     

6. While creating user can you assign any role?

 Yes

7. Can a segment (table) present on more than one tablespace?  No, Possible only when a table if create by using partition feature.

8. Can a segment (table) present on more than one datafiles?

Yes, Datafiles should belong to one tablespace.

9. Can we create the permanent objects in temporary tablespace?

No

10. Can you drop an object if tablespace is Offline?

 Yes

11. What privileges u gives normally when you create users?

 Create session

12. What is a View?
 A view is a virtual table. Every view has a Query attached to it.(The Query is a SELECTstatement that identifies the columns and rows of the table(s) the view uses.)

13. Can a View based on another View? Yes.14. Does a View contain Data?
 Views do not contain or store data.

15. When does a Transaction end?  When it is committed or Roll backed.

16. Define Transaction? 

A Transaction is a logical unit of work that comprises one or more SQL statements executed by a single user.

17. What is default tablespace? 

The Tablespace to contain schema objects created without specifying a tablespace name.
18. What is Tablespace Quota? 

The collective amount of disk space available to the objects in a schema on a particular tablespace.

19. What is the use 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.
20. What is a profile?

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

21. What are the roles and user accounts created automatically with the database?

20 roles are created, 7 user accounts are created when database is created manually

22. What is user Account in Oracle database?

A user account is not a physical structure in Database but it is having important relationship to the objects in the database and will be having certain privileges.

23. How do you create a table in another tablespace name?

 Create table xyz (a number) tablespace system

24. What are roles? How can we implement roles?Roles are the easiest way to grant and manage common privileges needed by different groups of database users. Creating roles and assigning provides to roles. Assign each role to group of users. This will simplify the job of assigning privileges to individual users.

25. What does ROLLBACK do

ROLLBACK retracts any of the changes resulting from the SQL statements in the transaction.

26. What is a deadlock? Explain.

Two processes waiting to update the same rows of a table which are locked by the other process then deadlock arises. In a database environment this will often happen because of not issuing proper row lock commands. Poor design of front-end application may cause this situation and the performance of server will reduce   drastically. These locks will be released automatically when a commit/rollback operation performed or any one of these processes being killed externally

27. How do you get you demo files get created in your user?

 $ORACLE_HOME/rdbms/admin/utlsampl.sql, edit and execute this script

28. Can we drop default profile?

 No

29. Can we edit default profile?

 Yes