Re: some simple questions about tablespace

Brian,

1. If a table is exported from a tablespace, it will try to re-load back into the same tablespace. You could create the tables in the new tablespace then when the import tries to create the table it will error (you can choose to ignore this.

To simplify this you could use the INDEXFILE option of 'imp' e.g. imp file=<datafile> indexfile='/tmp/test'
When this is done a text file called '/tmp/test' is created. In there the table create scripts (and index create scripts) are written. Edit this to change the tablespaces where you want each table to be put. Run it through SQL*Plus then do the import to get the data.

2) ORACLE uses rollback segments one after the other in a round-robin fashion. To point a large transaction to a large rollback segment use: Before you do anything else put 'set transaction use rollback segment <bigseg1>'
This segment will be used until the next COMMIT or ROLLBACK.

3) When a user requires temporary storage then ORACLE will use the tablespace stated in 'TEMPORARY TABLESPACE' when the schema was created. e.g.
CREATE USER FRED
IDENTIFIED BY JOE
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP; You should only need the large one and all schemas created should have their 'TEMPORARY TABLESPACE' set to this.
--
Regards Pete

Brian Yan wrote in message <362EA454.25DB_at_gpu.srv.ualberta.ca>...
>I am new on DBA stuff. I would appreciate it very much if someone can
>help me on the following questions.
>
>1). Can I specify the new tablespace while importing a schema data file?
>I created new tablespace DATA_1 and INDEXES_1 for the new imported
>schema datafile. How to let the import go to the new tablespace instead
>of existing tablespace?
>
>2). I have eight small rollback segments and three big rollback
>segments. Will the Oracle be smart enough to go to the big rollback
>segment when it is doing big transactions? Or I should turn the small
>rollback segments offline manully when Oracle does big transactions?
>
>3). Similar question to temporary tablespace. If I create a small temp
>tablespace and a big temp tablespace for importing. Is Oracle smart
>enough to select the appropriate tablespace according to the
>transaction? Or I should turn one offline manully?
>
>Thank you for your help!
>
>Brian
Received on Fri Oct 23 1998 - 09:36:03 CDT