0

So I'm a DBA newbie and I've got an Oracle Enterprise server running in production with the following information under All Reports -> Data Dictionary Reports -> Version Banner, under "Your_Database_Settings".

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production 
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

I would like to export/clone the database I have running on the server and import it into a new Oracle 11g Express installation to run some sandboxed tests.

Can someone help me with detailed instructions on how to make that happen?

Improve this question
4
  • how big is the database in file size? Does it use partitioning?
    – kevinskio
    Dec 8, 2015 at 17:02
  • Express edition has it's limitations. Like space, it is limited to 11GB of data. So you should use datapump export with query clause, to export subset of data(assuming that in your enterprise database there is lot more data than 11GB). And on the other side use datapump import to import the data. See examples in Oracle documentation.
    – mutap
    Dec 8, 2015 at 18:41
  • @kevinsky It's around 30 GB in size and does not use partitioning.
    – kfkhalili
    Dec 9, 2015 at 15:56
  • @mutap Thanks I'll give that a try. If you can detail the procedure below, I'd be grateful. Please remember my novice status. :)
    – kfkhalili
    Dec 9, 2015 at 15:58

1 Answer 1

Reset to default
0

This is example with HR user(example user in Oracle database) and Countries table. We'll extract a couple of rows with query - where region_id=2(table has 25 rows). I created another user, HR2 and table Countries with same structure(but no FK constraints to ease import). From HR we will export subset of Countries table and in HR2 we'll import the data.

> [oracle@orcla ~]$ expdp system/password tables=hr.countries dumpfile=testdump.dmp content=data_only query=hr.countries:\"where region_id=2\"

Export: Release 11.2.0.1.0 - Production on Wed Dec 9 23:51:33 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** tables=hr.countries dumpfile=testdump.dmp content=data_only query=hr.countries:"where region_id=2" 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
. . exported "HR"."COUNTRIES"                            5.921 KB       5 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/orcl1/dpdump/testdump.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 23:51:35

[oracle@orcla ~]$ impdp system/password directory=data_pump_dir dumpfile=testdump.dmp remap_schema=hr:hr2
Import: Release 11.2.0.1.0 - Production on Wed Dec 9 23:52:04 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=data_pump_dir dumpfile=testdump.dmp remap_schema=hr:hr2 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR2"."COUNTRIES"                           5.921 KB       5 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 23:52:06

Ok, now to check the result.

sqlplus hr2/hr2

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 10 00:02:39 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from countries;

  COUNT(*)
----------
         5

This is a general idea so adjust it to your environment and needs.

Improve this answer

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.