Interview Questions.

Q1. How Can You Enable A Trace For A Session?

Use the DBMS_SESSION.SET_SQL_TRACE or

Use ALTER SESSION SET SQL_TRACE = TRUE;

 

Q2. Name Three Advisory Statistics You Can Collect?

Buffer Cache Advice

Segment Level Statistics

Timed Statistics
 

Q3. Describe What Redo Logs Are?

Redo logs are logical and physical systems which can be designed to maintain all the modifications made to a database and are supposed to aid in the recovery of a database.

 

Q4. How Do You Reference A Parameter Indirectly?

To not directly reference a parameter use the NAME IN, COPY ‘constructed-ins to not directly set and reference the parameters cost’ Example name_in (’capital parameter my param’), Copy (’XXXX’,’Parameter my_param’)

Q5. Explain What Partitioning Is And What Its Benefit Is?

Partitioning is a method of taking massive tables and indexes and splitting them into smaller, more viable pieces.

 

Q6. What Is A Snapshot Log?

A photograph log is a table in the grasp database that is associated with the grasp table. ORACLE makes use of a picture log to music the rows which have been up to date inside the grasp desk. Snapshot logs are utilized in updating the snapshots based totally at the master table.

 

Q7. Explain The Difference Between Archivelog Mode And Noarchivelog Mode And The Benefits And Disadvantages To Each?

ARCHIVELOG mode is a mode that you may placed the database in for creating a backup of all tractions which have took place inside the database so that you can get better to any point in time. NOARCHIVELOG mode is essentially the absence of ARCHIVELOG mode and has the disadvantage of no longer being able to recover to any factor in time. NOARCHIVELOG mode does have the advantage of not having to write down tractions to an archive log and hence increases the overall performance of the database barely.

Q8. Explain The Use Of Table Functions?

Table functions are designed to return a fixed of rows via PL/SQL good judgment however are meant for use as a normal desk or view in a SQL declaration. They are also used to pipeline facts in an ETL technique.

 

Q9. What Is A Segment?

A phase is a set of extents allotted for a positive logical structure.

Q10. What Is Statement Auditing?

Statement auditing is the auditing of the effective machine privileges with out regard to mainly named gadgets

Q11. What Does Coalescing A Tablespace Do?

Coalescing is handiest legitimate for dictionary-managed tablespaces and de-fragments area by using combining neighboring free extents into large single extents.

Q12. What Are The Advantages Of Operating A Database In Archivelog Mode Over Operating It In No Archivelog Mode?

Complete database recovery from disk failure is viable best in ARCHIVELOG mode. Online database backup is viable handiest in ARCHIVELOG mode.

Q13. What Are The Basic Element Of Base Configuration Of An Oracle Database?

It consists of one or greater information documents. One or greater manage documents. Two or extra redo log files. The Database incorporates multiple users/schemas one or extra rollback segments one or extra tablespaces Data dictionary tables User objects (table, indexes, views etc.,) The server that get admission to the database includes SGA (Database buffer, Dictionary Cache Buffers, Redo log buffers, Shared SQL pool) SMON (System MONito) PMON (Process MONitor) LGWR (LoG Write) DBWR (Data Base Write) ARCH (ARCHiver) CKPT (Check Point) RECO Dispatcher User Process with associated PGS

Q14. Explain The Difference Between $oracle_home And $oracle_base.

ORACLE_BASE is the basis directory for oracle. ORACLE_HOME placed underneath ORACLE_BASE is where the oracle products live.

Q15. What Are The Database Administrators Utilities Available?

SQL * DBA – This allows DBA to monitor and control an ORACLE database.

SQL * Loader – It masses statistics from trendy operating device documents (Flat documents) into ORACLE database tables. Export (EXP) and Import (imp) utilities permit you to pass present data in ORACLE format to and from ORACLE database.

Q16. What Is The Maximum No Of Chars The Parameter Can Store?

The maximum no of chars the parameter can keep is most effective valid for char parameters, which may be as much as 64K. No parameters default to 23 Bytes and Date parameter default to 7 Bytes.

Q17. Explain The Use Of Setting Global_names Equal To True.

Setting GLOBAL_NAMES dictates how you would possibly connect to a database. This variable is either TRUE or FALSE and if it is set to TRUE it enforces database hyperlinks to have the equal name because the remote database to which they're linking.

 

Q18. What Is Database Clusters?

Group of tables physically stored collectively because they percentage common columns and are frequently used together is referred to as Cluster.

 

Q19. What Are The Referential Actions Supported By Foreign Key Integrity Constraint?

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

DELETE Cascade – When a referenced row is deleted all related based rows are deleted.

 

Q20. Explain About Horizontal, Vertical Tool Bar Canvas Views?

Tool bar canvas views are used to create device bars for man or woman windows. 

Horizontal tool bars are display at the top of a window, just under its menu bar. 

Vertical Tool bars are displayed alongside the left side of a window

Q21. What Is A Dba?

A DBA is a Database Administrator, and this is the maximum not unusual job which you find a database specialist doing. There are Development DBAs and Production DBAs.

A Development DBA typically works carefully with a crew of builders and receives more concerned in layout selections, giving recommendation on overall performance and writing appropriate SQL.

That can be satisfying at a human stage due to the fact you are part of a team and also you proportion the delight of the teams accomplishments.

A Production DBA (on the other hand) is responsible for retaining Databases inside an organisation, so it's far a completely tough and stressful job. He or she, often receives worried whilst all of the design choices were made, and has in reality to preserve matters up and running.

Therefore, of route, it is also a worthwhile job, both financially and in phrases of job delight. But it is a greater “lonely” job than being a Development DBA.

Q22. What Is Tablespace Quota?

The collective quantity of disk space available to the objects in a schema on a specific tablespace.

Q23. Explain The Difference Between A Function, Procedure And Package?

A feature and process are the equal in that they're intended to be a collection of PL/SQL code that carries a unmarried task. While a procedure does no longer ought to go back any values to the calling software, a function will return a unmarried cost. A package deal alternatively is a group of capabilities and procedures which can be grouped collectively based on their commonality to a commercial enterprise function or application.

 

Q24. What Is The Purpose Of The Product Order Option In The Column Property Sheet?

To specify the order of individual institution assessment in a move products.

Q25. What Are The System Resources That Can Be Controlled Through Profile?

The number of concurrent classes the person can set up the CPU processing time available to the user’s consultation the CPU processing time to be had to a single name to ORACLE made by a SQL declaration the amount of logical I/O available to the user’s session the quantity of logical I/O to be had to a single call to ORACLE made with the aid of a SQL announcement the allowed quantity of idle time for the user’s consultation the allowed amount of join time for the user’s consultation.

 

Q26. Compare And Contrast Truncate And Delete For A Table.

Both the truncate and delete command have the favored outcome of having rid of all of the rows in a table. The distinction between the 2 is that the truncate command is a DDL operation and just moves the excessive water mark and produces a now rollback. The delete command, alternatively, is a DML operation, in an effort to produce a rollback and as a consequence take longer to finish.

 

Q27. What Is The Difference Between The Sql*loader And Import Utilities?

These  Oracle utilities are used for loading statistics into the database. The distinction is that the import utility relies on the records being produced via another Oracle application EXPORT whilst the SQL*Loader software lets in information to be loaded that has been produced by means of other utilities from unique statistics sources in order that lengthy because it conforms to ASCII formatted or delimited documents.

 

Q28. If Two Groups Are Not Linked In The Data Model Editor, What Is The Hierarchy Between Them?

Two group this is above are the left maximum rank higher than the group that is to proper or beneath it.

Q29. What Is Default Tablespace?

The Tablespace to comprise schema objects created with out specifying a tablespace name.

Q30. Explain The Use Of Setting Global_names Equal To True?

Setting GLOBAL_NAMES dictates how you might connect to a database. This variable is both TRUE or FALSE and if it's far set to TRUE it enforces database links to have the equal call as the far off database to which they are linking.

Q31. What Is A Database Instance?

A database instance (Server) is a fixed of memory shape and background tactics that get admission to a hard and fast of database files. The process may be shared via all users. The memory shape that are used to shop most queried statistics from database. This helps up to enhance database overall performance by means of lowering the quantity of I/O accomplished against facts record.

Q32. Is It Possible To Insert Comments Into Sql Statements Return In The Data Model Editor?

Yes it's far possible to insert remarks into sq. Statements return in the records model editor

Q33. What Is The Difference Between A Temporary Tablespace And A Permanent Tablespace?

A brief tablespace is used for brief items such as kind systems at the same time as everlasting tablespaces are used to keep the ones objects intended to be used because the real objects of the database.

Q34. What Does A Control File Contain?

A Control document statistics the physical structure of the database. It carries the subsequent records. Database Name Names and places of a database’s files and redolog files. Time stamp of database introduction.

Q35. Explain Materialized Views And How They Are Used?

Materialized perspectives are objects which can be decreased sets of statistics that have been summarized, grouped, or aggregated from base tables. They are normally used in records warehouse or selection support structures.

Q36. Give The Two Types Of Tables Involved In Producing A Star Schema And The Type Of Data They Hold.

Fact tables and measurement tables. A fact table includes measurements while size tables will incorporate facts with a purpose to help describe the truth tables.

Q37. What Are The Different Levels Of Auditing?

Statement Auditing

Privilege Auditing 

 Object Auditing

Q38. How Can You Enable Automatic Archiving?

Shut the database Backup the database Modify/Include LOG_ARCHIVE_START_TRUE in init.Ora report. Start up the database.

Q39. What Is An Index? How It Is Implemented In Oracle Database?

An index is a database structure used by the server to have direct get entry to of a row in a desk. An index is routinely created when a unique of number one key constraint clause is specified in create desk command (Ver 7.Zero)

Q40. What Is Distributed Database?

A allotted database is a community of databases managed with the aid of more than one database servers that looks to a user as unmarried logical database. The records of all databases within the distributed database can be concurrently accessed and modified.

Q41. What Is The Use Of Analyze Command?

–To carry out one of these characteristic on an index, table, or cluster

– To gather facts approximately item used by the optimizer and save them within the statistics dictionary.

– To delete information approximately the item utilized by object from the statistics dictionary.

– To validate the structure of the item.

– To discover migrated and chained rows off the desk or cluster.

Q42. What Are The Different Modes Of Mounting A Database With The Parallel Server?

Exclusive Mode If the primary instance that mounts a database does so in different mode, simplest that Instance can mount the database. Parallel Mode If the first instance that mounts a database is started in parallel mode, other times that are started out in parallel mode also can mount the database.

Q43. What Are The Options Available To Refresh Snapshots?

COMPLETE – Tables are absolutely regenerated the use of the snapshots query and the master tables each time the picture referenced.

FAST – If simple photograph used then a picture log can be used to send the modifications to the picture tables.

FORCE – Default value. If feasible it performs a FAST refresh; Otherwise it will carry out a whole refresh.

 

Q44. A Table Is Classified As A Parent Table And You Want To Drop And Re-create It. How Would You Do This Without Affecting The Children Tables?

Disable the foreign key constraint to the figure, drop the table, re-create the desk, allow the overseas key constraint.

Q45. What Is The Use Of Image_zoom Built-in?

To manipulate photographs in photograph objects.