Re: some simple questions about tablespace

Brian,
You're asking some good questions for a new DBA!

From my experience, importing tables to a different tablespace requires the following steps:

1. change the importing user to have no quota on the old tablespaces, but set its default quota to DATA_1.
2. import the .dmp file and Oracle will recognize that it can't use the old tablespace and put the tables in the user's default tablespace. For indexes, I would just drop them and recreate them in the desired tablespace.

For rollback segments, I understand that without specifying a rollback statement, Oracle will choose one based on current system usage. It may pick the small one when you need the large one. To avoid this, use "set transaction use rollback segment xx;" before your large transactions. This is reset after every commit, so you must reissue it before every long running transaction. (see database admin guide chap 18)

For temp tablespace, I don't know of a way to specify a particular one. In my databases I only have one temporary tablespace and assign large data files to it. Maybe someone else knows something here.

I wish you the best,

    --Gary Waterman

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 - 14:51:09 CDT