Export/Import
Zeyad S
Does anyone know how to change the tablespace when you import a
table(s)? I have looked at the manuals and nothing is mentioned in this
matter. When exporting a table, Oracle specifies the tablespace name in
the create table statement. When importing the table, I would like to
change that tablespace or leave it to the default of the user importing
that table.
Any hint will be appreciated.
Thanks,
ZS
Yaroslav Perventsev
AFAIK...
import use default user tablespace(if tablespace from which exported doesn't
exist) .
Import/export in one database? If different, have another database same
tablepace?
Yaroslav.
Zeyad S <swei...@yahoo.com> сообщил в новостях
следующее:3B5D2A27...@yahoo.com...
Erwin Dondorp
> AFAIK...
> import use default user tablespace(if tablespace from which exported doesn't
> exist) .
Sorry, import uses the tablespace that the table was originally in when
the export was done.
But you can force the tables to be in a certain tablespace by first
creating all the empty tables in the wanted tablespace.
On import, the table-create will fail, but that can be ignored.
After that the data will be loaded into the table.
Erwin
> Zeyad S <swei...@yahoo.com> сообщил в новостях
> следующее:3B5D2A27...@yahoo.com...
Yaroslav Perventsev
Erwin Dondorp <erwi...@wxs.nl> сообщил в новостях
следующее:3B5D33F5...@wxs.nl...
> Yaroslav Perventsev wrote:
> > AFAIK...
> > import use default user tablespace(if tablespace from which exported
doesn't
> > exist) .
>
> Sorry, import uses the tablespace that the table was originally in when
> the export was done.
Especially for you. Try this:
In first database:
sqlplus internal <<EOD
create user user1 identified by ... default tablespace ts1; grant ... etc.
connect user1@db1;
create table test(a number) tablespace ts1;
insert into table test select 10 from dual;commit;
exit
EOD
exp user1/test@db1 file=user1.dmp;
In second database:
sqlplus internal <<EOD
create user user2 identified by ... default tablespace ts2; grant ... etc.
exit
EOD
imp user2/test@db2 file=user1.dmp fromuser=user1 touser=user2
Import successful imported test table into tablespace ts2 of user2;
What wrong?!
Best regards!
Yaroslav.
andrew_webby at hotmail
As Erwin said, all he has to do is create the table layout with the new TS
first, then run the import.
"Yaroslav Perventsev" <p_yar...@cnt.ru> wrote in message
news:9jjdja$k45$1...@news247.cnt.ru...
> Hello!
>
> Erwin Dondorp <erwi...@wxs.nl> сообщил в новостях
> следующее:3B5D33F5...@wxs.nl...
> > Yaroslav Perventsev wrote:
<snip>
Yaroslav Perventsev
See original question!
Yaroslav.
andrew_webby at hotmail <sp...@no.thanks.com> сообщил в новостях
следующее:995968737.8742.0...@news.demon.co.uk...
> But now the table is owned by a different schema.
>
> As Erwin said, all he has to do is create the table layout with the new TS
> first, then run the import.
>
> "Yaroslav Perventsev" <p_yar...@cnt.ru> wrote in message
> news:9jjdja$k45$1...@news247.cnt.ru...
> > Hello!
> >
> > Erwin Dondorp <erwi...@wxs.nl> ЯННАЫХК Б МНБНЯРЪУ
> > ЯКЕДСЧЫЕЕ:3B5D33F5...@wxs.nl...
Sybrand Bakker
Import imports in
a) the tablespace of the create tablespace
b) the default tablespace if
- the orginal tablespace doesn't exist
---------> The user has no quota on the tablespace
So to move a table by exp/imp
make sure : the target tablespace is the default tablespace of the owner of
the tables.
This account doesn't have unlimited tablespace privilege,
and doesn't have quota on the original tablespace.
Sorry to say so, but all answers were incomplete.
Regards,
Sybrand Bakker, Senior Oracle DBA
Zeyad S
Thanks all,
ZS
Fraser McCallum
If you were doing this to re-arrange your tables across multiple
tablespaces, for the purposes of load balancing or using different extent
sizes with locally managed tablespaces, you can use the index creation
script.
Do an import with indexes =Y, indexfile=filename.sql and rows=N in your
import parameter file and the end of this import the filename.sql file will
include all the tables that are going to be created.
As this file is intended by Oracle for index creation the table creates are
commented out. This requires a little work with an editor to change, remove
the index creation statements first and then un-comment the table creation
statements. Then by changing the tablespace in the table create statements
you will be able to pre-create them split them between multiple tablespaces.
Now run another import without the indexfile parameter set, rows=Y and
ignore=Y set and all will be done.
NOTE: You can also extract the table pre-creation statements using a spooled
PL/SQL command selecting from the dba_tables and dba_tab_columns. I use the
one for the O'Reilly scripts book.
Regards
Fraser McCallum
MVP Oracle
www.BrainBench.com
"Zeyad S" <swei...@yahoo.com> wrote in message
news:3B5E0711...@yahoo.com...
koert54
Setting quota's on tablespaces to zero and altering the user default
tablespace to another
is all fine - as long as you don't have PARTITIONED TABLES ! It wont work.
I've had this problem numerous times and could not find a decent index file
formatter
so I wrote my one (does even search and replace of storage parameters)
You can download it (including the code) at sourcforge or revealnet
http://sourceforge.net/projects/pretoria/
http://pretoria.sourceforge.net/
regard
Kurt
"Fraser McCallum" <fm...@NOSPAModbaguru.com> wrote in message
news:I7u77.328$c13.75@NewsReader...
Niall Litchfield
news:996005535.21854....@news.demon.nl...
> Import imports in
> a) the tablespace of the create tablespace
> b) the default tablespace if
> - the orginal tablespace doesn't exist
> ---------> The user has no quota on the tablespace
> So to move a table by exp/imp
> make sure : the target tablespace is the default tablespace of the owner
of
> the tables.
> This account doesn't have unlimited tablespace privilege,
> and doesn't have quota on the original tablespace.
>
> Sorry to say so, but all answers were incomplete.
And an added little problem.
If your table contains lob data which is stored out of row. i.e. the table
is something like
create table t1(
id number,
binary_data blob)
lob (binary_data) store as
(tablespace lob_data
disable storage in row)
tablespace users;
then an import into a new database which does not contain the tablespace
lob_data will fail.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
Legal disclaimer required by my employer
****************************************************************************
**
This email contains information intended for the addressee only.
It may be confidential and may be the subject of legal and/or professional
privilege.
Any dissemination, distribution, copyright or use of this communication
without prior permission of the addressee is strictly prohibited.
****************************************************************************
**