DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Where are sequence objects stored/how are they deleted all at once?

  1. #1
    Join Date
    Apr 2006
    Posts
    16

    Where are sequence objects stored/how are they deleted all at once?

    Hello,
    I'm having a set of sequences in a given schema 'A' in tablespace USERS.
    Now, I drop the USERS tablespace, and also the datafile for it.I make a new tablespace with the same name, but I can see the sequence objects listed for schema A in Enterprise manager.

    I cannot see any tables though.Building the data dictionary doesnt help.

    May I know how can I get rid of these sequence objects?I;ve tried dropping and creating a new TEMP tablespace, but that didn't help either.

    Thanks.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Sequences aren't individually associated with a tablespace, although the system table that preserves their values and their definition are in SYSTEM of course.

    To drop sequences use the "drop sequence sequence_name" command.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    ps. Don't drop the system tablespace.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Apr 2006
    Posts
    16
    Thanks.

    What I;m trying to do here is import new DB into users tablespace.
    Now, I do drop the USERS tablespace, but the sequence objects dont go away.

    When I import the new database dump, I get many errors pertaining to sequence objects already existing.

    Now I cannot insert anything to my tables, because PK constraint is getting violated (because PK is a sequence object and it's latest value corresponds to the old database state, and not the new one I imported).

    So I wanted to delete all sequence objects as well, before doing the importing.

    Other option is to delete the whole database and create a new one.That way I can get rid of sequence objects.But I really think that it'll be inefficient to do so. Isn't it?

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Dropping the tablespace won't drop any objects that aren't dependent upon it. Triggers would be dropped if they belong to a table that is dropped, but views, procedures, packages and sequences won't, nor will roles and system permissions of the user.

    If you want to drop all of a user's objects then you might do well to drop the user then recreate it with the appropriate grants. Or you can look through the USER_OBJECTS view and use that as the basis for dropping objects.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Jan 2002
    Posts
    96
    Hi

    U can check the sequences using:
    select * from user_sequences;
    This will give you the list of sequences.

    The way I understand your problem is that when you do you import and when you start inserting into the tables the PK is violated because the ID already exists. This means that your sequence is far behind and needs to be set to an amount greater than the tables ID. i.e. do a select sequence_name.currval from dual to determine which value your sequence is set to at the moment and then do a select max(column_PK_using_sequence) from table_name. and see there values and update sequence to the same value as max ID in table.

    Hope I made some sense and didn't waste your time reading this.

  7. #7
    Join Date
    Apr 2006
    Posts
    16
    Absolutely not charlton.Thanks.
    Thanks to slimdave also.I;m going to try out a few things now.

    edit:
    Btw, is there any parameter that tells oracle to overwrite all the existing objects while importing?Thx.

  8. #8
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    ignore=Y
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by Thomasps
    ignore=Y
    Not quite: http://download-east.oracle.com/docs...htm#sthref2448

    If you specify IGNORE=y, Import overlooks object creation errors when it attempts to create database objects, and continues without reporting the errors.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Quote Originally Posted by slimdave
    OK, I agree. Then how to over write safely exisitng objects while importing?
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

Posting Permissions

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


Click Here to Expand Forum to Full Width