- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Adding Datafiles Automatically
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
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
тАО09-15-2003 11:39 AM
тАО09-15-2003 11:39 AM
Thanks,
Sunny
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-15-2003 04:28 PM
тАО09-15-2003 04:28 PM
Re: Adding Datafiles Automatically
When the existing datafile (tablespace) gets to 90% full and if it is likely to increase in data then you have to plan to increase the tablespace by either resizing or adding a datafile to the tablespace.
You choose to resize (increase) an existing tablespace or add another datafile depending on the current datafile size.
The command to increase (resize) an existing datafile is
Alter database datafile 'name in full path' resize ??M;
The command to add a datafile to an existing tablespace is
Alter tablespace
I hope this is what you were looking for.
IA
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-15-2003 05:57 PM
тАО09-15-2003 05:57 PM
Re: Adding Datafiles Automatically
You can enable the auto extend option of your datafile. So whenever the data reaches the end of the file it automatically extends it.
The syntax is :
ALTER DATABASE DATAFILE 'D:\orant\database\Sys1ORCL.ora' AUTOEXTEND ON;
You can have a look at :
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/dfiles.htm#484
HTH
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-15-2003 07:44 PM
тАО09-15-2003 07:44 PM
Re: Adding Datafiles Automatically
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-15-2003 08:53 PM
тАО09-15-2003 08:53 PM
Re: Adding Datafiles Automatically
I would turn on the AUTOEXTEND feature and make optimal use of space by using A locally managed tablespace with uniform extents.
You can set it up by object size -- setting up extent sizes for "small" "med" and "large". You would want all segments to be placed in a tablespace such that they would never have more then say 1,000 extents.
So, maybe for you if you have some really small tables:
64KB extent size -- good for 64k to ~6MB, could goto ~60MB
512KB extent size -- good for 512k to ~50MB, could goto ~500MB
1MB extent size -- good for 1m to ~100MB, could goto ~1MB
5MB extent size -- good for 5m to ~500MB, could goto ~5MB
The added benefits that you will also get are:
- Locally managed tablespaces are much faster at allocating and de-allocating extents - many order of magnitudes faster.
- locally managed tablespaces decrease contention on the data dictionary.
E.g. For normal tablespaces
CREATE TABLESPACE LMT_SMALL
DATAFILE 'D:\ORACLE\ORADATA\MYDB\LMT_SMALL01.DBF' SIZE 92160 K
AUTOEXTEND ON NEXT 512 K
LOGGING
ONLINE
PERMANENT EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64 K
For temporary tablespace:
CREATE TEMPORARY TABLESPACE TEMP_LMT
TEMPFILE 'D:\ORACLE\ORADATA\MYDB\TEMPLMT01.DBF' SIZE 51200 K
AUTOEXTEND ON NEXT 640 K MAXSIZE 102400 K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64 K;
hope this helps!
regards
Yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-16-2003 06:36 AM
тАО09-16-2003 06:36 AM
Re: Adding Datafiles Automatically
Thanks for your suggestions. But I was looking for a way wherein the datafiles can be automatically added once the existing datafiles are used up. I want to restrict the size of the datafiles so cannot make them unlimited. So the autoextend usage is limited.
For eg. If there are 10 datafiles and the 11th datafile should get automatically added (may be thru a script or setting a parameter) once the tablespace size reaches its max.
Thanks,
Sunny
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-16-2003 10:41 AM
тАО09-16-2003 10:41 AM
Solutionas you have noticed by now, there is no automatism by the database to handle this for you. You will have to take it into your own hands(crontab). Roughly you will have to do the following steps.
1. find out, how much is left
select sum(bytes) from dba_free_space where tablespace_name='BLABLA';
2. find out, now many datafiles you already have.
select count(*) from dba_data_files where tablespace_name='BLABLA';
3. add one to number, construct new datafile name
4. add datafile using whatever size you want.
alter tablespace blabla add datafile 'newdatafilename' size 10m;
is that sufficient for you to get the job done?
Michael
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-16-2003 10:50 AM
тАО09-16-2003 10:50 AM
Re: Adding Datafiles Automatically
Thanks for the answer. I too thought that there is no automatic way of doing it. Just wanted to make it sure.
Thanks again !!!
Sunny