| Restoring part of the contents of a full database export [message #73912] |
Mon, 02 August 2004 20:52  |
Shibaji Ghosh
Messages: 39
Registered: April 2002
|
Member |
|
|
Hi,
I have an export dump file of an Oracle 9.2 instance. The size of the dump file is around 18GB. However, I want to import only 3 schemas from the dump file. Please let me know whether I can do the following.
i.> Can I import only 3 schemas from the full database export. What are the steps for doing the same.
ii.> The extent size of the objects in the dump file are on the higher side. I want to change the extent sizes to bare minimum, since I am having space constraints. Is there any workaround for the same.
Please let me know the above mentioned needs. I will be installing on a LINUX box.
Thanks in advance,
Shibaji
|
|
|
|
| Re: Restoring part of the contents of a full database export [message #73913 is a reply to message #73912] |
Tue, 03 August 2004 00:06  |
K Saseendra Babu
Messages: 1
Registered: August 2004
|
Junior Member |
|
|
I got an ORACLE specific problem explained below.
I have an Oracle 9i database "ORCL01" in server-1. And created a large number of tables. Later I have got another Oracle 9i database "ORCL01" in server-2. Both times I have adopted "Typical Installation". Now I want to port all tables of "ORCL01" of server-1 to ORCL01" of server-2 to. Database level Export/Import is giveing problems since both default database names are same (ORCL01).
I feel like managing the problem as follows.
Export all tables in ORCL01" of server-1 and copy all binary files thus generated to server-2 and
use Import utilty.
Is it possible to export multiple tables (any objects) to single binary file and import from them to multiple tables?Hope you have realised my problem and wil respond.
Regards
K Saseendra Babu
Deputy Director
CDAC, Trivandrum.
|
|
|
|
| Re: Restoring part of the contents of a full database export [message #73914 is a reply to message #73913] |
Tue, 03 August 2004 01:06 |
Shibaji Ghosh
Messages: 39
Registered: April 2002
|
Member |
|
|
The question which I posted was of a different kind.
Anyway the answer to your question is as follows. Yes, you can very much adopt the methodology you are willing to go for. Export can be of three type, database level, user level and table level. You should go for the last step. Perform a table level export for the tables you need. One single .dmp file can hold all the contents. Then restore the full .dmp file into the second instance.
Shibaji
|
|
|
|
| Re: Restoring part of the contents of a full database export [message #73916 is a reply to message #73912] |
Tue, 03 August 2004 01:27 |
Tarun Biswas
Messages: 23
Registered: May 2004
|
Junior Member |
|
|
yes u can import only three schemas from full database export. import command like this.
imp x/x file=y.dmp log=1.log fromuser=x touser=x
by the above command only x schema will be imported from full export dump file. In this way u can import three schema. before importing u have to create that three users mamually.
tarun
|
|
|
|
| Re: Restoring part of the contents of a full database export [message #73923 is a reply to message #73916] |
Tue, 03 August 2004 20:52 |
Shibaji Ghosh
Messages: 39
Registered: April 2002
|
Member |
|
|
Thanx for the response. That answers the first question. Now, I intend to know how do I create the objects of the 3 schemas with a lesser extent size. All I have with me is the full database export dump file. Its from a production environment. I am running out of space when I try to restore from that dump file. Once I am able to create the objects then I can import with fromuser=<..> touser=<..> with ignore=Y
Thanx again
Shibaji
|
|
|
|
| Re: Restoring part of the contents of a full database export [message #73924 is a reply to message #73923] |
Wed, 04 August 2004 01:42 |
Tarun Biswas
Messages: 23
Registered: May 2004
|
Junior Member |
|
|
can u identifiy the tables of three schemas ? if u able to identify the tables then open the export dump file in vi editor , find and locate the tables u will find "create tables " with storage clause modify the extent parameter with your required size.
if u not able to identify the tables of respective schemas , open the dump file in vi editor , go to end of the file then give the following commands
:%s/INITIAL ...... FREELISTS/INITIAL 65536 FREELISTS
before given the commands u check in the table storage clause INITIAL ...... FREELISTS combination is there or not. whatever combination is there with INITIAL clause give that combination .
Here ...... means six digit extent size. u will give 7 dots , 8 dots ,9 dots upto your maximum extent size. if u not understand pls mail back.
Tarun
|
|
|
|
| Re: Restoring part of the contents of a full database export [message #73930 is a reply to message #73924] |
Wed, 04 August 2004 04:19 |
Shibaji Ghosh
Messages: 39
Registered: April 2002
|
Member |
|
|
This seems to be a very good approach. Quite understandable too. However, there are a few questions in it.
1.> It is practically impossible to open an 18GB export dump file in any editor. Any way out ???
2.> I presume we need to create the 3 schemas first and then restore from the dump file with fromuser and touser. Do we need to have the same tablespace name (default tablespace) as it was from where the export was taken ? Can we restore the dump in a schema with any tablespace name as its default tablespace ?
3.> I presume we need to go for the initial .... next .... combination in case there are some way out to open the dump file in vi, rather than the freelist. Just enquering on the same.
4.> Is there any possibilities to use a shell script on the dump file. How ??
Thanx again for all the responses.
Shibaji
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
