Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Export Entire Oracle Database/design
- Thread starter JasGrand
- Start date
SantaMufasa
Technical User
Jason,
We refresh our development schemas all the time with production data by doing Oracle User-level (schema-level) exports. (Remember that in Oracle, a user [also known as "schema"] is terminologically equal to a "database" in the terms of other vendors such as Sybase, SQL Serviers, MySQL, et cetera.) To do a User export, you can issue the following command at your operating system prompt:
The above means:
1) allocate 15MB of memory buffer cache for exporting purposes
2) do not compress table data into one large extent per table
3) preserve and export the GRANT information along with table data
4) every 1000 rows, display a "." on the screen to show progress
5) use locks (if necessary) to ensure that the data remains consistent during the export
6) the rest of the parameters are intuitive
The parameters can appear in any order. There are additional parameters that you can read about by issuing the operating-system-prompt command: "exp help=y".
The counterpart "imp" command to read in the dump file is:
Of course, you can run the "exp" commands for as many Oracle users/schemas as you need to cover your production system.
Also, it is very important to remember to "empty out" your target schemas before you do your import if you want the resulting development schema(s) to look/act just like your production schema(s).
Let us know if this resolves your need.
![[santa]](/data/assets/smilies/santa.gif "[santa] [santa]")
Mufasa
(aka Dave of Sandy, Utah, USA @ 15:35 (29Sep04) UTC (aka "GMT" and "Zulu"), 08:35 (29Sep04) Mountain Time)
We refresh our development schemas all the time with production data by doing Oracle User-level (schema-level) exports. (Remember that in Oracle, a user [also known as "schema"] is terminologically equal to a "database" in the terms of other vendors such as Sybase, SQL Serviers, MySQL, et cetera.) To do a User export, you can issue the following command at your operating system prompt:
Code:
exp buffer=15000000 compress=n grants=y feedback=1000 consistent=y file=<name_you_choose>.dump log=<name_you_choose>.log owner=<name_of_Oracle_user_to_export> userid=<username>/<password>1) allocate 15MB of memory buffer cache for exporting purposes
2) do not compress table data into one large extent per table
3) preserve and export the GRANT information along with table data
4) every 1000 rows, display a "." on the screen to show progress
5) use locks (if necessary) to ensure that the data remains consistent during the export
6) the rest of the parameters are intuitive
The parameters can appear in any order. There are additional parameters that you can read about by issuing the operating-system-prompt command: "exp help=y".
The counterpart "imp" command to read in the dump file is:
Code:
imp buffer=15000000 grants=y feedback=1000 fromuser=DHUNT touser=DHUNT file=<name_of_source>.dump log=<name_you_choose_to_log_import_progress>.log userid=<username>/<password>Of course, you can run the "exp" commands for as many Oracle users/schemas as you need to cover your production system.
Also, it is very important to remember to "empty out" your target schemas before you do your import if you want the resulting development schema(s) to look/act just like your production schema(s).
Let us know if this resolves your need.
Mufasa(aka Dave of Sandy, Utah, USA @ 15:35 (29Sep04) UTC (aka "GMT" and "Zulu"), 08:35 (29Sep04) Mountain Time)
- Status
Similar threads
- Locked
- Question
- Locked
- Question
