cancel
Showing results for 
Search instead for 
Did you mean: 

snapshot too old error during drop tablespace

Former Member
0 Kudos
307

Hi Experts

When we are doing BW reorg and steps followed are

1. created a newtablespace with source tablespace TABART class reference.

2. Export the source tablespace to the filesystem level.

3. DROP the source tablespace now.

4. Rename the new tablespace to source tablespace name.

5. Import

Here in the third step i have received snapshot too old error.

BR0301E SQL error -604 at location BrSqlExecute-1, SQL statement:

'/* BRSPACE */ drop tablespace PSAPADSOLD including contents and datafiles cascade constraints'

ORA-00604: error occurred at recursive SQL level 1

ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too small

BR1017E Execution of SQL statement 'drop tablespace PSAPADSOLD including contents and datafiles cascade constraints' failed

so i tried to rename the tablespace and set to offline and tried to import only 240 tables were imported compared to 24057 tables.Still the PSAPADS - Source tablespace shows 65000 elements.

my queries:

1. After Export of the tablespace how come the Source tablespace retain the tables.

2. why i could not able to drop the tablespace

I had increased the UNDO_RETENTION to 86400 my oracle version is 10.2.04.

Source table space PSAPADS is 80 GB and has only 30 GB data and the remaining are free.

PSAPUNDO was 17GB in size.

Kinldy suggest

Regards

Bala

Accepted Solutions (0)

Answers (5)

Answers (5)

Former Member
0 Kudos

Hi Stefa

Thanks for your reply.

how to find the System undo is still active.How to track which Undo which is active and their steps.

As per the metalink note our PSAPUNDO is Locally Managed Tablepsace hence the second workaround is applicable..

How to validate this will not give a problem again while i am doing reorganization.

When i checked a sap note 1039060 this note is applicable to windows i dont how it can be impleneted.

Whether any merge fix will help on this

Regards

Bala

Former Member
0 Kudos

Hi Mark / Venkatesh

I could see PSAPUNDO is live.is there any methids to verify this and to check what is the error.

Regards

Bala

stefan_koehler
Active Contributor
0 Kudos

Hello Bala,

your issue has nothing to do with AUM itself. The "System Rollback Segment" is used for transactions to system objects (like DDIC changes). The system rollback segment is also active (and used for such activities) even if you are using AUM.

Previously mentioned metalink note #862469.1 is valid in your case and you should adapt the solution of this note.

Regards

Stefan

Former Member
0 Kudos

Hi Venkatesh

I had checked the metalink note. the system has been already moved to Auto Undo Management.

and Master note also mentions the same.

Is there any method to check the Undo issue or any checking

Regards

Bala

Former Member
0 Kudos

Hi Bala,

Have a look at the metalink note.

ORA-604 & ORA-1555 Rollback Segment 0 With Name "System" Too small [ID 862469.1]

Br,

Venky

Former Member
0 Kudos

ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too small

This looks wrong for a start. Have you a rollback segment in your SYSTEM tablespace?

check what is where and online for your rollback stuff

Mark