Oracle Database Administration from Zero to Hero - Using Deferred Segment Creation

This lecture covers deferred segment creation in Oracle databases, a feature introduced in Oracle 11G to optimize storage by delaying segment allocation until data is inserted. It explains how this feature saves disk space and reduces installation time. The lecture also discusses controlling this feature through initialization parameters and the CREATE TABLE statement. Additionally, it covers materializing database segments using the DBMS_SPACE_ADMIN procedure, allowing segments to be created without data insertion. The lecture concludes with a summary of key points.

What was a major drawback of Oracle databases before version 11G regarding table creation?

• Tables were created without any metadata.

• Each table creation consumed significant storage space.

• Tables could not be created without data.

• Tables were automatically indexed.

What happens when an object is created with deferred segment creation enabled?

• A segment is immediately assigned to the object.

• The object takes up space in the tablespace.

• Only metadata is created without assigning a segment.

• The object is not visible in the data dictionary.

How can deferred segment creation be controlled at the database level?

• By using the CREATE TABLE statement only.

• Through the initialization parameter deferred SEGMENT CREATION.

• By setting the tablespace size.

• Using the Oracle database management console.

What is the default setting for the deferred SEGMENT CREATION parameter?

• It depends on the tablespace size.

• False

• True

• It is not set by default.

Which of the following is NOT a benefit of deferred segment creation?

• Immediate segment creation for all tables.

• Flexibility for developers to decide on segment creation.

• Faster application installation time.

• Reduced disk space usage.

What is the purpose of the DBMS_SPACE_ADMIN.MATERIALIZED_DEFERRED_SEGMENTS procedure?

• To analyze database performance.

• To backup database segments.

• To create extents for tables without inserting data.

• To delete segments from the database.

What must be done to materialize a segment for a table with deferred segment creation?

• Change the tablespace of the table.

• Delete the table and recreate it.

• Insert data into the table.

• Use the DBMS_SPACE_ADMIN procedure.