Re: Import into a new table space is BROKEN!

From: <suskind_at_bbs.mdcbbs.com>
Date: 16 Sep 92 05:55:08 GMT
Message-ID: <1992Sep16.055508.1_at_bbs.mdcbbs.com>


In article <drt.716542402_at_brolga>, drt_at_brolga.cc.uq.oz.au (David Taylor) writes:
>
> I am having a problem with some undocumented features of import.
>
> The export file came from version 6somethingorother on a VAX. It was
> a straight user export.
> I want to import the tables under a different username on a DOS box
> also running version 6somethingorother.
>
> I created a special tablespace for the data (~200M) on a new disk.
> I created a user and granted resource access to the table space.
> I made the new table space the default for this user.
>
> When I run import it appears to work, but it creates
> the tables in the SYSTEM table space, and falls over eventually
> because there isn't enough space there. The manual clearly states
> that it should load into the default table space for this user.
> It even gives an example of how to use this feature to transfer
> stuff between table spaces.

I tried this myself from a FULL system export to a PC with a 150 MB disk. I wanted to expiriment with the db offline.

Even after setting the default user's tablespace to be other than system and only granting him quota on the other tablespace, the FULL import just put the stuff back into system. The problem is that when user SYSTEM's stuff is imported it hits all the tables that tell the user's defaults. In my case this user did have resource priv, something I am now going to revoke. Each time his stuff ended up back in the system tablespace. Worse I wanted the indexes in a different tablespace from the tables. The only way out of this mess was to do the import in pieces. Creating the tables by hand was out of the question, as there are too many tables and indexes. I wish the "show" option of import gave me a script that was usable to create the tables, but it does not (I am looking for one if it exists). Here is the way I did the import:

rem -- create the database --
sqldba _at_makora.ini
rem -- import SYSTEM's tables, but no grants -- imp file=\15maydat fromuser=system grants=N userid=system/manager rem -- modify the user so his stuff goes to another tablespace -- sqldba _at_fixuser1.sql
rem -- create the tables for this user from import -- imp file=\15maydat fromuser=cmcntl indexes=n rows=n userid=system/manager rem -- fix the user again so his indexes go to other tablespace -- sqldba _at_fixuser2.sql

rem	-- NOW import the user's tables, grants, and indexes --
rem	-- Since the tables are created already the data goes there --
rem	-- Indexes are not created so their stuff goes to the user's --
rem	-- current default tablespace --
imp file=\15maydat fromuser=cmcntl indexes=y rows=y grants=y 
		userid=system/manager
rem	-- re-run the fix on the user info (probably not needed) --
sqldba _at_fixuser2.sql
rem	-- Now import SYSTEM's grants --
rem	-- (I use system to grant to other users --
imp file=\15maydat fromuser=system rows=n grants=y userid=system/manager

fixuser1.sql
---
connect internal;
alter user system identified by manager;
revoke resource from cmcntl;
revoke resource on system from cmcntl;
grant connect to cmcntl identified by foo;
alter user cmcntl default tablespace cmuser temporary tablespace cmtemp;
grant resource on cmuser to cmcntl;
grant resource on cmtemp to cmcntl;
exit
==================================================
fixuser2.sql
---
connect internal;
alter user cmcntl default tablespace system;
grant resource on system to cmcntl;
alter user system identified by manager;
exit
--
sqldba _at_makora.ini
imp file=\15maydat fromuser=system grants=N userid=system/manager
sqldba _at_fixuser1.sql
imp file=\15maydat fromuser=(cmcntl,tanyaf,sandir,barrys) indexes=n rows=n
userid=system/manager
--
sqldba _at_fixuser2.sql
imp file=\15maydat fromuser=cmcntl indexes=y rows=y grants=y
userid=system/manager
sqldba _at_fixuser2.sql
imp file=\15maydat fromuser=system rows=n grants=y userid=system/manager

--------------
Now I'm no DBA expert, but this got the job done. It just took a couple of
passes. I wish Import would give me the script to create all the tables and
indexes so I could just run the script, then do the FULL import.

+---------------------------------------------------------------------+
Barry A. Suskind                  Internet:   suskind%edoras_at_mdcbbs.com
MaBell: 703-560-5000x2348             UUCP: uunet!mdcbbs!edoras!suskind
E-Systems / Melpar Division 7700 Arlington Blvd, Falls Church, VA 22046
JSNM                                             Just Stark Naked Magic
Received on Wed Sep 16 1992 - 07:55:08 CEST

Original text of this message