By Toothless


2018-05-16 12:58:59 8 Comments

I've installed Oracle 11g on my local machine and have created multiple tables. Now I want to share my local database copy to the entire team so that they can use same the tables without re-creating them. (I don't want them to use my local machine as a server) Is there any way to achieve this? so that I can save days of time

1 comments

@Littlefoot 2018-05-17 06:58:43

From my point of view, if you don't want them to connect to your computer (i.e. so that it acts like a database server), everyone will probably have to work on his/her own database, right? It means that all of them will have to install the database onto their computers. Furthermore, it means that you won't be able to share code nor data in the future. Changes you (or anyone else) makes won't be visible to other members of your team.

Your task can be done in two ways. The first one is to provide CREATE TABLE script(s), possibly INSERT INTO sample data, as well as CREATE PROCEDURE / FUNCTION / PACKAGE / whatever. All of that can fit into a .SQL file they would run in their databases.

Another - and probably better option - is to export your schema. I'd suggest you to use the original EXP (export) utility in this case. The result is a DMP (binary) file. Once they get it, they would use the IMP utility (import) and simply import everything into their database.

Here's an example.

You should run this:

M:\>exp scott/[email protected] file=test.dmp

Export: Release 11.2.0.2.0 - Production on ╚et Svi 17 08:53:02 2018

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes
Export done in EE8MSWIN1250 character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                          BONUS          0 rows exported
. . exporting table                           DEPT          4 rows exported
. . exporting table                          DUMMY          1 rows exported
. . exporting table                            EMP         14 rows exported
. . exporting table                       SALGRADE          5 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

M:\>

They should run this:

M:\>imp mike/[email protected] file=test.dmp full=y

Import: Release 11.2.0.2.0 - Production on ╚et Svi 17 08:57:19 2018

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by SCOTT, not by you

import done in EE8MSWIN1250 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into MIKE
. . importing table                        "BONUS"          0 rows imported
. . importing table                         "DEPT"          4 rows imported
. . importing table                        "DUMMY"          1 rows imported
. . importing table                          "EMP"         14 rows imported
. . importing table                     "SALGRADE"          5 rows imported
Import terminated successfully without warnings.

M:\>

As you can see, two simple commands can solve all your problems :)

@Frank Schmitt 2018-05-17 07:11:48

I would advise against using the old exp/imp commands. Their successors expdp and impdp have been available since version 10, and there are lots of features in newer database versions that exp and imp don't handle correctly (empty tables: serverfault.com/q/143794/461144, XMLType columns, sys_context, ...)

@Littlefoot 2018-05-17 07:21:40

Yes, @Frank, I agree. And yet, disagree. I said, in this case I'd use the original EXP and IMP. Regarding what the OP wants to do, Data Pump - which requires creating a directory & granting required privileges - might be an overkill. I presume (true, I might be very wrong) that those guys & gals don't bother with what you have mentioned, not in current stage of their project (a homework, maybe?). But, I have no objections - if they want to use Data Pump, go with it. It just won't be that simple for none of them.

@Frank Schmitt 2018-05-17 10:49:04

Fair enough. And you're right - setting up datapump is more involved than using exp/imp.

@Toothless 2018-05-17 12:36:57

Thank you @FrankSchmitt . Where can I find the exported test.dmp file? Where should I paste this .dmp file in second system in which I wanted to import?

@Littlefoot 2018-05-17 12:39:58

I believe I didn't use invisible ink while posting examples of how EXP and IMP are used. Both are executed at the operating system command prompt. DMP is created in the current directory.

@Toothless 2018-05-17 12:41:42

:) Yup sorry @Littlefoot tired with many tasks.

@Littlefoot 2018-05-17 12:45:28

Slow down; job doesn't have legs & won't run from you. If you're tired, you're making mistakes. Not seeing what I wrote is nothing, really; worry about true errors you might have made.

Related Questions

Sponsored Content

21 Answered Questions

[SOLVED] Insert into ... values ( SELECT ... FROM ... )

9 Answered Questions

7 Answered Questions

[SOLVED] What are the options for storing hierarchical data in a relational database?

3 Answered Questions

Configure Oracle 11g Database in Oracle Linux 6

2 Answered Questions

[SOLVED] How to create a database in oracle 11g express edition?

1 Answered Questions

4 Answered Questions

1 Answered Questions

[SOLVED] how to use installed oracle 11g in ubuntu?

1 Answered Questions

[SOLVED] How to duplicate Oracle database without sysdba privilege

  • 2013-08-14 19:00:06
  • Raistlin
  • 666 View
  • 0 Score
  • 1 Answer
  • Tags:   oracle oracle11g

3 Answered Questions

Sponsored Content