TABLESPACE MANAGEMENT

TABLESPACE MANAGEMENT

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

Tablespaces that record extent allocation in the data dictionary, are called dictionary managed tablespaces ie Increase IO, and tablespaces that record extent allocation in the tablespace(datafile) header, are called locally managed tablespaces ie decrease IO

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

PCTFREE and PCTUSED are the block storage parameters

PCTFREE- This parameter is used to specify how much space should be left in the block for updates.The default value is 10.

For Eg, if The PctFree for a block is 30, then Oracle keeps on adding new rows to the block until the block is 70 % full.

It leaves the 30% for future updates. That is, in case, any row is updated and requires some more space, it is taken

out from the 30% remaining in the Block. Specify the value of this parameter HIGH if future updates in the rows of

the table are going to need more space. In case ur table is less prone to updates, then this value can be specified LOW.

PCTUSED : Oracle stops INSERTING new Rows in a table when the space usage reaches the PCTFREE limit and removes it from the free list.

The data block is again added to the free list when the used space in it falls below the percent value set by the PCTUSED parameter. The default value is 40.

Once the data block is added to the free list, Oracle again starts inserting new rows into it.

Hence, if a large value is set for PCTUSED, the data blocks are more frequently returned to the free list and vice versa.

For Eg,suppose u have specified PCTUSED as 40 %. and PCTFREE as 20 %.

1. Oracle will keep on inserting new rows till the space is 80 % used. It will reserve the remaining 20% for future updates.

2. To start adding new rows again to the block, Oracle will check the space in the Block and the PCTUSED parameter.

3. When the space falls below 40 %, Oracle will start adding new rows to the block.

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 recovery when 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 manageability for VLDB by providing terabytes size to a single datafile itself

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 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 it’s 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?

NLS_TIMESTAMP_TZ_FORMAT

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?

Not possible

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?

Not possible