Oracle CTAS: The Complete Guide to Copying Tables (With All Dependencies)

Most Oracle developers start with the basic table copy command:

CREATE TABLE new_table AS SELECT * FROM old_table;

However, this only copies about 20% of what makes your table functional. If you’ve ever copied a table and wondered why your application broke, or why queries suddenly ran slower, or why data validation stopped working—this guide is for you.

What Is CTAS (CREATE TABLE AS SELECT)?

CTAS is a DDL (Data Definition Language) command that creates a new table based on the result set of a SELECT query.

What CTAS Can Do

First and foremost, it can copy complete table structure and data. Additionally, it allows you to copy filtered subsets of data and specific columns only. Moreover, you can create empty table structures, generate tables from complex joins and calculations, and perform quick data transformations.

Basic Syntax

CREATE TABLE new_table_name
AS
SELECT column1, column2, ...
FROM source_table
WHERE conditions;

7 Essential CTAS Scenarios (With Examples)

Scenario 1: Complete Table Copy (Structure + All Data)

Use Case: Quick backup or creating a testing copy

CREATE TABLE employees_copy
AS
SELECT * FROM employees;

Verify the copy:

-- Check row counts match
SELECT COUNT(*) FROM employees;
SELECT COUNT(*) FROM employees_copy;

-- Compare structure
DESC employees;
DESC employees_copy;

Scenario 2: Copy Only Selected Columns

Use Case: Create simplified views for reporting or external systems

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

Verify columns:

DESC employees_basic;
SELECT * FROM employees_basic WHERE ROWNUM <= 5;

Scenario 3: Copy Structure Only (No Data)

Use Case: Create template tables for bulk loading or staging

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

The trick: WHERE 1=0 is always false, so zero rows are returned, but the structure is preserved.

Verify it’s empty:

SELECT COUNT(*) FROM employees_staging;  -- Should return 0
DESC employees_staging;  -- Should show all columns

Scenario 4: Copy Filtered Data (WHERE Condition)

Use Case: Create department-specific tables or archive old records

-- Copy only sales department employees
CREATE TABLE employees_sales
AS
SELECT *
FROM employees
WHERE department_id = 80;

-- Copy employees hired after 2020
CREATE TABLE employees_recent
AS
SELECT *
FROM employees
WHERE hire_date >= DATE '2020-01-01';

Verify filtering:

SELECT COUNT(*), MIN(hire_date), MAX(hire_date) 
FROM employees_recent;

Scenario 5: Copy With Calculated/Derived Columns

Use Case: Precompute values for reporting tables

CREATE TABLE employees_compensation
AS
SELECT 
    employee_id,
    first_name,
    last_name,
    salary,
    salary * 12 AS annual_salary,
    salary * 12 * 0.15 AS estimated_tax,
    ROUND(salary * 1.1, 2) AS projected_salary
FROM employees;

Important Note: Calculated columns become regular columns—formulas are not preserved.

Scenario 6: Copy Using JOINs (Multiple Tables)

Use Case: Create denormalized tables for analytics or reporting

CREATE TABLE employee_department_summary
AS
SELECT 
    e.employee_id,
    e.first_name,
    e.last_name,
    e.salary,
    d.department_name,
    d.location_id,
    l.city,
    l.country_id
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id;

Scenario 7: Copy Table to Another Schema

Use Case: Move data between environments or share data across schemas

CREATE TABLE hr_backup.employees_archive
AS
SELECT * FROM hr.employees;

Prerequisites:

• First, you must have CREATE TABLE privilege in the target schema
• Second, you must have SELECT privilege on the source table
• Finally, the target schema must exist

Grant required privileges:

GRANT SELECT ON hr.employees TO hr_backup;
GRANT CREATE TABLE TO hr_backup;

⚠️ CRITICAL: What CTAS Does NOT Copy

Here’s where most developers get caught off guard. CTAS only copies the basic structure and data.

Specifically, it does NOT copy:

Let’s fix each one systematically.

Step-by-Step: Recreating All Table Dependencies

1. Check and Recreate Primary Keys

Check existing primary key:

SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'EMPLOYEES';

Constraint types:

• P → Primary Key
• R → Foreign Key (Referential)
• C → Check Constraint
• U → Unique Constraint

Recreate primary key:

ALTER TABLE employees_copy
ADD CONSTRAINT emp_copy_pk PRIMARY KEY (employee_id);

2. Check and Recreate Foreign Keys

Check foreign keys:

SELECT 
    a.constraint_name,
    a.table_name,
    a.column_name,
    c_pk.table_name AS references_table,
    c_pk.constraint_name AS references_constraint
FROM user_cons_columns a
JOIN user_constraints c ON a.constraint_name = c.constraint_name
JOIN user_constraints c_pk ON c.r_constraint_name = c_pk.constraint_name
WHERE c.constraint_type = 'R'
AND a.table_name = 'EMPLOYEES';

Recreate foreign key:

ALTER TABLE employees_copy
ADD CONSTRAINT emp_copy_dept_fk 
FOREIGN KEY (department_id) 
REFERENCES departments(department_id);

3. Check and Recreate Indexes

This is CRITICAL for performance.

Check all indexes:

SELECT 
    i.index_name,
    i.uniqueness,
    ic.column_name,
    ic.column_position
FROM user_indexes i
JOIN user_ind_columns ic ON i.index_name = ic.index_name
WHERE i.table_name = 'EMPLOYEES'
ORDER BY i.index_name, ic.column_position;

Recreate regular index:

CREATE INDEX emp_copy_email_idx 
ON employees_copy(email);

Recreate composite index:

CREATE INDEX emp_copy_dept_sal_idx 
ON employees_copy(department_id, salary);

Recreate unique index:

CREATE UNIQUE INDEX emp_copy_email_unq 
ON employees_copy(email);

4. Check and Recreate Triggers

Check triggers:

SELECT trigger_name, trigger_type, triggering_event, status
FROM user_triggers
WHERE table_name = 'EMPLOYEES';

Get trigger code:

SELECT trigger_body
FROM user_triggers
WHERE trigger_name = 'EMP_AUDIT_TRG';

Recreate trigger:

CREATE OR REPLACE TRIGGER emp_copy_audit_trg
BEFORE INSERT OR UPDATE ON employees_copy
FOR EACH ROW
BEGIN
    :NEW.last_modified := SYSDATE;
END;
/

5. Check and Recreate Sequences

Check sequences:

SELECT sequence_name, last_number, increment_by
FROM user_sequences
WHERE sequence_name LIKE '%EMP%';

Get current sequence value:

SELECT emp_seq.CURRVAL FROM dual;

Recreate sequence (starting from current value):

CREATE SEQUENCE emp_copy_seq
START WITH 1000
INCREMENT BY 1
NOCACHE;

6. Check and Recreate Grants

Check existing permissions:

SELECT grantee, privilege, grantable
FROM user_tab_privs
WHERE table_name = 'EMPLOYEES';

Reapply grants:

GRANT SELECT, INSERT, UPDATE ON employees_copy TO app_user;
GRANT SELECT ON employees_copy TO reporting_user;
GRANT ALL PRIVILEGES ON employees_copy TO admin_user;

7. Check and Recreate Check Constraints

Check constraints:

SELECT constraint_name, search_condition
FROM user_constraints
WHERE table_name = 'EMPLOYEES'
AND constraint_type = 'C'
AND constraint_name NOT LIKE 'SYS%';  -- Exclude system-generated

Recreate check constraints:

ALTER TABLE employees_copy
ADD CONSTRAINT emp_copy_salary_chk CHECK (salary > 0);

ALTER TABLE employees_copy
ADD CONSTRAINT emp_copy_email_chk CHECK (email LIKE '%@%');

8. Check and Recreate NOT NULL Constraints

Check NOT NULL constraints:

SELECT column_name, nullable
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES'
AND nullable = 'N';

Apply NOT NULL:

ALTER TABLE employees_copy
MODIFY (email NOT NULL);

ALTER TABLE employees_copy
MODIFY (employee_id NOT NULL);

9. Check and Recreate Default Values

Check default values:

SELECT column_name, data_default
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES'
AND data_default IS NOT NULL;

Apply defaults:

ALTER TABLE employees_copy
MODIFY (hire_date DEFAULT SYSDATE);

ALTER TABLE employees_copy
MODIFY (status DEFAULT 'ACTIVE');

🚨 Common CTAS Mistakes to Avoid

Mistake #1: Assuming Constraints Are Copied

-- ❌ WRONG: Thinking this copies everything
CREATE TABLE users_backup AS SELECT * FROM users;
-- Now trying to insert duplicate IDs succeeds (no PK!)

Mistake #2: Forgetting Indexes

-- ❌ WRONG: Copying large table without indexes
CREATE TABLE sales_archive AS SELECT * FROM sales;
-- Queries on sales_archive are now 100x slower

Mistake #3: Using CTAS for Production Replication

-- ❌ WRONG: Using CTAS for ongoing replication
CREATE TABLE live_backup AS SELECT * FROM live_data;
-- Triggers, sequences, and constraints are missing

Mistake #4: Not Validating Data Types

-- ❌ WRONG: Not checking if calculated columns have correct types
CREATE TABLE report AS 
SELECT salary * 12 AS annual FROM employees;
-- annual might be NUMBER instead of NUMBER(10,2)

✅ Best Practices for Using CTAS

1. Always Verify After Creation

First, check the structure to ensure all columns are present:

-- Check structure
DESC employees_copy;

-- Check row count
SELECT COUNT(*) FROM employees_copy;

-- Compare with original
SELECT COUNT(*) FROM employees;

2. Use Clear Naming Conventions

Moreover, establish consistent naming patterns for different table types:

• _copy → Exact copy for testing
• _bak → Backup before changes
• _staging → Temporary staging table
• _archive → Historical data archive
• _temp → Temporary working table

3. Document What’s Missing

Furthermore, always add comments to track what needs recreation:

-- Add comment to table
COMMENT ON TABLE employees_copy IS 
'Copy of employees table created 2026-01-15. Missing: PKs, FKs, triggers, indexes';

4. Use CTAS Appropriately

Good use cases:

Initially, CTAS works well for quick backups before major changes. Subsequently, it’s perfect for creating test datasets and staging tables for ETL processes. Additionally, it’s ideal for ad-hoc reporting tables and data archival.

Bad use cases:

Conversely, avoid using CTAS for production data replication. Similarly, don’t use it when creating tables with complex business logic. Finally, it’s not suitable when you need all constraints and dependencies.

📋 Complete Post-CTAS Checklist

After running CTAS, systematically check everything:

-- 1. Structure verification
DESC table_name;

-- 2. Row count
SELECT COUNT(*) FROM table_name;

-- 3. Constraints
SELECT constraint_name, constraint_type 
FROM user_constraints 
WHERE table_name = 'TABLE_NAME';

-- 4. Indexes
SELECT index_name, column_name 
FROM user_ind_columns 
WHERE table_name = 'TABLE_NAME';

-- 5. Triggers
SELECT trigger_name, status 
FROM user_triggers 
WHERE table_name = 'TABLE_NAME';

-- 6. Grants
SELECT grantee, privilege 
FROM user_tab_privs 
WHERE table_name = 'TABLE_NAME';

-- 7. Sequences (if applicable)
SELECT sequence_name 
FROM user_sequences;

-- 8. NOT NULL constraints
SELECT column_name 
FROM user_tab_columns 
WHERE table_name = 'TABLE_NAME' AND nullable = 'N';

-- 9. Default values
SELECT column_name, data_default 
FROM user_tab_columns 
WHERE table_name = 'TABLE_NAME' AND data_default IS NOT NULL;

🎯 Complete Example: Full Table Copy with All Dependencies

Here’s a complete workflow for copying a table with all its dependencies:

-- Step 1: Copy the data
CREATE TABLE employees_backup AS SELECT * FROM employees;

-- Step 2: Add primary key
ALTER TABLE employees_backup
ADD CONSTRAINT emp_bak_pk PRIMARY KEY (employee_id);

-- Step 3: Add foreign keys
ALTER TABLE employees_backup
ADD CONSTRAINT emp_bak_dept_fk 
FOREIGN KEY (department_id) REFERENCES departments(department_id);

-- Step 4: Recreate indexes
CREATE INDEX emp_bak_email_idx ON employees_backup(email);
CREATE INDEX emp_bak_dept_idx ON employees_backup(department_id);

-- Step 5: Add check constraints
ALTER TABLE employees_backup
ADD CONSTRAINT emp_bak_salary_chk CHECK (salary > 0);

-- Step 6: Apply NOT NULL constraints
ALTER TABLE employees_backup
MODIFY (email NOT NULL);

-- Step 7: Grant permissions
GRANT SELECT ON employees_backup TO reporting_user;

-- Step 8: Verify everything
SELECT 'Original count: ' || COUNT(*) FROM employees
UNION ALL
SELECT 'Backup count: ' || COUNT(*) FROM employees_backup;

Alternative: Using Oracle Data Pump for Complete Copies

If you need everything copied (including all dependencies), consider using Oracle Data Pump instead:

-- Export
expdp username/password TABLES=employees DIRECTORY=dump_dir DUMPFILE=emp.dmp

-- Import with new name
impdp username/password TABLES=employees DIRECTORY=dump_dir DUMPFILE=emp.dmp REMAP_TABLE=employees:employees_copy

Data Pump copies:

Notably, Data Pump handles all constraints automatically. In addition, it preserves all indexes and triggers. Furthermore, it maintains grants and privileges along with statistics.

Final Thoughts

CREATE TABLE AS SELECT is one of Oracle’s fastest and most powerful commands—but only when you understand its limitations.

Remember the golden rule: CTAS copies data and basic structure. Everything else must be recreated manually.

Consequently, master CTAS by:

1. Knowing exactly what it copies (data + column definitions)
2. Understanding what it skips (everything else!)
3. Having a systematic process to verify and recreate dependencies

Therefore, use this guide as your reference, bookmark it, and never lose data integrity when copying tables again.

Pro Tip: Create a script template that includes all verification queries and store it in your code repository. Then, every time you use CTAS, run through the complete checklist—it takes 2 minutes and prevents hours of debugging later.