Posted on

Oracle dba real time scenarios interview question and answer

1) What is an active session pool?
It is a type of resource allocation method. We can set a maximum allowable number of concurrent sessions within any consumer resource group.All sessions that are beyond the maximum limit are queued for execution after the freeing up of current active sessions.
2) What is an Automatic consumer group switching?
It is a type of resource allocation method.Using this method,we can specify that a user session be automatically switched to a different group after it runs more than a specified number of seconds.The group that the session should switch to is called as switch group,and the time limit is the switch time.The session can revert to its original consumer group after e end of the top call,which is defined as an entire PL/SQL block or a separate SQL statement.
3) What is cancelling SQL and terminating sessions?
It is a type of resource allocation method.By using CANCEL_SQL or KILL_SESSION as the switch group,we can direct a long-running SQL segment or even an entire session to be cancelled or terminated.
4) What is a Parallel Degree Limit?
It is a type of resource allocation method.We can use this method to specify the limit of the degree of parallelism for an operation.
5) What is a resource plan directive?
It is an element of database resource manager.It links a resource plan to a specific resource consumer group
6) How will you get details on SGA and PGA from v$ views?
We can get to know the size of SGA (System Global Area) components and PGA (Program Global Area) components using sql query. We can query the v$ dynamic performance views to get more information about the memory structure of the oracle instance.
SQL> column component format a30;
SQL> column current_size format 999999999;
SQL> column min_size format 999999999;
SQL> column max_size format 999999999;
SQL> select component,current_size,min_size,max_size from v$sga_dynamic_components;
—————————— ———— ———- ———-
shared pool 327155712 327155712 327155712
large pool 8388608 8388608 8388608
java pool 8388608 8388608 8388608
streams pool 0 0 0
DEFAULT buffer cache 452984832 452984832 452984832
KEEP buffer cache 0 0 0
RECYCLE buffer cache 0 0 0
DEFAULT 2K buffer cache 0 0 0
DEFAULT 4K buffer cache 0 0 0
DEFAULT 8K buffer cache 0 0 0
DEFAULT 16K buffer cache 0 0 0
—————————— ———— ———- ———-
DEFAULT 32K buffer cache 0 0 0
Shared IO Pool 0 0 0
ASM Buffer Cache 0 0 0
14 rows selected.
SQL> select name,value from v$pgastat where name in(‘maximum PGA allocated’,’total PGA allocated’);
—————————————————————- ———-
maximum PGA allocated 115508224
7) Why does desc index command does not work?
It is possible to obtain information on the object structure using the SQL command DESC.We can get to know the structure of a table using the command desc tablename
SQL> desc learnersreference_t;
Name Null? Type
—————————————– ——– —————————-
I tried describing an index using the command DESC indexname. I got the following error.
SQL> desc learnersreference_i;
ORA-04043: object learnersreference_i does not exist
I wondered why this happened as I’ve already created an index with this name. So,I tried creating an index with this name.
SQL> create index learnersreference_i on learnersreference_t(user_id);
create index learnersreference_i on learnersreference_t(user_id)
ERROR at line 1:
ORA-00955: name is already used by an existing object
Again this created a problem with an error saying that the object exists which is contradictory. So, I tried creating index on another column
SQL> create index learnersreference_i_new on learnersreference_t(user_id);
Index created.
Again tried DESC indexname command and got the same error
SQL> desc learnersreference_i_new;
ORA-04043: object learnersreference_i_new does not exist
Thus I concluded that DESC indexname command always returns the error ORA-04043: object object_name does not exist
8) What is a cursor? Why do I need it?
If you are a oracle newbie developer, experienced oracle developer the first important concept in the code is going to be cursor.
9) What is real time use of a cursor?
To put it simple, when we have a select statement output (set of ros usually more than one row) to be processed in a PL/SQL code, cursor usually comes into picture.
10) How do I start using a cursor?
A cursor is declared in the declaration section as a variable in PL/SQL. Here is the simple cursor syntax :
Cursor cursor_name is select colname from tables where condition;
for rec in cursor_name
perform operations….
end loop;
This makes the processing of set of records very simple, easy and efficient.
11) What jobs can be scheduled using scheduler in an oracle database?
Oracle Scheduler lets us schedule jobs in database. In OS cron can be used to schedule jobs. Following are some jobs that can be scheduled :
1) Gathering table and index statistics- Keeping statistics of tables, indexes up-to-date is essentially important for best database performance. As a best practice weekly full backup should happen after statistics collection is completed to have a best performing backup. This is a job that can be scheduled using dbms_Scheduler
2) Executing database monitoring scripts – With current OEM need for oracle monitoring has become GUI based. However, adhoc monitoring scripts can be created and scheduled using dbms_Scheduler
Batch jobs
Replication jobs
3) Running daily backups – RMAN backups can be created as scripts and scheduled to run on daily, weekly, hourly basis as needed
4) Index rebuild – If the schema data changes too often, indexes become stale owing to b-tree issues. As such index rebuild becomes mandate. This can be scheduled as monthly job
5) Month end reports – Reporting is creation of csv, text files using utilities like utl_file that creates and writes reports onto location specified using utl_file_dir These jobs can be automated using scheduler
12) You connect to the database and query v$pwfile_users. What does this mean?
In oracle database we can create users and grant sysdba and sysoper privileges. It is interesting to know that when an user connects to the database with sysoper privilege, and when we query the user, PUBLIC is displayed.
SQL> select * from v$pwfile_users;
—————————— —– —– —–
SQL> create user test1 identified by test1;
User created.
SQL> grant sysoper to test1;
Grant succeeded.
SQL> create user test2 identified by test2;
User created.
SQL> grant sysdba to test2;
Grant succeeded.
SQL> connect test1/test1 as sysoper
SQL> show user
SQL> connect test2/test2 as sysdba
SQL> show user
SQL> select * from v$pwfile_users;
—————————— —– —– —–
13) What is Oracle Database Vault?
Oracle Database Vault is a security solution from oracle corporation. It restricts access to certain areas of the database.Even users with administrative privileges have restricted access.This fine-grained access control protects the database from super-privileged users.
14) Where can we use Oracle Database Vault?
Oracle Database Vault can be used to protect standalone database instance,Oracle Real Application Cluster(RAC)
What are the components of Oracle Database Vault?
Oracle Database Vault has the following components:
1) Oracle Database Vault Access Control Components
2) Oracle Database Vault Administrator (DVA)
3) Oracle Database Vault Configuration Assistant (DVCA)
4) Oracle Database Vault DVSYS and DVF Schemas
5) Oracle Database Vault PL/SQL Interfaces and Packages
6) Oracle Database Vault and Oracle Label Security PL/SQL APIs
7) Oracle Database Vault Reporting and Monitoring Tools
15) What is Oracle Database Vault Access Control Components made of ?
It is formed of the following components that helps us manage security for the database instance.They are:
2)Command Rules
3) Factors
4) Rule sets
5) Security Application roles
16) What is a realm?
A realm is a functional grouping of database schemas,objects and roles that must be secured.After grouping we can use realms to control the use of system privileges to specific accounts or roles. This provides fine-grained access control.
17) What is a command rule?
A command rule is a special rule created to control how users can execute almost any SQL statement.This includes SELECT,ALTER SYSTEM,DDL and DML statements.
18) Do command rules work alone?
No.They work with rule sets to determine whether or not a statement is allowed.
19) What is a factor?
A factor is a named variable or a attribute such as user location,database IP address,or session user,which oracle database vault can recognize and secure.
20) What is the use of a factor?
We can use factors for activities such as authorizing database accounts to connect to the database or creating filter logic to restrict the visibility and manageability of data.
21) What is an identity?
Each factor can have one or more identities.An identitiy is the actual value of a factor. A factor can have several identities depending on the factor retrieval method or its identity mapping logic
22) What is a rule set?
A rule set is a collection of one or more rules that we can associate with a realm authorization, command rule, factor assignment, or secure application role.
23) What are the possible evaluations of a rule set?
The rule set evaluates to true or false based on the evaluation of each rule it contains and the evaluation type (All True or Any True).
24) What is a rule within a rule set?
The rule within a rule set is a PL/SQL expression that evaluates to true or false.
25) Can rules be reused?
We can have the same rule in multiple rule sets.
26) What are secure application roles?
A secure application role is a special Oracle Database role that can be enabled based on the evaluation of an Oracle Database Vault rule set.
27) What are the steps taken to secure database using Oracle database vault access control component?
1) Create a realm composed of the database schemas or database objects that we want to secure. 2) We can further secure the realm by creating rules, command rules, factors, identities, rule sets, and secure application roles.
3) We can run reports on the activities these components monitor and protect.
28) What is a Oracle database vault administrator(DVA)?
Oracle Database Vault Administrator(DVA) is a Java application that is built on top of the Oracle Database Vault PL/SQL application programming interfaces (API).
29) What is the use of DVA?
1) DVA allows security managers who may not be proficient in PL/SQL to configure the access control policy through a user-friendly interface.
2) Oracle Database Vault Administrator provides an extensive collection of security-related reports that assist in understanding the baseline security configuration.
3) These reports help to point out deviations from this baseline.
30) What is Oracle Database Vault Configuration Assistant (DVCA)?
It is a command-line utility that is used to perform maintenance tasks on Oracle Database Vault Installation.
31) What are the Oracle Database Vault schemas?
Two major schemas of oracle database vault are DVSYS and DVF
32) Give details on DVSYS schema:-
DVSYS schema stores objects needed to process Oracle data for Oracle database vault.This schema contains the roles,views,accounts,functions, and other database objects that the Oracle Database Vault uses
33) Give details on DVF schema:-
The DVF schema contains public functions to retrieve (at run time) the factor values set in the Oracle Database Vault access control configuration.
34) Give details on Oracle Database Vault PL/SQL Interfaces and Packages :-
Oracle Database Vault PL/SQL Interfaces and Packages are collections that allow security managers,application developers to configure security policy.The PL/SQL procedures and functions allow general database accounts to operate within the boundaries of the access control policy in the context of a given database session.
35) How LogMiner Utility works?
LogMiner reads the redo log files and puts the extracted contents into the v$LOGMNR_CONTENTS view.We can query the v$LOGMNR_CONTENTS view to obtain information about the transaction we’d be interested in.The information in redo logs is in the form of internal object identifier and data is in hexadecimal form.
Oracle recommends that we provide logMiner with access to the data dictionary so it can translate the contents of the reolog file into a form we can readily understand.
We can provide LogMiner with access to the data dictionary in three different ways :
1) We can extract data dictionary to a flat file
2) We can have a dictionary snapshot placed in the redo logs
3) We can do away with the extraction of the data dictionary and direct logMiner to use the online data dictionary
36) What are the database objects supported in latest version of Oracle database?
Oracle database the relational database management system supports RDBMS as well as object RDBMS objects. To start with it supports tables, indexes, views, index organized tables, clustered tables, java, stored procedures, functions, triggers, synonyms, database link, sequences, materialized views to name a few
Tables – Basic object that stores information . Table is composed of rows and columns
Views – A query stored in database admin tables called data dictionary. This doesnt consume any space and is executed on the fly
Indexes – Increase performance by storing reference to table rows
Index organized tables – Tables and indexes created together
Cluster – Some common columns are shared among tables. This forms cluster of tables
Synonym – An alias to database object
Materializd views – stores database object and has view basics. This forms basics of datawarehousing. Lets look at it in detail
java – The programming code stored in database
procedure – An business logic written in pl/sql language that a database understands.PL/SQL is oracle specific. Procedure implements a business logic and doesnt return any value
function – Pl/SQL program unit that returns a value
trigger – a database object that gets executed when a particular database event like logon,logoff etc happen. Triggers can trigger before or after an event
Database Link – This database object establishes connection and helps in sharing information among two different databases
Index-Organized (IOT) Table – Index-Organized (IOT) Table is a table whose physical storage is organized like an index.IOT stores data according to the primary key defined on tha table (sorted order).
37) What parameters are needed for dynamic service registration/automatic service registration?
The init.ora file or SPFILE should contain the following parameters for automatic service registration:
2) INSTANCE_NAME (eg: hr)
38) What if we don’t specify the value of SERVICE_NAMES?
If we don’t specify the value of SERVICE_NAMES parameter it defaults to the global database name
100) What is a global database name?
A global database name is a combination of DB_NAME and DB_DOMAIN parameters
39) How to retain case-sensitivity in Oracle database?
While we create objects in an Oracle database, we specify object names. Oracle database object names are case-sensitive. SQL*PLUS , SQL Developer tools will automatically convert lower case letters to upper case letters. If the name is specified within double quotes, case is retained.
As a summary object names in Oracle database are case sensitive
SQL> create table case (“today’s date” date);
Table created.
SQL> create table “case” (today date);
Table created.
SQL> select table_name from dba_tables where table_name=’case’;
SQL> select table_name from dba_tables where lower(table_name)=’case’;
40) How is data coded in oracle database?
A character set is a defined encoding scheme for representing characters as a sequence of bits.Data stored in the database must be coded into a character set.Oracle is independent of the operating system character set. It is shipped with its own character set.A character set consists of defined number of distinct characters. The number of characters that a character set can represent is limited by the number of bits the character set uses for each character
A single-byte character set will use only one-byte (8bits) per character.A multi-byte character set uses one,two,three bits per character.Oracle s default character set is seven bit ASCII or seven-bit EBCDIC
Oracle 10g ships with more than 250 character sets
41) Give details on tables and their relationship to segments in an oracle database :-
Table is the database object used to store information in the form of rows and columns. We can create tables in a schema and dba_extents can be used to obtain information about the tables.
SQL> create table date);
Table created.
SQL> select tablespace_name,file_id,extent_id,block_id,blocks,bytes from dba_extents where owner=’SYS’ and segment_name=’NEW’;
——- ———- ———- ———- ———-
SYSTEM                                  1          0      87576          8       65536
In Oracle database tables can be created in a schema. They are stored in form of segments. By default these tables are heap-organized tables.
42) What is the use of archiver process in an oracle database?
Archives the online redo log files to the archiving destination
There can be n archiving destinations – LOG_ARCHIVE_DEST parameter
Archives are used to restore and recover the database upon crash/failure
Database can be run in ARCHIVE/NOARCHIVE mode .It is a Safety measure
ARCHn processes depending on the number of archival destinations
I tried configuring the flash recovery area. First step in creating the flash recovery area is to set the value of log_archive_dest parameter. It is essential to set the value of db_recovery_file_dest before setting the value of log_archive_dest
SQL> alter system set log_archive_dest=’ ‘;
alter system set log_archive_dest=’ ‘
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or
SQL> alter system set db_recovery_file_dest=”;
System altered.
SQL> alter system set log_archive_dest=”;
System altered.
To create a package in a user’s schema the privilege needed is CREATE PROCEDURE. I had a misconception that it must be CREATE PACKAGE. I wanted to share this information with you all
SQL> connect sys/password as sysdba
SQL> grant create package to user;
grant create package to user
ERROR at line 1:
ORA-00990: missing or invalid privilege
SQL> grant create procedure to practice;
Grant succeeded.
SQL> connect user/password
SQL> create or replace package test_package as
2 procedure set_test;
3 end;
4 /
Package created.
43) How to create Table With LONG Datatype Column?
Tables are the basic objects used to store information in the form of rows/records in a database. We can create a table with one or more column of many different data types. LONG is one such datatype. It is an extended version of the most popular varchar2 data type with a maximum size of 2GB. IT is deprecated and is available in Oracle database 11g version for backward compatability. Here is an example of creating table with LONG datatype column
SQL> create table test_LONG(
2 id number,
3 testlong LONG);
Table created.
SQL> insert into test_LONG values(10,’LONG Value example’);
1 row created.
SQL> desc test_LONG;
Name Null? Type
—————————————– ——– —————————-
Flashback Drop feature is automatically enabled in Oracle Database 10g
44) What is a statement level read consistency in an oracle database?
Statement-level read consistency is the default.All the data that a query sees come from a single point in time. Query will see consistent data
In simple terms, SELECT,INSERT,UPDATE,DELETE statements will see the commited data. Any commits in due course will not affect the transaction which has already started
45) How to fix ORA-02421: missing or invalid schema authorization identifier?

I tried creating a schema with authorization and got the following error. Sequence of steps and fix have been discussed here
SQL> create schema authorization play;
create schema authorization play
ERROR at line 1:
ORA-02421: missing or invalid schema authorization identifier
SQL> create user play identified by play;
User created.
SQL> grant create session to play;
Grant succeeded.
SQL> connect play/play
SQL> create schema authorization play;
Schema created.

46) What is use of bitmap index?

Bitmap index is widely used in case of true or false scenarios like gender column. In addition it provides best performance in case of read only operations in systems like datawarehousing. However, benefits of bitmap index is application based

47) What does ORA-00928: missing SELECT keyword mean?
I tried joining two tables using UNION set operator and got this error. This is because I used a wrong syntax. Result set of two select statements can be combined together using an Union set operator
SQL> select * from t1 union t2;
select * from t1 union t2
ERROR at line 1:
ORA-00928: missing SELECT keyword

This change in syntax fixed the problem.
SQL> select name from t1 union select location from t2;

48) When does ORA-01950: no privileges on tablespace ‘SYSTEM’ occur?
I created an user, granted him create session, create table privilege. Now I connected to the user schema and tried creating a table. By default if no tablespace is assigned to an user he is assigned space in default tablespace. In such case it is necessary to specify the user quota in this default tablespace. Altering the user and allocating him quota in the default tablespace fixed the issue.
SQL> create user temp identified by temp;
User created.
SQL> grant create session to temp;
Grant succeeded.
SQL> grant create table to temp;
Grant succeeded.
SQL> connect temp/temp;
SQL> create table test(id int primary key,name varchar2(10));
create table test(id int primary key,name varchar2(10))
ERROR at line 1:
ORA-01950: no privileges on tablespace ‘SYSTEM’

SQL> alter user temp quota unlimited on system;
User altered.
SQL> connect temp/temp
SQL> create table test(id int primary key,name varchar2(10));
Table created.

49) How to collect html output form Oracle database tables?
Oracle database offers an easy sql*plus option to generate html output from sql prompt
set markup html on
spool output.html
select * from dual;
spool off
set markup html off
The above command does produce output in html format
Utilities like UTL_FILE does create output in csv, text format
50) What is the use of oratab file in an oracle database environment?
The file located in /etc/ on Linux and most of the UNIX systems, /var/opt/oracle/ in a solaris environment is the control file that is made use of by tools like database configuration assistant DBCA, database upgrade assistant DBUA. This file lists the databases that are installed on a particular server environment. In case of conflicts during a new database install or upgrade, check this file and edit this manually to comment or remove entries to fix conflicts
51) You are using DBUA to upgrade a database from to latest 12c. Will this automatically update the COMPATIBLE parameter value?
Yes. As far as the upgrades are concerned the database in version will have COMPATIBLE value set to 11.0.0 for upgrade purpose and this is taken care of automatically
52) You are using DBUA to upgrade a database from to latest 12c. Will this automatically update the COMPATIBLE parameter value?
The DBUA will not change the COMPATIBLE parameter and we need to manually make this change by setting COMPATIBLE to 12.1.0 for the database to utilize all 12c features
53) When is max_string_size set to extended?
By default oracle database 12c supports 32KB for datatypes like VARCHAR2,NVARCHAR2,RAW. To store over 4000 bytes this parameter value needs to be set to extended
54) Your 12c database is in open mode. You want to set max_String_size to EXTENDED. How will you do that?
For this changes to happen the database should be in upgrade mode. If database is in open mode, get a maintenance window and perform the following steps
Shutdown the database using immediate option. This is default in most cases
shutdown immediate;
export ORALCE_SID=value – Linux and unix systems
set ORACLE_SID=value – In windows systems
Log into database using an user with sysdba privilege
sqlplus ‘/ as sysdba’
Bring back database in upgrade mode
startup upgrade pfile=’specify location of this file’;
Change the value of max_String_size
Run the utl32k.sql script. This script needs to be run as an user with SYSDBA privilege. As we already logged in like that proceed further
Shutdown the database and startup the database in normal mode
shutdown immediate;
Make sure there are no invalid objects
select * from dba_objects where status<>‘VALID’;
select * from dba_objects where status=’INVALID’;
Make sure the above command returns No Rows
55) What are the possible values of max_string_size?
This parameter can take one of the two values – STANDARD, EXTENDED
56) You have set the value of max_String_Size to EXTENDED. Can you change it back to standard?
Once set max_string_size can’t be changed back to STANDARD. This parameter sets storage value of datatypes and this will have an impact
57) You are in process of performing an upgrade and see that there is low space in fast recovery area. DBUA tool is being used for upgrade purpose. How can you fix this issue?
During the process of upgrade it becomes necessary to monitor the space usage in fast recovery area. Also, check the alert.log file for errors related to space issues. As a first step to avoid space issues the database needs to be set to NOARCHIVELOG mode before upgrade. In case you still get space issues in fast recovery area during database upgrade using DBUA follow these steps:
export ORALCE_SID=value – Linux and unix systems
set ORACLE_SID=value – In windows systems
Log into database as sysdba user
sqlplus ‘/ as sysdba’
Increase the size of fast recovery area as follows :
alter system set db_recovery_file_dest_size=50g; (this is a new sample size. set values accordingly)
58) What are the uses of v$sysaux_occupants.move_procedure procedure?
As the name suggests this component is used for moving purpose like :
a) The procedure may be used for some components to relocate component data to the SYSAUX
tablespace from its current tablespace
b) The procedure may be used for some components to relocate component data from the SYSAUX
tablespace to another tablespace
59) How is the service registration performed with listener?
The service registration with oracle net listener is taken care of by the process monitor PMON process of each and every database instance as they get installed in an oracle server
60) Which commands are used to perform backup and restore of ASM metadata?
Using commands md_backup and md_restore ASM metadata is backedup and used for restore and recovery regularly. This information comes handy during restore and recovery of a failed diskgroup. RMAN backup can be used in combination with the md_restore command to perform the restore and recovery of a failed disk group
61) One of the default tablespaces in a pluggable database is corrupt. Does this demand complete database restart?
No. The corrupt tablespace of the pluggable database can be made offline
62) In your oracle database unified auditing has been enabled. Which oracle component is audited by default?
By default oracle RMAN the recovery manager is audited once the unified auditing option is enabled
63) Your container database runs in ARCHIVELOG mode with some pluggable databases. The SYSAUX tablespace of the root container goes corrupt. How to perform restore and recovery of the SYSAUX tablespace?
Perform the following steps to bring back SYSAUX tablespace online:
a) Take the corrupt SYSAUX tablespace offline
b) Restore the datafile from last VALID backup
c) Apply the archived reso logs to make it consistent
d) Bring back the SYSAUX tablespace online
64) In your oracle database you have enabled multiprocess multithreaded architecture. What advantage do you get from this feature?
Once the multiprocess multithreaded architecture is enabled it brings the following benefits:
a) Improved parallel execution performance
b) Reduced CPU utilization
c) Reduced virtual memory utilization
65) You have defined global partitioned B-tree indexes in a large heap table that you would like to move to a new tablespace with minimal impact. What happens to the indexes after this move?
Once the partitions are moved, global indexes must be manually rebuilt