Background: For the past few days weeks, I have been working on a way to export selective data from one schema to import into another. Now the problem is that “selective” data refers to the data as stored across various tables in the custom application that I’ve been working on, with elaborate relational links between the tables ( if you’re curious, the table count on the related data was about 65) - so it was not just couple of tables that I had to export.
They say “Necessity is the Power of Invention” - a quote which I fully understood the impact today. I’ve been trying to understand Oracle’s Hierarchical Queries for a while now, but never fully understood the situation under which or when it would be used. Yesterday I got a reason to use it. Let me begin.
Couple of days ago at work I was asked to create a new environment(schema, user) for specific application testing conditions. While that was no big deal, I was also asked to import all the data from existing production environment into a new environment - and I was in a bit of bother here - generally the whole importing/exporting is done by DBA’s not developers. While I knew the usage of exp and imp commands, creating schema would require stuff like tablespace creating, adding data files and more importantly - grants which I had NO clue on. So after a bit of experimenting and consulting with my fellow colleague Rupam, I finally managed to export and import the data. This post is on how-to go about doing it.
If you want to skip all the commands and all just get the import / export done in 1-click try Easy Dump Oracle 1.1 - a tool which I found on the database server after all the headbanging 😐
Anyways here’s a step by step process on how you export and import the data - do note that Easy Dump Oracle can import data ONLY if required grants are given to the user and tablespace has been allocated. For that you need to follow the below mentioned steps.
Over the period of time that I’ve been working on PL/SQL, the one the thing that we end up doing often is reset the sequences we use for Primary keys in our tables. Why do we do that, you might wonder.
Consider this case: