Import & Export your Oracle Database (11.x) schema - note to myself
This post mostly serves (as many others) as a technical note to myself for future reference. I am not an Oracle DB Administrator neither I plan to become one in the future. As a software engineer I am required (like many of you) ,from times to times to interact or administer at some level the underlying RDBMS. In general, (that is a huge topic maybe for a future post) I am a supporter of the RDBMS agnostic implementations and designs, meaning try not to put too much logic on the services and tricks your RDBMS offers - keep your software project independent from the technicalities of the RDBMS as much as you can.
So one of my tasks was to export and import some specific DB schemas (users in Oracle) in order to speed up a regression and migration testing phase. In my case an oracle user schema contained all the database tables related to the application (single db user/schema paradigm under the same SID). I really had some issues, since I made the usual mistake to use the old tools provided by oracle - mostly targeting versions prior to 10g. After some digging around I discovered the new-er version of the tools + a confirmation from our DB expert Serafeim Karapatis (thanks mate)
The underlying Oracle DB server was version 11.0.2, hosted on a RedHat OS.
Step 1: Make sure to use the specific OS user that Oracle was installed, since it will have the appropriate path and related variable settings loaded. Specifically we are interested on the following
- ORACLE_HOME
- ORACLE_SID
export ORACLE_HOME=[path_ora_home export ORACLE_SID=yoursid
Step 2: In order to export a current schema - it is better to use the new tools provided by oracle for versions 10 and onwards. See Oracle Data Pump utility expdp . Some people continue to use the older exp tool- that kind of works (in backward) compatibility mode - but is strongly suggested to use the latest version. I was getting weird errors in this full export mode (especially when trying to get the statistics metadata)
Step 3: In order to import an exported schema - it is better to use the impdb tool (the comment regarding the older version imp that I made earlier, applies here as well). In the statement below I am trying to import - an exported schema into another with a different name. Please have a look on the TABLE_EXISTS_ACTION param and the potential values. I intentionally entered the REPLACE value (but this is might not be the case for you).
Hope that helps. There is lots of documentation and parameters on the above tools and you can do partial or full exports, depending on your needs. I am not sure I really find all Oracle related material easy to read - some of them written in a very cryptic way - if you ask me.