why do oracle changes tablespaces

[Deanna Tran_1]

I have created a total of 5 tablespaces...how come oracle just write to one tablespaces, and left the other one alone. In addition, there are 5 rollback segments in each tablespaces...how do I force oracle to use all of the tablespace?

[Jeanine Kone · Accepted solution]

The tablesspace used when creating an object is determined as follows:

1) you can specify what tablespace to use in the create table or create index command (the owner needs to have quota on the specified tablespace)
create table
(field1 varchar2)
tablespace tbs_name;

2) if you do not specify, it will use the default tablespace for the object owner. you can select * from dba_users to see a users default tablespace. You can change this with the alter user command. (and make sure they have quota on the specified tablespace).

Basically, you force Oracle to use the tablespaces by assigning objects to each of them. Note, an object can not span tablespaces (with the exception of partitioned objects, where each partition can be in a separate tablespace).

As a note, I would put all of the rollback segments in one separate tablespace. I would not spread them across all five.

Jeanine

[Dave Chamberlin]

A tablespace is a logical entity - not a physical one. An Oracle user has a default tablespace for their objects. A single user will only use one tablespace, unless object creation commands specifically use another tablespace. The physical part of the data is datafiles. Each tablespace must have at least one, but may have many. The datafiles can be moved around the disk system for balancing IO. For example, My INV user in Oracle for instance uses the INV tablespace, which has 10 datafiles. That user creates all of its indexes in the INVX tablespace, which has 10 datafiles of its own.For a single user system, you would usually have the SYSTEM tablespace, a TEMP tablespace for sorts, an RBS tablespace for rollback segments, a USER tablespace for user tables, and an INDEX tablespace for user indexes. Other than these, there is no need create other tablespaces except for other users. BYW - You SHOULD put your rollback segments into their own tablespace.

[Deanna Tran_1]

Hi..
Now, I want to reorganize the structure of the oracle database. I want to drop all of the tablespaces except the system, index and temp..
and move the redo logfile to a different mountpoints and the control file to a different place? can I still be able to accomplish all of this without completely drop the database?

[Dave Chamberlin]

To move the controlfiles:
1) shutdown the database normally
2) move the controlfiles to their new locations
3) edit your initora file to have the new paths of your controlfiles
4) restart the database

To move redo logs is a little more complicated:
1) shutdown the database normally
2) move the redo log files to their new locations
3) only do a STARTUP MOUNT command when restarting the database.
4) do ALTER DATABASE RENAME 'the_old_path' to 'the_new_path'
the_old_path would be like /u01/oradata/PROD/redo01.log etc
5) do ALTER DATABASE OPEN, and you are done

[Jeanine Kone]

Yes, you can do the specified things with out dropping and recreating a new database.



just take the tablespaces offline and then drop them.

alter tablespace tbsname offline;
drop tablespace tbsname including contents;
or, you could use the gui....

(of course, make sure you backup any data you need before you do this).



I think the easiest way would be to add new logfiles using the new mountpoints and then drop the old ones. you can use the gui for this - alter database would be the command to use if you don't have the gui.



I would shut down the database. copy / move the control files to the new place, edit the init(sid).ora file to indicate the new location of control files, start the database back up.

BTW - take a full backup before you make significant changes like these - just in case.

[Deanna Tran_1]

thanks all for your help...I was able to mvoe the logfile into a new mount directories..and all other stuff...

I have reorganize the database structures...
I have created a 25G filesystem and use that as tablespace for Rollback as suggested by Jeanine... However, what i want to do is balancing the I/O with all the diskes?
Can I do that? And I am using filesystem to and not raw devices...
so I create a 25 G filesystem..and it expands to multiple diskes ..and i want to take an advantage of all the diskes

[Jeanine Kone]

What you want to do is spread the data that will be accessed concurrently onto different disks. So, the system tablespace, and temp, and rollback, and user_data, and user_index would all be on separate disks.

What I do is have at the o/s level different filesystems that map to different disks. For example, I set up a filesystem called oradata, one called oraindex, one called oratemp, orarbs, etc. Then I create tablespaces for each type of data, i.e. data, index, temp, rbs, etc. Each tablespace has it's datafiles placed in the appropriate filesystem.
Then you create your objects in the appropriate tablespaces. This ends you up with the different objects on different disks.

[Bill Thorsteinson]

Check the Oracle documentation
for OFA (Oracle Flexible
Architecture).

Create the following directories on different disks:

/u00/oradata/TEST
/u01/oradata/TEST
/u02/oradata/TEST
/u03/oradata/TEST
/u04/oradata/TEST

Substituting TEST with your
ORACLE_SID. Spread you files
across these by type. As you
add SIDs create new subdirectories for the new
SIDS.

I normally create an oracle
partition on each disk and
mount it as /u0x. This allows
a lot of flexibility in tuning
file placement. Create more
directory trees than you have
disks if you like.

Mirror your control files and
redo logs accross two disks.
Place your rollback segment
tablespace on a third. Split
the rest of the standard
tablespaces across the disks
with your redo logs or put
them in the additional
directories. Add application
tablespaces in the additional
directories. You should split
index and data tablespaces
on different directories.