Re: Import into a new table space is BROKEN!
Date: Tue, 15 Sep 92 18:42:21 GMT
Message-ID: <1992Sep15.184221.25014_at_srg.srg.af.mil>
David Taylor writes:
>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.
....[deleted]
Yup, Unix (SCO and SunOS) have this problem too. My way around it is to write a shell script to dump the names of each table to a file and then create an export dump script to export the INDIVIDUAL tables. This creates a single dump file with each table in it (and data) without requireing that the target tablespace be of the same name as the original tablespace.
As an illustration of what I mean, perform an ASCII dump
of a regular user dump file
(UNIX: cat expdmp.dat |strings >expdmp.text)
and notice the "TABLESPACE" clause in each "CREATE TABLE"
command. Arggg!
piece of shell script:
...[deleted]
echo "Exporting Tables.......\c" echo "userid=$LOGIN" >./params.exp echo "file=tables.dmp">>./params.exp echo "grants=Y" >>./params.exp echo "indexes=N" >>./params.exp echo "constraints=Y" >>./params.exp echo "tables=(\c" >>./params.exp
sqlplus $LOGIN _at_$PGM_DIR/get_tb_lst.sql >/dev/null OLD_IFS=$IFS
IFS=$LINE_IFS
for LINE in `cat tables.all |grep -v "^$"` do
TABLE=`echo $LINE|cut -f1 -d" "`
echo "$C$TABLE\c" >>./params.exp
C=", "
done
echo ")" >>./params.exp
IFS=$OLD_IFS
$EXP_DIR/exp parfile=./params.exp
...[deleted]
Where
LINE_IFS='
'
and get_tb_lst.sql is:
REM This script creates a list of tables to be exported.
set heading off
set feedback off
set linesize 35
set pagesize 1000
spool tables.all
select TABLE_NAME from user_tables
order by 1 ;
spool off
exit
-- ######################################################### David E. Trum Database Administrator Email: uunet!srg!dtrum ARINC Research Corporation Phone: (410)266-4772 2551 Riva Road MS 5-230 Fax: (410)573-3024 Annapolis, MD 21401-7461 ######################################################### -- ######################################################### David E. Trum Database Administrator Email: uunet!srg!dtrumReceived on Tue Sep 15 1992 - 20:42:21 CEST