Re: copy table to another schema programatically

On Jan 8, 6:22 pm, Sean Nakasone <seannakas..._at_yahoo.com> 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.

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 Received on Wed Jan 09 2008 - 08:22:20 CST