Trouble creating tablespace. ORA9 complains about SEGMENTSPACE...

I used SEGMENT SPACE MANUAL because schwertner said so in the other post. I don't really know what it does :( Will change to AUTO at least. Thanks

I get a new error now :(

SQL> CREATE TABLESPACE "DBIDS" LOGGING DATAFILE '/u01/app/oracle/oradata/DBIDS/dbids01.dbf' SIZE 4000M
  2  AUTOEXTEND ON NEXT 250M MAXSIZE 32768M
  3  PERMANENT ONLINE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

ORA-03206: maximum file size of (5592406) blocks in AUTOEXTEND clause is out of range

Which number is to big?

Another question which is in regard to this db I'm trying to set up:

Our old Oracle 9 database has a fixed (on the  nonsystem tablespace) size of 8GB in total. This data is supposed to get imported into the new database. Would my way of doing it work, eg making a default size of 4GB and then let the db autoextend when more space is needed?

MAXSIZE is too big.

try to reduce that value until it works; better is to have more (but smaller) datafiles.

SQL> CREATE TABLESPACE "DBIDS" LOGGING DATAFILE '/u01/app/oracle/oradata/DBIDS/dbids01.dbf' SIZE 4000M
  2  AUTOEXTEND ON NEXT 250M MAXSIZE 8000M
  3  PERMANENT ONLINE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Maybe the non-system tablespace(s) in your Oracle9 database had fixed size(s) of 8GB, but that doesn't mean the database was limited 8GB!  You were free to either add datafiles to tablespaces, or add tablespaces even if you couldn't (or didn't want to) extend the size of the current datafile(s) in that database.

Are you sure that you want "SEGMENT SPACE MANAGEMENT AUTO"?  Doesn't that give you differently-sized extents then in the tablespace?  If you do that, then if you ever drop or move an object, you may end up with free space that you cannot re-use.

angelIII:
Ok. I just thought, since all files were stored on one RAID-1 partition (only option I'm afraid, no multiple physical disks for each datafile etc..).

How would I re-write the SQL statement that failed to make 8 files with 500M and allow them to extend when they are full? (Does 8 files and 500M sounds ok?)

markgeer:
"Maybe the non-system tablespace(s) in your Oracle9 database had fixed size(s) of 8GB, but that doesn't mean the database was limited 8GB!  You were free to either add datafiles to tablespaces, or add tablespaces even if you couldn't (or didn't want to) extend the size of the current datafile(s) in that database."
Ok. I know we extended the files from 2GB in total to 8GB in total some years ago.


"Are you sure that you want "SEGMENT SPACE MANAGEMENT AUTO"?  Doesn't that give you differently-sized extents then in the tablespace?  If you do that, then if you ever drop or move an object, you may end up with free space that you cannot re-use."

Doesn't sound like I want that. Should I use "uniform size xxK" instead? What would be a good number for xx?

I just have to say.. Oracle is so much harder (at first at least ;)) than mysql... I'm very confused at times, and sad to say, I run a lot of these commands without knowing what it will do. :(

And to complete the first sentence....:

Ok. I just thought, since all files were stored on one RAID-1 partition (only option I'm afraid, no multiple physical disks for each datafile etc..), I could just go with one large file instead of a split.

I agree that Oracle is much more complex to manage than some other, simpler (and less-scalable) databases.  The flexibility, tunability and scalabilty of Oracle are directly related to all of the options that Oracle makes available to us.  So, the advantage is: Oracle is very tunable (for different servers, database size, numbers of users, type of application, etc.) but the disadvantage is: you have to know how to tune Oracle effectively, or your performance may be much less than what is possible.

I really like having uniform extent sizes in each tablespace.  That way, I know that if/when I move or drop an object, the free space remaining will be exactly the size that other objects in that tablespace can use.  I don't use the same extent sizes though for all tablespaces.

Please see this recent question (by fargo) with the title:
HOWTO with Tablespace maintenance and tuning
for some more good info on the subject:
https://www.experts-exchange.com/questions/21815994/HOWTO-with-Tablespace-maintenance-and-tuning.html

Ok I will.

How should I re-write the tablespace sql statement that failed on me?

Error:      ORA-3206
Text:      maximum file size of (%s) blocks in AUTOEXTEND clause is out of range
---------------------------------------------------------------------------
Cause:      The maximum file size for an autoextendable file has exceeded the
      maximum number of blocks allowed.
Action:      Reduce the size and retry.

your query, I changed to my path:

CREATE TABLESPACE "DBIDS"
LOGGING
DATAFILE  '/optware/oracle/9.2.0.6_64/oradata/test/dbids01.dbf' SIZE 4000M
AUTOEXTEND ON NEXT 100M
MAXSIZE 32768M             <------------------- this is the problem, just put a double hyphen (--) in the begining, & this'll execute successfully
PERMANENT ONLINE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT MANUAL

I just put a -- before line 5 and tablespace created,
but still I'll suggest, should try AUTO instead of MANUAL

hope this should solve ur problem...

cheers...:-)

I would write it like this:

CREATE TABLESPACE "DBIDS"
LOGGING
DATAFILE  '/optware/oracle/9.2.0.6_64/oradata/test/dbids01.dbf' SIZE 4000M
AUTOEXTEND ON NEXT 100M
PERMANENT ONLINE
EXTENT MANAGEMENT LOCAL
uniform size 2000K;

This value of 200K is just a suggestion.  Depending on the number of records that are in the tables you plan to put in this tablespace, and on your db_block_size, you may want a smaller or larger value, or one that is an even multiple of your db_block_size.

You may also want other tablespaces with a different "uniform size" value for tables that are larger or smaller than the ones you plan to put in this tablespace.

markgeer - you say 2000K in the SQL statement and 200K in the text. What is correct? There will be lots of records in the tablespace, on a busy time it will be addedd multiple records every minute.

Will check the SQL statement when I'm at work tomorrow.

markgeer - you also mentino a uniform size as an even multiple of my db_block_size. I was just told (https://www.experts-exchange.com/questions/21817358/Need-help-with-creating-an-initSID-ora-file.html) that db_block_size was the old way and that I should use db_cache_size instead... what do you think/recommend?

Also - I do still feel to much of a newbie on Oracle.. Could (any of) you recommend any good books/pdf/article to read to get the basics, on both installing and how Oracle is put together with databases, tablespace, etc? I'm not yet getting the whole concept of tablespaces and such... I have come to learn (I think) of a tablespace to be something of the same as what I would call a database in mysql..?

I went with 2000K. I also splitted the datafile into 4 1GB files. Almost everything seems to be ok at the moment, except when I try to start Oracle. The listener starts fine btw.

My error when starting Oracle (dbstart &)

Can't find init file for Database "DBIDS".
Database "DBIDS" NOT started.

So, I look into /u01/app/oracle/product/9.2.0/dbs/ and I don't have a initDBIDS.ora file, but I got a spfileDBIDS.ora file. Wasn't spfile going to replace the older initSID.ora? If I copied the initDBIDS.ora file into the directory it started just fine... Is there an explanation for this?

Yes, with Oracle9 and 10, the default is to use an "spfile" instead of the older style "init*.ora" file.  Normally, both of these are in the same directory under your Oracle_home.  For Unix and Linux, this is the /dbs sub-directory, for Windows it is the \database directory.  The install process should have created both an init*.ora file and an spfile for you, but if it didn't you can manually create either one from the other.  If you are logged in to SQL*Plus as SYS, you can do either:
create spfile from pfile
or
create pfile from spfile

I think you can only create the one that was not used to startup though.  For example, if you started from a pfile (init*.ora) you can create an spfile, or if you start from the spfile, you can create a pfile.  You may need to provide the full path to the pfile for either of these commands, something like this:
create spfile from pfile=C:\Oracle9\database\initDBIDS.ora

I started with a pfile (init*.ora) but I did not have it in the /dbs folder. I created the spfile from pfile at the end.

Anyway, am I correct to assume that I should have both files in /dbs and that it is not enough with only one of them?

Yes, both of these files should normally be in the /dbs sub-folder.

Is only one of them not enough?  That depends on what you want.  An Oracle9 or 10 database can run from either one, but the default is an spfile.  If you don't have an spfile, you may have to tell Oracle to use the init*.ora file when it starts.  Also, use of an spfile give you the ability to change a lot more of the initialization parameters on-the-fly without having to shut down and restart the database to get the new value.  I think there are a couple parameters that can only be changed with an init*.ora file, but you can then create a new spfile from that that includes the change(s).  Also, it is much easier to remove parameters (if you ever need to) with an init*.ora file.

So I can start Oracle with spfile only? How? It nagged about a missing init*.ora file, which I fixed by copying it into the correct place, where I already had an newly created spfile.

Yes, you certainly can start and run Oracle9 or Oracle10 with an spfile only.  An init*.ora file is not required.  Why did it complain about a missing init*.ora file?  I don't know, unless you didn't have the spfile in the expected directory location.

In /u01/app/oracle/product/9.2.0/dbs I have:

-rw-r--r--   1 oracle oinstall  5030 Apr 20 15:42 initDBIDS.ora
-rw-r--r--   1 oracle oinstall 12920 Mar  8  2002 initdw.ora
-rw-r--r--   1 oracle oinstall  8385 Mar  9  2002 init.ora
-rw-rw----   1 oracle oinstall    24 Apr 24 13:43 lkDBIDS
-rwSr-----   1 oracle oinstall  1536 Apr 20 11:39 orapwDBIDS
-rw-r-----   1 oracle oinstall  3584 Apr 20 11:46 spfileDBIDS.ora

my initDBIDS.ora file was copied to the location by me. The spfileDBIDS.ora was made by my install script from initDBIDS.ora. Perhaps the filename should not have ended with .ora??

No, that's not a problem, the spfile normally has a *.ora extension.

I think your original question has been solved, right?  If so, can you accept one (or more) of the responses here as the answer to close this question?

If you want more help on some of the differences between SQL Server and Oracle, please see this question:
https://www.experts-exchange.com/questions/21701397/EASY-basic-informations-about-Oracle-8-9.html

Yes I've got it running now :) Will accept one or more answers.

Thank you for closing this question.  Did you check out the other question I suggested?  If you are familiar with SQL Server, but new to Oracle, that question may have some significant tips for you.  Here's another one:

Oracle date columns can optionally include a time-of-day component.  Which parts of the date (quarter, month, week, day, year, etc.) and/or which parts of the time (hour, minute, second) are displayed, and which format of each of these date/time elements is used for displaying date/time information is highly customizable in Oracle, and can be different for different date values in the same query by use of the "to_char" operator and an appropriate format mask.