Where are sequence objects stored/how are they deleted all at once?
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.
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.
ps. Don't drop the system tablespace.
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?
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.
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.
Absolutely not charlton.Thanks.
Thanks to slimdave also.I;m going to try out a few things now.
Btw, is there any parameter that tells oracle to overwrite all the existing objects while importing?Thx.
Not quite: http://download-east.oracle.com/docs...htm#sthref2448
Originally Posted by Thomasps
If you specify IGNORE=y, Import overlooks object creation errors when it attempts to create database objects, and continues without reporting the errors.
OK, I agree. Then how to over write safely exisitng objects while importing?
Originally Posted by slimdave
Click Here to Expand Forum to Full Width