How to Create a Copy of an Oracle Table Without Copying the Data

How can I create a copy of an Oracle table without copying the data?

Just use a where clause that won't select any rows:

create table xyz_new as select * from xyz where 1=0;

Limitations

The following things will not be copied to the new table:

sequences
triggers
indexes
some constraints may not be copied
materialized view logs

This also does not handle partitions

Creating Duplicate Table From Existing Table

Use this query to create the new table with the values from existing table

CREATE TABLE New_Table_name AS SELECT * FROM Existing_table_Name;

Now you can get all the values from existing table into newly created table.

Copy table without copying data

Try:

CREATE TABLE foo SELECT * FROM bar LIMIT 0

Or:

CREATE TABLE foo SELECT * FROM bar WHERE 1=0

create full copy of existing table in oracle

You can use DBMS_REDEFINITION package, that thing can do a copy of whole table including comments etc. It will also "defragment" the new copy to occupy less space than the original table.

More info at https://docs.oracle.com/database/121/ARPLS/d_redefi.htm#ARPLS042

Example by request:

So let's create a test schema, a table and fill it with some data:

CREATE USER "TEST_SCHEMA" IDENTIFIED BY "TEST";
GRANT UNLIMITED TABLESPACE TO "TEST_SCHEMA";

CREATE TABLE "TEST_SCHEMA"."NAMES" ("ID" NUMBER, "NAME" VARCHAR2(25), PRIMARY KEY("ID"));

INSERT INTO "TEST_SCHEMA"."NAMES" VALUES (1, 'joe');
INSERT INTO "TEST_SCHEMA"."NAMES" VALUES (2, 'pete');
INSERT INTO "TEST_SCHEMA"."NAMES" VALUES (3, 'mark');

Now we can check the content of the test table:

SELECT * FROM "TEST_SCHEMA"."NAMES";

Let's begin the copy process by creating empty table exactly as the source table looks like (using WHERE 1=0, which won't take any data).

CREATE TABLE "TEST_SCHEMA"."NAMES_COPY" AS SELECT * FROM "TEST_SCHEMA"."NAMES" "A1" WHERE 1=0;

Here you can setup paralellization etc, prepare data etc..
After everything is done, let's start the REDEFINITION process:

BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE('TEST_SCHEMA','NAMES','NAMES_COPY');
END;
/

When the contents is copied, we need to copy every object that is dependent on the table (like triggers, indexes etc....)

DECLARE
  num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ('TEST_SCHEMA',  'NAMES',    'NAMES_COPY',
    DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/

We can check if any errors occured during copying of dependencies with this query:

SELECT "OBJECT_NAME", "BASE_TABLE_NAME", "DDL_TXT" FROM DBA_REDEFINITION_ERRORS;

If everything is alright, we can finish the process:

BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE ('TEST_SCHEMA', 'NAMES',    'NAMES_COPY');
END;
/

And voila, the new table is like the cloned sheep Dolly with everything in it:

SELECT * FROM "TEST_SCHEMA"."NAMES_COPY";

Now to cleaup the test schema, run these:

DROP TABLE "TEST_SCHEMA"."NAMES";
DROP TABLE "TEST_SCHEMA"."NAMES_COPY";
DROP USER "TEST_SCHEMA" CASCADE;

I hope it helped.

Oracle DB Create Table as Copy Vs. Merging Data into Empty Table

If it is a small table, it doesn't matter - both will be fast. Though, CTAS (create table as select) is probably the most usual way to create a "copy" of existing table.

If a table is very large, I don't know how it (CTAS) compares to merge; you should test it.

However, a backup table? Are you sure that's the right way to backup a table? I'd rather think of a proper (RMAN) database backup, or - at least - export (using Export Data Pump) into a file that resides in a filesystem (and can be stored elsewhere, e.g. onto an external hard disk drive, DVD and similar (does anyone use tapes any more? We do)).

Because, if database breaks down, along with your "original" table, that "backup" table will be lost as well.

Oracle copy data to another table

You need an INSERT ... SELECT

INSERT INTO exception_codes( code, message )
  SELECT code, message
    FROM exception_code_tmp

How to create a view where all data are collected concerning the person who uses the view in ORACLE?

Those tables are owned by user SCOTT so - connect as it; Scott will create a view and grant other users select privileges on a public synonym.

SQL> connect scott/tiger
Connected.
SQL> create or replace view v_emp as
  2    select d.deptno, d.dname, e.empno, e.ename, e.job
  3    from emp e join dept d on e.deptno = d.deptno
  4    where e.ename = user;

View created.

SQL> create public synonym psyn_v_emp for v_emp;

Synonym created.

SQL> grant select on psyn_v_emp to public;

Grant succeeded.

OK; so, what does the owner see?

SQL> show user
USER is "SCOTT"
SQL> select * from psyn_v_emp;

    DEPTNO DNAME               EMPNO ENAME      JOB
---------- -------------- ---------- ---------- ---------
        20 RESEARCH             7788 SCOTT      ANALYST
                                     -----
                                     Scott sees only Scott's data

Connect as some other user (I previously created user named KING):

SQL> connect king/king
Connected.
SQL> show user
USER is "KING"
SQL> select * from psyn_v_emp;

    DEPTNO DNAME               EMPNO ENAME      JOB
---------- -------------- ---------- ---------- ---------
        10 ACCOUNTING           7839 KING       PRESIDENT
                                     -----
                                     King sees only King's data

SQL>

Therefore, if I understood what you're asking, that would be "it".

How can I create a SQL table from another table without copying any values from the old table

If you are worried about iterating through the table:

CREATE TABLE new_table
  AS (SELECT *
      FROM (select * old_table where rownum = 1) t
      WHERE 1=2
     );