- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- why do oracle changes tablespaces
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-01-2002 06:35 AM
тАО07-01-2002 06:35 AM
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-01-2002 06:45 AM
тАО07-01-2002 06:45 AM
Solution1) 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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-01-2002 07:07 AM
тАО07-01-2002 07:07 AM
Re: why do oracle changes tablespaces
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-01-2002 07:08 AM
тАО07-01-2002 07:08 AM
Re: why do oracle changes tablespaces
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-01-2002 07:25 AM
тАО07-01-2002 07:25 AM
Re: why do oracle changes tablespaces
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-01-2002 07:27 AM
тАО07-01-2002 07:27 AM
Re: why do oracle changes tablespaces
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-01-2002 09:13 AM
тАО07-01-2002 09:13 AM
Re: why do oracle changes tablespaces
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-01-2002 09:28 AM
тАО07-01-2002 09:28 AM
Re: why do oracle changes tablespaces
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-02-2002 08:18 AM
тАО07-02-2002 08:18 AM
Re: why do oracle changes tablespaces
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.