Wednesday , July 26 2017
Home / Oracle DBA Interview Questions / ORACLE TABLESPACE INTERVIEW QUESTIONS

ORACLE TABLESPACE INTERVIEW QUESTIONS

TABLESPACE MANAGEMENT

  1. What is the difference between dictionary managed tablespace and locally managed tablespace?

    1. In dictionary managed tablespace, free block information is maintained in data dictionary cache there by increases IO. In locally managed tablespace, the same information is maintained in datafile header itself there by reducing the IO. Hence LMT is better than DMT

  1. What is coalescing and which process will handle this?

    1. Combining free blocks together is called coalescing and SMON will handle this only if ASSM is enabled for the tablespace

  1. Can we create dictionary managed tablespace in 10g/11g?

    1. Yes, but SYSTEM tablespace also should be dictionary for that

  1. Explain PCTFREE and PCTUSED?

    1. PCTFREE is the reserved space which is to be used by rows for future updates

    2. PCTUSED is the level of data in the block which allows further data insertion only after the level decreases beyond that

  1. Can we take SYSTEM and SYSAUX tablespace offline?

    1. We can take SYSAUX, but not SYSTEM

  1. How to change the segment management type for an existing tablespace?

    1. Once defined, we cannot change segment space management for any tablespace

  1. 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?

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

  1. What happens when you take a tablespace/datafile offline immediate?

    1. Any existing transactions will be stopped and datafiles of this tablespace need recoverywhen we make them online

  1. How to identify which datafiles are modified today?

    1. By looking at the timestamp of the files at OS level

  1. Is it possible to reduce the size of datafile?

    1. Yes, but data level should not have been crossed the limit

  1. What is the new feature of 11g tablespace management?

    1. Encryption of the tablespace

  1. What is bigfile tablespace and its use?

    1. It gives easy managebility for VLDB by providing terabytes size to a single datafile itself

  1. How will do capacity planning for your production databases?

    1. We will take every quarter for normal databases and every week for critical and fast growing databases

  1. What is ORA-1555 error and how to avoid that?

    1. It is snapshot too old error which will occur if data is not found in undo or datafile by a select statement. Increasing undo tablespace size, undo retention time, using retention gurantee clause with DML statement are the solutions

  1. If a transaction is running and DBA has taken particular rollback segment offline. What happens to that transaction?

    1. It will start using SYSTEM tablespace

    2. 16.How to convert dictionary managed permanent tablespace to temporary tablespace?

    1. It is not possible

  1. You are trying to add a datafile, but its not allowing and saying you cannot add it. What might be the reason?

    1. Either MAXDATAFILES controlfile parameter or DB_FILES parameter in pfile might have reached its max value

  1. What is OMF? What are its advantages and disadvantages?

    1. It helps in managing files by oracle automatically. But naming convention will be the problem

  2. How you will get timezone of database?

    1. NLS_TIMESTAMP_TZ_FORMAT

  1. How will you find out the current users who are using temporary tablespace segments?

    1. By checking in v$tempseg_usage

  1. How to drop a datafile without dropping a tablespace?

    1. SQL> alter database datafile ‘path’ offline drop;

  1. How to check SCN of the database?

    1. SQL> select current_scn from v$database;

  1. Can we make temporary tablespace read only?

    1. Not possible

  1. What is the relation between db_files and maxdatafiles?

    1. Both will specify how many max datafiles can be there in the database

  1. When a tablespace is offline, can we make it read only?

    1. Not possible

ORACLE REDOLOG INTERVIEW QUESTIONS

Read More:
ORACLE BACKUP & RECOVERY

ORACLE DATAPUMP

ORACLE EXPORT & IMPORT

Comments

comments

Check Also

ORACLE BACKUP and RECOVERY INTERVIEW QUESTIONS

BACKUP & RECOVERY INTERVIEW QUESTIONS 1.What is the difference between cold and hot backup? a.COLD …

Leave a Reply

Your email address will not be published. Required fields are marked *