import export and tablespace

[amalou]

hi.
i have a db on oracle 7 but the tables for this db are installed in "systems" tablespace
and when i will export this db tables to import it on oracle 9i the tables are created on "systems" tablespace
i have create my user whith default tablespace "users" but whene i import data its created on "systemes" tablespace

 

[sem]

Try to leave only permissions on USERS tablespace.

Regards, Dima

 

[alexhu]

create empty tables in the usr tablespace.

When you import the data answer 'yes' to the question;

'Ignore create error due to object existence (yes/no):'

This will import the data into the correct tablespace

Alex

 

[Scunningham99]

YOU COULD IMPORT USING PARAMETERS FROMUSER AND TOUSER.

FOR EXAMPLE

IMP USERID=SYS/MANAGER FILE=EXPORT.DAT FROMUSER=IEUSER TOUSER=IEUSER

THIS WILL IMPORT OBJECTS OWNED BY IEUSER AND PLACE IN THEIR DEFAULT TABLESPACE.

NO NEED TO CREATE TABLES FIRST.

ALSO MAKE SURE USERS HAVE QUOTAS ON TABLESPACES.

FOR EXAMPLE SELECT * FROM DBA_TS_QUOTAS TO CHECK. IF THEY DO NOT HAVE A QUOTA. USE

ALTER USER SIMON QUOTA UNLIMITED ON SIMON_TABLESPACE;

REGARDS

S

Sy UK

 

[alexhu]

Scunningham99's suggestion will work of course, but gives no opportunity to change INITIAL, NEXT, PCTINCREASE which most likely be set to the system tablespace defaults

Alex

 

[Scunningham99]

AL LONG AS YOU HAVE CREATED YOUR TABLESPACE FOR IUSER WITH DEFAULT SOTORAGE OPTIONS THE IMPORT WILL CREATE SEGMENTS USING THEM FOR EXAMPLE:

CREATE TABLESPACE IUSER DATAFILE '/u02/oracle/817/dbs/iuser.dbf' SIZE 100m REUSE,'/u02/oracle/V7.1.6/dbs/rbsV716.dbf' SIZE 100m REUSE DEFAULT STORAGE (INITIAL 2M NEXT 2M MINEXTENTS 2 MAXEXTENTS 121 PCTINCREASE 0)

THIS WILL LOCAL TO THIS TABLESPACE NOT SYSTEM.

S


Sy UK

 

[alexhu]

Scunningham99 - interesting, never seen this bit before - what does it do ?

REUSE,'/u02/oracle/V7.1.6/dbs/rbsV716.dbf' SIZE 100m REUSE

Alex

 

[Scunningham99]

CANT REMEMBER EXACT TERMINOLOGY. SOMETHING LIKE TELLS SMON TO REUSE SEGMENTS AFTER DELETE ETC.

SYNTAX

CREATE TABLESPACE IUSER DATAFILE '/u02/oracle/817/dbs/iuser.dbf' SIZE 10M REUSE



Sy UK

 

[alexhu]

You mean like setting PCTINCREASE to 1 on the tablespace ?

Alex

 

[AOLEURO]

Hi

REUSE = If the file exists during CREATE datafile, then REUSE the existing file.

create tablespace XXXXXX datafile 'YYYYY' size 100M REUSE

Of cause it can't reuse a file already used in a tablespace - but it can reuse a file from a dropped tablespace/datafile.


Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !

 

[appi]

Hi
will the reuse leave the datafile complete or erases the data and makes it empty ?

rgds
Uwe

 

[alexhu]

AOLEURO - I'm afraid you didn't quite read the response correctly

The statement was

CREATE TABLESPACE IUSER DATAFILE '/u02/oracle/817/dbs/iuser.dbf' SIZE 100m REUSE,'/u02/oracle/V7.1.6/dbs/rbsV716.dbf' SIZE 100m REUSE DEFAULT STORAGE (INITIAL 2M NEXT 2M MINEXTENTS 2 MAXEXTENTS 121 PCTINCREASE 0)

I understand the use of reuse with the tablespace datafile, but look at the next part of the statement

'/u02/oracle/V7.1.6/dbs/rbsV716.dbf' SIZE 100m REUSE

Whats that about?


appi - the reuse will reuse the datafile i.e.clear its contents



Alex

 

[AOLEURO]

Hi

The tablespace has 2 datafiles each 100Mb.

1. file is '/u02/oracle/817/dbs/iuser.dbf'
2. file is '/u02/oracle/V7.1.6/dbs/rbsV716.dbf'

You can create MULTI dafiles in CREATE TABLESPACE xxxx just use seperator ',' between datafile clause.


Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !

 

[alexhu]

AOLEURO - well call me old fashioned, but wouldn't you have datafiles of the same tablespace have similar names ?

Alex

 

[AOLEURO]

Hi Alexhu

Jep - the above folder names and filenames are not smart - but we could say - To be or not to be - because the above is that we have.

I would try to use Oracle-Managed-Files (OMF) - it is a new Oracle 9i feature and we will see more and more auto functionality with OMF in Oracle.

Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !