Top 25 Tablespace Interview Questions You Must Prepare 06.Dec.2023

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

SQL> alter database datafile ‘path’ offline drop;

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

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

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

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.

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.

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

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

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

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

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.

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

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

SQL> select current_scn from v$database;

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