simple export/import question

[kburrows]

hi everyone,

I am working on moving tables from one tablespace to another on an nt machine. I would like to use the import/export option. How do I start this utility on NT?. what kinds of things must I keep in mind when doing this.

1. Export table mode, select tables I want, compress = N.
exp xxx/xxx tables=(xxx,yyy,zzz) rows=Y file=export.dmp
2. Alter user default tablespace to new tablespace, grant quota on that tb space. (IS this needed)
3. Import those table rows =Y, commit = Y
imp xxx/xxx tables=9xxx,yyy,zzz) rows=Y file=import.dmp

Any more steps, Does the syntax look correct.

thanks as always,

[gpsingh]

Revoke any quota on the old tablespace for the user.

[tamilselvan]

To move a table from one tablespace to another tablespace , just use ALTER statement. You do not need to exp/imp.

[wasterw]

Just a quick question.... Why not use 'create table as select ...' to copy your tables to the new tablespsace and avoid having to write the data to disk?

Eg.
create table newtable tablespace newdata as select * from oldtable;
drop table oldtable;
rename newtable to oldtable;

Syntax above may be a bit dodgy... it came from the top of my head!

Adam.

[sambavan]

You have to specify it as following

exp sys/passwd@sid FILE=exp.dmp LOG= exp.log TABLES=(schema.table1, schema.table2,...) CONSISTENT=Y DIRECT=Y COMPRESS=N

Alter the user table space, grant the permissions

imp sys/passwd@sid FILE=exp.dmp LOG=imp.log TABLES=(schema.table1, schema.table2, schema.table3,...) COMMIT=Y

Btw, are you going to export and import a perticular user? if so then you have to use the OWNER=user_name on your export and then FROMUSER=user_name TOUSER=user_name in your import clause.

Good luck,
Sam

[kburrows]

thanks for great advice,

Why not use 'create table as select' well I am very new to this and I have read that you don't get everything, so I would have to rebuild Indexes, triggers, constraints? More than I know how to do right now.

'just use ALTER statement'. I have read several things about corrupt indexes after using this command. I also tried it and it wouldn't work, keep saying can't perform two operations at once?

So I figured export/import would be the best for a novice like me, right now.

Again, does anyone know how to use/start the export utility on NT, I am mostly experienced with UNIX.

Thanks,

[sambavan]

From the dos prompt issue the command exp or imp. The actual executable is in %ORACLE_HOME%\bin

Good luck,
Sam

[garry]

Hello kburrows
just a fellow like you
ok! now about your thing
just go to command pmt.type this
C:\>exp80 (return key)
it will ask you for everytihng it needs.
try make your own file.
For import than.
c:\>imp80 (return key)
and it will give you every thing.
keep in mind if a guy with dba privs made the expfile only someone with same or higher privs can read from it.