Creating and using tablespaces 2

[EliseFreedman]

Im using the following code to create a default tablespace for a user and also a temporary tablespace and then set the users default tablespace to user_data and the temporary tablespace to temporary_data.

<code>
CREATE TABLESPACE user_data
DATAFILE 'C:\orac\userdata1.ORA'
SIZE 1 M
DEFAULT STORAGE (INITIAL 5K NEXT 10K
MINEXTENTS 1 MAXEXTENTS 999
PCTINCREASE 10) ONLINE;

CREATE TABLESPACE temporary_data
DATAFILE 'C:\orac\tempdata1.ORA'
SIZE 1 M
DEFAULT STORAGE (INITIAL 5K NEXT 10K
MINEXTENTS 1 MAXEXTENTS 999
PCTINCREASE 10) ONLINE;

CREATE USER srsowner IDENTIFIED BY private
DEFAULT TABLESPACE user_data
TEMPORARY TABLESPACE temporary_data
QUOTA unlimited on user_data
QUOTA 10m ON temporary_data;

</data>

However, i get the following error

ORA-12911: permanent tablespace cannot be temporary tablespace

I tried changing the 2nd paragraph to

CREATE TABLESPACE temporary_data
DATAFILE 'C:\orac\tempdata1.ORA'
SIZE 1 M
DEFAULT STORAGE (INITIAL 5K NEXT 10K
MINEXTENTS 1 MAXEXTENTS 999
PCTINCREASE 10) ONLINE TEMPORARY;

but it didnt make any difference. What am I doing wrong??

 

[SantaMufasa]

Elise,

You are very close. The syntax you need to make it all work is:

CREATE TABLESPACE temporary_data [b]TEMPORARY[/b]
DATAFILE 'C:\orac\tempdata1.ORA'
SIZE 1 M
DEFAULT STORAGE (INITIAL 5K NEXT 10K
MINEXTENTS 1 MAXEXTENTS 999
PCTINCREASE 10);

The other problem with your tablespaces is that although they are starting out at reasonable sizes, you have not allowed them to grow (to a 2GB maximum is my preference). I like to grow them in 10MB increments. (I'll show you how in just a moment.)

Also, I cannot emphasize enough how beneficial it is to use Oracle's locally managed tablespaces (LMT). To make use of that, you would say (for your other [application] tablespace):

CREATE TABLESPACE user_data
DATAFILE 'C:\orac\userdata1.ORA' SIZE 1 M
AUTOEXTEND ON NEXT 10M MAXSIZE 2000M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

In using LMT, INITIAL and NEXT parameters no longer apply.

So, the above is how I create well-behaved tablespaces.

Let us know how you like the results.



Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[EliseFreedman]

Hiya

thanks for your advice.

Ive now changed my code to the following

<code>
CREATE TABLESPACE temporary_data TEMPORARY
DATAFILE 'C:\orac\tempdata.ORA' SIZE 1 M
AUTOEXTEND ON NEXT 10M MAXSIZE 2000M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
</code>

however, im now getting the following error

ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace

any suggestions??

 

[SantaMufasa]

Sorry...I did not emphasize as explicitly as I should have:

...you would say (for your other [application] tablespace (but not for your non-application TEMP tablespace))...

So, my inferrence is that LMT works well for application tablespaces but not for SYSTEM, TEMP, or RBS tablespaces. Therefore, your TEMP tablespace code should read:

CREATE TABLESPACE temporary_data TEMPORARY
DATAFILE 'C:\orac\tempdata.ORA' SIZE 1 M
AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;

Let us know if this is more successful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[EliseFreedman]

Thanks again for all your help

Unfortunately i still get the same error.

ORA-12913: Cannot create dictionary managed tablespace

my script so far is as follows

<code>
CREATE TABLESPACE user_data
DATAFILE 'C:\orac\userdata1.ORA' SIZE 1 M
AUTOEXTEND ON NEXT 10M MAXSIZE 2000M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

CREATE TABLESPACE temporary_data TEMPORARY
DATAFILE 'C:\orac\tempdata.ORA' SIZE 1 M
AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;

CREATE USER srsowner IDENTIFIED BY private
DEFAULT TABLESPACE user_data
TEMPORARY TABLESPACE temporary_data
QUOTA unlimited on user_data
QUOTA 10m ON temporary_data;

</code>

 

[SantaMufasa]

Elise,

Your code looks fine from my perspective. Could you please confirm what version of Oracle you are running?

Could you also please copy-and-paste the erroneous code and the error message that appears below it? Also, if you wish to offset your code inside a "Code Block", use square brackets ("["/"]") instead of carets ("<"/">").



Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[EliseFreedman]

Im running version 9.2

The erroneous code and error message are as follows

SQL> 
SQL> CREATE TABLESPACE temporary_data TEMPORARY
  2  DATAFILE 'C:\orac\tempdata.ORA' SIZE 1 M
  3  AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;
CREATE TABLESPACE temporary_data TEMPORARY
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace

 

[SantaMufasa]

I just did some research on your issue, Elise. When I asked the automated Oracle-error-explanation system for a profile of your error, this is what it said:

$ oerr ora 12913
12913, 00000, "Cannot create dictionary managed tablespace"
// *Cause: Attempt to create dictionary managed tablespace in database
// which has system tablespace as locally managed
// *Action: Create a locally managed tablespace.

So, this description suggests that your SYSTEM tablespace is (itself) LMT. As a result, your TEMP tablespace must also be LMT. My earlier suggestion to not use LMT for SYSTEM and TEMP tablespaces, should instead be, "Use tablespace management for TEMP tablespaces that is consistent with the SYSTEM tablespace." So, try the TEMP-tablespace CREATE once again, but use LMT to match your SYSTEM tablespace.

Hopefully, your stars will align and it will work. Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[EliseFreedman]

Hi again

Im now a bit confused as to what my temp tablespace create statemtn should be??

 

[SantaMufasa]

Elise,

I'm sorry for the ambiguity in your thread. The bottom line is that once your SYSTEM tablespace is LMT, all subsequent tablespace creation must also be LMT. To reduce the chance of my giving you incorrect advice, could you please post the results of this query on your system:

select tablespace_name, contents, extent_management
from dba_tablespaces
order by 1;

TABLESPACE_NAME                CONTENTS  EXTENT_MAN
------------------------------ --------- ----------
DATA1                          PERMANENT LOCAL
DATA2                          PERMANENT LOCAL
RBS                            PERMANENT LOCAL
SYSTEM                         PERMANENT DICTIONARY
TEMP                           TEMPORARY DICTIONARY
TEMP2                          TEMPORARY LOCAL
TEMP3                          TEMPORARY LOCAL

7 rows selected.

The above output is for my database. It will tell us much to have the output for your system. Once I see the output for your system, I shall suggest code for your TEMP tablespace.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[EliseFreedman]

Here is the output from my system

SQL> select tablespace_name, contents, extent_management
  2  from dba_tablespaces
  3  order by 1;

TABLESPACE_NAME                CONTENTS  EXTENT_MAN
------------------------------ --------- ----------
CWMLITE                        PERMANENT LOCAL
DRSYS                          PERMANENT LOCAL
EXAMPLE                        PERMANENT LOCAL
INDX                           PERMANENT LOCAL
ODM                            PERMANENT LOCAL
SYSTEM                         PERMANENT LOCAL
TEMP                           TEMPORARY LOCAL
TOOLS                          PERMANENT LOCAL
UNDOTBS1                       UNDO      LOCAL
USERS                          PERMANENT LOCAL
XDB                            PERMANENT LOCAL

11 rows selected.

 

[SantaMufasa]

Elise,

It appears to me that you have all the tablespaces you wanted, successfully set up. If you want to add more space to them via additional datafiles, that is a straightforward task, as well. (Let us know if you want that information.)

In the meantime, is there anything on the above listing that represents a deficiency in your tablespaces structures for which you would like assistance?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[EliseFreedman]

Good Morning

I still need to have a temporary tablespace called temporary_data.

Do I have to drop the temp tablespace first before i can create the temporary_data tablespace???

 

[SantaMufasa]

Actually, Elise, it is still "Good Evening" since it is about 1:00 a.m. here.

You should not have to drop the other TEMP tablespace. But since the other TEMP tablespace is one of the new-fangled "tempfile" tablespaces (which we didn't know until you ran that nice listing, above), you can try another approach...Try this code instead of the code from before:

CREATE TABLESPACE temporary_data TEMPORARY
[b]TEMP[/b]FILE 'C:\orac\tempdata.ORA' SIZE 1 M
AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;

Let us know if the TEMP tablespace create with the "tempfile" syntax finally achieves what you want. If it still throws an error, then add on the "EXTENT MANAGEMENT LOCAL AUTOALLOCATE;" syntax and give it yet another go.

I'm eager to hear of your results (but please forgive me if I wait until (my) morning to check out your findings.) Good luck with this, Elise,


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[EliseFreedman]

Hiya

Ive tried both approaches that you have just suggested. However Im getting the following error

ORA-02180: invalid option for CREATE TABLESPACE

Elise

 

[EliseFreedman]

It doesnt seem to like the tempfile bit

 

[stefanhei]

When using LMT following syntax should help:

CREATE TEMPORARY TABLESPACE temporary_data 
TEMPFILE 'C:\orac\tempdata.ORA' SIZE 1 M
AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;

You may use the TEMPFILE (space is allocated when needed) or DATAFILE (space is allocated when you create the tablespace) clause. Difference in syntax is just that keyword.

Stefan

 

[EliseFreedman]

Thanks Stefan

That worked successfully.

Thanks both of you for all your help. As a newbie to Oracle, its much appreciated