Oracle CTAS Statement: Changing Storage Parameters for a Table
Introduction
The CREATE TABLE AS SELECT (CTAS) statement in Oracle enables users to create a new table and populate it with data selected from another table.
Key Functionality
One key feature of the CTAS statement is its ability to modify storage parameters for the newly created table. This includes specifying the initial and next extent sizes, which determine how much space the table will initially occupy and how it will grow as data is added.
Considerations
When using the CTAS statement to change storage parameters, it's crucial to ensure that the segment structures of the new and existing tables match exactly. Failure to do so can result in errors. Additionally, specifying the TABLESPACE clause allows users to create the new table in a specific tablespace.
Example
The following CTAS statement creates a new table called "new_table" with initial and next extent sizes of 100 MB and 50 MB, respectively:
CREATE TABLE new_table AS SELECT * FROM old_table TABLESPACE tablespace_name INITIAL 100M NEXT 50M; 
Komentar