| Exp/Imp User [message #70277] |
Fri, 17 May 2002 10:48  |
shafik
Messages: 13
Registered: March 2002
|
Junior Member |
|
|
Yaar I want to export a user from my production databse to my development database. My development database has all tablespaces, users, profiles similar to production database. In other words i want to get the 2 users in dev an prod in sync.
Does anyone know the method of doing it.
Can i just disable all the constraints and drop all tables of the user in dev and import the tables of prod user. Will that take care of it ? Will it create the indexes, triggers ,functions, procedures and all other objects which were dropped by dropping the tables ?
Help!!!
|
|
|
|
| Re: Exp/Imp User [message #70280 is a reply to message #70277] |
Fri, 17 May 2002 11:42  |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
Here is how I do it.
1. export the source schema
2. drop all objects in target (dev) schema. Keeps grants.
3. import into target
4. compile invalid objects
5. analyze schema
1. PROD:
exp system/PASSWORD parfile=exp_user.par file=SCHEMANAME_expdat.dmp log=SCHEMANAME_exp.log owner=(SCHEMA)
EXP_USER.PAR:
BUFFER=64000
COMPRESS=Y
GRANTS=Y
INDEXES=Y
ROWS=Y
CONSTRAINTS=Y
Note: Do not use compress if using partitions
2. run the drop schema objects script. May need to modify if you have objects not known by this script such as java:
rem =========================================================================
rem
rem drop_user_objects.sql
rem
rem Copyright (C) Oriole Software, 1999
rem
rem Downloaded from http://www.oriolecorp.com
rem
rem This script for Oracle database administration is free software; you
rem can redistribute it and/or modify it under the terms of the GNU General
rem Public License as published by the Free Software Foundation; either
rem version 2 of the License, or any later version.
rem
rem This script is distributed in the hope that it will be useful,
rem but WITHOUT ANY WARRANTY; without even the implied warranty of
rem MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
rem GNU General Public License for more details.
rem
rem You should have received a copy of the GNU General Public License
rem along with this program; if not, write to the Free Software
rem Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
rem
rem =========================================================================
rem
rem This script, to be run by a DBA, generates a script to drop
rem all of a user's objects - while retaining the Oracle account with
rem all its privileges. This is especially useful before reimporting
rem data on a per-user basis, for instance to update the contents of
rem a test database.
rem
rem Usage : @drop_user_objects <username>
rem
rem It generates drop_<username>_objects.sql
rem
rem Note that it take cares of dropping only objects which do not
rem depend on other objects (indexes and triggers for instance are
rem dropped with the associated tables, as are package bodies with
rem the associated packages) in order to avoid harmless but irritating
rem error messages. However, no in-depth checking of dependencies is
rem performed.
rem This may lead to a number of misleading error messages :
rem - if you are using clusters (God forbid!) the generated script will
rem try to drop the clustered tables after the cluster is gone.
rem - with Oracle8, 'hidden' tables such as overflow tables for index
rem organized tables will be explicitly dropped after the main
rem table has been dropped.
rem - etc.
rem
rem As a rule, do not worry about '... does not exist' messages
rem
rem Note that, when run, the script which actually drops the objects
rem will generate a spool file.
rem
set pause off
set echo off
set scan on
set verify off
set pagesize 0
set feedback off
set recsep off
set termout off
spool drop_&1._objects.sql
select 'spool drop_&1._objects.log'
from dual
/
select 'drop ' || object_type || ' ' || owner || '.' || object_name
|| decode(object_type,
'CLUSTER', ' including tables cascade constraints;',
'TABLE', ' cascade constraints;',
';')
from dba_objects
where owner = upper('&1')
and object_type in ('CLUSTER', 'TABLE', 'VIEW', 'SEQUENCE', 'SYNONYM',
'FUNCTION',
'PROCEDURE', 'PACKAGE')
/
select 'spool off'
from dual
/
spool off
set feedback on
set pagesize 24
set termout on
prompt Run @drop_&1._objects to drop &1's objects ...
3. DEV:
imp system/PASSWORD parfile=imp_user.par file=SCHEMANAME_expdat.dmp log=SCHEMANAME_imp.log fromuser=(SOURCE_SCHEMA) touser=(TARGET_SCHEMA)
IMP_USER.PAR:
BUFFER=64000
GRANTS=Y
INDEXES=Y
IGNORE=Y
ROWS=Y
4. SQL> exec dbms_utility.compile_schema('SCHEMA_NAME');
5. SQL> exec dbms_utility.analyze_schema('SCHEMANAME','COMPILE');
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
