What is the difference between dictionary managed tablespace and locally managed tablespace?
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
What is coalescing and which process will handle this?
Combining free blocks together is called coalescing and SMON will handle this only if ASSM is enabled for the tablespace
Can we create dictionary managed tablespace in 10g/11g?
Yes, but SYSTEM tablespace also should be dictionary for that
Explain PCTFREE and PCTUSED?
PCTFREE is the reserved space which is to be used by rows for future updates
PCTUSED is the level of data in the block which allows further data insertion only after the level decreases beyond that
Can we take SYSTEM and SYSAUX tablespace offline?
We can take SYSAUX, but not SYSTEM
How to change the segment management type for an existing tablespace?
Once defined, we cannot change segment space management for any tablespace
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 tune it
What happens when you take a tablespace/datafile offline immediate?
Any existing transactions will be stopped and datafiles of this tablespace need recoverywhen we make them online
How to identify which datafiles are modified today?
By looking at the timestamp of the files at OS level
Is it possible to reduce the size of datafile?
Yes, but data level should not have been crossed the limit
What is the new feature of 11g tablespace management?
Encryption of the tablespace
What is bigfile tablespace and its use?
It gives easy managebility for VLDB by providing terabytes size to a single datafile itself
How will do capacity planning for your production databases?
We will take every quarter for normal databases and every week for critical and fast growing databases
What is ORA-1555 error and how to avoid that?
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
If a transaction is running and DBA has taken particular rollback segment offline. What happens to that transaction?
It will start using SYSTEM tablespace
16.How to convert dictionary managed permanent tablespace to temporary tablespace?
It is not possible
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 might have reached its max value
What is OMF? What are its advantages and disadvantages?
It helps in managing files by oracle automatically. But naming convention will be the problem
How you will get timezone of database?
How will you find out the current users who are using temporary tablespace segments?
By checking in v$tempseg_usage
How to drop a datafile without dropping a tablespace?
SQL> alter database datafile ‘path’ offline drop;
How to check SCN of the database?
SQL> select current_scn from v$database;
Can we make temporary tablespace read only?
What is the relation between db_files and maxdatafiles?
Both will specify how many max datafiles can be there in the database
When a tablespace is offline, can we make it read only?