Skip to main content
ExLibris
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    Cannot create table: "no privileges on tablespace ..."

    • Article Type: General
    • Product: Aleph
    • Product Version: 18.01

    Description:
    **Note - we are doing this work in conjunction with a Primo installation

    We are following the document "Aleph Publishing Mechanism" and have completed most of the technical steps for setting up Aleph publishing on our production instance. We created two new tablespaces (using util/o/17/1): TS5X and TS5D. These tablespaces are on a separate physical drive from the other tablespaces. We also enlarged the redo file(s) from 50 mb to 500 mb. We also edited the file_list in /abc50 to add the z00p lines.

    When we try to create the z00p table (using util/a/17/1) we receive the following errors:

    ---------------------------------------------------------------------------------------------------------
    Enter library name : ABC50
    You asked to delete from library ABC50

    Enter table name to Drop & Create (all/table-name): z00p
    enter yes to DROP z00p table for ABC50 and create new with NO data!!! yes
    This is your last chance!!! Would you like to change your mind? no
    ABC50 table z00p
    l_op == table
    l_table == z00p

    SQL*Plus: Release 10.2.0.4.0 - Production on Mon Apr 6 13:34:53 2009
    Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
    Enter user-name:
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    aleph_admin@LIVE18> 13:34:54 aleph_admin@LIVE18> DROP TABLE ABC50.Z00P
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist

    Elapsed: 00:00:00.00
    CREATE TABLE ABC50.Z00P (
    *
    ERROR at line 1:
    ORA-01950: no privileges on tablespace 'TS5D'

    Elapsed: 00:00:00.06
    13:34:54 aleph_admin@LIVE18> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Deleting statistics .....
    ABC50 index z00p
    l_op == index
    l_table == z00p

    SQL*Plus: Release 10.2.0.4.0 - Production on Mon Apr 6 13:34:55 2009
    Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

    Enter user-name:
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    aleph_admin@LIVE18> 13:34:55 aleph_admin@LIVE18> CREATE UNIQUE INDEX ABC50.Z00P_ID ON ABC50.Z00P (Z00P_REC_KEY ASC)
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist

    Elapsed: 00:00:00.00
    13:34:55 aleph_admin@LIVE18> CREATE INDEX ABC50.Z00P_ID1 ON ABC50.Z00P (Z00P_SET ASC)
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist

    <etc.>

    Resolution:
    We see that the ABC50 user lacks dba_ts_quotas for the TS5D and TS5X tablespaces:

    aleph_admin@LIVE18> select TABLESPACE_NAME, BYTES, BLOCKS from dba_ts_quotas where username='ABC50';
    TABLESPACE_NAME BYTES BLOCKS
    ------------------------------ ---------- ----------
    TS1X 602660864 73567
    TS0 4521984 552
    TS1 0 0
    TS2X 0 0
    TS4X 0 0
    TS3X 922746880 112640
    TS4D 1468006400 179200
    TSLOB 0 0
    TS2D 2684354560 327680
    TS3D 2936012800 358400
    TEMP 0 0
    TS1D 1129603072 137891
    TS2XA 5129633792 626176

    13 rows selected.

    Altering the USER values corrects this:

    aleph_admin@LIVE18> ALTER USER "ABC50" QUOTA UNLIMITED ON "TS5X";

    User altered.

    aleph_admin@LIVE18> ALTER USER "ABC50" QUOTA UNLIMITED ON "TS5D";

    User altered.

    aleph_admin@LIVE18> select TABLESPACE_NAME, BYTES, BLOCKS from dba_ts_quotas where username='ABC50';

    TABLESPACE_NAME BYTES BLOCKS
    ------------------------------ ---------- ----------
    TS1X 602660864 73567
    TS0 4521984 552
    TS1 0 0
    TS2X 0 0
    TS4X 0 0
    TS3X 922746880 112640
    TS4D 1468006400 179200
    TSLOB 0 0
    TS2D 2684354560 327680
    TS3D 2936012800 358400
    TEMP 0 0
    TS5X 0 0
    TS1D 1129603072 137891
    TS2XA 5129633792 626176
    TS5D 0 0


    • Article last edited: 10/8/2013