| import [message #69877] |
Fri, 15 March 2002 08:22  |
willskaren
Messages: 8
Registered: March 2002
|
Junior Member |
|
|
hi,
i have downloaded a dump file & now i want to import this file in my oracle database (8.1.6) on NT.
the dump file is a database ,all of it is in the system tablespace. i want to import from there into a schema's default tablespace i.e. data
i have tried but i am not able to ,as it goes into the system tablespace only instead of data....
please explain as to what's going on????????
thanx
|
|
|
|
| Re: import [message #69878 is a reply to message #69877] |
Fri, 15 March 2002 08:39  |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
You need to identify each schema and do a user level import. Once you have identified the schemas then:
1. Create the schema/user and make their default tablespace the new tablespace.
2. alter user SCHEMA quota 0M on SYSTEM.
3. alter user SCHEMA quota unlimited on NEWTABLESPACE.
4. imp system/manager file=YOURDUMP.dmp log=imp.log fromuser=SCHEMA touser=SCHEMA ignore=y
When the user doesn't exist import will create them and give them quota on the system tablespace and set that as their default tablespace because that's what it had when they were exported. When import does a switch user it creates the objects in their default tablespace. By pr-creating the user and setting to a different tablespace it will put the objects there. I would do a couple more things to the schema to be sure. Give the schema only CONNECT. Do not give any other roles and give them the following additional system priv's:
grant create procedure to SCHEMA;
grant create trigger to SCHEMA;
grant create type to SCHEMA;
once you have done the import you can give any additional privs back.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
