In my homework assignment, I am asked to create a tablespace called TOOLS. So I used the following command:

CREATE TABLESPACE TOOLS
DATAFILE 'tools1_data.dbf'
SIZE 10M;

So I got an output that "Tablespace created"

I have been asked to assign an existing user to this table space and I did that successfully without any issue. However, when I query the dba_ts_quotas, I am not able to see this tablespace in that list:

What could I be doing wrong here? Any insight is greatly appreciated here. Thanks in advance.

CodePudding user response：

You can only find tablespace information in dba_ts_quotas when you grant quota on it to an specific user.

Quota is the amount of space a specific user can use in a determined tablespace.

DBA_TS_QUOTAS describes tablespace quotas for all users.

So you have to issue a

ALTER USER xxxx QUOTA yyy ON tablespacename ;

Where

• xxxx is the user who you are granting quota on the specific tablespace
• yyyy is the quota. It can be an integer with K as Kilobytes, M as Megabytes or G as gigabytes. If you don't want to limit the quota, you use then quota unlimited
• tablespacename is the tablespace you have created.

Example

ALTER USER SCOTT QUOTA UNLIMITED ON TOOLS;

Demo

SQL> create tablespace tbdemo datafile '/bbdd_odcgrc1r/datos/tbdemo.dbf' size 10M autoextend on next 10m maxsize 10g ;

Tablespace created.

SQL> alter user test1 quota unlimited on tbdemo ;

User altered.

SQL> desc dba_ts_quotas
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLESPACE_NAME                           NOT NULL VARCHAR2(30)
 USERNAME                                  NOT NULL VARCHAR2(128)
 BYTES                                              NUMBER
 MAX_BYTES                                          NUMBER
 BLOCKS                                             NUMBER
 MAX_BLOCKS                                         NUMBER
 DROPPED                                            VARCHAR2(3)

SQL> select tablespace_name,username,max_bytes from dba_ts_quotas
  2  where tablespace_name = 'TBDEMO' ;

TABLESPACE    USERNAME       MAX_BYTES
--------------------------------------
TBDEMO        TEST1          -1

-1 indicates unlimited quota on this tablespace for the user TEST1