100 Important Oracle Architecture Interview Questions for Freshers & Experienced

100 Important Oracle Architecture Interview Questions for Freshers & Experienced

Q – 1 Explain what is the function of Redo Log?

Ans- Redo log is a part of physical structure of oracle. its basic function is to record all the changesmade to daatabase information. wheneveer an abnormal shutdown take place preventing system to update the database, changes can be obtained from redolog and hence the changes are not lost.

Q – 2 What is a Redo Log in Oracle Architecture?

Ans- Oracle maintains logs of all transactions against the databse. These transactions are recorded in files called Online Redo log files.These logs are used to recover the database’s transaction in their proper order in the evnt of database crash. The redo log information is stored in Redo log file which is external to the datafilesEach database have two or more online redo log files.

oracle writes tedo log file in cyclic order after the first log is filled , it writes to the second log files, until that one is filled. whn all of the online redo log file have been filled it move to the first online redo log file and start overwriting the transaction recordNote : if the Databse is in ARCHIVE log mode then database will make the copy of the redo log file before overwriting the contents of the log file..These ARCHIEVED redo log file can then be used to recover any part of the database to any point of time.

Q – 3 Explain what is remote login user and local user in database? And sid is used when user want to login as local user and Glodal database name is used when user wants to login as remote user..

What does it mean?

Ans- When you have distributed environment,you may access one database from other.This is normally done via db link.So lets say you have flight database and customer dabatabase.Now from customer database you intend to access flight database then you have to create a database link in customer database providing the login credentials for flight database which would mean that you are mentioning the user name(say flight_admin) of flight database in customer database.

Hence the remote user(in this case flight_admin).You may require to mention the domain name in the definition if the domain of both the databases are different else its ok.Local user,as the name suggest is the local user of the database residing within that database.

Q – 4 How to identify database size when you create a database first time?

Ans- 1. Sum of the data files select sum(bytes/1024/1024/1024) ‘Size in GB’ from dba_data_files;

2. Actual data size:select sum(bytes/1024/1024/1024) ‘Size in GB’ from DBA_segments;

Q – 5 Explain what are the components of Logical database structure of ORACLE database?

Ans- There are two types of database

1) logical database
2) physical database

logical database consist of tablespaces….and a tablespace consist of one or more segments….a segment consist of one or more extents…..an extent consist of contigious oracle data blocks.
logical database contain objects of users such as tables,indexes etc.

Q – 6 Tell me how to encrypt and decrypt oracle procedure?

Ans- The WRAP utility ‘wraps’, or hashes, the PL/SQL source code, but will not ‘unwrap’ it to restore the plain text starting point. Oracle can decipher this ‘translation’ internally, however to edit the code one must retain the plaintext source.WRAP is intended to obfuscate source code distributed to client sites in an effort to prevent prying eyes from seeing the underlying logic and to prevent any unauthorized code modification by the client.Again, there is no UNWRAP utility, so, once code is WRAPed it remains such to the naked eye.

Q – 7 What is Database Link in Oracle?

Ans- Data Base Link. A DB link is reference to the remote database, which can be located on completely different place.It is allows local user to access the remote database. create database link connected to Identified by using

Q – 8 Tell me what are the Large object types supported by Oracle?

Ans- Blob datatype is used for storing large Binary data Like Photos,signatures etc Clob datatype is used for stoeing large character data upto 4GB like resume and other docs

Q – 9 Tell me the relationship among Database, Tablespace and Data file?

Ans- Databases, tablespaces, and datafiles are closely related, but they have important differences:

1) An Oracle database consists of at least two logical storage units called tablespaces, which collectively store all of the database’s data. You must have the SYSTEM and SYSAUX tablespaces and a third tablespace, called TEMP, is optional.
2) Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle Database is running.
3) A database’s data is collectively stored in the datafiles that constitute each tablespace of the database.
For example, the simplest Oracle database would have one tablespace and one datafile. Another database can have three tablespaces, each consisting of two datafiles (for a total of six datafiles).

Q – 10 Explain can a Tablespace hold objects from different Schemes?

Ans- Yes one tablespace can hold objects from different schemas.Eg. when we create any schema (user) than we can assign default tablespace like this for any user user we can use same tablespace.

Eg. SYSTEM tablespace in oracle database, which is used by all default users whos default tablespace are not mentioned. or
all user will user system tablepsace as default tablespace if their default tablespace is not mentioned at the time of user creation.

Q – 11 Explain what is the difference between SID and Global Database Name?

Ans- SID (System Identifier) : A SID (almost) uniquely identifies an instance. Actually, $ORACLE_HOME, $ORACLE_SID and $HOSTNAME identify an instance uniquely. The SID is 64 characters, or less; at least on Oracle 9i. The system identifier is included in the CONNECT_DATA parts of the connect descriptors in a tnsnames.ora file. The SID defaults to the database name.

Global Database Name : A database is uniquely identified by a global database name. Usually, a global database name has the form somename.domain. The global database name is the composit of db_domain and db_name.

Q – 12 Do you know what is the maximum number of CHECK constraints that can be defined on a column?

Ans- You can create any number of check constraintsseparated by AND, OR, or NOT to create more complex conditions.

Q – 13 Tell me what are advantage of Data Dictionary manage Tablespace?

Ans- The main advantage of using dictionary managed tablespaces is that in this we can give the size of the extents according to our need. We can modify extent size but in locally managed tablespace extents are of uniform size.

Q – 14 What are synonyms used for in Oracle?

Ans- Synonyms are used to : Mask the real name and owner of an object.

Provide public access to an object

Provide location transparency for tables, views or program units of a remote database.

Simplify the SQL statements for database users.

Q – 15 What is Global Database Name?

Ans- A database is uniquely identified by a global database name. Usually, a global database name has the form somename.domain. The global database name is the composit of db_domain and db_name.

Q – 16 What is SID (System Identifier)?

Ans- A SID (almost) uniquely identifies an instance. Actually, $ORACLE_HOME, $ORACLE_SID and $HOSTNAME identify an instance uniquely. The SID is 64 characters, or less; at least on Oracle 9i. The system identifier is included in the CONNECT_DATA parts of the connect descriptors in a tnsnames.ora file. The SID defaults to the database name.

Q – 17 From where does PMON gets the conformance about failure? How is the user process terminated?

Ans- When user process is terminated abruptly due to power cut or any other factors then oracle database instance goes down then PMON mandatory background process is activated i.e it will then writes the commited data into respective datafiles which we can call this process as rollforward. if the data is not commited then by the action of rollbackword it erases the data from datafiles.

Q – 18 What is a View in Oracle?

Ans- View is also a data base object which represent the subset of data from one or more tables.. when u select a view it internally refers the table on which it’s created ,,when u drop a table the index on that table will be dropped ,, but the view can’t dropped …….but if u want to drop view u can drop itdrop view ..;ok friends byeee

Q – 19 What is Table in Oracle Architecture?

Ans- Table is data base object which stores the data in the logical representatiom in form of rows n coluns which is existing in two dimension form… and is stored in the databse.

Q – 20 Explain can objects of the same Schema reside in different tablespaces?

Ans- Yes objects of the same schema can reside in different tablepaces. Becuase when we create any schema (user) we can assign many tablespaces to one shcema so objects (eg. tables) will create in different tablespaces.

Q – 21 What is Data Block in Oracle Architecture?

Ans- Data Block:At the finest level of granularity, Oracle stores data in data blocks (also called logical blocks, Oracle blocks, or pages). One data block corresponds to a specific number of bytes of physical database space on disk.

Q – 22 What is Private Database Link in Oracle?

Ans- Database link is created by a user in his schema,it has 2 types

1.private database link
2.public database link /global

private database link is access the object in separate user

syntax:

create database link connect to username identified by password using ‘db_name’;

Q – 23 If a user executes a query which is already being used by a view exactly will oracle use already existing view to retrieve data or use the query executed by user?

Ans- Oracle uses query executed by user because when you use a view name in an other query, it simply transform query.

select empid,name from emp; — original
create view emp_view as select empid,name from emp; —– view created as name emp_view

select * from emp_view where empid=100; ———- this quey transform into
select * from (select empid,name from emp) where empid=100;

In above query it uses view because we use view name.

If we execute original query ,It does not have any view any to interpret,hence it uses original query.

Q – 24 What is the diff b/w BTREE INDEX and BITMAP INDEX?

Ans- Bitmap indexes are more advantageous than b-tree indexes when the table has millions of rows and the key columns have low cardinality. bitmap indexes provide better performance than b tree indexes when queries often use a combination of multiple where conditions involving the OR operator, the table is read-only, or when there is low update activity on the key columns.

Q – 26 Explain Virtual tables?

Ans- Either virtual table is a temporary table or v$ or Xv$ views

Q – 27 What is the use of Redo Log Information in Oracle?

These ARCHIEVED redo log file can then be used to recover any part of the database to any point of time.

Q – 28 Where does the process dumps information about the error?

Ans- Errors are stored in alert.log file it can be shecked using the parameter background_dump_dest

Use show parameter background_dump_dest
for path of the alert.log in trace directory

Q – 29 What is schema in Oracle Architecture?

Ans- Schema is a Collection of Database objects of a user.Example: Tables, Views, Synonyms, Sequences, Clusters, Triggers, Procedures, Packages.

Q – 30 How does archive log file helps to make backup consistent?

Ans- If database is in Archive Log Mode the only it is possible to perform HOT (Online) Backup, however all transaction entries would be done in Redo File and eventually it will be written to Archive Log File, it also called Archived Redo Log files.Hence by backing up Datafiles/Redo Log Files/Control Files and Archived redo Log Files you can have a consistent backup. Only in caseof Cold backup you dont reuire Archive Redo Log files as database instance is down while performing Cold backup.
For more details you may refer – http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12007.htm

Q – 31 How to apply archive log file in Oracle?

Ans- Archive Log need to apply when you need to recover database.
SQL> startup mount
SQL> alter database recover
<>

Q – 32 What is the use of archive log file?

Ans- It is used for Point In Time Database Instance Recovery.

Q – 33 Which parameter specifies the number of DBWn processes?

Ans- The initialization parameter DB_WRITER_PROCESSES specifies the number of DBWn processes. The maximum number of DBWn processes is 20. If it is not specified by the user during startup, Oracle determines how to set DB_BLOCK_PROCESSES based on the number of CPUs and processor groups.

Q – 34 Can we add a column to view in Oracle?

Ans- No, you cant ALTER VIEW to add or remove columns.
Need to recreate view with new column using CREATE OR REPLACE view command

Q – 35 What is a Data Segment in Oracle?

Ans- The level of logical database storage above an extent is called a segment. A segment is a set of extents, each of which has been allocated for a specific data structure and all of which are stored in the same tablespace. For example, each tables data is stored in its own data segment, while each index data is stored in its own index segment. If the table or index is partitioned, each partition is stored in its own segment.

Q – 36 Explain what is the command to find the database that already present before connecting?

Ans- As a sys or system user,select name from v$database;As a normal user,select global_name from global_name;select ora_database_name from dual; – dual is a dummy table in oracleshow parameter db_name – has to be given in sql prompt.

Q – 37 When does the LGWR writes all redo-enteries to the redo-log buffer?

Ans- LGWR writes one contiguous portion of the buffer to disk.

LGWR writes:

1. A commit record when a user process commits a transaction
2. Redo log buffers
– Every three seconds
– When the redo log buffer is one-third full
– When a DBWn process writes modified buffers to disk, if necessary

Q – 38 Tell me what is the name of the back ground process that modifies the db buffer cache?

Ans- Background process that modifies the db buffer cache and not data file is Server process(it fetch the data blocks from database and get it on data buffer cache)Process which modifies/writes the data file: DBWR will write back the data to data file. (But this is not the question)

Q – 39 How to determine the physical architecture of oracle?

Ans- The Physical Architectureof the Oracle involves,

1)SGA i.e Shared Global Area or System Global Area part of the Memory of system which is used by the Oracle Database.

2)Background Processes: To start up the oracle data base these are the minimum background processes required.

a)PMON: Process Monitor releases all the reources and transactions(I/D/U) which is held by the user after disconnecting from the database.

b)SMON: System Monitor performs the failure of Instance Crash suppose if the Data missing in DF. SMON recognizes such inconvinence and writes the data from RF to DF

c)Checkpoint: All the DB buffer changes wil be written into the DF aftr issuing the checkpoint. Checkpoint will be issued on timely basis by DBA.

d)DBWR: It writes the DB buffer changes to DF aftr the check point.

e)LGWR: It writes the LOGBUFFER changes to the RF when rollback or checkpoint is encountered.

3)Database files: Oracle data base contains three types of files that are,

a)DataFile(DF)
b)Redolog Files(RF)
c)Control File(CF)

To start up DB one Datafile two Redologfile and one Control file is needed.

Datafile is the place where data gets stored permanentely.
Redolog file is the place where all the comitted data gets stores.
Control file stores the information about the DF & RF.

Oracle Architecture also comprises of the following,

1)Tablespace
2)Extents
3)Segments
4)Data Base Blocks

Q – 40 Explain what is the back ground process in oracle architecture?

Ans- The Five Mandatory Background Processes in Oracle Architecture are as Follows:

1.Database Writer(DBWR)
2.Log Writer(LGWR)
3.Check Point(CKPT)
4.System Monitor(SMON)
5.Processes Monitor(PMON)

Q – 41 How does the SMON process performs recovery after instance failure?

Ans- When hardware failure, software failue and shutdown abnormally happen… SMON taking care of Instance failure, coalesce failure space and deallocating temp segment start to recover instance Roll fwd with help of redologfiles for commited transaction and roll backward upto uncommited transaction with help of undotablespace.

coalesce failure space: De-fragmenting every 3 sec
deallocating temp segment: Sorting purpose

Q – 42 What is high watermark in oracle?

Ans- 1. High water mark is the maximum amount of database blocks used so far by a segment. This mark cannot be reset by delete operations.

2. Delete Table operation wont reset HWM.

3. TRUNCATE will reset HWM.

4. The high water mark level is just a line separate the used blocks and free blocks.

The blocks above the HWM level is free blocks, they are ready to use.
The blocks below the HWM level is used blocks, they are already used.

Q – 43 What are the Characteristics of Data Files in Oracle?

Ans- A data file is created when a ‘create tablespace’ or ‘alter tablespace’ command is executed. Creating a data file of size 10MB actually allocates 10MB space on the hard disk. If a tablespace is declared to contain 100 data files, one slot per data file is reserved in the control file. There are four types of data files — system data files, application (or user) data files, temporary data files (used by oracle to store temp data while executing complex SQL statements) and rollback data files.It is possible to resize a datafile. The command which will serve this purpose is

Q – 44 What is a Private Synonyms in Oracle?

Ans- A Private Synonyms can be accessed only by the owner.

Q – 45 What is a Public Synonyms in Oracle?

Ans- Public synonyms can be accessed by any user on the database.

Q – 46 What are the advantages of Views in Oracle?

Ans- Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.

Hide data complexity.

Simplify commands for the user.

Present the data in a different perspective from that of the base table.

Store complex queries.

Q – 47 What are the type of Synonyms in Oracle?

Ans- There are two types of Synonyms Private and Public.

Q – 48 What are Schema Objects in Oracle?

Ans- Schema objects are the logical structures that directly refer to the database’s data. Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.

Q – 49 Tell me how to define Data Block size?

Ans- stansard block size, which is set with parameter DB_BLOCK_SIZE cannot be changed after creating database. We can set non standard parameter size later with parameter DB_nk_BLOCK_SIZE and it can be changed.NOTE: Standard block size cnannot be equal to nonstandars block size.

Q – 50 Explain the different type of Integrity Constraints supported by ORACLE?

Ans- NOT NULL Constraint – Disallows Nulls in a table’s column.

UNIQUE Constraint – Disallows duplicate values in a column or set of columns.

PRIMARY KEY Constraint – Disallows duplicate values and Nulls in a column or set of columns.

FOREIGN KEY Constrain – Require each value in a column or set of columns match a value in a related table’s UNIQUE or PRIMARY KEY.

CHECK Constraint – Disallows values that do not satisfy the logical expression of the constraint.

Q – 51 What is self-referential integrity constraint in Oracle?

Ans- Self referential integrity constraint refers to two columns related by referential integrity which belong to the same table.For example ManagerId column in employees table is a foreign key which refers to EmployeeId in the same table.

Q – 52 What is an Index in Oracle?

Ans- An Index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

Q – 53 What is a Temporary Segment in Oracle?

Ans- temporary segments are basically used for sort operations in oracle,,when the user tries to execute the query through order by clause. so system needs an space for an sort operation, and system will use temporary segments for this query.

Q – 54 What is Row Chaining in Oracle?

Ans- If a row is too large to fit into a single database block row chaining happens. For example, if you use a 4KB blocksize for your database, and you need to insert a row of 8KB into it, Oracle will use 3 blocks and store the row in pieces. Some conditions that will cause row chaining are: Tables whose rowsize exceeds the blocksize. Tables with long and long raw columns are prone to having chained rows.

Tables with more then 255 columns will have chained rows as Oracle break wide tables up into pieces. So, instead of just having a forwarding address on one block and the data on another we have data on two or more blocks.

Q – 55 What is a Data File in Oracle?

Ans- The data of logical database, say tables,indeses etc are stored in datafile.they can be associated with only one database. once created their size can not be altered. but we can add new data file to the table space.

Q – 56 What are Clusters in Oracle?

Ans- Clusters are groups of one or more tables physically stored together because they share common columns and are often used together. This improves disk access time.The related columns of the tables in a cluster are called the cluster key. The cluster key is indexed so that rows of the cluster can be retrieved with a minimum amount of I/O.No matter how many tables within the cluster contain the cluster key value, it is stored only once each in the cluster and the cluster index.

Therefore, less storage is required.Whether or not a table is part of a cluster is transparent to users and to applications. Data stored in a clustered table is accessed by SQL in the same way as data stored in a nonclustered table.

Q – 57 What is a Segment in Oracle?

Ans- A segment is a set of extents that contains all the data for a specific logical storage structure within a tablespace. For example, for each table, Oracle allocates one or more extents to form that table’s data segment; for each index, Oracle allocates one or more extents to form its index segment.

Oracle databases use four types of segments, these are:Data SegmentsIndex SegmentsTemporary SegmentsRolback SegmentsData Segments: A single data segment in an Oracle database holds all of the data for one of the following: a table that is not partitioned or clustered a partition of a partitioned table a cluster of tables Oracle creates this data segment when you create the table or cluster with the CREATE command.

The storage parameters for a table or cluster determine how its data segment’s extents are allocated. You can set these storage parameters directly with the appropriate CREATE or ALTER command. These storage parameters affect the efficiency of data retrieval and storage for the data segment associated with the object. Index Segments: Every nonpartitioned index in an Oracle database has a single index segment to hold all of its data. For a partitioned index, every partition has a single index segment to hold its data.

Oracle creates the index segment for an index or an index partition when you issue the CREATE INDEX command. In this command, you can specify storage parameters for the extents of the index segment and a tablespace in which to create the index segment. (The segments of a table and an index associated with it do not have to occupy the same tablespace.)

Setting the storage parameters directly affects the efficiency of data retrieval and storage.Temporary Segments: When processing queries, Oracle often requires temporary workspace for intermediate stages of SQL statement parsing and execution. Oracle automatically allocates this disk space called a temporary segment. Typically, Oracle requires a temporary segment as a work area for sorting.

Oracle does not create a segment if the sorting operation can be done in memory or if Oracle finds some other way to perform the operation using indexesRollback Segments: Each database contains one or more rollback segments. A rollback segment records the old values of data that was changed by each transaction (whether or not committed). Rollback segments are used to provide read consistency, to roll back transactions, and to recover the database

Q – 58 What is Hash Cluster in Oracle?

Ans- Hash Clusters cluster table data in a manner similar to normal, index clusters. To find or store a row in a hash cluster, Oracle applies a hash function to the row’s cluster key value. The resulting hash value corresponds to a data block in the cluster.All rows with the same key value are stored together on disk.

Hash clusters are a better choice than using an indexed table or index cluster when a table is often queried with equality queries (for example, WHERE product_id=123). For such queries, the specified cluster key value is hashed. The resulting hash key value points directly to the area on disk that stores the rows.This reduces the amount of I/Os that must be performed to locate and read/write a row of data.

Q – 59 Explain what is a Sequence?

Ans- A sequence is an automatically generated unique number.It is typically used to create a primary key value.It is a sharable object.Using sequence change the increment value, maximum value, minimum value, cycle option, or cache option.

Q – 60 Tell me do View contain Data?

Ans- No, View never contain the the data, Only defination of it stores in the data base, when ever you invoke them they show you the data based on their defination.Only Materlized view or SnaptShot contain the the data.

Q – 61 Do you know what is an Extent?

Ans- An extent is a logical unit of database storage space allocation made up of a number of contiguous data blocks. One or more extents in turn make up a segment. When the existing space in a segment is completely used, Oracle allocates a new extent for the segment. When you create a table, Oracle allocates to the table’s data segment an initial extent of a specified number of data blocks.

Although no rows have been inserted yet, the Oracle data blocks that correspond to the initial extent are reserved for that table’s rows. If the data blocks of a segment’s initial extent become full and more space is required to hold new data, Oracle automatically allocates an incremental extent for that segment. An incremental extent is a subsequent extent of the same or greater size than the previously allocated extent in that segment. (The next section explains the factors controlling the size of incremental extents.) For maintenance purposes, the header block of each segment contains a directory of the extents in that segment.

Q – 62 Explain what is a Synonym?

Ans- Ya synonym is used to rename or to create alias for the data base object ..

Q – 63 Explain what are the Limitations of a CHECK Constraint?

Ans- A check constraint allows you to specify a condition on each row in a table. Note: A check constraint can NOT be defined on a VIEW. The check constraint defined on a table must refer to only columns in that table. It can not refer to columns in other tables. A check constraint can NOT include a SUBQUERY. A check constraint can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

Q – 64 Tell me what is difference between UNIQUE constraint and PRIMARY KEY constraint?

Ans- A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can’t contain Nulls.Unique key cannot be used to create referential integrity whereas primary key can do.

Q – 65 Tell me what are the uses of Rollback Segment?

Ans- Rollback segments are used to get data blocks to the original state when user issues a ‘rollback’ statementgive consistent data to a user, hiding uncommitted changes made by other users/sessionsto undo uncommitted changes during database recovery. After applying all changes recorded in the redo logs, Oracle uses rollback segments to undo uncommitted changes. Since rollback segments are stored in the database buffers, they are automatically protected by the redo log.

Q – 66 Explain what are the components of Physical database structure of Oracle Database?

Ans- The phycial structures of the database are

1.datafile
2.redologfile
3.control filedatafile:-
datafile contains actial data of the database.

redologfile:- it contains all changes done to the data base. it works in the circular fashion.
controlfile:- it contains status and structure of the database.

Q – 67 What is a Tablespace?

Ans- A tablespace is a group of segments and a segment is a group of extents and an extent is a group of blocks.a tablespace can occupy two physical datafiles.

Q – 68 Explain what is SYSTEM tablespace and when is it Created?

Ans- HelloAs far as i know, a system tablespace is the only tablespace which is created automatically, when the oracle DB is installed, no matter what the DB will contain atleast one tablespace and that is the system tablespace. Do let me know if u have any other answers.Praveen.R

Q – 69 What is a Data Dictionary in Oracle?

Ans- The data dictionary of an ORACLE database is a set of tables and views that are used as a read-only reference about the database.

It stores information about both the logical and physical structure of the database, the valid users of an ORACLE database, integrity constraints defined for tables in the database and space allocated for a schema object and how much of it is being used.

Q – 70 Tell me what does a Control file Contain?

Ans- The controlfile contains :

1. Name and Location of Datafiles, Redo Logfiles.
2. Timestamp of Database Creation.
3. Current log sequence number is recorded when log switches are occur.
4. Checkpoint information is recorded as checkpoints are made.
5. Database identifier is recorded when the database is created.

Q – 71 Explain what are the Referential actions supported by FOREIGN KEY integrity constraint?

Ans- UPDATE and DELETE Restrict –
A referential integrity rule that disallows the update or deletion of referenced data.

Q – 72 Tell us what is an Integrity Constrains?

Ans- Special requirements for table data:

1) allow NULL value
2) uniqueness of value
3) primary key
4) foreign key
5) special value size/text/length etc requirements
All information is stored in Data Dictionary.

Q – 73 Tell us what is the use of Control File?

Ans- Controlfile contains the information about the database like names and locations of datafiles, name of the database, database creation timestamp and apart from this the SCN are also reported into control during the database operation. Especially during the database startup the controlfile is used to gather the information about the database and while opening the database the information in the controlfile is validated to check for the consistency of the database.

Q – 74 Explain what is meant by snapshot error?

Ans- Yes as U said,If on one side a query is executed and simultaneously a DML is happening at the same time. THEN the user doing the DML action gives a commit in between while the query is still executing on the other side then, on the query side U get this message “SNAPSHOT TOO OLD”Suggestion :Alter system set UNDO_retention = 900 ;or give a large value for undo retention, ie., the undo block will be kept in the memory until 900 microseconds even after the commit transaction on the same table, so that the query ends within that.

Q – 75 Explain can an Integrity Constraint be enforced on a table if some existing table data does not satisfy the constraint?

Ans- Yes.Use Enable Novalidate to enforce the constraints. Existing data will not be checked, only new/update will be validated.

Q – 76 Do you know how are Indexes Update?

Ans- Index is a Database object . It can be accessed rows physical fast. when you move the table from different table spaces or same table spaces indexes will unusually status. So You compulsory rebuild the index. Rebuilding the index is very fast than drop and create the new indexes.

Q – 77 Explain what is Rollback Segment?

Ans- It stores your uncommited temporart data. ex. If you do any insert/update/delete and you won’t commit it is written in rollback segment.

Q – 78 What is Referential Integrity?

Ans- A rule defined on a column (or set of columns) in one table that allows the insert or update of a row only if the value for the column or set of columns (the dependent value) matches a value in a column of a related table (the referenced value). It also specifies the type of data manipulation allowed on referenced data and the action to be performed on dependent data as a result of any action on referenced data.

Q – 79 Explain the different type of Segments?

Ans- Data Segment,
Index Segment,
Rollback Segment and
Temporary Segment.

Q – 80 Explain what is an Index Segment?

Ans- It’s an oracle object as other object like table. It stores index key + rowid for that key. So when you scan index it finds respective rowid and fetch data from that rowid. and it’s faster

Q – 81 Tell me what is the difference between materialized view and snapshot?

Ans- A materialized view is a replica of a target master from a singlepoint in time. The concept was first introduced with Oracle7 termed asSNAPSHOT. In Oracle release 7.1.6 snapshots were enhanced to enableDMLs along with a new terminology, updatable snapshots. With Oracle8.1.6 snapshots started to be used in data warehouse environments so anew terminology materialized view was introduced to address bothdistributed and data warehouse materialized views.

Q – 82 Explain what is Public Database Link?

Ans- A database link is a pointer that defines a one-way communication path from an Oracle Database server to another database server. The link pointer is actually defined as an entry in a data dictionary table. To access the link, you must be connected to the local database that contains the data dictionary.

Related Articles