كيف يمكن عمل نسخ ل schema من قاعدة بيانات الى اخرى ؟
بدون استخدام امر exp

من قاعدة البيانات المراد اخذ نسخة منها

exp system/****@[i]dbname[/i] owner=[i]schemaname[/i] file=[i]c:\expfile.dmp [/i]direct=y

الي قاعدة البيانات المراد عمل import لها

imp system/****@[i]dbname [/i]fromuser=[i]schemaname[/i] touser=[i]shemaname[/i] file=[i]c:\expfile.dmp[/i]

Below is a script written several years ago to copy users from one instance
to another..  After using these scripts, you can uese exp/imp to copy
tables.  Obviously in 10g you can use datapump for this.

/********************************************************************************
**********/
/* The move_users script needs the source database and the target database
defined        */
/* then it creates the following output
scripts:                                          */
/*
*/
/* CREATE_PROFILES.SQL:  This script creates a profile in the target
database that is not */
/*                       in the source
database.                                          */
/* ALTER_PROFILES.SQL:   This script moves changes in profiles from the source database   */
/*                       to the target
database.                                          */
/* CREATE_USERS.SQL:     This script creates a user in the target database
that is not in */
/*                       the source
database.                                             */
/* CHANGE_USERS.SQL:     This script moves changes in the user
(PASSWORD,                 */
/*                       DEFAULT_TABLESPACE, A.TEMPORARY_TABLESPACE,
A.PROFILE) from the  */
/*                       source database to the target
database.                          */
/* ADD_ROLES.SQL         This script grants roles to users in the target
database that    */
/*                       were in the source
database.                                     */
/* CREATE_ROLES.SQL      This script creates roles in the target database
that where */
/*                       in the source
database.                                          */
/********************************************************************************
**********/


SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
DEFINE sourceDB = '@SRCINST'
DEFINE targetDB = '@'DESTINST

/*******************************CREATE_PROFILES.SQL******************************
**********/
SPOOL CREATE_PROFILES.SQL

SELECT 'CREATE PROFILE '||PROFILE||' LIMIT '||RESOURCE_NAME||' '||LIMIT||';'
FROM DBA_PROFILES&sourceDB. WHERE PROFILE IN (
SELECT PROFILE FROM DBA_PROFILES&sourceDB. MINUS
SELECT PROFILE FROM DBA_PROFILES&targetDB.
) AND ROWNUM < 2;

SPOOL OFF

/*******************************ALTER_PROFILE.SQL********************************
**********/

SPOOL ALTER_PROFILE.SQL

SELECT 'ALTER PROFILE '||B.PROFILE||' LIMIT '||A.RESOURCE_NAME||'
'||A.LIMIT||';'
FROM DBA_PROFILES&sourceDB. A,
     DBA_PROFILES&targetDB. B
WHERE A.limit != B.limit AND A.profile = B.profile AND A.RESOURCE_NAME = B.RESOURCE_NAME;

SPOOL OFF

/*******************************CREATE_USERS.SQL*********************************
**********/

SPOOL CREATE_USERS.SQL
SELECT 'CREATE USER '||USERNAME||' IDENTIFIED BY VALUES '''||PASSWORD||
     ''' DEFAULT TABLESPACE '||DEFAULT_TABLESPACE||' TEMPORARY TABLESPACE
'
     ||TEMPORARY_TABLESPACE||' PROFILE '|| PROFILE||';'
FROM DBA_USERS&sourceDB. WHERE USERNAME IN (
    SELECT USERNAME FROM DBA_USERS&sourceDB. MINUS
    SELECT USERNAME FROM DBA_USERS&targetDB.
  );

SPOOL OFF

/*******************************CHANGE_USERS.SQL*********************************
**********/

SPOOL CHANGE_USERS.SQL
SELECT 'ALTER USER '||b.USERNAME||' IDENTIFIED BY VALUES '''||A.PASSWORD||
     ''' DEFAULT TABLESPACE '||A.DEFAULT_TABLESPACE||' TEMPORARY
TABLESPACE '||
     A.TEMPORARY_TABLESPACE||' PROFILE '||A.PROFILE||';'
FROM DBA_USERS&sourceDB. A,
     DBA_USERS&targetDB. B
where A.USERNAME = B.USERNAME AND A.PASSWORD||A.DEFAULT_TABLESPACE||A.TEMPORARY_TABLESPACE||A.PROFILE!=
     B.PASSWORD||B.DEFAULT_TABLESPACE||B.TEMPORARY_TABLESPACE||B.PROFILE;

SPOOL OFF

/****************************CREATE_ROLES.SQL************************************
**********/

SPOOL CREATE_ROLES.SQL

SELECT 'CREATE ROLE '||ROLE||';'
FROM DBA_ROLES&sourceDB. WHERE ROLE IN (
SELECT ROLE FROM DBA_ROLES&sourceDB. MINUS
SELECT ROLE FROM DBA_ROLES&targetDB.
);

SPOOL OFF

/*******************************ADD_ROLES.SQL************************************
**********/

SPOOL ADD_ROLES.SQL

SELECT 'GRANT '||GRANTED_ROLE||' TO '||GRANTEE||';'
FROM DBA_ROLE_PRIVS&sourceDB. WHERE (GRANTEE, GRANTED_ROLE) IN (
SELECT GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS&sourceDB. MINUS
SELECT GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS&targetDB.
);

SPOOL OFF

/********************************************************************************
**********/

SPOOL ADD_SYS.SQL

SELECT 'GRANT '||PRIVILEGE||' TO '||GRANTEE||';'
FROM DBA_SYS_PRIVS&sourceDB. WHERE (GRANTEE, PRIVILEGE) IN (
SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS&sourceDB. MINUS
SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS&targetDB.
);

SPOOL OFF

/********************************************************************************
**********/
/********************************************************************************
**********/
/**************************************************************************/
move db_links

select 'create public database link '||db_link||' using '''||db_link||''';'
from dba_db_links@oltpqa where USERNAME is null and db_link not in (select db_link from dba_db_links);


/*
@CREATE_PROFILES.SQL
@ALTER_PROFILES.SQL
@CREATE_USERS.SQL
@CHANGE_USERS.SQL
@ADD_ROLES.SQL
@ADD_SYS.SQL
*/

UNDEFINE sourceDB
UNDEFINE targetDB

SET FEEDBACK ON @LOC

REF

بعد ماتنفذ الاسكريبت ده بين الاتنين داتابيز ...تقدر تاخد الداتا من الجداول على هيئه FLAT FILES OR EXCEL SHEETS وتستخدم sql loader وتضيف البيانات دى تانى جوه الجداول بالترتيب
اتمنى اكون افدتك

كيف يمكن عمل نسخ للبعض الجداول فقط من مستخدم معين يعني مثلا اريد نسخ فقط اربع جداول من داخل المستخدم scott هل يجب ان انسخهم واحدا واحدا ام هنام امر ينسخهم مره واحده
سؤال اخر
هل ال داتا بيس 8i تدعم الامر imp لاني اليوم قمت بعمل امبورت لملف dmp ففوجئت برسالة من الويندوز ان هذا الامر غير مدعموم وها هو الامر الذي استخدمته
imp userid=scott/tiger@orcl file=e:\mando.dmp full= y

الأخ / احمد حمدي
جهد متميز كالعادة ... جزاك الله خيرا

الأخ / awn78
لوكان عندك اوراكل نسخة 8i
اكتب الأمر التالي

exp80 username/password file=path.dmp log=path.log

أما لاستيراد البيانات

imp80 system/manager file=path.dmp fromuser=username touser=usernam

salaam eng.t_hassan

D:\dmp>impdp system schemas=scott network_link=ezz_sis  parallel=5 logfile=scott_11oct_db1_db2.log

*** network_link from server 1 has db1 to server 2 has db2 but same schema