Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467

    Unanswered: Creating Oracle Managed tablespaces

    SETUP: 9i (9.2.0.1.0)

    I getting 2 errors. The first is not OMT specific.

    I specify cascade constraints but I am still getting this error:
    SQL> DROP TABLESPACE Data INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
    DROP TABLESPACE Data INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS
    *
    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-02429: cannot drop index used for enforcement of unique/primary key

    According to the doc in OMT if I create a tablespace and do not give a data file size Oracle will create a 100M file for me. But here is the result
    1 CREATE TABLESPACE Data
    2 DATAFILE 'D:\ORACLE\ORADATA\BCI92\DATA1.dbf' AUTOEXTEND ON
    3 EXTENT MANAGEMENT LOCAL AUTOALLOCATE
    4* SEGMENT SPACE MANAGEMENT AUTO
    SQL> /
    CREATE TABLESPACE Data
    *
    ERROR at line 1:
    ORA-01119: error in creating database file 'D:\ORACLE\ORADATA\BCI92\DATA1.dbf'
    ORA-17610: file 'D:\ORACLE\ORADATA\BCI92\DATA1.dbf' does not exist and no size specified
    ORA-27041: unable to open file
    OSD-04002: unable to open file
    O/S-Error: (OS 2) The system cannot find the file specified.

    I have work arounds in mind but I am curious if any of my good friends (ok you too Duck :-) ) have an answer.
    NOTE: Please disregard the label "Senior Member".

  2. #2
    Join Date
    Jan 2004
    Posts
    370
    Don't know why you are getting an ORA-02429 when trying to drop the tablespace. Do you have any domain indexes in the tablespace you are trying to drop?

    On the second error on Oracle managed files, is DB_CREATE_FILE_DEST set to an appropriate location?

  3. #3
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    ... on second error

    No. I realized later that this database was not created with OMF in mind. No DB_CREATE_FILE_DEST was specified.
    NOTE: Please disregard the label "Senior Member".

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    I came across a crazy trigger that existed that would not allow me to drop a tablespace or datafile. It drove me crazy until I found it and removed it.

    Looks like you solved the problem though?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •