What is View? Does view occupy space in database? Ans: This is very simple questions but actually it is tricky because generally people are forgetting about materialized view during interview question replying and actually question is asked for all kind of views. View is logical structure. Only definition of view is stored in metadata of database. Due to this reason, view doesn’t occupy space in database. But materialized view occupies space in database because materialized [...]

Where I can find out init.ora of my Oracle database? Or What is the default location of parameters files of Oracle like init.ora or spfile.ora? Ans: In Windows server, we can find out parameter files in %ORACLE_HOME%\database folder and on Unix or Linux server we can find out in $ORACLE_HOME/dbs folder. Remember, default locations of parameter files like init.ora and spfile. Offcourse, these are default location of files means there is possibility to database server [...]

Which factors are to be considered for creating index on table? How to target column for indexing? Ans: There are lots circumstances causing to create index on table like size of table and volume of data. If size of table is large and we need only few data for selecting or in report then we need to create index.  There are some basic reason of selecting column for indexing like cardinality and frequent usage in [...]

How to re-organize schema which has more than 300 objects? Ans: Simple technique is export/import or datapump for re-organization of schema. We can take logical backup of schema using export or datapump. After taking export, recreate schema with same privileges and roles. After recreating schema, we can import data from export or datapump utility. Remember, re-organization of schema using export/import or datapump is very simple and easy method but for using this technique, you need [...]

How to re-organize schema which has more than 300 objects? Ans: Simple technique is export/import or datapump. We can take logical backup of schema using export or datapump. After taking export, recreate schema with same privileges and roles. After recreating schema, we can import data from export or datapump utility. Remember, re-organization of schema using export/import or datapump is very simple and easy method but for using this technique, you need to recreate schema. This [...]

What is Change Vector number in Oracle database and what is the use of change vector? Ans: When any transaction starts, change vector is created by Oracle for keeping track of database changes. A change vector holds single change made to any single block in database and it includes data block version number, actual address of data block, the operation code of transaction including Undo segment id with various other information. Oracle stores change vector [...]

What is the difference between Schema and User in Oracle database? Ans: Schema is logical identity of user which is describing user’s workarea and creativity of objects. Schema simply point out ownership of database objects. Remember, this is very tricky interview question because user and schema both are physically same. You can describe ownership as schema like who is owner of this table, who is owner of this procedure, who is owner of index, etc.

I have 400 GB database and want to configure backup, what disk space I need on local server? Ans: Backup space depends on type of backup. If you want to configure user managed cold or hot backup then exact size of database disk space is requiring. If you want to deploy logical datapump or export backup then not more than 10 GB space require. If you configure RMAN then size of backup would depend on [...]

What would be result, if I fired select numeric 5 from emp table? Does it give error because there is no column with name numeric 5? If emp table contains 14 rows then we would get result numeric 5 for 13 times. Means we get result of number of rows of EMP table. We don’t get any error when we are executing numeric value in select statement with or without any other column. It just [...]

Welcome to Oracle DBA Interview Questions website. Here you can find out every useful tips about cracking technical interviews and other type of interviews. Basically, many types of interviews are being conducted nowadays like telephonic, personal, technical round, client round, manager and team lead round, interview with live chat and video, etc. In short, we can say, interview process is becoming more complicated and complex. It is not easy to crack interview so easy when [...]