| Re: how to copy table from user to another user [message #70558] |
Fri, 28 June 2002 08:30 |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
Import/export is the way to go but there are some gotchas.
exp system/manager owner=sourceschema ...
That's the easy part. Now comes the import. You have to remember that import does a switch user when creating the objects in the target schema. If the target schema has privs like DBA, UNLIMITED TABLESPACE, or RESOURCE you may run into problems. I remove these privs before I import then give them back when done. The reason for this is Public Synonyms or I may want to move the objects to another tablespace or you may have references to objects in another schema via triggers. To avoid this the target schema has:
CONNECT and the following system privs so it can create these objects if needed.
CREATE PROCEDURE
CREATE TYPE
CREATE TRIGGER
Then make sure the target schema has a DEFAULT tablespace set with quota. At this point I import.
imp system/manager fromuser=sourceschema touser=targetschema ...
Then give the privs back to the user that needs them. I have found that this works the best for most situations. Once you do this a few times you will have it down. I use scripts to do all this and can refresh schemas or create new ones very quickly without problems.
Also, if the target schema already exists make sure you know what INSERT and UPDATE access they have and what public synonyms exist. You surely do not want to right to the wrong table because a synonym is pointing to it. The following script has been very useful to me to get this info.
set echo off
set verify off
set pages 200
col granted_role form a20
col owner form a12
col table_name form a27
col privilege form a27
ACCEPT username prompt 'Enter Username : '
spool privs_&username..lst
PROMPT Roles granted to user
SELECT granted_role,admin_option,default_role
FROM dba_role_privs
WHERE grantee=UPPER('&username');
PROMPT Table Privileges granted to a user through roles
SELECT granted_role, owner, table_name, privilege
FROM ( SELECT granted_role
FROM dba_role_privs WHERE grantee=UPPER('&username')
UNION
SELECT granted_role
FROM role_role_privs
WHERE role in (SELECT granted_role
FROM dba_role_privs WHERE grantee=UPPER('&username')
)
) roles, dba_tab_privs
WHERE granted_role=grantee;
PROMPT System Privileges assigned to a user through roles
SELECT granted_role, privilege
FROM ( SELECT granted_role
FROM dba_role_privs WHERE grantee=UPPER('&username')
UNION
SELECT granted_role
FROM role_role_privs
WHERE role in (SELECT granted_role
FROM dba_role_privs WHERE grantee=UPPER('&username')
)
) roles, dba_sys_privs
WHERE granted_role=grantee;
PROMPT Table privileges assigned directly to a user
SELECT owner, table_name, privilege
FROM dba_tab_privs
WHERE grantee=UPPER('&username');
PROMPT System privileges assigned directly to a user
SELECT privilege, admin_option
FROM dba_sys_privs
WHERE grantee=UPPER('&username');
spool off
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
