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.
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 (mike) 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>