Data Versus Schema Replication Behavior

In Oracle Database, the CREATE TABLE AS SELECT (CTAS) construct provides a fast mechanism for duplicating tabular data along with column data types. However, relying on this command for structural mirroring introduces several silent failures. The database engine processes CTAS as a bulk data movement operation that selectively transfers metadata, intentionally bypassing most declarative integrity rules.

Constraint Inheritance Failures

When a source table contains validation rules, a direct CTAS clone will not carry those rules over to the target object. This behavior frequently causes downstream application logic to fail during insert or update operations.

Nullability Stripping

Source tables often enforce mandatory fields through NOT NULL restrictions. CTAS ignores these directives, resulting in fully nullable columns in the new table:

-- Inspect original nullability configuration
SELECT column_name, nullable
FROM all_tab_columns
WHERE table_name = 'DEPARTMENTS';

-- Execute structural clone without data
CREATE TABLE DEPT_SNAPSHOT AS
SELECT * FROM DEPARTMENTS WHERE ROWNUM = 0;

-- Verify cloned column properties
SELECT column_name, nullable
FROM all_tab_columns
WHERE table_name = 'DEPT_SNAPSHOT';

The query demonstrates that the DEPT_ID column transitions from restricted (N) to unrestricted (Y), nullifying the original business rule.

The DEFAULT and NOT NULL Conflict

A specific interaction exists between default expressions and null constraints. When a column defines both, CTAS inherits the null restriction but silently drops the default assignment:

-- Apply combined constraint to source table
ALTER TABLE DEPARTMENTS MODIFY manager_id DEFAULT 101 NOT NULL;

-- Clone the table
CREATE TABLE MGMT_DEPTS AS SELECT * FROM DEPARTMENTS;

-- Attempt partial row insertion
INSERT INTO MGMT_DEPTS (dept_id, department_name) VALUES (900, 'STRATEGY');

Executing this insert raises ORA-01400: cannot insert NULL into ("SCOTT"."MGMT_DEPTS"."MANAGER_ID"). The destination table correctly flags the column as non-nullable, yet the missing DEFAULT clause forces the optimizer to reject undefined values.

Dropping of Uniqueness and Referential Rules

Similar to nullability handling, unique indexes, check expressions, and foreign key references are entirely excluded from the cloning process. The generated table accepts duplicate records and orphaned references that would previously trigger constraint violations.

Extracting Complete Definition Scripts

To successfully duplicate a table while preserving every metadata attribute, storage parameter, and validation rule, utilize the dynamic dictionary package rather than manual DDL reconstruction:

-- Generate the exact creation script
SET long 5000
SET linesize 150
SELECT dbms_metadata.get_ddl('TABLE', 'DEPARTMENTS', 'SCOTT') AS full_structure
FROM dual;

This function returns a comprehensive CREATE TABLE statement containing primary keys, unique constraints, default expressions, storage clauses, and logging configurations. After executing the retrieved script, populate the fresh table using bulk load techniques such as INSERT /*+ APPEND */ to maintain performance and minimize redo generation.

Operational Takeaways

• CTAS replicates row contents and base datatypes but discards constraint definitions, indexes, and triggers.
• Columns combining DEFAULT values with NOT NULL lose their default expressions during cloning.
• Referential integrity, check conditions, and uniqueness guarantees require explicit recreation.
• Reserve CTAS for ephemeral testing datasets; deploy DBMS_METADATA.GET_DDL for production-grade schema replication.