1

I'm a newbie to Oracle World.

I have to export, from computer A, tables and their data, to computer B.

So, my question is: is there a way to export user, tablespace, tables and their data with one single command? Or I have to create before a tablespace and a user in the computer B, and then import the tables?

Sorry, the question might seem stupid, but I'm a newbie, I'm a little confused and afraid to not fully understand the correct procedure/steps. Thank you

1

Usual way of doing that is data pump. As you want to copy user, you'd use the schema mode.

This example is based on 11gXE. If you use another database version, you might need to adjust it.


As data pump requires existence of a directory (Oracle object), you'd have to create it in both source and target database - connect as SYS and run something like this:

SQL> create directory my_dir as 'c:\temp';

Directory created.

SQL> grant read, write on directory my_dir to scott;

Grant succeeded.

SQL>

Physical directory (c:\temp in my example) resides on a database server; as I run 11gXE, my laptop is the database server as well.

When exporting, the export (dmp) file, as well as log file, will be created in that directory. The same goes for import - you'll have to put the dmp file into it.

In both databases, I'm creating a user which will perform export/import and I'll grant it DBA role so that - during import - it also creates user. Otherwise, it'll fail. Also, I'm granting datapump_exp_full_database, datapump_imp_full_database so that it export privileges user already has (otherwise, everything will be exported, but newly imported user will lack in any privilege, even create session).

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

SQL> create user importer identified by imp
  2  default tablespace users
  3  temporary tablespace temp
  4  profile default
  5  quota unlimited on users;

User created.

SQL> grant dba, datapump_exp_full_database, datapump_imp_full_database to importer;

Grant succeeded.

At the operating system command prompt, run EXPDP to perform export:

expdp importer/imp@xe schemas=scott directory=my_dir dumpfile=scotte.dmp logfile=scott_e.log

It looks like this:

c:\Temp>expdp importer/imp@xe schemas=scott directory=my_dir dumpfile=scotte.dmp logfile=scott_e.log


Export: Release 11.2.0.2.0 - Production on ╚et Ruj 6 21:13:55 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Starting "IMPORTER"."SYS_EXPORT_SCHEMA_01":  importer/********@xe schemas=scott directory=my_dir dum
pfile=scotte.dmp logfile=scott_e.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."BONUS"                             6.210 KB       1 rows
. . exported "SCOTT"."DEPT"                              5.929 KB       4 rows
. . exported "SCOTT"."EMP"                               8.484 KB      12 rows
. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . exported "SCOTT"."EMP_LOG"                               0 KB       0 rows
. . exported "SCOTT"."TABLE01"                               0 KB       0 rows
. . exported "SCOTT"."TABLE02"                               0 KB       0 rows
. . exported "SCOTT"."TABLE03"                               0 KB       0 rows
Master table "IMPORTER"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for IMPORTER.SYS_EXPORT_SCHEMA_01 is:
  C:\TEMP\SCOTTE.DMP
Job "IMPORTER"."SYS_EXPORT_SCHEMA_01" successfully completed at 21:14:18

c:\Temp>

Copy dmp file to target computer and perform import.

I'll just drop user Scott (as I don't have access to another database/server right now):

SQL> show user
USER is "SYS"
SQL> drop user scott cascade;

User dropped.

SQL>

OK, let's import it - operating system command prompt again; command is almost equal to expdp. If tablespace names are different in the source and target databases, you'll need the REMAP_TABLESPACES parameter.

impdp importer/imp@xe schemas=scott directory=my_dir dumpfile=scotte.dmp logfile=scott_i.log

Import looks like this:

c:\Temp>impdp importer/imp@xe schemas=scott directory=my_dir dumpfile=scotte.dmp logfile=scott_i.log


Import: Release 11.2.0.2.0 - Production on ╚et Ruj 6 21:14:50 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Master table "IMPORTER"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "IMPORTER"."SYS_IMPORT_SCHEMA_01":  importer/********@xe schemas=scott directory=my_dir dum
pfile=scotte.dmp logfile=scott_i.log
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
ORA-39082: Object type TYPE:"SCOTT"."IBS_S_DOCUMENT_T_1" created with compilation warnings
ORA-39082: Object type TYPE:"SCOTT"."IBS_S_DOCUMENT_T_1" created with compilation warnings
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."BONUS"                             6.210 KB       1 rows
. . imported "SCOTT"."DEPT"                              5.929 KB       4 rows
. . imported "SCOTT"."EMP"                               8.484 KB      12 rows
. . imported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . imported "SCOTT"."EMP_LOG"                               0 KB       0 rows
. . imported "SCOTT"."TABLE01"                               0 KB       0 rows
. . imported "SCOTT"."TABLE02"                               0 KB       0 rows
. . imported "SCOTT"."TABLE03"                               0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ORA-39082: Object type ALTER_PROCEDURE:"SCOTT"."A1_P_LOG" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"SCOTT"."OVERDUETODAY" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"SCOTT"."PROC" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"SCOTT"."PROC_SECOND" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"SCOTT"."P_MATCH" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"SCOTT"."P_SURVEY" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"SCOTT"."REPORTS_ITEM_INFO" created with compilation warnings

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
ORA-39082: Object type PACKAGE_BODY:"SCOTT"."MY_JOIN_TEST_SP_PACKAGE_3" created with compilation war
nings
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
ORA-39082: Object type TRIGGER:"SCOTT"."TRG_BU_DEPT" created with compilation warnings
ORA-39082: Object type TRIGGER:"SCOTT"."TRG_BU_DEPT" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "IMPORTER"."SYS_IMPORT_SCHEMA_01" completed with 12 error(s) at 21:14:55


c:\Temp>

Certain objects were imported in invalid state; no wonder, I'm using Scott for testing purposes, creating & dropping objects all the time. In your case, import will probably look prettier.

Let's try to connect to newly imported Scott:

c:\Temp>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.2.0 Production on ╚et Ruj 6 21:30:44 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
DEPT_EXT                       TABLE
EMP                            TABLE
EMP_LOG                        TABLE
SALGRADE                       TABLE
TABLE01                        TABLE
TABLE02                        TABLE
TABLE03                        TABLE

9 rows selected.

SQL> create table test (id number);

Table created.

SQL>

Seems to be OK. Give it a try; you can't spoil anything. If anything goes wrong at the target database, just drop the imported user and try again.

I suggest you read data pump documentation. Seeing an example is nice, but - you'd better understand what's going on.

Good luck!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.