Interview Questions.

Q1. What Is The Relation Between Db_files And Maxdatafiles?

Both will specify what number of max datafiles may be there inside the database.

Q2. Can We Make Temporary Tablespace Read Only?

Not viable

Q3. How To Drop A Datafile Without Dropping A Tablespace?

SQL> adjust database datafile ‘course’ offline drop;

Q4. Can We Create Dictionary Managed Tablespace In 10g/11g?

Yes, but SYSTEM tablespace additionally must be dictionary for that.

Q5. What Happens When You Take A Tablespace/datafile Offline Immediate?

Any current transactions will be stopped and datafiles of this tablespace need recoverywhen we cause them to online.

Q6. A Select Query Is Executing And You Observed Temp Tablespace Is Getting Full. You Added 50gb, But Even That Is Eaten Up. What Is Your Next Action?

We need to see what that query is and if required we need to track it.

Q7. When A Tablespace Is Offline, Can We Make It Read Only?

Not viable.

Q8. How To Convert Dictionary Managed Permanent Tablespace To Temporary Tablespace?

It is not viable.

Q9. How To Identify Which Datafiles Are Modified Today?

By searching on the timestamp of the files at OS stage.

Q10. Explain Pctfree And Pctused?

PCTFREE is the reserved area which is to be used by rows for destiny updates.

PCTUSED is the extent of statistics in the block which lets in in addition facts insertion most effective after the extent decreases past that.

Q11. What Is The Difference Between Dictionary Managed Tablespace And Locally Managed Tablespace?

In dictionary managed tablespace, loose block information is maintained in records dictionary cache there by means of increases IO. In regionally controlled tablespace, the identical information is maintained in datafile header itself there via decreasing the IO. Hence LMT is better than DMT.

Q12. How Will You Find Out The Current Users Who Are Using Temporary Tablespace Segments?

By checking in v$tempseg_usage.

Q13. How Will Do Capacity Planning For Your Production Databases?

We will take every quarter for regular databases and every week for essential and fast developing databases.

Q14. What Is Coalescing And Which Process Will Handle This?

Combining free blocks collectively is referred to as coalescing and SMON will deal with this best if ASSM is enabled for the tablespace.

Q15. What Is Omf? What Are Its Advantages And Disadvantages?

It enables in dealing with files with the aid of oracle automatically. But naming convention will be the trouble.

Q16. Can We Take System And Sysaux Tablespace Offline?

We can take SYSAUX, but no longer SYSTEM.

Q17. What Is Bigfile Tablespace And Its Use?

It gives easy managebility for VLDB with the aid of offering terabytes size to a single datafile itself.

Q18. What Is Ora-1555 Error And How To Avoid That?

It is snapshot too vintage error with the intention to occur if statistics isn't always found in undo or datafile by means of a choose announcement. Increasing undo tablespace length, undo retention time, the use of retention gurantee clause with DML announcement are the answers.

Q19. How To Change The Segment Management Type For An Existing Tablespace?

Once defined, we can not change section area control for any tablespace.

Q20. If A Transaction Is Running And Dba Has Taken Particular Rollback Segment Offline. What Happens To That Transaction?

It will begin the use of SYSTEM tablespace.

Q21. How You Will Get Timezone Of Database?

NLS_TIMESTAMP_TZ_FORMAT

Q22. Is It Possible To Reduce The Size Of Datafile?

Yes, but statistics degree should not had been crossed the limit.

Q23. How To Check Scn Of The Database?

SQL> pick current_scn from v$database;

Q24. You Are Trying To Add A Datafile, But Its Not Allowing And Saying You Cannot Add It. What Might Be The Reason?

Either MAXDATAFILES controlfile parameter or DB_FILES parameter in pfile would possibly have reached its max cost.

Q25. What Is The New Feature Of 11g Tablespace Management?

Encryption of the tablespace.