users and tablespaces

[dcampo]

Hi all

I'm sorry but I don't write very well the english!!

Is possible a user may to have many tablespaces or only one tablespaces? and why reason?

thanks

 

[SantaMufasa]

DCampo,

Actually, yes, a user may store her/his objects in any tablespace for which s/he has quota. Additionally, a user has a default tablespace, which, unfortunately, is the SYSTEM tablespace if a DBA creates a user without explicitly assigning a default tablespace (besides the SYSTEM tablespace) for the user. Having a default tablespace does not mean that the user can store objects/data in that tablespace without the DBA granting quota on that (or any other tablespace).

If a user wishes to store an object (or its data) in a tablespace that differs from her/his default tablespace, then the user must explicitly indicate, at object-create time, which tablespace s/he wishes for the object.

The way that a DBA grants quota to a user is:

alter user <username> quota <n M | UNLIMITED> on data1;

(for example)

SQL> alter user fred quota 10m on data1;

User altered.

Then, to specify a specific tablespace to hold an object, you can say:

create table x (y number) tablespace data1;

Table created.

Let us know if this answers your questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[dcampo]

thanks for response...

So, a user can multiple tablespaces? in the Manager Oracle, where can I set than an user have multiple tablespaces? because the moment of create an user, only appear a one tablespace.....

I need to know this basic information for create my database in Oracle.

thanks

 

[SantaMufasa]

DCampo,

A user can have only one DEFAULT tablespace, and that is the one that you are seeing in your administrative tool. The way that a user can have objects in many tablespaces results from that user have quota (i.e., tablespace storage allocation) in the additional tablespaces.

The way that you can see how much quota a user has both as a total and consumed for different tablespaces is with this SQL query:

select * from dba_ts_quotas;

TABLESPACE USERNAME        BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS
---------- ---------- ---------- ---------- ---------- ----------
DATA1      DHUNT         5767168         -1        704         -1
SYSTEM     DONTDROP        32768         -1          4         -1
DATA1      TESTNEW       4653056         -1        568         -1
DATA1      TEST         28573696         -1       3488         -1
DATA2      TEST                0  524288000          0      64000

...or, to check quota for the single user to which you are currently connected, you can say:

select * from user_ts_quotas;

TABLESPACE      BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS
---------- ---------- ---------- ---------- ----------
DATA1        28573696         -1       3488         -1
DATA2               0  524288000          0      64000

In either case, wherever you see the value "-1", that translates to "UNLIMITED quota".

Let us know if this answers and resolves your questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]