Tablespace Management FAQs

1) What is Block or Database Block?

Storage area for storing rows

2) What is the unit of Block?

Bytes

3) What is the default Standard Block Size in Oracle 10g?

8KB

3) What is Non Standard Block Size?

Other than 8KB

4) How many Non-Standard Block Sizes are supported by Oracle? What are they?

Four: 2KB, 4KB, 16KB, 32KB

5) What is Extent?

A Collection of Blocks

6) What is Segment?

A Collection of Extents

7) What are the types of Segments?

Table, Index, Undo & Temporary

8) What is Table Segment?

It stores actual committed data

9) What is Index Segment?

It stores Indexes information which are created on tables

10) What is Undo Segment?

It stores pre-image value

11) What are the advantages of Undo Segment?

Provides Read Consistency, to rollback uncommitted transactions during (ROLL BACKWARD) by SMON

12) What is Temporary Segment?

Performing Sorting, Joins, Having, group by

13) What is Tablespace?

It is Logical collection of Segments & Physically related with data file

14) What are the types of Tablespaces?

Permanent, Temporary

15) What is Logical Structure?

Tablespace

16) What is the hierarchy of the Logical Structures?

Tablespace _ Contains _ Segments _ Contains _ Extents _ Contains Blocks

17) What are the Mandatory Tablespaces during Database Creation?

SYSTEM & SYSAUX

18) What are the Mandatory Tablespaces during Database Usage?

TEMPORARY, UNDO & Application (User) Tablespaces

19) What is SYSTEM Tablespace?

It contains Metadata Information (Data Dictionary)

20) What is SYSAUX Tablespace?

It contains Database Workload Information

21) When SYSAUX Tablespace is introduced?

Oracle 10g

21) What is Undo Tablespace?

It stores Pre-Image value

22) What is Permanent Tablespace?

It stores the data permanently

23) What are the types of Permanent Tablespace?

SYSTEM, SYSAUX, User defined tablespaces

24) What is Temporary Tablespace?

It stores the data temporarily during Sorting Operations, Join Operations

25) What is Temporary Tablespace Group?

It is Logical which contains more than one temporary tablespace at a time

26) What is Extent Management?

Allocating & Reallocating the Blocks

27) What are the types of Extent Management?

Locally Managed, Dictionary Managed

28) What type of Extent Management is preferred?

Locally Managed Tablespace (LMT)

29) What is Locally Managed Tablespace (LMT)?

In this the Extent Information will be stored in the form of Bitmaps in Data File Header

This Bitmap indicates whether free space is available in the block or not

30) What is Dictionary Managed Tablespace (DMT)?

In this the Extent Information will be stored in the Data Dictionary Tables

31) What is Recursive SQL?

Oracle writes internal queries to get the data from the Data Dictionary Tables

32) What are the drawbacks of Dictionary Managed Tablespace?

Results in the Contention of the Data Dictionary Tables

Size of the extents are managed automatically by the system

Changes to the extent bitmaps do not generate undo information

Avoid Recursive Space Management Operations

32) What is Segment Space Management?

Managing the space in the segments

33) What are the types of Segment Space Management?

Manual Segment Space Management, Automatic Segment Space Management

34) What type of Segment Space Management is preferred? Why?

Automatic, everything will be managed by Oracle

35) What are the drawbacks of Manual Segment Space Management?

We have to specify PCTFREE, PCTUSED

36) What is PCTFREE?

How much space is available for INSERTION?

37) What is PCTUSED?

How much space is available for UPDATION?

38) What is Auto Extend on with Tablespace?

When the tablespace is filled up it will allocate some space to tablespace

39) How to make a tablespace OFFLINE?

ALTER TABLESPACE <tablespace_name> OFFLINE;

40) What are the different options available when we make a tablespace OFFLINE?

NORMAL, TEMPORARY, TRANSACTIONAL, FOR RECOVERY

41) What is Tablespace OFFLINE NORMAL?

It performs Checkpoint

It can be made OFFLINE only when there are no error conditions in data files

42) What is Tablespace OFFLINE TEMPORARY?

It performs Checkpoint

A tablespace can be taken OFFLINE even if there are error conditions on data files

43) What is Tablespace OFFLINE IMMEDIATE?

It does not perform any Checkpoint

It requires Media Recovery before making tablespace ONLINE

44) Is it possible to make a Tablespace OFFLINE when the database is running in NOARCHIVELOG mode?

No

45) What is Tablespace OFFLINE FOR RECOVER?

It is deprecated & used for backward compatibility

46) What is Physical Structure?

Data File

47) What does a Tablespace is Logically Contains?

Segments, Extents & Blocks

48) What does a Tablespace Physically Contains?

Data Files

49) What are the types of Tablespaces with respect to the data files?

Small File Tablespace & Big File Tablespace

50) What is the small file tablespace?

Default Permanent tablespace

51) How many data files can exist in small file tablespace?

65535

52) What is big file tablespace?

It is a single data file which can store data up to 128TB

53) How many data files can exist in big file tablespace?

Single

54) What are the advantages of Big File Tablespace?

One Tablespace has only one data file

No need to constantly add data files to Tablespace

Data File Management in Large Databases is simplified

Storage Capacity is Increases

55) What is LOGGING with respect to the tablespace?

Changes are tracked in Online Redo Log Files

56) What is NOLOGGING with respect to the tablespace?

Changes are not tracked in Online Redo Log Files

57) What is UNIFORM with respect to the tablespace?

Extents are allocated at a uniform rate which is specified by the user

58) What is AUTO ALLOCATE with respect to the tablespace?

Extents are allocated automatically by the oracle

59) What is the default option for allocating the extents that is UNIFORM or AUTO ALLOCATE?

Auto Allocated

60) Is it possible to Drop/Rename SYSTEM/SYSAUX Tablespace?

No

61) What is Default Permanent Tablespace?

It is allocated to a user who has been not allocated to any permanent application tablespace

62) What is Default Temporary Tablespace?

It is allocated to a user who has been not allocated to any temporary tablespace

63) How to move a table from one tablespace to another tablespace?

ALTER TABLE <table_name> MOVE TABLESPACE <tablespace_name>;

64) How to move an index from one tablespace to another tablespace?

ALTER INDEX <index_name> REBUILD TABLESPACE <tablespace_name>;

65) What are storage parameters in Tablespace (DMT)?

Free Lists, PCT FREE, PCT USED

66) What are free lists?

Space available for INSERT

67) What is PCTFREE?

Space available for UPDATE

68) What is PCTUSED?

Threshold value for how much space used

69) How to DE allocate the unused Extents?

ALTER TABLE <table_name> DEALLOCATED UNSED;

70) Create a tablespace of BLOCKSIZE 16K?

CREATE TABLESPACE <tablespace_name> DATAFILE <path> BLOCKSIZE 16K;

71) What is DROP TABLESPACE <tablespace_name>?

It will drop only the tablespace logically

72) What is DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS?

It will drop the tablespace along with the contents (Segments, Extents & Blocks)

73) What is DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS AND DATAFILES?

It will drop the tablespace along with the contents & Physical Data Files

74) How to rename a tablespace?

ALTER TABLESPACE <tablespace_name> RENAME TO <new_tablespace_name>;

75) How to make a tablespace READ ONLY?

ALTER TABLESPACE <tablespace_name> READ ONLY;

76) How to make a tablespace READ WRITE?

ALTER TABLESPACE <tablespace_name> READ WRITE;

77) How to make a tablespace ONLINE?

ALTER TABLESPACE <tablespace_name> ONLINE;

78) How to make a tablespace OFFLINE?

ALTER TABLESPACE <tablespace_name> OFFLINE;

79) What is the Data Dictionary views which gives the information about Tablespaces?

DBA_TABLESPACES, V$TABLESPACE

83) What is the Data Dictionary Views which gives the information about the Segments?

DBA_SEGMENTS

84) What is the Data Dictionary Views which gives the information about the Extents?

DBA_EXTENTS

85) What is the Data Dictionary Views which gives the information about the data files?

DBA_DATA_FILES, V$DATFILE

86) What is the Data Dictionary Views which gives the information about the Temporary files?

DBA_TEMP_FILES

87) What is the Data Dictionary Views which gives the information about the Temporary Tablespace

Groups?

DBA_TABLESPACE_GROUPS

Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: