copy table to another schema programatically

#1

anyone have code snippets that copy a table from one schema to another? i
usually use toad to do this but i really would like to find more automated
way.

#2

Sean Nakasone wrote:

Quote:

anyone have code snippets that copy a table from one schema to another?
i usually use toad to do this but i really would like to find more
automated way.

CREATE TABLE new_table AS
SELECT * FROM schema_name.existing_table;

Why use a tool?
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#3

Sean Nakasone wrote:

Quote:

anyone have code snippets that copy a table from one schema to another?
i usually use toad to do this but i really would like to find more
automated way.

CREATE TABLE new_table AS
SELECT * FROM schema_name.existing_table;

Why use a tool?
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#4

Sean Nakasone wrote:

Quote:

anyone have code snippets that copy a table from one schema to another?
i usually use toad to do this but i really would like to find more
automated way.

CREATE TABLE new_table AS
SELECT * FROM schema_name.existing_table;

Why use a tool?
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#5

Sean Nakasone wrote:

Quote:

anyone have code snippets that copy a table from one schema to another?
i usually use toad to do this but i really would like to find more
automated way.

CREATE TABLE new_table AS
SELECT * FROM schema_name.existing_table;

Why use a tool?
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#6

On Jan 8, 6:22*pm, Sean Nakasone <seannakas... (AT) yahoo (DOT) com> wrote:

Quote:

anyone have code snippets that copy a table from one schema to another? *i
usually use toad to do this but i really would like to find more automated
way.

Define 'more automated way'.

One way is like this:

accept v_dest prompt 'Enter destination schema: '
accept v_src prompt 'Enter source schema: '
accept v_tbl prompt 'Enter table name: '

create table &v_dest..&v_tbl
as select * from &v_src..&v_tbl;

Of course it takes tablespace defaults for the storage settings (using
LMTs usually eliminates setting those in the DDL anyway) and requires
a DBA-privileged account. To illustrate it works:

SQL> create table waldo(pepper number, salt number, oregano
varchar2(73))
2 tablespace tools;

Table created.

SQL> @cr_tbl_other_user_dyn_ex
Enter destination schema: bing
Enter source schema: sys
Enter table name: waldo
old 1: create table &v_dest..&v_tbl
new 1: create table bing.waldo
old 2: as select * from &v_src..&v_tbl
new 2: as select * from sys.waldo

Table created.

SQL> connect bing/######@%%%%%%%%%
Connected.
SQL> desc waldo
Name Null? Type
----------------------------------------- --------
----------------------------
PEPPER NUMBER
SALT NUMBER
OREGANO VARCHAR2(73)

David Fitzjarrell

#7

On Jan 8, 6:22*pm, Sean Nakasone <seannakas... (AT) yahoo (DOT) com> wrote:

Quote:

anyone have code snippets that copy a table from one schema to another? *i
usually use toad to do this but i really would like to find more automated
way.

Define 'more automated way'.

One way is like this:

accept v_dest prompt 'Enter destination schema: '
accept v_src prompt 'Enter source schema: '
accept v_tbl prompt 'Enter table name: '

create table &v_dest..&v_tbl
as select * from &v_src..&v_tbl;

Of course it takes tablespace defaults for the storage settings (using
LMTs usually eliminates setting those in the DDL anyway) and requires
a DBA-privileged account. To illustrate it works:

SQL> create table waldo(pepper number, salt number, oregano
varchar2(73))
2 tablespace tools;

Table created.

SQL> @cr_tbl_other_user_dyn_ex
Enter destination schema: bing
Enter source schema: sys
Enter table name: waldo
old 1: create table &v_dest..&v_tbl
new 1: create table bing.waldo
old 2: as select * from &v_src..&v_tbl
new 2: as select * from sys.waldo

Table created.

SQL> connect bing/######@%%%%%%%%%
Connected.
SQL> desc waldo
Name Null? Type
----------------------------------------- --------
----------------------------
PEPPER NUMBER
SALT NUMBER
OREGANO VARCHAR2(73)

David Fitzjarrell

#8

On Jan 8, 6:22*pm, Sean Nakasone <seannakas... (AT) yahoo (DOT) com> wrote:

Quote:

anyone have code snippets that copy a table from one schema to another? *i
usually use toad to do this but i really would like to find more automated
way.

Define 'more automated way'.

One way is like this:

accept v_dest prompt 'Enter destination schema: '
accept v_src prompt 'Enter source schema: '
accept v_tbl prompt 'Enter table name: '

create table &v_dest..&v_tbl
as select * from &v_src..&v_tbl;

Of course it takes tablespace defaults for the storage settings (using
LMTs usually eliminates setting those in the DDL anyway) and requires
a DBA-privileged account. To illustrate it works:

SQL> create table waldo(pepper number, salt number, oregano
varchar2(73))
2 tablespace tools;

Table created.

SQL> @cr_tbl_other_user_dyn_ex
Enter destination schema: bing
Enter source schema: sys
Enter table name: waldo
old 1: create table &v_dest..&v_tbl
new 1: create table bing.waldo
old 2: as select * from &v_src..&v_tbl
new 2: as select * from sys.waldo

Table created.

SQL> connect bing/######@%%%%%%%%%
Connected.
SQL> desc waldo
Name Null? Type
----------------------------------------- --------
----------------------------
PEPPER NUMBER
SALT NUMBER
OREGANO VARCHAR2(73)

David Fitzjarrell

#9

On Jan 8, 6:22*pm, Sean Nakasone <seannakas... (AT) yahoo (DOT) com> wrote:

Quote:

anyone have code snippets that copy a table from one schema to another? *i
usually use toad to do this but i really would like to find more automated
way.

Define 'more automated way'.

One way is like this:

accept v_dest prompt 'Enter destination schema: '
accept v_src prompt 'Enter source schema: '
accept v_tbl prompt 'Enter table name: '

create table &v_dest..&v_tbl
as select * from &v_src..&v_tbl;

Of course it takes tablespace defaults for the storage settings (using
LMTs usually eliminates setting those in the DDL anyway) and requires
a DBA-privileged account. To illustrate it works:

SQL> create table waldo(pepper number, salt number, oregano
varchar2(73))
2 tablespace tools;

Table created.

SQL> @cr_tbl_other_user_dyn_ex
Enter destination schema: bing
Enter source schema: sys
Enter table name: waldo
old 1: create table &v_dest..&v_tbl
new 1: create table bing.waldo
old 2: as select * from &v_src..&v_tbl
new 2: as select * from sys.waldo

Table created.

SQL> connect bing/######@%%%%%%%%%
Connected.
SQL> desc waldo
Name Null? Type
----------------------------------------- --------
----------------------------
PEPPER NUMBER
SALT NUMBER
OREGANO VARCHAR2(73)

David Fitzjarrell

#10

DA Morgan <damorgan (AT) psoug (DOT) org> wrote:

Quote:

Sean Nakasone wrote:
anyone have code snippets that copy a table from one schema to another?
i usually use toad to do this but i really would like to find more
automated way.

CREATE TABLE new_table AS
SELECT * FROM schema_name.existing_table;

If we wanted to do this with several tables, would we have
any issues with relationship integrity? I.e., if we do
several create tables in a transaction, will the
tables be consistent?

create table1 from other.table1;
(other.table1, other.table2 updated by other people)
create table2 from other.table2;
commit;

Many TIA!
Mark

--
Mark Harrison
Pixar Animation Studios