I want to copy existing table from one schema to another without changing anything-CodePudding

I am trying to copy 1 table from 1 schema into another schema in non existing table using DBVizualizer or SQL Developer. Also if possible just for testing purposes will need example how to copy 1 table into another (non existing table) in the same schema.

I would really appreciate any help.

Have tried this:

SELECT * 
INTO new_table 
FROM old_table;

ALTER TABLE old_table 
RENAME TO new_table;

in both DBVizuallizer and SQL Developer doesn't work and I am not really sure what I have done since it was copy paste from w3school.

CodePudding user response：

CTAS (Create Table As Select) is a simple option:

SQL> show user
USER is "SCOTT"
SQL> create table dept_new as select * From dept;

Table created.

SQL> select * from dept_new;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

However, if a new table should reside in another schema, then you might have a problem because you can't create objects in other schemas unless you have privileges to do so. Normally, we don't because that requires create any table privilege, and that's not something you (or your DBA) should grant easily.

SQL> connect scott/tiger
Connected.
SQL> create table mike.dept_new as select * From dept;
create table mike.dept_new as select * From dept
                                            *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> connect sys as sysdba
Enter password:
Connected.
SQL> grant create any table to scott;

Grant succeeded.

SQL> connect scott/tiger
Connected.
SQL> create table mike.dept_new as select * From dept;

Table created.

SQL>

Presuming that another schema already contains such a table (but it is empty), you'd just copy data. But, that also won't be easy because of - guess what? - missing privileges. We aren't supposed to mess up with other users' data.

SQL> insert into mike.dept_new select * From dept;
insert into mike.dept_new select * From dept
                 *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL>

This time we don't need a DBA to grant privileges; other user (mienter code hereke) can do that:

SQL> connect mike/lion
Connected.
SQL> grant insert on dept_new to scott;

Grant succeeded.

SQL> connect scott/tiger
Connected.
SQL> insert into mike.dept_new select * From dept;

4 rows created.

SQL>

Yet another option is to export data from source schema and import it into target schema. First, drop it from mike (as you said that it shouldn't already exist):

SQL> connect mike/lion
Connected.
SQL> drop table dept_new;

Table dropped.

SQL>

Export:

SQL> $exp scott/tiger file=dept.dmp tables=dept

Export: Release 11.2.0.2.0 - Production on Pon Pro 20 21:41:36 2021

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Export done in EE8MSWIN1250 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                           DEPT          4 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

Import:

SQL> $imp mike/lion file=dept.dmp

Import: Release 11.2.0.2.0 - Production on Pon Pro 20 21:41:50 2021

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by SCOTT, not by you

import done in EE8MSWIN1250 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SCOTT's objects into MIKE
Import terminated successfully without warnings.

SQL>