Copying tables from one user to another 2

[Sam3207]

how do I copy a table from one user to another, without rekeying all the data ?

I'm studying Oracle 9i; attempting to get practice using select/single-row/group functs/date functions, joins and sub-queries, etc.
But some of the tables are under USER: student
Others are under USER: scott

Is there a way to copy a table called: customer
FROM the USER: scott
to the
USER: student

thanks for your instruction

 

[lewisp]

Connect as student, then make sure student has SELECT privilege on scotts tables.

[tt]CREATE TABLE customer AS
SELECT * FROM scott.customer;[/tt]

 

[Sam3207]

lewisp,

I tried that with the BONUS table, but received an error message. The BONUS table is under the SCOTT user database.
So I connected to STUDENT database and keyed in the below
(create table bonus as select * from scott.bonus.


CREATE TABLE bonus AS SELECT * FROM scott.bonus;

*ERROR at line 1:
ORA-00942: table or view does not exist


Am I doing something wrong ?

 

[vc123]

'SCOTT' has to grant the 'select' privilege on the 'bonus' table to 'STUDENT':

grant select on bonus to student;

VC

 

[Sam3207]

thanks for replying VC,

I tried the grant feature below. I connected to SCOTT,
GRANTED select to the bonus table to STUDENT.
Then I connected to STUDENT to try to select something from
the bonus table, it still gives the 'table does not exist'.

Since the BONUS table is NOT in the STUDENT database,
do I need to be SYSTEM user to get the GRANT to work ?

----------------------
SQL> conn scott
Enter password: *****
Connected.
SQL> grant select on bonus to student;

Grant succeeded.

SQL> conn student
Enter password: ***
Connected.
SQL> select * from bonus;
select * from bonus
*
ERROR at line 1:
ORA-00942: table or view does not exist

 

[Sam3207]

thanks lewis and VC,

It works now !!!. I'll have to do the other tables the same way.

thanks again.

 

[SantaMufasa]

Sam,

When you use the technique(s) mentioned above, you need to be aware that if there are any constraints on the original tables, you need to re-build them by hand (except for NOT NULL). This means that any PRIMARY KEY, FOREIGN KEY, CHECK, and UNIQUE constraints don't come across to the new table with "CREATE TABLE...AS SELECT..." syntax.

A method that does include cloning of all constraints, storage parameters, et cetera, and allows you to even edit the table-creation code before running is to use the following procedure:

1) Log into the source schema.
2) select dbms_metadata.get_ddl('<obj. type>','<name>','<schema>') from dual;
Example: select dbms_metadata.get_ddl('TABLE','S_EMP','DHUNT') from dual;
3) Copy the result to a file that becomes a SQL script.
4) Edit the script to change the schema name from its original to the new, target schema. Notice that if there are any foreign keys, you must either create the parent tables first or disable the constraints and re-enable them after you create the parent tables. If there are other schema references such as indexes, modify the schema names as needed, as well.
5) Execute the script.
6) Once the new table is in place, you can to an
&quot;insert into <new_table_name> select * from <original_schema>.<original_table_name>;&quot;

Thanks go to Tek-Tipster, Carp, for refreshing my memory on the syntax for &quot;select dbms_metadata.get_ddl('<obj. type>','<name>','<schema>') from dual;&quot;

Let us know if this method works for you,

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:17 (15Dec03) GMT, 11:17 (15Dec03) Mountain Time)

 

[sem]

You may use COPY command instead of CREATE AS SELECT:
<code>
COPY FROM scott/tiger TO student/<password> CREATE bonus USING select * from bonus </code>

In this case you need no grants because each operation is made from its own connection. Besides this is a good way for handling LONG columns, that can not be copied by CREATE AS SELECT.

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

Just a reminder to all readers of this thread: the &quot;COPY FROM...&quot; method still ignores PRIMARY KEY, FOREIGN KEY, CHECK, and UNIQUE constraint definitions in the source table. That problem goes away with the &quot;...dbms_metadata.get_ddl('<obj. type>','<name>','<schema>')...&quot; method (above), which preserves ALL constraint definitions. The one major benefit of COPY is that it picks up LONG columns. (Aren't LONGs a pain in the neck?)

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA @ 08:49 (16Dec03) GMT, 01:49 (16Dec03) Mountain Time)

 

[sem]

Yes Dave, that was why I suggested it as an alternative for CREATE AS SELECT, not for the whole solution, that IMO should consist of both techniques (catching DDL and COPY with INSERT option).

Regards, Dima

http://seminihin.narod.ru

 

[ifxtechie]

Hi,
Thanks for the above post. I used the discussed techniques, but landed into a small problem. It is not showing the complete table description. The results are as below:

SQL> desc MIR_STORES
Name Null? Type
----------------------------------------- -------- ----------------------------

ID NOT NULL NUMBER(38)
NAME NVARCHAR2(30)
ADDRESS1 NVARCHAR2(30)
ADDRESS2 NVARCHAR2(30)
CITY NVARCHAR2(30)
STATE NVARCHAR2(2)
ZIP NVARCHAR2(10)

SQL> set SERVEROUTPUT ON;
SQL> select dbms_metadata.get_ddl('TABLE','MIR_STORES','SYSTEM') from dual;

DBMS_METADATA.GET_DDL('TABLE','MIR_STORES','SYSTEM')
--------------------------------------------------------------------------------


CREATE TABLE &quot;SYSTEM&quot;.&quot;MIR_STORES&quot;
( &quot;ID&quot; NUMBER(*,0),
&quot;NAME&quot; NVARCHAR2


SQL>


Any ideas as why it is failing.
thanks
Partha

 

[sem]

SET LONG 100000
Your LONG field is truncated

Regards, Dima

http://seminihin.narod.ru

 

[ifxtechie]

Thanks very much, Dima. It worked.
[I don't know how many of this kind I am missing/don't know! ]
Partha

 

[pabloj]

Hi all, Santa wrote:

6) Once the new table is in place, you can to an
&quot;insert into <new_table_name> select * from <original_schema>.<original_table_name>;&quot;

Does this work with LONG RAW columns?

I need to copy tables from one user to another as I found out that a guy installed many different applications under the same schema, mixing (and messing) everything up.
Thanks!!

Stick to your guns

 

[sem]

No, you should use COPY (or exp/imp) for tables with LONG columns.

Regards, Dima

http://seminihin.narod.ru