Complete Guide to Creating Duplicate Tables from Existing Tables in Oracle Database

Overview of Oracle Table Duplication Techniques

In database management practice, creating duplicate tables with identical structure and data from existing tables is a common requirement. Oracle Database provides multiple implementation methods, among which the CREATE TABLE AS SELECT (CTAS) statement has become the preferred solution due to its conciseness and efficiency.

Core Syntax Analysis of CTAS Statement

The basic syntax structure of the CTAS statement is: CREATE TABLE new_table_name AS SELECT * FROM existing_table_name;. This statement performs two key operations: first, it creates a new table whose column names and data types completely inherit from the source table; then it copies all data records from the source table to the new table.

Complete Data Duplication Implementation

The following example demonstrates the complete table duplication process:

CREATE TABLE employees_backup AS 
SELECT * FROM employees;

After executing this statement, the system creates a new table named employees_backup, which contains not only the complete structure definition of the employees table but also all its data records. The advantage of this method lies in its atomicity—either successfully creating a complete copy with all data, or completely rolling back when encountering errors.

Selective Data Duplication Techniques

In practical applications, it may be necessary to copy only part of the source table's data. The CTAS statement supports conditional filtering through the WHERE clause:

CREATE TABLE active_employees AS 
SELECT * FROM employees 
WHERE status = 'ACTIVE';

This example copies only employee records with "ACTIVE" status, effectively reducing data redundancy and improving duplication efficiency.

Partial Column Duplication Strategy

When the complete table structure is not needed, specific columns can be specified for duplication:

CREATE TABLE employee_contacts AS 
SELECT employee_id, first_name, last_name, email 
FROM employees;

This method creates a new table containing only the specified four columns, suitable for building lightweight data views or dedicated data subsets.

Empty Table Structure Duplication

If only the table structure needs to be copied without data, this can be achieved by adding a permanently false condition:

CREATE TABLE employees_structure AS 
SELECT * FROM employees 
WHERE 1 = 0;

This statement creates an empty table with exactly the same structure as the source table but without any data records, commonly used for test environment setup or template table creation.

Multi-Table Joint Duplication

The CTAS statement supports extracting data from multiple tables and merging them into a new table:

CREATE TABLE employee_orders AS 
SELECT e.employee_id, e.first_name, o.order_id, o.order_date 
FROM employees e 
JOIN orders o ON e.employee_id = o.sales_rep_id;

This advanced usage enables cross-table data integration, providing flexible data reorganization solutions for complex business scenarios.

Performance Optimization Considerations

For large-scale data duplication, it is recommended to adopt parallel processing strategies:

CREATE TABLE large_table_backup PARALLEL 8 AS 
SELECT * FROM large_table;

By specifying the parallelism parameter, the execution efficiency of large-volume data duplication operations can be significantly improved. Meanwhile, reasonable use of the NOLOGGING option can reduce redo log generation, further enhancing performance.

Comparison with Other Database Syntaxes

It is worth noting that Oracle's CTAS statement differs from the SELECT INTO syntax in other database systems. For example, in SQL Server, a similar operation is: SELECT * INTO new_table FROM existing_table. Oracle developers need to pay special attention to this syntax difference to avoid compatibility issues during cross-database migration.

Best Practice Recommendations

In actual deployment, it is recommended to follow these principles: always verify duplication results in a test environment before applying to production systems; for large tables, consider batch duplication to reduce transaction lock contention; regularly maintain table statistics to ensure the query optimizer can make optimal execution plan choices.