Re: interpreting tablespace settings

"Pete Sharman" <peter.sharman_at_oracle.com> wrote in message news:ae0keo0pr9_at_drn.newsguy.com...
> In article <ae0hvu$9i0$1_at_lust.ihug.co.nz>, "Howard says...
> >
> mighty big snip
> >9i release 2 changes that slightly: dictionary-managed tablespace can't
be
> >created at all in that particular release.
> >
> Hmm, is this really the case? I thought it was more the case that you
can't
> create a dictionary managed tablespace in a database where the SYSTEM
tablespace
> is locally managed (the default in 9iR2), as yuo can see from this:
>
> SQL> create tablespace test datafile 'd:\temp\test.dbf' size 1m extent
managemen
> t dictionary;
> create tablespace test datafile 'd:\temp\test.dbf' size 1m extent
management dic
> tionary
> *
> ERROR at line 1:
> ORA-12913: Cannot create dictionary managed tablespace
>
> where the error manual states:
>
> ORA-12913 Cannot create dictionary managed tablespace
>
> Cause: An attempt was made to create a dictionary managed tablespace in a
> database whose SYSTEM tablespace is locally managed.
>
> Action: Create a locally managed tablespace.
>
> Unfortunately my laptop only has one database with the default settings,
and I
> don't have room for another. I thought that if you used dictionary
managed for
> the SYSTEM tablespace, you could still use it for other tablespaces. Has
anyone
> got a database like that for 9iR2 that they could verify this one for us?

I'll buy this (like you, I don't currently have space to create another 9iv2 database, so I'll take it on trust).

Pretty big "default", though, even so: once SYSTEM's been created, you're stuck with it until you create a new database. And create it by hand, as I don't see an option to create a dictionary managed SYSTEM tablespace using DBCA. As it is, I rather thought the 'create database' command only allowed the specification of a datafile for SYSTEM... the actual creation of the SYSTEM *tablespace* is done by the first operative line of sql.bsq (as ever). Mine reads:

create tablespace SYSTEM datafile "D_DBFN"   "D_DSTG" online

...and I wouldn't know where to stick the 'extent management dictionary' in that little lot and make it work (uh oh, I feel an experiment coming on...).

So I'll modify my claim somewhat: *practically* you can't create dictionary-managed tablespaces in 9iv2. Not without an awful lot of forethought and effort -which we agree (I think) isn't worth expending in the first place!! ;-)

> >
> >But the autoextension bit refers to the datafile, not the tablespace.
> >Therefore, to allow autoextension to take place after the event, the
correct
> >syntax is:
> >
> >alter datafile 'c:\x.dbf' autoextend on;
> >
> >However, it's an extremely bad idea just to switch on autoextend without
> >also saying what it should extend *by* -and when it should stop
extending.
> >Thus:
> >
> >alter datafile 'c:\x.dbf' autoextend on next 10m maxsize 100m;
> >
> >...would be the preferred command. If you happen to think that autoextend
is
> >a good idea, that is. Actually, of course, autoextend is a lousy idea for
> >any database that is going to be properly managed with a more or less
> >full-time DBA on hand, and for which performance is a design requirement.
In
> >those circumstances, the DBA should size the thing correctly in the first
> >place, or manually resize the files in anticipation of a large workload:
> >
> >alter datafile 'c:\x.dbf' resize to 100m;
> >
> >...means that you are in control of when the resizing happens, not
> >Oracle -and you can therefore schedule it for a time when people won't
> >actually be wanting to do work on the database.
> >
> >Regards
> >HJR
> >
> and all three of these commands are missing the magic word "database".
They
> should be ALTER DATABASE DATAFILE ...
>

Bloody search and replace!! I'd typed 'alter tablespace' with the original post in mind. Noticed the mistake, and replaced 'tablespace' with 'database'.... only, clearly, I hit the wrong button somewhere along the line!!

Anyway: Pete's right. It's 'alter database datafile...'

Thanks for the correction.

Regards
HJR
> HTH. Additions and corrections welcome.
>
> Pete
>
> SELECT standard_disclaimer, witty_remark FROM company_requirements;
>
Received on Sun Jun 09 2002 - 21:24:36 CDT