Re: interpreting tablespace settings

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?
>
>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 ...

HTH. Additions and corrections welcome.

Pete

SELECT standard_disclaimer, witty_remark FROM company_requirements; Received on Sun Jun 09 2002 - 17:22:16 CDT